MySQL Bugs: #65305: INSERT INTO NULL BUG

Bug #65305	INSERT INTO NULL BUG
Submitted:	13 May 2012 21:06	Modified:	24 May 2012 3:10
Reporter:	German Bernhardt	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	5.1.63	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
INSERT INTO NULL BUG

How to repeat:
CREATE TABLE IF NOT EXISTS `lineas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `SECONDARY` (`nombre`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO lineas (name) VALUES (NULL);
#1048 - Column 'name' cannot be null
SELECT * FROM lineas;
+-----------+
| id | name |
+-----------+
OK: NO INSERTO NINGUN REGISTRO PORQUE EL CAMPO NAME ES NULO Y NO PUEDE SER NULO
    WHY DO NOT INSERT ANY FIELD NAME REGISTRATION IS NULL AND CAN NOT BE NULL

INSERT INTO lineas (name) VALUES (NULL),(NULL);
#1062 - Duplicate entry '' for key 'SECONDARY'
SELECT * FROM lineas;
+-----------+
| id | name |
| 1  |      |
+-----------+
FAILED: INSERTO 1 REGISTRO SIENDO QUE NAME NO PUEDE SER NULO
        WHEREAS INSERT NAME 1 RECORD CAN NOT BE NULL

First of all, please, provide complete repeatable test case and just copy/paste the results of it's run in mysql command line client. You referred to column name in the secondary index, `nombre`, that does not exist in the table...

Also, please, check if the same problem still happens with a recent version, 5.1.63.

CREATE TABLE IF NOT EXISTS `lineas` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `xxx` varchar(25) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 UNIQUE KEY `SECONDARY` (`xxx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
INSERT INTO lineas (xxx) VALUES
(NULL);
SELECT * FROM lineas;
INSERT INTO lineas (xxx) VALUES
(NULL),
(NULL);
SELECT * FROM lineas;

C:\Archivos de programa\EasyPHP-5.3.2i\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.1.45-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test;
Database changed
mysql> CREATE TABLE IF NOT EXISTS `lineas` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `xxx` varchar(25) NOT NULL DEFAULT '',
    ->  PRIMARY KEY (`id`),
    ->  UNIQUE KEY `SECONDARY` (`xxx`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO lineas (xxx) VALUES
    -> (NULL);
ERROR 1048 (23000): Column 'xxx' cannot be null
mysql> SELECT * FROM lineas;
Empty set (0.00 sec)

mysql> INSERT INTO lineas (xxx) VALUES
    -> (NULL),
    -> (NULL);
ERROR 1062 (23000): Duplicate entry '' for key 'SECONDARY'
mysql> SELECT * FROM lineas;
+----+-----+
| id | xxx |
+----+-----+
|  1 |     |
+----+-----+
1 row in set (0.00 sec)

mysql>

With 5.1.61 I already have the same result in both cases, no matter how many NULLs you are trying to insert:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE IF NOT EXISTS `lineas` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `xxx` varchar(25) NOT NULL DEFAULT '',
    ->  PRIMARY KEY (`id`),
    ->  UNIQUE KEY `SECONDARY` (`xxx`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO lineas (xxx) VALUES
    -> (NULL);
ERROR 1048 (23000): Column 'xxx' cannot be null
mysql> SELECT * FROM lineas;
Empty set (0.00 sec)

mysql> INSERT INTO lineas (xxx) VALUES
    -> (NULL),
    -> (NULL);
ERROR 1048 (23000): Column 'xxx' cannot be null
mysql> SELECT * FROM lineas;
Empty set (0.00 sec)

So, please, upgrade.

ok thanks I'll update the version!

5.1.45-community-log
5.1.61-community
5.5.20-log

the error persists I will try different versions

USE test;
CREATE TABLE IF NOT EXISTS `lineas` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `xxx` varchar(25) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 UNIQUE KEY `SECONDARY` (`xxx`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
INSERT INTO lineas (xxx) VALUES
(NULL);
SELECT * FROM lineas;
INSERT INTO lineas (xxx) VALUES
(NULL),
(NULL);
SELECT * FROM lineas;
INSERT INTO lineas (xxx) VALUES
('XXX'),
(NULL);
SELECT * FROM lineas;
####################################################################
5.1.45 to 5.1.62 FINAL
INSERT INTO lineas (xxx) VALUES (NULL),(NULL);
---> FAILED!!!!!
####################################################################
C:\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.1.45-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test;
Database changed
mysql> CREATE TABLE IF NOT EXISTS `lineas` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `xxx` varchar(25) NOT NULL DEFAULT '',
    ->  PRIMARY KEY (`id`),
    ->  UNIQUE KEY `SECONDARY` (`xxx`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO lineas (xxx) VALUES
    -> (NULL);
ERROR 1048 (23000): Column 'xxx' cannot be null
mysql> SELECT * FROM lineas;
Empty set (0.00 sec)

mysql> INSERT INTO lineas (xxx) VALUES
    -> (NULL),
    -> (NULL);
ERROR 1062 (23000): Duplicate entry '' for key 'SECONDARY'
mysql> SELECT * FROM lineas;
+----+-----+
| id | xxx |
+----+-----+
|  1 |     |
+----+-----+
1 row in set (0.00 sec)

mysql>

####################################################################
5.1.63 FINAL
INSERT INTO lineas (xxx) VALUES (NULL),(NULL);
---> OK!!!!!
####################################################################
5.1.63 FINAL
INSERT INTO lineas (xxx) VALUES ('XXX'),(NULL);
---> FAILED!!!!!
####################################################################
C:\mysql\bin>mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 132
Server version: 5.1.63-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test;
Database changed
mysql> CREATE TABLE IF NOT EXISTS `lineas` (
    ->  `id` int(11) NOT NULL AUTO_INCREMENT,
    ->  `xxx` varchar(25) NOT NULL DEFAULT '',
    ->  PRIMARY KEY (`id`),
    ->  UNIQUE KEY `SECONDARY` (`xxx`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO lineas (xxx) VALUES
    -> (NULL);
ERROR 1048 (23000): Column 'xxx' cannot be null
mysql> SELECT * FROM lineas;
Empty set (0.00 sec)

mysql> INSERT INTO lineas (xxx) VALUES
    -> (NULL),
    -> (NULL);
ERROR 1048 (23000): Column 'xxx' cannot be null
mysql> SELECT * FROM lineas;
Empty set (0.00 sec)

mysql> INSERT INTO lineas (xxx) VALUES
    -> ('XXX'),
    -> (NULL);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM lineas;
+----+-----+
| id | xxx |
+----+-----+
|  1 | XXX |
|  2 |     |
+----+-----+
2 rows in set (0.00 sec)

mysql>

sorry Valeriy Kravchuk  pero esta vez tengo razon
Valeriy Kravchuk sorry but this time I have reason

si queres lo pruebo en la ultima version 5.5 mañana pero seguro da error
I try it if you want the latest version 5.5 tomorrow but surely fails