Bug #102824 JSON_TABLE's check for duplicate column names is case-sensitive
Submitted: 5 Mar 2021 10:33 Modified: 13 Apr 2021 18:53
Reporter: Knut Anders Hatlen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[5 Mar 2021 10:33] Knut Anders Hatlen
Description:
JSON_TABLE checks if there are duplicate column names. This check is case-sensitive, but column names 

How to repeat:
The duplicate column name check for ordinary derived tables is case-insensitive:

mysql> select * from (select 1 as x, 2 as X) t;
ERROR 1060 (42S21): Duplicate column name 'X'

But the similar check for the JSON_TABLE table function is case-sensitive:

mysql> select * from json_table('{}', '$' columns (x for ordinality, x for ordinality)) jt;
ERROR 1060 (42S21): Duplicate column name 'x'

mysql> select * from json_table('{}', '$' columns (x for ordinality, X for ordinality)) jt;
+------+------+
| x    | X    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0,00 sec)
[27 Apr 2021 0:30] Jon Stephens
Documented fix as follows in the MySQL 8.0.27 changelog:

    JSON_TABLE() allowed duplicate column names if the names
    differed in case only, although column names are
    case-insensitive in MySQL.

    Now this function compares column names in case-insensitive
    fashion.

Also noted this change in the description of the function in the 8.0 Manual. 

Closed.
[16 Sep 2021 20:18] Jon Stephens
BUG#104935 is a duplicate of this bug.