Bug 77897 - EDITING: Query in GUI-mode fails, when using a function in the field and a fieldname with special characters in criterion of the field
Summary: EDITING: Query in GUI-mode fails, when using a function in the field and a fi...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Queries
  Show dependency tree / graph
 
Reported: 2014-04-24 14:16 UTC by Gerard Bekking
Modified: 2022-03-12 07:29 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Attachments
screenshots (80.88 KB, image/png)
2014-04-24 14:16 UTC, Gerard Bekking 		Details
Database with query, quoted in SQL-view, square brackets in design-view (3.70 KB, application/vnd.sun.xml.base)
2014-04-24 18:28 UTC, Robert Großkopf 		Details
query_quote_date_field.odb (5.23 KB, application/vnd.sun.xml.base)
2014-04-25 02:14 UTC, Gerard Bekking 		Details
Query works with a normal field, doesn't work with Datediff (5.22 KB, application/vnd.sun.xml.base)
2014-04-27 10:08 UTC, Robert Großkopf 		Details
Show Obsolete (1) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Gerard Bekking 2014-04-24 14:16:50 UTC 
Created attachment 97903 [details]
screenshots

After editing and testing in the graphical mode I saved my Query.
When I opend it Icould not run the query (syntax error). However I coul run is in SQL-mode.
After a lot of searching I found that quotes dissapeard when saving. Since I have a space in the fieldname the graphical envirement needs those quotes.

Both LibreOffice 3.5.4.2 Build ID 350m1(build2)(debian) and LibreoOffice 4.2.3.3 downloaded from the LibreOffice-site have the same problem.

I append a screenshot from before saving (or switching to SQL-mode) and after

Before: <["afschrijf periode (jaar)"]*12 

After:  <[afschrijf periode (jaar)]*12 


I am running Debian 7 amd64

Thanks, Gerard Bekking
Comment 1 Robert Großkopf 2014-04-24 18:28:01 UTC 
Created attachment 97919 [details]
Database with query, quoted in SQL-view, square brackets in design-view

I have tried to reproduce the bug. Have created a little database, a table with fieldnames including a space and a query with code, which is shown in square brackets in the design-view.
I switched from design-view to SQL-view. in SQL-view it is shown with quotes. The query works as expected.
Comment 2 Gerard Bekking 2014-04-25 02:14:04 UTC 
Created attachment 97926 [details]
query_quote_date_field.odb

Thanks for your swift reaction,

I modified your database to include date-fields.
Problem returned. After saving the design the query wil not function
anymore in edit mode. It will function corectly in sql-mode
After opening in edit-mode you can't save anymore due to a syntax-error.
In SQL-mode there is no problem.





On Thu, 24 Apr 2014 18:28:01 +0000
bugzilla-daemon@freedesktop.org wrote:

