#15883: ExecuteEdit on UNIONed SELECT corrupts data

Michal reported 2022-01-04T11:08:30Z · last modified 2022-01-06T20:57:23Z

ExecuteEdit on UNIONed SELECT corrupts data

Dev	tomconrad
QA	Lwaugh

Priority	Major
Complexity	Unknown

Component	Tools - Table Data Editor
Version	Future

Hello,

I did union of two data sets (note that they are practically the same, down to the PK definition):

SELECT * FROM  tMeteoMessageMETAR WHERE issueDate >= '2022-01-04 00:00' or issueDate is null
UNION ALL
SELECT * FROM  tMeteoMessageSPECI WHERE issueDate >= '2022-01-04 00:00' or issueDate is null

And run it as Execute Edit since I needed to alter them manually. After doing my changes in just appeared new window, I hit Ctrl+S to actually do the changes.

To my great surprise a data originating from tMeteoMessageMETAR was changed, but the other ones were NOT. After some digging around and examining the generated SQL UPDATE statements, I found out, that they point to METAR table, despite being from SPECI table.

So in previous run, after hitting Ctrl + S wrong data were modified (confirmed by looking them up by PK). But since it modified exactly one row with each statement no error was risen by AquaDataStudio.

I hope this explanation is clear enough. If not I will be happy to provide more details.

Aqua Data Studio 20.0.4
Build #: 57271
Built on: 2019-Oct-21 09:27:07 AM

2 attachments

Union1.png

2022-01-05T22:03:25Z

176 KB
Union2.png

2022-01-05T22:03:40Z

192 KB

All Comments (6) Change History

tomconrad 2022-01-04T18:31:24Z

Hi Michal,

Which database are you using?

Thanks,

Tom

Michal 2022-01-05T13:00:54Z · (edited)

Hi Tom,

It is SAP ASE 16.0

Michal

SELECT @@version

column1

--------------------------------------------------------------------------------------------------------------------------------------

Adaptive Server Enterprise/16.0 SP02 PL06 HF1/EBF 27300 SMP/P/X64/Windows Server/ase160sp02pl06x/0/64-bit/FBO/Sat Jul 08 17:53:16 2017

tomconrad 2022-01-05T22:22:52Z

Hi Michal,

I think you found a bug in the ADS Table Data Editor. Given this test case, when I execute the query with the union, the result set is fed to the Table Data Editor as a complete result set(composite of both tables). If I modify the data from table t2, in the Table Data Editor, it is not modified in the correct table because that data is not found in table t1. All updates are only modified in table t1. In your case, since you have ambiguous data in both of your tables, some data is updated that should not be.

This needs to be investigated more to identify the root cause and see if we can fix in a patch. It seems like we would have to split the union and run each query of the union individually. The composite could be added to the Table Data Editor. This way, we could track updates by table. Another option is to disallow unions completely.

Will let you know what we come up with.

Thanks,

Tom

tomconrad 2022-01-05T22:24:13Z

Some test cases...

CREATE TABLE dbo.t1  ( 
   c1 int NULL,
   c2 datetime NULL,
   c3 varchar(25) NULL 
   )
GO
CREATE TABLE dbo.t2  ( 
   c1 int NULL,
   c2 datetime NULL,
   c3 varchar(25) NULL 
   )  
GO

DELETE FROM dbo.t1
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(1, '2022-01-04 12:01', 't1 One')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(2, '2022-01-04 12:02', 't1 Two')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(3, '2022-01-04 12:03', 't1 Three')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(4, '2022-01-04 12:04', 't1 Four')
GO
SELECT * FROM dbo.t1
GO
   
DELETE FROM dbo.t2
GO
INSERT INTO dbo.t2(c1, c2, c3) 
   VALUES(1, '2022-01-04 12:01', 't2 One')
GO
INSERT INTO dbo.t2(c1, c2, c3) 
   VALUES(2, '2022-01-04 12:02', 't2 Two')
GO
INSERT INTO dbo.t2(c1, c2, c3) 
   VALUES(3, '2022-01-04 12:03', 't2 Three')
