Bug #54394 Make sure server doesn't accept NULL as inferred column type

• Submitted: 10 Jun 2010 9:09
• Reporter: Georgi Kodinov
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 5.1 and up
• OS: Any
• CPU Architecture: Any

[10 Jun 2010 9:09] Georgi Kodinov

Description:
This is a spin-off of bug #54044 requesting the change in behavior. 
One can create a table with a NULL as an inferred column type. Unlike non-SELECT variant of CREATE TABLE (where NULL is not a valid column type) in the case of CREATE TABLE .. SELECT NULL the server will just pass the NULL to the storage engine.
Some storage engines will support this column type (as demonstrated by the "how to repeat" below, others will throw an error.
But since this is a discrepancy between CREATE TABLE .. SELECT and the non-SELECT variants I think a column type of NULL should not be passed to the storage engines. 
MySQL server should either reject it with an error message or substitute it for some other valid column type.  

How to repeat:
+SET storage_engine=MYISAM;
+CREATE TEMPORARY TABLE mk_upgrade AS 
+SELECT IF(NULL IS NOT NULL, NULL, NULL) AS A;
+SHOW CREATE TABLE mk_upgrade;
+Table  Create Table
+mk_upgrade     CREATE TEMPORARY TABLE `mk_upgrade` (
+  `A` null DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO mk_upgrade VALUES (NULL);
+INSERT INTO mk_upgrade VALUES (1);
+SELECT * FROM mk_upgrade;
+A
+
+
+
+DROP TABLE mk_upgrade;

Suggested fix:
Reject the above create table with an error message asking for specifying a valid column type.