SQL Execution Yields Inexplicable Results

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

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

| username: TiDB_C罗

Bug Report
Clearly and accurately describe the issue you have found. Providing any possible steps to reproduce the issue will help the development team address it promptly.
[TiDB Version]
v5.4.0
[Impact of the Bug]
The query returns inexplicable results.
[Possible Steps to Reproduce the Issue]
Original Query

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

The result of subquery t1 is
±------±----------±--------------±------------------+
| 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 |
±------±----------±--------------±------------------+

The result obtained using the min and max functions is
±----------------------------±-----------------------------+
| min | max |
±----------------------------±-----------------------------+
| 13314398617609 | 13314398617609 |
±----------------------------±-----------------------------+

Finally, the query was rewritten using 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;

The correct result was obtained
±----------------------------±-----------------------------+
| min | max |
±----------------------------±-----------------------------+
| 20000 | 35100 |
±----------------------------±-----------------------------+

Storing the result of subquery t1 into a temporary table also yielded the correct result

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

[Observed Unexpected Behavior]

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

[Expected Behavior]

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

| username: onlyacat | Original post link

Could you provide the test cases, table structure, table data, and explain results?

| username: TiDB_C罗 | Original post link

Table structure

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) unsigned 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='Orders table'

Problematic query execution plan:

+------------------------------------+---------+-----------+-------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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), <nil>)->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), <nil>)->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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+------------------------------------+---------+-----------+-------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Rewritten using 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, <nil>)->Column#68, if(and(and(gt(a.orders.apply_time, 0), gt(a.orders.stage_num, 1)), le(Column#65, 720)), a.orders.loan, <nil>)->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)

| username: jansu-dev | Original post link

Follow this issue --》 max/min got wrong result · Issue #43805 · pingcap/tidb (github.com)

| username: TiDB_C罗 | Original post link

Looking at the execution plan, there is a cast conversion, but I don’t understand why a cast conversion is needed.

| username: zanmato | Original post link

Our engineers have provided an analysis of the issue on the GitHub issue page. Please take a look.

The fix PR has been submitted and is currently under review. We will fix this issue in a future release. If you need a workaround, you can temporarily rewrite using WITH AS.

Thank you~

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.