> https://bugs.freedesktop.org/show_bug.cgi?id=77897
> 
> --- Comment #1 from robert@familiegrosskopf.de ---
> Created attachment 97919 [details]
>   --> https://bugs.freedesktop.org/attachment.cgi?id=97919&action=edit
> Database with query, quoted in SQL-view, square brackets in
> design-view
> 
> I have tried to reproduce the bug. Have created a little database, a
> table with fieldnames including a space and a query with code, which
> is shown in square brackets in the design-view.
> I switched from design-view to SQL-view. in SQL-view it is shown with
> quotes. The query works as expected.
>
Comment 3 Robert Großkopf 2014-04-25 19:14:57 UTC 
(In reply to comment #2)
 
> I modified your database to include date-fields.
> Problem returned. After saving the design the query wil not function
> anymore in edit mode. It will function corectly in sql-mode
> After opening in edit-mode you can't save anymore due to a syntax-error.
> In SQL-mode there is no problem.

It seems to be the subquery in Criterion which couldn't be interpreted by the GUI. I have tried to remove the quotes in the SQL-view. Works with square brackets instead.

The difference between square brackets and quotes is not the problem for the GUI, as you could see in my example.

The problem I see: The GUI doesn't recognize the code, but will accept it when the query will be executed without switching to direct SQL.

We need more examples and should change the title of this bug - or create a new which show, when a query isn't executed in GUI-design-mode, but with switched off design.
Comment 4 Gerard Bekking 2014-04-26 01:52:01 UTC 
I think the GUI recognized the code all right and it execute it
properly, only when saved or when switched to SQL (and switched back)
the code is changed in the GUI and can't be executed.
While designing and testing everything works fine.
The manual state that tekst with spaces should always be in quotes,
that is why I found this.

My workaround is replacing all spaces with underscore so quotes are
avoided.




On Fri, 25 Apr 2014 19:14:57 +0000
bugzilla-daemon@freedesktop.org wrote:

> https://bugs.freedesktop.org/show_bug.cgi?id=77897
> 
> robert@familiegrosskopf.de changed:
> 
>            What    |Removed                     |Added
> ----------------------------------------------------------------------------
>                  CC|
> |robert@familiegrosskopf.de
> 
> --- Comment #3 from robert@familiegrosskopf.de ---
> (In reply to comment #2)
> 
> > I modified your database to include date-fields.
> > Problem returned. After saving the design the query wil not function
> > anymore in edit mode. It will function corectly in sql-mode
> > After opening in edit-mode you can't save anymore due to a
> > syntax-error. In SQL-mode there is no problem.
> 
> It seems to be the subquery in Criterion which couldn't be
> interpreted by the GUI. I have tried to remove the quotes in the
> SQL-view. Works with square brackets instead.
> 
> The difference between square brackets and quotes is not the problem
> for the GUI, as you could see in my example.
> 
> The problem I see: The GUI doesn't recognize the code, but will
> accept it when the query will be executed without switching to direct
> SQL.
> 
> We need more examples and should change the title of this bug - or
> create a new which show, when a query isn't executed in
> GUI-design-mode, but with switched off design.
>
Comment 5 Robert Großkopf 2014-04-27 10:08:24 UTC 
Created attachment 98066 [details]
Query works with a normal field, doesn't work with Datediff

I have tested a little bit more. Have added another query to the database. Seems to be a special problem together with DATEDIFF() and fieldnames with special characters. Same problem with MONTH(date1) - MONTH(date2)
There is a query, which works in GUI-mode and in SQL-mode with fields with special characters - also with date-fields.
I don't know if there are other combinations, which doesn't work. Let us try it out.
Comment 6 Gerard Bekking 2014-04-28 00:58:09 UTC 
You suggested a change of title to better represent the issue, which
title would be the best in your opinion?


On Sun, 27 Apr 2014 10:08:24 +0000
bugzilla-daemon@freedesktop.org wrote:

> https://bugs.freedesktop.org/show_bug.cgi?id=77897
> 
> robert@familiegrosskopf.de changed:
> 
>            What    |Removed                     |Added
> ----------------------------------------------------------------------------
>   Attachment #97919 [details]|0                           |1
>         is obsolete|                            |
> 
> --- Comment #5 from robert@familiegrosskopf.de ---
> Created attachment 98066 [details]
>   --> https://bugs.freedesktop.org/attachment.cgi?id=98066&action=edit
> Query works with a normal field, doesn't work with Datediff
> 
> I have tested a little bit more. Have added another query to the
> database. Seems to be a special problem together with DATEDIFF() and
> fieldnames with special characters. Same problem with MONTH(date1) -
> MONTH(date2) There is a query, which works in GUI-mode and in
> SQL-mode with fields with special characters - also with date-fields.
> I don't know if there are other combinations, which doesn't work. Let
> us try it out.
>
Comment 7 Robert Großkopf 2014-04-29 19:11:17 UTC 
I have tried to change the title.
When I add a date-function to the field (could be DATEDIFF, could also be DAY, MONTH ...) you couldn't add a fieldname with special characters to the criterion of the field in GUI-mode. Same criterion would work at fields without functions.

It seems to be the same problem with text-function like UPPER and other functions.

The quotes would be moved. There appear square-brackets for all fieldnames with special characters instead. But this will work with normal fields, not with a function in the field.

Its the same behavior in all LO-versions, beginning with the first LO 3.3.*. So I set the version to "Inherited from OOo"
Comment 8 Alex Thurgood 2015-01-03 17:41:12 UTC Comment hidden (no-value)
Comment 9 QA Administrators 2016-01-17 20:04:46 UTC Comment hidden (obsolete)
Comment 10 Robert Großkopf 2016-01-31 08:21:14 UTC 
Bug still exists with LO 5.1.0.3, OpenSUSE 42.1 Leap, 64bit rpm Linux.
Fieldnames with special characters are set in [square-brackets] instead of "quotes".
Comment 11 QA Administrators 2017-03-06 14:30:40 UTC Comment hidden (obsolete)
Comment 12 Robert Großkopf 2017-03-10 18:43:13 UTC 
Bug still exists with LO 5.3.1.1, OpenSUSE 42.1 Leap, 64bit rpm Linux.
Comment 13 QA Administrators 2018-03-11 03:41:30 UTC Comment hidden (obsolete)
Comment 14 QA Administrators 2020-03-11 03:25:21 UTC Comment hidden (obsolete)
Comment 15 Robert Großkopf 2020-03-11 15:06:44 UTC 
Bug still exists with LO 6.4.2.1, OpenSUSE 15.1, 64bit rpm Linux.
Comment 16 QA Administrators 2022-03-12 03:38:07 UTC Comment hidden (obsolete)
Comment 17 Robert Großkopf 2022-03-12 07:29:03 UTC 
Have tested again.
Downloaded https://bugs.documentfoundation.org/attachment.cgi?id=98066
Executed "query_fails_in_GUI" → works
Opened query for editing in GUI.
Executed query and get
------------------------------------------------------------------------------
SQL-Status: HY000
Fehlercode: 1000
Syntaxfehler im SQL-Ausdruck /home/buildslave/source/libo-core/dbaccess/source/core/api/SingleSelectQueryComposer.cxx:109
------------------------------------------------------------------------------

Bug still exists in LO 7.3.1.3 on OpenSUSE 15.3 64bit rpm Linux