Bug 143656 - Tools > SQL : add a checkbox to enable Direct SQL
Summary: Tools > SQL : add a checkbox to enable Direct SQL
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.1.5.2 release
Hardware: All All
: medium enhancement
Assignee: Julien Nabet
URL:
Whiteboard: target:7.3.0
Keywords:
Depends on:
Blocks:
 
Reported: 2021-08-01 07:07 UTC by Robert Großkopf
Modified: 2021-09-24 07:07 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Open the database, test the queries and do the same with Direct → SQL (13.07 KB, application/vnd.oasis.opendocument.database)
2021-08-01 07:07 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2021-08-01 07:07:05 UTC
Created attachment 174003 [details]
Open the database, test the queries and do the same with Direct → SQL

Open the attached database.
There is a table with some "Name", which could be marked by *.
The following SQL-commenad should be executed:

SELECT * FROM "Person" WHERE "Name" LIKE '%*'

It has to show all "Name", which will end with an '*'.

Execute Query_GUI.
It will show all rows of the table. The GUI will set '*' to '%'.

Execute Query_direct_SQL.
It will show one row. The query is send directly to the database. This is the expected result.

Now start Tools → SQL.
Copy the query
SELECT * FROM "Person" WHERE "Name" LIKE '%*'
Chose "Show output of "select" statements" and press "Execute" (doesn't work with LO 7.2.0.2 - use a version before 7.2)
You get the same result as shown in Query_GUI: All rows will be shown.

Open the form.
The same query could be started from the button in the form. 
A macro will be executed and the result will be shown.
Same result as in Tools → SQL and Query_GUI.

Seems only "Run SQL command directly", chosen in the Query-editor, will be executed directly. Tools → SQL will be interpreted at some point and set the code to wrong code. Same behavior with Macros.

Expected behavior: Query_direct_SQL should show the same content as Tools → SQL and queries in a macro.

This bug has been detected in LO 7.1.5.1, but it will be the same in older versions. Tested with OpenSUSE 15.2

Hint: You cant test with LO 7.2.0.2, because the "Execute" button in Tools → SQL won't be activated.
Comment 1 m_a_riosv 2021-08-01 13:44:24 UTC
See the help
https://help.libreoffice.org/latest/en-US/text/sdatabase/02010100.html?&DbPAR=BASE&System=WIN
search for
Like Escape Sequence: {escape 'escape-character'}
Example: select * from Item where ItemName like 'The *%' {escape '*'}
The example will give you all of the entries where the item name begins with 'The *'. This means that you can also search for characters that would otherwise be interpreted as placeholders, such as *, ?, _, % or the period.

So in LO you need to escape *, with in direct-sql not.

I think it's not a bug, it is how it works. Maybe there was a reason to implement such think in base.
Comment 2 Robert Großkopf 2021-08-01 14:34:31 UTC
Please let it unconfirmed.
There shouldn't be a difference between direct connecting to the database with a query and Tools → SQL.

I know it will work with
SELECT * FROM "Person" WHERE "Name" LIKE '%\*' ESCAPE '\'
This will also work in GUI.

HSQLDB accepts also 
SELECT * FROM "Person" WHERE "Name" LIKE '%*'
with the right results, when switching to direct SQL. And this should be the way Tools → SQL and also executing through macro should work: Direct to the database without changing the content of a query.

By the way: It is the same buggy behavior when connecting to MariaDB.
SELECT * FROM `Person` WHERE `Name` LIKE '%*'
will work with direct SQL in the query-editor (and also, for example, in PHPMyAdmin), but only
SELECT * FROM `Person` WHERE `Name` LIKE '%$*' ESCAPE '$'
will give the same result with Tools → SQL

Only queries created through the GUI should change the content so LO Base could work with this queries, for example to input data. Changing content of input though macros and Tools → SQL shouldn't be allowed.
Comment 3 m_a_riosv 2021-08-01 14:54:51 UTC
So you are asking for a change of behavior. I think it works as implemented, even we don't like it.
Comment 4 Robert Großkopf 2021-08-01 15:08:57 UTC
(In reply to m.a.riosv from comment #3)
> So you are asking for a change of behavior. I think it works as implemented,
> even we don't like it.

Yes, the behavior should be changed. 
We had a discussion in a German forum. Someone asks for a code of such a query and I tested this in the query editor. So I answered: This code will work well in direct SQL, wouldn't work through GUI. 

Answer: No problem - I will use the code in a macro. 

And then: It doesn't work in a macro, it doesn't work in Tools → SQL. This doesn't make sense, because you couldn't use the features of the GUI, special direct input of data in a query, with Tools → SQL or macro. You have to send an INSERT then (or UPDATE for changing content).

We should see if someone knew why it has been implemented in this way. And also should see how it could be removed, if there couldn't be found any reason for.
Comment 5 m_a_riosv 2021-08-02 17:16:23 UTC
I didn't find exactly, but should be possible to run the sql as direct in a macro.
In https://wiki.documentfoundation.org/images/b/b0/BH5009-Macros.pdf, page 71 shows how to enable direct sql for mail-merge.
Comment 6 Robert Großkopf 2021-08-02 17:45:53 UTC
(In reply to m.a.riosv from comment #5)
> I didn't find exactly, but should be possible to run the sql as direct in a
> macro.
> In https://wiki.documentfoundation.org/images/b/b0/BH5009-Macros.pdf, page
> 71 shows how to enable direct sql for mail-merge.

It's only mail merge and I haven't tested it works not the same way as SQL in macros will work. Mail merge needs to know here, if it should use a table directly (by tablename), a query directly (by name of the query) or SQL-code, which should be written separately instead of the name of a query, as I have written in the Base Handbook.

Note: I have written most of the content of Base Handbook. Base Handbook is translated to English from the German Base Handbuch. When I have written all these I haven't detected there is a difference between direct SQL in a query (which is real direct) and SQL in macros and Tools → SQL. I thought all this will be the same - but it isn't. If there is a real good argument for the difference between real direct SQL in a query and SQL in macros and Tools → SQL I have to change the content of the (German) Handbook here. My opinion: The difference, which appears here, isn't intended. It is a buggy behavior.
Comment 7 Julien Nabet 2021-08-03 15:16:26 UTC
On pc Debian x86-64 with master sources updated today, I could reproduce this.
(With https://cgit.freedesktop.org/libreoffice/core/commit/?id=5b98dd53c7dc101d3a5ff693d3f0520ec1abd3d1, we can now test again Tools/Sql)

I put Lionel on cc but I trust Robert's judgement since, even if he's not a dev, is a functional reference for Base part.

Let's put 7.1.5 as earliest version even if we're pretty sure it's older.
If someone wants to give it a try with an older version and confirm the behaviour, don't hesitate to update this value.
Comment 8 Julien Nabet 2021-08-03 15:30:35 UTC
At least for Tools/SQL the escape processing is done here:
dbaccess/source/core/api/statement.cxx:480

Here's a part of gdb session.
OStatement::execute (this=0x8661500, _rSQL="SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%*'") at dbaccess/source/core/api/statement.cxx:480
480	    MutexGuard aGuard(m_aMutex);
(gdb) n
481	    ::connectivity::checkDisposed(OComponentHelper::rBHelper.bDisposed);
(gdb) n
483	    disposeResultSet();
(gdb) n
485	    OUString sSQL( impl_doEscapeProcessing_nothrow( _rSQL ) );
(gdb) p _rSQL
$4 = "SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%*'"
(gdb) n
486	    return m_xAggregateStatement->execute( sSQL );
(gdb) p sSQL
$5 = "SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%%'"
Comment 9 Lionel Elie Mamane 2021-08-03 16:11:09 UTC
(In reply to Robert Großkopf from comment #0)

> Expected behavior: Query_direct_SQL should show the same content as Tools →
> SQL and queries in a macro.

No, that's not the documented behaviour. In the macro, to disable escape processing (that is, to do the query as "direct SQL"), insert a line

	oSQL_Statement.EscapeProcessing = False

after

	oSQL_Statement = oConnection.createStatement()

but before

	oResult = oSQL_Statement.executeQuery(stSql)

As to "Tools/SQL", it would be useful to have in this dialog a checkbox to enable/disable escape processing, too. The UI can present it as "Run SQL command directly" to be consistent with the Query Design UI.
Comment 10 Robert Großkopf 2021-08-04 05:42:57 UTC
(In reply to Julien Nabet from comment #8)
> (gdb) p _rSQL
> $4 = "SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%*'"
> (gdb) n
> 486	    return m_xAggregateStatement->execute( sSQL );
> (gdb) p sSQL
> $5 = "SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%%'"

This is the reason for the buggy behavior: Not the Escaping, but the function, which returns '%%' instead of '%*'.
Comment 11 Robert Großkopf 2021-08-04 05:55:52 UTC
(In reply to Lionel Elie Mamane from comment #9)
> 
> 	oSQL_Statement.EscapeProcessing = False
> 
You are right, works well in the attached database. Don't know why it has something to do with escaping, because there is changed a '*' for a '%' …

> As to "Tools/SQL", it would be useful to have in this dialog a checkbox to
> enable/disable escape processing, too. The UI can present it as "Run SQL
> command directly" to be consistent with the Query Design UI.

TOOLS → SQL should be improved a little bit. See  bug 140298. So it would be good to add such a checkbox.
Comment 12 Julien Nabet 2021-08-16 09:33:05 UTC
Following last comments, I thought about recycling this into an enhancement.

Don't hesitate to revert back if not ok.
Comment 13 Julien Nabet 2021-08-16 10:36:14 UTC
https://gerrit.libreoffice.org/c/core/+/120535
Comment 14 Commit Notification 2021-08-16 12:31:33 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/76f89b0097c02fa68c36cfc9a31de3b2e9166abc

tdf#143656: Tools > SQL : add a checkbox to enable Direct SQL

It will be available in 7.3.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 15 Julien Nabet 2021-08-16 12:32:39 UTC
Cherry-pick for 7.2 (so we don't need to wait for 7.3.0) waiting for review here:
https://gerrit.libreoffice.org/c/core/+/120446
Comment 16 Julien Nabet 2021-08-16 15:16:37 UTC
(In reply to Julien Nabet from comment #15)
> Cherry-pick for 7.2 (so we don't need to wait for 7.3.0) waiting for review
> here:
> https://gerrit.libreoffice.org/c/core/+/120446

Sorry, I had forgotten the string freeze.
https://wiki.documentfoundation.org/ReleasePlan/7.2
=> cherry-pick for 7.2 abandoned.
Comment 17 flywire 2021-09-22 22:31:15 UTC
Resolved but version not yet available. Another test:

(In reply to Robert Großkopf from comment #4)
> This code will work well in direct SQL, wouldn't work through GUI. 

Similar discussion https://ask.libreoffice.org/t/recursive-tree-in-base-sqlite3/68241/1 - test code from https://sqlite.org/lang_with.html#controlling_depth_first_versus_breadth_first_search_of_a_tree_using_order_by

Empty SQLite database:
1. Output is never generated by Tools → SQL
2. Run code through Query → Direct SQL and table is generated first time, run WITH RECURSIVE... code again and output is generated. ???


*query.sql*

CREATE TABLE org(
  name TEXT PRIMARY KEY,
  boss TEXT REFERENCES org
) WITHOUT ROWID;
INSERT INTO org VALUES('Alice',NULL);
INSERT INTO org VALUES('Bob','Alice');
INSERT INTO org VALUES('Cindy','Alice');
INSERT INTO org VALUES('Dave','Bob');
INSERT INTO org VALUES('Emma','Bob');
INSERT INTO org VALUES('Fred','Cindy');
INSERT INTO org VALUES('Gail','Cindy');

WITH RECURSIVE
  under_alice(name,level) AS (
    VALUES('Alice',0)
    UNION ALL
    SELECT org.name, under_alice.level+1
      FROM org JOIN under_alice ON org.boss=under_alice.name
     ORDER BY 2 DESC
  )
SELECT substr('..........',1,level*3) || name FROM under_alice;

*output*

Alice
...Bob
......Dave
......Emma
...Cindy
......Fred
......Gail
Comment 18 Robert Großkopf 2021-09-23 13:16:50 UTC
(In reply to flywire from comment #17)
> Resolved but version not yet available.

Seems it isn't totally resolved by the checkbox. 
Have downloaded daily build from 2021-09-23.
The query from posted report will work, but the query posted in comment #17 for SQLite and ODBC gives

1: The execution of the update statement doesn't effect any rows. /tinderbox/buildslave/source/libo-master/connectivity/source/drivers/odbc/OStatement.cxx:524

But this query
-----

WITH RECURSIVE
  under_alice(name,level) AS (
    VALUES('Alice',0)
    UNION ALL
    SELECT org.name, under_alice.level+1
      FROM org JOIN under_alice ON org.boss=under_alice.name
     ORDER BY 2 DESC
  )
SELECT substr('..........',1,level*3) || name FROM under_alice;

-----
will work in direct SQL in the query editor.