Debian Codegraph @

mysql-test/suite/innodb_fts/t/hypergraph_optimizer.test

raw
1 # Test cases for full-text search with the hypergraph optimizer
2 # (WL#14422). FTS with the hypergraph optimizer is also tested by the
3 # other tests in this suite. This test adds coverage for cases of
4 # particular interest for the hypergraph optimizer, which are not
5 # covered by the other tests.
6
7 # Some of the queries give different (wrong) results when running with
8 # the old optimizer, so this test only runs with the hypergraph
9 # optimizer enabled.
10 --source include/have_hypergraph.inc
11
12 CREATE TABLE t(x VARCHAR(100), FULLTEXT KEY (x));
13 INSERT INTO t VALUES ('abc'), ('xyz'), (NULL), ('abc abc'), ('abc xyz');
14 ANALYZE TABLE t;
15
16 # Hash joins don't work well with FTS and are currently disabled.
17 # Below is a query which gives the wrong result when executed as a
18 # hash join.
19 --sorted_result
20 SELECT a.x, MATCH (a.x) AGAINST ('abc') AS score_a,
21 b.x, MATCH (b.x) AGAINST ('xyz') AS score_b
22 FROM t a, t b WHERE a.x = b.x;
23
24 # Compare MATCH to NULL.
25 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t
26 WHERE MATCH (x) AGAINST ('abc') > NULL;
27
28 # Use the same full-text search in multiple predicates in the same
29 # query.
30 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t
31 WHERE MATCH (x) AGAINST ('abc')
32 AND MATCH (x) AGAINST ('abc') > 0.05;
33 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t
34 WHERE MATCH (x) AGAINST ('abc') > 0.05
35 AND MATCH (x) AGAINST ('abc');
36
37 # Some extra coverage for aggregated queries which use FTS. The
38 # hypergraph optimizer uses a different strategy than the old
39 # optimizer in most cases (sorting before aggregation instead of
40 # materialization).
41 --sorted_result
42 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY x;
43 --sorted_result
44 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY x, score;
45 --sorted_result
46 SELECT MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY score;
47 --sorted_result
48 SELECT x, MATCH (x) AGAINST ('abc')+1 AS score FROM t GROUP BY x;
49 --sorted_result
50 SELECT x, CONCAT(MATCH (x) AGAINST ('abc'), MAX(x)) AS score FROM t GROUP BY x;
51 --sorted_result
52 SELECT x FROM t GROUP BY x HAVING MATCH (x) AGAINST ('abc');
53 --sorted_result
54 SELECT x FROM t GROUP BY x HAVING MATCH (x) AGAINST ('abc') > 0;
55 --sorted_result
56 SELECT MATCH (x) AGAINST ('abc') AS score FROM t
57 GROUP BY score WITH ROLLUP HAVING score > 0;
58 --sorted_result
59 SELECT MATCH (x) AGAINST ('abc') AS score FROM t
60 GROUP BY score WITH ROLLUP HAVING score > 0 OR score IS NULL;
61 --error ER_FULLTEXT_WITH_ROLLUP
62 SELECT MATCH (x) AGAINST ('abc') AS score, x FROM t
63 GROUP BY x WITH ROLLUP ORDER BY score, x;
64 # Check that ANY_VALUE picks a value from the correct group. The WHERE
65 # clause picks rows with distinct scores, so that there is only one
66 # row per group, in order to make the result deterministic.
67 SELECT ANY_VALUE(x), MATCH (x) AGAINST ('abc') AS score
68 FROM t
69 WHERE x IN ('abc', 'abc abc', 'xyz')
70 GROUP BY score
71 ORDER BY score;
72
73 DROP TABLE t;