View Issue Details

IDProjectCategoryView StatusLast Update
15742Bug reportsRemoteControlpublic2020-03-16 13:23
Reporterfabianlehner Assigned Toc_schmitz  
PriorityhighSeveritypartial_block 
Status closedResolutionfixed 
Product Version4.0.0-RC14 
Fixed in Version4.1.10 
Summary15742: RC export_responses fails with "undefined table"
Description

On my newly-upgraded installation, the api call export_responses fails (used to work on 3.21.1):

Request:
{"method": "export_responses", "params": {"sSessionKey": "(key)", "iSurveyID": (survey id), "sDocumentType": "json", "sLanguageCode": null, "sCompletionStatus": "complete", "sHeadingType": "code", "sResponseType": "short", "iFromResponseID": null, "iToResponseID": null}, "id": 1}

Response:
CDbCommand failed to execute the SQL statement: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "answerl10ns"
LINE 1: ...id = question.qid) WHERE (question.sid=(survey id) AND answerL10n...

Steps To Reproduce

Activate JSON-RPC interface and issue an export_responses request to the endpoint like above.

Additional Information

tried on two distinct installations of the same version, on separate servers

TagsNo tags attached.
Complete LimeSurvey version number (& build)4.0.0+200116
I will donate to the project if issue is resolvedNo
Browser
Database & DB-VersionPostgres 10
Server OS (if known)Debian
Webserver software & version (if known)nginx
PHP Version7.4.1

Relationships

has duplicate 15934 closedc_schmitz Exporting survey responses leads to 'CDbCommand failed to execute the SQL Statement' 

Activities

fabianlehner

fabianlehner

2020-01-24 13:03

reporter   ~55456

Hi,
thanks @cdorin for taking this. Are you able to reproduce the issue? Can I somehow help to resolve it?

cdorin

cdorin

2020-01-24 13:11

manager   ~55457

Not yet. I am aware of the issues that exist with Posgres though - we will discuss in the team about them and try solving them asap.

Can you try to use 4.0.1 and let us know if the issue still persists?

fabianlehner

fabianlehner

2020-01-24 13:17

reporter   ~55458

Thanks for your fast response!
Tried with 4.0.1+200120, same issue unfortunately.

Database says:
db_1 | 2020-01-24 12:13:45.647 UTC [397] ERROR: missing FROM-clause entry for table "answerl10ns" at character 1013
db_1 | 2020-01-24 12:13:45.647 UTC [397] STATEMENT: 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"."question_order" AS "t2_c9", "question"."scale_id" AS "t2_c10", "question"."same_default" AS "t2_c11", "question"."relevance" AS "t2_c12", "question"."modulename" AS "t2_c13", "question"."encrypted" 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={survey id} AND answerL10ns.language = 'de-informal') ORDER BY question.question_order, t.scale_id, sortorder

Please let me know if I can help further!

fabianlehner

fabianlehner

2020-01-24 13:24

reporter   ~55460

The issue might be that in postgres, you have to place an "AS" before the table alias (hence, LEFT OUTER JOIN "lime_answer_l10ns" AS "answerL10ns") whereas in MySQL you can leave it out. I don't have enough insight how queries are created in LS/Yii (I'm more fluent in Django), but assuming that queries are auto-generated I wonder why this issue doesn't arise elsewhere.

fabianlehner

fabianlehner

2020-01-29 10:18

reporter   ~55557

Any news on that @cdorin? I could try to dig deeper if you give me a hint where and how the query is built.

fabianlehner

fabianlehner

2020-01-31 23:45

reporter   ~55611

I got news, at least. My first guess was wrong; the aliasing in the query is valid also in Postgres.
What doesn't work, is the alias naming answerL10ns: In Postgres, unquoted identifiers (such as the answersL10ns in the WHERE-clause) are always folded to lowercase, wheres quoted identifiers (such as the "answerL10ns" everywhere else in the clause) are used as-is (see https://www.postgresql.org/docs/10/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS for more info, especially the last paragraph of that section).

I tried to run the query with quotes added (in application/helpers/admin/export/SurveyDao.php, line 46 on fc998ce) – works! Now I only need to know how to do this cross-DB-safely, since MySQL uses backticks instead of double quotes as table name delimiter per default, afaik (not an expert in MySQL). If you can give me a hint on how this is done in LS/Yii, I'd be happy to create a PR.

c_schmitz

c_schmitz

2020-03-13 18:33

administrator   ~56445

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

c_schmitz

c_schmitz

2020-03-13 18:33

administrator   ~56446

Thank you!

lime_release_bot

lime_release_bot

2020-03-16 13:23

administrator   ~56571

Fixed in Release 4.1.11+200316

Related Changesets

LimeSurvey: master 532863fb

2020-03-13 18:32:39

c_schmitz

Details Diff
Fixed issue 15742: RC export_responses fails with "undefined table" on Postgres Affected Issues
15742
mod - application/helpers/admin/export/SurveyDao.php Diff File

Issue History

Date Modified Username Field Change
2020-01-20 14:20 fabianlehner New Issue
2020-01-22 16:08 cdorin Assigned To => cdorin
2020-01-22 16:08 cdorin Status new => assigned
2020-01-24 13:03 fabianlehner Note Added: 55456
2020-01-24 13:11 cdorin Note Added: 55457
2020-01-24 13:17 fabianlehner Note Added: 55458
2020-01-24 13:24 fabianlehner Note Added: 55460
2020-01-24 13:37 cdorin Priority none => high
2020-01-29 10:18 fabianlehner Note Added: 55557
2020-01-31 23:45 fabianlehner Note Added: 55611
2020-03-13 18:33 c_schmitz Changeset attached => LimeSurvey master 532863fb
2020-03-13 18:33 c_schmitz Note Added: 56445
2020-03-13 18:33 c_schmitz Assigned To cdorin => c_schmitz
2020-03-13 18:33 c_schmitz Resolution open => fixed
2020-03-13 18:33 c_schmitz Status assigned => resolved
2020-03-13 18:33 c_schmitz Fixed in Version => 4.1.10
2020-03-13 18:33 c_schmitz Note Added: 56446
2020-03-13 18:43 c_schmitz Relationship added has duplicate 15934
2020-03-16 13:23 lime_release_bot Note Added: 56571
2020-03-16 13:23 lime_release_bot Status resolved => closed