|
Re: Empty resultset when joining OQGRAPH tables
Interesting, Stephane. I haven't seen a join fail like this before.
One issue might be the join-order that the optimiser works out for your join, likely it'll do t1 first and that might create a problem, as looking up origid makes no sense, it should be "stuffed in to" the engine so it can do its calculations. The subquery does something similar.
Do you have EXPLAIN output for this, and possibly a debug log so we can see what the optimiser is thinking?
thanks
|
|
Reproducible on 5.2, 5.3, 5.5.
It is obviously not worth fixing in 5.2 or 5.3, for 5.5 please decide.
Complete test case:
DROP TABLE IF EXISTS t1, graph;
|
|
CREATE TABLE graph (
|
latch SMALLINT UNSIGNED NULL,
|
origid BIGINT UNSIGNED NULL,
|
destid BIGINT UNSIGNED NULL,
|
weight DOUBLE NULL,
|
seq BIGINT UNSIGNED NULL,
|
linkid BIGINT UNSIGNED NULL,
|
KEY (latch, origid, destid) USING HASH,
|
KEY (latch, destid, origid) USING HASH
|
) ENGINE=OQGRAPH;
|
|
delete from graph;
|
insert into graph(origid, destid) values (1,2), (2,1);
|
insert into graph(origid, destid) values (1,3), (3,1);
|
insert into graph(origid, destid) values (3,4), (4,3);
|
insert into graph(origid, destid) values (3,5), (5,3);
|
insert into graph(origid, destid) values (5,6), (6,5);
|
|
CREATE TABLE t1 (id INT) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
SELECT * FROM graph WHERE origid IN (1,2,3) AND latch =1;
|
SELECT * FROM graph INNER JOIN t1 ON t1.id = graph.origid WHERE t1.id IN (1,2,3) AND graph.latch =1;
|
SELECT * FROM graph WHERE graph.origid IN (SELECT t1.id FROM t1 WHERE t1.id IN (1,2,3)) AND graph.latch =1;
|
Results:
MariaDB [test]> SELECT * FROM graph WHERE origid IN (1,2,3) AND latch =1;
|
+-------+--------+--------+--------+------+--------+
|
| latch | origid | destid | weight | seq | linkid |
|
+-------+--------+--------+--------+------+--------+
|
| 1 | 1 | NULL | 3 | 6 | 6 |
|
| 1 | 1 | NULL | 2 | 5 | 5 |
|
| 1 | 1 | NULL | 2 | 4 | 4 |
|
| 1 | 1 | NULL | 1 | 3 | 3 |
|
| 1 | 1 | NULL | 1 | 2 | 2 |
|
| 1 | 1 | NULL | 0 | 1 | 1 |
|
| 1 | 2 | NULL | 4 | 6 | 6 |
|
| 1 | 2 | NULL | 3 | 5 | 5 |
|
| 1 | 2 | NULL | 3 | 4 | 4 |
|
| 1 | 2 | NULL | 2 | 3 | 3 |
|
| 1 | 2 | NULL | 1 | 2 | 1 |
|
| 1 | 2 | NULL | 0 | 1 | 2 |
|
| 1 | 3 | NULL | 2 | 6 | 2 |
|
| 1 | 3 | NULL | 2 | 5 | 6 |
|
| 1 | 3 | NULL | 1 | 4 | 4 |
|
| 1 | 3 | NULL | 1 | 3 | 5 |
|
| 1 | 3 | NULL | 1 | 2 | 1 |
|
| 1 | 3 | NULL | 0 | 1 | 3 |
|
+-------+--------+--------+--------+------+--------+
|
18 rows in set (0.00 sec)
|
MariaDB [test]> select * from t1;
|
+------+
|
| id |
|
+------+
|
| 1 |
|
| 2 |
|
| 3 |
|
+------+
|
3 rows in set (0.00 sec)
|
MariaDB [test]> SELECT * FROM graph INNER JOIN t1 ON t1.id = graph.origid WHERE t1.id IN (1,2,3) AND graph.latch =1;
|
Empty set (0.00 sec)
|
|
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM graph INNER JOIN t1 ON t1.id = graph.origid WHERE t1.id IN (1,2,3) AND graph.latch =1;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
|
| 1 | SIMPLE | graph | ALL | latch,latch_2 | NULL | NULL | NULL | 16 | 75.00 | Using where; Using join buffer (flat, BNL join) |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
2 rows in set, 1 warning (0.00 sec)
|
|
MariaDB [test]> show warnings;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`graph`.`latch` AS `latch`,`test`.`graph`.`origid` AS `origid`,`test`.`graph`.`destid` AS `destid`,`test`.`graph`.`weight` AS `weight`,`test`.`graph`.`seq` AS `seq`,`test`.`graph`.`linkid` AS `linkid`,`test`.`t1`.`id` AS `id` from `test`.`graph` join `test`.`t1` where ((`test`.`graph`.`latch` = 1) and (`test`.`t1`.`id` in (1,2,3)) and (`test`.`t1`.`id` = `test`.`graph`.`origid`)) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
MariaDB [test]> SELECT * FROM graph WHERE graph.origid IN (SELECT t1.id FROM t1 WHERE t1.id IN (1,2,3)) AND graph.latch =1;
|
Empty set (0.00 sec)
|
|
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM graph WHERE graph.origid IN (SELECT t1.id FROM t1 WHERE t1.id IN (1,2,3)) AND graph.latch =1;
|
+------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 3 | 100.00 | |
|
| 1 | PRIMARY | graph | ALL | latch,latch_2 | NULL | NULL | NULL | 16 | 75.00 | Using where; Using join buffer (flat, BNL join) |
|
| 2 | MATERIALIZED | t1 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
|
+------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+
|
3 rows in set, 1 warning (0.00 sec)
|
|
MariaDB [test]> show warnings;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`graph`.`latch` AS `latch`,`test`.`graph`.`origid` AS `origid`,`test`.`graph`.`destid` AS `destid`,`test`.`graph`.`weight` AS `weight`,`test`.`graph`.`seq` AS `seq`,`test`.`graph`.`linkid` AS `linkid` from `test`.`graph` semi join (`test`.`t1`) where ((`test`.`graph`.`latch` = 1) and (`test`.`t1`.`id` in (1,2,3)) and (`test`.`graph`.`origid` = `test`.`t1`.`id`)) |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
LPexportBug837496.xml
LPexportBug837496_leaf-explain.txt