GO
INSERT INTO dbo.t2(c1, c2, c3) 
   VALUES(4, '2022-01-04 12:04', 't2 Four')
GO
SELECT * FROM dbo.t2
GO

SELECT * FROM dbo.t1 WHERE c2 >= '2022-01-04 12:03' OR c2 is null
UNION ALL
SELECT * FROM dbo.t2 WHERE c2 >= '2022-01-04 12:03' OR c2 is null

//////////////////////////////////////

CREATE TABLE dbo.t1  ( 
   c1 int NULL,
   c2 datetime NULL,
   c3 varchar(25) NULL 
   )
GO
CREATE TABLE dbo.t2  ( 
   c11    int NULL,
   c22    datetime NULL,
   c33    varchar(25) NULL 
   )  
GO

DELETE FROM dbo.t1
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(1, '2022-01-04 12:01', 't1 One')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(2, '2022-01-04 12:02', 't1 Two')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(3, '2022-01-04 12:03', 't1 Three')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(4, '2022-01-04 12:04', 't1 Four')
GO
SELECT * FROM dbo.t1
GO
   
DELETE FROM dbo.t2
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(1, '2022-01-04 12:01', 't2 One')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(2, '2022-01-04 12:02', 't2 Two')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(3, '2022-01-04 12:03', 't2 Three')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(4, '2022-01-04 12:04', 't2 Four')
GO
SELECT * FROM dbo.t2
GO
SELECT * FROM dbo.t1 WHERE c2 >= '2022-01-04 12:03' OR c2 is null
UNION ALL
SELECT * FROM dbo.t2 WHERE c22 >= '2022-01-04 12:03' OR c22 is null
ORDER BY c1

//////////////////////////////////////

CREATE TABLE dbo.t1  ( 
   c1 int NULL,
   c2 datetime NULL,
   c3 varchar(25) NULL 
   )
GO
CREATE TABLE dbo.t2  ( 
   c11    int NULL,
   c22    datetime NULL,
   c33    varchar(25) NULL 
   )  
GO

DELETE FROM dbo.t1
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(1, '2022-01-04 12:01', 't1 One')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(2, '2022-01-04 12:02', 't1 Two')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(3, '2022-01-04 12:03', 't1 Three')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(4, '2022-01-04 12:04', 't1 Four')
GO
SELECT * FROM dbo.t1
GO
   
DELETE FROM dbo.t2
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(5, '2022-01-04 12:01', 't2 Five')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(6, '2022-01-04 12:02', 't2 Six')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(7, '2022-01-04 12:03', 't2 Seven')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(8, '2022-01-04 12:04', 't2 Eight')
GO
SELECT * FROM dbo.t2
GO
SELECT * FROM dbo.t1 WHERE c2 >= '2022-01-04 12:03' OR c2 is null
UNION ALL
SELECT * FROM dbo.t2 WHERE c22 >= '2022-01-04 12:03' OR c22 is null

Some test cases...

CREATE TABLE dbo.t1  ( 
   c1 int NULL,
   c2 datetime NULL,
   c3 varchar(25) NULL 
   )
GO
CREATE TABLE dbo.t2  ( 
   c1 int NULL,
   c2 datetime NULL,
   c3 varchar(25) NULL 
   )  
GO

DELETE FROM dbo.t1
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(1, '2022-01-04 12:01', 't1 One')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(2, '2022-01-04 12:02', 't1 Two')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(3, '2022-01-04 12:03', 't1 Three')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(4, '2022-01-04 12:04', 't1 Four')
GO
SELECT * FROM dbo.t1
GO
   
DELETE FROM dbo.t2
GO
INSERT INTO dbo.t2(c1, c2, c3) 
   VALUES(1, '2022-01-04 12:01', 't2 One')
GO
INSERT INTO dbo.t2(c1, c2, c3) 
   VALUES(2, '2022-01-04 12:02', 't2 Two')
