| Bug #34666 | Incomplete metadata returned to client on view + group by + Innodb/Falcon | ||
|---|---|---|---|
| Submitted: | 19 Feb 2008 15:11 | Modified: | 28 Apr 2008 14:15 |
| Reporter: | Philip Stoev | ||
| Status: | Verified | ||
| Category: | Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0/5.1/6.0.4 | OS: | Any |
| Assigned to: | Evgeny Potemkin | Target Version: | |
| Triage: | Triaged: D2 (Serious) | ||
[20 Feb 2008 1:20]
Miguel Solorzano
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 15:56]
Konstantin Osipov
View metadata bug, setting the right lead.
[10 Apr 2008 17: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
Description: When doing a GROUP BY on views containing a Falcon or Innodb table, the server will not return the name of the view as part of the metadata returned to the client. How to repeat: --enable_metadata CREATE TABLE t1 (id int(10)); INSERT INTO t1 VALUES (1); CREATE VIEW v3 AS SELECT t1.id + 12 AS renamed FROM t1; SELECT * FROM v3 WHERE renamed = 1 GROUP BY renamed; Running this case produces: $ perl mysql-test-run.pl --mysqld=--default-storage-engine=myisam metadata_bug Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def v3 renamed 8 12 0 Y 32896 0 63 renamed $ perl mysql-test-run.pl --mysqld=--default-storage-engine=innodb metadata_bug Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def renamed 8 12 0 Y 32896 0 63 renamed Observe that "v3" was returned for MyISAM and nothing was returned for Innodb. Nothing is returned for Falcon as well.