[MDEV-32986] MariaDB, SPIDER engine, usage of REGEXP - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.11.6, 11.3.1, 10.4(EOL)
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3
Component/s: Storage Engine - Spider
Labels:
- spider
- spider-gbh
Environment:
Debian11, docker

Description

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (

  `name` varchar(16) not null

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');

MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';

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

| name            |

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

| TestSpiderRegex |

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

1 row in set (0.000 sec)

Now, creating the respective SPIDER schema - table:

~MariaDB [test_db]> create database spider_test_db;

MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (

  `name` varchar(16) NOT NULL

) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNode", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;

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

| name            |

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

| TestSpiderRegex |

+-----------------+~

Selecting with REGEXP fails:

~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Attachments

Activity

Ascending order - Click to sort in descending order

Iacovos Botsaris created issue - 2023-12-11 13:18

Iacovos Botsaris made changes - 2023-12-11 13:19

Field	Original Value	New Value
Description	Hello, It appears as if I cannot use regular expressions against SPIDER engine tables. I have searched the documentation for related configuration parameters, but I was unable to find any. A Simple example can be seen below: ~MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` ( `name` varchar(16) not null ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;~ ~MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex'); MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test\|Spider\|Regex)'; +-----------------+ \| name \| +-----------------+ \| TestSpiderRegex \| +-----------------+ 1 row in set (0.000 sec)~ Now, creating the respective SPIDER schema - table: ~MariaDB [test_db]> create database spider_test_db; MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` ( `name` varchar(16) NOT NULL ) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"'; MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`; +-----------------+ \| name \| +-----------------+ \| TestSpiderRegex \| +-----------------+~ Selecting with REGEXP fails: ~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test\|Spider\|Regex)'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test\|Spider\|Regex)'))' at line 1~ Issue persists with all regular expressions I could try, in all queries I could try. Thank you	Hello, It appears as if I cannot use regular expressions against SPIDER engine tables. I have searched the documentation for related configuration parameters, but I was unable to find any. A Simple example can be seen below: ^MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` ( `name` varchar(16) not null ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;~ ~MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex'); MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test\|Spider\|Regex)'; +-----------------+ \| name \| +-----------------+ \| TestSpiderRegex \| +-----------------+ 1 row in set (0.000 sec)^ Now, creating the respective SPIDER schema - table: ~MariaDB [test_db]> create database spider_test_db; MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` ( `name` varchar(16) NOT NULL ) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"'; MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`; +-----------------+ \| name \| +-----------------+ \| TestSpiderRegex \| +-----------------+~ Selecting with REGEXP fails: ~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test\|Spider\|Regex)'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test\|Spider\|Regex)'))' at line 1~ Issue persists with all regular expressions I could try, in all queries I could try. Thank you

Iacovos Botsaris made changes - 2023-12-11 13:20

Description

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

^MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;~

~MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)^

Now, creating the respective SPIDER schema - table:
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~

Selecting with REGEXP fails:
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

bq. ^MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
bq. `name` varchar(16) not null
bq. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;~
bq.
bq. ~MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
bq. MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
bq. +-----------------+
bq. | name |
bq. +-----------------+
bq. | TestSpiderRegex |
bq. +-----------------+
bq. 1 row in set (0.000 sec)^

Now, creating the respective SPIDER schema - table:
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~

Selecting with REGEXP fails:
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Iacovos Botsaris made changes - 2023-12-11 13:20

Description

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

bq. ^MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
bq. `name` varchar(16) not null
bq. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;~
bq.
bq. ~MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
bq. MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
bq. +-----------------+
bq. | name |
bq. +-----------------+
bq. | TestSpiderRegex |
bq. +-----------------+
bq. 1 row in set (0.000 sec)^

Now, creating the respective SPIDER schema - table:
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~

Selecting with REGEXP fails:
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

{quote}bq. ^MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
bq. `name` varchar(16) not null
bq. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;~
bq.
bq. ~MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
bq. MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
bq. +-----------------+
bq. | name |
bq. +-----------------+
bq. | TestSpiderRegex |
bq. +-----------------+
bq. 1 row in set (0.000 sec)^{quote}

Now, creating the respective SPIDER schema - table:
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~

