| Bug #87264 | Query not returning expected results | ||
|---|---|---|---|
| Submitted: | 31 Jul 2017 21:49 | Modified: | 2 Aug 2017 7:33 |
| Reporter: | Tao Li | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.6.36, 5.6.37, 5.7.19 | OS: | CentOS (release 6.9) |
| Assigned to: | CPU Architecture: | Any | |
[31 Jul 2017 21:49]
Tao Li
[1 Aug 2017 0:21]
MySQL Verification Team
Thank you for the bug report. You must provide a repeatable test case (dump with create table/insert data, queries, real result and expected one) you can zip the files and attach the Files tab, otherwise we can't repeat. Thanks.
[1 Aug 2017 21:30]
Tao Li
mysql dump file to restore db
Attachment: dump.sql (application/octet-stream, text), 298.21 KiB.
[1 Aug 2017 21:31]
Tao Li
Query file to start a transaction
Attachment: query.txt (text/plain), 269.29 KiB.
[1 Aug 2017 21:39]
Tao Li
1. Use dump.sql to restore db state 2. Run queries from query.txt by running "source <path>" from mysql shell. The file starts with a transaction. 3. Run below query and see 24 rows returned select * from PARTITION_KEY_VALS where PART_ID in (8343,8145,8213,8108,8326,8219) order by PART_ID asc, INTEGER_IDX asc; Then run the below query (which has INTEGER_IDX >= 0) and only see 15 rows, which is wrong. select * from PARTITION_KEY_VALS where PART_ID in (8343,8145,8213,8108,8326,8219) and INTEGER_IDX >= 0 order by PART_ID asc, INTEGER_IDX asc; 4. run "rollback;" to quit transaction without committing the changes.
[2 Aug 2017 7:33]
MySQL Verification Team
Thank you for providing requested details. Observed this with 5.6.37 and 5.7.19.
[31 Aug 2017 8:18]
ashe sun
hello,在5.7.19 debug版本中，没有遇到你说的问题，我等下试下官方的二进制版本。 mysql> select @@version; +------------------+ | @@version | +------------------+ | 5.7.19-debug-log | +------------------+ 1 row in set (0.00 sec) mysql> select * from PARTITION_KEY_VALS where PART_ID in (8343,8145,8213,8108,8326,8219) order by PART_ID asc, INTEGER_IDX asc; +---------+---------------+-------------+ | PART_ID | PART_KEY_VAL | INTEGER_IDX | +---------+---------------+-------------+ | 8108 | zh-TW | 0 | | 8108 | Windows Phone | 1 | | 8108 | HTC | 2 | | 8108 | T8788 | 3 | | 8145 | zh-TW | 0 | | 8145 | Windows Phone | 1 | | 8145 | HTC | 2 | | 8145 | Schubert | 3 | | 8213 | zh-TW | 0 | | 8213 | Windows Phone | 1 | | 8213 | HTC | 2 | | 8213 | T7575 | 3 | | 8219 | zh-TW | 0 | | 8219 | Windows Phone | 1 | | 8219 | NOKIA | 2 | | 8219 | 800 | 3 | | 8326 | zh-TW | 0 | | 8326 | Windows Phone | 1 | | 8326 | HTC | 2 | | 8326 | mwp6985 | 3 | | 8343 | zh-TW | 0 | | 8343 | Windows Phone | 1 | | 8343 | HTC | 2 | | 8343 | Omega | 3 | +---------+---------------+-------------+ 24 rows in set (0.00 sec) mysql> select * from PARTITION_KEY_VALS where PART_ID in (8343,8145,8213,8108,8326,8219) and INTEGER_IDX >= 0 order by PART_ID asc, INTEGER_IDX asc; +---------+---------------+-------------+ | PART_ID | PART_KEY_VAL | INTEGER_IDX | +---------+---------------+-------------+ | 8108 | zh-TW | 0 | | 8108 | Windows Phone | 1 | | 8108 | HTC | 2 | | 8108 | T8788 | 3 | | 8145 | zh-TW | 0 | | 8145 | Windows Phone | 1 | | 8145 | HTC | 2 | | 8145 | Schubert | 3 | | 8213 | zh-TW | 0 | | 8213 | Windows Phone | 1 | | 8213 | HTC | 2 | | 8213 | T7575 | 3 | | 8219 | zh-TW | 0 | | 8219 | Windows Phone | 1 | | 8219 | NOKIA | 2 | | 8219 | 800 | 3 | | 8326 | zh-TW | 0 | | 8326 | Windows Phone | 1 | | 8326 | HTC | 2 | | 8326 | mwp6985 | 3 | | 8343 | zh-TW | 0 | | 8343 | Windows Phone | 1 | | 8343 | HTC | 2 | | 8343 | Omega | 3 | +---------+---------------+-------------+ 24 rows in set (0.01 sec)
[31 Aug 2017 9:04]
ashe sun
hello Umesh I have not encountered this bug on my environment。 mysql> select * from PARTITION_KEY_VALS where PART_ID in (8343,8145,8213,8108,8326,8219) and INTEGER_IDX >= 0 order by PART_ID asc, INTEGER_IDX asc; +---------+---------------+-------------+ | PART_ID | PART_KEY_VAL | INTEGER_IDX | +---------+---------------+-------------+ | 8108 | zh-TW | 0 | | 8108 | Windows Phone | 1 | | 8108 | HTC | 2 | | 8108 | T8788 | 3 | | 8145 | zh-TW | 0 | | 8145 | Windows Phone | 1 | | 8145 | HTC | 2 | | 8145 | Schubert | 3 | | 8213 | zh-TW | 0 | | 8213 | Windows Phone | 1 | | 8213 | HTC | 2 | | 8213 | T7575 | 3 | | 8219 | zh-TW | 0 | | 8219 | Windows Phone | 1 | | 8219 | NOKIA | 2 | | 8219 | 800 | 3 | | 8326 | zh-TW | 0 | | 8326 | Windows Phone | 1 | | 8326 | HTC | 2 | | 8326 | mwp6985 | 3 | | 8343 | zh-TW | 0 | | 8343 | Windows Phone | 1 | | 8343 | HTC | 2 | | 8343 | Omega | 3 | +---------+---------------+-------------+ 24 rows in set (0.05 sec) mysql> mysql> mysql> select * from PARTITION_KEY_VALS where PART_ID in (8343,8145,8213,8108,8326,8219) order by PART_ID asc, INTEGER_IDX asc; +---------+---------------+-------------+ | PART_ID | PART_KEY_VAL | INTEGER_IDX | +---------+---------------+-------------+ | 8108 | zh-TW | 0 | | 8108 | Windows Phone | 1 | | 8108 | HTC | 2 | | 8108 | T8788 | 3 | | 8145 | zh-TW | 0 | | 8145 | Windows Phone | 1 | | 8145 | HTC | 2 | | 8145 | Schubert | 3 | | 8213 | zh-TW | 0 | | 8213 | Windows Phone | 1 | | 8213 | HTC | 2 | | 8213 | T7575 | 3 | | 8219 | zh-TW | 0 | | 8219 | Windows Phone | 1 | | 8219 | NOKIA | 2 | | 8219 | 800 | 3 | | 8326 | zh-TW | 0 | | 8326 | Windows Phone | 1 | | 8326 | HTC | 2 | | 8326 | mwp6985 | 3 | | 8343 | zh-TW | 0 | | 8343 | Windows Phone | 1 | | 8343 | HTC | 2 | | 8343 | Omega | 3 | +---------+---------------+-------------+ 24 rows in set (0.00 sec) mysql> mysql> select @@verison; ERROR 1193 (HY000): Unknown system variable 'verison' mysql> mysql> mysql> select @@version; +------------+ | @@version | +------------+ | 5.7.19-log | +------------+ 1 row in set (0.00 sec)
