|
I am not sure whether it's the next stage of any of MDEV-25256 / MDEV-25254 / MDEV-25202, or all, or neither, so filing separately.
CREATE TABLE t1 (a INT, b VARCHAR(8));
|
INSERT INTO t1 VALUES (1,'{}'),(2,'[]');
|
|
CREATE TABLE t2 (a INT);
|
INSERT INTO t2 VALUES (2),(3);
|
|
SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1;
|
|
CREATE OR REPLACE VIEW v AS
|
SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1;
|
|
SELECT * FROM v;
|
The plain SELECT works, CREATE VIEW works, but SELECT from the view fails with ER_VIEW_INVALID.
|
bb-10.6-mdev17399-hf 160bd1691
|
MariaDB [test]> SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1;
|
Empty set (0.001 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> CREATE OR REPLACE VIEW v AS
|
-> SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1;
|
Query OK, 0 rows affected (0.011 sec)
|
|
MariaDB [test]>
|
MariaDB [test]> SELECT * FROM v;
|
ERROR 1356 (HY000): View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
MariaDB [test]> show create view v \G
|
*************************** 1. row ***************************
|
View: v
|
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (JSON_TABLE(`test`.`t1`.`b`, '$' COLUMNS (`o` FOR ORDINALITY)) `jt` left join (`test`.`t1` join `test`.`t2` on(`test`.`t1`.`a` = `test`.`t2`.`a`)) on(`test`.`t1`.`a` = `jt`.`o`)) where `test`.`t1`.`a` = 1
|
character_set_client: utf8
|
collation_connection: utf8_general_ci
|
1 row in set, 1 warning (0.001 sec)
|
MariaDB [test]> ANALYZE FORMAT=JSON SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1\G
|
*************************** 1. row ***************************
|
ANALYZE: {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.411648062,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 2,
|
"r_table_time_ms": 0.204035477,
|
"r_other_time_ms": 0.028717069,
|
"filtered": 100,
|
"r_filtered": 50,
|
"attached_condition": "t1.a = 1"
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 2,
|
"r_rows": 2,
|
"r_table_time_ms": 0.093966739,
|
"r_other_time_ms": 0.055881052,
|
"filtered": 100,
|
"r_filtered": 0,
|
"attached_condition": "t2.a = 1"
|
},
|
"buffer_type": "flat",
|
"buffer_size": "174",
|
"join_type": "BNL",
|
"r_filtered": null
|
},
|
"table": {
|
"table_name": "jt",
|
"access_type": "ALL",
|
"r_loops": 0,
|
"rows": 40,
|
"r_rows": null,
|
"filtered": 100,
|
"r_filtered": null,
|
"table_function": "json_table",
|
"attached_condition": "jt.o = 1"
|
}
|
}
|
}
|
1 row in set (0.002 sec)
|
MySQL returns ER_UNKNOWN_TABLE upon the plain SELECT and CREATE VIEW.
|