JOIN on a String After Converted to Integer

Question

I am trying to LEFT JOIN on a CHAR after it is converted to an INT. Like this:

SELECT o.title, d.title FROM original o
LEFT JOIN directory d ON CONVERT(o.directory_index, UNSIGNED INT) = d.index

This gives me the correct response if the index is greater than zero. But if the index is zero or an empty string, I get duplicates of the row for every directory title:

o.title       (o.index)    d.title          (d.index)

"Big Boss"    "2"          "OUTER HEAVEN"    2
"Snake"       "0"          "FOX"             0
"Snake"       "0"          "FOXHOUND"        1
"Snake"       "0"          "OUTER HEAVEN"    2
"Kerotan"     ""           "FOX"             0
"Kerotan"     ""           "FOXHOUND"        1
"Kerotan"     ""           "OUTER HEAVEN"    2

So I added a COALESCE statement to convert every empty string to a NULL value:

SELECT o.title, d.title FROM original o
LEFT JOIN directory d ON CONVERT(
    CASE WHEN COALESCE(o.directory_index, '') = '' THEN NULL ELSE o.directory_index END,
    UNSIGNED INT
) = d.index

The COALESCE statement does give me the correct response (I tested the output of the function alone), but now I get NULL for every directory title, unless the index was set to 0, in which case I get the same result as before:

o.title       (o.index)    d.title          (d.index)

"Big Boss"    "2"          NULL              2
"Snake"       "0"          "FOX"             0
"Snake"       "0"          "FOXHOUND"        1
"Snake"       "0"          "OUTER HEAVEN"    2
"Kerotan"     ""           NULL              0
"Kerotan"     ""           NULL              1
"Kerotan"     ""           NULL              2 

What am I doing wrong?

---

As suggested, I got rid of the COALESCE statement, and swapped it out for a NULLIF statement:

SELECT o.title, d.title FROM original o
LEFT JOIN directory d ON CONVERT(
    NULLIF(o.directory_index, ''),
    UNSIGNED INT
) = d.index

But I am met with the same result as if I had the COALESCE statement.

I created a Fiddle of the examples I have provided, and strangely enough it gives me my desired result. But this is not the result I am getting in Workbench. The server is running an older version of MySQL (5.1 I think?) could that be the problem?

---

Okay... so I am pretty embarrassed. I was banging my head against the wall all day yesterday. Then I come in this morning and take a look at it, it turns out what was supposed to be LEFT JOIN ... ON ... = d.index was set to LEFT JOIN ... ON ... = d.title. I am going to up-vote everyone on this thread. But I hope you all down vote me and/or mark the question to be closed.

Answer 1

COALESCE does not replace an empty string with NULL values. Rather, it returns the first non-NULL expression in the list.

To replace an zero length string with a NULL, you could use a variety of expressions...

 NULLIF(expr,'')

 IF(expr='',NULL,expr)

 CASE WHEN expr = '' THEN NULL ELSE expr END

And to convert a string (CHAR, VARCHAR) expression to a numeric value, you could use as shorthand:

expr + 0 

But that's not guaranteed to return an UNSIGNED INT, or even an integer value.

---

ORIGINAL ANSWER below

What's missing here are the definitions of the `directory_index` column from `directory` table and the `index` column from the `original` table.

What are the datatypes of those columns? Are either of them nullable, or is either unique? Is there a foreign key relationship?

It's not possible to answer your question "What am I doing wrong" if we don't know what it is you are trying to achieve.

At first glance, the join condition looks way more complicated than it needs to be.

Can you explain why this doesn't (or wouldn't) work?

SELECT o.title
     , d.title
  FROM original o
  LEFT
  JOIN directory d 
    ON d.directory_index = o.index
 ORDER BY ...

What problem are you trying to solve by adding the functions in the join condition?

You seem to be aware of datatype conversions (from CHAR to INT, etc.) And you seem to be aware that MySQL will do a lot of those datatype conversions implicitly. And you can make those datatype conversions explicit, and can override what MySQL would do by default.

In a numeric context, MySQL will evaluate an empty string as a value of 0, not NULL. As a simple demonstration,

SELECT '' + 0 

But, again, what is it that you are trying to achieve?

This is where a small amount of sample data, and the expected output would clarify the specification.

How to go about achieving that result is really going to depend on the actual datatypes of the columns used in the expressions, and what you want returned.

Any "try this" suggestions we make would just be guesses, not answers.

Answer 2

It's kind'a confusing since your query looks like it's looking o.directory_index...but your data you show o.index (could that be your mistake?) Anyways, try this one.

SELECT o.title,d.title
FROM original o
LEFT JOIN directory d
ON (CASE WHEN o.directory_index IS NULL OR o.directory_index = '' THEN -1
         ELSE CONVERT(o.directory_index,UNSIGNED INT)
    END) = d.index;

sqlfiddle If CONVERT() is the bug in older MySQL Version...maybe try CAST()

SELECT o.title,d.title
FROM original o
LEFT JOIN directory d
ON (CASE WHEN o.directory_index IS NULL OR o.directory_index = '' THEN -1
         ELSE CAST(o.directory_index as UNSIGNED INT)
    END) = d.index;

Since there's a bug with CONVERT() to UNSIGNED INT....why not try CONVERT() to CHAR... like this (might not work but worth a try).

SELECT o.title,d.title
FROM original o
LEFT JOIN directory d
ON o.directory_index = CONVERT(d.index,CHAR);

sqlfiddle