MySQL Bugs: #33696: CSV storage engine allows nullable columns via ALTER TABLE statements

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

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.

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)

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.

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.

Repeatable only with 6.0, the very likely cause of this problem is online alter table.

I was able to reproduce this bug with 5.1.23-ndb-6.3.11-bk also.

Reproducible only in 6.0. Wasn't able to reproduce with mysql-5.1-ndb-merge branch

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

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.

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.

Ok Patrick, When I push the patch I will leave the test disabled and will not add .result file.

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.

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

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

Pushed into 6.0.9-alpha  (revid:satya.bn@sun.com-20081126083009-b5fz09ytpwodd24x) (version source revid:satya.bn@sun.com-20081126083009-b5fz09ytpwodd24x) (pib:5)

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

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)

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)

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)

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.

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.

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)

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

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.

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)