Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Inconsistent Results with JSON_UNQUOTE Between Table and View

Description

Description:


When querying data involving JSON_UNQUOTE in a SELECT statement, there is inconsistent behavior between queries executed on a table created via CREATE TABLE AS SELECT and a view created using the same query logic. Specifically, the number of rows returned differs when applying the same WHERE clause on the table and view.

 

How to repeat:


1.Create t0 and insert data:

CREATE TABLE t0 (c0 TINYTEXT, c1 BOOLEAN, c2 TINYTEXT); INSERT INTO t0 (c0, c1, c2) VALUES ('BOlrcg7j5o7', -18, 'EFwSqLFXCceSBMB'); INSERT INTO t0 (c0, c1, c2) VALUES ('Jw7rnQgst5u2KGDhae1Lq5KZfxcKI1KbptD9JBsNC8', 63, 'QzntjExkXUWEIiP8wpToXdjUax6JEU7yH6JtbUXmKKT916z'); INSERT INTO t0 (c0, c1, c2) VALUES ('cRqKUpaY72qPB0rQFsdIPWWi5vlMzI', -8, 'Gxuqi6YhJxXIBZg');

2.Create t1 as a table and query it:

CREATE TABLE t1 AS (SELECT (JSON_UNQUOTE(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 ); mysql> SELECT c2, c1, (c0) FROM t1 WHERE ((c1 = c1) AND ((c0) <= c2)); +-------------------------------------------------+------+--------------------------------------------+ | c2 | c1 | c0 | +-------------------------------------------------+------+--------------------------------------------+ | EFwSqLFXCceSBMB | -18 | BOlrcg7j5o7 | | QzntjExkXUWEIiP8wpToXdjUax6JEU7yH6JtbUXmKKT916z | 63 | Jw7rnQgst5u2KGDhae1Lq5KZfxcKI1KbptD9JBsNC8 | +-------------------------------------------------+------+--------------------------------------------+ 2 rows in set (0.00 sec)

3.Create t1 as a view instead and query it:

CREATE VIEW t1 AS (SELECT (JSON_UNQUOTE(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 ); mysql> SELECT c2, c1, (c0) FROM t1 WHERE ((c1 = c1) AND ((c0) <= c2)); +-------------------------------------------------+------+--------------------------------------------+ | c2 | c1 | c0 | +-------------------------------------------------+------+--------------------------------------------+ | EFwSqLFXCceSBMB | -18 | BOlrcg7j5o7 | | QzntjExkXUWEIiP8wpToXdjUax6JEU7yH6JtbUXmKKT916z | 63 | Jw7rnQgst5u2KGDhae1Lq5KZfxcKI1KbptD9JBsNC8 | | Gxuqi6YhJxXIBZg | -8 | cRqKUpaY72qPB0rQFsdIPWWi5vlMzI | +-------------------------------------------------+------+--------------------------------------------+ 3 rows in set (0.00 sec)

 

Expected Result:

 

The results of the queries on t1 (table) and t1 (view) should be identical, as they are based on the same data and query logic.

 

Actual Result:

 

  • The query on t1 (table) returns 2 rows.

  • The query on t1 (view) returns 3 rows.

Environment

None

Activity

Show:

Aaditya Dubey January 31, 2025 at 1:01 PM

Hi

Thank you for the report.
It is verified as described.

Smart Checklist

Created January 14, 2025 at 2:00 AM
Updated January 31, 2025 at 1:02 PM
Configure

Flag notifications