The long way to describe a table and it's columns



  • select * from table_name where 1=2;

     

    Thankfully, it didnt find any other usage but in development.



  • I've seen that, or something similar, in production!

    Basically, the where clause was set to that, and then changes if data was posted. It was on a page to list things from a database.

    Not the biggest WTF ever, but certainly not a sensible way of doing things. 



  • [quote user="kuroshin"]

    select * from table_name where 1=2;

    [/quote]

    I see that one on a daily basis. Sadly, it is in production, and I'm the only developer who thinks the entire code base isn't worth its weight in AOL CDs.



    • Let a and b be equal non-zero quantities
      a = b
    • Multiply through by a
      a2 = ab
    • Subtract b2
      a2b2 = abb2
    • Factor both sides
      (ab)(a + b) = b(ab)
    • Divide out (ab)
      a + b = b
    • Observing that a = b
      b + b = b
    • Combine like terms on the left
      2b = b
    • Divide by the non-zero b
      2 = 1
    Now your query's going to return the entire database! You're screwed. Should have been "WHERE TRUE=FALSE". Much more robust that way.


  • [quote user="m0ffx"]

    • Let a and b be equal non-zero quantities
      a = b
    • Multiply through by a
      a2 = ab
    • Subtract b2
      a2b2 = abb2
    • Factor both sides
      (ab)(a + b) = b(ab)
    • Divide out (ab)
      a + b = b
    • Observing that a = b
      b + b = b
    • Combine like terms on the left
      2b = b
    • Divide by the non-zero b
      2 = 1

    Now your query's going to return the entire database! You're screwed. Should have been "WHERE TRUE=FALSE". Much more robust that way.
    [/quote]

     

    Wow...

     You broke the maths.



  • oops, double post



  • [quote user="m0ffx"]

    • Divide out (ab)

      a + b = b

    [/quote]

    You can make magic things happen once you divide by zero (a-b = 0, by definition).



  • Not a WTF.  This is useful when you want to append a record to a database but don't want the DB engine to clobber the network fetching the contents of the database to your machine.  I use the key field, however -- "where recnum=0"



  • [quote user="RonBeck62"]Not a WTF.  This is useful when you want to append a record to a database but don't want the DB engine to clobber the network fetching the contents of the database to your machine.  I use the key field, however -- "where recnum=0"
    [/quote]

     

    Bwa?  Example, please?

     

    I've seen this used as a quick way of creating a temp table from a permanent template:

     

    select *

    into #foo

    from foo

    where 1 = 2

     

     

    The WHERE bulletproofs it against someone screwing up and inserting data into the template.

     



  • I've deliberately inserted such things (albeit WHERE 1=0) into production systems... usually when I can detect that a query *cannot* (mutually exclusive terms, etc, which are often beyond the optimiser, as it relates more to business rules than indexing) return any rows, but I want to make sure not to break anything that is expecting the grid with <x> columns. Of course, the UI can worry about displaying a friendly "no records found" etc.

    But no WTF here... *depending* on usage of course ;-p

    Marc



  • Wow, it's IEFBR14 all over again!

     



  • [quote user="RonBeck62"]Not a WTF.  This is useful when you want to append a record to a database but don't want the DB engine to clobber the network fetching the contents of the database to your machine.  I use the key field, however -- "where recnum=0"
    [/quote]

    Agreed, this isn't a WTF. It's common when adding a record to a "bound" recordset. You perform a SELECT that returns no rows, add a new row with the required data, then send that off to the database.

    Using the key field is another way to do this, but may not be as efficient as using "1=2". With the key field, the database doesn't know that no records exist with that ID, so it has to look up indexes or do a table scan. With "1=2", the database doesn't have to look up anything, as nothing will ever satisfy that condition.



  • I never tried the "where 1=2" because SQL is so fussy about syntax.  It never occurred to me that it would be a valid "query".  I suppose this means that "where false" would also work, but not require as much parsing.



  • [quote user="eimaj2nz"]

    [quote user="RonBeck62"]Not a WTF.  This is useful when you want to append a record to a database but don't want the DB engine to clobber the network fetching the contents of the database to your machine.  I use the key field, however -- "where recnum=0"
    [/quote]

    Agreed, this isn't a WTF. It's common when adding a record to a "bound" recordset. You perform a SELECT that returns no rows, add a new row with the required data, then send that off to the database.

    [/quote]

     

    Is this referring to screen controls that auto-bind to the last record (or in this case a lack of record) read from the appropriate table?

     



  • My assumption is that a naive SQL server might go through each record in turn, applying the conditional to decide whether to return the record, without checking if the conitional even refers to a field.

    Or that there's just a query to return the fields in a table.

    I don't know SQL in great detail though.
     


Log in to reply