[MDEV-32986] MariaDB, SPIDER engine, usage of REGEXP Created: 2023-12-11  Updated: 2023-12-22  Resolved: 2023-12-21

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.4, 10.11.6, 11.3.1
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Critical
Reporter: Iacovos Botsaris Assignee: Yuchen Pei
Resolution: Fixed Votes: 0
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

 Comments   
Comment by Yuchen Pei [ 2023-12-13 ]

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

Comment by Alexey Botchkov [ 2023-12-19 ]

ok to push.

Comment by Yuchen Pei [ 2023-12-21 ]

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.

Generated at Thu Feb 08 10:35:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.