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)
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)