Selecting with REGEXP fails:
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Iacovos Botsaris made changes - 2023-12-11 13:21

Description

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

{quote}bq. ^MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
bq. `name` varchar(16) not null
bq. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;~
bq.
bq. ~MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
bq. MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
bq. +-----------------+
bq. | name |
bq. +-----------------+
bq. | TestSpiderRegex |
bq. +-----------------+
bq. 1 row in set (0.000 sec)^{quote}

Now, creating the respective SPIDER schema - table:
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~

Selecting with REGEXP fails:
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)

Now, creating the respective SPIDER schema - table:
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~

Selecting with REGEXP fails:
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Iacovos Botsaris made changes - 2023-12-11 13:24

Description

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)

Now, creating the respective SPIDER schema - table:
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~

Selecting with REGEXP fails:
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

{noformat}
A Simple example can be seen below:
{noformat}

{code:sql}
MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)
{code}

{noformat}
Now, creating the respective SPIDER schema - table:
{noformat}

{code:sql}
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~
{code}

{noformat}
Selecting with REGEXP fails:
{noformat}

{code:sql}
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~
{code:sql}

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Iacovos Botsaris made changes - 2023-12-11 13:27

Description

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

{noformat}
A Simple example can be seen below:
{noformat}

{code:sql}
MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)
{code}

{noformat}
Now, creating the respective SPIDER schema - table:
{noformat}

{code:sql}
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~
{code}

{noformat}
Selecting with REGEXP fails:
{noformat}

{code:sql}
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~
{code:sql}

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

{code:sql}
MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)
{code}

Now, creating the respective SPIDER schema - table:

{code:sql}
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~
{code}

{noformat}
Selecting with REGEXP fails:
{noformat}

{code:sql}
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~
{code:sql}

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Iacovos Botsaris made changes - 2023-12-11 13:28

Description

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

{code:sql}
MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)
{code}

Now, creating the respective SPIDER schema - table:

{code:sql}
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~
{code}

{noformat}
Selecting with REGEXP fails:
{noformat}

{code:sql}
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~
{code:sql}

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

{code:sql}
MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)
{code}

Now, creating the respective SPIDER schema - table:

{code:sql}
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~
{code}

Selecting with REGEXP fails:

{code:sql}
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~
{code:sql}

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Iacovos Botsaris made changes - 2023-12-11 13:29

Description

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

{code:sql}
MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)
{code}

Now, creating the respective SPIDER schema - table:

{code:sql}
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~
{code}

Selecting with REGEXP fails:

{code:sql}
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~
{code:sql}

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

{code:sql}
MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)
{code}

Now, creating the respective SPIDER schema - table:

{code:sql}
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~
{code}

Selecting with REGEXP fails:

{code:sql}
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~
{code}

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Iacovos Botsaris made changes - 2023-12-11 13:30

Description

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

{code:sql}
MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)
{code}

Now, creating the respective SPIDER schema - table:

{code:sql}
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNodeGalera", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~
{code}

Selecting with REGEXP fails:

{code:sql}
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~
{code}

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Hello,
It appears as if I cannot use regular expressions against SPIDER engine tables.
I have searched the documentation for related configuration parameters, but I was unable to find any.

A Simple example can be seen below:

