On Sun, Feb 14, 2021 at 05:14:10PM +0400, Alexey Botchkov wrote:

One question left about the json_table_mysql.text.

--error ER_WRONG_OUTER_JOIN SELECT * FROM t1 RIGHT JOIN (SELECT o FROM t2, JSON_TABLE(j, '$[*]' COLUMNS (o FOR ORDINALITY)) AS jt) AS t3 ON (t3.o = t1.x);

What is exactly wrong with the above join?

It should work. The reason it doesn't work was described by me in the email with subject "MDEV-17399: JSON_TABLE's dependencies are not updated after derived merge" dated Feb, 8th. What happens here is: Thread 14 "mysqld" hit Breakpoint 1, Table_function_json_table::setup() (gdb) list 1091 } 1092 } 1093

1094 m_dep_tables= m_json->used_tables(); 1095

(gdb) p m_dep_tables $44 = 1 If you go one frame up, you will see that we are in select#2. tables are: (gdb) p join->select_lex->leaf_tables.elem(0)->alias.str $56 = 0x7ffeb0016550 "t2" (gdb) p join->select_lex->leaf_tables.elem(0)->table->map $58 = 1 (gdb) p join->select_lex->leaf_tables.elem(1)->alias.str $60 = 0x7ffeb0017d70 "jt" (gdb) p join->select_lex->leaf_tables.elem(1)->table->map $62 = 2 Ok, jt depends on t2. Then: Thread 14 "mysqld" hit Breakpoint 2, simplify_joins (gdb) p join->select_lex->leaf_tables.elements $65 = 3 (gdb) p join->select_lex->leaf_tables.elem(0)->alias.str $66 = 0x7ffeb0015870 "t1" (gdb) p join->select_lex->leaf_tables.elem(0)->table->map $67 = 1 (gdb) p join->select_lex->leaf_tables.elem(1)->alias.str $68 = 0x7ffeb0016550 "t2" (gdb) p join->select_lex->leaf_tables.elem(1)->table->map $69 = 2 (gdb) p join->select_lex->leaf_tables.elem(2)->alias.str $70 = 0x7ffeb0017d70 "jt" (gdb) p join->select_lex->leaf_tables.elem(2)->table->map $71 = 4 But the m_dep_tables still has the old value: (gdb) p join->select_lex->leaf_tables.elem(2)->table_function->m_dep_tables $72 = 1 BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net