2

Is this a bug in MySQL (5.1.41-community) -- 

CREATE TEMPORARY TABLE zztest (stuff varchar(20) NULL)

INSERT INTO zztest
SELECT
    'test'
FROM (
    select '1' as testcol
    union
    select null
) a
WHERE **IF(a.testcol IS NULL,'',a.testcol)** IS NOT NULL

This is a simplified query. The IF expression between the **'s was supposed to be bolded. It comes from a dynamic query, where The IF expression was innocently placed into a query.

The select by itself works. When combined with the insert statement it did not work. The inner select is designed to show that the temporary table created can have nulls in the column being checked.

The issue was "fixed" by replacing the IF with an IFNULL(a.testool, '')

Some other replacesments fail too (whereas the selects by themselves work):

1  IF (a.testcol, '', a.testcol)
2  IF (a.testcol, '', '')
3  IF ('', '', '')

Does this bug exist in other version of MySQL?

Edit -- Results of query with select:

Message: Error Code: 1292. Truncated incorrect INTEGER value: ''

The results it is supposed to have are that two 'test' strings are input into the zztest table.

Edit -- I tried it out again after about 2 hours. The last two statements "worked" (remember to plug them into the query above..). The first replacement statement failed. Then numbers 2 and three failed again.

So it looks like statement 1 is the culprit (and the original, it errors out now..).. And perhaps some kind of bad state exists where statements that produce the same intermediary results also fail.

So a follow up question then is -- is there a way to make sure that the query is reset?

Just after I tried to execute the query in a new connection, workbench crashed.. Related?

edit -- found that an explicit cast works:

IF (a.testcol, cast('' as char), cast('' as char))

So I've found my way around it. The question is -- why would '' (with anything within the quotes) be interpreted as an INTEGER? I even got it to be interpreted as a DOUBLE by concat-ing ''.

And why did it work sometimes, and not others? <-- this part sucks the mostess.

Gerard ONeill
  • 3,914
  • 39
  • 25

1 Answers1

2

This: 

IF(a.testcol IS NULL,'',a.testcol)

never become null, and your condition:

WHERE IF(a.testcol IS NULL,'',a.testcol) IS NOT NULL

will return always all rows.

When testcol is null you assigning empty string to it: ''

Empty string is diffrent from NULL and this is not a bug.

You should use something like this:

SELECT testcol FROM
(
    SELECT '1' AS testcol
    UNION
    SELECT null
) a

WHERE IF(a.testcol IS NULL, NULL ,a.testcol) IS NOT NULL

or:

SELECT testcol FROM
(
    SELECT '1' AS testcol
    UNION
    SELECT null
) a

WHERE ISNULL(a.testcol) = true

ISNULL just returns "true" when argument is null.

Kamil
  • 13,363
  • 24
  • 88
  • 183
  • `ISNULL(a.testcol) = true` isn't MySQL... is it? Couldn't you just do `WHERE a.testcol IS NULL`? – gen_Eric Feb 19 '13 at 22:55
  • @RocketHazmat ISNULL works in MySQL, and you can use just IS NULL, but I wanted to show him why his IF doesn't work, and I dont know how he want to use it (i assume that he showed us very simplified code). – Kamil Feb 19 '13 at 23:07
  • I didn't know about `ISNULL`. But wouldn't it then be `WHERE ISNULL(a.testcol)`? I didn't think MySQL had `true`/`false` keywords. – gen_Eric Feb 19 '13 at 23:11
  • @RocketHazmat TRUE and FALSE are synonyms of 1 and 0. More information here: http://stackoverflow.com/questions/10852337/true-false-vs-0-1-in-mysql – Kamil Feb 19 '13 at 23:19
  • @RocketHazmat programming language without TRUE/FALSE? Weird idea :) – Kamil Feb 19 '13 at 23:36
  • Thanks guys, I know my SQL. I wasn't asking for a *better* way of doing it. This if expression was plugged in as a formula. The intent of the formula was to eliminate the null. The query it plugged into cannot assume that the expression plugged in would not equal NULL. – Gerard ONeill Feb 20 '13 at 02:35