MySQL problem: SELECT * FROM event WHERE name is null ;

Howard_L · #1 03-Aug-2008, 01:54

I had a newline at the end of my input file and now have this table:

Code:

mysql> select * from event;
+----------+------------+----------+-----------------------------+
| name     | date       | type     | remark                      |
+----------+------------+----------+-----------------------------+
| Chirpy   | 1999-03-21 | vet      | needed beak straightened    |
| Bowser   | 1991-10-12 | kennel   | NULL                        |
| Fang     | 1991-10-12 | kennel   | NULL                        |
| Whistler | 1998-12-09 | birthday | First birthday              |
| NULL     | NULL       | NULL     | NULL                        |
+----------+------------+----------+-----------------------------+

I have a problem when trying to delete by the column "name".
I can:
mysql> select * from event where name = 'Fang' ;
+------+------------+----------+--------------------------+
| name | date       | type     | remark                   |
+------+------------+----------+--------------------------+
| Fang | 1991-10-12 | kennel   | NULL                     |
+------+------------+----------+--------------------------+

mysql> select * from event where remark is null ;
+--------+------------+--------+--------+
| name   | date       | type   | remark |
+--------+------------+--------+--------+
| Bowser | 1991-10-12 | kennel | NULL   |
| Fang   | 1991-10-12 | kennel | NULL   |
| NULL   | NULL       | NULL   | NULL   |
+--------+------------+--------+--------+

mysql> select * from event where date is null ;
+------+------+------+--------+
| name | date | type | remark |
+------+------+------+--------+
| NULL | NULL | NULL | NULL   |
+------+------+------+--------+

But I don't seem to be able to do this:
mysql> select  *  from event where name is null ;
Empty set (0.01 sec)

Why am I having this problem?

Howard_L · #2 03-Aug-2008, 07:54

Code:

mysql> delete  from event where name is null ;
Query OK, 0 rows affected (0.52 sec)

mysql> delete  from event where date is null ;
Query OK, 1 row affected (0.08 sec)

mysql> # guess I can settle for that but why is 'name is null' not found?   
mysql> # ...or what's going on here?
mysql> show columns in event;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name   | varchar(20)  | YES  |     | NULL    |       | 
| date   | date         | YES  |     | NULL    |       | 
| type   | varchar(15)  | YES  |     | NULL    |       | 
| remark | varchar(255) | YES  |     | NULL    |       | 
+--------+--------------+------+-----+---------+-------+

TurboPT · #3 03-Aug-2008, 09:49

Yes that definitely seems strange, but your queries worked for me:

Code:

mysql> select * from event;
+----------+------------+----------+--------------------------+
| name     | date       | type     | remark                   |
+----------+------------+----------+--------------------------+
| Chirpy   | 1999-03-21 | vet      | needed beak straightened |
| Bowser   | 1991-10-12 | kennel   | NULL                     |
| Fang     | 1991-10-12 | kennel   | NULL                     |
| Whistler | 1998-12-09 | birthday | First Birthday           |
| NULL     | NULL       | NULL     | NULL                     |
+----------+------------+----------+--------------------------+
5 rows in set (0.00 sec)

mysql> delete from event where name is null;
Query OK, 1 row affected (0.09 sec)

mysql> select * from event;
+----------+------------+----------+--------------------------+
| name     | date       | type     | remark                   |
+----------+------------+----------+--------------------------+
| Chirpy   | 1999-03-21 | vet      | needed beak straightened |
| Bowser   | 1991-10-12 | kennel   | NULL                     |
| Fang     | 1991-10-12 | kennel   | NULL                     |
| Whistler | 1998-12-09 | birthday | First Birthday           |
+----------+------------+----------+--------------------------+
4 rows in set (0.00 sec)

Put the null row back in for SELECT test:
mysql> insert into event values(NULL, NULL, NULL, NULL);
Query OK, 1 row affected (0.05 sec)

mysql> select * from event where name is null;
+------+------+------+--------+
| name | date | type | remark |
+------+------+------+--------+
| NULL | NULL | NULL | NULL   |
+------+------+------+--------+
1 row in set (0.00 sec)

