diff options
| author | unknown <timour@askmonty.org> | 2012-12-17 15:23:58 +0200 |
|---|---|---|
| committer | unknown <timour@askmonty.org> | 2012-12-17 15:23:58 +0200 |
| commit | d7a0148758335f3237f0b78a074be4a0a06944dc (patch) | |
| tree | ba09a292b2bdab55d16688e8b00833faf1c536dc /mysql-test | |
| parent | a334e87d6569f71324a10e104074668fc483c520 (diff) | |
| download | mariadb-git-d7a0148758335f3237f0b78a074be4a0a06944dc.tar.gz | |
MDEV-3818: Query against view over IS tables worse than equivalent query without view
Analysis:
The reason for the suboptimal plan when querying IS tables through a view
was that the view columns that participate in an equality are wrapped by
an Item_direct_view_ref and were not recognized as being direct column
references.
Solution:
Use the original Item_field objects via the real_item() method.
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/information_schema.result | 20 | ||||
| -rw-r--r-- | mysql-test/t/information_schema.test | 18 |
2 files changed, 38 insertions, 0 deletions
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 9d993e723c2..437c2aa0f84 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1934,6 +1934,26 @@ event_object_table trigger_name # Switching to connection 'default'. # # +# MDEV-3818: Query against view over IS tables worse than equivalent query without view +# +CREATE VIEW v1 AS SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS; +explain extended +SELECT column_name FROM v1 +WHERE (TABLE_SCHEMA = "osm") AND (TABLE_NAME = "test"); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE COLUMNS ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases +Warnings: +Note 1003 select `information_schema`.`COLUMNS`.`COLUMN_NAME` AS `COLUMN_NAME` from `INFORMATION_SCHEMA`.`COLUMNS` where ((`information_schema`.`COLUMNS`.`TABLE_SCHEMA` = 'osm') and (`information_schema`.`COLUMNS`.`TABLE_NAME` = 'test')) +explain extended +SELECT INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AS COLUMN_NAME +FROM INFORMATION_SCHEMA.COLUMNS +WHERE (information_schema.COLUMNS.TABLE_SCHEMA = 'osm') and (information_schema.COLUMNS.TABLE_NAME = 'test'); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE COLUMNS ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases +Warnings: +Note 1003 select `information_schema`.`COLUMNS`.`COLUMN_NAME` AS `COLUMN_NAME` from `INFORMATION_SCHEMA`.`COLUMNS` where ((`information_schema`.`COLUMNS`.`TABLE_SCHEMA` = 'osm') and (`information_schema`.`COLUMNS`.`TABLE_NAME` = 'test')) +drop view v1; +# # Clean-up. drop database mysqltest; # diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index e95f41f6c8d..9ed61a90be2 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1789,6 +1789,24 @@ disconnect con12828477_1; disconnect con12828477_2; disconnect con12828477_3; + +--echo # +--echo # MDEV-3818: Query against view over IS tables worse than equivalent query without view +--echo # + +CREATE VIEW v1 AS SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS; + +explain extended +SELECT column_name FROM v1 +WHERE (TABLE_SCHEMA = "osm") AND (TABLE_NAME = "test"); + +explain extended +SELECT INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME AS COLUMN_NAME +FROM INFORMATION_SCHEMA.COLUMNS +WHERE (information_schema.COLUMNS.TABLE_SCHEMA = 'osm') and (information_schema.COLUMNS.TABLE_NAME = 'test'); + +drop view v1; + --echo # --echo # Clean-up. drop database mysqltest; |