• Is that a suppository up my ass or are you just happy to see me? (unregistered)

    FISTED! FUCK YEAH!

  • Linux User (O/S: Ubuntu Linux 11.10 Oneiric) (unregistered)

    Second!

    Captcha: inhebeo -- i inhibeo my home town

  • Linux User (O/S: Ubuntu Linux 11.10 Oneiric) (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?:
    FRIST! FUCK YEAH!
    FTFY
  • Rob Herbert (unregistered)

    The WTF is obviously that they used a string for an id field. Sheesh!

  • gizmux (unregistered)

    OMG client 13791 is missing... clearly some malformed SQL right there.

  • (cs)

    The stupid way would be to do "select max(id_customer)+1 from customer". They actually managed to find an even stupider way.

    Come to think of it, though, this means that customer records must be pre-created. If you don't create the record first, you cannot enter a new customer.

    Again, I ask: why?

  • vintagepc64 (unregistered) in reply to gizmux

    And what about poor customer #12?

  • (cs) in reply to gizmux
    gizmux:
    OMG client 13791 is missing... clearly some malformed SQL right there.
    You, Good sir, have found the next customer record available for the next new customer.

    TRWTF is selecting * from customer records :p

  • (cs) in reply to vintagepc64
    vintagepc64:
    And what about poor customer #12?
    Not a good customer - DELETED!
  • Smelge the Unsustainable (unregistered)

    I'm not a SQL bod so please forgive my naivety, but isn't this what Autonumber is for?

    Or have I missed the point of what they're trying to achieve?

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

    He died. It was a nice funeral. They had ham sandwiches.

  • (cs) in reply to Smelge the Unsustainable
    Smelge the Unsustainable:
    I'm not a SQL bod so please forgive my naivety, but isn't this what Autonumber is for?

    Or have I missed the point of what they're trying to achieve?

    Who the hell knows what they were doing... It looks like they were trying to select all the column fields from a database, but manually skip the first 13 thousand some odd records (skipping a few numbers here and there). What they did with it after that is anybodys guess.

    According to the story they used that code to create a new record for the next client, but that doesnt really make any sense unless they had a script get the next available record space and then automatically modify the code that stored the query. But yes, a simple AUTO_INCREMENT on database creation would have saved them a Metric Shit-ton of time and processing power for adding a new record to the database.

  • Smelge the Unsustainable (unregistered) in reply to Smelge the Unsustainable
    Smelge the Unsustainable:
    I'm not a SQL bod so please forgive my naivety, but isn't this what Autonumber is for?

    Or have I missed the point of what they're trying to achieve?

    Dammit - I meant [UNIQUEIDENTIFIER]. Too much Access crap in my head.

  • Tim (unregistered) in reply to Severity One
    Severity One:
    The stupid way would be to do "select max(id_customer)+1 from customer". They actually managed to find an even stupider way.

    Come to think of it, though, this means that customer records must be pre-created. If you don't create the record first, you cannot enter a new customer.

    Again, I ask: why?

    the more i think about this, the less it makes sense. if the select statement is kept up to date to include all customers, it never returns any rows; if you first insert the new customer with a dummy ID (999999), the select will just return the dummy customer, but that doesn't help at all with allocating a new ID. I call a hoax on this one.

  • Tim (unregistered) in reply to Smelge the Unsustainable
    Smelge the Unsustainable:
    Smelge the Unsustainable:
    I'm not a SQL bod so please forgive my naivety, but isn't this what Autonumber is for?

    Or have I missed the point of what they're trying to achieve?

    Dammit - I meant [UNIQUEIDENTIFIER]. Too much Access crap in my head.

    haha - you stumbled on the answer to your own question - databases don't implement sequence number generation in a consistent way, so you either write database-specific code or come up with a crap solution (not normally this crap though!)

  • F knows (unregistered) in reply to Tim
    Tim:
    Severity One:
    The stupid way would be to do "select max(id_customer)+1 from customer". They actually managed to find an even stupider way.

    Come to think of it, though, this means that customer records must be pre-created. If you don't create the record first, you cannot enter a new customer.

    Again, I ask: why?

    the more i think about this, the less it makes sense. if the select statement is kept up to date to include all customers, it never returns any rows; if you first insert the new customer with a dummy ID (999999), the select will just return the dummy customer, but that doesn't help at all with allocating a new ID. I call a hoax on this one.

    Maybe that list is only updated after the ID is assigned?

    e.g, you have 5 customers N+1 for each. When customer 6 joins, you do a select * from Customers not containing ID 1-5, and it then returns 6? You then do something with 6, no idea what, and then add that ID to your master list?

    Fuck if I know.

  • Matt (unregistered)

    SELECT MAX 1 * FROM customer left join ( Select '4' AS ID union select '10' union select '11' union select '14' --etc ) as usedCustomerIDs on customer.id_customer = usedCustomerIDs.id where usedCustomerIDs.id is null order by customer.id_customer DESC

    Fixed for you

  • Anon (unregistered)

    Forgetting the sheer stupidity of this solution, why are the customer id's assumed to be +3 each time?!

  • Stev (unregistered) in reply to Anon
    Anon:
    Forgetting the sheer stupidity of this solution, why are the customer id's assumed to be +3 each time?!

    Not sure you're reading it right? Each "line" is 3 customers, not one.

  • asdf (unregistered)

    Not to mention != operator usually cannot use index.

  • Ziplodocus (unregistered)

    The only way I can see this working is if there is already a pre-poulated table of customer ids, but which are not assigned to anyone. This query could be generated dynamically to select everything but the lowest not used or deleted customer id.

    SELECT * FROM dictionary WHERE Word != 'Aardvark' AND...

  • (cs) in reply to Ziplodocus

    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 );
    
  • Jesper (unregistered)

    So, is this a hand-written SQL statement, or is this generated by some badly programmed piece of software?

    It would be a bigger WTF if it's the first of those two.

  • Ziplodocus (unregistered) in reply to Jesper

    Try telling that to the guy(s) employed to add the next customer number to the un-select query everytime a new customer is made.

  • Geoff (unregistered) in reply to Rob Herbert

    Even if they did you'd think someone would have introduced a wild idea like using some scalar functions ISNULL(),CAST( AS [type]), maybe COALESCE() by now. Still might not be pretty, or performance wise but its got be better than an ever growing string of AND(s) with conditionals comparing strings

  • Dr Doom (unregistered) in reply to Smelge the Unsustainable

    Not enterprisey enough. Why opt for using perfectly good unique values out of the box when you can spend months writing your own implementation?

  • Anon (unregistered) in reply to Stev
    Stev:
    Anon:
    Forgetting the sheer stupidity of this solution, why are the customer id's assumed to be +3 each time?!

    Not sure you're reading it right? Each "line" is 3 customers, not one.

    Yep, your right. Being rather slow today!

  • Le Forgeron (unregistered)

    Let me guess...

    They do generate automatically the customer ID when INSERTing the pieces of data in the table.

    The only problem is that they do not know which ID the SQL dabatase will be using, so let's now retrieve all the table, without the customer we already know of.

    The select should return only one record line: the new customer. After visual check and communication of the ID to the customer (well, it's modern, we send an email or print it on a nice card), the statement will be updated.

    Of course, there is only one new customer at a time: it's centralised process, done only by Bob the Database-man. (Joe is able to do it too, but only when Bob is on vacation. Anyway, there is only one console here)

  • Doug (unregistered)

    My guess: there is a script with a loop that appends the next numbered id and reruns the query until it finally fails, and that gives the next id. It worked like a charm the first time and nobody looked at it again. Because with this style, there is always another fire to put out.

  • (cs)

    In County Hyderabad, customer #12 office had accident.

    [image]
  • (cs) in reply to Lockwood
    Lockwood:
    In County Hyderabad, customer #12 office had accident.

    [image]

    Hyderabad is in Ireland?

  • pinipple (unregistered)

    Well I feel more valued as a customer knowing that the database admin knows all customers by id.

  • Jerry (unregistered) in reply to PiisAWheeL
    PiisAWheeL:
    ...a script get the next available record space and then automatically modify the code that stored the query.
    Ahh, yes, you haven't lived until you've written some self-modifying code!

    Go ahead, try it now.

    You know you want to.

    No one will ever know.

    What could it hurt?

  • Frank (unregistered)

    I'm sorry, Ruben, but you misunderstood the purpose of that code. It is not for finding the next available customer ID. Rather it is used when preparing to do a mass mailing about a new product. The SELECT is simply a cost saving measure, to skip mailing to those customers who have told us they would never buy from us again even if we were the last company on Earth.

  • F (unregistered) in reply to Steve The Cynic
    Steve The Cynic:
    Lockwood:
    In County Hyderabad, customer #12 office had accident.

    [image]

    Hyderabad is in Ireland?

    Yes. It's pronounced "Cork". Irish spelling is a little strange.

  • Jerry (unregistered) in reply to Jerry
    Jerry:
    PiisAWheeL:
    ...a script get the next available record space and then automatically modify the code that stored the query.
    Ahh, yes, you haven't lived until you've written some self-modifying code!

    Go ahead, try it now.

    You know you want to.

    No one will ever know.

    What could it hurt?

    Come on, what are you waiting for?

    It would be soooo cool!

  • John Preston (unregistered)

    Holy crap people, this is why we have NOT IN statements. Talk about taking the long way around.

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

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

  • Omniscient (not legally binding) (unregistered) in reply to Tim
    Tim:
    Smelge the Unsustainable:
    Smelge the Unsustainable:
    I'm not a SQL bod so please forgive my naivety, but isn't this what Autonumber is for?

    Or have I missed the point of what they're trying to achieve?

    Dammit - I meant [UNIQUEIDENTIFIER]. Too much Access crap in my head.

    haha - you stumbled on the answer to your own question - databases don't implement sequence number generation in a consistent way, so you either write database-specific code or come up with a crap solution (not normally this crap though!)
    Is there a "crap" solution that's anywhere near safe? I believe (correct me on this) that max(id)+1 is safe if you use a transaction around the select and the insert, but doesn't that start getting you into platform-specific code anyway?

  • Omniscient (not legally binding) (unregistered) in reply to Omniscient (not legally binding)

    P.s. that should have been "you AND EVERYONE ELSE use transactions".

  • Tud (unregistered)

    They should have written a Python script that calls 'SELECT *' first, to know how many customers are there in the records, then automatically generates a query excluding all rows except the last one. Would make it much more portable.

    Wait, there's no need to 'SELECT *'. Just manually read the database file to check how many rows it has. Much more efficient.

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

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

    Perhaps she found the next customer ID.

  • (cs) in reply to draNgNon
    1. Select MAX()+1 is a really bad idea. Far too many ways to get non sequential, non gapping numbers.

    2. More likely this is a generated statement. I have seen many ORM's do something like this. [Which supports the view that one must always review the SQL used by ORMs, and IMHO for many cases, this is sufficient to use some other approach)

  • (cs)

    The real WTF is that there's an extra space before the case where the id != 4. That throws the aesthetic appeal of the page on its face. Needs immediate attention.

  • (cs)

    It seems like a huge gaping lack of knowledge. If you DIDN'T KNOW that a customer insert proc could simply return the ID number it generated to the calling code, you might consider something vaguely approaching this. Not this. But something vaguely approaching this.

  • xxx (unregistered) in reply to zelmak

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

    Now tell me how will you get the id of newly created customer? Select it by name or etc?

  • (cs) in reply to asdf
    asdf:
    Not to mention != operator usually cannot use index.

    Sure it can. Why do you say that?

    http://weblogs.sqlteam.com/dang/archive/2009/03/07/Low-Hanging-Fruit-of-Sargable-Expressions.aspx

    They might have an issue with passing in strings for their id field, assuming it's really an int, but probably not. Of course a 13,000 OR condition case may not use an index no matter.

  • visualbasucks (unregistered)

    could be random sequential number.

    if it isnt select max(id_customer) from customer instead of a generator function and wondering where off by one came from.

    or a /dev/random readout and one did select count(id_customer) from customer where id_customer= <ID>) with result not being zero and then some insert

  • Antony (unregistered)

    It's sad to see so many "fixed it" type comments advocating "max Id from table" and the like. Has no one here ever worked on a system with more than one concurrent user?

  • (cs) in reply to zelmak
    zelmak:
    SELECT CUSTOMER_ID_SEQ.NEXT_VAL FROM DUAL;
    

    So DUAL really is the "dummy" table in Oracle? I did have a bit of Oracle training at my last job, but I didn't really believe the guy who was teaching when he said you had to select from DUAL if you wanted to just do calculations without any actual table data - why in the world would you need a dummy table? And even if you did, why would it be named DUAL?!?

Leave a comment on “The Sentimental Query”

Log In or post as a guest

Replying to comment #:

« Return to Article