Description:
When performing a comparison using >= ALL, if the left-hand side is the boolean literal TRUE, the server returns 1 (true) even when the underlying data in the subquery contains values that should make the comparison false (e.g., string '4').
However, if TRUE is replaced by an expression that evaluates to 1 (like EXISTS(...) or integer 1), the query correctly returns 0 (false).
This issue is strictly dependent on the NOT NULL constraint.
How to repeat:
DROP TABLE IF EXISTS t1, t2;
-- Fixed table definition from original snippet
CREATE TABLE t1(c1 CHAR(10) NOT NULL);
CREATE TABLE t2(c1 INT);
INSERT INTO t1(c1) VALUES('i');
INSERT INTO t1(c1) VALUES('4');
INSERT INTO t1(c1) VALUES('E');
INSERT INTO t2(c1) VALUES(1);
SELECT 1 >='4';
-- Returns 0
SELECT TRUE >= ALL(SELECT c1 from t1);
-- Returns 1 (Incorrect)
SELECT (EXISTS (SELECT c1 FROM t2)) >= ALL(SELECT c1 from t1);
-- Returns 0 (Correct)
Description: When performing a comparison using >= ALL, if the left-hand side is the boolean literal TRUE, the server returns 1 (true) even when the underlying data in the subquery contains values that should make the comparison false (e.g., string '4'). However, if TRUE is replaced by an expression that evaluates to 1 (like EXISTS(...) or integer 1), the query correctly returns 0 (false). This issue is strictly dependent on the NOT NULL constraint. How to repeat: DROP TABLE IF EXISTS t1, t2; -- Fixed table definition from original snippet CREATE TABLE t1(c1 CHAR(10) NOT NULL); CREATE TABLE t2(c1 INT); INSERT INTO t1(c1) VALUES('i'); INSERT INTO t1(c1) VALUES('4'); INSERT INTO t1(c1) VALUES('E'); INSERT INTO t2(c1) VALUES(1); SELECT 1 >='4'; -- Returns 0 SELECT TRUE >= ALL(SELECT c1 from t1); -- Returns 1 (Incorrect) SELECT (EXISTS (SELECT c1 FROM t2)) >= ALL(SELECT c1 from t1); -- Returns 0 (Correct)