Bug 122461 - SQL parser doesn't accept identifiers containing newlines [and gives a syntax error when Query Wizard correctly generates a query containing one]
Summary: SQL parser doesn't accept identifiers containing newlines [and gives a syntax...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.3.2 release
Hardware: All All
: medium normal
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:7.0.0 target:6.4.5
Keywords:
Depends on:
Blocks:
 
Reported: 2019-01-04 09:21 UTC by Jean-Sebastien Bevilacqua
Modified: 2020-06-05 09:20 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
test file (67.04 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-01-04 09:21 UTC, Jean-Sebastien Bevilacqua
Details
test case with embedded firebird (11.13 KB, application/vnd.oasis.opendocument.database)
2020-05-08 05:54 UTC, Lionel Elie Mamane
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jean-Sebastien Bevilacqua 2019-01-04 09:21:04 UTC
Created attachment 147992 [details]
test file

Hello,

When using the Query Wizard, there is a SQL exception.
Here the steps to reproduce:

1 - Download the attached spreedsheet "data-test.ods"
2 - Open Base > Connect to an existing database > Spreadsheet
3 - Select the data-test.ods and create the database
4 - Select Queries > Use Wizard to Create Query
5 - Double Click on "Contrat" in "Available fields", "Contrat" moves to "Fields in the Query"
6 - Click on Finish
-> You get the SQL error

The error appears only with the fields "Contrat" and "Service d'affectation".
For the others fields, there are no SQL exception.

Sincerely,
Jean-Sébastien
Comment 1 Alex Thurgood 2019-01-04 10:17:21 UTC
Confirming with

Version: 6.3.0.0.alpha0+
Build ID: 608cd2cfe9c82855360c94d629eaac7a76ae212a
CPU threads: 4; OS: Mac OS X 10.14.2; UI render: default; VCL: osx; 
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded

The error message contains further details:

SQL Status: HY000
Error code: 1000

syntax error, unexpected INVALIDSYMBOL, Unterminated name string: ""


Looks like the parser is getting confused by the space character at the end of the column name "Contrat ".

SELECT "Employés"."Contrat
" AS "Contrat
" FROM "Employés" "Employés"

If you edit the Calc document to remove that space, close LO and reload the ODB file, the query operation works successfully.

I suppose it is debatable whether LO should be able to parse space characters at the end of strings intended to be field names, but I would be inclined to say that it should not, and that this is a user error.
Comment 2 Jean-Sebastien Bevilacqua 2019-01-04 10:21:15 UTC
I submitted a fix HERE: https://gerrit.libreoffice.org/#/c/65851/
Comment 3 Xisco Faulí 2019-04-09 12:02:24 UTC
Dear Jean-Sebastien Bevilacqua,
This bug has been in ASSIGNED status for more than 3 months without any
activity. Resetting it to NEW.
Please assigned it back to yourself if you're still working on this.
Comment 4 Lionel Elie Mamane 2020-05-08 05:54:04 UTC
Created attachment 160515 [details]
test case with embedded firebird
Comment 5 Commit Notification 2020-05-08 09:04:45 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b6ab865a371f5c46f96d931721f03afde82b7ec1

tdf#122461 SQL identifiers (names) can contain newlines

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 6 Commit Notification 2020-06-05 09:20:52 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/ede39fd49c03f2f604507bb1b79ac32fe52da493

tdf#122461 SQL identifiers (names) can contain newlines

It will be available in 6.4.5.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.