GO
INSERT INTO dbo.t2(c1, c2, c3) 
   VALUES(3, '2022-01-04 12:03', 't2 Three')
GO
INSERT INTO dbo.t2(c1, c2, c3) 
   VALUES(4, '2022-01-04 12:04', 't2 Four')
GO
SELECT * FROM dbo.t2
GO

SELECT * FROM dbo.t1 WHERE c2 >= '2022-01-04 12:03' OR c2 is null
UNION ALL
SELECT * FROM dbo.t2 WHERE c2 >= '2022-01-04 12:03' OR c2 is null

//////////////////////////////////////

CREATE TABLE dbo.t1  ( 
   c1 int NULL,
   c2 datetime NULL,
   c3 varchar(25) NULL 
   )
GO
CREATE TABLE dbo.t2  ( 
   c11    int NULL,
   c22    datetime NULL,
   c33    varchar(25) NULL 
   )  
GO

DELETE FROM dbo.t1
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(1, '2022-01-04 12:01', 't1 One')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(2, '2022-01-04 12:02', 't1 Two')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(3, '2022-01-04 12:03', 't1 Three')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(4, '2022-01-04 12:04', 't1 Four')
GO
SELECT * FROM dbo.t1
GO
   
DELETE FROM dbo.t2
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(1, '2022-01-04 12:01', 't2 One')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(2, '2022-01-04 12:02', 't2 Two')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(3, '2022-01-04 12:03', 't2 Three')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(4, '2022-01-04 12:04', 't2 Four')
GO
SELECT * FROM dbo.t2
GO
SELECT * FROM dbo.t1 WHERE c2 >= '2022-01-04 12:03' OR c2 is null
UNION ALL
SELECT * FROM dbo.t2 WHERE c22 >= '2022-01-04 12:03' OR c22 is null
ORDER BY c1

//////////////////////////////////////

CREATE TABLE dbo.t1  ( 
   c1 int NULL,
   c2 datetime NULL,
   c3 varchar(25) NULL 
   )
GO
CREATE TABLE dbo.t2  ( 
   c11    int NULL,
   c22    datetime NULL,
   c33    varchar(25) NULL 
   )  
GO

DELETE FROM dbo.t1
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(1, '2022-01-04 12:01', 't1 One')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(2, '2022-01-04 12:02', 't1 Two')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(3, '2022-01-04 12:03', 't1 Three')
GO
INSERT INTO dbo.t1(c1, c2, c3) 
   VALUES(4, '2022-01-04 12:04', 't1 Four')
GO
SELECT * FROM dbo.t1
GO
   
DELETE FROM dbo.t2
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(5, '2022-01-04 12:01', 't2 Five')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(6, '2022-01-04 12:02', 't2 Six')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(7, '2022-01-04 12:03', 't2 Seven')
GO
INSERT INTO dbo.t2(c11, c22, c33) 
   VALUES(8, '2022-01-04 12:04', 't2 Eight')
GO
SELECT * FROM dbo.t2
GO
SELECT * FROM dbo.t1 WHERE c2 >= '2022-01-04 12:03' OR c2 is null
UNION ALL
SELECT * FROM dbo.t2 WHERE c22 >= '2022-01-04 12:03' OR c22 is null

Michal 2022-01-05T23:02:37Z

Hi Tom,

Thanks for the confirmation and explanation. Good luck to you in solving it.

Michal

tomconrad 2022-01-06T20:57:23Z · (edited)

This defect has been there for a long time. It is really an edge case as it requires unioned tables to have the same column names and data ambiguity. Nonetheless, this should be fixed. Any union, intersect, minus... etc set operators will probably not be updatable in the table data editor.

See QueryPanelExecuteEditThread->validateTypes to figure out a way to use the parser to eliminate these type of statements.

Search Tips

Aqua Data Studio / nhilam

ExecuteEdit on UNIONed SELECT corrupts data

2 attachments

Issue #15883

Completion

Search Tips

Aqua Data Studio / nhilam

Title

ExecuteEdit on UNIONed SELECT corrupts data

2 attachments

Issue #15883

Completion