WingsGo opened a new issue #3528:
URL: https://github.com/apache/incubator-doris/issues/3528
# Background
When we compare string and number in Doris, it may cause some problem:
1. Cause full table scan, in extreme cases, it will make doris cannot work.
For example: if our sql like `SELECT * FROM tbl WHERE
olap_date='20200501'`, the predicate will fails and cause full table scan.If
then table has a hundred billion pieces of data, the disk IO will be full and
other query will be influenced.
2. Query will get different result.
For example, if we have two sql like following:
```
select count(*) from tbl where date=20200418 and
appId=2882303761517473127;
select count(*) from tbl where date=20200418 and
appId=‘2882303761517473127’;
```
their result is different, because in Doris, appId and stringliteral
'2882303761517473127' will be cast to Double to compare, which will cause if
the appId's value is 2882303761517473267 it will take in count. It is because
new Double(2882303761517473127L) is equal with new
Double("2882303761517473267"); Let's do an experiment to verify our inference
in following.
```
MySQL [(none)]> select 2882303761517473127 = "2882303761517473127";
+-----------------------------------------------+
| 2.8823037615174733E18 = 2.8823037615174733E18 |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+
1 row in set (0.07 sec)
MySQL [(none)]> select 2882303761517473127 = "2882303761517473267";
+-----------------------------------------------+
| 2.8823037615174733E18 = 2.8823037615174733E18 |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+
1 row in set (0.07 sec)
```
# Suggest solution
So, I think we should optimize the case to avoid the problems, One solution
is convert both type to numerical when one side of binary predicate is SlotRef
and the other is a StringLiteral.If there is another solution, we can disscuss
below, thanks~
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
