Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5328

Using regexp_substr on table with engine=columnstore results in error:"Function 'regexp_substr' isn't supported."

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 23.02, 6.4.2, 6.4.6
    • 23.10.2
    • None
    • 2024-1

    Description

      Using regexp_substr on table with engine=columnstore results in error:

      ERROR 1178 (42000): The storage engine for the table doesn't support MCS-1001: Function 'regexp_substr' isn't supported.

      To reproduce:
      create table t3(a int not null, b varchar(100)) engine=columnstore;
      insert into t3 values(1, 'My mouse'),(2,'Breakfast Food');
      select a, regexp_substr(b, 'a|e|i|o|u') AS "vowel" from t3;

      works with engine=innodb.

      Attachments

        Activity

          m-narita Masataka Narita added a comment - - edited

          In my tests, REGEXP_SUBSTR works in v1.2.5, so I think this is a regression bug, not a new feature.

          [root@mcs125 ~]# mcsmysql
          Welcome to the MariaDB monitor.  Commands end with ; or \g.
          Your MariaDB connection id is 21
          Server version: 10.3.16-MariaDB-log Columnstore 1.2.5-1
           
          Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
           
          Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
           
          MariaDB [(none)]>
          MariaDB [(none)]> create table test.T(col1 varchar(5)) engine columnstore;
          Query OK, 0 rows affected (0.223 sec)
           
          MariaDB [(none)]> insert into test.T values ('abc'),('def');
          Query OK, 2 rows affected (0.261 sec)
          Records: 2  Duplicates: 0  Warnings: 0
           
          MariaDB [(none)]> SELECT REGEXP_SUBSTR(col1, '[a-z]+') from test.T;
          +-------------------------------+
          | REGEXP_SUBSTR(col1, '[a-z]+') |
          +-------------------------------+
          | abc                           |
          | def                           |
          +-------------------------------+
          2 rows in set (0.059 sec)
           
          MariaDB [(none)]> insert into test.T values ('123');
          Query OK, 1 row affected (0.119 sec)
           
          MariaDB [(none)]> SELECT REGEXP_SUBSTR(col1, '[a-z]+') from test.T;
          +-------------------------------+
          | REGEXP_SUBSTR(col1, '[a-z]+') |
          +-------------------------------+
          | abc                           |
          | def                           |
          |                               |
          +-------------------------------+
          3 rows in set (0.012 sec)
           
          MariaDB [(none)]> show create table test.T
              -> ;
          +-------+-------------------------------------------------------------------------------------------------+
          | Table | Create Table                                                                                    |
          +-------+-------------------------------------------------------------------------------------------------+
          | T     | CREATE TABLE `t` (
            `col1` varchar(5) DEFAULT NULL
          ) ENGINE=Columnstore DEFAULT CHARSET=latin1 |
          +-------+-------------------------------------------------------------------------------------------------+
          1 row in set (0.000 sec)
          

          m-narita Masataka Narita added a comment - - edited In my tests, REGEXP_SUBSTR works in v1.2.5, so I think this is a regression bug, not a new feature. [root@mcs125 ~]# mcsmysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 21 Server version: 10.3.16-MariaDB-log Columnstore 1.2.5-1   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   MariaDB [(none)]> MariaDB [(none)]> create table test.T(col1 varchar(5)) engine columnstore; Query OK, 0 rows affected (0.223 sec)   MariaDB [(none)]> insert into test.T values ('abc'),('def'); Query OK, 2 rows affected (0.261 sec) Records: 2 Duplicates: 0 Warnings: 0   MariaDB [(none)]> SELECT REGEXP_SUBSTR(col1, '[a-z]+') from test.T; +-------------------------------+ | REGEXP_SUBSTR(col1, '[a-z]+') | +-------------------------------+ | abc | | def | +-------------------------------+ 2 rows in set (0.059 sec)   MariaDB [(none)]> insert into test.T values ('123'); Query OK, 1 row affected (0.119 sec)   MariaDB [(none)]> SELECT REGEXP_SUBSTR(col1, '[a-z]+') from test.T; +-------------------------------+ | REGEXP_SUBSTR(col1, '[a-z]+') | +-------------------------------+ | abc | | def | | | +-------------------------------+ 3 rows in set (0.012 sec)   MariaDB [(none)]> show create table test.T -> ; +-------+-------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------+ | T | CREATE TABLE `t` ( `col1` varchar(5) DEFAULT NULL ) ENGINE=Columnstore DEFAULT CHARSET=latin1 | +-------+-------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec)

          columnstore doesn't support regexp_substr and never did, at least from 2021. We can take this function to implement

          leonid.fedorov Leonid Fedorov added a comment - columnstore doesn't support regexp_substr and never did, at least from 2021. We can take this function to implement
          drrtuy Roman added a comment -

          Hi m-narita.
          `REGEXP_SUBSTR` in 1.2.5 was supported via autoswitch that effectively hands over query processing to MDB server in a slow compatibility mode. You can mimic the same behavior per-query setting `columnstore_select_handler=OFF` or `columnstore_select_handler=AUTO`.

          drrtuy Roman added a comment - Hi m-narita . `REGEXP_SUBSTR` in 1.2.5 was supported via autoswitch that effectively hands over query processing to MDB server in a slow compatibility mode. You can mimic the same behavior per-query setting `columnstore_select_handler=OFF` or `columnstore_select_handler=AUTO`.
          allen.herrera Allen Herrera added a comment - - edited

          Roman provided the solution already, sharing the results here - tested on 10.6.14-9-MariaDB-enterprise
          set session columnstore_select_handler=AUTO;
          You can set the global value to auto or to make persistent add it to your server.cnf file

          • vi /etc/my.cnf.d/server.cnf
            [mariadb]
            columnstore_select_handler=AUTO

          The ask is still though to add REGEXP_SUBSTR in columnstore engine for a performance boost i believe

          Proof:

          ariaDB [test]> create table t3(a int not null, b varchar(100)) engine=columnstore;
          Query OK, 0 rows affected (0.349 sec)
           
          MariaDB [test]> insert into t3 values(1, 'My mouse'),(2,'Breakfast Food');
          Query OK, 2 rows affected (1.169 sec)
          Records: 2  Duplicates: 0  Warnings: 0
           
          MariaDB [test]> select a, regexp_substr(b, 'a|e|i|o|u') AS "vowel" from t3;
          ERROR 1178 (42000): The storage engine for the table doesn't support MCS-1001: Function 'regexp_substr' isn't supported.
           
           
          MariaDB [test]> show variables like '%columnstore_select_handler%';
          +-------------------------------------------------+-------+
          | Variable_name                                   | Value |
          +-------------------------------------------------+-------+
          | columnstore_select_handler                      | ON    |
          | columnstore_select_handler_in_stored_procedures | ON    |
          +-------------------------------------------------+-------+
          2 rows in set (0.001 sec)
           
          MariaDB [test]> set session columnstore_select_handler=off;
          Query OK, 0 rows affected (0.001 sec)
           
          MariaDB [test]> show variables like '%columnstore_select_handler%';
          +-------------------------------------------------+-------+
          | Variable_name                                   | Value |
          +-------------------------------------------------+-------+
          | columnstore_select_handler                      | OFF   |
          | columnstore_select_handler_in_stored_procedures | ON    |
          +-------------------------------------------------+-------+
          2 rows in set (0.001 sec)
           
          MariaDB [test]> select a, regexp_substr(b, 'a|e|i|o|u') AS "vowel" from t3;
          +---+-------+
          | a | vowel |
          +---+-------+
          | 1 | o     |
          | 2 | e     |
          +---+-------+
          2 rows in set (0.036 sec)
           
          MariaDB [test]> set session columnstore_select_handler=AUTO;
          Query OK, 0 rows affected (0.000 sec)
           
          MariaDB [test]> show variables like '%columnstore_select_handler%';
          +-------------------------------------------------+-------+
          | Variable_name                                   | Value |
          +-------------------------------------------------+-------+
          | columnstore_select_handler                      | AUTO  |
          | columnstore_select_handler_in_stored_procedures | ON    |
          +-------------------------------------------------+-------+
          2 rows in set (0.001 sec)
           
          MariaDB [test]> select a, regexp_substr(b, 'a|e|i|o|u') AS "vowel" from t3;
          +---+-------+
          | a | vowel |
          +---+-------+
          | 1 | o     |
          | 2 | e     |
          +---+-------+
          2 rows in set, 1 warning (0.009 sec)
          

          allen.herrera Allen Herrera added a comment - - edited Roman provided the solution already, sharing the results here - tested on 10.6.14-9-MariaDB-enterprise set session columnstore_select_handler=AUTO; You can set the global value to auto or to make persistent add it to your server.cnf file vi /etc/my.cnf.d/server.cnf [mariadb] columnstore_select_handler=AUTO The ask is still though to add REGEXP_SUBSTR in columnstore engine for a performance boost i believe Proof: ariaDB [test]> create table t3(a int not null , b varchar( 100 )) engine=columnstore; Query OK, 0 rows affected ( 0.349 sec)   MariaDB [test]> insert into t3 values( 1 , 'My mouse' ),( 2 , 'Breakfast Food' ); Query OK, 2 rows affected ( 1.169 sec) Records: 2 Duplicates: 0 Warnings: 0   MariaDB [test]> select a, regexp_substr(b, 'a|e|i|o|u' ) AS "vowel" from t3; ERROR 1178 ( 42000 ): The storage engine for the table doesn 't support MCS-1001: Function ' regexp_substr ' isn' t supported.     MariaDB [test]> show variables like '%columnstore_select_handler%' ; +-------------------------------------------------+-------+ | Variable_name | Value | +-------------------------------------------------+-------+ | columnstore_select_handler | ON | | columnstore_select_handler_in_stored_procedures | ON | +-------------------------------------------------+-------+ 2 rows in set ( 0.001 sec)   MariaDB [test]> set session columnstore_select_handler=off; Query OK, 0 rows affected ( 0.001 sec)   MariaDB [test]> show variables like '%columnstore_select_handler%' ; +-------------------------------------------------+-------+ | Variable_name | Value | +-------------------------------------------------+-------+ | columnstore_select_handler | OFF | | columnstore_select_handler_in_stored_procedures | ON | +-------------------------------------------------+-------+ 2 rows in set ( 0.001 sec)   MariaDB [test]> select a, regexp_substr(b, 'a|e|i|o|u' ) AS "vowel" from t3; +---+-------+ | a | vowel | +---+-------+ | 1 | o | | 2 | e | +---+-------+ 2 rows in set ( 0.036 sec)   MariaDB [test]> set session columnstore_select_handler=AUTO; Query OK, 0 rows affected ( 0.000 sec)   MariaDB [test]> show variables like '%columnstore_select_handler%' ; +-------------------------------------------------+-------+ | Variable_name | Value | +-------------------------------------------------+-------+ | columnstore_select_handler | AUTO | | columnstore_select_handler_in_stored_procedures | ON | +-------------------------------------------------+-------+ 2 rows in set ( 0.001 sec)   MariaDB [test]> select a, regexp_substr(b, 'a|e|i|o|u' ) AS "vowel" from t3; +---+-------+ | a | vowel | +---+-------+ | 1 | o | | 2 | e | +---+-------+ 2 rows in set, 1 warning ( 0.009 sec)

          functions

          regexp_substr
          regexp_instr
          regexp_replace
          

          are to be delivered in the next release

          leonid.fedorov Leonid Fedorov added a comment - functions regexp_substr regexp_instr regexp_replace are to be delivered in the next release

          for testing purposes we need to check functions
          regexp
          regexp_substr
          regexp_instr
          regexp_replace
          on columnstore tables
          according public documentation on the same functions on server side

          leonid.fedorov Leonid Fedorov added a comment - for testing purposes we need to check functions regexp regexp_substr regexp_instr regexp_replace on columnstore tables according public documentation on the same functions on server side
          susil.behera Susil Behera added a comment -

          Reproduced the error,
          MariaDB-columnstore-engine.x86_64 10.6.17_12_23.10.1-1.el8

          select a, regexp_substr(b, 'a|e|i|o|u') AS "vowel" from t3;
          ERROR 1178 (42000): The storage engine for the table doesn't support MCS-1001: Function 'regexp_substr' isn't supported.
          select a, regexp_instr(b, 'a|e|i|o|u') AS "vowel" from t3;
          ERROR 1178 (42000): The storage engine for the table doesn't support MCS-1001: Function 'regexp_instr' isn't supported.
          select a, regexp_replace(b, 'a|e|i|o|u', 'x') AS "vowel" from t3;
          ERROR 1178 (42000): The storage engine for the table doesn't support MCS-1001: Function 'regexp_replace' isn't supported.

          Verified on develop branch commit 197acba>
          MariaDB [test]> create table t3(a int not null, b varchar(100)) engine=columnstore;
          Query OK, 0 rows affected (0.272 sec)

          MariaDB [test]> insert into t3 values(1, 'My mouse'),(2,'Breakfast Food');
          Query OK, 2 rows affected (0.272 sec)

          MariaDB [test]> show variables like '%columnstore_select_handler%';
          ------------------------------------------------------+

          Variable_name Value

          ------------------------------------------------------+

          columnstore_select_handler ON
          columnstore_select_handler_in_stored_procedures ON

          ------------------------------------------------------+
          2 rows in set (0.002 sec)

          MariaDB [test]> select a, regexp_substr(b, 'a|e|i|o|u') AS "vowel" from t3;
          --------+

          a vowel

          --------+

          1 o
          2 e

          --------+
          2 rows in set (0.013 sec)

          MariaDB [test]> select a, regexp_instr(b, 'a|e|i|o|u') AS "vowel" from t3;
          --------+

          a vowel

          --------+

          1 5
          2 3

          --------+
          2 rows in set (0.013 sec)

          MariaDB [test]> select a, regexp_replace(b, 'a|e|i|o|u', 'x') AS "vowel" from t3;
          -----------------+

          a vowel

          -----------------+

          1 My mxxsx
          2 Brxxkfxst Fxxd

          -----------------+
          2 rows in set (0.013 sec)

          Working fine.

          susil.behera Susil Behera added a comment - Reproduced the error, MariaDB-columnstore-engine.x86_64 10.6.17_12_23.10.1-1.el8 select a, regexp_substr(b, 'a|e|i|o|u') AS "vowel" from t3; ERROR 1178 (42000): The storage engine for the table doesn't support MCS-1001: Function 'regexp_substr' isn't supported. select a, regexp_instr(b, 'a|e|i|o|u') AS "vowel" from t3; ERROR 1178 (42000): The storage engine for the table doesn't support MCS-1001: Function 'regexp_instr' isn't supported. select a, regexp_replace(b, 'a|e|i|o|u', 'x') AS "vowel" from t3; ERROR 1178 (42000): The storage engine for the table doesn't support MCS-1001: Function 'regexp_replace' isn't supported. Verified on develop branch commit 197acba> MariaDB [test] > create table t3(a int not null, b varchar(100)) engine=columnstore; Query OK, 0 rows affected (0.272 sec) MariaDB [test] > insert into t3 values(1, 'My mouse'),(2,'Breakfast Food'); Query OK, 2 rows affected (0.272 sec) MariaDB [test] > show variables like '%columnstore_select_handler%'; ------------------------------------------------ ------+ Variable_name Value ------------------------------------------------ ------+ columnstore_select_handler ON columnstore_select_handler_in_stored_procedures ON ------------------------------------------------ ------+ 2 rows in set (0.002 sec) MariaDB [test] > select a, regexp_substr(b, 'a|e|i|o|u') AS "vowel" from t3; -- ------+ a vowel -- ------+ 1 o 2 e -- ------+ 2 rows in set (0.013 sec) MariaDB [test] > select a, regexp_instr(b, 'a|e|i|o|u') AS "vowel" from t3; -- ------+ a vowel -- ------+ 1 5 2 3 -- ------+ 2 rows in set (0.013 sec) MariaDB [test] > select a, regexp_replace(b, 'a|e|i|o|u', 'x') AS "vowel" from t3; -- ---------------+ a vowel -- ---------------+ 1 My mxxsx 2 Brxxkfxst Fxxd -- ---------------+ 2 rows in set (0.013 sec) Working fine.
          susil.behera Susil Behera added a comment -

          Verified OK.

          susil.behera Susil Behera added a comment - Verified OK.

          People

            leonid.fedorov Leonid Fedorov
            mpflaum Maria M Pflaum
            Votes:
            1 Vote for this issue
            Watchers:
            11 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.