The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. October 14, 2019, 06:50:03 PM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: MySQL Concatenation Bug?  (Read 5863 times)
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« on: April 25, 2007, 04:49:13 PM »

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

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #1 on: April 25, 2007, 07:28:03 PM »

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... Need Help
Logged

We can't stop here, this is bat country.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #2 on: April 25, 2007, 08:07:26 PM »

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

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
« Last Edit: April 25, 2007, 08:09:03 PM by thedarkness » Logged

"I want to be the guy my dog thinks I am."
 - Unknown
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: April 25, 2007, 10:03:53 PM »

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?  ROFLMAO
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #4 on: April 26, 2007, 12:02:03 AM »

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

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

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
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #5 on: April 26, 2007, 07:55:24 AM »

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
Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #6 on: April 26, 2007, 01:54:35 PM »

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
ROFLMAO

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  Wink
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
thedarkness
Lifer
*****
Offline Offline

Posts: 585



View Profile
« Reply #7 on: April 26, 2007, 04:11:05 PM »

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

 ROFLMAO
Logged

"I want to be the guy my dog thinks I am."
 - Unknown
qweqwe
Rookie
**
Offline Offline

Posts: 14


View Profile
« Reply #8 on: January 12, 2010, 07:25:02 PM »

<spammer dick go boom>
« Last Edit: January 12, 2010, 08:33:06 PM by nutballs » Logged

No links in signatures please
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!