NULL-terminated SELECTs



  • So, I got another ASP Classic intranet app added to the list of legacy nonsense that I get to "maintain".  Unlike most of these, this one is at least largely sensible -- in that I can figure out what it was designed to do, anyway.  Mostly.  Except for the SQL queries.  Every single one of them is ... well, NULL-terminated.

    SELECT gID, gName, NULL ...

    SELECT  II, XI, D, NULL ...

    SELECT START_MINUTE, EVENT_LENGTH, ATTRIB_VAL, NULL ...

    None of the NULLs ever seem to be used, and for the life of me, I cannot fathom why anyone would do this.

     



  • Maybe the results of the query are being fed into something that expects there to be N columns, the last of which being something that was later deemed unnecessary -- removed from the DB, but the results processing code wasn't updated to match, so you throw in an empty column?



  • @Serpentes said:

    So, I got another ASP Classic intranet app added to the list of legacy nonsense that I get to "maintain".  Unlike most of these, this one is at least largely sensible -- in that I can figure out what it was designed to do, anyway.  Mostly.  Except for the SQL queries.  Every single one of them is ... well, NULL-terminated.

    SELECT gID, gName, NULL ...

    SELECT  II, XI, D, NULL ...

    SELECT START_MINUTE, EVENT_LENGTH, ATTRIB_VAL, NULL ...

    None of the NULLs ever seem to be used, and for the life of me, I cannot fathom why anyone would do this.

     

     

    Obviously someone who came from C. Though not entirely successfully.



  •  Clearly they're null-terminated so that nobody will read on and execute the WHERE clauses.



  • @smxlong said:

    Maybe the results of the query are being fed into something that expects there to be N columns, the last of which being something that was later deemed unnecessary -- removed from the DB, but the results processing code wasn't updated to match, so you throw in an empty column?

    I'd considered this, but it just isn't possible.  The first excerpt, for example, queries what is just a two-column table used to translate a system code into a human-readable descriptive name.  There's never been a third column in that table, but there's still that NULL in the query.  And while we've got a few applications that drop data into even-legacy-er spreadsheets and fixed-format forms, this isn't one of them.



  • My guess is this was an attempt to make something generic. If you now loop the columns of the resultset, when you encounter the null column, you know it's the last.

    Too bad you can't ask the original developer and beat him over the head with the null column.



  • @b_redeker said:

    My guess is this was an attempt to make something generic. If you now loop the columns of the resultset, when you encounter the null column, you know it's the last.

    Too bad you can't ask the original developer and beat him over the head with the null column.

     

    Yeah, maybe it was developed with an old library that didn't have peek or size functionality, and to prevent going out of bounds on the result set they had a NULL column.  Not the best way to do it, but it would certainly work.



  • I think I see why it may have been done. Consider this pseudo-code. It is valid even in the event @fields is empty.

    Note: It's still a bad idea.



    $str = "SELECT ";

    foreach $field (@fields)

    {

    $str += "$field, ";

    }

    $str += "NULL FROM TABLE ...";



  • @Frakkle said:

    $str = "SELECT ";

    foreach $field (@fields)

    {

    $str += "$field, ";

    }

    $str += "NULL FROM TABLE ...";

    "Why should we have the code check the fields before we run the query? The SQL server is smart, let it figure it out!"



  • @Frakkle said:

    I think I see why it may have been done. Consider this pseudo-code. It is valid even in the event @fields is empty.
    Note: It's still a bad idea.

    $str = "SELECT ";
    foreach $field (@fields)
    {
    $str += "$field, ";
    }
    $str += "NULL FROM TABLE ...";
    I see that in keeping the spirit of TDWTF you opted to use concatenation inside a for loop instead of <font face="courier new,courier">implode(", ", $fields);</font>  Well done.

    EDIT: yeah, there would be a missing comma. TRWTF is me.



  • @Zecc said:

    @Frakkle said:

    I think I see why it may have been done. Consider this pseudo-code. It is valid even in the event @fields is empty.
    Note: It's still a bad idea.

    $str = "SELECT ";
    foreach $field (@fields)
    {
    $str += "$field, ";
    }
    $str += "NULL FROM TABLE ...";
    I see that in keeping the spirit of TDWTF you opted to use concatenation inside a for loop instead of <font face="courier new,courier">implode(", ", $fields);</font>  Well done.

    EDIT: yeah, there would be a missing comma. TRWTF is me.

    That sure is some funny-looking perl.



  • @morbiuswilters said:

    funny-looking perl
    Is there another type?



  • @bstorer said:

    @morbiuswilters said:

    funny-looking perl
    Is there another type?


    Yes. There's also "line noise".



  • @bstorer said:

    @morbiuswilters said:
    funny-looking perl
    Is there another type?
    Oh yes there is.  I've seen some that was definitely not amusing at all.



  • @morbiuswilters said:

    Filed under: And you can just append a comma to implode(). Using a foreach is pretty dumb.<input name="ctl00$ctl00$bcr$bcr$ctl00$PostList$ctl12$ctl23$ctl01" id="ctl00_ctl00_bcr_bcr_ctl00_PostList_ctl12_ctl23_ctl01_State" value="value:Filed%20under%3A%20%3Ca%20href%3D%22%2Ftags%2FAnd%2Byou%2Bcan%2Bjust%2Bappend%2Ba%2Bcomma%2Bto%2Bimplode_280029002E00_%2B%2BUsing%2Ba%2Bforeach%2Bis%2Bpretty%2Bdumb_2E00_%2Fdefault.aspx%22%20rel%3D%22tag%22%3EAnd%20you%20can%20just%20append%20a%20comma%20to%20implode().%20%20Using%20a%20foreach%20is%20pretty%20dumb.%3C%2Fa%3E" type="hidden">

     

    or even implode(",",(@fields, NULL)) if implode works anything like join in perl.




  • @morbiuswilters said:

    That sure is some funny-looking perl.
    This is why I should refrain from posting late.@morbiuswilters said:
    Filed under: And you can just append a comma to implode(). Using a foreach is pretty dumb.
    Yes, but what if @fields is empty? Then I'd have an extra comma before "NULL".



  • @Zecc said:

    @morbiuswilters said:

    That sure is some funny-looking perl.
    This is why I should refrain from posting late.@morbiuswilters said:
    Filed
    under: And you can just append a comma to implode(). Using a
    foreach is pretty dumb.
    Yes, but what if @fields is empty? Then I'd have an extra comma before "NULL".


    I recently found out that there is actually a way to run code conditionally.

    $str="";
    goto "label".@fields;
    label1:;label2:
    ;label3:;
    label5:;label6:;label7:;
    label4:;
    label8:;
    
    label8:;label9:;
    
    $str=join(",", @fields).",";
    
    label0:
    $str = $str . "NULL";
    


  • @julmu said:

    I recently found out that there is actually a way to run code conditionally.

    <snip>

    Thanks. I really felt like having an aneurism before lunch.



  • @julmu said:

    I recently found out that there is actually a way to run code conditionally.
    Oh, you mean an if statement?  Yeah, I've heard about those.  They're just a fad.



  • I am curious: what happens when one of the real queried columns happens to have a NULL in it?



  • @Abdiel said:

    I am curious: what happens when one of the real queried columns happens to have a NULL in it?



    The query will reach the speed of light, then let the database implode into a black hole which will effectively be the end of the universe.


  • @b_redeker said:

    @Abdiel said:

    I am curious: what happens when one of the real queried columns happens to have a NULL in it?



    The query will reach the speed of light, then let the database implode into a black hole which will effectively be the end of the universe.
     

    And Twitter!



  • I was wrong, although I wish I wasn't.  Those NULLs are being used, in a procedure, hidden from mortal eyes in an include file.  I can't unsee this code, but I can share my suffering with others.

    As background, the recordsets returned by the SQL queries in this app are never directly employed.  Instead, they're bundled into large arrays via GetRows().  Arguably not ideal, but so far no WTFery.  Here and there, however, there's a suspicious looking conditional statement.  For example:

    [code]IF isINVALIDdata(IXDarray,3,1) then ... else ...[/code]

    Sometimes the last two parameters would be variables, sometimes hardcoded magic numbers.  It clearly raised concern, thus beginning my search through nested include files to find it.

    [code]function isINVALIDdata(DATAarray, r, test_c)[/code]
    [code]IF DATAarray(test_c, r) = DATAarray(ubound(DATAarray,1), r) THEN isINVALIDdata = TRUE else isINVALIDdata = FALSE[/code]
    [code]end function[/code]

    Feel free to count the WTFs.  I'm gonna see if I can find something alcoholic on my lunch break.


  • @badcaseofspace said:

    @b_redeker said:

    @Abdiel said:

    I am curious: what happens when one of the real queried columns happens to have a NULL in it?



    The query will reach the speed of light, then let the database implode into a black hole which will effectively be the end of the universe.
     

    And Twitter!

    For the love of God, somebody please insert some nulls into that database!

  • Discourse touched me in a no-no place

    @Serpentes said:

    I'm gonna see if I can find something alcoholic on my lunch break.
    May I reccomend Absinthe. At least a pint of.



  • So if the value in that particular row/col is equal to the value in the the last row for the same col (or vice versa, this is unclear), the data is "invalid"?

    ...why?

     

    I still suggest you look up the original "developer" (and I use the word loosely) and beat him over the head. Maybe with something a bit more substantial than a column of nulls though.



  • @b_redeker said:

    So if the value in that particular row/col is equal to the value in the the last row for the same col (or vice versa, this is unclear), the data is "invalid"?

    ...why?

     

    I still suggest you look up the original "developer" (and I use the word loosely) and beat him over the head. Maybe with something a bit more substantial than a column of nulls though.

    Other way around.  GetRows creates arrays in (col, row) orientation.  I know, the fact that the parameters are passed in the opposite order doesn't help.  But this is actually comparing a particular array entry to the last column in the same row...



  •  ...which is NULL. Duh.



  • @bstorer said:

    For the love of God, somebody please insert some nulls into that database!
     

    Someone already is trying to, but unfortunately it appears he keeps getting distracted and instead succeeds in being a powerful opponent to the TDWTF mod team -- in just 140 characters or less fewer!



  • @b_redeker said:

     ...which is NULL. Duh.

    The best part of which is that this is vbscript and not PHP, so (NULL = NULL) returns NULL, not TRUE.  Now that I finally have tracked down why those NULL columns were created, I assume it was a psychotically complex substitute IsNull().

    Too bad it also doesn't work...


Log in to reply