Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 同样语句加不加and结果不一样
【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
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.
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)
This is the first time I’ve seen this kind of shorthand. Please use standard syntax.
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
.
I’ve never seen the syntax id<=2<=id1 before. Returning 3 and 4 feels strange, like 3<=2<=4.
But this ID is not greater than or equal to 2.
First, calculate id<=2
, and then compare the result with id1
.
What is this result? The comparison result should be true and false, right?
I placed it in comparison, the result is false for the first one and true for the second one.
MySQL 5.7
The test results are consistent with this.
This syntax result is a bit strange, it feels very misleading.
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’.
Uh… Can it be written like this? It’s the first time I’ve seen this kind of syntax.
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)
It feels like the parsing algorithm has changed.
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:
-
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.
-
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.
This is the first time I’ve seen this way of writing, let’s stick to standard syntax.
Obviously, the two methods of writing have different actual calculation methods, so it’s normal for the results to be different.
This explanation is very clear.