MySQL Bugs: #120986: Inconsistent comparison result between JSON scalar and dynamic comparison expression vs CTAS materialized boolean/intege

Bug #120986	Inconsistent comparison result between JSON scalar and dynamic comparison expression vs CTAS materialized boolean/intege
Submitted:	23 Jul 12:12	Modified:	21 Aug 7:53
Reporter:	Xiaoyuan Xie	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.46, 8.4.10, 9.7.1	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
An execution logic inconsistency occurs when comparing a JSON scalar column with a dynamic predicate/comparison expression (c0 <= c1) directly in the WHERE clause, compared to evaluating the same comparison against a materialized column derived via CREATE TABLE ... AS SELECT (CTAS).

Specifically:

In base table t0, the comparison expression (c0 <= c1) (DECIMAL <= DATETIME) dynamically yields 1 (TRUE). The predicate c2 <= (c0 <= c1) comparing JSON "3" against dynamic (c0 <= c1) evaluates to TRUE and returns 1 row.

In table t1 (created via CREATE TABLE t1 AS SELECT (c0 <= c1) AS c0, c2 FROM t0), c0 is materialized as a physical integer column storing 1. However, the predicate c2 <= c0 comparing JSON "3" against materialized column c0 (value 1) evaluates to FALSE, returning Empty set.

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

-- Step 1: Create base table t0 with DECIMAL, DATETIME, and JSON columns
CREATE TABLE t0 (c0 DECIMAL, c1 DATETIME, c2 JSON);

-- Step 2: Insert sample row
INSERT INTO t0 (c0, c1, c2) VALUES (-5545872277.154684, '1961-06-08 17:15:31', "3");

-- Step 3: Materialize comparison expression into table t1 via CTAS
CREATE TABLE t1 AS SELECT (c0 <= c1) AS c0, c2 AS c2 FROM t0;

-- Query 1: Dynamic evaluation on base table t0
SELECT c2 FROM t0 WHERE (((c2 <= (c0 <= c1))));
-- Returns: 1 row ("3")

-- Query 2: Evaluation on materialized table t1
SELECT c2 FROM t1 WHERE (((c2 <= (c0))));
-- Returns: Empty set (0 rows)

Hi Xiaoyuan Xie,

Thank you for the test case. Verified as described.

For the record: I do not think that, unless proper BOOLEAN column type is implemented, this is a bug at all. There's an extra conversion to integer in the CREATE..SELECT case and that changes the semantics.

Thank you for the report.

Georgi's comment is correct. The observed difference is expected because MySQL does not have a distinct BOOLEAN column type. In Query 1, (c0 <= c1) is still a Boolean expression. However, CREATE TABLE ... AS SELECT creates t1.c0 as an integer column.
It preserves the value 1, but not the fact that the value originated from a Boolean expression.

Consequently, the two queries perform different JSON comparisons:

- Query 1 compares the JSON integer 3 with the Boolean value TRUE. Since BOOLEAN has higher JSON type precedence than INTEGER, the predicate is true.
- Query 2 compares the JSON integer 3 with the integer value 1. These are compared numerically, so the predicate is false.

This follows from the documented JSON comparison rules (https://dev.mysql.com/doc/refman/9.7/en/json.html#json-comparison).

If MySQL had a proper Boolean column type, and the CTAS statement created t1.c0 with that type, the expectation that both queries return the same result would be reasonable. That is not how Boolean expressions are currently materialized, however.

The Boolean nature of each value can instead be preserved by storing it as JSON:

CREATE TABLE t1 AS
  SELECT CAST(c0 <= c1 AS JSON) AS c0, c2
  FROM t0;

With this definition, t1.c0 contains a JSON Boolean, and Query 2 returns the same row as Query 1.

Closing as not a bug.