| Bug #75681 | using index for group-by not behaving as intended | ||
|---|---|---|---|
| Submitted: | 29 Jan 2015 13:10 | Modified: | 19 Feb 2015 14:13 |
| Reporter: | Carsten Bohuslav | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.6.21-1~dotdeb.1 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Optimizer | ||
[29 Jan 2015 13:10]
Carsten Bohuslav
[29 Jan 2015 14:19]
MySQL Verification Team
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.6 > CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)
mysql 5.6 > USE d1;
Database changed
mysql 5.6 > CREATE TABLE `catalog_product_entity` (
-> `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
-> `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set ID',
-> PRIMARY KEY (`entity_id`),
-> KEY `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`)
-> )ENGINE=InnoDB AUTO_INCREMENT=29589 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table';
Query OK, 0 rows affected (0.19 sec)
mysql 5.6 >
mysql 5.6 > INSERT INTO `catalog_product_entity` VALUES (20554,62),(20553,70);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.6 > SELECT DISTINCT `attribute_set_id` FROM `catalog_product_entity` WHERE `entity_id` IN (20554,20553);
+------------------+
| attribute_set_id |
+------------------+
| 62 |
| 70 |
+------------------+
2 rows in set (0.00 sec)
mysql 5.6 > set session optimizer_switch='use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)
mysql 5.6 > SELECT DISTINCT `attribute_set_id` FROM `catalog_product_entity` WHERE `entity_id` IN (20554,20553);
+------------------+
| attribute_set_id |
+------------------+
| 62 |
| 70 |
+------------------+
2 rows in set (0.00 sec)
[29 Jan 2015 14:23]
MySQL Verification Team
Please check with latest release 5.6.22:
C:\dbs>C:\dbs\mysql-5.6.22-winx64\bin\mysql -uroot --port=3306 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.6 > CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)
mysql 5.6 > USE d1;
Database changed
mysql 5.6 > CREATE TABLE `catalog_product_entity` (
-> `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
-> `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set ID',
-> PRIMARY KEY (`entity_id`),
-> KEY `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`)
-> )ENGINE=InnoDB AUTO_INCREMENT=29589 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table';
Query OK, 0 rows affected (0.19 sec)
mysql 5.6 >
mysql 5.6 > INSERT INTO `catalog_product_entity` VALUES (20554,62),(20553,70);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql 5.6 > SELECT DISTINCT `attribute_set_id` FROM `catalog_product_entity` WHERE `entity_id` IN (20554,20553);
+------------------+
| attribute_set_id |
+------------------+
| 62 |
| 70 |
+------------------+
2 rows in set (0.00 sec)
mysql 5.6 > set session optimizer_switch='use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)
mysql 5.6 > SELECT DISTINCT `attribute_set_id` FROM `catalog_product_entity` WHERE `entity_id` IN (20554,20553);
+------------------+
| attribute_set_id |
+------------------+
| 62 |
| 70 |
+------------------+
2 rows in set (0.02 sec)
[29 Jan 2015 14:34]
Carsten Bohuslav
Thx for Information, we will need to wait for the dotdeb Repo to udpdate to 5.6.22 then i will retest it.
[29 Jan 2015 23:10]
MySQL Verification Team
Tested on CentOS 6.5 Linux:
[miguel@vbcentos65 mysql-5.6.22-linux-glibc2.5-x86_64]$ bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `catalog_product_entity` (
-> `entity_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
-> `attribute_set_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute Set ID',
-> PRIMARY KEY (`entity_id`),
-> KEY `IDX_CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID` (`attribute_set_id`)
-> )ENGINE=InnoDB AUTO_INCREMENT=29589 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Table';
Query OK, 0 rows affected (0.10 sec)
mysql>
mysql> INSERT INTO `catalog_product_entity` VALUES (20554,62),(20553,70)
-> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> SELECT DISTINCT `attribute_set_id` FROM `catalog_product_entity` WHERE `entity_id` IN (20554,20553);
+------------------+
| attribute_set_id |
+------------------+
| 62 |
| 70 |
+------------------+
2 rows in set (0.00 sec)
mysql> set session optimizer_switch='use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT DISTINCT `attribute_set_id` FROM `catalog_product_entity` WHERE `entity_id` IN (20554,20553);
+------------------+
| attribute_set_id |
+------------------+
| 62 |
| 70 |
+------------------+
2 rows in set (0.00 sec)
mysql>
[19 Feb 2015 14:05]
Carsten Bohuslav
I have another case where this Problem appears. I will prepare MySQL Dumps and Sample Query later today.
[19 Feb 2015 14:12]
Carsten Bohuslav
Table
Attachment: catalog_product_index_eav.sql (application/octet-stream, text), 739.61 KiB.
[19 Feb 2015 14:13]
Carsten Bohuslav
The Query which makes Problems in the other case is this one: SELECT DISTINCT value FROM catalog_product_index_eav WHERE store_id=3 AND attribute_id=81; And depening on SET SESSION optimizer_switch='use_index_extensions=on'; or SET SESSION optimizer_switch='use_index_extensions=off'; The results are diffrent. And with use_index_extensions=off the result is empty.
