View Issue Details

Jump to NotesJump to History
This bug affects 1 person(s).
 26
IDProjectCategoryView StatusDate SubmittedLast Update
03494Bug reportsSurvey editingpublic2009-07-21 15:152009-07-31 14:42
Reporteruser3749Assigned Tojcleeland  
PrioritynormalSeveritycrash 
Status closedResolutionduplicate 
Product Version1.85+ 
Summary03494: MS SQL syntax error upon survey activation attempt
Description

LimeSurvey Version 1.85+ (7337)

when attempting to activate a newly created survey....

xxxxxxx/admin/admin.php?action=activate&sid=58364

a SQL error is returned

syntax error

applies to activate.php

line 63

$groupquery = "SELECT g.gid,g.group_name,count(q.qid) as count from {$dbprefix}questions as q RIGHT JOIN {$dbprefix}groups as g ON q.gid=g.gid WHERE g.sid=$postsid group by g.gid AND g.language='$baselang' AND q.language='$baselang';";

should be

$groupquery = "SELECT g.gid,g.group_name,count(q.qid) as count from {$dbprefix}questions as q RIGHT JOIN {$dbprefix}groups as g ON q.gid=g.gid WHERE g.sid=$postsid AND g.language='$baselang' AND q.language='$baselang' GROUP BY g.gid, g.group_name;";

at least for MSSQL, which I am using.

I changed my code and it works now.

TagsNo tags attached.
Bug heat26
Complete LimeSurvey version number (& build)7337
I will donate to the project if issue is resolved
BrowserIE 8
Database type & versionMSSQL 2005
Server OS (if known)WINDOWS SERVER 2008
Webserver software & version (if known)IIS7
PHP Version5

Relationships

Relationship GraphDependency Graph
has duplicate 03520 closedc_schmitz MSSQL Syntax error 
has duplicate 03527 closedkadejo Due to SQL bad syntax, survey can not be activated at all under PostgreSQL (any version) 
has duplicate 03583 closeduser372 SQL error when activating a survey 

Users monitoring this issue

There are no users monitoring this issue.

Activities

bobc55

bobc55

2009-07-21 21:33

reporter   ~08892

group by is standard for all SQL based database engines. Must be used when there is any aggregation like sum, avg, count and etc.

the group by must include all columns used within the select.

user3749

2009-07-21 23:13

  ~08893

I guess the "bug" which I was reporting was the two AND clauses being in the GROUP BY statement.

In my opinion, those AND clauses should be in part of the WHERE statement... as I rewrote the code. Otherwise the query crashes.

Issue History

Date Modified Username Field Change
2009-07-21 15:15 user3749 New Issue
2009-07-21 15:15 user3749 Status new => assigned
2009-07-21 15:15 user3749 Assigned To => user372
2009-07-21 15:15 user3749 Build Number => 7337
2009-07-21 15:15 user3749 Browser => IE 8
2009-07-21 15:15 user3749 Database & DB-Version => MSSQL 2005
2009-07-21 15:15 user3749 Operating System (Server) => WINDOWS SERVER 2008
2009-07-21 15:15 user3749 Webserver => IIS7
2009-07-21 15:15 user3749 PHP Version => 5
2009-07-21 16:01 user372 Assigned To user372 => jcleeland
2009-07-21 21:33 bobc55 Note Added: 08892
2009-07-21 23:13 user3749 Note Added: 08893
2009-07-28 11:29 user372 Relationship added has duplicate 03520
2009-07-31 07:18 user372 Relationship added has duplicate 03527
2009-07-31 14:42 c_schmitz Status assigned => closed
2009-07-31 14:42 c_schmitz Resolution open => duplicate
2009-08-19 00:55 c_schmitz Relationship added has duplicate 03583
2010-10-25 00:17 c_schmitz Category Survey Design => Survey design
2019-11-01 17:25 c_schmitz Category Survey design => Survey editing