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