[MDEV-15093] MyRocks: add support for virtual columns Created: 2018-01-26  Updated: 2019-07-06

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - RocksDB
Affects Version/s: None
Fix Version/s: 10.4

Type: Bug Priority: Minor
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None


 Description   

MyRocks-in-MariaDB doesn't support virtual columns:

create table sbtest1 (pad varchar(60)) engine=rocksdb;


MariaDB [j2]> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;
 
ERROR 1910 (HY000): ROCKSDB storage engine does not support generated columns

Btw, Percona Server doesn't suppor them either:

MySQL [test]> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;
 
ERROR 3106 (HY000): 'Specified storage engine' is not supported for generated columns.



 Comments   
Comment by Philip orleans [ 2019-04-20 ]

I need to create an index on a part of a column, but Rocksdb does not support virtual columns. Is there any alternative to get an index on the first character of a field only?

Comment by Sergei Petrunia [ 2019-04-22 ]

philip_38, just use a prefix index? {{CREATE TABLE t ( a VARCHAR(10), KEY(a(1))); }} ?

Comment by Philip orleans [ 2019-07-06 ]

The optimizer is not smart enough to take advantage of KEY(a(3)), in the case of RocksDB.
I have a primary key field number varchar(10), and added a new key test(number(3)), then I issue
a simple select
select distinct left(number,3) from table use index(IDX_lrn_did) ;
the optimizer ignores this index, which contains all the information needed to satisfy the query. If the table is very large, this select can take hours vs seconds.

Is there a workround?

Generated at Thu Feb 08 08:18:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.