| Bug #117199 | Inconsistent Results with JSON_UNQUOTE Between Table and View | ||
|---|---|---|---|
| Submitted: | 13 Jan 14:07 | Modified: | 13 Jan 14:34 |
| Reporter: | Wenqian Deng | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Jan 14:34]
MySQL Verification Team
Hi Mr. Deng, Thank you for your bug report. We repeated the bug on all versions from 8.0 to 9.2: +-------------------------------------------------+------+--------------------------------------------+ | c2 | c1 | c0 | +-------------------------------------------------+------+--------------------------------------------+ | EFwSqLFXCceSBMB | -18 | BOlrcg7j5o7 | | QzntjExkXUWEIiP8wpToXdjUax6JEU7yH6JtbUXmKKT916z | 63 | Jw7rnQgst5u2KGDhae1Lq5KZfxcKI1KbptD9JBsNC8 | +-------------------------------------------------+------+--------------------------------------------+ +-------------------------------------------------+------+--------------------------------------------+ | c2 | c1 | c0 | +-------------------------------------------------+------+--------------------------------------------+ | EFwSqLFXCceSBMB | -18 | BOlrcg7j5o7 | | QzntjExkXUWEIiP8wpToXdjUax6JEU7yH6JtbUXmKKT916z | 63 | Jw7rnQgst5u2KGDhae1Lq5KZfxcKI1KbptD9JBsNC8 | | Gxuqi6YhJxXIBZg | -8 | cRqKUpaY72qPB0rQFsdIPWWi5vlMzI | +-------------------------------------------------+------+--------------------------------------------++-------------------------------------------------+------+--------------------------------------------+ | c2 | c1 | c0 | +-------------------------------------------------+------+--------------------------------------------+ | EFwSqLFXCceSBMB | -18 | BOlrcg7j5o7 | | QzntjExkXUWEIiP8wpToXdjUax6JEU7yH6JtbUXmKKT916z | 63 | Jw7rnQgst5u2KGDhae1Lq5KZfxcKI1KbptD9JBsNC8 | +-------------------------------------------------+------+--------------------------------------------+ +-------------------------------------------------+------+--------------------------------------------+ | c2 | c1 | c0 | +-------------------------------------------------+------+--------------------------------------------+ | EFwSqLFXCceSBMB | -18 | BOlrcg7j5o7 | | QzntjExkXUWEIiP8wpToXdjUax6JEU7yH6JtbUXmKKT916z | 63 | Jw7rnQgst5u2KGDhae1Lq5KZfxcKI1KbptD9JBsNC8 | | Gxuqi6YhJxXIBZg | -8 | cRqKUpaY72qPB0rQFsdIPWWi5vlMzI | +-------------------------------------------------+------+--------------------------------------------+ Verified as reported.
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.