0

I want to create a temporal table from a SELECT statement in MySQL. It involves several JOINs, and it can produce NULL values that I want MySQL to take as zeroes. It sounds like an easy problem (simply default to zero), but MySQL (5.6.12) fails to elicit the default value.

For example, take the following two tables:

mysql> select * from TEST1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    4 |   25 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from TEST2;
+------+------+
| b    | c    |
+------+------+
|    2 |  100 |
|    3 |  100 |
+------+------+
2 rows in set (0.00 sec)

A left join gives:

mysql> select TEST1.*,c from TEST1 left join TEST2 on TEST1.b=TEST2.b;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |  100 |
|    4 |   25 | NULL |
+------+------+------+
2 rows in set (0.00 sec)

Now, if I want to save these values in a temporal table (changing NULL for zero), this is the code I would use:

mysql> create temporary table TEST_JOIN (a int, b int, c int default 0 not null)
       select TEST1.*,c from TEST1 left join TEST2 on TEST1.b=TEST2.b;
ERROR 1048 (23000): Column 'c' cannot be null

What am I doing wrong? The worst part is that this code used to work before I did a system-wide upgrade (I don't remember which version of MySQL I had, but surely it was lower than my current 5.6). It used to produce the behavior I would expect: if it's NULL, use the default, not the frustrating error I'm getting now.

From the documentation of 5.6 (unchanged since 4.1):

Inserting NULL into a column that has been declared NOT NULL. For multiple-row INSERT statements or INSERT INTO ... SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. INSERT INTO ... SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT, no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)

My current workaround is to store the NULL values in the temporal table, and then replace them by zeroes, but it seems rather cumbersome with many columns (and terribly inefficient). Is there a better way to do it?

BTW, I cannot simply ignore some columns in the query (as suggested for another question), because it's a multirow query.

Community
  • 1
  • 1
Luis M Rodriguez-R
  • 1,212
  • 11
  • 10

1 Answers1

1
IFNULL(`my_column`,0);

That would set NULLs to 0. Other values stay as is.

Just wrap your values/column names with IFNULL and it will convert them to whatever default value you put into the function. E.g. 0. Or "european swallow", or whatever you want.

Then you can keep strict mode on and still handle NULLs gracefully.

Buttle Butkus
  • 9,206
  • 13
  • 79
  • 120
  • 2
    [`IFNULL()`](http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull) takes *two* arguments. Here very probably the column name, and then the default value. – Sylvain Leroux Aug 14 '13 at 21:04
  • OOps, thank you. I fixed it in my answer. I just noticed you made an edit, but I think mine was similar. Thanks again for pointing that out. – Buttle Butkus Aug 14 '13 at 21:06
  • This solved it: create temporary table TEST_JOIN (a int, b int, c int default 0 not null) select TEST1.*,IFNULL(c,0) as c from TEST1 left join TEST2 on TEST1.b=TEST2.b; – Luis M Rodriguez-R Aug 14 '13 at 21:10