Created attachment 138737 [details] example database STR (1) Download and open attached a02.fdb. It is an embedded Firebird database with an empty table. (2) In Database pane click <Tables>. (3) In Tables pane, right-click Table1. (4) In pop-up menu select Copy. (5) Right-click on background of Tables pane. (6) In pop-up menu, right-click Paste. (7) In dialog "Copy table" click button <Next>>. (8) In dialog "Apply columns", click button ">>". and then click button <Next>>. (9) In dialog "Type formatting", click button <Create>. Observe unexpected terminal message (first line rewrapped): warn:connectivity.firebird:12371:1: connectivity/source/drivers/firebird/Util.cxx:52: firebird_sdbc error: *Dynamic SQL Error *SQL error code = -104 *Token unknown - line 1, column 46 *( caused by 'isc_dsql_prepare' A backtrace reveals that the SQL statement is: INSERT INTO "Table12" ( "cardinal","words" ) ( SELECT "cardinal", "words" FROM "Table1" ) Note that Table1 is empty. When Table1 has data, there is an additional terminal message: warn:connectivity.firebird:12526:1: connectivity/source/drivers/firebird/Util.cxx:52: firebird_sdbc error: *Attempt to reclose a closed cursor caused by 'isc_dsql_free_statement: close cursor' and message box: LibreOfficeDev Base An error occurred. Do you want to contiue copying? <Yes> <No> <More> These observations are from debian-buster and daily Linux dbgutil bibisect repository version 2017-12-29.
Couldn't confirm with Version: 6.0.0.1 Build-ID: d2bec56d7865f05a1003dc88449f2b0fdd85309a Also couldn't confirm with Version: 5.4.4.2 Build-ID: 2524958677847fb3bb44820e40380acbe820f960 Both tested on OpenSUSE 42.2 64bit rpm Linux. Downloaded daily build Version: 6.0.0.1.0+ Build ID: 4393ced78656c083b01b66d8987ff956e90b39ab CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: kde4; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-0, Time: 2017-12-30_00:57:19 Locale: de-DE (de_DE.UTF-8); Calc: group Also no error. Table has been pasted. Only thing I recognized: Pasting of the table is very slow.
On pc Debian x86-64 with master sources updated yesterday, I see the error messages in console but the table is copied.
Let's put this one to NEW since it's been reproduced on tdf#114711
I retrieved a backtrace from location indicated by console logs and got this: #0 0x00007fffc4b9610e in connectivity::firebird::StatusVectorToString(long const (&) [20], rtl::OUString const&) (rStatusVector=..., rCause="isc_dsql_prepare") at /home/julien/lo/libreoffice/connectivity/source/drivers/firebird/Util.cxx:54 #1 0x00007fffc4b962fb in connectivity::firebird::evaluateStatusVector(long const (&) [20], rtl::OUString const&, com::sun::star::uno::Reference<com::sun::star::uno::XInterface> const&) (rStatusVector=..., rCause="isc_dsql_prepare", _rxContext=uno::Reference to (connectivity::firebird::OStatement *) 0x5555580dea40) at /home/julien/lo/libreoffice/connectivity/source/drivers/firebird/Util.cxx:64 #2 0x00007fffc4b8a5c6 in connectivity::firebird::OStatementCommonBase::prepareAndDescribeStatement(rtl::OUString const&, XSQLDA*&, XSQLDA*) (this=0x5555580dea40, sql="INSERT INTO \"Table12\" ( \"cardinal\",\"words\" ) ( SELECT \"cardinal\", \"words\" FROM \"Table1\" )", pOutSqlda=@0x5555580debe0: 0x5555580dcfa0, pInSqlda=0x0) at /home/julien/lo/libreoffice/connectivity/source/drivers/firebird/StatementCommonBase.cxx:163 #3 0x00007fffc4b88f46 in connectivity::firebird::OStatement::executeQuery(rtl::OUString const&) (this=0x5555580dea40, sql="INSERT INTO \"Table12\" ( \"cardinal\",\"words\" ) ( SELECT \"cardinal\", \"words\" FROM \"Table1\" )") at /home/julien/lo/libreoffice/connectivity/source/drivers/firebird/Statement.cxx:112 #4 0x00007fffc4b892bb in connectivity::firebird::OStatement::execute(rtl::OUString const&) (this=0x5555580dea40, sql="INSERT INTO \"Table12\" ( \"cardinal\",\"words\" ) ( SELECT \"cardinal\", \"words\" FROM \"Table1\" )") at /home/julien/lo/libreoffice/connectivity/source/drivers/firebird/Statement.cxx:148 #5 0x00007fffc4b88dcc in connectivity::firebird::OStatement::executeUpdate(rtl::OUString const&) (this=0x5555580dea40, sql="INSERT INTO \"Table12\" ( \"cardinal\",\"words\" ) ( SELECT \"cardinal\", \"words\" FROM \"Table1\" )") at /home/julien/lo/libreoffice/connectivity/source/drivers/firebird/Statement.cxx:96 #6 0x00007fffcae09623 in OStatement::executeUpdate(rtl::OUString const&) (this=0x5555580ddff0, _rSQL="INSERT INTO \"Table12\" ( \"cardinal\",\"words\" ) ( SELECT \"cardinal\", \"words\" FROM \"Table1\" )") at /home/julien/lo/libreoffice/dbaccess/source/core/api/statement.cxx:478 #7 0x00007fffc835d28e in dbaui::CopyTableWizard::impl_doCopy_nothrow() (this=0x5555580a3cf0) at /home/julien/lo/libreoffice/dbaccess/source/ui/uno/copytablewizard.cxx:1368 The request is generated from CopyTableWizard::impl_getServerSideCopyStatement_throw 1442 const OUString sComposedTableName = ::dbtools::composeTableName( xDestMetaData, _xTable, ::dbtools::EComposeRule::InDataManipulation, true ); 1443 OUString sSql("INSERT INTO " + sComposedTableName + " ( " + sColumns.makeStringAndClear() + " ) ( " + m_pSourceObject->getSelectStatement() + " )"); see https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/uno/copytablewizard.cxx#1425 Lionel: here are 2 questions: 1) I haven't found SQL ref about INSERT INTO SELECT but when I read about it on to https://www.w3schools.com/sql/sql_insert_into_select.asp or on other websites, I never see parenthesis around SELECT block. Do you think it'd be ok to remove them? 2) Now about the table used for INSERT block, the sql can't succeed since the new table doesn't exist yet at this moment. However, if we use the same table as the SELECT block, it won't work as soon as there's primary/unique index. Any thoughts?
Julien, I accused the extra parentheses on the grounds that neither of https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-insert.html show parentheses around the embedded SELECT. I have verified via Tools > SQL that Firebird accepts the INSERT without the extra parentheses. That, BTW, is how bug 114702 came to be. <sigh />
(In reply to Julien Nabet from comment #4) > Lionel: here are 2 questions: > 1) I haven't found SQL ref about INSERT INTO SELECT (...) > I never see parenthesis around SELECT block. > Do you think it'd be ok to remove them? Yes, removing them should be fine. Both versions should work in most DBMSs (at least those that support subqueries). The version without parentheses is actually "more basic SQL", while the version with parentheses is a subquery. Just hoping it will not break another DBMS. > 2) Now about the table used for INSERT block, the sql can't succeed since > the new table doesn't exist yet at this moment. That's weird. The code should definitely first do the CREATE TABLE and then the INSERT.
The table creation should happen there https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/misc/WCopyTable.cxx#1233 when that function is called from https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/uno/copytablewizard.cxx#1324 If it doesn't, maybe the Firebird SDBC driver doesn't implement the necessary API calls? If that's the reason, that warrants a wishlist bug that blocks bug 51780.
(In reply to Terrence Enger from comment #5) > Julien, > > I accused the extra parentheses on the grounds that neither of > > https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25- > en/html/fblangref25-dml-select.html > > https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25- > en/html/fblangref25-dml-insert.html > show parentheses around the embedded SELECT. I have verified via > Tools > SQL that Firebird accepts the INSERT without the extra > parentheses. > > That, BTW, is how bug 114702 came to be. <sigh /> Yes but the content of dbaccess/source/ui/uno/copytablewizard.cxx must work for every DB not just for Firebird. Anyway, I also read the Lionel's comment and will propose a patch to just remove these parenthesis.
(In reply to Lionel Elie Mamane from comment #7) > The table creation should happen there > https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/misc/ > WCopyTable.cxx#1233 > when that function is called from > https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/uno/ > copytablewizard.cxx#1324 > > If it doesn't, maybe the Firebird SDBC driver doesn't implement the > necessary API calls? If that's the reason, that warrants a wishlist bug that > blocks bug 51780. Thank you for your detailed feedback Lionel. Dumb me, I was testing the sql retrieved from gdb, of course table couldn't exist. I've just tested after having removed parenthesis from SELECT block, I go no more error from Firebird in console. Just a lot of these: warn:legacy.osl:27488:27488:comphelper/source/property/propertycontainerhelper.cxx:186: OPropertyContainerHelper::implPushBackProperty: name already exists! warn:legacy.osl:27488:27488:comphelper/source/property/propertycontainerhelper.cxx:187: OPropertyContainerHelper::implPushBackProperty: handle already exists! and twice: warn:legacy.osl:27488:27488:cppuhelper/source/propshlp.cxx:990: Property array is not sorted but that's another story.
I submitted a patch to review here: https://gerrit.libreoffice.org/#/c/47218/1
Julien Nabet committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b3ea7ed747991696b9f02ede14c57f333c28a41b tdf#114755: Remove extra parenthesis It will be available in 6.1.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-6-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=2d1a732a2afabb6946d389f01dc491f0f3b6a571&h=libreoffice-6-0 tdf#114755: Remove extra parenthesis It will be available in 6.0.0.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Terrence: could you give a new try with an empty and a not empty table once there's a build including the patch? (knowing that it could also help for tdf#114711)
I observe the problem fixed in daily Linux dbgutil bibisect repository 2018-01-01. I am setting status VERIFIED FIXED. The program also copied a table with 4 rows without problem. So, tdf#114755 seems to be fixed as well. Thank you, Julien.