Bug 93478 - HSQLDB doesn't support newlines in column name in a database connected to a spreadsheet
Summary: HSQLDB doesn't support newlines in column name in a database connected to a s...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: Other All
: low enhancement
Assignee: Not Assigned
Depends on:
Blocks: Files-Linked Database-HSQLDB
  Show dependency treegraph
Reported: 2015-08-17 03:12 UTC by Jouni Järvinen
Modified: 2020-09-26 11:16 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Overview given by Base's query wizard (616 bytes, text/plain)
2015-08-17 03:12 UTC, Jouni Järvinen
The document (76.04 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-08-17 03:14 UTC, Jouni Järvinen
The error details and the SQL the wizard created (1013 bytes, text/plain)
2015-08-17 03:15 UTC, Jouni Järvinen

Note You need to log in before you can comment on or make changes to this bug.
Description Jouni Järvinen 2015-08-17 03:12:42 UTC
Created attachment 117957 [details]
Overview given by Base's query wizard

I have created a spreadsheet to make it easier to choose which one of specific items to buy. I figured I'd use an SQL database to query the colums for some specific info.

Since 2 of the columns got a newline, the query created by the wizard is syntax violation. Once the newlines are removed, it works perfectly.
Comment 1 Jouni Järvinen 2015-08-17 03:14:09 UTC
Created attachment 117958 [details]
The document
Comment 2 Jouni Järvinen 2015-08-17 03:15:29 UTC
Created attachment 117959 [details]
The error details and the SQL the wizard created
Comment 3 Alex Thurgood 2015-08-17 08:30:22 UTC
@Jouni : if I have understood you correctly, you are expecting Base to deal with line feed characters in your field names. I don't know of any database program that allows that to happen (unless maybe Access does ?). The line feeds for your column headers are purely for presentation / display purposes when the sheet is opened in Calc.

At the moment, I fail to understand what the problem is, or why you think that Base should support such line feed characters in its field names.

Did this, for example, work in previous versions of LibreOffice ? If so, then at least we could confirm that this is a regression, but if not, then theoretically an enhancement request. In the latter case, how should Base know to ignore a line feed character in field name, and what would it substitute that character with (space, underscore, hyphen, delete ?) but not in the data corresponding to a tuple ?

Seems like a very non-trivial coding request to me for little benefit to the user, especially when the workaround is simple, i.e. don't put line feeds in your column headers if you want to then import that spreadsheet into Base.
Comment 4 Jouni Järvinen 2015-08-17 08:41:55 UTC
Base could auto-convert newlines to something else, such as a space, and notify the user. The big deal is you have to completely re-do whatever you entered in the wizard if you didn't know Base can't handle some character[s] you had to use in Calc, for instance.
Comment 5 Alex Thurgood 2015-08-17 09:00:50 UTC
Well, whatever, added as enhancement request.
Comment 6 Jouni Järvinen 2015-12-05 02:04:04 UTC
5.0.4 RC1 ( x64 tested, the newlines are automatically replaced with nothing, uglily concatenating the lines but at least it works just fine. It takes pretty long loading the result, though, on AMD A4-5000 APU which has broken/incomplete OpenCL support regardless of driver version (I'm using very latest betas as they come, but I get no notifications and don't realize to check often).