SQL执行得到莫名其妙的结果

TiDB_C罗 · 2023 年5 月 9 日 08:20

Bug 反馈
清晰准确地描述您发现的问题，提供任何可能复现问题的步骤有助于研发同学及时处理问题
【 TiDB 版本】
v5.4.0
【 Bug 的影响】
查询得到莫名其妙的结果
【可能的问题复现步骤】
原查询

select
min(if(apply_time > 0 and stage_num > 1 and apply_to_now_days <= 30,loan,null)) as min,
max(if(apply_time > 0 and stage_num > 1 and apply_to_now_days <= 720,loan,null)) as max
from
(select
loan,stage_num,apply_time,
datediff(from_unixtime(unix_timestamp() + 18000), from_unixtime(apply_time/1000 + 18000)) as apply_to_now_days
from order where account_id = 210802010000721168 and id != 1) t1

子查询t1的结果为
±------±----------±--------------±------------------+
| loan | stage_num | apply_time | apply_to_now_days |
±------±----------±--------------±------------------+
| 8800 | 0 | 1648454711139 | 407 |
| 1000 | 0 | 0 | 19486 |
| 25000 | 0 | 0 | 19486 |
| 1000 | 0 | 0 | 19486 |
| 25000 | 0 | 0 | 19486 |
| 3100 | 0 | 1647262477029 | 421 |
| 10000 | 0 | 0 | 19486 |
| 35000 | 0 | 1657189859291 | 306 |
| 35000 | 0 | 1654768902733 | 334 |
| 35000 | 0 | 1659605647822 | 278 |
| 30000 | 0 | 1660164034063 | 271 |
| 30000 | 0 | 1663246103369 | 236 |
| 29700 | 0 | 1668073744928 | 180 |
| 29700 | 0 | 1672824379359 | 125 |
| 29700 | 0 | 1670406570078 | 153 |
| 20000 | 0 | 1677827765308 | 67 |
| 29700 | 0 | 1665661999253 | 208 |
| 30000 | 0 | 1660796131772 | 264 |
| 20000 | 0 | 1675404540416 | 95 |
| 20000 | 2 | 1682484268727 | 13 |
| 20000 | 0 | 1680264429988 | 39 |
| 35100 | 4 | 1650885615002 | 379 |
±------±----------±--------------±------------------+

使用min,max函数得到的结果为
±----------------------------±-----------------------------+
| min | max |
±----------------------------±-----------------------------+
| 13314398617609 | 13314398617609 |
±----------------------------±-----------------------------+

最终使用with改写查询

with t1 as (select
loan,stage_num,apply_time,
datediff(from_unixtime(unix_timestamp() + 18000), from_unixtime(apply_time/1000 + 18000)) as apply_to_now_days
from order where account_id = 210802010000721168 and id != 1)
select
min(if(apply_time > 0 and stage_num > 1 and apply_to_now_days <= 30,loan,null)) as min,
max(if(apply_time > 0 and stage_num > 1 and apply_to_now_days <= 720,loan,null)) as max
from t1;
得到了正确的结果
±----------------------------±-----------------------------+
| min | max |
±----------------------------±-----------------------------+
| 20000 | 35100 |
±----------------------------±-----------------------------+

把子查询t1的结果存入到一个临时表，得到的结果也是正确的
select
min(if(apply_time > 0 and stage_num > 1 and apply_to_now_days <= 30,loan,null)) as min,
max(if(apply_time > 0 and stage_num > 1 and apply_to_now_days <= 720,loan,null)) as max
from tmp
【看到的非预期行为】

±----------------------------±-----------------------------+
| min | max |
±----------------------------±-----------------------------+
| 13314398617609 | 13314398617609 |
±----------------------------±-----------------------------+

【期望看到的行为】

±----------------------------±-----------------------------+
| min | max |
±----------------------------±-----------------------------+
| 20000 | 35100 |
±----------------------------±-----------------------------+

