Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.4(EOL)
-
None
Description
According to [1], no Item_equal should exist at JOIN::make_aggr_tables_info().
[1] https://mariadb.slack.com/archives/C021E77G7K2/p1710685372525459
However, this is not the case during the execution select statement in the following test:
10.4 d912a6369c6f7f8ba233ac88436d59f6e420c368 |
CREATE TABLE `t1` (`c` INT(10) UNSIGNED NOT NULL, `b` VARCHAR(255) NOT NULL , PRIMARY KEY (`c`) USING BTREE ) ENGINE=MYISAM; |
CREATE TABLE `t2` (`a` INT(10) UNSIGNED NOT NULL, `c` INT(10) UNSIGNED NOT NULL ) ENGINE=MYISAM; |
INSERT INTO t1 VALUES(1,'oooo'); |
INSERT INTO t2 VALUES(1,1); |
SELECT t2.a,t1.c FRoM t2 LEFT join t1 ON (t2.c = t1.c) WHERE t2.a = 1; |
drop table t1, t2; |
To see this, put a breakpoint at JOIN::make_aggr_tables_info(), and when it is hit, print the value of join_list->elem(0)->on_expr:
(rr) p join_list->elem(0)->on_expr
|
$4 = (Item_equal *) 0x52b000065fd8
|
Attachments
Issue Links
- relates to
-
MDEV-33679 spider returns parsing failure on valid left join select by translating the on expression to ()
-
- Closed
-
Note that this is a very edge case: all tables in the join are constant:
MariaDB [test]> explain extended SELECT t2.a,t1.c FRoM t2 LEFT join t1 ON (t2.c = t1.c) WHERE t2.a = 1;
+------+-------------+-------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t2 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | SIMPLE | t1 | system | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | |
+------+-------------+-------+--------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (27.379 sec)
MariaDB [test]> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------+
| Note | 1003 | select 1 AS `a`,1 AS `c` from `test`.`t1` where 1 |
+-------+------+---------------------------------------------------+
1 row in set (0.000 sec)
If I add some rows into t2 (leaving t1 still empty):
MariaDB [test]> insert into t2 values (1,1),(2,2);
Query OK, 2 rows affected (0.000 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> explain extended SELECT t2.a,t1.c FRoM t2 LEFT join t1 ON (t2.c = t1.c) WHERE t2.a = 1;
Then I see the execution enter this code:
Perform the optimization on fields evaluation mentioned above
for all on expressions.
*/
{
{
*tab->on_expr_ref= substitute_for_best_equal_field(thd, NO_PARTICULAR_TAB,
*tab->on_expr_ref,
tab->cond_equal,
(gdb) p dbug_print_item(*tab->on_expr_ref)
$8 = 0x5555577d3c80 <dbug_item_print_buf> "multiple equal(t2.c, t1.c)"
(gdb) next
(gdb) next
(gdb) next
(gdb) next
(gdb) p dbug_print_item(*tab->on_expr_ref)
$9 = 0x5555577d3c80 <dbug_item_print_buf> "t1.c = t2.c"
and then
Thread 36 "mysqld" hit Breakpoint 2, JOIN::make_aggr_tables_info (this=0x7ffee8018588) at /home/psergey/dev-git2/10.4-look2/sql/sql_select.cc:328
(gdb) p join_list->elem(0)->on_expr
$10 = (Item *) 0x7ffee80982e8
(gdb) set print obj
(gdb) p join_list->elem(0)->on_expr
$11 = (Item_func_eq *) 0x7ffee80982e8