Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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...)

share|improve this question
    
Which version of MySQL? –  ypercube Sep 5 '12 at 9:48
    
@ypercube server version: 5.0.22-log –  Martin 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 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
show 2 more comments

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks! I guess I'll have to ask the client to update or I'll just live with it then. –  Martin Sep 5 '12 at 14:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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