I must recognize I don't understand how to reproduce this.

Here what I did:
- create a brand new odb file (HSQL embedded)
- select "Create Query in SQL View..."
- copy paste:
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 un


- Click "Run SQL Command directly" icon (at right)
- Click "Save" icon + close
- Double click query to execute it
=> Error message:
SQL Status: 00000
Error code: -155

Statement does not generate a result set /home/julien/lo/libreoffice/connectivity/source/drivers/jdbc/Object.cxx:174

(In reply to Julien Nabet from comment #1)
> I must recognize I don't understand how to reproduce this.
> 
> Here what I did:
> - create a brand new odb file (HSQL embedded)

You lose! How can anyone understand how to do this? *Please* confirm bug 144588 and see bug 144588, comment 9 for the wiki I created (unfortunately only for Windows). Despite comments by Alex Thurgood existing help is inadequate and the UI is poor too.</rant>

TLDR; Create empty file as SQLite database, Install ODBC Driver, Configure database ODBC connection, Open Base which runs Database Wizard, Connect existing database. As per comment 0.

I added Robert Großkopf for a technical view.

@Julien
You need an SQLite-connection with ODBC. The command flywire posted won't run with internal HSQLDB.

If you have connected to SQLite (empty file for the database content, ODBC installed) you could run

---
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');
---
under tools → SQL

This will work. The part, which is really a query, won't work under Tools → SQL, but will work when created as a query with direct SQL:

---
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;
---
Try this as a query, switched to direct SQL. It will give the result flywire posted.
Try this query with Tools → SQL, switched by the new possibility to "Run SQL command directly". It won't give any result, The message will be:
"The execution of the update statement doesn't effect any rows."

So we get different results in the query editor and in Tools → SQL with direct executed SQL. Seems Tools → SQL doesn't really run directly, only sets escaping to false.

Thank you Robert, I could reproduce this.

The problem is in dbaccess/source/ui/dlg/directsql.cxx:
230 if (_rStatement.toAsciiUpperCase().startsWith("SELECT"))
231 {
232     css::uno::Reference< css::sdbc::XResultSet > xRS = xStatement->executeQuery(_rStatement);
233     if (m_xShowOutput->get_active())
234         display(xRS);
235     }
236     else
237     {
238         sal_Int32 resultCount = xStatement->executeUpdate(_rStatement);
239         addOutputText(OUString(OUString::number(resultCount) + " rows updated\n"));
240     }
See https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/dlg/directsql.cxx?r=76f89b00#230

The pb isn't about "Run SQL command directly" checkbox. It's the fact that this dialog uses "executeUpdate" as soon as a command doesn't begin with "START".
Here it begins with:
"WITH RECURSIVE"

Perhaps we can consider these cases:
- UPDATE -> executeUpdate
- INSERT INTO -> executeUpdate
- CREATE TABLE -> execute
- anything else -> executeQuery

Here's a patch:
diff --git a/dbaccess/source/ui/dlg/directsql.cxx b/dbaccess/source/ui/dlg/directsql.cxx
index e6828ae2aa3c..0d5c00248e85 100644
--- a/dbaccess/source/ui/dlg/directsql.cxx
+++ b/dbaccess/source/ui/dlg/directsql.cxx
@@ -227,17 +227,27 @@ namespace dbaui
             }
             else
             {
-                if (_rStatement.toAsciiUpperCase().startsWith("SELECT"))
-                {
-                    css::uno::Reference< css::sdbc::XResultSet > xRS = xStatement->executeQuery(_rStatement);
-                    if (m_xShowOutput->get_active())
-                        display(xRS);
-                }
-                else
+                if (_rStatement.toAsciiUpperCase().startsWith("UPDATE"))
                 {
                     sal_Int32 resultCount = xStatement->executeUpdate(_rStatement);
                     addOutputText(OUString(OUString::number(resultCount) + " rows updated\n"));
+                };
+                if (_rStatement.toAsciiUpperCase().startsWith("INSERT"))
+                {
+                    sal_Int32 resultCount = xStatement->executeUpdate(_rStatement);
+                    addOutputText(OUString(OUString::number(resultCount) + " rows inserted\n"));
+                };
+                if (_rStatement.toAsciiUpperCase().startsWith("CREATE"))
+                {
+                    xStatement->execute(_rStatement);
+                    addOutputText(u"Command executed\n");
                 }
+                else
+                {
+                    css::uno::Reference< css::sdbc::XResultSet > xRS = xStatement->executeQuery(_rStatement);
+                    if (m_xShowOutput->get_active())
+                        display(xRS);
+                };
             }
             // successful
             sStatus = DBA_RES(STR_COMMAND_EXECUTED_SUCCESSFULLY);

