When truncate is NULL, 1 BETWEEN truncate() AND 1 outputs unexpected 1

1. Minimal reproduce step (Required)

create table t1 ( c1 int primary key);
insert into t1 (c1) values (1), (2), (3), (4), (5), (6), (7), (11), (12), (13), (14), (15), (16), (17), (21), (22), (23), (24), (25), (26), (27), (116), (127), (121), (122), (113), (214), (251), (261), (217), (91), (92), (39), (94), (95), (69), (79), (191), (129);

create view v2 as select 0 as q2 from t1;

select distinct
  1 between NULL and 1 as w0, 
  truncate(1, (cast(ref_1.q2 as unsigned) % 0)) as w1,
  (1 between truncate(1, (cast(ref_1.q2 as unsigned) % 0)) and 1) as w2
from 
  (v2 as ref_0 inner join v2 as ref_1 on (1=1))
;

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

The third column should not have 1
in TiDB with unistore:

mysql> select distinct   1 between NULL and 1 as w0,    truncate(1, (cast(ref_1.q2 as unsigned) % 0)) as w1,   (1
between truncate(1, (cast(ref_1.q2 as unsigned) % 0)) and 1) as w2 from    (v2 as ref_0 inner join v2 as ref_1 on
(1=1));
+------+------+------+
| w0   | w1   | w2   |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+
1 row in set, 3042 warnings (0.05 sec)

3. What did you see instead (Required)

in TiDB with TiKV

mysql> select distinct
    ->   1 between NULL and 1 as w0, 
    ->   truncate(1, (cast(ref_1.q2 as unsigned) % 0)) as w1,
    ->   (1 between truncate(1, (cast(ref_1.q2 as unsigned) % 0)) and 1) as w2
    -> from 
    ->   (v2 as ref_0 inner join v2 as ref_1 on (1=1))
    -> ;
+------+------+------+
| w0   | w1   | w2   |
+------+------+------+
| NULL | NULL |    1 |
| NULL | NULL | NULL |
+------+------+------+
2 rows in set, 1880 warnings (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v8.5.0-alpha-184-g1c059a1216
Edition: Community
Git Commit Hash: 1c059a1216db711e2cb56ea9f3d1ad8c23db6327
Git Branch: HEAD
UTC Build Time: 2024-11-21 04:56:22
GoVersion: go1.23.3
Race Enabled: false
Check Table Before Drop: false
Store: tikv

topology:
distributed.yaml:

global:
  user: "tidb"
  ssh_port: 22
  deploy_dir: "/tidb-deploy"
  data_dir: "/tidb-data"

tidb_servers:
  - host: 10.0.2.81

pd_servers:
  - host: 10.0.2.82

tikv_servers:
  - host: 10.0.2.83

about us

We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned vulnerabilities in TiDB that may lead to database logic error.

Root cause:

In "and" operator, we check the warning count to decide whether to fallback to rowByRow calculation(between is compiled to and(1, ge(1, truncate(blablabla))):

tidb/pkg/expression/builtin_op_vec.go

Lines 381 to 387 in b11d034

    
           beforeArg1Warns := ctx.WarningCount() 
        
           err = b.args[1].VecEvalInt(ctx, input, buf1) 
        
           afterArg1Warns := ctx.WarningCount() 
        
           if err != nil || afterArg1Warns > beforeArg1Warns { 
        
           	ctx.TruncateWarnings(beforeArg0Warns) 
        
           	return b.fallbackEvalInt(ctx, input, result) 
        
           }

The projection has concurrency, at the same time mod operation generates "divide by 0" warnings continuously, thus the total expression calculation of "and" function fallback to rowByRow mode

Unluckily, we have a wrong short-path in truncate rowByRow mode calculation, which may return non-null value even if the second argument is null:

tidb/pkg/expression/builtin_math.go

Lines 2160 to 2162 in b11d034

    
           if mysql.HasUnsignedFlag(b.args[1].GetType(ctx).GetFlag()) { 
        
           	return x, false, nil 
        
           }

BTW, during to the point 1, the warning count is random in this case.

tidb/pkg/expression/builtin_op_vec.go

Lines 381 to 387 in b11d034

    
           beforeArg1Warns := ctx.WarningCount() 
        
           err = b.args[1].VecEvalInt(ctx, input, buf1) 
        
           afterArg1Warns := ctx.WarningCount() 
        
           if err != nil || afterArg1Warns > beforeArg1Warns { 
        
           	ctx.TruncateWarnings(beforeArg0Warns) 
        
           	return b.fallbackEvalInt(ctx, input, result) 
        
           }

/severity moderate

expression: fix incorrect short path in `truncate` function (#58656)

expression: fix incorrect short path in `truncate` function (#58656) (#…

Navigation Menu

Repository navigation

When `truncate` is NULL, `1 BETWEEN truncate() AND 1` outputs unexpected 1 #57608

1. Minimal reproduce step (Required)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

about us

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	beforeArg1Warns := ctx.WarningCount()
	err = b.args[1].VecEvalInt(ctx, input, buf1)
	afterArg1Warns := ctx.WarningCount()
	if err != nil \|\| afterArg1Warns > beforeArg1Warns {
	ctx.TruncateWarnings(beforeArg0Warns)
	return b.fallbackEvalInt(ctx, input, result)
	}

	if mysql.HasUnsignedFlag(b.args[1].GetType(ctx).GetFlag()) {
	return x, false, nil
	}

When truncate is NULL, 1 BETWEEN truncate() AND 1 outputs unexpected 1 #57608

Description

1. Minimal reproduce step (Required)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

about us

Activity

yibin87 commented on Nov 26, 2024

xzhangxian1008 commented on Jan 2, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

When `truncate` is NULL, `1 BETWEEN truncate() AND 1` outputs unexpected 1 #57608