Bug #93703 | EXPLAIN SELECT returns inconsistent number of ROWS in main.group_by | ||
---|---|---|---|
Submitted: | 20 Dec 2018 12:30 | Modified: | 22 Jan 2019 5:56 |
Reporter: | Przemysław Skibiński (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Tests | Severity: | S7 (Test Cases) |
Version: | 8.0.13, 8.0.14 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[20 Dec 2018 12:30]
Przemysław Skibiński
[16 Jan 2019 8:06]
MySQL Verification Team
Hello Przemysław, Thank you for the report and test case. I'm not seeing any issues at my end using provided test case. cat t/bug93703.test CREATE TABLE t1 (a INT, b INT, KEY(a)); INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4); CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a; EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a; EXPLAIN SELECT a FROM t1 GROUP BY a; EXPLAIN SELECT a FROM t2 GROUP BY a; ./mtr --nocheck-testcases bug93703 Logging: ./mtr --nocheck-testcases bug93703 MySQL Version 8.0.13 Checking supported features... Using 'all' suites Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/export/umesh/server/binaries/GABuilds/mysql-8.0.13/mysql-test/var'... Installing system database... Using parallel: 1 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13019 CREATE TABLE t1 (a INT, b INT, KEY(a)); INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4); CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL a NULL NULL NULL 4 100.00 Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` IGNORE INDEX (`a`) group by `test`.`t1`.`a` EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ALL a NULL NULL NULL 4 100.00 Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` IGNORE INDEX (`a`) group by `test`.`t2`.`a` EXPLAIN SELECT a FROM t1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index a a 5 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` EXPLAIN SELECT a FROM t2 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 5 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` group by `test`.`t2`.`a` [100%] main.bug93703 [ pass ] 48 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.048 of 8 seconds executing testcases Completed: All 1 tests were successful. Please let me know if anything I'm missing here. Thank you! regards, Umesh
[21 Jan 2019 14:58]
Przemysław Skibiński
Hi Umesh, Please try debug server with Ubuntu Bionic: ./mysql-test/mtr --debug-server bug93703 I have the following results at every run: CREATE TABLE t1 (a INT, b INT, KEY(a)); INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4); CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL a NULL NULL NULL 5 100.00 Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` IGNORE INDEX (`a`) group by `test`.`t1`.`a` EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ALL a NULL NULL NULL 4 100.00 Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` IGNORE INDEX (`a`) group by `test`.`t2`.`a`
[22 Jan 2019 5:56]
MySQL Verification Team
Thank you, Przemysław. In the second attempt observed the issue. -- cat /etc/*release DISTRIB_ID=Ubuntu DISTRIB_RELEASE=18.04 DISTRIB_CODENAME=bionic DISTRIB_DESCRIPTION="Ubuntu 18.04 LTS" NAME="Ubuntu" VERSION="18.04 LTS (Bionic Beaver)" ID=ubuntu ID_LIKE=debian PRETTY_NAME="Ubuntu 18.04 LTS" VERSION_ID="18.04" HOME_URL="https://www.ubuntu.com/" SUPPORT_URL="https://help.ubuntu.com/" BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/" PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy" VERSION_CODENAME=bionic UBUNTU_CODENAME=bionic - ushastry@BionicBeaver:~/Downloads/mysql-8.0.14$ cat docs/INFO_SRC commit: 775cb233ec89476150e7a040e372a39e4a64eb81 date: 2018-12-20 21:05:15 +0100 build-date: 2018-12-20 21:14:22 +0100 short: 775cb23 branch: mysql-8.0.14-release MySQL source 8.0.14 ushastry@BionicBeaver:~/Downloads/mysql-8.0.14/mysql-test$ ./mtr --debug-server --nocheck-testcases bug93703 Logging: ./mtr --debug-server --nocheck-testcases bug93703 MySQL Version 8.0.14 Checking supported features - Binaries are debug compiled Using 'all' suites Collecting tests Checking leftover processes Removing old var directory Creating var directory '/home/ushastry/Downloads/mysql-8.0.14/mysql-test/var' Installing system database Using parallel: 1 ============================================================================== TEST NAME RESULT TIME (ms) COMMENT ------------------------------------------------------------------------------ CREATE TABLE t1 (a INT, b INT, KEY(a)); INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4); CREATE TABLE t2 (a INT, b INT, KEY(a)); INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4); EXPLAIN SELECT a FROM t1 IGNORE INDEX (a) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL ALL a NULL NULL NULL 5 100.00 Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` IGNORE INDEX (`a`) group by `test`.`t1`.`a` EXPLAIN SELECT a FROM t2 IGNORE INDEX (a) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL ALL a NULL NULL NULL 4 100.00 Using temporary Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` IGNORE INDEX (`a`) group by `test`.`t2`.`a` EXPLAIN SELECT a FROM t1 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL index a a 5 NULL 5 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` group by `test`.`t1`.`a` EXPLAIN SELECT a FROM t2 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index a a 5 NULL 4 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` group by `test`.`t2`.`a` [100%] main.bug93703 [ pass ] 460 ------------------------------------------------------------------------------ The servers were restarted 0 times Spent 0.460 of 52 seconds executing testcases