View Issue Details

IDProjectCategoryView StatusLast Update
15869Bug reports[All Projects] _ Unknownpublic2020-02-13 16:11
Reportermarkusmr Assigned To 
PriorityhighSeveritycrash 
Status confirmedResolutionopen 
Product Version4.1.2 
Target VersionFixed in Version 
Summary15869: PostgreSQL support is broken
Description

I am experiencing a lot of issues with a completely new installation with PostgreSQL as database.

When I open the created survey in the admin interface and click on "Responses" and "Partial (saved) responses" I get:
CDbCommand failed to execute the SQL statement: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "group"
LINE 1: ...gid"="group"."gid") WHERE (t.sid=471146) ORDER BY group.grou...
^. The SQL statement executed was: SELECT "t"."qid" AS "t0_c0", "t"."parent_qid" AS "t0_c1", "t"."sid" AS "t0_c2", "t"."gid" AS "t0_c3", "t"."type" AS "t0_c4", "t"."title" AS "t0_c5", "t"."preg" AS "t0_c6", "t"."other" AS "t0_c7", "t"."mandatory" AS "t0_c8", "t"."encrypted" AS "t0_c9", "t"."question_order" AS "t0_c10", "t"."scale_id" AS "t0_c11", "t"."same_default" AS "t0_c12", "t"."relevance" AS "t0_c13", "t"."modulename" AS "t0_c14", "group"."gid" AS "t1_c0", "group"."sid" AS "t1_c1", "group"."group_order" AS "t1_c2", "group"."randomization_group" AS "t1_c3", "group"."grelevance" AS "t1_c4" FROM "lime_questions" "t" LEFT OUTER JOIN "lime_groups" "group" ON ("t"."gid"="group"."gid") WHERE (t.sid=471146) ORDER BY group.group_order DESC, question_order
The problem here is that group is used without double quotes in ORDER BY, even though it is an reserved keyword.

When I go to the survey list and click the statistics button:
CDbCommand failed to execute the SQL statement: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~ unknown
LINE 1: ... COUNT() FROM "lime_saved_control" "t" WHERE sid LIKE '%471...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.. The SQL statement executed was: SELECT COUNT(
) FROM "lime_saved_control" "t" WHERE sid LIKE :ycp0. Bound with :ycp0='%471146%'
The problem is that sid is Integer type, but LIKE is not defined for Integer type. Casting with sid::TEXT works fine.

When using JSON RPC method export_responses_by_token:
CDbCommand failed to execute the SQL statement: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table \"answerl10ns\"\nLINE 1: ...id = question.qid) WHERE (question.sid=471146 AND answerL10n...\n ^. The SQL statement executed was: SELECT \"t\".\"aid\" AS \"t0_c0\", \"t\".\"qid\" AS \"t0_c1\", \"t\".\"code\" AS \"t0_c2\", \"t\".\"sortorder\" AS \"t0_c3\", \"t\".\"assessment_value\" AS \"t0_c4\", \"t\".\"scale_id\" AS \"t0_c5\", \"answerL10ns\".\"id\" AS \"t1_c0\", \"answerL10ns\".\"aid\" AS \"t1_c1\", \"answerL10ns\".\"answer\" AS \"t1_c2\", \"answerL10ns\".\"language\" AS \"t1_c3\", \"question\".\"qid\" AS \"t2_c0\", \"question\".\"parent_qid\" AS \"t2_c1\", \"question\".\"sid\" AS \"t2_c2\", \"question\".\"gid\" AS \"t2_c3\", \"question\".\"type\" AS \"t2_c4\", \"question\".\"title\" AS \"t2_c5\", \"question\".\"preg\" AS \"t2_c6\", \"question\".\"other\" AS \"t2_c7\", \"question\".\"mandatory\" AS \"t2_c8\", \"question\".\"encrypted\" AS \"t2_c9\", \"question\".\"question_order\" AS \"t2_c10\", \"question\".\"scale_id\" AS \"t2_c11\", \"question\".\"same_default\" AS \"t2_c12\", \"question\".\"relevance\" AS \"t2_c13\", \"question\".\"modulename\" AS \"t2_c14\" FROM \"lime_answers\" \"t\" LEFT OUTER JOIN \"lime_answer_l10ns\" \"answerL10ns\" ON (\"answerL10ns\".\"aid\"=\"t\".\"aid\") LEFT OUTER JOIN \"lime_questions\" \"question\" ON (t.qid = question.qid) WHERE (question.sid=471146 AND answerL10ns.language = 'en') ORDER BY question.question_order, t.scale_id, sortorder
Here "answerL10ns" is used as an alias, but it is later used without quotes. In Postgres names without quotes are converted to lowercase, which means the alias is not found.

