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.
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.
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.
So you are asking for a change of behavior. I think it works as implemented, even we don't like it.
(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.
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.
(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.
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.
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 '%%'"
(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.
(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 '%*'.
(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.
Following last comments, I thought about recycling this into an enhancement. Don't hesitate to revert back if not ok.
https://gerrit.libreoffice.org/c/core/+/120535
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.
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
(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.
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
(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.