The Daily WTF: Curious Perversions in Information Technology

2012-02-20 Reply Admin

FISTED! FUCK YEAH!

2012-02-20 Reply Admin

Second!

Captcha: inhebeo -- i inhibeo my home town

2012-02-20 Reply Admin

Is that a suppository up my ass or are you just happy to see me?:
FRIST! FUCK YEAH!

FTFY

2012-02-20 Reply Admin

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

2012-02-20 Reply Admin

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

Severity One · 2012-02-20 Reply Admin

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?

2012-02-20 Reply Admin

And what about poor customer #12?

PiisAWheeL · 2012-02-20 Reply Admin

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

PiisAWheeL · 2012-02-20 Reply Admin

vintagepc64:
And what about poor customer #12?

Not a good customer - DELETED!

2012-02-20 Reply Admin

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?

2012-02-20 Reply Admin

vintagepc64:
And what about poor customer #12?

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

PiisAWheeL · 2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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!)

2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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

2012-02-20 Reply Admin

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

2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

Not to mention != operator usually cannot use index.

2012-02-20 Reply Admin

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...

zelmak · 2012-02-20 Reply Admin

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 );

2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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

2012-02-20 Reply Admin

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

2012-02-20 Reply Admin

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!

2012-02-20 Reply Admin

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)

2012-02-20 Reply Admin

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.

Lockwood · 2012-02-20 Reply Admin

In County Hyderabad, customer #12 office had accident.

[image]

Steve The Cynic · 2012-02-20 Reply Admin

Lockwood:
In County Hyderabad, customer #12 office had accident.
[image]

Hyderabad is in Ireland?

2012-02-20 Reply Admin

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

2012-02-20 Reply Admin

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?

2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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!

2012-02-20 Reply Admin

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

2012-02-20 Reply Admin

vintagepc64:
And what about poor customer #12?

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

2012-02-20 Reply Admin

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?

2012-02-20 Reply Admin

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

2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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.

TheCPUWizard · 2012-02-20 Reply Admin

Select MAX()+1 is a really bad idea. Far too many ways to get non sequential, non gapping numbers.
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)

Matt Westwood · 2012-02-20 Reply Admin

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.

wbrianwhite · 2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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?

wbrianwhite · 2012-02-20 Reply Admin

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.

2012-02-20 Reply Admin

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

2012-02-20 Reply Admin

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?

ekolis · 2012-02-20 Reply Admin

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?!?

The Sentimental Query

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

Leave a comment on “The Sentimental Query”