Description:
JSON_ARRAY() produces inconsistent JSON representations for expressions that all evaluate to the same SQL value.
In the following test case, the first three expressions all return the SQL value 1. However, when passed to JSON_ARRAY(), the first two are represented as JSON boolean true, while the table-referencing scalar subquery is represented as JSON number 1.
This makes the JSON representation dependent on whether the scalar subquery references a table, even though all three underlying SQL expressions evaluate to the same value.
How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0
(
c0 BLOB NULL
);
INSERT INTO t0 (c0)
VALUES (1);
SELECT
(TRUE),
(SELECT TRUE),
(SELECT TRUE FROM t0),
JSON_ARRAY(TRUE),
JSON_ARRAY((SELECT TRUE)),
JSON_ARRAY((SELECT TRUE FROM t0))
FROM t0;
Actual result:
+--------+----------------+-----------------------+------------------+-------------------------+--------------------------------+
| (TRUE) | (SELECT TRUE) | (SELECT TRUE FROM t0) | JSON_ARRAY(TRUE) | JSON_ARRAY((SELECT TRUE)) | JSON_ARRAY((SELECT TRUE FROM t0)) |
+--------+----------------+-----------------------+------------------+-------------------------+--------------------------------+
| 1 | 1 | 1 | [true] | [true] | [1] |
+--------+----------------+-----------------------+------------------+-------------------------+--------------------------------+
In other words:
(TRUE) -> 1
(SELECT TRUE) -> 1
(SELECT TRUE FROM t0) -> 1
but:
JSON_ARRAY(TRUE) -> [true]
JSON_ARRAY((SELECT TRUE)) -> [true]
JSON_ARRAY((SELECT TRUE FROM t0)) -> [1]
The discrepancy is therefore not caused by the SQL expressions producing different scalar values. All three expressions return 1.
The difference appears specifically when the result of a table-referencing scalar subquery is passed to JSON_ARRAY(). The same TRUE expression without a table reference is represented as JSON boolean true, whereas the table-referencing scalar subquery is represented as JSON number 1.
This is unexpected because the JSON representation of the value changes solely due to whether the scalar subquery references t0.
For comparison, JSON_ARRAY(TRUE) is documented to produce a JSON array containing the JSON boolean true.
Expected result
The JSON representation should be consistent for equivalent TRUE expressions. In particular, the following should not differ solely because the scalar subquery references a table:
JSON_ARRAY(TRUE) -> [true]
JSON_ARRAY((SELECT TRUE)) -> [true]
JSON_ARRAY((SELECT TRUE FROM t0)) -> [true]
Actual result
[true] | [true] | [1]
The third expression is serialized as a JSON number rather than a JSON boolean.
Description: JSON_ARRAY() produces inconsistent JSON representations for expressions that all evaluate to the same SQL value. In the following test case, the first three expressions all return the SQL value 1. However, when passed to JSON_ARRAY(), the first two are represented as JSON boolean true, while the table-referencing scalar subquery is represented as JSON number 1. This makes the JSON representation dependent on whether the scalar subquery references a table, even though all three underlying SQL expressions evaluate to the same value. How to repeat: DROP TABLE IF EXISTS t0; CREATE TABLE t0 ( c0 BLOB NULL ); INSERT INTO t0 (c0) VALUES (1); SELECT (TRUE), (SELECT TRUE), (SELECT TRUE FROM t0), JSON_ARRAY(TRUE), JSON_ARRAY((SELECT TRUE)), JSON_ARRAY((SELECT TRUE FROM t0)) FROM t0; Actual result: +--------+----------------+-----------------------+------------------+-------------------------+--------------------------------+ | (TRUE) | (SELECT TRUE) | (SELECT TRUE FROM t0) | JSON_ARRAY(TRUE) | JSON_ARRAY((SELECT TRUE)) | JSON_ARRAY((SELECT TRUE FROM t0)) | +--------+----------------+-----------------------+------------------+-------------------------+--------------------------------+ | 1 | 1 | 1 | [true] | [true] | [1] | +--------+----------------+-----------------------+------------------+-------------------------+--------------------------------+ In other words: (TRUE) -> 1 (SELECT TRUE) -> 1 (SELECT TRUE FROM t0) -> 1 but: JSON_ARRAY(TRUE) -> [true] JSON_ARRAY((SELECT TRUE)) -> [true] JSON_ARRAY((SELECT TRUE FROM t0)) -> [1] The discrepancy is therefore not caused by the SQL expressions producing different scalar values. All three expressions return 1. The difference appears specifically when the result of a table-referencing scalar subquery is passed to JSON_ARRAY(). The same TRUE expression without a table reference is represented as JSON boolean true, whereas the table-referencing scalar subquery is represented as JSON number 1. This is unexpected because the JSON representation of the value changes solely due to whether the scalar subquery references t0. For comparison, JSON_ARRAY(TRUE) is documented to produce a JSON array containing the JSON boolean true. Expected result The JSON representation should be consistent for equivalent TRUE expressions. In particular, the following should not differ solely because the scalar subquery references a table: JSON_ARRAY(TRUE) -> [true] JSON_ARRAY((SELECT TRUE)) -> [true] JSON_ARRAY((SELECT TRUE FROM t0)) -> [true] Actual result [true] | [true] | [1] The third expression is serialized as a JSON number rather than a JSON boolean.