Description:
The following two queries may produce inconsistent result.
The first query:
mysql> SELECT (NOT ((COALESCE('x')) != (t1.c1))) from t1;
+------------------------------------+
| (NOT ((COALESCE('x')) != (t1.c1))) |
+------------------------------------+
| 1 |
+------------------------------------+
The second query:
mysql> SELECT t1.c1 from t1 Where (NOT ((COALESCE('x')) != (t1.c1)));
Empty set, 1 warning (0.17 sec)
Because the result of the expresssion, '(NOT ((COALESCE('x')) != (t1.c1)))',is true(please check the first query),the second query will produce one row.But the result of the second query is empty.
How to repeat:
CREATE TABLE t1 (
c0 int,
c1 decimal(10,0),
UNIQUE KEY c1 (c1)
);
insert into t1 select 0,0;
SELECT (NOT ((COALESCE('x')) != (t1.c1))) from t1;
SELECT t1.c1 from t1 Where (NOT ((COALESCE('x')) != (t1.c1)));
Description: The following two queries may produce inconsistent result. The first query: mysql> SELECT (NOT ((COALESCE('x')) != (t1.c1))) from t1; +------------------------------------+ | (NOT ((COALESCE('x')) != (t1.c1))) | +------------------------------------+ | 1 | +------------------------------------+ The second query: mysql> SELECT t1.c1 from t1 Where (NOT ((COALESCE('x')) != (t1.c1))); Empty set, 1 warning (0.17 sec) Because the result of the expresssion, '(NOT ((COALESCE('x')) != (t1.c1)))',is true(please check the first query),the second query will produce one row.But the result of the second query is empty. How to repeat: CREATE TABLE t1 ( c0 int, c1 decimal(10,0), UNIQUE KEY c1 (c1) ); insert into t1 select 0,0; SELECT (NOT ((COALESCE('x')) != (t1.c1))) from t1; SELECT t1.c1 from t1 Where (NOT ((COALESCE('x')) != (t1.c1)));