When using JSON RPC method add_response:
CDbCommand failed to execute the SQL statement: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table \"questionl10ns\"\nLINE 1: ...qid\") WHERE (((sid='471146') AND (type='|')) AND (questionL1...\n ^. The SQL statement executed was: SELECT \"t\".\"qid\" AS \"t0_c0\", \"t\".\"parent_qid\" AS \"t0_c1\", \"t\".\"sid\" AS \"t0_c2\", \"t\".\"gid\" AS \"t0_c3\", \"t\".\"type\" AS \"t0_c4\", \"t\".\"title\" AS \"t0_c5\", \"t\".\"preg\" AS \"t0_c6\", \"t\".\"other\" AS \"t0_c7\", \"t\".\"mandatory\" AS \"t0_c8\", \"t\".\"encrypted\" AS \"t0_c9\", \"t\".\"question_order\" AS \"t0_c10\", \"t\".\"scale_id\" AS \"t0_c11\", \"t\".\"same_default\" AS \"t0_c12\", \"t\".\"relevance\" AS \"t0_c13\", \"t\".\"modulename\" AS \"t0_c14\", \"questionL10ns\".\"id\" AS \"t1_c0\", \"questionL10ns\".\"qid\" AS \"t1_c1\", \"questionL10ns\".\"question\" AS \"t1_c2\", \"questionL10ns\".\"help\" AS \"t1_c3\", \"questionL10ns\".\"script\" AS \"t1_c4\", \"questionL10ns\".\"language\" AS \"t1_c5\" FROM \"lime_questions\" \"t\" LEFT OUTER JOIN \"lime_question_l10ns\" \"questionL10ns\" ON (\"questionL10ns\".\"qid\"=\"t\".\"qid\") WHERE (((sid=:ycp0) AND (type=:ycp1)) AND (questionL10ns.language=:ycp2)). Bound with :ycp0='471146', :ycp1='|', :ycp2='en'
This is the same problem as above, only with questionL10ns as alias.

Unfortunately I suspect that there are a lot more occurrences of similar issues than I have found.

Steps To Reproduce

Setup

  1. Use PostgreSQL as database.
  2. Create a survey with one question.
  3. Create a complete and a partial response. (I don't know if this is even necessary)
  • In the admin interface click on "Responses" and "Partial (saved) responses".
  • Go to the survey list and click the statistics button.
  • Use the JSON RPC method export_responses_by_token.
  • Use JSON RPC method add_response.
Additional Information

Similar issue: https://bugs.limesurvey.org/view.php?id=15742 (with RPC method export_responses)

TagsNo tags attached.
Complete LimeSurvey version number (& build)Version 4.1.4+200214
I will donate to the project if issue is resolvedNo
Browser
Database & DB-Versionpostgresql-12, 12.1-1.pgdg18.04+1
Server OS (if known)Ubuntu 18.04.2
Webserver software & version (if known)Apache 2.4.29
PHP Version7.2.24-0ubuntu0.18.04.2

Activities

ollehar

ollehar

2020-02-13 16:11

administrator   ~55973

Thanks for reporting. This is work-in-progress right now.

Issue History

Date Modified Username Field Change
2020-02-13 16:09 markusmr New Issue
2020-02-13 16:11 ollehar Note Added: 55973
2020-02-13 16:11 ollehar Priority none => high
2020-02-13 16:11 ollehar Status new => confirmed