onlyacat · 2023 年5 月 10 日 02:49

能提供下测试用例不，表结构，表数据，explain 结果

TiDB_C罗 · 2023 年5 月 10 日 12:55

表结构

CREATE TABLE orders (
id bigint(20) unsigned NOT NULL ,
account_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
product_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
product_id_org bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
loan bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
loan_org bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
float_rate int(11) NOT NULL DEFAULT ‘0’ ,
float_rate_org int(11) NOT NULL DEFAULT ‘0’ ,
period int(10) unsigned NOT NULL DEFAULT ‘0’ ,
period_org int(10) unsigned NOT NULL DEFAULT ‘0’ ,
pre_order bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
roll_times int(10) NOT NULL DEFAULT ‘0’ ,
min_repay_amount bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
op_uid bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
risk_ctl_status smallint(2) unsigned NOT NULL DEFAULT ‘0’ ,
risk_ctl_finish_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
risk_ctl_regular varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’ ,
reject_reason smallint(2) unsigned NOT NULL DEFAULT ‘0’ ,
is_temporary tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
is_overdue tinyint(2) NOT NULL DEFAULT ‘0’ ,
is_dead_debt tinyint(1) unsigned NOT NULL DEFAULT ‘0’ ,
is_reloan tinyint(1) unsigned NOT NULL DEFAULT ‘0’ ,
is_up_hold_photo tinyint(1) NOT NULL DEFAULT ‘0’ ,
check_status tinyint(1) unsigned NOT NULL DEFAULT ‘1’ ,
apply_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
check_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
phone_verify_time bigint(20) unsigned NOT NULL DEFAULT ‘0’,
repay_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
loan_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
finish_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
penalty_utime bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
credit_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
random_value tinyint(3) unsigned NOT NULL DEFAULT ‘0’ ,
fixed_random int(10) NOT NULL DEFAULT ‘0’ ,
livingbest_reloanhand_similar varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’ ,
after_black_similar varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’ ,
random_mark int(10) NOT NULL DEFAULT ‘0’,
op_desc varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’ ,
from_platform tinyint(2) NOT NULL DEFAULT ‘0’ ,
stage_num int(20) unsigned NOT NULL DEFAULT ‘0’ ,
stage_info text COLLATE utf8mb4_unicode_ci DEFAULT NULL ,
third_paid_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
amount bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
overdue_run_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
risk_hyrule_status int(10) DEFAULT ‘0’ ,
order_invalid_type tinyint(2) unsigned NOT NULL DEFAULT ‘0’ ,
order_invalid_time bigint(20) unsigned NOT NULL DEFAULT ‘0’,
first_diff_tag int(11) unsigned NOT NULL DEFAULT ‘0’ ,
phone_verify_failed_time bigint(20) unsigned NOT NULL DEFAULT ‘0’,
phone_verify_failed_type tinyint(2) unsigned NOT NULL DEFAULT ,
confirm_enter_time bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
loan_flow_version tinyint(5) unsigned NOT NULL DEFAULT ‘0’ ,
confirm_disburse_time bigint(20) NOT NULL DEFAULT ‘0’ ,
offline_handle_time bigint(20) NOT NULL DEFAULT ‘0’ ,
is_jump_confirm smallint(2) NOT NULL DEFAULT ‘0’ ,
is_deleted tinyint(2) NOT NULL DEFAULT ‘0’ ,
orders_ctime bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
orders_utime bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
orders_ext_ctime bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
orders_ext_utime bigint(20) unsigned NOT NULL DEFAULT ‘0’ ,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY idx_orders_account_id (account_id),
KEY idx_orders_apply_time (apply_time),
KEY idx_orders_orders_ctime (orders_ctime),
KEY idx_orders_utime (orders_utime),
KEY idx_orders_ext_utime (orders_ext_utime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=‘订单表’
有问题的查询语句执行计划：
±-----------------------------------±--------±----------±------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-----------------------------------±--------±----------±------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_8 | 1.00 | root | | funcs:min(Column#65)->Column#62, funcs:max(Column#66)->Column#63 |
| └─Projection_36 | 45.21 | root | | if(and(and(gt(a.orders.apply_time, 0), gt(a.orders.stage_num, 1)), le(datediff(2023-05-10 17:45:34, from_unixtime(plus(div(cast(a.orders.apply_time, decimal(20,0) UNSIGNED BINARY), 1000), 18000))), 30)), cast(a.orders.loan, decimal(20,0) UNSIGNED BINARY), )->Column#65, if(and(and(gt(a.orders.apply_time, 0), gt(a.orders.stage_num, 1)), le(datediff(2023-05-10 17:45:34, from_unixtime(plus(div(cast(a.orders.apply_time, decimal(20,0) UNSIGNED BINARY), 1000), 18000))), 720)), cast(a.orders.loan, decimal(20,0) UNSIGNED BINARY), )->Column#66 |
| └─IndexLookUp_18 | 45.21 | root | | |
| ├─Selection_17(Build) | 45.21 | cop[tikv] | | ne(a.orders.id, 1) |
| │ └─IndexRangeScan_15 | 45.21 | cop[tikv] | table:orders, index:idx_orders_account_id(account_id) | range:[210802010000721168,210802010000721168], keep order:false |
| └─TableRowIDScan_16(Probe) | 45.21 | cop[tikv] | table:orders | keep order:false |
±-----------------------------------±--------±----------±------------------------------------------------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

使用with改写后：

±-----------------------------------±--------±----------±------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±-----------------------------------±--------±----------±------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_16 | 1.00 | root | | funcs:min(Column#68)->Column#66, funcs:max(Column#69)->Column#67 |
| └─Projection_20 | 45.21 | root | | if(and(and(gt(a.orders.apply_time, 0), gt(a.orders.stage_num, 1)), le(Column#65, 30)), a.orders.loan, )->Column#68, if(and(and(gt(a.orders.apply_time, 0), gt(a.orders.stage_num, 1)), le(Column#65, 720)), a.orders.loan, )->Column#69 |
| └─CTEFullScan_18 | 45.21 | root | CTE:tmp | data:CTE_0 |
| CTE_0 | 45.21 | root | | Non-Recursive CTE |
| └─Projection_7(Seed Part) | 45.21 | root | | a.orders.loan, a.orders.stage_num, a.orders.apply_time, datediff(2023-05-10 17:46:47, from_unixtime(plus(div(cast(a.orders.apply_time, decimal(20,0) UNSIGNED BINARY), 1000), 18000)))->Column#61 |
| └─IndexLookUp_14 | 45.21 | root | | |
| ├─Selection_13(Build) | 45.21 | cop[tikv] | | ne(a.orders.id, 1) |
| │ └─IndexRangeScan_11 | 45.21 | cop[tikv] | table:orders, index:idx_orders_account_id(account_id) | range:[210802010000721168,210802010000721168], keep order:false |
| └─TableRowIDScan_12(Probe) | 45.21 | cop[tikv] | table:orders | keep order:false |
±-----------------------------------±--------±----------±------------------------------------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)

Aric · 2023 年5 月 15 日 05:42

追这个 issue 吧 --》 max/min got wrong result · Issue #43805 · pingcap/tidb (github.com)

TiDB_C罗 · 2023 年5 月 16 日 09:30

看执行计划有cast转换，但是不明白为什么需要cast转换

zanmato · 2023 年5 月 18 日 02:51

我们的工程师在 github issue 上给出了问题的分析，麻烦留意一下哈。

现在 fix pr 已经发出正在 review，我们会在后续版本中 fix 这个问题。如果需要 workaround，可以暂时使用 with as 改写。

谢谢~

system · 2023 年7 月 17 日 02:51

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。