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.