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

Bigint unsigned column value in CASE WHEN operator is converted to bigint signed #9869

Closed
wjhuang2016 opened this issue Mar 23, 2019 · 3 comments · Fixed by #21062
Closed
Labels
severity/major sig/planner SIG: Planner type/bug The issue is confirmed as a bug.

Comments

@wjhuang2016
Copy link
Member

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?
    If possible, provide a recipe for reproducing the error.
drop table if exists t1;
create table t1(a int, b bigint unsigned);
insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997),(3,11120436154190595086);
select (case t1.a when 0 then 0 else t1.b end), cast(t1.b as signed)  from t1;

See also https://bugs.mysql.com/bug.php?id=79657
2. What did you expect to see?

mysql> select (case t1.a when 0 then 0 else t1.b end), cast(t1.b as signed)  from t1;
+-----------------------------------------+----------------------+
| (case t1.a when 0 then 0 else t1.b end) | cast(t1.b as signed) |
+-----------------------------------------+----------------------+
|                     4572794622775114594 |  4572794622775114594 |
|                    18196094287899841997 |  -250649785809709619 |
|                    11120436154190595086 | -7326307919518956530 |
+-----------------------------------------+----------------------+
3 rows in set (0.01 sec)

  1. What did you see instead?
mysql> select (case t1.a when 0 then 0 else t1.b end), cast(t1.b as signed)  from t1;
+-----------------------------------------+----------------------+
| (case t1.a when 0 then 0 else t1.b end) | cast(t1.b as signed) |
+-----------------------------------------+----------------------+
|                     4572794622775114594 |  4572794622775114594 |
|                     -250649785809709619 |  -250649785809709619 |
|                    -7326307919518956530 | -7326307919518956530 |
+-----------------------------------------+----------------------+
3 rows in set (0.00 sec)
  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-260-g47a564b-dirty
Git Commit Hash: 47a564be2fc6045c6423e63b28a421924d5557bf
Git Branch: master
UTC Build Time: 2019-03-22 07:10:11
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

@jackysp
Copy link
Member

jackysp commented Mar 24, 2019

Thanks for your report, @wjhuang2016 !
I've reproduced it.
PTAL @XuHuaiyu

@jackysp jackysp added the type/bug The issue is confirmed as a bug. label Mar 24, 2019
@ghost
Copy link

ghost commented Jul 14, 2020

Confirming this issue still exists in master:

drop table if exists t1;
create table t1(a int, b bigint unsigned);
insert into t1 (a, b) values (1,4572794622775114594), (2,18196094287899841997),(3,11120436154190595086);
select (case t1.a when 0 then 0 else t1.b end), cast(t1.b as signed)  from t1;

..

mysql> select (case t1.a when 0 then 0 else t1.b end), cast(t1.b as signed)  from t1;
+-----------------------------------------+----------------------+
| (case t1.a when 0 then 0 else t1.b end) | cast(t1.b as signed) |
+-----------------------------------------+----------------------+
|                     4572794622775114594 |  4572794622775114594 |
|                     -250649785809709619 |  -250649785809709619 |
|                    -7326307919518956530 | -7326307919518956530 |
+-----------------------------------------+----------------------+
3 rows in set (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

@ti-srebot
Copy link
Contributor

Please edit this comment or add a new comment to complete the following information

Not a bug

  1. Remove the 'type/bug' label
  2. Add notes to indicate why it is not a bug

Duplicate bug

  1. Add the 'type/duplicate' label
  2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
severity/major 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