Multiple inserting NULL into a column that has been declared NOT NULL

Hi!

I'm not sure if this is a documentation-problem or a mysqld-related
problem.

The manual states at 6.4.3, on INSERTs - more specific in the paragraphs
about INSERT ... SELECT and INSERT ... VALUES statement with multiple value
lists:

==
* Inserting NULL into a column that has been declared NOT NULL. The
column is set to its default value.
==

This doesn't seem to be the case for mysqld 4.0.13 (linux) or
4.1.0-alpha-max-nt (WinXP). Om both servers I end up with the following
result, which could easily be replicated:

mysql> CREATE TABLE foo (id int NOT NULL default 8);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO foo (id) VALUES (NULL);
ERROR 1048: Column 'id' cannot be null

mysql> INSERT INTO foo (id) VALUES (3),(NULL),(5);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 1

mysql> SELECT * FROM foo;
+----+
| id |
+----+
| 3 |
| 0 |
| 5 |
+----+
3 rows in set (0.01 sec)


Please notice that the second INSERT did result in 0, instead of the
default-value 8 - which would be the case in e.g.
INSERT INTO foo () VALUES ()

--
- Peter Brodersen


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Multiple inserting NULL into a column that has been declared NOT NULL

Hi,

> mysql> CREATE TABLE foo (id int NOT NULL default 8);
> Query OK, 0 rows affected (0.00 sec)
>=20
> mysql> INSERT INTO foo (id) VALUES (NULL);
> ERROR 1048: Column 'id' cannot be null
>=20
> mysql> INSERT INTO foo (id) VALUES (3),(NULL),(5);
> Query OK, 3 rows affected (0.01 sec)
> Records: 3 Duplicates: 0 Warnings: 1
>=20
> mysql> SELECT * FROM foo;
> +----+
> | id |
> +----+
> | 3 |
> | 0 |
> | 5 |
> +----+
> 3 rows in set (0.01 sec)

Thanks for an excellent bug report. This has been filed as bug #615 - you
can follow its progress at http://bugs.mysql.com/bug.php?id=3D615


Rgds,
Indrek

--=20
Are you MySQL certified? - http://www.mysql.com/certification/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Indrek Siitan
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, The Support Guy
/_/ /_/\_, /___/\___\_\___/ Uuem=F5isa, Haapsalu, Estonia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org