Description:
UNIONing Selects from tables that both (or all) have a column that is JSON, yields a union where that column has type longtext (and therefore is treated wrongly when subsequently processed).
The example below is with two tables, and I put the results into views, so that 'describe' shows the problem. A view of a single table has type 'json', but when UNIONed with a second table, the resultant view's column has type 'longtext'.
Server version: 5.7.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database if not exists aatest;
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> drop table if exists aatest.test1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop table if exists aatest.test2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop view if exists aatest.testview1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop view if exists aatest.testview2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> create table aatest.test1 (json json);
Query OK, 0 rows affected (0.01 sec)
mysql> create table aatest.test2 (json json);
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> create view aatest.testview1
-> as select * from aatest.test1;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> create view aatest.testview2
-> as select * from aatest.test1
-> union all select * from aatest.test2 ;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> describe aatest.testview1;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| json | json | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> describe aatest.testview2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| json | longtext | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
How to repeat:
create database if not exists aatest;
drop table if exists aatest.test1;
drop table if exists aatest.test2;
drop view if exists aatest.testview1;
drop view if exists aatest.testview2;
create table aatest.test1 (json json);
create table aatest.test2 (json json);
create view aatest.testview1
as select * from aatest.test1;
create view aatest.testview2
as select * from aatest.test1
union all select * from aatest.test2 ;
describe aatest.testview1;
describe aatest.testview2;
Suggested fix:
the UNION of two JSON columns should be JSON.
Description: UNIONing Selects from tables that both (or all) have a column that is JSON, yields a union where that column has type longtext (and therefore is treated wrongly when subsequently processed). The example below is with two tables, and I put the results into views, so that 'describe' shows the problem. A view of a single table has type 'json', but when UNIONed with a second table, the resultant view's column has type 'longtext'. Server version: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database if not exists aatest; Query OK, 1 row affected, 1 warning (0.03 sec) mysql> drop table if exists aatest.test1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop table if exists aatest.test2; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop view if exists aatest.testview1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop view if exists aatest.testview2; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> create table aatest.test1 (json json); Query OK, 0 rows affected (0.01 sec) mysql> create table aatest.test2 (json json); Query OK, 0 rows affected (0.00 sec) mysql> mysql> create view aatest.testview1 -> as select * from aatest.test1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> create view aatest.testview2 -> as select * from aatest.test1 -> union all select * from aatest.test2 ; Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> describe aatest.testview1; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | json | json | YES | | NULL | | +-------+------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> describe aatest.testview2; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | json | longtext | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 1 row in set (0.00 sec) How to repeat: create database if not exists aatest; drop table if exists aatest.test1; drop table if exists aatest.test2; drop view if exists aatest.testview1; drop view if exists aatest.testview2; create table aatest.test1 (json json); create table aatest.test2 (json json); create view aatest.testview1 as select * from aatest.test1; create view aatest.testview2 as select * from aatest.test1 union all select * from aatest.test2 ; describe aatest.testview1; describe aatest.testview2; Suggested fix: the UNION of two JSON columns should be JSON.