MySQL Bugs: #33371: default required for column in strict mode when value is specified

Bug #33371	default required for column in strict mode when value is specified
Submitted:	19 Dec 2007 16:39	Modified:	27 Dec 2007 17:06
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
This one http://bugs.mysql.com/bug.php?id=1873
got closed and I did not get a reply to this!

drop table if exists t;
create table t 
(
    pid int not null, 
    rank int not null, 
    points int not null,  
    primary key (pid)
);

INSERT t (rank, pid, points) VALUES (1, 1, 0), (2, 4, 0), (3, 9, 0);
-- (3 row(s)affected)
INSERT t (rank, pid) VALUES (4, 1), (5, 4), (6, 9)
    ON DUPLICATE KEY UPDATE points = 1;
-- Error Code : 1364
-- Field 'points' doesn't have a default value
 
It should update 'points' column with '1' value so it won't use that
default anyway - why then demand it in the table definition ??!!

How to repeat:
see above

Suggested fix:
check in this order

1: will it INSERT or UPDATE?

2: If it UPDATES: is there sufficient specification for the column to update?

and only if 2) is not the case

3: check against what the sql_mode requires for UPDATE (NULL/NOT NULL, DEFAULT exists on column or not)

Peter,

I think you used strict mode.

mysql> set sql_mode='STRICT_TRANS_TABLES';

at the table you created all columns are defined as not null.

You try: INSERT t (rank, pid) VALUES (4, 1), (5, 4), (6, 9)
    ON DUPLICATE KEY UPDATE points = 1;

ok, you want to update the "points", how should the system know, that you really don't want to insert the data?

The system first check, if it is possible, to insert the values like this.

INSERT t (rank, pid) VALUES (4, 1), (5, 4), (6, 9);

Of course this failed, because you didn't tell the system, what it should do with the "points" column. It's "not null", so it can't insert "NULL" and you didn't set a default ...
What else should it do here, instead of failing?

Many thanks for choosing MySQL.

I am sorry .. was my mistake!

This

drop table if exists t;
create table t 
(
    pid int not null, 
    rank int not null, 
    points int not null,  
    primary key (pid)
);

INSERT t (rank, pid, points) VALUES (1, 1, 0), (2, 4, 0), (3, 9, 0);
-- (3 row(s)affected)
INSERT t (rank, point) VALUES (4, 1), (5, 4), (6, 9)
    ON DUPLICATE KEY UPDATE points = 1;

also fails.  And what what I meant!

Peter,

Sorry, but once again you are trying to insert NULL into NOT NULL column (pid) without a default value:

drop table if exists t;
create table t 
(
    pid int not null, 
    rank int not null, 
    points int not null,  
    primary key (pid)
);

INSERT t (rank, point) VALUES (4, 1), (5, 4), (6, 9)
    ON DUPLICATE KEY UPDATE points = 1;

There is NO pid in INSERT above, so default value is needed, hence:

mysql> INSERT t (rank, points) VALUES (4, 1), (5, 4), (6, 9)
    ->     ON DUPLICATE KEY UPDATE points = 1;
ERROR 1364 (HY000): Field 'pid' doesn't have a default value

I do not see a bug here, sorry.

okokok ...

I am sorry but forth next weeks I have no MySQL server available!  Only CyberCafe's.

Please also try (I think I remember that failed as well.  If I am wrong please close!)

drop table if exists t;
create table t 
(
    pid int not null auto_increment, 
    rank int not null, 
    points int not null,  
    primary key (pid)
);

INSERT t (rank, pid, points) VALUES (1, 1, 0), (2, 4, 0), (3, 9, 0);
-- (3 row(s)affected)
INSERT t (rank, point) VALUES (4, 1), (5, 4), (6, 9)
    ON DUPLICATE KEY UPDATE points = 1;

I see no bug with this your latest example also:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.54-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists t;
Query OK, 0 rows affected (0.06 sec)

mysql> create table t
    -> (
    ->     pid int not null auto_increment,
    ->     rank int not null,
    ->     points int not null,
    ->     primary key (pid)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT t (rank, pid, points) VALUES (1, 1, 0), (2, 4, 0), (3, 9, 0);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT t (rank, points) VALUES (4, 1), (5, 4), (6, 9)
    ->     ON DUPLICATE KEY UPDATE points = 1;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t;
+-----+------+--------+
| pid | rank | points |
+-----+------+--------+
|   1 |    1 |      0 |
|   4 |    2 |      0 |
|   9 |    3 |      0 |
|  10 |    4 |      1 |
|  11 |    5 |      4 |
|  12 |    6 |      9 |
+-----+------+--------+
6 rows in set (0.00 sec)