| Bug #20501 | CSV Engine Null Values | ||
|---|---|---|---|
| Submitted: | 16 Jun 2006 12:31 | Modified: | 25 Jan 2007 11:09 |
| Reporter: | Claudio Andenmatten | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.21/5.1bk | OS: | Solaris (Solaris 8 (64bit)) |
| Assigned to: | Antony Curtis | CPU Architecture: | Any |
[16 Jun 2006 19:08]
MySQL Verification Team
Thank you for the bug report. Verified as described on Suse 10 32-bit.
[16 Jun 2006 22:16]
Peter Laursen
I do not think that EMPTY STRING "" should be considered NULL! An empty string is not a NULL value! NULL values should be stored as /N or NULL (un-quoted)!
[16 Jun 2006 22:17]
Peter Laursen
Of course I meant \N ... not /N
[16 Jun 2006 22:44]
MySQL Verification Team
Yes should be NULL instead of 0 when the column is number type and not "" when string one.
[19 Jun 2006 6:38]
Claudio Andenmatten
I was not aware of the fact that I am able to discuss core features of the CSV engine here. Therefore I was not very exact. I apologize. First I agree with Peter, NULL is not equal to an empty string. So this rises to questions: Q1: how to store a NULL in CSV Q2: how to treat (cast) empty strings in SQL queries IMHO both questions are not trivial. A1: Store \N would be a solution. But nobody else is doing it this way (eg. Excel), so the portability is weakened. And as I understand portability is a goal of the CSV engine. A2: Is MySql (like other RDBMS) casting empty strings to NULL or not? As I can see it is not. The MyISAM engine casts empty numbers always to 0 (zero). CSV engine as well. This is why I filed the bug. This is strange behaviour, but acceptable. Empty Strings are casted to NULL by MyISAM engine, BUT in retrieval it can still be distinguished between NULL and empty string! So it kind of stores a special NULL for empty strings. In CSV engine it stores "" for empty strings and therefore it is not possible to distinguish empty strings and NULLs anymore. NULL values are lost. So long speech short, IMHO these are not bugs, but slightly inconsistent behaviour. I am looking forward to your comment. Claudio
[25 Sep 2006 17:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/12487 ChangeSet@1.2324, 2006-09-25 10:29:42-07:00, acurtis@xiphis.org +5 -0 Bug#20501 "NULL not supported in CSV tables" Add support for NULL values
[27 Sep 2006 17:34]
Calvin Sun
Brian comments: "that is a backwards incompatible change".
[25 Jan 2007 11:12]
Antony Curtis
Patch pending for 122 days.
Description: If I am inserting NULL values into a CSV table it stores 0 (zeros) in the file. As a result of that the SELECT is returning 0 instead off NULL's How to repeat: mysql> desc orders_stage; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | order_mode | varchar(8) | YES | | NULL | | | customer_id | decimal(6,0) | YES | | NULL | | | order_status | decimal(2,0) | YES | | NULL | | | order_total | decimal(8,2) | YES | | NULL | | | sales_rep_id | decimal(6,0) | YES | | NULL | | | promotion_id | decimal(6,0) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 6 rows in set (0.01 sec) mysql> insert into orders_stage (order_mode, customer_id) values ('foobar',100); Query OK, 1 row affected (0.01 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from orders_stage; +------------+-------------+--------------+-------------+--------------+--------------+ | order_mode | customer_id | order_status | order_total | sales_rep_id | promotion_id | +------------+-------------+--------------+-------------+--------------+--------------+ | foobar | 100 | 0 | 0.00 | 0 | 0 | +------------+-------------+--------------+-------------+--------------+--------------+ 1 row in set (0.00 sec) mysql> content of file: > cat orders_stage.CSV "foobar","100","0","0.00","0","0" mysql> show create table orders_stage; +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | orders_stage | CREATE TABLE `orders_stage` ( `order_mode` varchar(8) default NULL, `customer_id` decimal(6,0) default NULL, `order_status` decimal(2,0) default NULL, `order_total` decimal(8,2) default NULL, `sales_rep_id` decimal(6,0) default NULL, `promotion_id` decimal(6,0) default NULL ) ENGINE=CSV DEFAULT CHARSET=latin1 | +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Suggested fix: Change the behaviour that NULLs are stored as NULLs (like "") and "" are retrieved as NULLs instead of zeros.