View Issue Details

IDProjectCategoryView StatusLast Update
14182Bug reports[All Projects] Survey designpublic2019-04-30 09:10
ReportermedhatAssigned Todominikvitt 
PrioritynoneSeveritymajor 
Status closedResolutionfixed 
Product Version3.13.x 
Target VersionFixed in Version3.15.x 
Summary14182: Database error when trying to define a quota
Description

After creating a quota to terminate the survey, I'm getting the following error when trying to specify the related question (by clicking "Add Answer" in quota section) :

"CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: [SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique."

Steps To Reproduce

1- Add new quota, fill-in the details and save
2- click: "Add Answer"
the error will show up.

TagsNo tags attached.
Complete LimeSurvey version number (& build)Version 3.14.8+180829
I will donate to the project if issue is resolvedNo
Browserchrome Version 68.0
Database & DB-VersionMS sql server 2012
Server OS (if known)windows 10
Webserver software & version (if known)IIS
PHP Versionphp 7.0

Activities

dominikvitt

dominikvitt

2018-11-13 11:40

developer   ~49622

I'm unable to reproduce this issue.
Please enable debug mode in your application/config/config.php file with 'debug'=>2 and 'debugsql'=>1 set.
Then save whole page as html file and upload here.

DenisChenu

DenisChenu

2018-11-13 12:09

developer   ~49623

MS sql server 2012 related : each param need a different name. Then maybe something like this

But still : «Columns in the order by list must be unique.» sure, must be fixed :)

sdsAdm1n

sdsAdm1n

2018-11-14 07:42

reporter   ~49644

I just would like to share that I am currently facing same issue using MS SQL Server 2016. the related select statement get executed includes the column name "group_order" twice within the "order by" clause as per following : ORDER BY [groups].[group_order],[t].[question_order], group_order ASC
which cause execution failure of the query.
At MS SQL SERVER studio, the query get executed successfully after removing the extra "group_order" column from the order by clause. Thus, updating corresponding source file that generate the query to fix the syntax of order by clause may resolve the issue.

guest

guest

2018-11-14 11:10

viewer   ~49651

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=28520

dominikvitt

dominikvitt

2018-11-14 11:14

developer   ~49652

This fix should solve your issue.

sdsAdm1n

sdsAdm1n

2018-11-15 12:04

reporter   ~49665

Mentioned fix has been applied, however the issue is not yet resolved. will you please verify on that ?
the order by clause is still duplicating the column [groups].[group_order] : "ORDER BY [groups].[group_order],[t].[question_order], groups.group_order ASC"

guest

guest

2018-11-16 15:07

viewer   ~49681

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=28529

dominikvitt

dominikvitt

2018-11-16 15:09

developer   ~49682

This latest patch should work, I tested it on SQL Server 2017.

https://github.com/LimeSurvey/LimeSurvey/commit/49f76c6b5f74294bc14250e4fb38b45ab533167f

DenisChenu

DenisChenu

2018-11-16 15:15

developer   ~49683

Last edited: 2018-11-16 15:16

View 2 revisions

@dominikvitt : i see 2 times groups.group_order in line shown by sdsAdm1n.

[groups].[group_order],[t].[question_order], groups.group_order

I think the 1st two came from https://github.com/LimeSurvey/LimeSurvey/blob/49f76c6b5f74294bc14250e4fb38b45ab533167f/application/models/Survey.php#L1841
And the second one by https://github.com/LimeSurvey/LimeSurvey/blob/49f76c6b5f74294bc14250e4fb38b45ab533167f/application/models/Survey.php#L396

Since we set the group_order in the relation : we don't need it too for other SQL server ;). More : since it must be 1st : we must remove it from relation, since order is not good …

dominikvitt

dominikvitt

2018-11-16 15:23

developer   ~49684

@DenisChenu: It may seems to be logical way to fix it.
But, if order is removed from relations, it would have major impact, on every Question Group in Survey context.
So, it would give very unpredictable results.

DenisChenu

DenisChenu

2018-11-16 15:37

developer   ~49685

Ok, but then : SQL server have bad order in Question list ?

DenisChenu

DenisChenu

2018-11-16 15:41

developer   ~49686

In my opinion , the group_order relation must be removed

  1. It was added in a commit unrelated with an order issue : https://github.com/LimeSurvey/LimeSurvey/commit/b2c5563d7267a5ee439ef3b97d4fcc1eb86b7d62
  2. Set relation in the JOIN broke real ordering (else we can remove it for all)
  3. It don't exist before August 2017
guest

guest

2018-11-16 16:14

viewer   ~49687

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=28530

Related Changesets

LimeSurvey: master 4866c18e

2018-11-14 11:10:11

Dominik Vitt

Details Diff
Fixed issue 14182: Database error when trying to define a quota using MS sql server 2012 Affected Issues
14182
mod - application/models/Survey.php Diff File

LimeSurvey: master 49f76c6b

2018-11-16 15:06:53

Dominik Vitt

Details Diff
Fixed issue 14182: Database error when trying to define a quota Affected Issues
14182
mod - application/models/Survey.php Diff File

LimeSurvey: master 7a9d137a

2018-11-16 16:13:55

Dominik Vitt

Details Diff
Fixed issue 14182: Database error when trying to define a quota Affected Issues
14182
mod - application/models/Survey.php Diff File

Issue History

Date Modified Username Field Change
2018-10-25 08:25 medhat New Issue
2018-11-13 10:45 dominikvitt Assigned To => dominikvitt
2018-11-13 10:45 dominikvitt Status new => assigned
2018-11-13 11:40 dominikvitt Status assigned => feedback
2018-11-13 11:40 dominikvitt Note Added: 49622
2018-11-13 12:09 DenisChenu Note Added: 49623
2018-11-14 07:42 sdsAdm1n Note Added: 49644
2018-11-14 11:10 Changeset attached => LimeSurvey master 4866c18e
2018-11-14 11:10 guest Note Added: 49651
2018-11-14 11:10 guest Resolution open => fixed
2018-11-14 11:14 dominikvitt Status feedback => resolved
2018-11-14 11:14 dominikvitt Fixed in Version => 3.15.x
2018-11-14 11:14 dominikvitt Note Added: 49652
2018-11-15 12:04 sdsAdm1n Note Added: 49665
2018-11-16 15:07 Changeset attached => LimeSurvey master 49f76c6b
2018-11-16 15:07 guest Note Added: 49681
2018-11-16 15:09 dominikvitt Note Added: 49682
2018-11-16 15:15 DenisChenu Note Added: 49683
2018-11-16 15:16 DenisChenu Note Edited: 49683 View Revisions
2018-11-16 15:23 dominikvitt Note Added: 49684
2018-11-16 15:37 DenisChenu Note Added: 49685
2018-11-16 15:41 DenisChenu Note Added: 49686
2018-11-16 16:14 Changeset attached => LimeSurvey master 7a9d137a
2018-11-16 16:14 guest Note Added: 49687
2019-04-30 09:10 c_schmitz Status resolved => closed