My question applies to MariaDB 10.2.7

The query

WITH prefiltered AS (
  SELECT id, fulltext_indexed_column
  FROM fulltext_table
  WHERE id < 30
FROM prefiltered
WHERE MATCH(fulltext_indexed_column) AGAINST ('Foo')

leads to

Error #1191 - Can't find FULLTEXT index matching the column list

I understand why, the CTE results are not indexed. I can rewrite the query to not use the CTE, or to use fulltext search inside the first CTE.

But is there a way to achieve what I'm trying to do in this? Tell the DB to index the CTE results?

  • Looks like a query optimizer bug to me -- it should be able to perform the CTE merge (unless your actual query is different).
    – mustaccio
    Mar 11, 2019 at 18:06
  • The query is exactly the same, except for different table and column names.
    – Martin
    Mar 12, 2019 at 13:24


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.