| Bug #12151 | NOT NULL in view remains in create table .. select from .. left join view | ||
|---|---|---|---|
| Submitted: | 25 Jul 2005 13:34 | Modified: | 23 Aug 2005 10:55 |
| Reporter: | Jun Murai | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.9-beta | OS: | Windows (Windows XP and Linux) |
| Assigned to: | CPU Architecture: | Any | |
[25 Jul 2005 14:17]
Valeriy Kravchuk
Thank you for your bug report. But I can't call the behaviour you described a bug. I can provide you a much simpler test case, wich has nothing to do with views and LEFT JOINs:
mysql> create table t12151 (c1 int not null, c2 char(30) not null);
Query OK, 0 rows affected (0.08 sec)
mysql> desc t12151;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1 | int(11) | NO | | | |
| c2 | char(30) | NO | | | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> create table tt (c1 int, c2 char(30));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into tt values(NULL, NULL);
Query OK, 1 row affected (0.04 sec)
mysql> insert into t12151 select * from tt;
Query OK, 1 row affected, 2 warnings (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 2
mysql> show warnings;
+---------+------+--------------------------------------------------------------
---------------+
| Level | Code | Message
|
+---------+------+--------------------------------------------------------------
---------------+
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
'c1' at row 1 |
| Warning | 1263 | Column set to default value; NULL supplied to NOT NULL column
'c2' at row 1 |
+---------+------+--------------------------------------------------------------
---------------+
2 rows in set (0.00 sec)
mysql> select * from t12151;
+----+----+
| c1 | c2 |
+----+----+
| 0 | |
+----+----+
1 row in set (0.00 sec)
mysql> select * from t12151 where c2 is null;
Empty set (0.01 sec)
So, strictly according to the manual (http://dev.mysql.com/doc/mysql/en/design-limitations.html):
" If you insert an ``incorrect'' value in a column, such as a too-large numeric value into a numeric column, MySQL sets the column to the ``best possible value'' instead of giving an error. For numerical values, this is 0, the smallest possible value or the largest possible value. For strings, this is either the empty string or as much of the string as can be stored in the column."
0 and empty string were inserted. (Try "select * from CopiedViewFromView where str is null;" in your case). No constraints are violated. That is why, NOT NULL remains - non-null values are inserted.
[25 Jul 2005 14:41]
Jun Murai
I know that NULL is changed to 0 or empty string when NOT NULL constraint is made. But I can't understand why NULL is allowed in case 1-4, but not in case 5-6. These cases are derived from same tables.
[23 Aug 2005 10:55]
Sergei Golubchik
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/ Additional info: This was apparently fixed since 5.0.9, current 5.0.12 returns identical results for all your cases 1-6.
Description: Normally, when table A is joined as '... LEFT JOIN table A', not-null constraint on columns from A should be cleared in a new table, because joined columns will be null if there's no corresponding record. With tables, that's true. But, when a table is created with sql 'CREATE TABLE ... SELECT * FROM table LEFT JOIN view on ...', not null constraint in the view is kept on the new table. How to repeat: create table t1 (id int not null, name char(9) not null); create table t2 (dbid int not null, str char(30) not null); create view vt2 as select * from t2; insert into t1 values (1, "blue"), (2, "black"), (3, "red"); insert into t2 values (1, "A"), (3, "B"); create view ViewFromTable as select * from t1 left join t2 on t2.dbid = t1.id; create table CopiedViewFromTable select * from ViewFromTable; create table TableFromTable select * from t1 left join t2 on t2.dbid = t1.id; create view ViewFromView as select * from t1 left join vt2 on vt2.dbid = t1.id; create table CopiedViewFromView select * from ViewFromView; create table TableFromView select * from t1 left join vt2 on vt2.dbid = t1.id; show warnings; select * from ViewFromTable; -- 1 select * from CopiedViewFromTable; -- 2 select * from TableFromTable; -- 3 select * from ViewFromView; -- 4 select * from CopiedViewFromView; -- 5 select * from TableFromView; -- 6 desc ViewFromTable; -- 1 desc CopiedViewFromTable; -- 2 desc TableFromTable; -- 3 desc ViewFromView; -- 4 desc CopiedViewFromView; -- 5 desc TableFromView; -- 6 ------------------------ Results Results of select statement 1-6 should be same, but the result from 5 and 6 differs from the others. Result from 1-4 id name dbid str 1 blue 1 A 2 black NULL NULL 3 red 3 B Result from 5-6 id name dbid str 1 blue 1 A 2 black 0 3 red 3 B Warnings and results of describe command show that null is not allowed in column 'dbid' and 'str'. warning: NULL supplied to NOT NULL column DESC result from 1-4 Field Type Null Default id int(11) NO 0 name char(9) or varchar(9) NO dbid int(11) YES NULL str char(30) or varchar(30) YES NULL DESC result from 5-6 Field Type Null Default id int(11) NO 0 name char(9) NO dbid int(11) NO 0 str char(30) NO Suggested fix: This problem won't be happen if the view is created with directive 'algorithm = temptable'. or this problem can be avoided when a table is created with definition as below, CREATE TABLE CopiedViewFromView (id int, name varchar(9), dbid int, str varchar(30)) SELECT * FROM ViewFromView;