Jose Costa Teixeira added a comment - 2017-09-05 15:50 - edited Update: After checking the result of EXPLAIN, it seems that this is related to the use of temporary tables (please forgive my language, I am not very familiar with SQL) The version where it does not work has "Using filesort" while the one that works has a "Using temporary; Using filesort" After finding that the temporary tables could be due to ORDER BY on a same column, I made a workaround : added a "ORDER BY table1.entityKey DESC" in the subquery. This seems to resolve the issue. Perhaps it points to a solution. The query that works is (in bold underlined the addition to make it work) is: Select table1.entityKey from table1 where table1.Deleted = 0 and table1.MasterKey is null and table1.entityTypeKey = 8 and table1.entityKey in (select table2.entityKey from table2 where table2.Flag <> 2 and (table2.IndexKey = 4 and MATCH (table2.xhtmltext) AGAINST ('gold')) ORDER BY entityKey DESC ) order by table1.entityKey DESC

Elena Stepanova added a comment - 2017-09-19 21:21 Thanks for the report. As a workaround, you can set optimizer_switch=orderby_uses_equalities=off in the config file, it should help. Test case --source include/have_innodb.inc   CREATE TABLE t1 (entityKey INT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2),(3);   CREATE TABLE t2 (entityKey INT , xhtmltext TEXT, FULLTEXT(xhtmltext)) ENGINE=InnoDB; INSERT INTO t2 VALUES (1, "silver" ),(2, "gold" ),(3, "gold" ),(4, "gold" );   SELECT t1.entityKey FROM t1 WHERE t1.entityKey IN ( SELECT t2.entityKey FROM t2 WHERE MATCH (t2.xhtmltext) AGAINST ( 'gold' ) ) ORDER BY t1.entityKey DESC ;   # Cleanup DROP TABLE t1, t2; Actual result on 10.2 with default optimizer_switch MariaDB [test]> SELECT t1.entityKey FROM t1 -> WHERE t1.entityKey IN ( -> SELECT t2.entityKey FROM t2 -> WHERE MATCH (t2.xhtmltext) AGAINST ( 'gold' ) -> ) -> ORDER BY t1.entityKey DESC ; Empty set (0.06 sec) Expected result MariaDB [test]> SELECT t1.entityKey FROM t1 -> WHERE t1.entityKey IN ( -> SELECT t2.entityKey FROM t2 -> WHERE MATCH (t2.xhtmltext) AGAINST ( 'gold' ) -> ) -> ORDER BY t1.entityKey DESC ; + -----------+ | entityKey | + -----------+ | 3 | | 2 | + -----------+ 2 rows in set (0.08 sec) After fixing, please also check this test case. It differs from the first one only in the highlighted line, one extra value is inserted: Another test case --source include/have_innodb.inc   CREATE TABLE t1 (entityKey INT PRIMARY KEY ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2),(3),(4);   CREATE TABLE t2 (entityKey INT , xhtmltext TEXT, FULLTEXT(xhtmltext)) ENGINE=InnoDB; INSERT INTO t2 VALUES (1, "silver" ),(2, "gold" ),(3, "gold" ),(4, "gold" );   SELECT t1.entityKey FROM t1 WHERE t1.entityKey IN ( SELECT t2.entityKey FROM t2 WHERE MATCH (t2.xhtmltext) AGAINST ( 'gold' ) ) ORDER BY t1.entityKey DESC ;   # Cleanup DROP TABLE t1, t2; Actual result from the 2nd test case MariaDB [test]> SELECT t1.entityKey FROM t1 -> WHERE t1.entityKey IN ( -> SELECT t2.entityKey FROM t2 -> WHERE MATCH (t2.xhtmltext) AGAINST ( 'gold' ) -> ) -> ORDER BY t1.entityKey DESC ; + -----------+ | entityKey | + -----------+ | 4 | | 4 | | 4 | + -----------+ 3 rows in set (0.00 sec) With optimizer_switch=orderby_uses_equalities=off both test cases return expected results.

Jose Costa Teixeira added a comment - 2017-09-25 10:07 Hi. The workaround does not work in my specific case. I will have to wait for a fix. Thanks!

Varun Gupta (Inactive) added a comment - 2018-04-20 11:49 It is possibly a duplicate of MDEV-13694 which is in review