maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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