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
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.045 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.