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 '19 at 18:06
  • The query is exactly the same, except for different table and column names. – Martin Mar 12 '19 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.