next »

[perkiset]

I just performed this query:

SELECT CONCAT( fname, '|', lname, '|', email, '|', prime_city, '|', prime_state, '|', prime_zip ) 
FROM contacts
WHERE id =1

... and I get nothing from my tables, even though there is a record with an ID of 1.

On a hunch after looking at the data - I did this:

SELECT CONCAT( fname, '|', lname, '|', email) 
FROM contacts
WHERE id =1

... excluding the fields that had a NULL value. It worked perfectly. So I did a bunch of other tests, and sure enough, of I preform a query with the CONCAT in either a field-select position or the WHERE clause and any of the fields are null, it does not recognize the row and I get back bad results.

This seems REALLY weird to me... or is this normal behavior in SQL?

[KaptainKrayola]

does mysql have a built in function that will swap in another value if the column you are selecting is null?  t-sql has one (i think) but the Kaptain can't for the life of him remember what it is just that he had to use it for something once.

another utterly useless post by the Kaptain...

[thedarkness]

LOL, you too huh Perk, we move in the same circles.....   

This is by design.

Use CONCAT_WS() instead or wrap NULLable paremeters with IFNULL() function.

I think concat is a mysql construction and not part of SQL (i could easily be wrong) so they can do whatever they bloody well like ;-)

Cheers,
td

[perkiset]

Never heard of that one... and looking at the MySQL dox it's a clusterPhuque - CONCAT stops evaluation of the job if ANY item is NULL, CONCAT_WS simply ignores any NULLs, but does not kill the entire line. Why the difference? What where they smoking?

And how the HELL did you find that one? 

[thedarkness]

It was in the mysql version of bugzilla  reported as a bug 

Reply was the good old "this behaviour is by design, please read the documentation and the docs on submitting bugs"..... it's a "feature" 

Have you tried wrapping the paramters with the ifnull() function? using your initial example you would do something like the following;

SELECT CONCAT( fname, '|', lname, '|', email, '|', IFNULL(prime_city, 'Joliet'), '|', IFNULL(prime_state, 'Illinois'), '|', IFNULL(prime_zip, '60436' ) FROM contactsWHERE id =1

Cheers,
td

P.S. Found the initial report that led me on the path to righteousness, amen, yea verily http://bugs.mysql.com/bug.php?id=480

[KaptainKrayola]

ifnull is basically what the Kaptain was getting at above just couldn't remember what it was - thanks TD now we can finally sleep better at night

[perkiset]

P.S. Found the initial report that led me on the path to righteousness, amen, yea verily http://bugs.mysql.com/bug.php?id=480

Actually, it enraged me off so mightily that I smote down the vile null fields and made them required-default-emptys instead. I have no need for them to be optionally null, so fuck it. Works like a charm now 

[thedarkness]

ifnull is basically what the Kaptain was getting at above just couldn't remember what it was - thanks TD now we can finally sleep better at night