0

My question applies to MariaDB 10.2.7

The query

WITH prefiltered AS (
  SELECT id, fulltext_indexed_column
  FROM fulltext_table
  WHERE id < 30
)
SELECT *
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?

Improve this question
2
  • 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

0

Reset to default

Your Answer

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