Comment On The Sentimental Query

FISTED! FUCK YEAH!

Second!

Captcha: inhebeo -- i inhibeo my home town

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

FRIST! FUCK YEAH!

FTFY

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

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

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?

And what about poor customer #12?

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

vintagepc64:

And what about poor customer #12?

Not a good customer - DELETED!

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?

vintagepc64:

And what about poor customer #12?

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

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:

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.

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.

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

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.

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

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

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.

Not to mention != operator usually cannot use index.

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

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

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.

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.

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

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

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!

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)

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.

In County Hyderabad, customer #12 office had accident.

Lockwood:

In County Hyderabad, customer #12 office had accident.

Hyderabad is in Ireland?

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

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?

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.

Steve The Cynic:

Lockwood:

In County Hyderabad, customer #12 office had accident.

Hyderabad is in Ireland?

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

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!

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

vintagepc64:

And what about poor customer #12?

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

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?

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

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.

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.

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)

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.

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.

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?

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.

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

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?

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