Bug #33696 CSV storage engine allows nullable columns via ALTER TABLE statements
Submitted: 4 Jan 2008 21:13 Modified: 2 Feb 2009 14:58
Reporter: Patrick Crews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: CSV Severity:S3 (Non-critical)
Version:6.0 OS:Any
Assigned to: Satya B CPU Architecture:Any
Tags: ALTER TABLE, csv, null columns
Triage: Triaged: D3 (Medium)

[4 Jan 2008 21:13] Patrick Crews
Description:
The CSV storage engine has been modified to require columns be explicitly NOT NULL in CREATE TABLE statements.

However, adding columns via the ALTER TABLE command allows nullable columns to be added to an existing CSV table.

How to repeat:
Run the following SQL in the database of your choice - 

CREATE TABLE t1 (a int NOT NULL);
DESC t1;
ALTER TABLE t1 add column (b float);
DESC t1;

You can see that the new column added despite the lack of NOT NULL and that it is a nullable column

Suggested fix:
Ensure that any modifications to a CSV table will not allow / result in a nullable column being added to the table.
[4 Jan 2008 21:38] Miguel Solorzano
Thank you for the bug report.

mysql> create table tb1 (col1 float) engine=csv;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns
mysql> create table tb1 (col1 float not null) engine=csv;
Query OK, 0 rows affected (0.09 sec)

mysql> alter table tb1 add b float;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb1\G
*************************** 1. row ***************************
       Table: tb1
Create Table: CREATE TABLE `tb1` (
  `col1` float NOT NULL,
  `b` float DEFAULT NULL
) ENGINE=CSV DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 6.0.5-alpha-main-tree-nt |
+--------------------------+
1 row in set (0.03 sec)
[4 Jan 2008 22:45] Patrick Crews
NOTE:  This is also true for MODIFY and CHANGE column statements as well.  

That is, existing columns can be made nullable through these statements rather than just nullable columns being added to a table.
[11 Jan 2008 19:09] Patrick Crews
NOTE:

Tests have been written to exercise this functionality (pending review and commit) for Bug#33558.

The .test file, csv_alter_table.test will be disabled until this Bug is fixed.
(Will also create a new bug noting the disabled status once the tests have been pushed)

Noted to prevent redundant tests / save time / etc.

Please add to this .test file any additional tests that are deemed necessary to fully test this.
[25 Mar 2008 10:53] Sergey Vojtovich
Repeatable only with 6.0, the very likely cause of this problem is online alter table.
[2 Apr 2008 16:50] Jon Stephens
I was able to reproduce this bug with 5.1.23-ndb-6.3.11-bk also.
[3 Nov 2008 10:43] Satya B
Reproducible only in 6.0. Wasn't able to reproduce with mysql-5.1-ndb-merge branch
[4 Nov 2008 5:25] 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/57742

2900 Satya B	2008-11-04
      Fix for Bug#33696 - CSV storage engine allows nullable columns via ALTER TABLE statements
[5 Nov 2008 12:04] 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/57875

2900 Satya B	2008-11-05
      Fix for Bug#33696 - CSV storage engine allows nullable columns via ALTER TABLE statements
      
      Problem:
      After the fast online-alter table changes, CSV engine allows nullable columns via alter table and also the existing non-null columns  can be modified to null columns.
      All columns in CSV Engine should be NON-NULL always.
      
      How it was solved:
      Found that we evaluate the changes that will be made to altered table. During this evaluation, we check for the behaviour of the new fields in temporary altered table with the fields in the original table and maintain as table_changes. This is passed to storage engines along with the new information. Storage engines can add further checks and return the compatibility state.CSV Engine has this information but ignores it and returns always true. Changed the behaviour to return compatible or not based on the information passed from the server.
      
      In sql_table.cc: mysql_alter_table(...), create_altered_table(...) creates the altered table and table->file->check_if_supported_alter(..) checks if a table can be altered on-line
      
      Note:
      The csv_alter_table exists in the repository and was disabled due to this bug.
