Description:
mysql>
mysql> SELECT id, GREATEST(v, 1) FROM t; -- 1 → 'abc' (string order: 'abc' > '1')
+----+----------------+
| id | GREATEST(v, 1) |
+----+----------------+
| 1 | abc |
| 2 | 1 |
| 3 | 5 |
+----+----------------+
3 rows in set (0.00 sec)
mysql> SELECT id, CASE WHEN v > 1 THEN v ELSE 1 END FROM t; -- 1 → 1 (numeric order: 'abc'→0 < 1)
+----+-----------------------------------+
| id | CASE WHEN v > 1 THEN v ELSE 1 END |
+----+-----------------------------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 5 |
+----+-----------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> SELECT id, v > 1 FROM t; -- 1 → 0 (standalone comparator: numeric)
+----+-------+
| id | v > 1 |
+----+-------+
| 1 | 0 |
| 2 | 0 |
| 3 | 1 |
+----+-------+
3 rows in set, 1 warning (0.00 sec)
GREATEST(x, y) = x is mathematically equivalent to x >= y — both queries must return the same rows (2); they return 3 vs 2 (the 'abc' row is the smoking gun). (Verification note: the original report compared against v > 1 (1 row); the exact equivalent is v >= 1 (2 rows) — the split evidence is unchanged.)
How to repeat:
CREATE TABLE t (id INT PRIMARY KEY, v VARCHAR(20));
INSERT INTO t VALUES (1,'abc'),(2,'1'),(3,'5');
SELECT id, GREATEST(v, 1) FROM t; -- 1 → 'abc' (string order: 'abc' > '1')
SELECT id, CASE WHEN v > 1 THEN v ELSE 1 END FROM t; -- 1 → 1 (numeric order: 'abc'→0 < 1)
SELECT id, v > 1 FROM t; -- 1 → 0 (standalone comparator: numeric)
