| Bug #118849 | after add HINT:JOIN_ORDER, the result is wrong. | ||
|---|---|---|---|
| Submitted: | 18 Aug 12:14 | Modified: | 18 Aug 15:32 |
| Reporter: | Alice Alice | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[18 Aug 15:28]
MySQL Verification Team
Hi,
Had to change it a bit:
mysql> INSERT INTO `tbl_1_all_typeindex` VALUES
-> (67, _binary 'fff2', 'ccc', '{"key1": 1}', _binary 'ª'),
-> (69, _binary 'fff2', 'ccc', '{"key1": 1}', _binary '\0'),
-> (85, NULL, NULL, NULL, _binary ''),
-> (87, NULL, NULL, NULL, _binary 'ª'),
-> (89, NULL, NULL, NULL, _binary '\0'),
-> (91, NULL, NULL, NULL, _binary ''),
-> (93, NULL, NULL, NULL, _binary ''),
-> (95, NULL, NULL, NULL, _binary ''),
-> (97, NULL, NULL, NULL, _binary 'ª'),
-> (99, NULL, NULL, NULL, _binary '\0'),
-> (101, NULL, NULL, NULL, _binary ''),
-> (103, NULL, NULL, NULL, _binary ''),
-> (105, NULL, NULL, NULL, _binary ''),
-> (107, NULL, NULL, NULL, _binary 'ª'),
-> (109, NULL, NULL, NULL, _binary '\0'),
-> (111, NULL, NULL, NULL, _binary ''),
-> (113, NULL, NULL, NULL, _binary '');
ERROR 1406 (22001): Data too long for column 'bit_col' at row 1
mysql> INSERT INTO `tbl_1_all_typeindex` VALUES
-> (67, _binary 'fff2', 'ccc', '{"key1": 1}', _binary '\1'),
-> (69, _binary 'fff2', 'ccc', '{"key1": 1}', _binary '\0'),
-> (85, NULL, NULL, NULL, _binary '\1'),
-> (87, NULL, NULL, NULL, _binary '\1'),
-> (89, NULL, NULL, NULL, _binary '\0'),
-> (91, NULL, NULL, NULL, _binary ''),
-> (93, NULL, NULL, NULL, _binary ''),
-> (95, NULL, NULL, NULL, _binary '\1'),
-> (97, NULL, NULL, NULL, _binary '\1'),
-> (99, NULL, NULL, NULL, _binary '\0'),
-> (101, NULL, NULL, NULL, _binary ''),
-> (103, NULL, NULL, NULL, _binary ''),
-> (105, NULL, NULL, NULL, _binary '\1'),
-> (107, NULL, NULL, NULL, _binary '\1'),
-> (109, NULL, NULL, NULL, _binary '\0'),
-> (111, NULL, NULL, NULL, _binary ''),
-> (113, NULL, NULL, NULL, _binary '');
Query OK, 17 rows affected (0.001 sec)
Records: 17 Duplicates: 0 Warnings: 0
mysql>
[18 Aug 15:29]
MySQL Verification Team
mysql> SELECT col1, col2, ifnull(col2,col3) as col3, col4, col3-col4 col5,if(strcmp(col1,col2),col1,col2) as col6,case when strcmp(col1,col3) then col1 else col3 end as col7 FROM tbl_1_all_typeindex b JOIN (SELECT blob_col col1,json_col col2,bit_col col3,SUM(enum_col) col4 FROM tbl_1_all_type2index GROUP BY 1,2,3 HAVING 0=1 or blob_col not in ('ffffffffff1')) a on 0=0 or (a.col2,a.col3) <= (select json_col,bit_col from tbl_1_all_typeindex order by 1,2 limit 1) where 0=0 and a.col3 = blob_col GROUP BY 1,2,3,4,5,6,7 HAVING 0=1 or col1 not in ('f') ORDER BY 1,2,3,4,5,6,7 limit 10 offset 1;
+------------+-------------+--------------------------+------+------+------------+------------+
| col1 | col2 | col3 | col4 | col5 | col6 | col7 |
+------------+-------------+--------------------------+------+------+------------+------------+
| 0x66666632 | {"key1": 1} | 0x7B226B657931223A20317D | 3 | -3 | 0x66666632 | 0x66666632 |
+------------+-------------+--------------------------+------+------+------------+------------+
1 row in set, 1 warning (0.006 sec)
mysql> SELECT /*+ JOIN_ORDER(b,a) */ col1, col2, ifnull(col2,col3) as col3, col4, col3-col4 col5,if(strcmp(col1,col2),col1,col2) as col6,case when strcmp(col1,col3) then col1 else col3 end as col7 FROM tbl_1_all_typeindex b JOIN (SELECT blob_col col1,json_col col2,bit_col col3,SUM(enum_col) col4 FROM tbl_1_all_type2index GROUP BY 1,2,3 HAVING 0=1 or blob_col not in ('ffffffffff1')) a on 0=0 or (a.col2,a.col3) <= (select json_col,bit_col from tbl_1_all_typeindex order by 1,2 limit 1) where 0=0 and a.col3 = blob_col GROUP BY 1,2,3,4,5,6,7 HAVING 0=1 or col1 not in ('f') ORDER BY 1,2,3,4,5,6,7 limit 10 offset 1;
Empty set (0.002 sec)
mysql>
[18 Aug 15:32]
MySQL Verification Team
Hi, The result set content (returned rows/columns) should be the same, provided the hint does not cause the optimizer to produce a different execution plan that could change row ordering or tie-breaking for otherwise unordered results. But in your case, since you have an ORDER BY clause, the result order is deterministic. This is a verified bug, thank you for the report.
Description: after add HINT:JOIN_ORDER, the result is wrong. mysql> SELECT col1, col2, ifnull(col2,col3) as col3, col4, col3-col4 col5,if(strcmp(col1,col2),col1,col2) as col6,case when strcmp(col1,col3) then col1 else col3 end as col7 FROM tbl_1_all_typeindex b JOIN (SELECT blob_col col1,json_col col2,bit_col col3,SUM(enum_col) col4 FROM tbl_1_all_type2index GROUP BY 1,2,3 HAVING 0=1 or blob_col not in ('ffffffffff1')) a on 0=0 or (a.col2,a.col3) <= (select json_col,bit_col from tbl_1_all_typeindex order by 1,2 limit 1) where 0=0 and a.col3 = blob_col GROUP BY 1,2,3,4,5,6,7 HAVING 0=1 or col1 not in ('f') ORDER BY 1,2,3,4,5,6,7 limit 10 offset 1; +------------+-------------+--------------------------+------+------+------------+------------+ | col1 | col2 | col3 | col4 | col5 | col6 | col7 | +------------+-------------+--------------------------+------+------+------------+------------+ | 0x66666632 | {"key1": 1} | 0x7B226B657931223A20317D | 3 | -3 | 0x66666632 | 0x66666632 | +------------+-------------+--------------------------+------+------+------------+------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT /*+ JOIN_ORDER(b,a) */ col1, col2, ifnull(col2,col3) as col3, col4, col3-col4 col5,if(strcmp(col1,col2),col1,col2) as col6,case when strcmp(col1,col3) then col1 else col3 end as col7 FROM tbl_1_all_typeindex b JOIN (SELECT blob_col col1,json_col col2,bit_col col3,SUM(enum_col) col4 FROM tbl_1_all_type2index GROUP BY 1,2,3 HAVING 0=1 or blob_col not in ('ffffffffff1')) a on 0=0 or (a.col2,a.col3) <= (select json_col,bit_col from tbl_1_all_typeindex order by 1,2 limit 1) where 0=0 and a.col3 = blob_col GROUP BY 1,2,3,4,5,6,7 HAVING 0=1 or col1 not in ('f') ORDER BY 1,2,3,4,5,6,7 limit 10 offset 1; Empty set (0.00 sec) How to repeat: DROP TABLE IF EXISTS `tbl_1_all_type2index`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `tbl_1_all_type2index` ( `id_col` int NOT NULL AUTO_INCREMENT, `blob_col` blob, `enum_col` enum('aaa','bbb','ccc') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, `json_col` json DEFAULT NULL, `bit_col` bit(8) DEFAULT b'0', PRIMARY KEY (`id_col`), KEY `ndx_id_decimal_col` (`enum_col`), KEY `ndx_bigint_time_col` (`enum_col` DESC), KEY `ndx_timestamp_varchar_col` (`enum_col`), KEY `ndx_datetime_timestamp_col` (`enum_col`) ) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='NONE'; /*!40101 SET character_set_client = @saved_cs_client */; INSERT INTO `tbl_1_all_type2index` VALUES (9,_binary 'ffffffffff1','bbb','{\"key2\": 2}',_binary '\0'),(19,_binary 'ffffffffff1','bbb','{\"key2\": 2}',_binary '\0'),(29,_binary 'ffffffffff1','bbb','{\"key2\": 2}',_binary '\0'),(39,_binary 'ffffffffff1','bbb','{\"key2\": 2}',_binary '\0'),(49,_binary 'ffffffffff1','bbb','{\"key2\": 2}',_binary '\0'),(59,_binary 'ffffffffff1','bbb','{\"key2\": 2}',_binary '\0'),(69,_binary 'fff2','ccc','{\"key1\": 1}',_binary '\0'),(79,_binary 'a','aaa','{\"key\": 1, \"key2\": 2}',_binary '\0'),(89,NULL,NULL,NULL,_binary '\0'),(99,NULL,NULL,NULL,_binary '\0'),(109,NULL,NULL,NULL,_binary '\0'); DROP TABLE IF EXISTS `tbl_1_all_typeindex`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `tbl_1_all_typeindex` ( `id_col` int NOT NULL AUTO_INCREMENT, `blob_col` blob, `enum_col` enum('aaa','bbb','ccc') CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL, `json_col` json DEFAULT NULL, `bit_col` bit(8) DEFAULT b'0', UNIQUE KEY `id_col` (`id_col`) ) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='ZLIB'; INSERT INTO `tbl_1_all_typeindex` VALUES (67,_binary 'fff2','ccc','{\"key1\": 1}',_binary 'ª'),(69,_binary 'fff2','ccc','{\"key1\": 1}',_binary '\0'),(85,NULL,NULL,NULL,_binary 'ÿ'),(87,NULL,NULL,NULL,_binary 'ª'),(89,NULL,NULL,NULL,_binary '\0'),(91,NULL,NULL,NULL,_binary '\(93,NULL,NULL,NULL,_binary ''),(95,NULL,NULL,NULL,_binary 'ÿ'),(97,NULL,NULL,NULL,_binary 'ª'),(99,NULL,NULL,NULL,_binary '\0'),(101,NULL,NULL,NULL,_binary '\(103,NULL,NULL,NULL,_binary ''),(105,NULL,NULL,NULL,_binary 'ÿ'),(107,NULL,NULL,NULL,_binary 'ª'),(109,NULL,NULL,NULL,_binary '\0'),(111,NULL,NULL,NULL,_binary '\(113,NULL,NULL,NULL,_binary ''); SELECT col1, col2, ifnull(col2,col3) as col3, col4, col3-col4 col5,if(strcmp(col1,col2),col1,col2) as col6,case when strcmp(col1,col3) then col1 else col3 end as col7 FROM tbl_1_all_typeindex b JOIN (SELECT blob_col col1,json_col col2,bit_col col3,SUM(enum_col) col4 FROM tbl_1_all_type2index GROUP BY 1,2,3 HAVING 0=1 or blob_col not in ('ffffffffff1')) a on 0=0 or (a.col2,a.col3) <= (select json_col,bit_col from tbl_1_all_typeindex order by 1,2 limit 1) where 0=0 and a.col3 = blob_col GROUP BY 1,2,3,4,5,6,7 HAVING 0=1 or col1 not in ('f') ORDER BY 1,2,3,4,5,6,7 limit 10 offset 1; SELECT /*+ JOIN_ORDER(b,a) */ col1, col2, ifnull(col2,col3) as col3, col4, col3-col4 col5,if(strcmp(col1,col2),col1,col2) as col6,case when strcmp(col1,col3) then col1 else col3 end as col7 FROM tbl_1_all_typeindex b JOIN (SELECT blob_col col1,json_col col2,bit_col col3,SUM(enum_col) col4 FROM tbl_1_all_type2index GROUP BY 1,2,3 HAVING 0=1 or blob_col not in ('ffffffffff1')) a on 0=0 or (a.col2,a.col3) <= (select json_col,bit_col from tbl_1_all_typeindex order by 1,2 limit 1) where 0=0 and a.col3 = blob_col GROUP BY 1,2,3,4,5,6,7 HAVING 0=1 or col1 not in ('f') ORDER BY 1,2,3,4,5,6,7 limit 10 offset 1; Suggested fix: the last two queries shall return same results.