• Russ (unregistered)

    how about just adding # to the beginning of every table name?

     

     

  • (cs)

    "risk of failure"

     hilarious...as if flooding the DB with garbage data wasn't a mode of a failure

    i also like the developer's "not again!" whining...you'd think he could learn from the first experience.

     mike: send him an email, copy somebody important, and tell him that his code has the potential to bring the production DB to a halt, suggest the obvious solution to him, and he either's gonna comply or get fired.

  • (cs) in reply to Russ
    Anonymous:

    how about just adding # to the beginning of every table name?

     

    How about not using temp tables, and using proper queries instead?  Or did I miss the sarcasm tag again?

  • (cs) in reply to savar

    savar:
    i also like the developer's "not again!" whining...you'd think he could learn from the first experience.

    A. A. Milne:
    Here is Edward Bear, coming downstairs now, bump, bump, bump, on the back of his head. It is, as far as he knows, the only way of coming downstairs, but sometimes he feels that there really is another way, if only he could stop bumping for a moment and think of it. And then he feels that perhaps there isn't.

  • Jason (unregistered) in reply to Grimoire

    Or how about using REAL temp tables (only where necessary, a good query is always better) that clean themselves up when you're done w/ them?

     captcha: billgates - not the problem here for once.
     

  • Dazed (unregistered) in reply to Grimoire

    At least there seems to be a straightforward fix here, seeing that it calculates commissions.

    Step 1: inform the development manager that this procedure could collapse at any moment, and that he needs to get a competent developer to rewrite it (with the DBA doing quality control).

    Step 2 (if step 1 isn't sufficiently effective): just tweak something somewhere so that the commissions no longer get credited. A mere DBA may not be able to kick a development manager into action, but an entire sales force baying for blood most certainly will.

  • (cs)
    Alex Papadimoulis:

    "we can't possibly risk altering the code for this procedure; the risk of failure is just too high."

    That is why development databases exist. Wonder how this dude got through the BS filter.

  • (cs) in reply to Grimoire
    Grimoire:
    Anonymous:

    how about just adding # to the beginning of every table name?

     

    How about not using temp tables, and using proper queries instead?  Or did I miss the sarcasm tag again?

    With very large amounts of data, temp tables can be important for writing efficient procedures.  In my experience, you can't always just write a query.

    As for the wtf, I can imagine a developer thinking: "Temp tables are so ugly, you have to create/drop them all the time. . .  I know, I'll create actual tables, and then just truncate them each time the stored procedure is finished!"  Then in the implementation, he forgot about the truncating part and just create/dropped as usual.

  • sir_flexalot (unregistered)

    "risk of failure" = "doesn't really work that well as is"

     

    ha ha.


  • my name is missing (unregistered) in reply to kuroshin

    How do people stay in business with such crap? O yeah, I worked for one like this...

  • Marak (unregistered)

    I just don't understand.

     How could anyone be in charge of developing such a vital stored procedure without KNOWING SQL.

    The true WTF here is:

    1. Whoever put this person in the charge of writing stored procedures

    2. The developer himself for not reading past the SELECT INTO and DROP parts of ANY SQL tutorial

     

  • Dazed (unregistered) in reply to rmr
    rmr:
    As for the wtf, I can imagine a developer thinking: "Temp tables are so ugly, you have to create/drop them all the time. . .  I know, I'll create actual tables, and then just truncate them each time the stored procedure is finished!"  Then in the implementation, he forgot about the truncating part and just create/dropped as usual.

    More likely a developer who hasn't heard of joins hasn't heard of temporary tables either.

  • Anonymoose DBA (unregistered)

    So many WTF's here hard to know where to start . . .  and that this is so common in the real world causes much unneeded pain..

    I particularly like how the statement shown is a selection from one "temp" table into another "temp" table.  These sorts of things are horrible for database performance and while putting a '#" in front of every table name would solve one problem it won't fix the whole mess being done so wrong.

     

  • Say What? (unregistered)
    Alex Papadimoulis:

    Unlike just about every other aggregate data computing script, this procedure managed to do all of its work without a single UPDATE, INNER JOIN, OUTER JOIN, or, really, anything but a SELECT INTO statement. It did its work by created a whole slew of "temp" tables in this manner: 

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]
     GROUP BY [Itm_Nm]
     ORDER BY 2

    I am not well versed in SQL: are these two not the same, or at least similar? Wouldn't the query parser/compiler portion of the DB implement these in the same way, or am I missing something subtle? (thanks)

     

  • Unklegwar (unregistered)
    Alex Papadimoulis:
    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]
     GROUP BY [Itm_Nm]
     ORDER BY 2

    WTF is "order by 2"?

    WTF ironic CAPTCHA: random (2nd time in a row) 

     

  • WHO WANTS TO KNOW? (unregistered) in reply to Unklegwar

    Standard SQL, BION!  Order by 2(nd field)!  So it orders by Max_Itm_Amt!

     Steve

  • (cs) in reply to Unklegwar
    Anonymous:
    Alex Papadimoulis:
    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]
     GROUP BY [Itm_Nm]
     ORDER BY 2

    WTF is "order by 2"?

    WTF ironic CAPTCHA: random (2nd time in a row) 

    You can specify column order (1 based counting) from the query in the order by instead of using the column name.

  • dbomp (unregistered) in reply to Digitalbath
    Digitalbath:
    Anonymous:

    WTF is "order by 2"?

    WTF ironic CAPTCHA: random (2nd time in a row) 

    You can specify column order (1 based counting) from the query in the order by instead of using the column name.

    Yeah, though why you'd want to bother ordering when you're inserting into a table is a bit of a mystery.

    Truth be told, I still have some code that uses temporary tables like this.  Back in 1994 or so, old Ingres didn't do temporary tables so well (or at all?), so we had lots of worthless temp-yet-too-permanent tables laying around.  This month I started rewriting code to ditch the last major use of them. 

     

  • (cs)

    This makes sense on a DB2 environment.

    All those DROP TABLEs imply it isn't running on DB2, though. Probably a DB2 developer who didn't bother learning how SQL Server, or Oracle, or whatever works.

  • (cs) in reply to dbomp

    The WTF for me is that people who write stuff in a syntax like

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]
    GROUP BY [Itm_Nm]
    ORDER BY 2

    get to call themselves "developers."

  • (cs) in reply to mrprogguy

    DROPPING TABLES IN A PRODUCTION ENVIRONMENT?

     

    Well, [ORA-00942: table or view does not exist ] happens.

  • WTF Batman (unregistered) in reply to dbomp

    Anonymous:
    Digitalbath:
    Anonymous:

    WTF is "order by 2"?

    WTF ironic CAPTCHA: random (2nd time in a row) 

    You can specify column order (1 based counting) from the query in the order by instead of using the column name.

    Yeah, though why you'd want to bother ordering when you're inserting into a table is a bit of a mystery.

    Truth be told, I still have some code that uses temporary tables like this.  Back in 1994 or so, old Ingres didn't do temporary tables so well (or at all?), so we had lots of worthless temp-yet-too-permanent tables laying around.  This month I started rewriting code to ditch the last major use of them. 

    I've actually seen code use this in conjunction with an identity field. Inserting them in a certain order makes sure that the identity field's values are assigned in that order. 

  • Merlin (unregistered)

    O dear, had this been with a more Oracle-like syntax and I would swear that I used to manage that application.

    In a previous job, I becale responsible by default for such an app. The app would contain one main table with many millions of rows and a hundred columns. That amount was growing every day with every sale from the big company. During the night, the thing would start an impressive stored procedure that split the table into smaller ones, started checking correspondance between customer NAMES (no unique id, just a manually typed name!) and started "insert into dist_field5 select distinct field5 from huge_table" for nearly every column or combinations thereof and did other things I could never understand.

    Of course, that huge procedure would fail once in a while. Generally because a DBA would kill that resource hog. In those cases, the data would be a real mess. I would then have to manually check the logs, figure out how to put the pieces together and restart the thing.

    To avoid too much trouble, the procedure was also copying all tables to bkup_* tables. A nice DBA put those tables in the TEMP but of course, if the thing crashed, the backup would be lost as well. And such a huge transaction is close to impossible.

    I wanted to rewrite the app properly but there was no documentation at all (only from poor fellows like me who tried to reverse engineer the thing). The original developer (he started with FileMaker Pro !) was unknown and there no user with enough understanding of the thing to start a design from scratch.

     Oh, I forgot the best: that application was generating billing for millions of dollars !


    The application survived for many years as-is.
     

  • (cs)

    How the heck did the application ID get dbo access to the database in the first place?  Maybe I should submit this procedure:

     

    create procedure F_up_db

    as

    exec sp_msforeachtable "drop table ?" 

    go 

  • (cs)

    *Nice*

    just like the guy who had a 5th normal database model that was cross platform (unix - oracle to windows and Sql Server 2k or whatever) that had 200 + / - tables in it.  Each table had a insert/update/delete trigger upon it that for all intents and purposes hammered the other half of the database by storing the audit information in the "history" tables.   All he did was (wtf #1) copy the data model, including triggers to "History"<tablename> etc. so a insert happened, a trigger happily fired, one row on the "History"<tablename> commenced and the trigger on the "History"<tablename> fired.  Can you change that? oh no, it's SYSTEM generated.

     <NOOB/>

  • merreborn's nemesis (unregistered)

    This is a perfect example of someone who has no knowledge whatsoever of what makes something work, but thinks that they can write decent code for it anyway.

     

    I'm cleaning up after just such a person in my business now:  the took a state-less definition and 'built-in' state information storage/manipulation (in the implementation) using magic keywords...needless to say the state information they stored can all be generated on the fly by the calling process and passed in the defined manner.

     

    <action type="expressive" name="sigh" area="face" />

     

    CAPTCHA:  batman (all these people think they are him)

  • rmg66 (unregistered) in reply to mrprogguy
    mrprogguy:

    The WTF for me is that people who write stuff in a syntax like

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]
    GROUP BY [Itm_Nm]
    ORDER BY 2

    get to call themselves "developers."

     What's wrong with this syntax? It's perfectly acceptable.

    What would you do instead?

  • merreborn's nemesis (unregistered) in reply to rmg66
    Anonymous:
    mrprogguy:

    The WTF for me is that people who write stuff in a syntax like

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]
    GROUP BY [Itm_Nm]
    ORDER BY 2

    get to call themselves "developers."

     What's wrong with this syntax? It's perfectly acceptable.

    What would you do instead?

     

    How about:

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]

  • (cs)

    Mmmm...one of my favorite things - permanent temporary objects.  :thumbsup:

  • (cs)

    Revoke create table rights.  Done and done.

  • An apprentice (unregistered)

    __TMP_STEP01SC, __TMP_STEP02SC, __TMP_STEP03SC, __TMP_STEP04SC, __TMP_STEP05SC, __TMP_STEP06SC...

    Imperative programming considered harmful?

  • (cs) in reply to merreborn's nemesis
    Anonymous:

    How about:

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]

     

    That's fine if you don't have an autonumber field in there.  I do stuff similar to this frequently when I'm generating reports or whatever, from tables that don't have good indexes (only in a more sane manner):  you scan the tables you want with the best indexes you can get, and throw everything into a temp-table that has exactly what indexes you want, and then you can report directly off the temp-tables without having to actually sort them, because you created them in sorted order!

  • dsfgsddsfgsdfgdsffg (unregistered) in reply to merreborn's nemesis
    Anonymous:
    Anonymous:
    mrprogguy:

    The WTF for me is that people who write stuff in a syntax like

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]
    GROUP BY [Itm_Nm]
    ORDER BY 2

    get to call themselves "developers."

     What's wrong with this syntax? It's perfectly acceptable.

    What would you do instead?

     

    How about:

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]

    You can't use aggregate functions without a GROUP BY if you also have non-aggregate columns.
  • rmg66 (unregistered) in reply to merreborn's nemesis
    Anonymous:
    Anonymous:
    mrprogguy:

    The WTF for me is that people who write stuff in a syntax like

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]
    GROUP BY [Itm_Nm]
    ORDER BY 2

    get to call themselves "developers."

     What's wrong with this syntax? It's perfectly acceptable.

    What would you do instead?

     

    How about:

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]

     

    Hello!

    You can't do a MAX() without a group clause, and gee, maybe I would like them to be ordered by column two.

    You must be a client side developer.

  • (cs) in reply to merreborn's nemesis
    Anonymous:
    Anonymous:
    mrprogguy:

    The WTF for me is that people who write stuff in a syntax like

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]
    GROUP BY [Itm_Nm]
    ORDER BY 2

    get to call themselves "developers."

     What's wrong with this syntax? It's perfectly acceptable.

    What would you do instead?

     

    How about:

    INSERT INTO [__TMP_STEP05SC]
    SELECT [Itm_Nm], MAX([Itm_Amt]) AS [Max_Itm_Amt]
      FROM [__TMP_STEP04SC]



    What if Itm_Nm is not unique?  You'd receive a different data set with the Group By clause.

    And I am not sure about other vendors as I've only used SQL Server, but with MS you would receive an error: "Itm_Nm is invalid in the selection list because it is not contained in either an aggregate function or the group by clause."
  • (cs)

    A tip an experienced DBA once taught me:

    Whenever someone screws with the database, drop their tables, drop their procedures, drop their user and so on. Basically drop everything they have. It sort of like a 3 strikes rule, only that there's only one strike. Of course, a logical backup is advised first, but most of the time you never use it.

    At the time, it seemed a bit harsh, but there's such an abundance of stupid developers, that you just can’t be easy on them.

  • tieTYT (unregistered) in reply to kuroshin

    kuroshin:
    That is why development databases exist. Wonder how this dude got through the BS filter.

    Sometimes where I work that's not considered an option.  If we're just about to have a release and it already works (this problem has a 'treatment', for lack of a better word.  Attempting to fix it could put it in a worse situation), fixing this would require more time to qa the problem and require multiple builds if it doesn't work correctly the first time.  When this is expected to be released tomorrow and the process i mention takes 2 weeks, we often choose to punt on fixing the problem.

    Is that a bad practice?

  • Runtime Error (unregistered) in reply to XMLord
    XMLord:

    A tip an experienced DBA once taught me:

    Whenever someone screws with the database, drop their tables, drop their procedures, drop their user and so on. Basically drop everything they have. It sort of like a 3 strikes rule, only that there's only one strike. Of course, a logical backup is advised first, but most of the time you never use it.

    At the time, it seemed a bit harsh, but there's such an abundance of stupid developers, that you just can’t be easy on them.

     We should dust off and nuke the site from orbit.  Its the only way to be sure.

     

  • (cs) in reply to Runtime Error

    I may know next to nill about coding but even I can tell that creating tables that already exist doesn't work very well & normally fails.

  • brandon (unregistered)

    How about having columns with trailing spaces in the names (I kid you not!!)

    SELECT
        [Column Name ]
    FROM
        [Table]

    Some developers will never get it!

     

     

     

     

  • (cs) in reply to tieTYT
    Anonymous:

    kuroshin:
    That is why development databases exist. Wonder how this dude got through the BS filter.

    Sometimes where I work that's not considered an option.  If we're just about to have a release and it already works (this problem has a 'treatment', for lack of a better word.  Attempting to fix it could put it in a worse situation), fixing this would require more time to qa the problem and require multiple builds if it doesn't work correctly the first time.  When this is expected to be released tomorrow and the process i mention takes 2 weeks, we often choose to punt on fixing the problem.

    Is that a bad practice?

    If your design was a horrible shitty kludge from the start, you can't "fix" it in any reasonable way with days or weeks left on the timetable and unfinished business to do. You lost your chance months ago. But on the other hand, this gives you a great upgrade featureset for v2: Half the hardware requirements and realtime tracking, just by replacing one stored procedure!

     The developer in the original wtf must base his development philosophy around:

    [image]

    I'm really surprised the DBA didn't force him to put the drop table at the beginning, especially since it didn't work at all for days or weeks without them. I'd smack the guy on the head.

  • (cs) in reply to DigitalLogic
    DigitalLogic:
    What if Itm_Nm is not unique?  You'd receive a different data set with the Group By clause.

    And I am not sure about other vendors as I've only used SQL Server, but with MS you would receive an error: "Itm_Nm is invalid in the selection list because it is not contained in either an aggregate function or the group by clause."


    I haven't yet seen a DB server that would allow it. Here's what a few other DBs return:

    Oracle: [ORA-00979: not a GROUP BY expression]
    MySQL: ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
    PostgreSQL: 42803 GROUPING ERROR

    Well, actually, I don't know how PostgreSQL returns errors... that was just a guess from the manual.


  • anonymous (unregistered)
    Alex Papadimoulis:

    There was little Mike could do to stop this madness, so he ran the "cleanup script" from the developer and recommended that conditional DROP TABLE statements be placed at the beginning of the procedure. "Oh, no no no," the developer replied, "we can't possibly risk altering the code for this procedure; the risk of failure is just too high."

    Thats a good suggestion. Remind me videogames. On a game you refresh the screen just before draw...

    ...
    refresh_screen();//wait for retrace?
    draw();
    ...

     

     

     

  • Yetihehe (unregistered) in reply to brandon

    Why am I thinking it had something to do with French? I work for french clients and they just simply LOVE trailing spaces. Especially when importing from csv:

    column1 ;column2 ;... 

  • Kp (unregistered) in reply to WTF Batman

     

    <snip>I've actually seen code use this in conjunction with an identity field. Inserting them in a certain order makes sure that the identity field's values are assigned in that order. </snip>

     No, no, no!  Go to the back of the class.  Ordering does not ensure IDs get assigned in that order.

  • Fp (unregistered)

    Yep, got an exact app just like that from a 3rd party here in the UK! Taken 3 years to get them to even consider using "real" temp tables as opposed to what they considered temp tables. At first they were dumping them in the system tablespace (Orakel), until we managed to convince them to let us change the processing user's defaults. Processing runs and leaves the tables then another jobs comes in and drops them, absolutely awful but we can't touch it, for fear of voiding our support, what little we actual get out of these clowns.  Just another example of that db-is-a-black-box mentality.

  • john (unregistered) in reply to Marak

    > How could anyone be in charge of developing such a vital stored procedure without KNOWING SQL.

    I suspect he just learned the bare minimum of what he thought he needed to know, and then stuck with the design principles of C/Pascal/Fortran/Basic or whatever else procedural language he knew.  I did the exact same thing when I first learned db stuff -- but then, I wasn't being paid for it, and I did bother to keep learning for a while before I did get paid for it.  Even today, I consider myself a novice.

  • Roganu (unregistered) in reply to Fp

    This reminds me of a previous job. Our system architect had forbidden the use of temp tables (apparently the chance of a tempdb lock was bad) so everyone used these 'temp real' tables instead. Similarly to this wtf, the database performed billing commissions etc, so normally only one user needed to run code, but in busier areas there would be waiting involved when users were locked out of things while waiting for the first user to complete.

    The end result was there could be hundreds of mystery tables floating about the db. The dba wouldn't delete them because he wasn't sure what they did, and many developers had left the company so these tables would often just persist indefinitely. One day someone introduced the idea of prefixing them all with the same letter but by then it was much too late to have an effect.

    The real WTF here is that the script assumed everything was perfect. If you do need to use 'real temp' tables, thats nothing major. It might even be good if you are dealing with millions of records, but how hard is it to place a simple schema check to drop one of these tables if it already exists?

     

     

     

     

     

     



     

  • Mike Dimmick (unregistered)

    If this is SQL Server 2000 or later, table variables are even better than temp tables. Only use temp tables if you need to use the same table in more than one batch on the same connection - temp tables are scoped to a connection, not a procedure. Temp tables also need space in the transaction log. A downside is that you can't SELECT INTO a table variable - you must declare the table variable first, then use INSERT ... SELECT. I think that Alex, in the anonymization process, has transformed a SELECT INTO into INSERT ... SELECT, because SELECT INTO always creates a table.

    The original design causes transaction log explosion as well. All the metadata creation has to be logged, then all the stuff inserted, plus any indexing, and this isn't cleared when the table is dropped, so this happens whether or not the drop table statements run. This also happens for temp tables, but tempdb is always set in Simple recovery mode, so the log space is reused after the transactions commit. If your live database is in Simple recovery mode, that's a WTF of its own, since you can never recover to the point of failure (only to the point of last full backup).

  • Chris (unregistered)

    One of our standard interview questions for developers is "what is the difference between an inner and outer join in SQL?". I can see why now..

Leave a comment on “They're Just Temp Tables”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article