Debian Codegraph @

mysql-test/suite/innodb_fts/r/hypergraph_optimizer.result

raw
1 CREATE TABLE t(x VARCHAR(100), FULLTEXT KEY (x));
2 INSERT INTO t VALUES ('abc'), ('xyz'), (NULL), ('abc abc'), ('abc xyz');
3 ANALYZE TABLE t;
4 Table Op Msg_type Msg_text
5 test.t analyze status OK
6 SELECT a.x, MATCH (a.x) AGAINST ('abc') AS score_a,
7 b.x, MATCH (b.x) AGAINST ('xyz') AS score_b
8 FROM t a, t b WHERE a.x = b.x;
9 x score_a x score_b
10 abc 0.0492168664932251 abc 0
11 abc abc 0.0984337329864502 abc abc 0
12 abc xyz 0.0492168664932251 abc xyz 0.15835624933242798
13 xyz 0 xyz 0.15835624933242798
14 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t
15 WHERE MATCH (x) AGAINST ('abc') > NULL;
16 x score
17 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t
18 WHERE MATCH (x) AGAINST ('abc')
19 AND MATCH (x) AGAINST ('abc') > 0.05;
20 x score
21 abc abc 0.0984337329864502
22 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t
23 WHERE MATCH (x) AGAINST ('abc') > 0.05
24 AND MATCH (x) AGAINST ('abc');
25 x score
26 abc abc 0.0984337329864502
27 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY x;
28 x score
29 NULL 0
30 abc 0.0492168664932251
31 abc abc 0.0984337329864502
32 abc xyz 0.0492168664932251
33 xyz 0
34 SELECT x, MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY x, score;
35 x score
36 NULL 0
37 abc 0.0492168664932251
38 abc abc 0.0984337329864502
39 abc xyz 0.0492168664932251
40 xyz 0
41 SELECT MATCH (x) AGAINST ('abc') AS score FROM t GROUP BY score;
42 score
43 0
44 0.0492168664932251
45 0.0984337329864502
46 SELECT x, MATCH (x) AGAINST ('abc')+1 AS score FROM t GROUP BY x;
47 x score
48 NULL 1
49 abc 1.049216866493225
50 abc abc 1.0984337329864502
51 abc xyz 1.049216866493225
52 xyz 1
53 SELECT x, CONCAT(MATCH (x) AGAINST ('abc'), MAX(x)) AS score FROM t GROUP BY x;
54 x score
55 NULL NULL
56 abc 0.0492168664932251abc
57 abc abc 0.0984337329864502abc abc
58 abc xyz 0.0492168664932251abc xyz
59 xyz 0xyz
60 SELECT x FROM t GROUP BY x HAVING MATCH (x) AGAINST ('abc');
61 x
62 abc
63 abc abc
64 abc xyz
65 SELECT x FROM t GROUP BY x HAVING MATCH (x) AGAINST ('abc') > 0;
66 x
67 abc
68 abc abc
69 abc xyz
70 SELECT MATCH (x) AGAINST ('abc') AS score FROM t
71 GROUP BY score WITH ROLLUP HAVING score > 0;
72 score
73 0.0492168664932251
74 0.0984337329864502
75 SELECT MATCH (x) AGAINST ('abc') AS score FROM t
76 GROUP BY score WITH ROLLUP HAVING score > 0 OR score IS NULL;
77 score
78 0.0492168664932251
79 0.0984337329864502
80 NULL
81 SELECT MATCH (x) AGAINST ('abc') AS score, x FROM t
82 GROUP BY x WITH ROLLUP ORDER BY score, x;
83 ERROR HY000: MATCH does not support ROLLUP columns.
84 SELECT ANY_VALUE(x), MATCH (x) AGAINST ('abc') AS score
85 FROM t
86 WHERE x IN ('abc', 'abc abc', 'xyz')
87 GROUP BY score
88 ORDER BY score;
89 ANY_VALUE(x) score
90 xyz 0
91 abc 0.0492168664932251
92 abc abc 0.0984337329864502
93 DROP TABLE t;