search

pingcap / tidb

TiDB is an open-source, cloud-native, distributed, MySQL-Compatible database for elastic scale and real-time analytics. Try AI-powered Chat2Query free at : https://www.pingcap.com/tidb-serverless/
https://pingcap.com
Apache License 2.0
36.86k stars 5.8k forks source link

incorrect query result using ISNULL #54803

Closed wjhuang2016 closed 1 month ago

wjhuang2016 commented 1 month ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t1db47fc1` (
  `col_67` time NOT NULL DEFAULT '16:58:45',
  `col_68` tinyint(3) unsigned DEFAULT NULL,
  `col_69` bit(6) NOT NULL DEFAULT b'11110',
  `col_72` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY HASH (`col_68`) PARTITIONS 5;

INSERT INTO `t1db47fc1` VALUES ('09:36:56',31,x'00',122.24588206656426), ('09:36:56',197,x'00',122.24588206656426), ('09:36:56',193,x'00',122.24588206656426), ('09:36:56',164,x'00',122.24588206656426), ('09:36:56',70,x'00',122.24588206656426), ('09:36:56',82,x'00',122.24588206656426), ('03:50:07',137,x'13',4875.021178245137), ('09:36:56',181,x'00',122.24588206656426), ('09:36:56',209,x'00',122.24588206656426), ('09:36:56',85,x'00',122.24588206656426), ('09:36:56',87,x'00',1779.277511572647), ('09:36:56',66,x'00',122.24588206656426), ('09:36:56',106,x'00',122.24588206656426), ('09:36:56',31,x'00',122.24588206656426), ('09:36:56',237,x'00',122.24588206656426), ('09:36:56',59,x'00',122.24588206656426), ('09:36:56',171,x'00',122.24588206656426), ('09:36:56',89,x'00',122.24588206656426), ('09:36:56',209,x'00',122.24588206656426), ('09:36:56',NULL,x'00',122.24588206656426), ('09:36:56',142,x'00',122.24588206656426), ('09:36:56',138,x'00',122.24588206656426), ('09:36:56',NULL,x'00',122.24588206656426), ('09:36:56',17,x'00',122.24588206656426), ('09:36:56',73,x'00',122.24588206656426), ('09:36:56',112,x'00',122.24588206656426), ('09:36:56',28,x'00',122.24588206656426), ('09:36:56',203,x'00',122.24588206656426), ('09:36:56',232,x'00',122.24588206656426), ('09:36:56',20,x'00',122.24588206656426), ('09:36:56',195,x'00',122.24588206656426), ('09:36:56',2,x'00',122.24588206656426), ('09:36:56',35,x'00',122.24588206656426), ('09:36:56',121,x'00',122.24588206656426), ('09:36:56',111,x'00',122.24588206656426), ('09:36:56',52,x'00',1779.277511572647), ('09:36:56',136,x'00',122.24588206656426), ('09:36:56',126,x'00',122.24588206656426), ('09:36:56',173,x'00',122.24588206656426), ('09:36:56',170,x'00',122.24588206656426), ('09:36:56',52,x'00',1779.277511572647), ('09:36:56',16,x'00',122.24588206656426), ('09:36:56',110,x'00',122.24588206656426), ('09:36:56',56,x'00',122.24588206656426), ('09:36:56',100,x'00',122.24588206656426), ('09:36:56',NULL,x'00',122.24588206656426), ('09:36:56',38,x'00',122.24588206656426), ('09:36:56',202,x'00',122.24588206656426), ('09:36:56',82,x'00',122.24588206656426), ('09:36:56',110,x'00',122.24588206656426), ('09:36:56',96,x'00',122.24588206656426), ('09:36:56',165,x'00',122.24588206656426), ('09:36:56',37,x'00',122.24588206656426), ('09:36:56',190,x'00',122.24588206656426), ('09:36:56',185,x'00',122.24588206656426), ('09:36:56',156,x'00',122.24588206656426), ('09:36:56',153,x'00',122.24588206656426), ('09:36:56',221,x'00',122.24588206656426), ('09:36:56',218,x'00',122.24588206656426), ('09:36:56',127,x'00',122.24588206656426), ('09:36:56',186,x'00',122.24588206656426), ('09:36:56',209,x'00',122.24588206656426), ('09:36:56',27,x'00',122.24588206656426), ('09:36:56',31,x'00',122.24588206656426), ('09:36:56',184,x'00',122.24588206656426), ('09:36:56',39,x'00',122.24588206656426), ('09:36:56',127,x'00',122.24588206656426), ('09:36:56',170,x'00',122.24588206656426), ('09:36:56',175,x'00',122.24588206656426), ('09:36:56',141,x'00',122.24588206656426), ('09:36:56',77,x'00',122.24588206656426), ('09:36:56',65,x'00',122.24588206656426), ('09:36:56',2,x'00',122.24588206656426), ('09:36:56',238,x'00',122.24588206656426), ('09:36:56',142,x'00',122.24588206656426), ('09:36:56',164,x'00',122.24588206656426), ('09:36:56',61,x'00',122.24588206656426), ('09:36:56',201,x'00',122.24588206656426), ('09:36:56',239,x'00',122.24588206656426), ('09:36:56',238,x'00',122.24588206656426), ('09:36:56',190,x'00',122.24588206656426), ('09:36:56',62,x'00',122.24588206656426), ('09:36:56',193,x'00',122.24588206656426), ('09:36:56',203,x'00',122.24588206656426), ('09:36:56',238,x'00',122.24588206656426), ('09:36:56',238,x'00',122.24588206656426), ('09:36:56',238,x'00',122.24588206656426), ('09:36:56',238,x'00',122.24588206656426), ('05:26:45',112,x'10',734.7885623865941), ('07:20:21',15,x'01',2964.4315631218133), ('09:36:56',NULL,x'00',9340.874344324497), ('09:36:56',18,x'00',2502.7536842684317), ('16:03:02',239,x'17',9735.462351850127), ('19:38:37',207,x'2b',5301.634836855874), ('22:17:43',112,x'12',9753.281480523972), ('23:24:59',137,x'05',4890.175273407417), ('12:20:28',205,x'2f',4007.5723017321557), ('09:36:56',127,x'00',9483.638745637576), ('05:25:31',9,x'16',7503.664503434865), ('17:32:27',126,x'11',7966.662786537855), ('08:27:53',223,x'34',14.627502910041219), ('02:10:51',17,x'0f',3486.4315417897797), ('04:36:44',255,x'1f',7050.707263070245), ('09:36:56',108,x'00',4568.301195698245), ('01:28:10',72,x'03',2244.99419792915), ('15:01:57',210,x'16',4049.1946762974485), ('18:24:27',2,x'35',8218.36743118453), ('07:56:44',114,x'0a',4436.205146592797), ('02:13:54',15,x'38',4880.274576611283), ('22:50:14',95,x'09',7953.624880365652), ('11:48:40',8,x'0c',4875.4926716046975), ('12:06:57',52,x'09',1779.277511572647), ('01:06:49',50,x'3a',1499.4325603710363), ('16:22:32',59,x'15',2180.3007657333033), ('06:44:54',24,x'18',4292.626531005586), ('12:01:36',128,x'27',482.45879862909027), ('07:31:46',116,x'0d',2557.2048331582114), ('18:35:36',2,x'33',1118.208744706535), ('07:33:38',NULL,x'07',3302.349889798098), ('12:40:23',70,x'35',6793.364307236022), ('10:01:30',126,x'0b',7430.426382902264), ('22:05:21',1,x'08',1166.098908791206), ('11:44:11',205,x'09',4464.465471390726), ('05:55:53',156,x'00',6844.612864481644), ('13:00:50',88,x'14',7557.3798174498015), ('12:26:12',177,x'2b',2036.9238383919255), ('10:08:02',NULL,x'2e',1488.5325007153765), ('16:03:11',128,x'1d',5322.131984461228), ('09:15:35',44,x'30',2775.6679451266305), ('01:44:15',206,x'22',2367.4850407518807), ('07:37:35',141,x'3e',1940.8032700268993), ('21:09:03',1,x'24',2602.475951640343), ('01:55:52',168,x'08',1582.1802216278138), ('03:31:20',104,x'11',1978.410890115393), ('22:43:10',82,x'38',3746.6759596384704), ('15:06:49',211,x'2a',6540.502820865758), ('10:48:18',14,x'18',6150.460356621456), ('05:05:22',66,x'27',6707.618164955623), ('11:22:41',223,x'27',4434.504816866472), ('14:34:47',1,x'2f',6718.099451453515), ('17:23:57',181,x'00',8387.549319494787), ('10:48:39',211,x'02',4791.843902312558), ('23:31:38',37,x'26',9301.312653786392), ('12:22:54',239,x'00',4946.920703718034), ('01:47:36',101,x'36',4882.380118722788), ('05:06:54',66,x'16',1237.881583588924), ('07:46:49',107,x'20',7865.5487669107615), ('04:41:45',136,x'21',3772.3081049930706), ('12:19:56',210,x'37',5620.0049306554065), ('07:21:53',126,x'1a',1176.6130587590358), ('21:00:40',193,x'04',6279.53377182129), ('23:48:06',190,x'14',6993.408908461073), ('15:39:08',139,x'08',662.0884598788416), ('12:05:35',175,x'00',1121.642782799416), ('12:23:24',63,x'12',7008.574102626612), ('05:51:59',226,x'30',1829.0414179863978), ('19:54:16',93,x'0e',7550.301613336231), ('09:36:56',77,x'00',8937.294307780387), ('09:36:56',77,x'00',8937.294307780387), ('07:16:33',77,x'37',8937.294307780387), ('14:12:23',77,x'01',8937.294307780387), ('09:36:56',77,x'00',8937.294307780387), ('09:31:05',155,x'3b',4223.846273016973), ('06:30:07',190,x'12',5149.309620455332), ('23:32:23',2,x'13',6275.75610072306), ('01:08:48',222,x'2a',8073.006336938704), ('14:54:07',11,x'0d',6564.768289649105), ('00:40:34',165,x'1e',616.0271331880743), ('04:21:56',205,x'24',9844.948077948486), ('01:38:19',113,x'11',9917.55561666248), ('20:02:20',119,x'1e',1559.3625355742136), ('21:13:16',200,x'0a',9546.784666988327), ('14:12:15',103,x'1b',1617.9554864854445), ('14:45:55',97,x'0f',962.233419876431), ('12:04:42',0,x'1b',3546.0795998870485), ('10:24:23',174,x'04',1791.1969510680294), ('13:32:06',78,x'1d',8612.603259830246), ('00:22:11',143,x'08',2056.1928094523178), ('11:51:23',143,x'2c',4323.315193182945), ('01:01:00',119,x'03',2927.1407952163418), ('01:13:25',215,x'2e',5427.171839880901), ('15:37:08',55,x'20',1764.2518040064886), ('14:03:33',11,x'10',7933.539954185864), ('15:47:09',254,x'34',6136.033652760992), ('20:02:55',194,x'16',9661.204355073609), ('07:12:27',85,x'3d',3271.9793871971847), ('07:13:39',90,x'3b',9728.636154009837), ('15:22:27',0,x'02',4021.21560105941), ('12:25:50',191,x'1e',8875.840012679952), ('01:25:01',129,x'35',1907.127136740281), ('02:32:20',159,x'16',108.14658039741968);

SELECT TRIM(`t1db47fc1`.`col_68`) AS `r0` FROM `t1db47fc1` WHERE ISNULL(`t1db47fc1`.`col_68`) GROUP BY `t1db47fc1`.`col_68` HAVING ISNULL(`t1db47fc1`.`col_68`) OR `t1db47fc1`.`col_68` IN (62,200,196,99) LIMIT 106149535;

2. What did you expect to see? (Required)

mysql> SELECT TRIM(`t1db47fc1`.`col_68`) AS `r0` FROM `t1db47fc1` WHERE ISNULL(`t1db47fc1`.`col_68`) GROUP BY `t1db47fc1`.`col_68` HAVING ISNULL(`t1db47fc1`.`col_68`) OR `t1db47fc1`.`col_68` IN (62,200,196,99) LIMIT 106149535;
+------+
| r0   |
+------+
| NULL |
+------+
1 row in set (0.01 sec)

3. What did you see instead (Required)

mysql> SELECT TRIM(`t1db47fc1`.`col_68`) AS `r0` FROM `t1db47fc1` WHERE ISNULL(`t1db47fc1`.`col_68`) GROUP BY `t1db47fc1`.`col_68` HAVING ISNULL(`t1db47fc1`.`col_68`) OR `t1db47fc1`.`col_68` IN (62,200,196,99) LIMIT 106149535;
Empty set (0.00 sec)

4. What is your TiDB version? (Required)

8edd4ed543765ce63a6f7298c443d46993d93a2b

wjhuang2016 commented 1 month ago

It's likely caused by https://github.com/pingcap/tidb/commit/41ed0e508d901753f4fac3bd3aae9b13bd202481

hawkingrei commented 1 month ago

Before https://github.com/pingcap/tidb/commit/41ed0e508d901753f4fac3bd3aae9b13bd202481

+----------------------------+----------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------+
| id                         | estRows  | task      | access object   | operator info                                                                                                 |
+----------------------------+----------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------+
| Projection_11              | 8.00     | root      |                 | trim(cast(test.t1db47fc1.col_68, var_string(20)))->Column#7                                                   |
| └─Limit_14                 | 8.00     | root      |                 | offset:0, count:106149535                                                                                     |
|   └─HashAgg_17             | 8.00     | root      |                 | group by:test.t1db47fc1.col_68, funcs:firstrow(test.t1db47fc1.col_68)->test.t1db47fc1.col_68                  |
|     └─TableReader_24       | 10.00    | root      | partition:p0    | data:Selection_23                                                                                             |
|       └─Selection_23       | 10.00    | cop[tikv] |                 | isnull(test.t1db47fc1.col_68), or(isnull(test.t1db47fc1.col_68), in(test.t1db47fc1.col_68, 62, 200, 196, 99)) |
|         └─TableFullScan_22 | 10000.00 | cop[tikv] | table:t1db47fc1 | keep order:false, stats:pseudo                                                                                |
+----------------------------+----------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------+

After https://github.com/pingcap/tidb/commit/41ed0e508d901753f4fac3bd3aae9b13bd202481

+----------------------------+---------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------+
| id                         | estRows | task      | access object   | operator info                                                                                                 |
+----------------------------+---------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------+
| Projection_11              | 1.00    | root      |                 | trim(cast(test.t1db47fc1.col_68, var_string(20)))->Column#7                                                   |
| └─Limit_14                 | 1.00    | root      |                 | offset:0, count:106149535                                                                                     |
|   └─HashAgg_17             | 1.00    | root      |                 | group by:test.t1db47fc1.col_68, funcs:firstrow(test.t1db47fc1.col_68)->test.t1db47fc1.col_68                  |
|     └─TableReader_24       | 0.19    | root      | partition:dual  | data:Selection_23                                                                                             |
|       └─Selection_23       | 0.19    | cop[tikv] |                 | isnull(test.t1db47fc1.col_68), or(isnull(test.t1db47fc1.col_68), in(test.t1db47fc1.col_68, 62, 200, 196, 99)) |
|         └─TableFullScan_22 | 191.00  | cop[tikv] | table:t1db47fc1 | keep order:false, stats:pseudo                                                                                |
+----------------------------+---------+-----------+-----------------+---------------------------------------------------------------------------------------------------------------+