Tri-bool / SQL BUG?



  • I don't know if this counts as a tri-bool, an infinite bool, or what.  I found this on a sql forum.  This guy was going nuts trying to figure this one out, and now I am too, so I figured I would share the insanity.  If you have sql server try this:

     

    -- Creates simple temp table
    CREATE TABLE #foo (
     IID int not null
    )

    -- insert to that table
    INSERT INTO #foo VALUES (1)

     

    -- Now run the following
    SELECT * FROM
    #foo,
    (SELECT IID FROM #foo WHERE IID = 1) d1,
    (SELECT IID FROM #foo WHERE IID = 1) d2
    WHERE  1 = 1

    As you would expect you get 1 row with 3 values 1, 1, 1


    -- Now run this
    SELECT * FROM
    #foo,
    (SELECT IID FROM #foo WHERE IID = 1) D1,
    (SELECT IID FROM #foo WHERE IID = 1) D2
    WHERE
    (SELECT COUNT(IID) FROM #foo WHERE IID = 1) = 0

    As you would expect you get no rows.  We know that the count of #foo.IID where IID = 1 is 1.  1 <> 0 so no rows.


    -- Just to prove the point
    SELECT * FROM
    #foo,
    (SELECT IID FROM #foo WHERE IID = 1) D1,
    (SELECT IID FROM #foo WHERE IID = 1) D2
    WHERE
    (SELECT COUNT(IID) FROM #foo WHERE IID = 1) = 1

    Returns 1 row as you would expect.


    -- Now run this
    SELECT * FROM
    #foo,
    (SELECT IID FROM #foo WHERE IID = 1) D1,
    (SELECT IID FROM #foo WHERE IID = 1) D2

    WHERE
    (SELECT COUNT(IID) FROM #foo WHERE IID = D1.IID) =0

     

    This should return 0 rows but it returns one row

     

    Now here's the insanity part.  Change that last =0 to =1 and it still evaluates to true.  Change it to whatever you want ( <-1, is null, is not null) and it evaluates to true.  Hell ,I even cast the left side of the equality as a varchar and compared it to 'bob' and it still evaluates to true



  • For what it's worth, postgresql returns 0 rows for that last query.

    tribool=> select * from
    foo,
    (select iid from foo where iid = 1) d1,
    (select iid from foo where iid = 1) d2
    where (select count(iid) from foo where iid = d1.iid) = 0;
     iid | iid | iid
    -----+-----+-----
    (0 rows)

    Also worth noting:
    tribool=> select * from
    foo,
    (select iid from foo where iid = 1) d1,
    (select iid from foo where iid = 1) d2
    where (select count(iid) from foo where iid = d1.iid) <> 0;
     iid | iid | iid
    -----+-----+-----
       1 |   1 |   1
    (1 row)


  • @Angstrom said:

    For what it's worth, postgresql returns 0 rows for that last query.

    tribool=> select * from
    foo,
    (select iid from foo where iid = 1) d1,
    (select iid from foo where iid = 1) d2
    where (select count(iid) from foo where iid = d1.iid) = 0;
     iid | iid | iid
    -----+-----+-----
    (0 rows)

    Also worth noting:
    tribool=> select * from
    foo,
    (select iid from foo where iid = 1) d1,
    (select iid from foo where iid = 1) d2
    where (select count(iid) from foo where iid = d1.iid) <> 0;
     iid | iid | iid
    -----+-----+-----
       1 |   1 |   1
    (1 row)


    Just tested. It seems to happen on SQL 2000 only.

    2005 returns no rows as expected.



  • But what is the actual difference between the two queries testing for "= 0", one which returns no rows and the other one which does? What triggers the strange behaviour? (which is probviously a bug)

    As far as I can tell, it's a space between "=" and "0" (could be a typo), and an empty line between the FROM and the WHERE (could be a typo as well).

    =0 occurred to me as possibly being the culprit (=0 means something special in C++, maybe it does so too in MSSQL), but that would be very stupid and you said you could test for various conditions and they all turned out to be true.

    Leaves the empty line between FROM and WHERE.

    My guess, the SQL parser stops parsing at the empty line, so you're basically executing a SELECT without a WHERE clause, returning all rows.



  • Gah nevermind.

    I really should pay more attention to posts before I reply (not to mention be able to edit my posts so I don't look like an idiot).

    The problem is the difference in the WHERE clause that makes the second query a join, I see it now.



  • Interestingly, this sequence works as one would expect in Oracle (9i):

    create table foo ( iid int not null )
    insert into foo values(1)

    select * from foo
    returns: 1 :)

    select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where 1=1
    returns: 1 1 1 :)

    select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where (select count(iid) from foo where iid=1) = 0
    returns 3 empty columns :)

    select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where (select count(iid) from foo where iid=1) = 1
    returns: 1 1 1 :)

    select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where (select count(iid) from foo where iid=d1.iid) =0
    returns 3 empty columns :)

    select * from foo, (select iid from foo where iid=1) d1, (select iid from foo where iid=1) d2 where (select count(iid) from foo where iid=d1.iid) =1
    returns: 1 1 1 :)


    From this, one might conclude:
    Sql Server isn't really ready for prime time
    Microsoft still hasn't figured out how to build a properly behaving SQL engine
    Oracle appears to have gotten it right (I'm not really an Oracle person, we just happen to be using it here)
    If one needs the database to behave correctly, do not use Sql Server (?)


  • ♿ (Parody)

    It's probably a bug on an unpatched version ... here's my results

    select @@version  outputs ...
    Microsoft SQL Server  2000 - 8.00.194 (Intel X86)
     Aug  6 2000 00:57:48
     Copyright (c) 1988-2000 Microsoft Corporation
     Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    -- Now run this
    SELECT * FROM
    #foo,
    (SELECT IID FROM #foo WHERE IID = 1) D1,
    (SELECT IID FROM #foo WHERE IID = 1) D2
    WHERE
    (SELECT COUNT(IID) FROM #foo WHERE IID = D1.IID) =0
    --= (0 row(s) affected)
     
    SELECT * FROM
    #foo,
    (SELECT IID FROM #foo WHERE IID = 1) D1,
    (SELECT IID FROM #foo WHERE IID = 1) D2
    WHERE
    (SELECT COUNT(IID) FROM #foo WHERE IID = D1.IID) =1
    --= (1 row(s) affected)

    ditto when i run it on the serv (2k, SP3)



  • Just to be fair, I've experienced comparable problems with an unpatched version of Oracle 8i, too.



  • In the past, I'd had a few occasions where the optimizer was not evaluating derived tables when expected.  Adding the "FORCE ORDER" hint usually solves this problem.

    Incidentally, I'm running 2000 Ent. sp4 on 2k3 and can duplicate this problem.  Then again, I'd have to shoot myself if I ever used a query that actually looked like that.

     



  • @bullseye said:

    Then again, I'd have to shoot myself if I ever used a query that actually looked like that.




    Amen to that. I personally prefer to rock it old-school and use arcane JOINs over subqueries where possible. I find the order of evaluation etc for complex queries tends to be significantly clearer, although they're less natural to write.



    I did try to work out a JOIN-style query for that last one, but those twisted subqueries, and the sheer contrived state of the example, left me somewhat confused. If I parsed it correctly (and I doubt I did), it should simplify down to SELECT * FROM #foo WHERE IID IS NULL. Fnord.



  • @Irrelevant said:

    @bullseye said:
    Then again, I'd have to shoot myself if I ever used a query that actually looked like that.




    Amen to that. I personally prefer to rock it old-school and use arcane JOINs over subqueries where possible. I find the order of evaluation etc for complex queries tends to be significantly clearer, although they're less natural to write.



    I did try to work out a JOIN-style query for that last one, but those twisted subqueries, and the sheer contrived state of the example, left me somewhat confused. If I parsed it correctly (and I doubt I did), it should simplify down to SELECT * FROM #foo WHERE IID IS NULL. Fnord.

    For some reason, I find myself a little uneasy and somewhat confused....




  • <FONT face=Tahoma size=1><FONT color=#006400>-- Try running this:</FONT>
    <FONT color=#0000ff>SELECT</FONT> *
    <FONT color=#0000ff>FROM</FONT> #foo,
    (<FONT color=#0000ff>SELEC</FONT>T IID <FONT color=#0000ff>FROM</FONT> #foo <FONT color=#0000ff>WHERE</FONT> IID = 1) D1,
    (<FONT color=#0000ff>SELECT</FONT> IID <FONT color=#0000ff>FROM</FONT> #foo <FONT color=#0000ff>WHERE</FONT> IID = 1) D2
    <FONT color=#0000ff>WHERE</FONT>
    (<FONT color=#0000ff>SELECT</FONT> <FONT color=#ff1493>COUNT</FONT>(IID) <FONT color=#0000ff>FROM</FONT> #foo <FONT color=#0000ff>WHERE</FONT> IID = D1.IID) != (<FONT color=#0000ff>SELECT</FONT> <FONT color=#ff1493>COUNT</FONT>(IID) <FONT color=#0000ff>FROM</FONT> #foo <FONT color=#0000ff>WHERE</FONT> IID = D1.IID)


    A bug indeed...</FONT>



  • Wow, that is a weird one...
    I've confirmed it on MSSQL2000-SP4 too, for what it's worth.
    I think it is a bug, and I'm really wishing I wasn't using MSSQL atm :(

    Although, to be fair, I also hope I never come across any queries like that in a production system :P



  • <FONT face=Tahoma size=2>it must be the aggregate function count()

    <FONT color=#006400>--try this too</FONT>
    <FONT color=#0000ff>SELECT</FONT> * <FONT color=#0000ff>FROM</FONT>
    #foo,
    (<FONT color=#0000ff>SELECT</FONT> IID <FONT color=#0000ff>FROM</FONT> #foo <FONT color=#0000ff>WHERE</FONT> IID = 1) D1,
    (<FONT color=#0000ff>SELECT</FONT> IID <FONT color=#0000ff>FROM</FONT> #foo <FONT color=#0000ff>WHERE</FONT> IID = 1) D2
    <FONT color=#0000ff>WHERE</FONT>
    (<FONT color=#0000ff>SELECT</FONT> <FONT color=#ff1493>sum</FONT>(IID) <FONT color=#0000ff>FROM</FONT> #foo <FONT color=#0000ff>WHERE</FONT> IID = 1) = 1
    <FONT color=#006400>--or max(IID) or avg(IID),
    --i haven't tested every aggregate though but this should work correctly
    </FONT>

    <FONT face="Times New Roman" size=3><FONT face=Tahoma size=2>@aihtdikh said:

    Wow, that is a weird one...
    I've confirmed it on MSSQL2000-SP4 too, for what it's worth.
    I think it is a bug, and I'm really wishing I wasn't using MSSQL atm :(

    Although, to be fair, I also hope I never come across any queries like that in a production system :P

    </FONT>
    </FONT>
    but due to code optimizations in production queries, this should be a rare occurence...hope so...[:S]
    </FONT>



  • I feel a little less bad about all the bugs in MySQL now.  If you don't do anything interesting, it works great.  But the edge cases are a bitch and a half (eg: replication and temporary tables)


Log in to reply