Description:
MySQL produces inconsistent results between an `IN` predicate and the corresponding pairwise equality comparisons.
In the reproducer below, both individual equality comparisons return `0`:
```sql
'' = TIMEDIFF((SELECT '1' WHERE (IFNULL(t2.c0, 251) >= 0)), 1)
-- 0
'' = 'None'
-- 0
```
However, combining exactly the same two comparison operands in an `IN` predicate returns `1`:
```sql
'' IN (
TIMEDIFF((SELECT '1' WHERE (IFNULL(t2.c0, 251) >= 0)), 1),
'None'
)
-- 1
```
Thus, the observed result is:
```text
re0 = 0
re1 = 0
re2 = 1
```
The unexpected behavior is that the `IN` predicate evaluates to `TRUE` even though neither of its corresponding equality comparisons evaluates to `TRUE`.
This is particularly surprising because the `IN` predicate contains exactly the same operands used by the two equality comparisons.
How to repeat:
```sql
DROP TABLE IF EXISTS t2;
CREATE TABLE t2
(
c0 MEDIUMTEXT
);
INSERT INTO t2 (c0)
VALUES ('1');
SELECT
'' = TIMEDIFF(
(SELECT '1'
WHERE (IFNULL(t2.c0, 251) >= 0)),
1
) AS re0,
'' = 'None' AS re1,
(
'' IN (
TIMEDIFF(
(SELECT '1'
WHERE (IFNULL(t2.c0, 251) >= 0)),
1
),
'None'
)
) AS re2
FROM t2;
```
**Actual result**
```text
+-----+-----+-----+
| re0 | re1 | re2 |
+-----+-----+-----+
| 0 | 0 | 1 |
+-----+-----+-----+
```
The intermediate `TIMEDIFF()` expression evaluates to:
```text
00:00:00
```
Therefore, the relevant comparisons are effectively:
```sql
'' = '00:00:00' -- 0
'' = 'None' -- 0
```
while:
```sql
'' IN ('00:00:00', 'None') -- 1
```
is observed through the original expression.
**Expected result**
The `IN` predicate should not evaluate to `TRUE` when none of its corresponding equality comparisons evaluates to `TRUE`.
Therefore, the expected result is:
```text
+-----+-----+-----+
| re0 | re1 | re2 |
+-----+-----+-----+
| 0 | 0 | 0 |
+-----+-----+-----+
```
**Additional observation**
The issue appears to involve the interaction between `IN` comparison semantics, implicit type conversion, and the `TIME` result produced by `TIMEDIFF()`.
The problem is not simply that `TIMEDIFF()` produces an unexpected value: the function independently produces `00:00:00`. The inconsistency occurs when this value is used as an element of the `IN` predicate.
The following relationship is expected to hold for this case:
```sql
x IN (a, b)
```
should not produce `TRUE` when both:
```sql
x = a
x = b
```
produce `FALSE`.
This makes the discrepancy reproducible as a comparison-semantics inconsistency rather than merely an unexpected formatting or display issue.
Description: MySQL produces inconsistent results between an `IN` predicate and the corresponding pairwise equality comparisons. In the reproducer below, both individual equality comparisons return `0`: ```sql '' = TIMEDIFF((SELECT '1' WHERE (IFNULL(t2.c0, 251) >= 0)), 1) -- 0 '' = 'None' -- 0 ``` However, combining exactly the same two comparison operands in an `IN` predicate returns `1`: ```sql '' IN ( TIMEDIFF((SELECT '1' WHERE (IFNULL(t2.c0, 251) >= 0)), 1), 'None' ) -- 1 ``` Thus, the observed result is: ```text re0 = 0 re1 = 0 re2 = 1 ``` The unexpected behavior is that the `IN` predicate evaluates to `TRUE` even though neither of its corresponding equality comparisons evaluates to `TRUE`. This is particularly surprising because the `IN` predicate contains exactly the same operands used by the two equality comparisons. How to repeat: ```sql DROP TABLE IF EXISTS t2; CREATE TABLE t2 ( c0 MEDIUMTEXT ); INSERT INTO t2 (c0) VALUES ('1'); SELECT '' = TIMEDIFF( (SELECT '1' WHERE (IFNULL(t2.c0, 251) >= 0)), 1 ) AS re0, '' = 'None' AS re1, ( '' IN ( TIMEDIFF( (SELECT '1' WHERE (IFNULL(t2.c0, 251) >= 0)), 1 ), 'None' ) ) AS re2 FROM t2; ``` **Actual result** ```text +-----+-----+-----+ | re0 | re1 | re2 | +-----+-----+-----+ | 0 | 0 | 1 | +-----+-----+-----+ ``` The intermediate `TIMEDIFF()` expression evaluates to: ```text 00:00:00 ``` Therefore, the relevant comparisons are effectively: ```sql '' = '00:00:00' -- 0 '' = 'None' -- 0 ``` while: ```sql '' IN ('00:00:00', 'None') -- 1 ``` is observed through the original expression. **Expected result** The `IN` predicate should not evaluate to `TRUE` when none of its corresponding equality comparisons evaluates to `TRUE`. Therefore, the expected result is: ```text +-----+-----+-----+ | re0 | re1 | re2 | +-----+-----+-----+ | 0 | 0 | 0 | +-----+-----+-----+ ``` **Additional observation** The issue appears to involve the interaction between `IN` comparison semantics, implicit type conversion, and the `TIME` result produced by `TIMEDIFF()`. The problem is not simply that `TIMEDIFF()` produces an unexpected value: the function independently produces `00:00:00`. The inconsistency occurs when this value is used as an element of the `IN` predicate. The following relationship is expected to hold for this case: ```sql x IN (a, b) ``` should not produce `TRUE` when both: ```sql x = a x = b ``` produce `FALSE`. This makes the discrepancy reproducible as a comparison-semantics inconsistency rather than merely an unexpected formatting or display issue.