[17 Nov 2008 20:27] Patrick Crews
Submitted patch to re-enable csv_alter_table.test in 5.1 since the Bug only appears in the 6.0 tree.
See Bug#33970 - Test main.csv_alter_table disabled

Will push .result file to 6.0 pending review, but leave the test disabled until fix is pushed.
[19 Nov 2008 5:20] Satya B
Ok Patrick, When I push the patch I will leave the test disabled and will not add .result file.
[19 Nov 2008 8:41] Satya B
Patrick,

On second thought, do you want me to leave the test disabled and do not add the result file?  Or do you want to do it yourself with  BUG#33970 after my push?

If it is not a problem, I will be enabling the test and will add the result file.
[24 Nov 2008 15:21] 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/59684

2744 Satya B	2008-11-24
      Fix for Bug#33696 - CSV storage engine allows nullable columns via 
      ALTER TABLE statements
            
      After the fast online-alter table changes, CSV engine allows nullable
      columns via alter table and also the existing non-null columns can be
      modified to null columns. All columns in CSV Engine should be NON-NULL 
      always.
            
      Found that we evaluate the changes that will be made to altered table. 
      During this evaluation, we check for the behaviour of the new fields 
      in temporary altered table with the fields in the original table and
      maintain as table_changes. This is passed to storage engines along 
      with the new information. Storage engines can add further checks and 
      return the compatibility state.CSV Engine has this information but 
      ignores it and returns always true. Changed the behaviour to return 
      compatible or not based on the information passed from server
      
      Note: No testcase added as it already exists in the repository
[26 Nov 2008 8:30] 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/59872

2747 Satya B	2008-11-26
      Bug#33696 - CSV storage engine allows nullable columns via ALTER TABLE statements
      
      Adding the result file for csv_alter_table.test
[8 Dec 2008 11:30] Bugs System
Pushed into 6.0.9-alpha  (revid:satya.bn@sun.com-20081126083009-b5fz09ytpwodd24x) (version source revid:satya.bn@sun.com-20081126083009-b5fz09ytpwodd24x) (pib:5)
[9 Jan 2009 11:05] 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/62797

2787 Tomas Ulin	2009-01-08
      Bug#33696 2008-01-21 pcrews no .result file - bug allows NULL columns in CSV tables
[19 Jan 2009 11:30] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108140353-0yibkz4btguv39p8) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:08] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:13] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
[2 Feb 2009 14:58] Tony Bedford
An entry has been added to the 6.0.9 changelog:

The CSV storage engine had been modified to require columns to be explicitly specified as NOT NULL in CREATE TABLE statements.

However, adding columns via the ALTER TABLE command allowed nullable columns to be added to an existing CSV table.
[10 Feb 2009 19: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/65794

3041 Patrick Crews	2009-02-10
      Bug#33696 - CSV storage engine allows nullable columns via ALTER TABLE statements
      
      Re-enabling this test in 6.0 as the bug is closed.
      Test is already enabled in 5.1 trees.
[10 Feb 2009 20:12] Bugs System
Pushed into 6.0.10-alpha (revid:alik@sun.com-20090210194937-s7xshv5l3m1v7wi9) (version source revid:tomas.ulin@sun.com-20090108141324-1pgriy6hilnr64lw) (merge vers: 6.0.10-alpha) (pib:6)
[12 Feb 2009 10:06] 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/66011

2814 He Zhenxing	2009-02-12 [merge]
      Auto merge 6.0 -> 6.0-rpl
[13 Feb 2009 21:33] 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/66305

3038 Patrick Crews	2009-02-13
      Bug#33696: CSV storage engine allows nullable columns in ALTER TABLE statements
      Re-enabling this test in 6.0 as the bug has been fixed here.
      Already enabled in 5.1 tree.
[19 Feb 2009 13:03] Bugs System
Pushed into 6.0.10-alpha (revid:sergey.glukhov@sun.com-20090218125737-5y5b2xo3duo1wlvo) (version source revid:patrick.crews@sun.com-20090213213326-k26lb01wdh8a6s8f) (merge vers: 6.0.10-alpha) (pib:6)