Open any *.odb-file connected to MariaDB with direct connection. There should be 2 tables, which could be connected by a query. Connect this 2 tables in a query. Query is write protected every time you create a query. Now change to edit SQL. You have to delete databasename and alias tablename and the query will be work well. If name of database is the same for all tables in a query editing should be allowed like for internal HSQLDB, internal Firebird or, for example, MariaDB with JDBC-connection. MariaDB for JDBC connection also allows the Alias for a combination of database.table and query could be filled with data… Bug appears in LO 7.6.2.1, but also in all other versions I could test since we have included direct connection for MariaDB.
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.