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.

Sign up for GitHub

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

Jump to bottom

Expression: errors from comparing different data types #19534

Closed
fzhedu opened this issue Aug 27, 2020 · 2 comments · Fixed by #21150
Closed

Expression: errors from comparing different data types #19534

fzhedu opened this issue Aug 27, 2020 · 2 comments · Fixed by #21150
Assignees
@iosmanthus
Labels
challenge-program severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.
Milestone
v4.0.10

Comments

@fzhedu
Copy link
Contributor

fzhedu commented Aug 27, 2020 •
edited by ti-srebot
Loading

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!
Test case sources are available in https://github.com/tikv/copr-test/tree/master/push-down-test/prepare

Root Cause: maybe due to infer errors like #17994
#18921

1. Minimal reproduce step (Required)

create database expr;
use expr;
source loaded file from the link above

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

mysql> select   STRCMP( `col_time_key`, `col_smallint_unsigned_key` )  as f0, `col_time_key` = `col_smallint_unsigned_key` as f1  FROM `table1000_int_autoinc` group by f0,f1;
+------+------+
| f0   | f1   |
+------+------+
|    0 |    1 |
|    1 |    0 |
|   -1 |    0 |
| NULL | NULL |
+------+------+
4 rows in set (0.00 sec)

 SELECT   COALESCE( ( LEAST( `col_double_unsigned_key`, 4556,  '2000-01-09'  ) ), '1990-06-16 17:22:56.005534' ) as f0 FROM `table1000_int_autoinc` group by f0;
+----------------------------+
| f0                         |
+----------------------------+
| 1990-06-16 17:22:56.005534 |
| 0                          |
| 1                          |
| 2                          |
| 2000-01-09                 |
| 11                         |
| 189                        |
| 1111                       |
| 1579                       |
| 1752                       |
| 1954                       |
| 10537                      |
| 11433                      |
| 11838                      |
| 11923                      |
| 12644                      |
| 13142                      |
| 13192                      |
| 13213                      |
| 13230                      |
| 13336                      |
| 13399                      |
| 14041                      |
| 14286                      |
| 14296                      |
| 14483                      |
| 14787                      |
| 14980                      |
| 15286                      |
| 15360                      |
| 16467                      |
| 16590                      |
| 16599                      |
| 16625                      |
| 17299                      |
| 17896                      |
| 18313                      |
| 18628                      |
| 18950                      |
| 1003618304                 |
| 1028653056                 |
| 1031536640                 |
| 1049690112                 |
| 1050476544                 |
| 1053753344                 |
| 1172373504                 |
| 1180106752                 |
| 1184628736                 |
| 1188495360                 |
| 1246429184                 |
| 1266352128                 |
| 1356791808                 |
| 1441988608                 |
| 1476460544                 |
| 1496121344                 |
| 1513619456                 |
| 1567555584                 |
| 1600323584                 |
| 1638334464                 |
| 1647312896                 |
| 1716912128                 |
| 1761345536                 |
| 1805254656                 |
| 1895890944                 |
| 1907818496                 |
| 1946288128                 |
| 1964244992                 |
| 1969422336                 |
| 1978990592                 |
+----------------------------+
69 rows in set (0.00 sec)


SELECT    LEAST( `col_double_unsigned_key`, 4556 , '1990-06-16 17:22:56.005534' ) as f0 FROM `table1000_int_autoinc` group by f0 order by f0;
+----------------------------+
| f0                         |
+----------------------------+
| NULL                       |
| 0                          |
| 1                          |
| 1003618304                 |
| 1028653056                 |
| 1031536640                 |
| 1049690112                 |
| 1050476544                 |
| 10537                      |
| 1053753344                 |
| 11                         |
| 1111                       |
| 11433                      |
| 1172373504                 |
| 1180106752                 |
| 11838                      |
| 1184628736                 |
| 1188495360                 |
| 11923                      |
| 1246429184                 |
| 12644                      |
| 1266352128                 |
| 13142                      |
| 13192                      |
| 13213                      |
| 13230                      |
| 13336                      |
| 13399                      |
| 1356791808                 |
| 14041                      |
| 14286                      |
| 14296                      |
| 1441988608                 |
| 14483                      |
| 1476460544                 |
| 14787                      |
| 1496121344                 |
| 14980                      |
| 1513619456                 |
| 15286                      |
| 15360                      |
| 1567555584                 |
| 1579                       |
| 1600323584                 |
| 1638334464                 |
| 16467                      |
| 1647312896                 |
| 16590                      |
| 16599                      |
| 16625                      |
| 1716912128                 |
| 17299                      |
| 1752                       |
| 1761345536                 |
| 17896                      |
| 1805254656                 |
| 18313                      |
| 18628                      |
| 189                        |
| 18950                      |
| 1895890944                 |
| 1907818496                 |
| 1946288128                 |
| 1954                       |
| 1964244992                 |
| 1969422336                 |
| 1978990592                 |
| 1990-06-16 17:22:56.005534 |
+----------------------------+
68 rows in set (0.01 sec)

