DB2 error: SQL subselect error not recognized

Question

I think I just found an error in DB2 itself. When I run this code I expect it to throw an error when executing the delete statement (the subselect wrongly uses A_NAME instead of NAME). But: it acts as if there was no where clause and deletes all the rows in table NAMES!

CREATE TABLE NAMES    (A_NAME VARCHAR(20));
CREATE TABLE OLDNAMES (NAME   VARCHAR(20));

INSERT INTO NAMES VALUES ('ANNA'), ('ELLA'), ('JOHN'), ('EARL');
INSERT INTO OLDNAMES VALUES ('ELLA'), ('EARL');

-- this should throw an error message:
DELETE FROM NAMES WHERE A_NAME IN (SELECT A_NAME FROM OLDNAMES);

-- this should show ANNA & JOHN if the subselect 
-- was correct, but shows nothing
SELECT * FROM NAMES;

-- cleanup
DROP TABLE NAMES;
DROP TABLE OLDNAMES;

I ran it on a DB2/LINUXX8664 10.5.9

Or is "not a bug, but a feature"?!

Gordon Linoff · Accepted Answer · 2020-01-24 12:26:06Z

4

You are wrong. SQL has scoping rules for resolving column references in subqueries. If the column reference is not resolved in the inner query, then it looks to the outer query.

These are the rules of SQL, not specific to DB2.

That is SQL interprets your logic as:

DELETE FROM NAMES
    WHERE NAMES.A_NAME IN (SELECT NAMES.A_NAME FROM OLDNAMES ON);

And this is valid -- if meaningless -- SQL.

This is why qualifying ALL column references is recommended. The better way to write this query is:

DELETE FROM NAMES
    WHERE NAMES.A_NAME IN (SELECT ON.A_NAME FROM OLDNAMES ON);

answered Jan 24, 2020 at 12:26

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

koem Over a year ago

Wow! So the inner select produces all 4 names from NAMES, right?

Mark Barinstein Over a year ago

No. It must work as follows logically. A check is performed for each qualified row in NAMES. For each such a row a result set is formed in the sub-select. Every time it consists of 2 rows with the same constant got from A_NAME column of the row being evaluated for deletion. The result of such an evaluation for every row in NAMES is obviously TRUE (valX in (valX, valX) is TRUE for every valX) and it’s is why you get all the rows deleted. The query works correctly.

Collectives™ on Stack Overflow

DB2 error: SQL subselect error not recognized

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related