1

I'm maintaining a database with InnoDB tables. These tables have some columns of type (from show create table):

`val0` varchar(30) default NULL,
`val1` varchar(30) default NULL,
etc...

From the mysql command line I am searching for NULL entries in these tables.

Now I SELECTed some rows where val0 IS NULL AND val1 = '' (empty string), but the mysql command line shows both values as

+------+------+
| val0 | val1 |
+------+------+
| NULL | NULL |
+------+------+

Apparently this is confusing me. Is this behavior intentional? Is there a method to define how empty strings are displayed?


EDIT :

I should mention that STRICT MODE (see the docs) seems to be enabled.

The query

SELECT * FROM table WHERE val0 IS NULL AND val1 = '';

finds the row above. The query

SELECT * FROM table WHERE val0 IS NULL AND val1 IS NULL;

doesn't find that row. Still the '' is displayed as 'NULL'. How do I change that?

Server version is "5.0.22-log".


EDIT 2 :

Here is the full (anonymized) SHOW CREATE TABLE mytable:

CREATE TABLE `mytable` (
  `id` int(11) NOT NULL,
  `time` int(20) NOT NULL,
  `val0` varchar(30) default NULL,
  `val1` varchar(30) default NULL,
  `val2` varchar(30) default NULL,
  `val3` varchar(30) default NULL,
  `val4` varchar(30) default NULL,
  `val5` varchar(30) default NULL,
  `val6` varchar(30) default NULL,
  `val7` varchar(30) default NULL,
  `val8` varchar(30) default NULL,
  `val9` varchar(30) default NULL,
  `status` tinyint(4) default '0',
  UNIQUE KEY `idtime` (`id`,`time`),
  KEY `status` (`status`),
  KEY `id` (`id`),
  KEY `time` (`time`),
  CONSTRAINT `mytable_ibfk_1` FOREIGN KEY (`id`) REFERENCES `idtable` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Here are my queries:

select * from mytable where val1 is null limit 1;

-> no result

select * from mytable where val1 = '' limit 1;

result:

+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+
| id  | time       | val0 | val1 | val2 | val3 | val4 | val5 | val6 | val7 | val8 | val9 | status |
+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+
| 300 | 1346855716 | 0    | NULL | 42   | 4711 | blib | blub | NULL | NULL | NULL | NULL |      5 |
+-----+------------+------+------+------+------+------+------+------+------+------+------+--------+

fun fact: These last four NULLs are actually the string "NULL". (I'm only maintaining this system, I didn't build it...)

  • Which version of MySQL? – ypercubeᵀᴹ Sep 5 '12 at 9:48
  • @ypercube server version: 5.0.22-log – Martin Hennings Sep 5 '12 at 9:50
  • Looks like the first comment here: sql-info.de/mysql/gotchas.html#1_1 - "If you define a column as NOT NULL and query the table with a IS NULL condition this should return no rows. But MySQL knows that the column cannot be NULL, thus simply "optimizes" the IS NULL condition away and returns all rows!!!!" – JohnP Sep 5 '12 at 11:30
  • @JohnP But the columns are not defined as NOT NULL. They even have NULL as default value. – Martin Hennings Sep 5 '12 at 11:35
  • Does select null val0,'' val1 also display the same output as show in your post (NULL | NULL)? – miracle173 Sep 5 '12 at 14:13
2

Bug #20067 empty string returns NULL as dispalyed value

This bug is possibly fixed 5.0.23. Look at the document referenced by the above page.

  • Thanks! I guess I'll have to ask the client to update or I'll just live with it then. – Martin Hennings Sep 5 '12 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.