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: | |
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
[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>