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. |
---|