{code:sql}
MariaDB [test_db]> CREATE TABLE `test_db`.`just_testing` (
`name` varchar(16) not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

MariaDB [test_db]> insert into `test_db`.`just_testing`(name) values ('TestSpiderRegex');
MariaDB [test_db]> select `name` from `test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+
1 row in set (0.000 sec)
{code}

Now, creating the respective SPIDER schema - table:

{code:sql}
~MariaDB [test_db]> create database spider_test_db;
MariaDB [test_db]> CREATE TABLE `spider_test_db`.`just_testing` (
`name` varchar(16) NOT NULL
) ENGINE=SPIDER DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='wrapper "mysql", srv "dataNode", database "test_db", table "just_testing"';

MariaDB [spider_test_db]> select * from `spider_test_db`.`just_testing`;
+-----------------+
| name |
+-----------------+
| TestSpiderRegex |
+-----------------+~
{code}

Selecting with REGEXP fails:

{code:sql}
~MariaDB [spider_test_db]> select `name` from `spider_test_db`.`just_testing` where `NAME` REGEXP '(Test|Spider|Regex)';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'regexp(t0.`name` , '(Test|Spider|Regex)'))' at line 1~
{code}

Issue persists with all regular expressions I could try, in all queries I could try.

Thank you

Iacovos Botsaris made changes - 2023-12-11 13:32

Labels

spider

Sergei Golubchik made changes - 2023-12-11 17:08

Fix Version/s

10.11 [ 27614 ]

Yuchen Pei made changes - 2023-12-13 03:49

Status

Open [ 1 ]

Confirmed [ 10101 ]

Yuchen Pei made changes - 2023-12-13 03:49

Affects Version/s

10.4 [ 22408 ]

Yuchen Pei added a comment - 2023-12-13 03:51

Thanks for the report ibotsaris, the following patch should fix it.

Hi holyfoot, ptal thanks:

upstream/bb-10.11-mdev-32986 1172790235505e04e7efe4ca4f079814bc771964

MDEV-32986 Make regexp operator work in spider group by handler

In spider_db_mbase_util::print_item_func(), if the sql item_func has

an UNKNOWN_FUNC type, by default the spider group by handler (gbh)

transform infix to prefix. But regexp should remain infix, so we add

an if condition to account for this.

For 10.4, which is an identical patch, see

f6145762b1d upstream/bb-10.4-mdev-32986 MDEV-32986 Make regexp operator work in spider group by handler

Yuchen Pei added a comment - 2023-12-13 03:51 Thanks for the report ibotsaris , the following patch should fix it. Hi holyfoot , ptal thanks: upstream/bb-10.11-mdev-32986 1172790235505e04e7efe4ca4f079814bc771964 MDEV-32986 Make regexp operator work in spider group by handler In spider_db_mbase_util::print_item_func(), if the sql item_func has an UNKNOWN_FUNC type, by default the spider group by handler (gbh) transform infix to prefix. But regexp should remain infix, so we add an if condition to account for this. For 10.4, which is an identical patch, see f6145762b1d upstream/bb-10.4-mdev-32986 MDEV-32986 Make regexp operator work in spider group by handler

Yuchen Pei made changes - 2023-12-13 03:51

Assignee	Yuchen Pei [ JIRAUSER52627 ]	Alexey Botchkov [ holyfoot ]
Status	Confirmed [ 10101 ]	In Review [ 10002 ]

Yuchen Pei made changes - 2023-12-13 03:52

Labels

spider

spider spider-gbh

Yuchen Pei made changes - 2023-12-13 03:52

Priority

Major [ 3 ]

Critical [ 2 ]

Alexey Botchkov added a comment - 2023-12-19 21:07

ok to push.

Alexey Botchkov added a comment - 2023-12-19 21:07 ok to push.

Alexey Botchkov made changes - 2023-12-19 21:07

Assignee	Alexey Botchkov [ holyfoot ]	Yuchen Pei [ JIRAUSER52627 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Yuchen Pei added a comment - 2023-12-21 00:14

pushed c73417c68eb03e35c93e4896df3f3d2d4dd4bfc0 to 10.4

Patch could be cleanly applied to 10.5,10.6,10.11,11.0

Tested locally also at 11.0 just in case.

Yuchen Pei added a comment - 2023-12-21 00:14 pushed c73417c68eb03e35c93e4896df3f3d2d4dd4bfc0 to 10.4 Patch could be cleanly applied to 10.5,10.6,10.11,11.0 Tested locally also at 11.0 just in case.

Yuchen Pei made changes - 2023-12-21 00:14

Fix Version/s		10.4.33 [ 29516 ]
Fix Version/s	10.11 [ 27614 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

JiraAutomate made changes - 2023-12-21 00:14

Fix Version/s		10.5.24 [ 29517 ]
Fix Version/s		10.6.17 [ 29518 ]
Fix Version/s		10.11.7 [ 29519 ]
Fix Version/s		11.0.5 [ 29520 ]
Fix Version/s		11.1.4 [ 29024 ]
Fix Version/s		11.2.3 [ 29521 ]

People

Assignee:: Yuchen Pei

Reporter:: Iacovos Botsaris

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2023-12-11 13:18

Updated:: 2023-12-22 23:51

Resolved:: 2023-12-21 00:14

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration