parser, builtin: MATCH/AGAINST are not supported

[winkyao]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1),UNIQUE(f1));
EXPLAIN SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1));

2. What did you expect to see?

mysql> CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1),UNIQUE(f1));
Query OK, 0 rows affected (0.05 sec)

mysql> EXPLAIN SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1));
+----+-------------+-------+------------+----------+---------------+------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table | partitions | type     | possible_keys | key  | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+-------+------------+----------+---------------+------+---------+-------+------+----------+-----------------------------------+
|  1 | PRIMARY     | t1    | NULL       | index    | NULL          | f1_2 | 8       | NULL  |    1 |   100.00 | Using index                       |
|  2 | SUBQUERY    | t1    | NULL       | fulltext | f1            | f1   | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking |
|  2 | SUBQUERY    | a     | NULL       | index    | NULL          | f1_2 | 8       | NULL  |    1 |   100.00 | Using index                       |
+----+-------------+-------+------------+----------+---------------+------+---------+-------+------+----------+-----------------------------------+
3 rows in set, 1 warning (0.00 sec)

3. What did you see instead?

mysql> CREATE TABLE t1(f1 VARCHAR(6) NOT NULL, FULLTEXT KEY(f1),UNIQUE(f1));
Query OK, 0 rows affected (0.09 sec)

mysql> EXPLAIN SELECT 1 FROM t1 WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1 a ON (MATCH(t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1));
ERROR 1105 (HY000): line 0 column 77 near "t1.f1) AGAINST ("")) WHERE t1.f1 GROUP BY t1.f1))" (total length 126)

4. What version of TiDB are you using (tidb-server -V)?

[morgo]

FULLTEXT functions and indexes are currently documented as unsupported: https://www.pingcap.com/docs/sql/mysql-compatibility/

[morgo]

I am going to close this issue in favour of #1793 (they are closely related).

Please feel free to re-open this issue if you have any questions. Thanks!