3. What did you see instead (Required)

mysql> select   STRCMP( `col_time_key`, `col_smallint_unsigned_key` )  as f0, `col_time_key` = `col_smallint_unsigned_key` as f1  FROM `table1000_int_autoinc` group by f0,f1;
+----+----+
| f0 | f1 |
+----+----+
| NULL | NULL |
|  1 |  1 |
| -1 |  0 |
|  1 |  0 |
+----+----+
4 rows in set (0.00 sec)

mysql> SELECT   COALESCE( ( LEAST( `col_double_unsigned_key`, 4556,  '2000-01-09'  ) ), '1990-06-16 17:22:56.005534' ) as f0 FROM `table1000_int_autoinc` group by f0;
+--------------------------------------------------------+
| f0                                                     |
+--------------------------------------------------------+
| 0x38                                                   |
| 0x31313131                                             |
| 0x313939302D30362D31362031373A32323A35362E303035353334 |
| 0x35                                                   |
| 0x353130                                               |
| 0x36                                                   |
| 0x3131                                                 |
| 0x353039                                               |
| 0x363835                                               |
| 0x31353739                                             |
| 0x32                                                   |
| 0x34                                                   |
| 0x39                                                   |
| 0x32303030                                             |
| 0x313839                                               |
| 0x31373532                                             |
| 0x31                                                   |
| 0x33                                                   |
| 0x37                                                   |
| 0x31393534                                             |
| 0x30                                                   |
| 0x3938                                                 |
+--------------------------------------------------------+
22 rows in set, 2 warnings (0.00 sec)


mysql> SELECT    LEAST( `col_double_unsigned_key`, 4556 , '1990-06-16 17:22:56.005534' ) as f0 FROM `table1000_int_autoinc` group by f0 order by f0;
+------+
| f0   |
+------+
| NULL |
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   11 |
|   98 |
|  189 |
|  509 |
|  510 |
|  685 |
| 1111 |
| 1579 |
| 1752 |
| 1954 |
| 1990 |
+------+
22 rows in set, 2 warnings (0.00 sec)

4. What is your TiDB version? (Required)

master

SIG slack channel

#sig-exec

Score

  • 300

Mentor
@fzhedu fzhedu added the type/bug The issue is confirmed as a bug. label Aug 27, 2020
This was referenced Aug 27, 2020
Closed
Closed
@time-and-fate
Copy link
Member

/label sig/execution

@ti-srebot ti-srebot added the sig/execution SIG execution label Aug 27, 2020
@ichn-hu ichn-hu mentioned this issue Nov 3, 2020
Closed
@jebter jebter assigned SunRunAway and unassigned SunRunAway Nov 4, 2020
@scsldb scsldb added this to the v4.0.9 milestone Nov 4, 2020
@lzmhhh123 lzmhhh123 linked a pull request Nov 24, 2020 that will close this issue
expression: fix type infer for tidb's builtin compare(least and greatest) #21150
Merged
@SunRunAway SunRunAway modified the milestones: v4.0.9, v4.0.10 Nov 25, 2020
@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
Assignees

@iosmanthus iosmanthus

Labels
challenge-program severity/major sig/execution SIG execution type/bug The issue is confirmed as a bug.
Projects
None yet
Milestone
v4.0.10
Development

Successfully merging a pull request may close this issue.

expression: fix type infer for tidb's builtin compare(least and greatest) iosmanthus/tidb
7 participants
@SunRunAway @jebter @scsldb @fzhedu @iosmanthus @time-and-fate @ti-srebot