[MDEV-6452] Table elimination not applied to outer joins without WHERE Created: 2014-07-17  Updated: 2015-03-04  Resolved: 2015-03-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.12
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Sergei Petrunia
Resolution: Won't Fix Votes: 0
Labels: table-elimination


 Description   

I have no idea if this is a bug, but I'd expect it to work, so I hope it's worth reporting.

In the following test case the right table is read, but it doesn't affect the results in any way:

MariaDB [test]> SHOW CREATE TABLE city \G
 
*************************** 1. row ***************************
 
       Table: city
 
Create Table: CREATE TABLE `city` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `name` varchar(80) NOT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SHOW CREATE TABLE user \G
 
*************************** 1. row ***************************
 
       Table: user
 
Create Table: CREATE TABLE `user` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `city_id` int(11) NOT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> EXPLAIN SELECT c.name FROM city c LEFT JOIN user u ON c.id = u.city_id;
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
 
|    1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                                 |
 
|    1 | SIMPLE      | u     | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using where; Using join buffer (flat, BNL join) |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
 
2 rows in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2014-07-17 ]

Hi Federico,

it doesn't affect the results in any way

Why not? user.city_id is not unique, so the contents of the user table can easily affect the resultset of the query:

MariaDB [test]> CREATE TABLE `city` (
 
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
 
    ->   `name` varchar(80) NOT NULL,
 
    ->   PRIMARY KEY (`id`)
 
    -> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
 
Query OK, 0 rows affected (0.97 sec)
 
 
 
MariaDB [test]> CREATE TABLE `user` (
 
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
 
    ->   `city_id` int(11) NOT NULL,
 
    ->   PRIMARY KEY (`id`)
 
    -> ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
 
Query OK, 0 rows affected (0.92 sec)
 

MariaDB [test]> INSERT INTO city VALUES (1,'London');
 
Query OK, 1 row affected (0.07 sec)
 
 
 
MariaDB [test]>  SELECT c.name FROM city c LEFT JOIN user u ON c.id = u.city_id;
 
+--------+
 
| name   |
 
+--------+
 
| London |
 
+--------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> INSERT INTO user VALUES (1,1),(2,1);
 
Query OK, 2 rows affected (0.23 sec)
 
Records: 2  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]>  SELECT c.name FROM city c LEFT JOIN user u ON c.id = u.city_id;
 
+--------+
 
| name   |
 
+--------+
 
| London |
 
| London |
 
+--------+
 
2 rows in set (0.00 sec)

Comment by Federico Razzoli [ 2014-07-18 ]

SORRY, sometimes I'm so stupid. I forgot DISTINCT:

MariaDB [test]> EXPLAIN SELECT DISTINCT(c.name) FROM city c LEFT JOIN user u ON c.id = u.city_id;
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                                     |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------+
 
|    1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary                                           |
 
|    1 | SIMPLE      | u     | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where; Distinct; Using join buffer (flat, BNL join) |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------------+
 
2 rows in set (0.00 sec)

Comment by Elena Stepanova [ 2014-07-18 ]

Thanks for clarification.
I'll assign it to psergey to decide whether it's a bug, I am not sure it's one either.

Comment by Sergei Petrunia [ 2015-03-04 ]

"Semi-joins" are for subqueries. The example uses OUTER joins. changing the title.

Comment by Sergei Petrunia [ 2015-03-04 ]
  • Using DISTINCT from the select list is outside of scope of the table elimination feature. (in other words: it's not a logical extension of the analysis that we're doing)
  • I don't think that this example (no unique key, only left columns, select DISTINCT) - is a widely used practical case.

Considering the above, closing as "wont' fix". Feel free to reopen or file another bug if there arguments against the above.

Generated at Thu Feb 08 07:12:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.