Add string access operator []

[Rafal]

I propose to introduce a new operators [pos] and [pos:len] to access elements of string types. Where pos - element index, starting from 0, as in c++ stings. Example:

mysql> CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

mysql> INSERT INTO `test` (`id`,`data`) VALUES(0,'');
mysql> UPDATE `test` SET `data`[5] = 'xxx';

mysql> SELECT * FROM `test` WHERE `id`=0;
+-----+----------------------------+
| sid | data |
+-----+----------------------------+
| 0 | ' xxx'
+-----+----------------------------+

mysql> UPDATE `test` SET `data`[3] = 'aaa';

mysql> SELECT * FROM `test` WHERE `id`=0;
+-----+----------------------------+
| sid | data |
+-----+----------------------------+
| 0 | ' aaaxx'
+-----+----------------------------+

mysql> UPDATE `test` SET `data`[3:1] = 'bbbbbb';

mysql> SELECT * FROM `test` WHERE `id`=0;
+-----+----------------------------+
| sid | data |
+-----+----------------------------+
| 0 | ' baaxx'
+-----+----------------------------+

mysql> SELECT `data`[4:3] FROM `test` WHERE `id`=0;
+----------------------------+
| data |
+----------------------------+
| 'aax'
+----------------------------+

[Nilnandan Joshi (nilnandan-joshi)]

Hi,

Could you specify semantics in more details ? like for
https://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_substring-index

After that, you should report this to upstream bug (Oracle MySQL). Because Percona server is based on Native MySQL.

[Rafal (andruwn)]

The idea is to make available to work with strings like char array in c++ (character arrays).
operator [index] and operator [index:len] in read querys (SELECT) works as a SUBSTRING(str,pos) and SUBSTRING(str,pos,len). The forms without a len argument return a substring from string starting at position index. The forms with a len argument return a substring len characters long from string str, starting at position index. String elements numbering starting from 0.
Examples:
mysql> SELECT "abv123cdf"[4];
+----------------------------+
 ' 23cdf'
+----------------------------+
mysql> SELECT "abv123cdf"[4:2];
+----------------------------+
 ' 23'
+----------------------------+
mysql> SELECT "abv123cdf"[104:2];
+----------------------------+
 ' '
+----------------------------+
operator [index]=val and operator [index:len]=val in write querys (UPDATE) works as INSERT(str,pos,len,newstr) or RPAD(str,len,padstr) + INSERT(str,pos,len,newstr).
1. String automatically right-padded with space for *char,text strings and NULL for *binary, blob strings to a length of val + index in form without a len argument, or to index+len in form with a len argument.
2. Substring beginning at position index and length of val characters long replaced by the val in form without a len argument. In form with a len argument substring beginning at position index and len characters long replaced by the val.
String elements numbering starting from 0.
Examples:
mysql> INSERT INTO `test` (`data`) VALUES('');
mysql> UPDATE `test` SET `data`[3] = 'abcmef';
mysql> SELECT * FROM `test`;
+----------------------------+
 ' abcmef'
+----------------------------+
mysql> INSERT INTO `test` (`data`) VALUES('');
mysql> UPDATE `test` SET `data`[3:2] = 'abcmef';
mysql> SELECT * FROM `test`;
+----------------------------+
 ' me'
+----------------------------+
mysql> INSERT INTO `test` (`data`) VALUES('11111');
mysql> UPDATE `test` SET `data`[3:5] = 'abcmef';
mysql> SELECT * FROM `test`;
+----------------------------+
 ' 111abcme'
+----------------------------+
mysql> INSERT INTO `test` (`data`) VALUES('11111');
mysql> UPDATE `test` SET `data`[1:1] = 'ab', `data`[3:1] = 'cd';
mysql> SELECT * FROM `test`;
+----------------------------+
 ' 1a1c1'
+----------------------------+

[Rafal (andruwn)]

Examples

[Rafal (andruwn)]

Bug #77346 in Oracle MySQL: http://bugs.mysql.com/bug.php?id=77346&thanks=4

[Sveta Smirnova (svetasmirnova)]

You can use Query Rewrite Plugins in 5.7 and implement in on your own. See https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html

[Rafal (andruwn)]

mysql> INSERT INTO `test` (`data`) VALUES('222');
mysql> UPDATE `test` SET `data`[1] = 'ab', `data`[3] = 'cd';

Last request Query Rewrite Plugins must rewrite to (or something like that):
mysql> UPDATE `test` SET `data` = INSERT(RPAD(`data`,MAX(LENGTH(`data`), 1+LENGTH('ab')),'\0'), 1, LENGTH('ab'), 'ab'), `data` = INSERT(RPAD(`data`,MAX(LENGTH(`data`), 3+LENGTH('cd')),'\0'), 3, LENGTH('cd'), 'cd');

Sveta, сan you help me implement this through Query Rewrite Plugins?
Thanks.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3289