Created attachment 184626 [details] Database for testing: Copy the query and try to insert as new table - error and no content copied Open the attached database file. Switch to Queries, right mouse click on qry_Name → Copy Switch to Tables, right mouse click under tbl_Name (hint: If tbl_Name has been marked you would run in a already posted bug of the wizard) There will appear a dialog "Copy table". Table name is "qry_Name". Change it to "qry_Name1" Options → Definitions and data is chosen. Type 'Next' and take all existing columns from left to right. Type 'Next and right mouse click on "ID". Chose "Primary key". Type 'Create'. An Error will appear. Type 'More' and the error details will give: SQL Status: 07009 Internal error: invalid column type index. at /home/buildslave/source/libo-core/connectivity/source/commontools/dbexception.cxx:462 Type 'OK' and try to continue ('Yes'). You have to type it two times (for every row of the query). Table has been created without any content. There is something going wrong with content of the columns of the query while trying to insert. You could see this when trying to insert the content in "tbl_Auto". Copy query → Tables → mark tbl_Auto → right mouse click Insert Option "append data" will be chosen → 'Next' → deselect "ID" in source Table 'continue' and the content will be pasted. But if you open the table tbl_Auto the content form field "ID" will have been pasted to the field "Name". This bug appears in LO 7.4.4.2, doesn't appear in LO 7.1.5.2 on OpenSUSE 15.3 64bit rpm Linux. So it's a regression.
This bug doesn't appear in LO 7.3.6.2 Only the known error SQL Status: S1000 Error code: -73 This operation is not supported in statement [ALTER TABLE "tbl_Auto1" ALTER "ID" RESTART WITH 3] /home/buildslave/source/libo-core/connectivity/source/drivers/jdbc/Object.cxx:175 appears, because the column "ID" isn't set as Auto Value and the wizard tries to set the start of an auto value. Fields will be created in new table, content will be inserted. LO 7.4.0.3: Same behavior as LO 7.3.6.2 LO 7.4.2.3: Same behavior as LO 7.3.6.2 LO 7.4.3.2: Same behavior as LO 7.3.6.2 Bug appears first in LO 7.4.4.2, also LO 7.5.0.1 here on OpenSUSE 15.3 64bit rpm Linux.
Confirming with Version: 7.4.4.2 / LibreOffice Community Build ID: 85569322deea74ec9134968a29af2df5663baa21 CPU threads: 8; OS: Mac OS X 13.0.1; UI render: Skia/Raster; VCL: osx Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR Calc: threaded Data is lost on paste.
I've submitted this first patch: https://gerrit.libreoffice.org/c/core/+/145499 but then I've got: warn:dbaccess:165069:165069:dbaccess/source/ui/uno/copytablewizard.cxx:1421: com.sun.star.sdbc.SQLException message: "This operation is not supported in statement [ALTER TABLE "qry_Name1" ALTER "ID" RESTART WITH 3] at /home/julien/lo/libreoffice/connectivity/source/drivers/jdbc/Object.cxx:172" SQLState: S1000 ErrorCode: -73 wrapped: void message: "at /home/julien/lo/libreoffice/comphelper/source/misc/diagnose_ex.cxx:66"
(In reply to Julien Nabet from comment #3) > I've submitted this first patch: > https://gerrit.libreoffice.org/c/core/+/145499 > > but then I've got: > warn:dbaccess:165069:165069:dbaccess/source/ui/uno/copytablewizard.cxx:1421: > com.sun.star.sdbc.SQLException message: "This operation is not supported in > statement [ALTER TABLE "qry_Name1" ALTER "ID" RESTART WITH 3] at > /home/julien/lo/libreoffice/connectivity/source/drivers/jdbc/Object.cxx:172" > SQLState: S1000 ErrorCode: -73 > wrapped: void message: "at > /home/julien/lo/libreoffice/comphelper/source/misc/diagnose_ex.cxx:66" It seems due to the fact that "ID" field in "tbl_Name' is not an IDENTITY column. To change this, you must set "autoincrement" to "yes" and then it works.
Oups, I forgot the ref https://www.hsqldb.org/doc/1.8/guide/guide.html " Identity Auto-Increment Columns Each table can contain one auto-increment column, known as the IDENTITY column. An IDENTITY column is always treated as the primary key for the table (as a result, multi-column primary keys are not possible with an IDENTITY column present). Support has been added for CREATE TABLE <tablename>(<colname> IDENTITY, ...) as a shortcut. Since 1.7.2, the SQL standard syntax is used by default, which allows the initial value to be specified. The supported form is(<colname> INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH n, [INCREMENT BY m])PRIMARY KEY, ...). Support has also been added for BIGINT identity columns. As a result, an IDENTITY column is simply an INTEGER or BIGINT column with its default value generated by a sequence generator. When you add a new row to such a table using an INSERT INTO <tablename> ...; statement, you can use the NULL value for the IDENTITY column, which results in an auto-generated value for the column. The IDENTITY() function returns the last value inserted into any IDENTITY column by this connection. Use CALL IDENTITY(); as an SQL statement to retrieve this value. If you want to use the value for a field in a child table, you can use INSERT INTO <childtable> VALUES (...,IDENTITY(),...);. Both types of call to IDENTITY() must be made before any additional update or insert statements are issued on the database. The next IDENTITY value to be used can be set with the ALTER TABLE ALTER COLUMN <column name> RESTART WITH <new value>; " Notice the last line should be: ALTER TABLE <table name> ALTER COLUMN <column name> RESTART WITH <new value>;
(In reply to Julien Nabet from comment #4) > ... > It seems due to the fact that "ID" field in "tbl_Name' is not an IDENTITY > column. To change this, you must set "autoincrement" to "yes" and then it > works. To be more precise, UI tells "AutoValue" not "autoincrement".
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/c9b0eacae71f111209cb77f14f2693d73e5e8d90 Related tdf#153003: Copying a query and pasting as table in same DB impossible It will be available in 7.6.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.
cherry-picks waiting for review: 7.5: https://gerrit.libreoffice.org/c/core/+/145440 7.4: https://gerrit.libreoffice.org/c/core/+/145441
(In reply to Julien Nabet from comment #3) > I've submitted this first patch: > https://gerrit.libreoffice.org/c/core/+/145499 > > but then I've got: > warn:dbaccess:165069:165069:dbaccess/source/ui/uno/copytablewizard.cxx:1421: > com.sun.star.sdbc.SQLException message: "This operation is not supported in > statement [ALTER TABLE "qry_Name1" ALTER "ID" RESTART WITH 3] at > /home/julien/lo/libreoffice/connectivity/source/drivers/jdbc/Object.cxx:172" > SQLState: S1000 ErrorCode: -73 > wrapped: void message: "at > /home/julien/lo/libreoffice/comphelper/source/misc/diagnose_ex.cxx:66" Patch would solve this bug. The error you see is bug 153004, which first appears in LO 7.2.0.3.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/db8a4526ff64d6fbdd39ccbb1f18412801225533 Related tdf#153003: Copying a query and pasting as table in same DB impossible It will be available in 7.4.5. 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.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/aaa072e736a45be6ca942abd20be19935334124f Related tdf#153003: Copying a query and pasting as table in same DB impossible It will be available in 7.5.1. 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.
7.4.5 was a hotfix release, updating target in status-whiteboard
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-7-5-0": https://git.libreoffice.org/core/commit/f5ef2945d1af500c80d831ee09c1c34c766b0a54 Related tdf#153003: Copying a query and pasting as table in same DB impossible It will be available in 7.5.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.