Bug #34666 | Incomplete metadata returned to client on view + group by + Innodb/Falcon | ||
---|---|---|---|
Submitted: | 19 Feb 2008 14:11 | Modified: | 28 Apr 2008 12:15 |
Reporter: | Philip Stoev | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.0/5.1/6.0.4 | OS: | Any |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[19 Feb 2008 14:11]
Philip Stoev
[20 Feb 2008 0:20]
MySQL Verification Team
Thank you for the bug report. c:\dbs>5.1\bin\mysql -uroot test --column-type-info Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.1.24-rc-nt-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t1 (id int(10)) ENGINE INNODB; Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO t1 VALUES (1); Query OK, 1 row affected (0.06 sec) mysql> DROP VIEW IF EXISTS v3; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE VIEW v3 AS SELECT t1.id + 12 AS renamed FROM t1; Query OK, 0 rows affected (0.08 sec) mysql> SELECT * FROM v3 WHERE renamed = 1 GROUP BY renamed; Field 1: `renamed` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 12 Max_length: 0 Decimals: 0 Flags: BINARY NUM 0 rows in set (0.05 sec) mysql> ALTER TABLE t1 ENGINE MyISAM; Query OK, 1 row affected (0.16 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM v3 WHERE renamed = 1 GROUP BY renamed; Field 1: `renamed` Catalog: `def` Database: `` Table: `v3` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 12 Max_length: 0 Decimals: 0 Flags: BINARY NUM 0 rows in set (0.00 sec) mysql>
[27 Feb 2008 14:56]
Konstantin Osipov
View metadata bug, setting the right lead.
[10 Apr 2008 15:25]
Georgi Kodinov
Actually the bug is not directly related to Innodb or falcon. It's just that the optimizer has much better statistics about the data when using MyISAM tables. So it can be sure that a table has 1 row when optimizing and thus do the select from this table at compile time and use the results to check for impossible where (which is what the SELECT statement here triggers). So for MyISAM it's detecting the impossible WHERE and acting accordingly. The behavior is unified across storage engines when you put in two or more rows. The real problem is that : CREATE VIEW v1 as SELECT a + 1 as calculated FROM t1 SELECT calculated from v1 group by calculated is not returning the same metadata as : SELECT calculated from (SELECT a + 1 as calculated FROM t1) v1 group by calculated