1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
|
# 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;
|
