Bug #19089 CREATE TABLE ... SELECT * FROM a view with an ENUM column and GROUP BY fails
Submitted: 13 Apr 2006 20:49 Modified: 5 Jun 2006 21:14
Reporter: Beat Vontobel (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.20 OS:Any (any)
Assigned to: Igor Babaev CPU Architecture:Any

[13 Apr 2006 20:49] Beat Vontobel
Description:
A CREATE TABLE ... SELECT statement that selects an ENUM column (defined as NOT NULL in the base table) from a VIEW fails with "ERROR 1067 (42000): Invalid default value" if the VIEW contains a GROUP BY clause.

How to repeat:
CREATE TABLE t (e ENUM('a') NOT NULL);

CREATE VIEW v AS SELECT * FROM t GROUP BY e;

CREATE TABLE c1 SELECT * FROM t GROUP BY e;
CREATE TABLE c2 SELECT * FROM v;

c1 succeeds, but c2 (the same thing from a VIEW) fails with "ERROR 1067 (42000): Invalid default value for e"

Suggested fix:
-
[15 Apr 2006 12:18] Hartmut Holzgraefe
mysqltest test case

Attachment: bug19089.tar.gz (application/x-gunzip, text), 509 bytes.

[8 May 2006 4:07] Igor Babaev
I observed another minifestation of this bug:

mysql> CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx');
Query OK, 0 rows affected (0.26 sec)

mysql> INSERT INTO t1(id) VALUES (1), (2), (3), (4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 VALUES (5,'yyy'), (6,'yyy');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+----+------+
| id | a    |
+----+------+
|  1 | xxx  |
|  2 | xxx  |
|  3 | xxx  |
|  4 | xxx  |
|  5 | yyy  |
|  6 | yyy  |
+----+------+
6 rows in set (0.00 sec)

mysql>
mysql> CREATE VIEW v1(a, m) AS SELECT a, MIN(id) FROM t1 GROUP BY a;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v1;
+------+------+
| a    | m    |
+------+------+
| xxx  |    1 |
| yyy  |    5 |
+------+------+
2 rows in set (0.01 sec)

mysql>
mysql> CREATE TABLE t2 SELECT * FROM v1;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2(m) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| a    | m    |
+------+------+
| xxx  |    1 |
| yyy  |    5 |
| NULL |    0 |
+------+------+
3 rows in set (0.00 sec)

Apprently that the last row should contain no NULL values.
[21 May 2006 1:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6673
[22 May 2006 14:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6710
[24 May 2006 8:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6816
[31 May 2006 23:47] Igor Babaev
When a CREATE TABLE command created a table from a materialized
  view id does not inherit default values from the underlying table.
  Moreover the temporary table used for the view materialization
  does not inherit those default values.
  In the case when the underlying table contained ENUM fields it caused
  misleading error messages. In other cases the created table contained
  wrong default values.
  The code was modified to ensure inheritance of default values for
  materialized views.

ChangeSet
  1.2123 06/05/20 18:54:43 igor@rurik.mysql.com +5 -0
ChangeSet
  1.2135 06/05/22 07:57:46 igor@rurik.mysql.com +4 -0
ChangeSet
  1.2139 06/05/24 11:56:59 monty@mysql.com +7 -0

The last changeset by monty contains the patch that actually re-architectured the original fix.

Fix will appear in 5.0.23 and 5.1.11
[5 Jun 2006 21:14] Mike Hillyer
Documented in changelog:

    <listitem>
        <para>
          A <literal>CREATE TABLE</literal> statement that created a
          table from a materialized view did not inherit default values
          from the underlying table. (Bug #19089)
        </para>
      </listitem>