Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

the results is wrong because of bug in bigint(45) handle column comparing #33175

Closed
ChenPeng2013 opened this issue Mar 17, 2022 · 1 comment · Fixed by #33218
Closed

the results is wrong because of bug in bigint(45) handle column comparing #33175

ChenPeng2013 opened this issue Mar 17, 2022 · 1 comment · Fixed by #33218
Assignees
Labels
affects-4.0 This bug affects 4.0.x versions. affects-5.0 This bug affects 5.0.x versions. affects-5.1 This bug affects 5.1.x versions. affects-5.2 This bug affects 5.2.x versions. affects-5.3 This bug affects 5.3.x versions. affects-5.4 This bug affects 5.4.x versions. affects-6.0 severity/critical sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@ChenPeng2013
Copy link
Contributor

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
source [test.txt](https://github.com/pingcap/tidb/files/8280923/test.txt)
alter table PK_SIGNED_10093 cache;

# you can execute many times to reproduce
select col1, col2 from PK_SIGNED_10093 where col1 > (select max(col1) from PK_SIGNED_10093 t1 where t1.col1 > 0);

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

MySQL [test]> select col1, col2 from PK_SIGNED_10093 where col1 > (select max(col1) from PK_SIGNED_10093 t1 where t1.col1 > 0);
Empty set (0.00 sec)

3. What did you see instead (Required)

MySQL [test]> select col1, col2 from PK_SIGNED_10093 where col1 > (select max(col1) from PK_SIGNED_10093 t1 where t1.col1 > 0);
+----------------------+--------------------------------------------------------------+
.....
+----------------------+--------------------------------------------------------------+
50 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

Release Version: v6.0.0-alpha-122-g46c43fe
Edition: Community
Git Commit Hash: 46c43febcac0e24bc9f0872e93cf6125db69d93d
Git Branch: master
UTC Build Time: 2022-03-17 01:02:30
GoVersion: go1.17.6
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
@ChenPeng2013 ChenPeng2013 added type/bug The issue is confirmed as a bug. sig/sql-infra SIG: SQL Infra severity/critical feature/developing the related feature is in development labels Mar 17, 2022
@ti-chi-bot ti-chi-bot added may-affects-4.0 This bug maybe affects 4.0.x versions. may-affects-5.0 This bug maybe affects 5.0.x versions. may-affects-5.1 This bug maybe affects 5.1.x versions. may-affects-5.2 This bug maybe affects 5.2.x versions. may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.4 This bug maybe affects 5.4.x versions. labels Mar 17, 2022
@ChenPeng2013 ChenPeng2013 removed may-affects-4.0 This bug maybe affects 4.0.x versions. may-affects-5.1 This bug maybe affects 5.1.x versions. may-affects-5.2 This bug maybe affects 5.2.x versions. may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-5.0 This bug maybe affects 5.0.x versions. labels Mar 17, 2022
@tiancaiamao tiancaiamao added sig/planner SIG: Planner and removed feature/developing the related feature is in development labels Mar 17, 2022
@tiancaiamao
Copy link
Contributor

Again, this is neither a subquery nor a bug of table cache...
It's a general one of the UnionScan executor, or you can say it's a planner bug.
Here is the minimal reproduce:

mysql> create table t (id bigint(45) unsigned not null, c varchar(20), primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (9734095886065816707, 'a'), (10353107668348738101, 'b'), (0, 'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values (33, 'd');
Query OK, 1 row affected (0.00 sec)

mysql> select max(id) from t;
+---------+
| max(id) |
+---------+
|      33 |
+---------+
1 row in set (0.00 sec)

@ChenPeng2013 ChenPeng2013 changed the title table-cache: the results is wrong in subquery statement the results is wrong because UnionScan doesn't keep order Mar 17, 2022
@tiancaiamao tiancaiamao changed the title the results is wrong because UnionScan doesn't keep order the results is wrong because of bug in bigint(45) handle column comparing Mar 17, 2022
@bb7133 bb7133 removed the sig/sql-infra SIG: SQL Infra label Mar 18, 2022
@ChenPeng2013 ChenPeng2013 added affects-4.0 This bug affects 4.0.x versions. affects-5.0 This bug affects 5.0.x versions. labels May 5, 2022
@ChenPeng2013 ChenPeng2013 added affects-5.1 This bug affects 5.1.x versions. affects-5.2 This bug affects 5.2.x versions. affects-5.3 This bug affects 5.3.x versions. affects-5.4 This bug affects 5.4.x versions. labels May 5, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
affects-4.0 This bug affects 4.0.x versions. affects-5.0 This bug affects 5.0.x versions. affects-5.1 This bug affects 5.1.x versions. affects-5.2 This bug affects 5.2.x versions. affects-5.3 This bug affects 5.3.x versions. affects-5.4 This bug affects 5.4.x versions. affects-6.0 severity/critical sig/planner SIG: Planner type/bug The issue is confirmed as a bug.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

5 participants