Details
Minor
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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)
Thanks for clarification.
I'll assign it to Sergei Petrunia to decide whether it's a bug, I am not sure it's one either.
"Semi-joins" are for subqueries. The example uses OUTER joins. changing the title.
- 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.
Hi Federico,
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)