qw4990
commented
1 year ago Open aytrack opened 1 year ago
qw4990
commented
1 year ago qw4990
commented
1 year ago The minimum reproducible case:
create table t (a smallint);
prepare stmt from 'SELECT * FROM t WHERE a = ? AND a * ? > ?;';
set @a=28145, @b=28145, @c=28145;
execute stmt using @a,@b,@c;
set @a=-360860821470, @b=1037975153909, @c=687344291891;
execute stmt using @a,@b,@c;The new value is too large BIGINT value is out of range in '(-360860821470 * 1037975153909)':
[2023/05/30 15:07:01.237 +08:00] [ERROR] [expr_to_pb.go:108] ["eval constant or correlated column"] [expression="not recognized const vanue"] [error="[types:1690]BIGINT value is out of range in '(-360860821470 * 1037975153909)'"]The minimum reproducible case:
create table t (a smallint); prepare stmt from 'SELECT * FROM t WHERE a = ? AND a * ? > ?;'; set @a=28145, @b=28145, @c=28145; execute stmt using @a,@b,@c; set @a=-360860821470, @b=1037975153909, @c=687344291891; execute stmt using @a,@b,@c;
After const propagation, the predicate a = ? and a * ? > ? is converted to ? * ? > ?.
In the first execution, the result of 28145 * 28145 is OK and can be handled by INT.
In the second execution, the result of -360860821470 * 1037975153909 is too large for INT, and the execution returns some error.
qw4990
commented
1 year ago A minimum reproducible case:
prepare stmt from 'SELECT * FROM t WHERE ? * ? > ?;';
set @a=28145, @b=28145, @c=28145;
execute stmt using @a,@b,@c;
set @a=-360860821470, @b=1037975153909, @c=687344291891;
execute stmt using @a,@b,@c;v6.5.0 has the same issue:
mysql> execute stmt using @a,@b,@c;
ERROR 1815 (HY000): expression gt(mul(-360860821470, 1037975153909), 687344291891) cannot be pushed down
mysql> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.4.0-20230324
Edition: Community
Git Commit Hash: 706c3fa3c526cdba5b3e9f066b1a568fb96c56e3
Git Branch: HEAD
UTC Build Time: 2023-05-30 08:05:32
GoVersion: go1.20.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)I'll fix it later on.
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
follow sql query success
3. What did you see instead (Required)
the plan changed.
query failed plan
query success
mysql> explain for connection 8625184934786498979 ; +-------------------------+---------+---------+-----------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------+--------+------+ | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | +-------------------------+---------+---------+-----------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------+--------+------+ | TableReader_7 | 0.06 | 0 | root | | time:2.01ms, loops:1, cop_task: {num: 1, max: 1.93ms, proc_keys: 74, tot_proc: 127.5µs, tot_wait: 185.4µs, rpc_num: 1, rpc_time: 1.88ms, copr_cache_hit_ratio: 0.00, build_task_duration: 19.9µs, max_distsql_concurrency: 1} | data:Selection_6 | N/A | N/A | | └─Selection_6 | 0.06 | 0 | cop[tikv] | | tikv_task:{time:0s, loops:2}, scan_detail: {total_process_keys: 74, total_process_keys_size: 3922, total_keys: 75, get_snapshot_time: 144.1µs, rocksdb: {key_skipped_count: 74, block: {}}} | eq(new_data.idt_multi15847strobjstrobj.col2, -360860821470), gt(mul(-360860821470, 1037975153909), 687344291891) | N/A | N/A | | └─TableFullScan_5 | 74.00 | 74 | cop[tikv] | table:IDT_MULTI15847STROBJSTROBJ | tikv_task:{time:0s, loops:2} | keep order:false, stats:pseudo | N/A | N/A | +-------------------------+---------+---------+-----------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------+--------+------+