The same statement yields different results with and without "and"

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 同样语句加不加and结果不一样

| username: Jjjjayson_zeng

【TiDB Usage Environment】Production Environment / Testing / PoC
【TiDB Version】
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issue: Issue Symptoms and Impact】
【Resource Configuration】
【Attachments: Screenshots / Logs / Monitoring】


The above is 0, the below is 1

| username: Jellybean | Original post link

Please indicate which version of the cluster it is.
Also, paste the complete text content so that we can help you verify and test it.

| username: 柴米油盐 | Original post link

These two conditions do not seem to be equivalent.

Example:

mysql> select * from t1;
+------+------+
| id   | id1  |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from t1 where id<=2 and id1>=2;
+------+------+
| id   | id1  |
+------+------+
|    1 |    2 |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t1 where id<=2<=id1;
+------+------+
| id   | id1  |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)
| username: tidb狂热爱好者 | Original post link

This is the first time I’ve seen this kind of shorthand. Please use standard syntax.

| username: tidb菜鸟一只 | Original post link

The calculation order of id<=2<=id1 is to first evaluate id<=2, and then compare the result with id1. This will cause all rows that satisfy id<=2 to be returned, regardless of the value of id1.

| username: zhanggame1 | Original post link

I’ve never seen the syntax id<=2<=id1 before. Returning 3 and 4 feels strange, like 3<=2<=4.

| username: Jjjjayson_zeng | Original post link

But this ID is not greater than or equal to 2.

| username: zhanggame1 | Original post link

First, calculate id<=2, and then compare the result with id1.

What is this result? The comparison result should be true and false, right?

| username: Jjjjayson_zeng | Original post link

I placed it in comparison, the result is false for the first one and true for the second one.

| username: 有猫万事足 | Original post link

MySQL 5.7

The test results are consistent with this.

| username: 春风十里 | Original post link

This syntax result is a bit strange, it feels very misleading.

| username: 随缘天空 | Original post link

Your way of writing is really hard to describe. Wouldn’t it be better to use a normal writing style? Try to put the column fields at the front as much as possible, for example, C.TIMESHEETTIMEFROM <= ‘19:00’ and C.TIMESHEETTIMETO >= ‘19:00’.

| username: 小龙虾爱大龙虾 | Original post link

Send the execution plan.

| username: Kongdom | Original post link

Uh… Can it be written like this? It’s the first time I’ve seen this kind of syntax.

| username: 江湖故人 | Original post link

The SQL select * from t1 where id1<=1<=id2 first evaluates id1<=1 to get a logical value of 0 or 1, and then compares it with id2:

le(le(test.t1.id1, 1), test.t1.id2)

It is not recommended to use such non-standard SQL.

The testing process is as follows:

my:root@192.168.3.22:4000=> create table t1(id1 int,id2 int);
CREATE TABLE
my:root@192.168.3.22:4000=> insert into t1 values(1,1);
INSERT 1
my:root@192.168.3.22:4000=> insert into t1 values(2,1);
INSERT 1
my:root@192.168.3.22:4000=> select * from t1 where id1<=1<=id2;
 id1 | id2 
-----+-----
   1 |   1 
   2 |   1 
(2 rows)

my:root@192.168.3.22:4000=> select * from t1 where id1<=1 and 1<=id2;
 id1 | id2 
-----+-----
   1 |   1 
(1 row)

my:root@192.168.3.22:4000=> explain 
my:root@192.168.3.22:4000-> select * from t1 where id1<=1<=id2;
          id           | estRows  |   task    | access object |            operator info            
-----------------------+----------+-----------+---------------+-------------------------------------
 TableReader_7         | 8000.00  | root      |               | data:Selection_6 
 └─Selection_6       | 8000.00  | cop[tikv] |               | le(le(test.t1.id1, 1), test.t1.id2) 
   └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo 
(3 rows)

my:root@192.168.3.22:4000=> explain 
my:root@192.168.3.22:4000-> select * from t1 where id1<=1 and 1<=id2;
          id           | estRows |   task    | access object |             operator info              
-----------------------+---------+-----------+---------------+----------------------------------------
 TableReader_7         | 0.22    | root      |               | data:Selection_6 
 └─Selection_6       | 0.22    | cop[tikv] |               | le(1, test.t1.id2), le(test.t1.id1, 1) 
   └─TableFullScan_5 | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo 
(3 rows)
| username: dba远航 | Original post link

It feels like the parsing algorithm has changed.

| username: Jellybean | Original post link

Not sure what scenario would use this kind of writing. For where id<=2<=id1, the internal execution process is actually where (id<=2)<=id1. That is:

  1. First, evaluate id<=2 to get a result of 0 or 1.

    • 0 means false, which will be the result for values in the id column greater than 2. For example, if the id column is 3, then 3<=2 results in 0.
    • 1 means true, which will be the result for values in the id column less than or equal to 2. For example, if the id column is 1 or 2, then the result is 1.
  2. Then use the result value 0 or 1 from the previous step to compare with the id1 column to get the final output value.

Conclusion: Unless there is a special business need and you are very sure that your business scenario requires this unconventional SQL writing and you are not worried about causing ambiguity, it is not recommended to use this writing.

| username: andone | Original post link

This is the first time I’ve seen this way of writing, let’s stick to standard syntax.

| username: 小龙虾爱大龙虾 | Original post link

Obviously, the two methods of writing have different actual calculation methods, so it’s normal for the results to be different.

| username: zhanggame1 | Original post link

This explanation is very clear.