Of course, we should make the strings translatable but it's another point.

Lionel: any thoughts here?

Argh, there's at least DELETE statement missing in my previous comment...
The more I think about it, the more it seems we should use an SQL parser here.
Now I won't be able to plumber this dialog to the existing SQL parser we must have.

There's a Direct SQL scope discussion that needs to be had here. User should be able to run a SQL script that returns the result as a view (ie query table grid). Tools → SQL doesn't do that. I'd expect it in Query → Direct SQL (with one select).

==========

Another test case.

SQL code:

CREATE TABLE datetime_int (d1 int);

INSERT INTO datetime_int (d1)
VALUES(strftime('%s','now'));

SELECT d1 FROM datetime_int;

SELECT datetime(d1,'unixepoch')
FROM datetime_int;


SQLite2 output:

E:\demo\gnucash_books>\sqlite\sqlite3
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE datetime_int (d1 int);
sqlite>
sqlite> INSERT INTO datetime_int (d1)
   ...> VALUES(strftime('%s','now'));
sqlite>
sqlite> SELECT d1 FROM datetime_int;
1632557102
sqlite>
sqlite> SELECT datetime(d1,'unixepoch')
   ...> FROM datetime_int;
2021-09-25 08:05:02


Expect 2 lines in Tools → SQL Output
1632557102
2021-09-25 08:05:02


Error in Query → Direct SQL is reasonable:
"The data content could not be loaded. only one SQL statement allowed"

