Really wonky database results



  • You would think I'd be used to this nonsense, but this is making me want to rip my hair out.

     
    I have a products table, and I'm trying to add new ones by grabbing values from another table which has the new info.  Some of them have already been added, so I need to check and make sure that they haven't already been added.  So I do a simple:

    select SKU from ProductsToAdd where SKU not in (select SKU from Products)

    The result?  0 rows - it says everything matches (I know this to be false).  Okay then, let me check a single product to make sure it's been added.  "SELECT sku FROM Products WHERE sku = 'ABC-12345'" 

    and.... 0 rows.  So now it's telling me that "ABC-12345" does NOT exist in the Products table.  So it exists and doesn't exist at the same time, since my initial query returns nothing, when it should be returning product SKUs in ProductsToAdd that aren't already in Products (unless I'm having a MAJOR brainfart and doing some Lysdexic SLQ [Dyslexic SQL]) , but when I search for a specific product, its telling me it's not there (which I know, that's the point) - I can't ADD it, though, because the query I'd use to add is based off of the first query, which says that all of the new products are already in products.

    Is somethign just wonky, or am I just stupid (don't answer that second part)? 


  • Discourse touched me in a no-no place

    @ObiWayneKenobi said:

    select SKU from ProductsToAdd where SKU not in (select SKU from Products)

     

    <font face="courier new,courier">select SKU from ProductsToAdd where not exists (select SKU from Products) </font>?




  • Are the column names are the same in both tables? If not, the subquery could just be parroting back each ProductsToAdd.SKU back to the select statement. Here's an example from oracle that demostrates this:

    <FONT face="courier new,courier">create table prod(sku varchar2(30) not null);
    insert into prod(sku) values('ABC-1');
    insert into prod(sku) values('ABC-2');
    insert into prod(sku) values('ABC-3');
    insert into prod(sku) values('ABC-4');
    insert into prod(sku) values('ABC-5');</FONT>

    <FONT face="courier new,courier">rem Create newprod table with different name for sku columns.... </FONT>

    <FONT face="courier new,courier">


    create table newprod(sku_code varchar2(30) not null);
    insert into newprod(sku_code) values('ABC-6');
    insert into newprod(sku_code) values('ABC-7');
    insert into newprod(sku_code) values('ABC-8');
    insert into newprod(sku_code) values('ABC-9');

    rem select with accidental reference to newprod's sku_code column in prod subquery</FONT>

    <FONT face="courier new,courier">select * from newprod
    where sku_code not in (select sku_code from prod);</FONT>

    <FONT face="courier new,courier">SKU_CODE                      
    ------------------------------ </FONT>

    <FONT face="courier new,courier">0 rows selected</FONT>

     

    Note the select on the bottom is a lot like yours, and also returns zero rows despite there being data. The prod table does not contain a sku_code column, so the subquery is returning the sku_code column from the newprod table for each row in the prod table.

     This could also happen if the SKU column in the subquery is prefixed with the table alias for the main select statement (always a typo). EG:

    <FONT face="courier new,courier">select pta.sku from ProductsToAdd as pta where pta.sku not in (select pta.sku from Products as prod)  </FONT>



  • @PJH said:

    <font face="courier new,courier">select SKU from ProductsToAdd where not exists (select SKU from Products)</font>
     

    That certainly looks like how I would have written it.

    (Not that I am a SQL expert in any way, shape or form.



  • @PJH said:

    <FONT face="courier new,courier">select SKU from ProductsToAdd where not exists (select SKU from Products) </FONT>?

     This would return no rows as long as there exists at least one row in Products. <FONT face="courier new,courier">exist</FONT>s checks for the existence of records return by a subquery.

     

    <FONT face="courier new,courier">select SKU from ProductsToAdd where not exists(select Products.SKU from Products where Products.SKU=ProductsToAdd.SKU)</FONT>

    A correlated subquery Is probably what you were thinking of....



  • Well I'll be damned - that works.  Learn something new every day, I guess.  Thanks a ton! 



  • @ObiWayneKenobi said:

    Well I'll be damned - that works.  Learn something new every day, I guess.  Thanks a ton! 

     

    PJH's statement you mean? Or dextron's?


  • Discourse touched me in a no-no place

    @dextron said:

    A correlated subquery Is probably what you were thinking of....
    Ah - indeed - and it seems to have been what was required.



  • @dextron said:

     Here's an example from oracle that demostrates this:....The prod table does not contain a sku_code column, so the subquery is returning the sku_code column from the newprod table for each row in the prod table.

    Haha. Oracle sucks. A decent dbms will evaluate the subquery by itself and produce an error if you try to select a column that isn't in the table you are selecting from. But well done spotting what was happening, that was pretty sharp.



  • Are there any NULL's in the Products table?  I have had this sort of problem when the subquery returns a null value all of the tests against it then return false.  Which is sort of what you would expect but is really un-helpful.  I would try

    select SKU from ProductsToAdd where SKU not in (select SKU from Products where SKU is not null)

    If this works then it will probably perform better than the correlated sub query.



  • @vr602 said:

    Haha. Oracle sucks. A decent dbms will evaluate the subquery by itself and produce an error if you try to select a column that isn't in the table you are selecting from.

     

    Which database systems are decent then in your opinion? The database systems I know allow column names from the outer query to be used in the subquery.

     



  • @ammoQ said:

    @vr602 said:

    Haha. Oracle sucks. A decent dbms will evaluate the subquery by itself and produce an error if you try to select a column that isn't in the table you are selecting from.

     

    Which database systems are decent then in your opinion? The database systems I know allow column names from the outer query to be used in the subquery.

     

    Apparently MSSQL 2005 is out of the running too as it behaves the same way.

    This is one of those cases that illustrates fully qualifying the object makes error detection/debugging easier.



Log in to reply