Indeed you did reproduce in that you selected all of the 1 entries you inserted into table item and inadvertently used the field name from the table in the subquery in the NOT IN case ;)
A subquery "select itemcode from tmp_salesitm", is allowed to refer to fields from the outer "select * from item" , making it a correlated subquery. Since there is no where clause, the subquery will return the correlated field itemcode (as opposed to it's native field itemid) exactly once for each row it contains. Adding the same element to a set n>0 times is no different than adding the element to the set exactly once, so the IN set has, for each execution, one element and the expression <n> IN (<n>) is always true. As duly noted <n> IN (<m>) is logically equivalent to <n> == <m>, but inherently very much slower in execution because SQLite does not check/optimize for this edge case. -----Urspr?ngliche Nachricht----- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Igor Tandetnik Gesendet: Mittwoch, 07. Oktober 2015 19:52 An: sqlite-users at mailinglists.sqlite.org Betreff: Re: [sqlite] Strange behaviour of IN/NOT IN subqueries? On 10/7/2015 10:40 AM, Constantine Yannakopoulos wrote: > I have two tables, "item" (items on sale) where a column named "itemcode" > is the PK and "tmp_salesitm" (invoice item lines) where the foreign > key column to "item" is called "itemid" and not "itemcode" for some > reason. I wanted to find all records from the first with no matching > records in the second (i.e. items that have never been sold). So I hastily > typed: > > select * from item where itemcode not in (select itemcode from > tmp_salesitm) > > forgetting that the column name was different. To my amazement the > query returned ALL rows from table "item" Can't reproduce. sqlite> create table item(itemcode integer); insert into item(itemcode) sqlite> values (1); create table tmp_salesitm(itemid integer); insert sqlite> into tmp_salesitm(itemid) values (100); select * from item sqlite> where itemcode not in (select itemcode from tmp_salesitm); sqlite> select * from item where itemcode not in (select itemid from tmp_salesitm); 1 sqlite> select * from item where itemcode in (select itemcode from tmp_salesitm); 1 > So I wondered, shouldn't the first query throw an error No it should not. The query is valid, if meaningless. The correlated subquery is allowed to use columns from the enclosing query's tables. > instead of returning a result that does not make sense? It's not up to the engine to determine whether or not a query would make sense to a human. Its job is to check whether the query is syntactically valid, then execute it as written. > Then I thought that the SQL > parser may have interpreted "itemcode" in the subquery as a reference > to the "itemcode" column of the outer query. That's what happens, yes. > My question is, is this behaviour normal? Should a NOT IN subquery, > that uses a different from clause and is -to my knowledge- not > correlated, be allowed to select columns of the outer query's FROM tables? What, in your opinion, is the definition of "correlated subquery", if not "a subquery that happens to be using columns from the outer query"? > Shouldn't an > error be raised instead or am I missing something? You are missing something. > FYI the outcome is similar if I replace "NOT IN" with "IN". With: > > select * from item where itemcode in (select itemcode from > tmp_salesitm) > > I get zero records For me, it works the other way round (as I would expect). IN return all records, since it's essentially equivalent to select * from item where itemcode = itemcode; NOT IN returns no records, since it's essentially equivalent to select * from item where itemcode != itemcode; Are you sure you are not mixing up the two? -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
