Bug 153003 - Copying a query and pasting as table in same database impossible
Summary: Copying a query and pasting as table in same database impossible
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
7.4.4.2 release
Hardware: All All
: high major
Assignee: Julien Nabet
URL:
Whiteboard: target:7.6.0 target:7.4.6 target:7.5....
Keywords: bibisectRequest, dataLoss, regression
Depends on:
Blocks:
 
Reported: 2023-01-13 07:52 UTC by Robert Großkopf
Modified: 2023-01-24 21:37 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Database for testing: Copy the query and try to insert as new table - error and no content copied (3.76 KB, application/vnd.oasis.opendocument.database)
2023-01-13 07:52 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2023-01-13 07:52:17 UTC
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.
Comment 1 Robert Großkopf 2023-01-13 08:05:15 UTC
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.
Comment 2 Alex Thurgood 2023-01-13 10:55:58 UTC
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.
Comment 3 Julien Nabet 2023-01-13 21:10:26 UTC
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"
Comment 4 Julien Nabet 2023-01-13 21:17:26 UTC
(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.
Comment 5 Julien Nabet 2023-01-13 21:18:54 UTC
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>;
Comment 6 Julien Nabet 2023-01-13 21:23:26 UTC
(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".
Comment 7 Commit Notification 2023-01-13 22:33:06 UTC
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.
Comment 8 Julien Nabet 2023-01-13 22:34:19 UTC
cherry-picks waiting for review:
7.5: https://gerrit.libreoffice.org/c/core/+/145440
7.4: https://gerrit.libreoffice.org/c/core/+/145441
Comment 9 Robert Großkopf 2023-01-14 06:59:55 UTC
(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.
Comment 10 Commit Notification 2023-01-16 08:44:37 UTC
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.
Comment 11 Commit Notification 2023-01-16 08:44:40 UTC
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.
Comment 12 Xisco Faulí 2023-01-24 10:36:20 UTC
7.4.5 was a hotfix release, updating target in status-whiteboard
Comment 13 Commit Notification 2023-01-24 21:37:19 UTC
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.