Curious IDS 9.40 subquery problem
A user on IDS 9.40 found that "AND a.serv_id NOT IN (SELECT serv_id FROM serv_65)" returned no rows, while adding any WHERE filter to the subquery made the row appear. Jonathan Leffler correctly diagnosed the cause: serv_65 contained rows with NULL serv_id, and a NOT IN list containing NULL never evaluates true, so the whole predicate fails. The poster confirmed this and worked around it by adding "WHERE serv_id IS NOT NULL" to the subquery. Follow-ups noted IN and NOT IN can both return no rows with NULLs, which Art Kagel explained as correct SQL NULL semantics.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Below is my test SQL:
SELECT serv_id FROM serv_65 where serv_id = 148951No datum returns.
SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
WHERE a.state in ('F0A', 'F0J')
AND a.serv_id NOT IN (SELECT serv_id FROM serv_65 where serv_id =
148951)
AND a.completed_date <= '2007-01-31 23:59:59'
AND a.serv_id = 148951returns one record, its serv_id = 148951.
But after removing the condition in the subquery:
SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
WHERE a.state in ('F0A', 'F0J')
AND a.serv_id NOT IN (SELECT serv_id FROM serv_65)
AND a.completed_date <= '2007-01-31 23:59:59'
AND a.serv_id = 148951No datum returns.
This problem has puzzled me for days.
Merlin Ran wrote:
> Below is my test SQL:
>
> SELECT serv_id FROM serv_65 where serv_id = 148951> No datum returns.
>
> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
> WHERE a.state in ('F0A', 'F0J')
> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65 where serv_id =
> 148951)
> AND a.completed_date <= '2007-01-31 23:59:59'
> AND a.serv_id = 148951> returns one record, its serv_id = 148951.
>
> But after removing the condition in the subquery:
> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
> WHERE a.state in ('F0A', 'F0J')
> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65)
> AND a.completed_date <= '2007-01-31 23:59:59'
> AND a.serv_id = 148951> No datum returns.
>
> This problem has puzzled me for days.
>
If you use tables in sub-querys with similar column names as the outside table(s) you must qualify the column names with the table prefix (name or alias).
It's a known situation where you can get unknown effects.
Regards.
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
It becomes more interesting.
After I change the 3rd SQL statement to:
SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
WHERE a.state in ('F0A', 'F0J')
AND a.serv_id NOT IN (SELECT serv_id FROM serv_65 where serv_id = 1 or
serv_id <> 1)
AND a.completed_date <= '2007-01-31 23:59:59'
AND a.serv_id = 148951It returns the same as the 2nd SQL.
As the example shows, what important is the existence of WHERE clause, not
the content.
"Merlin Ran" <merlinran@gmail.com> д''''Ϣ'''':1171091709.295591.277160@q2g2000cwa.googlegroups.com...
> Below is my test SQL:
>
> SELECT serv_id FROM serv_65 where serv_id = 148951> No datum returns.
>
> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
> WHERE a.state in ('F0A', 'F0J')
> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65 where serv_id =
> 148951)
> AND a.completed_date <= '2007-01-31 23:59:59'
> AND a.serv_id = 148951> returns one record, its serv_id = 148951.
>
> But after removing the condition in the subquery:
> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
> WHERE a.state in ('F0A', 'F0J')
> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65)
> AND a.completed_date <= '2007-01-31 23:59:59'
> AND a.serv_id = 148951> No datum returns.
>
> This problem has puzzled me for days.
>
>
I'm confused by your second note. The second SQL in the first note
had no where clause and returned no rows. The SQL in your second
note had a where clause and you say it behaved the same as the second
SQL from before and draw the conclusion that the difference is in the
presence of the where clause.
Could you clarify?
It seems to me that the odd behavior is not the where clause but the
fact that the first where clause causes a null set to be returned for
the sub query. Can you verify this by changing the test in the
clause from = 148951 to = a.serv_id?
Which fixpack of 9.4 are you running? What logging mode is used on
the database in question?
Christine
On Feb 10, 2007, at 6:35 AM, Merlin Ran wrote:
> It becomes more interesting.
>
> After I change the 3rd SQL statement to:
> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
> WHERE a.state in ('F0A', 'F0J')
> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65 where serv_id = 1 or
> serv_id <> 1)
> AND a.completed_date <= '2007-01-31 23:59:59'
> AND a.serv_id = 148951> It returns the same as the 2nd SQL.
>
> As the example shows, what important is the existence of WHERE
> clause, not
> the content.
>
> "Merlin Ran" <merlinran@gmail.com> дÈëÏûÏ¢ÐÂÎÅ:
> 1171091709.295591.277160@q2g2000cwa.googlegroups.com...
>> Below is my test SQL:
>>
>> SELECT serv_id FROM serv_65 where serv_id = 148951>> No datum returns.
>>
>> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
>> WHERE a.state in ('F0A', 'F0J')
>> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65 where serv_id =
>> 148951)
>> AND a.completed_date <= '2007-01-31 23:59:59'
>> AND a.serv_id = 148951>> returns one record, its serv_id = 148951.
>>
>> But after removing the condition in the subquery:
>> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
>> WHERE a.state in ('F0A', 'F0J')
>> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65)
>> AND a.completed_date <= '2007-01-31 23:59:59'
>> AND a.serv_id = 148951>> No datum returns.
>>
>> This problem has puzzled me for days.
>>
>>
>
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
Merlin Ran wrote:
> It becomes more interesting.
>
> After I change the 3rd SQL statement to:
> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
> WHERE a.state in ('F0A', 'F0J')
> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65 where serv_id = 1 or
> serv_id <> 1)
> AND a.completed_date <= '2007-01-31 23:59:59'
> AND a.serv_id = 148951> It returns the same as the 2nd SQL.
I suspect that there is a row in serv_65 with a null in the serv_id
column, and this is messing up your thinking.
Try: SELECT COUNT(*) FROM serv_65 WHERE serv_id IS NULL;
If the count is not zero, inspect the row(s) with a null serv_id, remove
them (or update the serv_id to a suitable non-null value), and try again.
We then have to work out whether the behaviour is correct - but NOT IN
sub-selects and NULL values are pretty confusing.
> As the example shows, what important is the existence of WHERE clause, not
> the content.
>
> "Merlin Ran" <merlinran@gmail.com> wrote:
>> Below is my test SQL:
>>
>> SELECT serv_id FROM serv_65 where serv_id = 148951>> No datum returns.
>>
>> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
>> WHERE a.state in ('F0A', 'F0J')
>> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65 where serv_id =
>> 148951)
>> AND a.completed_date <= '2007-01-31 23:59:59'
>> AND a.serv_id = 148951>> returns one record, its serv_id = 148951.
>>
>> But after removing the condition in the subquery:
>> SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
>> WHERE a.state in ('F0A', 'F0J')
>> AND a.serv_id NOT IN (SELECT serv_id FROM serv_65)
>> AND a.completed_date <= '2007-01-31 23:59:59'
>> AND a.serv_id = 148951>> No datum returns.
>>
>> This problem has puzzled me for days.
>>
>>
>
>
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
On Feb 11, 1:39 pm, Jonathan Leffler <jleff...@earthlink.net> wrote:
> Merlin Ran wrote:
> > It becomes more interesting.
>
> > After I change the 3rd SQL statement to:
> > SELECT a.acc_nbr, a.serv_id, a.state FROM f_serv a
> > WHERE a.state in ('F0A', 'F0J')
> > AND a.serv_id NOT IN (SELECT serv_id FROM serv_65 where serv_id = 1 or
> > serv_id <> 1)
> > AND a.completed_date <= '2007-01-31 23:59:59'
> > AND a.serv_id = 148951> > It returns the same as the 2nd SQL.
>
> I suspect that there is a row in serv_65 with a null in the serv_id
> column, and this is messing up your thinking.
>
> Try: SELECT COUNT(*) FROM serv_65 WHERE serv_id IS NULL;
>
> If the count is not zero, inspect the row(s) with a null serv_id, remove
> them (or update the serv_id to a suitable non-null value), and try again.
>
> We then have to work out whether the behaviour is correct - but NOT IN
> sub-selects and NULL values are pretty confusing.
>
The reason is exactly as what you suspected. I simply add a filter
"WHERE serv_id IS NOT NULL" as workaround.
[sniped]
> Jonathan Leffler #include <disclaimer.h>
> Email: jleff...@earthlink.net, jleff...@us.ibm.com
> Guardian of DBD::Informix v2005.02 --http://dbi.perl.org/- Hide quoted text -
>
Jonathan Leffler never spoke more truly than when he said:
> We then have to work out whether the behaviour is correct - but NOT IN
> sub-selects and NULL values are pretty confusing.
Take a look at the test case below and tell me if the behaviour is
correct. When a subquery returns a set of nulls (which is not the
same as a null set), IN and NOT IN return the same results: no rows
found.
-- Start clean
DROP TABLE test_nulls;
DROP TABLE test_not_nulls;
-- Create tables
CREATE TABLE test_nulls (nullid_col SERIAL, null_col CHAR(10));
CREATE TABLE test_not_nulls (not_nullid_col SERIAL, not_null_colCHAR(10));
-- Insert test data
INSERT INTO test_nulls (nullid_col) VALUES (0);
INSERT INTO test_nulls (nullid_col) VALUES (0);
INSERT INTO test_nulls (nullid_col) VALUES (0);
INSERT INTO test_not_nulls (not_nullid_col, not_null_col) VALUES (0,"first");
INSERT INTO test_not_nulls (not_nullid_col, not_null_col) VALUES (0,"second");
INSERT INTO test_not_nulls (not_nullid_col, not_null_col) VALUES (0,"third");
-- First select, no hits expected
SELECT * FROM test_not_nulls
WHERE not_null_col IN (SELECT null_col FROM test_nulls)
;
-- not_nullid_col not_null_col
--
-- No rows found.
-- Second select, opposite results expected?
SELECT * FROM test_not_nulls
WHERE not_null_col NOT IN (SELECT null_col FROM test_nulls)
;
-- not_nullid_col not_null_col
--
-- No rows found.
-- Change one row to get back some data
UPDATE test_nulls SET null_col =
(SELECT not_null_col FROM test_not_nulls WHERE not_nullid_col = 3)
WHERE nullid_col = 3
;
-- First select, one hit expected
SELECT * FROM test_not_nulls
WHERE not_null_col IN (SELECT null_col FROM test_nulls)
;
-- not_nullid_col not_null_col
--
-- 3 third
--
-- 1 row(s) retrieved.
-- Second select, opposite results expected?
SELECT * FROM test_not_nulls
WHERE not_null_col NOT IN (SELECT null_col FROM test_nulls)
;
-- not_nullid_col not_null_col
--
-- No rows found.
-- Clean up
DROP TABLE test_nulls;
DROP TABLE test_not_nulls;
CHRISTOPHER COLEMAN
Database Analyst
MEDIWARE INFORMATION SYSTEMS
Office: (913) 307-1073
Fax: (913) 307-1111
christopher.coleman@mediware.com
christopherc@gmail.com wrote: > Jonathan Leffler never spoke more truly than when he said: > >>We then have to work out whether the behaviour is correct - but NOT IN >>sub-selects and NULL values are pretty confusing. > > > Take a look at the test case below and tell me if the behaviour is > correct. When a subquery returns a set of nulls (which is not the > same as a null set), IN and NOT IN return the same results: no rows > found. <SNIP> Yes, because nothing, even NULL, either matches or does not match a NULL. The semantics of that value require that since its actual value is unknown no filter except IS NULL/IS NOT NULL is allowed to match. Art S. Kagel