Re: defined with default NULL, but missing ...

[Michael Stassen <suppressed>]

C.R.Vegelin wrote:
> Hi everybody,
> I defined a field Date_End with default NULL, but am missing it.
>
> CREATE TABLE Regions
> ( Country CHAR(4) NOT NULL,
>   Date_Start CHAR(4) NOT NULL,
>   Date_End CHAR(4) default NULL,  # this one ...
>   Description CHAR(50)
> ) Engine = MyISAM;
>
> Regions table is filled with a tab-delimited input table like:
> 0001    1997    2500     France
> 1000    1976             WORLD
> 1010    1976             INTRA-EUR
> LOAD DATA INFILE '../Regions.txt' INTO TABLE Regions
> FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;

You have empty strings, '', not NULLs in column 3.  NULLs are indicated by \N 
for use with LOAD DATA INFILE.  See the manual for all the details 
<http://dev.mysql.com/doc/refman/5.0/en/load-data.html>.

> SELECT * FROM Regions WHERE Country >= 1000;
> +---------+------------+----------+-------------+
> | country | date_start | date_end | description |
> +---------+------------+----------+-------------+
> | 1000   | 1976        |          | WORLD       |
> | 1010   | 1976        |          | INTRA-EUR   |
> | 1011   | 1976        |          | EXTRA-EUR   |
> etc.
>
> Question: why is NULL not shown for date_end ?

Because date_end = '', not NULL, for those rows.

> DELETE FROM Regions WHERE Country >= 1000 AND Date_End < 2001;
> Query OK; 33 rows affected; 
>
> SELECT * FROM Regions WHERE Country >= 1000;
> Empty Set
>
> Question: So NULL values are matching Date_End < 2001 in the DELETE ???

No, '' is interpreted as 0 in numeric context, hence it is less than 2001.

> I am using MySQL version 5.0.15-nt. Help will be appreciated.
> Cor Vegelin

Either use \N where you want NULLs in your Regions.txt file, or fix them after 
importing.  Fixing them is probably just a matter of

  UPDATE Regions
  SET date_end = NULL
  WHERE Country >= 1000 AND Date_End = '';

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/suppressed