What mysql version do you have?

The above example, on the machine I'm using at the moment, has:
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)

Howard_L · #4 03-Aug-2008, 10:31

Code:

This one came with FC6:
$ mysql --version
mysql  Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0

Did you load your original data from a file?  
I found that the NULL line is different when it comes from a newline at the end of a LOAD 'ed file.
Here's what I did:
-----
mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));
-----
Then made this tab delimited file: 
Chirpy  1999-03-21      vet     needed beak straightened
Bowser  1991-10-12      kennel
Fang    1991-10-12      kennel
Whistler        1998-12-09      birthday        First birthday

-----
( important: note the newline at end of file, 
    that's what causes the unwanted NULL last line
  
  ALSO be advised that when I mouse copied that data from vi to the above
    it changed all the tabs to spaces!
    and I can't insert tabs within this edit window! )
-----
Then I loaded the data like this:
mysql> LOAD DATA LOCAL INFILE '/path/event.txt' INTO TABLE event;
---------------------------------------------------------------------

Anyhow, I have done some fooling around and found that for me it the deal is like this:
from:  http://dev.mysql.com/doc/refman/5.0/...cting-all.html
User Comments:
Posted by xxxxxxx on May 11 2006 4:33pm  
Or, if, for instance, you added an extra blank line in pet.txt and ended up 
with an extra empty row, you can use:

DELETE FROM pet WHERE name='';

#### ####
     YES THAT IS WORKING FOR ME!
     BUT I NOTICED SOME OTHER THINGS ALONG THE WAY:
     HERE IS THE ORIGINAL NULL FROM THE NEWLINE IN THE FILE:
mysql> select * from event;
...
| NULL     | NULL       | NULL     | NULL                        |
+----------+------------+----------+-----------------------------+

mysql> delete from event where name = ('');
Query OK, 1 row affected (0.01 sec) 
## so that removes THAT NULL line 
    ...moving on: these creates a similar line which the above won't remove:

mysql> insert into event VALUES (); 
Query OK, 1 row affected (0.01 sec)

mysql> select * from event;
+------+------+------+--------+
| name | date | type | remark |
+------+------+------+--------+
| NULL | NULL | NULL | NULL   |
+------+------+------+--------+
1 row in set (0.00 sec)

mysql> insert into event VALUES ( NULL, NULL, NULL, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into event VALUES ( null, null, null, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from event;
+------+------+------+--------+
| name | date | type | remark |
+------+------+------+--------+
| NULL | NULL | NULL | NULL   |
| NULL | NULL | NULL | NULL   |
| NULL | NULL | NULL | NULL   |
+------+------+------+--------+
3 rows in set (0.00 sec)

mysql> insert into event VALUES ('','','','');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from event;
+------+------------+------+--------+
| name | date       | type | remark |
+------+------------+------+--------+
| NULL | NULL       | NULL | NULL   |
| NULL | NULL       | NULL | NULL   |
| NULL | NULL       | NULL | NULL   |
| NULL | 0000-00-00 | NULL | NULL   |
+------+------------+------+--------+
4 rows in set (0.00 sec)

mysql> delete from event where name IS NULL;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from event;
+------+------------+------+--------+
| name | date       | type | remark |
+------+------------+------+--------+
| NULL | 0000-00-00 | NULL | NULL   |
+------+------------+------+--------+
1 row in set (0.00 sec)

mysql> delete from event where name = ('') ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from event;
Empty set (0.02 sec)

####
aha, so I'm guessing :
- the file with an extra line puts an 'empty string' in 'name'
             and fills the missing data with true 'NULL' values ?

- the line 'insert ('','','','')' line puts an 'empty string' in each column?

#### So I take a closer look:

mysql> insert into event VALUES ('','','','');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from event;
+------+------------+------+--------+
| name | date       | type | remark |
+------+------------+------+--------+
| NULL | 0000-00-00 | NULL | NULL   |
+------+------------+------+--------+
1 row in set (0.01 sec)

mysql> select * from event where name IS NULL || date IS NULL || type IS NULL || remark IS NULL;
Empty set (0.00 sec)

mysql> select * from event where name = 0 && date = 0 && type = 0 && remark = 0;
+------+------------+------+--------+
| name | date       | type | remark |
+------+------------+------+--------+
| NULL | 0000-00-00 | NULL | NULL   |
+------+------------+------+--------+
1 row in set (0.00 sec)

####
So not only do I find each with an '' , I find that '' is 0 (zero) !
ahh , empty string ,,, like a C '\0' ???!!!
#### ####

Is that what your getting ???
Thanks, Howard();

TurboPT · #5 03-Aug-2008, 11:06

I tried loading the file on XP, but it didn't do anything with the extra blank line. Only 4 rows were inserted.

Let me try with a Fedora machine that I have and I'll post again with results.

TurboPT · #6 03-Aug-2008, 11:13

Ok, different results!

Code:

+----------+------------+----------+----------------+
| name     | date       | type     | remark         |
+----------+------------+----------+----------------+
| Chirpy   | 1999-10-21 | vet      | beak adjusted  |
| Bowser   | 2001-01-01 | kennel   | NULL           |
| Fang     | 2002-02-02 | kennel   | NULL           |
| Whistler | 2005-01-02 | Birthday | First Birthday |
|          | NULL       | NULL     | NULL           |
+----------+------------+----------+----------------+
5 rows in set (0.00 sec)

It's interesting that the name field is blank...that would explain the name field behavior you mentioned previously, it is NOT null [blank], so the query was running as expected.
Hmmm, I'll have to ponder this a little more.

TurboPT · #7 03-Aug-2008, 13:31

Ok, after trying some of the other options available for 'load data...' without success, it seemed easier to 'pre-scrub' the file to eliminate all blank lines.

The command using sed is:

Code:

sed '/^$/d' event.txt > event.tmp

That command says, "starting from the beginning (^) of the line, followed by an end-of-line ($), delete (d) the line". I redirected the original file to event.tmp, so that the original data is retained.

Then do the 'load data...' command using the temp file.
HTH

Howard_L · #8 03-Aug-2008, 13:38

Code:

Yes... That sure makes the most sense, clean up the file before loading!
You have sed in windows?
In the real world I suppose blank lines could be a problem.
Does this get the line for you in all cases?
select * where name = '';

The only other thing I have to try is an install at our hosting service 
and I get blank line in the name field as you do in your Linux. 
Some testing show it to be '' BUT interestingly 0  doesn't work there as on my machine.
I do these commands:

# mysql --version
mysql  Ver 14.12 Distrib 5.0.45, for unknown-linux-gnu (x86_64) using readline 5.0

mysql> CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255));
mysql> LOAD DATA LOCAL INFILE 'petload_event.txt' INTO TABLE event;
mysql> insert into event VALUES ('','','','');
mysql> insert into event VALUES (NULL, NULL, NULL, NULL);
mysql> insert into event VALUES (null, null, null, null);
mysql> select * from event;
+----------+------------+----------+--------------------------+
| name     | date       | type     | remark                   |
+----------+------------+----------+--------------------------+
| Chirpy   | 1999-03-21 | vet      | needed beak straightened |
| Bowser   | 1991-10-12 | kennel   | NULL                     |
| Fang     | 1991-10-12 | kennel   | NULL                     |
| Whistler | 1998-12-09 | birthday | First birthday           |
|          | NULL       | NULL     | NULL                     |
|          | 0000-00-00 |          |                          |
| NULL     | NULL       | NULL     | NULL                     |
| NULL     | NULL       | NULL     | NULL                     |
+----------+------------+----------+--------------------------+
8 rows in set (0.00 sec)

mysql> select * from event where name = '' ;
+------+------------+------+--------+
| name | date       | type | remark |
+------+------------+------+--------+
|      | NULL       | NULL | NULL   |
|      | 0000-00-00 |      |        |
+------+------------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from event where name IS NULL ;
+------+------+------+--------+
| name | date | type | remark |
+------+------+------+--------+
| NULL | NULL | NULL | NULL   |
| NULL | NULL | NULL | NULL   |
+------+------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from event where date IS NULL ;
+------+------+------+--------+
| name | date | type | remark |
+------+------+------+--------+
|      | NULL | NULL | NULL   |
| NULL | NULL | NULL | NULL   |
| NULL | NULL | NULL | NULL   |
+------+------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from event where name = 0 ;
+----------+------------+----------+--------------------------+
| name     | date       | type     | remark                   |
+----------+------------+----------+--------------------------+
| Chirpy   | 1999-03-21 | vet      | needed beak straightened |
| Bowser   | 1991-10-12 | kennel   | NULL                     |
| Fang     | 1991-10-12 | kennel   | NULL                     |
| Whistler | 1998-12-09 | birthday | First birthday           |
|          | NULL       | NULL     | NULL                     |
|          | 0000-00-00 |          |                          |
+----------+------------+----------+--------------------------+
6 rows in set (0.00 sec)

#### WELL!  That's a bit different
     BUT
mysql> select * from event where name = '' ;
mysql> select * from event where name IS NULL ;

do the same things in both mysql installs.
...I guess as they are specified to do.
0 (zero) is probably not specified to be anything but itself !
and just happens to work in that FC6 install...
If you try to use it you could expect  to get. what's he say, "undefined behavior" :)

huh, on the FC6 machine I just deleted all the data and reloaded and now:
mysql> select * from event where name = 0 ;
shows all lines... so it was just a fluke

hmm
turbo 1:
Server version: 5.0.41-community-nt MySQL Community Edition (GPL)
Hosting:
mysql  Ver 14.12 Distrib 5.0.45,forunknown-linux-gnu (x86_64) usingreadline 5.0
FC6:
mysql  Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0

oh well... interesting to note but I'm supposed to be doing "working with two tables"
Thanks for the interest, moron.Howard();

TurboPT · #9 03-Aug-2008, 15:41

Quote:

Originally Posted by Howard_L

You have sed in windows?

Having cygwin installed, yes. However, since I was on the Fedora box, I used it there.

EDIT:

Quote:

Originally Posted by Howard_L

Does this get the line for you in all cases?
select * where name = '';

Yes. That query, when applied to this table, info:

Code:

+--------+------------+----------+----------------+
| name   | date       | type     | remark         |
+--------+------------+----------+----------------+
| Chirpy | 2001-01-01 | vet      | beak adjusted  |
| NULL   | NULL       | NULL     | NULL           |
| Bowser | 2002-01-01 | kennel   | NULL           |
| Fang   | 2003-01-01 | kennel   | NULL           |
| NULL   | NULL       | NULL     | NULL           |
| Wolfy  | 2004-01-01 | Birthday | First Birthday |
|        | 0000-00-00 |          |                |
|        | NULL       | NULL     | NULL           |
+--------+------------+----------+----------------+

gives...

Code:

+------+------------+------+--------+
| name | date       | type | remark |
+------+------------+------+--------+
|      | 0000-00-00 |      |        |
|      | NULL       | NULL | NULL   |
+------+------------+------+--------+

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Cpanel downgrade MYSQL 4.1.X to MySQL 4.0.xx	Webhosting-live	Web Hosting Forum	1	01-Sep-2006 03:54
[Tutorial] Pointers in C (Part I)	Stack Overflow	C Programming Language	1	08-Apr-2005 19:35
Starting Mysql server problem	pjacks	MySQL / PHP Forum	23	08-Sep-2004 18:23
A problem Between MySQL <> phpBB	mirable	MySQL / PHP Forum	3	10-Sep-2003 06:31
select problem	zuzupus	MySQL / PHP Forum	0	15-Aug-2003 08:25

MySQL problem: SELECT * FROM event WHERE name is null ;

Re: mysql problem: SELECT * FROM event WHERE name is null ;

Re: mysql problem: SELECT * FROM event WHERE name is null ;

Re: mysql problem: SELECT * FROM event WHERE name is null ;

Re: mysql problem: SELECT * FROM event WHERE name is null ;

Re: mysql problem: SELECT * FROM event WHERE name is null ;

Re: mysql problem: SELECT * FROM event WHERE name is null ;

Re: mysql problem: SELECT * FROM event WHERE name is null ;

Re: mysql problem: SELECT * FROM event WHERE name is null ;