Details
Minor
Description
tested on mariadb 5.2.8
Issue SELECT with JOIN OR SUBQUERIES
SELECT * FROM t_oqg WHERE t_oqg.origid IN (1,2,3) AND latch =1;
works fine
SELECT * FROM t_oqg INNER JON t1 ON t1.id = t_oqg.origid WHERE t1.id IN (1,2,3) AND t_oqg.latch =1;
empty result
SELECT * FROM t_oqg WHERE t_oqg.origid IN (SELECT t1.id FROM t1 WHERE t1.id IN (1,2,3)) AND t_oqg.latch =1;
empty result
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I'm just discovering OQGRAPH, and I immediately ran into this issue. I'm looking at OQG for simplifying management and queries of categorical data (trees) , and one of the queries I want to execute is "show me the paths for leaf nodes to the root of a branch." The query works for explicit values in an IN() clause but not the same values returned by a subquery.
I'll attach a simple case using a tiny subset of the Tree of Life data including EXPLAIN EXTENDED for each query. Generated using MariaDB 5.2.9.
leaf-explain.txt
LPexportBug837496_leaf-explain.txt
Re: Empty resultset when joining OQGRAPH tables
I'm just discovering OQGRAPH, and I immediately ran into this issue. I'm looking at OQG for simplifying management and queries of categorical data (trees) , and one of the queries I want to execute is "show me the paths for leaf nodes to the root of a branch." The query works for explicit values in an IN() clause but not the same values returned by a subquery.
I'll attach a simple case using a tiny subset of the Tree of Life data including EXPLAIN EXTENDED for each query. Generated using MariaDB 5.2.9.
Launchpad bug id: 837496
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)
Can someone please re-validate this for oqgraph v3 in MariaDB 10.0 ?
please report back here with findings.
thanks
Elena - whether it can be easily fixed in 5.5 will depend on the nature of the problem. There is a slim chance that if the issue exists in 10.0, it's the same in 5.5 and then the same fix might work for both versions.
Can someone please re-validate this for oqgraph v3 in MariaDB 10.0 ?
I already did, that's why the Affected Version(s) contains 5.x but not 10.0.
Sorry for not mentioning it, I now realize that it's not obvious at all.
It is still possible that a similar problem exists in 10.0, but this test case (adapted as little as possible just to work on 10.0) does not reveal it.
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