Unable to create a table with the full text search for datatype TEXT

Help
1

Dears,

Using SingleStore version 7.5.11 I’m trying to create a column table with FullText feature for two fields one is a VARCHAR while the other one is TEXT datatype.

CREATE TABLE ftextsearch (
   id INT UNSIGNED,    
    msg VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    cause TEXT,
    KEY (id) USING CLUSTERED COLUMNSTORE,
    FULLTEXT(cause,msg)
);

I got the following error
CREATE TABLE ftextsearch ( id INT UNSIGNED, msg VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, cause TEXT, KEY (id) USING CLUSTERED COLUMNSTORE, FULLTEXT(cause,msg) )
ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature ‘FULLTEXT KEY with unsupported type’ is not supported by SingleStore.

After cheking my code and SingleStore documentation. It seems that I’m not able to create a fulltext index for TEXT datatype.

The following SQL statement is working fine
CREATE TABLE ftextsearchs (
id INT UNSIGNED,
msg VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
cause TEXT,
KEY (id) USING CLUSTERED COLUMNSTORE,
FULLTEXT(msg)
);

Could you tell me if I have made something wrong?

Regards
Emmanuel

2

This

CREATE TABLE ftextsearch (
   id INT UNSIGNED,    
    msg VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    cause TEXT,
    KEY (id) USING CLUSTERED COLUMNSTORE,
    FULLTEXT(cause,msg)
);

works for me on 7.5.11 on a fresh install.

Do you have any variables set to non-default settings? What is your setting for default_table_type? Mine was set to columntore.

3

Hi,

Thank you for your feedback. You are right if you are using fulltext with VARCHAR data types it works. But as mention in my previous message FULLTEXT does not work with TEXT, LONGTEXT and MEDIUMTEXT data type.

According to the documentation Working with Full Text Search

Please try this SQL statement:

CREATE TABLE ftextsearch (
    id INT UNSIGNED,    
    msg VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    cause TEXT,
    KEY (id) USING CLUSTERED COLUMNSTORE,
    FULLTEXT(cause,msg)
);

For me it returns:

ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature ‘FULLTEXT KEY with unsupported type’ is not supported by SingleStore.

Regards
Emmanuel

4

This is what you want to use - FULLTEXT indexing.

ALTER TABLE c_contacts ADD FULLTEXT full_name(fname, mname, lname)

Then use MATCH:
Kodi nox

SELECT * 
FROM c_contacts 
WHERE (MATCH(fname, mname, lname) AGAINST ('John' IN BOOLEAN MODE)) 
AND (MATCH(fname, mname, lname) AGAINST ('Smith' IN BOOLEAN MODE));
5

Hi,

If I apply the following on my columnar table this does not work.

ALTER TABLE ftextsearch ADD FULLTEXT full_name(msg, cause);

I have the following error

ERROR 1706 ER_MEMSQL_FEATURE_LOCKDOWN: Feature ‘Adding an INDEX that is not a HASH INDEX on COLUMNAR table’ is not supported by SingleStore.

My goal is to create a fulltext search on TEXT data type.

Regards
Emmanuel

6

Hi,

I have found my issue. It comes from the collate definition of my columns.

Thank you to all for your help

Regards
Emmanuel