(In reply to flywire from comment #6)
> User should be able to run a SQL script that returns the result as a view
> (ie query table grid).

Creating a query, in the left part of the screen, "query" and then "new" does that.

> Tools → SQL doesn't do that.

Doesn't it do that if you check the "show results" box?

(In reply to Julien Nabet from comment #5)
> The more I think about it, the more it seems we should use an SQL parser
> here.

That's a possibility, but it will stay a heuristic; it will never cover all dialects of SQL. Another direction altogether would be to just ask the user to choose between "execute as Query (return rows)" and "execute as Update or DDL (return update count)" or (see below) "automatic"; if the SDBC driver doesn't support automatic mode, grey out the automatic choice and "force" the user to tell us.

If the SDBC drivers were all "perfect" and "fully able", we would just unconditionally use "execute" and then use
 com::sun::star::sdbc::XMultipleResults::getResultSet()
 com::sun::star::sdbc::XMultipleResults::getUpdateCount()
to retrieve the result, and
 com::sun::star::sdbc::XMultipleResults::getMoreResults()
to retrieve the next result; this has the *driver* (the database system?) tell us whether that was a query, and update, or both at the same time.

My memory tells me I already have changed the Tools->SQL code to do just that, if/when the underlying SDBC driver supports com::sun::star::sdbc::XMultipleResults
and indicates so in
com::sun::star::sdbc::XDatabaseMetaData::supportsMultipleResultSets

Anybody wanting to enhance the heuristic used when the driver is not feature-complete (no XMultipleResults or supportsMultipleResultSets returns false) is welcom to, but IMO the best / most robust results is getting the SDBC drivers to support that interface.

Created attachment 175266 [details]
Tools to SQL Window

(In reply to Lionel Elie Mamane from comment #7)
> > Tools → SQL doesn't do that.
> 
> Doesn't it do that if you check the "show results" box?
No, see image just loaded,

Re Lionel's other comments - interesting. Tools → SQL doesn't really have much to do with Base so maybe it could just be a shell, possibly a macro on a button. Query → Direct SQL has to return results into a view (query grid).

I gave a try with https://gerrit.libreoffice.org/c/core/+/122608
I just used the patch I quoted + took into account DELETE statement so it corresponds to the quick workaround compared to the Lionel's solution.

ODBC part seems to implement com::sun::star::sdbc::XMultipleResults but the pb is  Tools/SQL could be used with any database type.

(In reply to Julien Nabet from comment #10)
> ODBC part seems to implement com::sun::star::sdbc::XMultipleResults but the
> pb is  Tools/SQL could be used with any database type.

Then the affected ODBC driver doesn't return Y for
  SQLGetInfo(..., SQL_MULT_RESULT_SETS, ...)
which makes the Tools->SQL code use the heuristic path instead of the "ask the driver part".

Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/1c1b2b2f8d2abd6d179128b2fc4cb40c490566eb

tdf#144694: improve Direct SQL dialog command type heuristics

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.

Patch waiting for review in 7.2:
https://gerrit.libreoffice.org/c/core/+/122632

Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-7-2":

https://git.libreoffice.org/core/commit/638bdd87fc19377b157c82feb073be22c89d32de

tdf#144694: improve Direct SQL dialog command type heuristics

It will be available in 7.2.3.

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.

Created attachment 175318 [details]
Execute SQL Statement V2

Patch results in no obvious output change to either section:
1. Tools → SQL
2. Run code through Query → Direct SQL

I'd expect both option boxes to be selected by default and any change retained.

Ilmari Lauhakangas committed a patch related to this issue.
It has been pushed to "master":

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

tdf#144694 In direct SQL dialog, activate options 'Run SQL command

It will be available in 25.8.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.

Ilmari Lauhakangas committed a patch related to this issue.
It has been pushed to "libreoffice-25-2":

https://git.libreoffice.org/core/commit/215ce86bc1148ee231f92ce829a0a803ec4e6dc0

tdf#144694 In direct SQL dialog, activate options 'Run SQL command

It will be available in 25.2.0.0.beta2.

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.

(In reply to flywire from comment #15)
> Created attachment 175318 [details]
> Execute SQL Statement V2
> 
> Patch results in no obvious output change to either section:
> 1. Tools → SQL
> 2. Run code through Query → Direct SQL
> 
> I'd expect both option boxes to be selected by default and any change
> retained.

Option changes are retained during the session after the commit related to bug 140298.

> *query.sql*

Generating Syntax error in SQL statement

Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: f27d2d76c4e577c7231ae755548a8fc20711ca48
CPU threads: 6; OS: Windows 11 X86_64 (build 22631); UI render: Skia/Vulkan; VCL: win
Locale: en-AU (en_AU); UI: en-GB
Calc: CL threaded

(In reply to flywire from comment #19)
> > *query.sql*
> 
> Generating Syntax error in SQL statement
> 
> Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community
> Build ID: f27d2d76c4e577c7231ae755548a8fc20711ca48
> CPU threads: 6; OS: Windows 11 X86_64 (build 22631); UI render: Skia/Vulkan;
> VCL: win
> Locale: en-AU (en_AU); UI: en-GB
> Calc: CL threaded

Maybe that's a new regression or something? In any case, best to create a new report for it as this has already so many comments and commits.

(In reply to Buovjaga from comment #20)
> (In reply to flywire from comment #19)
> > > *query.sql*
> > 
> > Generating Syntax error in SQL statement
> > 
> > Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community
> > Build ID: f27d2d76c4e577c7231ae755548a8fc20711ca48
> > CPU threads: 6; OS: Windows 11 X86_64 (build 22631); UI render: Skia/Vulkan;
> > VCL: win
> > Locale: en-AU (en_AU); UI: en-GB
> > Calc: CL threaded
> 
> Maybe that's a new regression or something? In any case, best to create a
> new report for it as this has already so many comments and commits.

Just be clear, This *IS* the bug that does not work:

(In reply to flywire from comment #0)
> Direct SQL should process code the same as running it directly for both
> Tools → SQL and Query → Direct SQL.
> 
> The only real difference between these commands should be the second one
> holds the output in a view (query grid).
> 
> (In reply to Robert Großkopf from
> https://bugs.documentfoundation.org/show_bug.cgi?id=143656#c18)
> > (In reply to flywire from comment #17)
> 
> Your findings are consistent with mine:
> 
> > Empty SQLite database:
> > 1. Output is never generated by Tools → SQL
> > 2. Run code through Query → Direct SQL and table is generated first time [but returns error "The data content could not be loaded. The execution of the query doesn't return a valid result set."], 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
> 
> 
> Version: 7.3.0.0.alpha0+ (x64) / LibreOffice Community
> Build ID: e9b674a768fcf534335f172664aaf13dc2c79023
> CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL:
> win
> Locale: en-AU (en_AU); UI: en-GB
> Calc: threaded

Then let's make it clearer by improving the summary!!

Created attachment 198125 [details]
Base Direct SQL test code

Testing with https://www.sqlite.org/lang_with.html#controlling_depth_first_versus_breadth_first_search_of_a_tree_using_order_by

See But if we change the ORDER BY clause to add the "DESC" modifier, that will cause lower levels in the organization (with larger "level" values) to be processed first by the recursive-select, resulting in a depth-first search:

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;
The output of this revised query is:

Alice
...Bob
......Dave
......Emma
...Cindy
......Fred
......Gail

(In reply to Commit Notification from comment #17)
> Ilmari Lauhakangas committed a patch related to this issue.
> 
> tdf#144694 In direct SQL dialog, activate options 'Run SQL command

Tools → SQL Brings up the Execute SQL Statement dialog. (I note dialog width cannot be reduced now.)
Select:
 1. Run SQL command directly
 2. Show output of "select" statements
Paste *query.sql* test code
Execute

@Ilmari Lauhakangas Can you include a screenshot showing the output pane?

When I Execute the Output pane only contains the text "Command executed", despite selecting Show output of "select" statements. 

Expected Output pane:

Alice
...Bob
......Dave
......Emma
...Cindy
......Fred
......Gail


PART 2

This fix seems to have broken Create Query in SQL View but it would run as two queries (Seems a different interface demonstrates the same issue differently).

Select and Paste *query.sql* test code
Save generates Error - Syntax error in SQL statement
Use tool to set Run SQL command directly
Run Query generates Error

On first  run - The data content could not be loaded, The execution of the query does not return a valid result set.
Refresh tables and the org table has been created.

On second run - The data content could not be loaded, table org already exists (1).
Delete everything above WITH RECURSIVE and the query can be saved as query2.sql then Run query and the expected output is generated.

Test Environment:

Run https://wiki.documentfoundation.org/Documentation/HowTo/Base/Connect_to_SQLite if required, LibreOfficeDev Base, Connect to existing database: ODBC, Choose a data source, create new database, then as described above.

Version: 25.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: da56d1bd13e0396ca04b58d9e09bc9ad6315a869
CPU threads: 6; OS: Windows 11 X86_64 (build 22631); UI render: Skia/Vulkan; VCL: win
Locale: en-AU (en_AU); UI: en-GB
Calc: CL threaded

> it would run as two queries

Confirmed. Expected output is generated by both Tools → SQL and Query → Direct SQL if the SQL uses different queries for CREATE TABLE and WITH RECURSIVE clauses. The whole expression should be executed as a single expression.

(In reply to flywire from comment #25)
> > it would run as two queries
> 
> Confirmed. Expected output is generated by both Tools → SQL and Query →
> Direct SQL if the SQL uses different queries for CREATE TABLE and WITH
> RECURSIVE clauses. The whole expression should be executed as a single
> expression.

Previously reported: https://bugs.documentfoundation.org/show_bug.cgi?id=143656#c18

The whole process of fake fixes is a waste of everybody's time.

== The preparation ==

Set up SQLite3 based on https://wiki.documentfoundation.org/Documentation/HowTo/Base/Connect_to_SQLite

On Linux, for ODBC, use instructions for your distro. I used https://wiki.archlinux.org/title/Open_Database_Connectivity

I installed unixodbc from Arch repos.

I installed sqliteodbc from AUR.

I created an empty test database with `> test.db` (`touch test.db` also works).

I created an /etc/odbc.ini referencing the test database.

[ODBC Data Sources]
tdf144694            = SQLite3 Driver

[tdf144694]
Driver      = /usr/lib/libsqlite3odbc.so
Description = tdf144694
database    = /path/to/test.db

Then I followed the steps from the TDF wiki article.

In Base, select database - Connect to an existing database: ODBC
Set up ODBC connection - Browse and Choose a data source, tdf144694
Set up user authentication - Test connection
Save and proceed to create database

== The testing itself ==

The problematic query that you should copy is in the text file attachment 198125 [details]. The query first creates a table called org and inserts data into it. Then it immediately selects some data.

No errors are shown, if you execute it via Tools - SQL (both checkboxes should be checked, run directly and show output). This does not show the result of the select in the Output field, though. The result is only shown, if you execute the creation of the table and the select (starting with WITH RECURSIVE) separately.

The error "The data content could not be loaded. The execution of the query doesn't return a valid result set" is shown with these steps:

1. Go to the Queries view in Base, single-click "Create Query in SQL View"
2. Activate "Run SQL command directly" from the right side of the toolbar
3. Paste in the full query from attachment 198125 [details] and click Save, give it a name, close the query window
4. Double-click the name of the query you created to run it

Now you should see the error. The table itself has been created. The result is shown fine, if you split the query into the creation and select steps as mentioned before.

When testing, the table can be deleted by executing this command in the SQL dialog:
DROP TABLE org;

(the status will be "1: The execution of the query does not return a valid result set.", which is fine in this case)

It is more convenient to drop the table than delete the .db file and create a blank one because in that case the connection in an existing Base file will no longer work and you would have to create a new Base file.

You can always check the state via the command line with
sqlite3 test.db

and saying

.tables

It will print "org", if the table exists or nothing, if it doesn't.

Use .quit to exit.

Side notes (I should report these separately): on Linux with kf6 UI, the error dialog can't be closed - the application has to be forcefully closed. Best to run from the command line to make it easy with Ctrl+C, then.

With any UI, using Tools - SQL to run the query, closing it and opening it again, the dialog width will be huge. Even twice the screen width with gtk3.

With some quick debugging, the exception "The data content could not be loaded" when running from Queries originates from ODatabaseForm::executeRowSet() in forms/source/component/DatabaseForm.cxx

This is where it executes onError():
catch(const SQLException& eDb)
{
    _rClearForNotifies.clear();
    if (!m_sCurrentErrorContext.isEmpty())
        onError(eDb, m_sCurrentErrorContext);

(In reply to Buovjaga from comment #27)
> Side notes (I should report these separately): on Linux with kf6 UI, the
> error dialog can't be closed - the application has to be forcefully closed.
> Best to run from the command line to make it easy with Ctrl+C, then.

Reported and bibisected as bug 164353

> With any UI, using Tools - SQL to run the query, closing it and opening it
> again, the dialog width will be huge. Even twice the screen width with gtk3.

I decided to mention this to Heiko in bug 140298 comment 8