Today's code is an example of a fairly common pattern I see: a SQL query generator from some middleware generates an absurdly long "IN" predicate that includes the identifier from each row in the table. I'm not quite sure how one could manage to build a system that generates such a query, but I've always let these submissions slide anyway; they just didn't seem to be "enough." What makes Tim Hughes' find so impressive is that the "crack" C++ developers on his team were perfectly aware of how bad of an idea it is to make such absurdly long "IN" lists. Instead, they made sure to "OR" together their really long "IN" lists together. They even made sure that the WHERE clause wasn't too long, instead performing a "UNION" on multiple queries. The one part that they were having trouble with was getting their query to run fast, though ... 

SELECT ORDR_NUM, ORDR_TTL, TAX_CD
  FROM ORDR
 WHERE ( CUST_NUM IN ('110019','110015','110013','110011','110017','110015',
    '110013','110018','110016','110014','110012','120015','120016','150019',
    '150010','150012','150018','150016','150017','150011','150017','150015',
    /* SNIP: few hundred lines */
    ) OR CUST_NUM IN ('210017','210013','210011','21V000','22K018','22W010',
    '22W018','230057','230237','230502','230311','230314','230318','230357',
    /* SNIP: another few hundred lines */
    ) OR CUST_NUM IN ('41K020','41K016','41K018','41K011','41K012','41K018',
    '41K019','41K015','41K015','41K018','41K016','41K014','41K012','41M012',
    '41M014','41M013','41M018','41M013','41M015','41N014','41S006','41T003',
    '41T001','41T001','41T012','41T010','41T018','41T015','41V003','41V002'))
UNION
SELECT ORDR_NUM, ORDR_TTL, TAX_CD
  FROM ORDR
 WHERE ( CUST_NUM IN ('610016','610012','610010','610013','610024','610032',
    '610040','610019','610010','610017','610010','610019','610012','610010',
    '610018','610018','610016','610017','611011','611012','612011','612019',
    /* SNIP: a bunch more lines */
    ) OR CUST_NUM IN ('
    '68E015','68K000','68K012','68K019','68K016','68K012','68K018','68K014',
    '68K012','68K010','68K019','68N019','68T010','68U015','68V021','68V039',
    '68V047','68V054','68V062','68V070','68V088','68V096','68V104','68V112',
    '68V120','68V138','68V146','68V153','68V017','68V018','68V011','68V016'))
UNION
/* You probably get the pattern by now */
UNION
SELECT ORDR_NUM, ORDR_TTL, TAX_CD
  FROM ORDR
 WHERE ( CUST_NUM IN ('920001','920002','920003','920000','920008','920007',
    '920015','920010','920012','920018','920013','920019','920019','920012',
    '920013','920012'))
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!