Empty resultset when joining OQGRAPH tables

[Stephane VAROQUI]

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

[Arjen Lentz (arjen-lentz)]

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

[Steven Peckins (sep16)]

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.