Bug 157916

I can confirm this with a query on a MySQL 8.0.27 (MySQL Community Server - GPL) instance such as the one below:

SELECT * from ipgalore as t1 LEFT OUTER JOIN (priority as t2,owner_list as t3,inventor_list as t4) ON (t2.fmpro_id = t1.fmpro_id AND t3.fmpro_id = t1.fmpro_id AND t4.fmpro_id = t1.fmpro_id)

The "fmpro_id" field here is the primary key for each table of the join and the query joins 4 tables in total.

The resulting query grid view data is non-writable.

However, this is very old behaviour, as I've had this query for quite a long time and it was always non-writable from LibreOffice. I don't ever recall it being writable.

If this is the kind of query you are talking about, then I can confirm the bug report.

(In reply to Alex Thurgood from comment #1)

> If this is the kind of query you are talking about, then I can confirm the
> bug report.


Note that this query wasn't created through the GUI, but via direct SQL execution when defining the query, so perhaps not quite what you are reporting ?

(In reply to Alex Thurgood from comment #1)
> 
> SELECT * from ipgalore as t1 LEFT OUTER JOIN (priority as t2,owner_list as
> t3,inventor_list as t4) ON (t2.fmpro_id = t1.fmpro_id AND t3.fmpro_id =
> t1.fmpro_id AND t4.fmpro_id = t1.fmpro_id)
> 
> If this is the kind of query you are talking about, then I can confirm the
> bug report.

This query won't be editable in any database connected to Base. It is missing the primary key of second table.

Should be something like

SELECT `table1`.*, `table2`.* FROM `database`.`table1` `table1`, `database`.`table2` `table2` WHERE `table1`.`ID` = `table2`.`ForeignkeyID`

Such a query will be created for 2 tables where second table has a foreignkey for the first table. All fields of both tables will be shown.

If you change the query to 

SELECT `table1`.*, `table2`.* FROM `table1`, `table2` WHERE `table1`.`ID` = `table2`.`ForeignkeyID`

you could input data into this query.

On pc Debian x86-64 with master sources updated today, I could reproduce this.

I noticed this on console:
warn:connectivity.parse:24957:24957:connectivity/source/parse/sqliterator.cxx:2025: Adding error com.sun.star.sdbc.SQLException message: "The database does contain neither a table nor a query named "Commandes". at /home/julien/lo/libreoffice/connectivity/source/parse/sqliterator.cxx:2018" SQLState: HY000 ErrorCode: 1000
    wrapped: void message: "at /home/julien/lo/libreoffice/comphelper/source/misc/diagnose_ex.cxx:66"

Lionel: after some debugging, I found that in OSQLParseTreeIterator::impl_locateRecordSource, LO searches the full table name and not just the table name.
I tried this locally and it worked:
diff --git a/connectivity/source/parse/sqliterator.cxx b/connectivity/source/parse/sqliterator.cxx
index a89d631e256b..a42fbadf420e 100644
--- a/connectivity/source/parse/sqliterator.cxx
+++ b/connectivity/source/parse/sqliterator.cxx
@@ -369,8 +369,10 @@ OSQLTable OSQLParseTreeIterator::impl_locateRecordSource( const OUString& _rComp
 
         // check whether the table container contains an object with the given name
         if ( !bQueryDoesExist && !m_pImpl->m_xTableContainer->hasByName( sComposedName ) )
+        {
             sComposedName = lcl_findTableInMetaData( m_pImpl->m_xDatabaseMetaData, sCatalog, sSchema, sName );
-        bool bTableDoesExist = m_pImpl->m_xTableContainer->hasByName( sComposedName );
+        }
+        bool bTableDoesExist = (m_pImpl->m_xTableContainer->hasByName( sComposedName ) || m_pImpl->m_xTableContainer->hasByName( sName ));
 
         // now obtain the object
 
@@ -404,7 +406,12 @@ OSQLTable OSQLParseTreeIterator::impl_locateRecordSource( const OUString& _rComp
                 impl_getQueryParameterColumns( aReturn );
             }
             else if ( bTableDoesExist )
-                m_pImpl->m_xTableContainer->getByName( sComposedName ) >>= aReturn;
+            {
+                if (m_pImpl->m_xTableContainer->hasByName( sComposedName ))
+                    m_pImpl->m_xTableContainer->getByName( sComposedName ) >>= aReturn;
+                else
+                    m_pImpl->m_xTableContainer->getByName( sName ) >>= aReturn;
+            }
             else
             {
                 if ( m_pImpl->m_xQueryContainer.is() )

Any thoughts here? I mean, is it an ugly hack and you got in mind an hint for a a cleaner fix or does it seem reasonable?

Hi,

the word wrapping makes it a bit hard to read patches inside comments. When you want to discuss a patch in bugzilla,  upload it as an attachment, and check the "patch" box. Thanks.

do I understand correctly that sName is just the table's name (not necessarily unique) but sComposedName is the full schemaName.tableName? Then I think it would be best to fix the MariaDB SDBC driver so that m_pImpl->m_xTableContainer is populated by ComposedName rather than by just tablename, like the other SDBC drivers seem to do.

Else I think we will have problems when the same tableName happens in several schemas, which is allowed.

(In reply to Lionel Elie Mamane from comment #5)
> Hi,
> 
> the word wrapping makes it a bit hard to read patches inside comments. When
> you want to discuss a patch in bugzilla,  upload it as an attachment, and
> check the "patch" box. Thanks.
Ok I wanted to avoid to submit a patch on gerrit and abandon it afterwards if it's wrong and don't know how to keep on.

> 
> do I understand correctly that sName is just the table's name (not
> necessarily unique) but sComposedName is the full schemaName.tableName? Then
> I think it would be best to fix the MariaDB SDBC driver so that
> m_pImpl->m_xTableContainer is populated by ComposedName rather than by just
> tablename, like the other SDBC drivers seem to do.
> 
Yes it's exactly the pb sComposedName is schemaName.tableName (and perhaps for another database, it would be catalogName.schemaName.tableName).

I'm gonna search about m_pImpl->m_xTableContainer population, hope I won't got lost :-)

> 
> Else I think we will have problems when the same tableName happens in
> several schemas, which is allowed.
Indeed

After having spent several hours, I give up.
Each time I thought I was on the right way, I finally looped on an already encountered method.
=> uncc myself.