# Test cases for full-text search with the hypergraph optimizer # (WL#14422). FTS with the hypergraph optimizer is also tested by the # other tests in this suite. This test adds coverage for cases of # particular interest for the hypergraph optimizer, which are not # covered by the other tests. # Some of the queries give different (wrong) results when running with # the old optimizer, so this test only runs with the hypergraph # optimizer enabled. --source include/have_hypergraph.inc CREATE TABLE t(x VARCHAR(100), FULLTEXT KEY (x)); INSERT INTO t VALUES ('abc'), ('xyz'), (NULL), ('abc abc'), ('abc xyz'); ANALYZE TABLE t; # Hash joins don't work well with FTS and are currently disabled. # Below is a query which gives the wrong result when executed as a # hash join. --sorted_result SELECT a.x, MATCH (a.x) AGAINST ('abc') AS score_a, b.x, MATCH (b.x) AGAINST ('xyz') AS score_b FROM t a, t b WHERE a.x = b.x; # Compare MATCH to NULL. SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t WHERE MATCH (x) AGAINST ('abc') > NULL; # Use the same full-text search in multiple predicates in the same # query. SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t WHERE MATCH (x) AGAINST ('abc') AND MATCH (x) AGAINST ('abc') > 0.05; SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t WHERE MATCH (x) AGAINST ('abc') > 0.05 AND MATCH (x) AGAINST ('abc'); # Some extra coverage for aggregated queries which use FTS. The # hypergraph optimizer uses a different strategy than the old # optimizer in most cases (sorting before aggregation instead of # materialization). --sorted_result SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY x; --sorted_result SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY x, score; --sorted_result SELECT MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY score; --sorted_result SELECT x, MATCH (x) AGAINST ('abc')+1 AS score FROM t GROUP BY x; --sorted_result SELECT x, CONCAT(MATCH (x) AGAINST ('abc'), MAX(x)) AS score FROM t GROUP BY x; --sorted_result SELECT x FROM t GROUP BY x HAVING MATCH (x) AGAINST ('abc'); --sorted_result SELECT x FROM t GROUP BY x HAVING MATCH (x) AGAINST ('abc') > 0; --sorted_result SELECT MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY score WITH ROLLUP HAVING score > 0; --sorted_result SELECT MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY score WITH ROLLUP HAVING score > 0 OR score IS NULL; --error ER_FULLTEXT_WITH_ROLLUP SELECT MATCH (x) AGAINST ('abc') AS score, x FROM t GROUP BY x WITH ROLLUP ORDER BY score, x; # Check that ANY_VALUE picks a value from the correct group. The WHERE # clause picks rows with distinct scores, so that there is only one # row per group, in order to make the result deterministic. SELECT ANY_VALUE(x), MATCH (x) AGAINST ('abc') AS score FROM t WHERE x IN ('abc', 'abc abc', 'xyz') GROUP BY score ORDER BY score; DROP TABLE t;