• Henry (unregistered) in reply to Peter Gordon

    I agree, although it's obvious that whoever posted this story didn't quite understand that.

    Also, what kind of crappy DB setup are they using that "almost brings down the server" on a mere 13000 compares? I understand if the db rejects the statement as too long, but 13000 comparisons on an id field should be barely noticeable.

  • populus (unregistered) in reply to Is that a suppository up my ass or are you just happy to see me?
    Is that a suppository up my ass or are you just happy to see me?:
    FISTED! FUCK YEAH!

    Another great mind

  • populus (unregistered) in reply to Linux User (O/S: Ubuntu Linux 11.10 Oneiric)
    Linux User (O/S: Ubuntu Linux 11.10 Oneiric):
    Second!

    Captcha: inhebeo -- i inhibeo my home town

    And another great mind

  • populus (unregistered)

    Probably because idiots used text for PK, and didn't know about casting...

  • iToad (unregistered)

    Isn't there some kind of upper limit on how long a SELECT statement can be, or how many WHERE conditions it can contain?

  • (cs) in reply to iToad

    If so, start to UNION more stuff in!

  • Bananas (unregistered)

    Ouch! This one literally took my breath away :-(

  • Jay (unregistered)

    Hey, quiet guys! If they find out about sequences and autonumber fields, what happens to the poor guy in Kazakhstan who updates the query every time a new customer is added? Are you trying to put him out of a job?

  • Cbuttius (unregistered)

    This is a very typical situation where you are not allowed to add a column to a database table because it will break things.

    The alternative options should be to add another table, with customer_id as the key and then join it with the other table, but even then creating a new table is considered nearly taboo in some places.

    The query is likely to have been script-generated someway rather than hand-written.

    It is of course highly inefficient compared to using an extra column or table with join.

  • Jay (unregistered) in reply to asdf
    asdf:
    >> Not to mention != operator usually cannot use index. > Sure it can. Why do you say that?

    Because when that condition returns almost all rows, DB engine will not use such index, it would be pointless. Index will be used only when it shrinks result set significantly, otherwise just ignored and do the sequential scan.

    I ran into a mildly amusing case of this once. We had a table with an "exported" field, defined as boolean. Normally almost all the records are exported and only a few new ones are not. The query to find the unexported records said "where exported != true". This took forever to run. I discovered that it was doing a sequential search of the entire table. I changed it to "where exported = false" and it ran in a millisecond, because now it used the index.

  • Cbuttius (unregistered)

    Of course if all you want is the next available customer_id, use an identity column as the primary key.

  • Cbuttius (unregistered)

    Of course if all you want is the next available customer_id, use an identity column as the primary key.

  • (cs)

    ORM would have solved this problem completely and wholly. No need for silly sql to compound problems where none should exist only in the first place.

  • nfg (unregistered) in reply to Scarlet Manuka
    Scarlet Manuka:
    John Preston:
    Holy crap people, this is why we have NOT IN statements. Talk about taking the long way around.
    Nah, you can't use more than 1,000 values in a NOT IN clause. At least in Oracle.
    ekolis:
    So DUAL really is the "dummy" table in Oracle? [...] - why in the world would you need a dummy table? And even if you did, why would it be named DUAL?!?
    It's derived from the usage of the term "dual" in mathematics. I suppose it simplifies the syntax parser if calculations take the same form as table queries.
    herbie:
    It doesn't make sense to select * when we are creating a new record. It doesn't even make sense to look for a record we haven't made yet.[...] I can (almost) believe this sort of code exists somewhere in the universe, but the usage as presented in the article simply doesn't make sense. at all. even as a WTF.
    People complaining that it doesn't make sense to look for a new customer ID this way are overthinking it. Of course it doesn't make sense, but you shouldn't expect it to make sense, because there is no situation in which this query would make sense. Picking on "select *" is ridiculous - we see situations all the time on this site where "select *" is used to retrieve a single value. At least they put a WHERE clause in this query, even if it was a stupid one; that already puts them ahead of some of the contributions we've seen.

    I can imagine a design that does things this way. It goes without saying that it's a horrible and broken design, of course. But I wouldn't rule the possibility out entirely.

    Who's picking on "select *"? I thought the OP's point was that you're using select to find an ID you haven't created yet....

  • geoffrey, MCP, PMP (unregistered) in reply to PiisAWheeL
    PiisAWheeL:
    geoffrey:
    PiisAWheeL:
    geoffrey:
    OK, I get that "select *" means "poor performance," but is that really a WTF-worthy article? We must really be scraping the bottom of the bad code samples barrel.

    You are trolling right? Like... did you miss the 4500 lines of while != code...? The select * is just the icing on this one.

    We have no context around the != code. It could be generated. The execution plan would look the same whether you include a myriad of inequality operators or a NOT IN statement. So what difference does it make how it looks? Neither has much asthetic appeal, anyway, so you pick a solution and go with it. That's no WTF, just reality.

    != or NOT IN is not the problem here. Its the manually skipping all the used record spaces. There is a plethora of solutions in my head I can think of that would use a quite a bit less processing power, quite a bit less code, and quite a bit less... WTF. Hell... I can think of about 7 different contexts that this code would be used in and come up with a solution that is much much better. This is the kind of solution you develop when you hate your client with a passion. Its almost a code bomb. If this is your idea of reality, then you sir, have a broken concept of what reality is.

    I guess we'll just have to agree to disagree on that one.

  • Mani (unregistered) in reply to Enduriel
    Enduriel:
    On the subject that this query might be used to get the last inserted id of an insert: There are WAY better ways to achieve this in a transaction than doing anything remote to that.

    For example, off the top of my head I know that last_insert_id returns the id that the last insert statement generated in mysql. A google search resulted in @@IDENTITY for Sql Server. And I am quite optimistic that in all major databases some feature like this exists.

    So there is no excuse to this query.

    For this query, no, but...... there are legitimate times the last_insert_id function in mySQL can break / not act as expected. For example, try this: have a table with an auto-generated id. Put 3 items in there, one by one. Run last_insert_id and you will get 3. Now use one line to insert three more rows (as opposed to individually as before). Now run last_insert_id. What do you expect? 6. What do you actually get from the function? 4.

    Thats because that function only returns the FIRST Id it generated for your connection for multiple inserts in the same line - not the last, not a listing, but only the first. If you were relying on the built-in functionality your code would be borked.

    To me, more surprising than people not using the built-in functions are the people who assume the built in functions always act as expected in typical situations.

  • (cs) in reply to Tasty
    Tasty:
    vintagepc64:
    And what about poor customer #12?

    That's Inspector #12, and she found something in the underwear.

    It was Matthew Broderick, inspecting her gadget.

  • bob (unregistered)

    It is critical that they leave space incase they need to insert a customer, but they will only know where when it happens.

  • noone (unregistered) in reply to Antony

    The real WTF is that (at least in previous versions) you could insert into it which would break about everything (a SELECT 4*3 FROM DUAL would now return two rows).

  • Bidual (unregistered) in reply to noone

    dual perversion

    http://awads.net/wp/2005/11/08/insert-into-dual/

    and THE solution:

    DECLARE

    CURSOR C IS SELECT Customer_Id FROM Customer ORDER BY Custormer_Id;

    R C%ROWTYPE;

    ID VARCHAR2(500);

    BEGIN

    OPEN C; -- OPEN & FETCH & WHILE & CLOSE rather than FOR...LOOP (reading comprehension) FETCH C INTO R; WHILE C%FOUND LOOP

    ID:=R.Customer_Id;
FETCH C INTO R;

    END LOOP;

    CLOSE C;

    INSERT INTO Customer_Max_Id VALUES (ID);

    END; /

    And then, fetch just ONE!! REGISTRY with your java app this silly table...

  • Bidual (unregistered)

    sorry... commit forgotten

  • Ben (unregistered)

    I guess those wondering why you would ever want to do this have never worked on a legacy system. In the days before disk space was cheap, people used to use ID numbers with as small a range as possible. If the programs are tightly coupled to the database then it's often easier to implement a way of re-using old numbers than to increase the field size throughout the whole system. Particularly on something like customer number which will be everywhere! I bet this is why certain numbers (like 12) are 'missing' from the query.

    Having said that there are more scalable ways to achieve this. I'm just not at all surprised that code like this exists and auto_increment doesn't solve all problems in the real world where cost to implement is a factor.

  • Anonymous (unregistered)

    I once worked on a database with seemingly random number assigned to client, it made no sense why they would not use auto id, then one day i had an epiphany and realized they were the clients phone numbers, none in the development crew had any idea, when i exposed this to the sale team they answered something along the line of; but of course how else would we remember all those numbers...

  • lollan (unregistered)

    Now that's just sad. Be brage Ruben ! Good luck

  • ballymcgash (unregistered) in reply to Steve The Cynic

    yes people steal in ireland too in this case an ATM - see the police officer? ...and they didn't use the digger as a getaway car either so sorry dudes nothing hilariously stupid happened on this occasion in ireland

  • abdullah (unregistered) in reply to zelmak
    zelmak:
    Must've been Oracle ... no autonumber, autoindex, autoincrement ... whatever ... fields.

    They just needed to learn about sequences.

    INSERT INTO CUSTOMERS ( ID, NAME, ETC ) 
  VALUES ( CUSTOMER_ID_SEQ.NEXT_VAL, NewName, OtherInfo );

    My favorites, tho, are those yayhoos who do

    SELECT CUSTOMER_ID_SEQ.NEXT_VAL FROM DUAL;
// do stuff to grab the number from the previous query
INSERT INTO CUSTOMERS ( ID, NAME, ETC )
  VALUES ( NewCustomerID, NewName, OtherInfo );

    Don't mock this technique, it works 99% of time. Not bad odds.

  • withheld (unregistered)

    argh, they're not even sparse... not that it would matter...

Leave a comment on “The Sentimental Query”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article