mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-14 09:12:57 +00:00
3866512cf6
* feat: add more mysql string functions Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * refactor: use datafusion aliasing mechanism, close #7415 Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: comment Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: comment and style Signed-off-by: Dennis Zhuang <killme2008@gmail.com> --------- Signed-off-by: Dennis Zhuang <killme2008@gmail.com>
348 lines
10 KiB
Plaintext
348 lines
10 KiB
Plaintext
-- MySQL-compatible string function tests
|
|
-- LOCATE function tests
|
|
SELECT LOCATE('world', 'hello world');
|
|
|
|
+-------------------------------------------+
|
|
| locate(Utf8("world"),Utf8("hello world")) |
|
|
+-------------------------------------------+
|
|
| 7 |
|
|
+-------------------------------------------+
|
|
|
|
SELECT LOCATE('xyz', 'hello world');
|
|
|
|
+-----------------------------------------+
|
|
| locate(Utf8("xyz"),Utf8("hello world")) |
|
|
+-----------------------------------------+
|
|
| 0 |
|
|
+-----------------------------------------+
|
|
|
|
SELECT LOCATE('o', 'hello world');
|
|
|
|
+---------------------------------------+
|
|
| locate(Utf8("o"),Utf8("hello world")) |
|
|
+---------------------------------------+
|
|
| 5 |
|
|
+---------------------------------------+
|
|
|
|
SELECT LOCATE('o', 'hello world', 5);
|
|
|
|
+------------------------------------------------+
|
|
| locate(Utf8("o"),Utf8("hello world"),Int64(5)) |
|
|
+------------------------------------------------+
|
|
| 5 |
|
|
+------------------------------------------------+
|
|
|
|
SELECT LOCATE('o', 'hello world', 6);
|
|
|
|
+------------------------------------------------+
|
|
| locate(Utf8("o"),Utf8("hello world"),Int64(6)) |
|
|
+------------------------------------------------+
|
|
| 8 |
|
|
+------------------------------------------------+
|
|
|
|
SELECT LOCATE('', 'hello');
|
|
|
|
+--------------------------------+
|
|
| locate(Utf8(""),Utf8("hello")) |
|
|
+--------------------------------+
|
|
| 1 |
|
|
+--------------------------------+
|
|
|
|
SELECT LOCATE('世', 'hello世界');
|
|
|
|
+--------------------------------------+
|
|
| locate(Utf8("世"),Utf8("hello世界")) |
|
|
+--------------------------------------+
|
|
| 6 |
|
|
+--------------------------------------+
|
|
|
|
SELECT LOCATE(NULL, 'hello');
|
|
|
|
+----------------------------+
|
|
| locate(NULL,Utf8("hello")) |
|
|
+----------------------------+
|
|
| |
|
|
+----------------------------+
|
|
|
|
SELECT LOCATE('o', NULL);
|
|
|
|
+------------------------+
|
|
| locate(Utf8("o"),NULL) |
|
|
+------------------------+
|
|
| |
|
|
+------------------------+
|
|
|
|
-- ELT function tests
|
|
SELECT ELT(1, 'a', 'b', 'c');
|
|
|
|
+---------------------------------------------+
|
|
| elt(Int64(1),Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+---------------------------------------------+
|
|
| a |
|
|
+---------------------------------------------+
|
|
|
|
SELECT ELT(2, 'a', 'b', 'c');
|
|
|
|
+---------------------------------------------+
|
|
| elt(Int64(2),Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+---------------------------------------------+
|
|
| b |
|
|
+---------------------------------------------+
|
|
|
|
SELECT ELT(3, 'a', 'b', 'c');
|
|
|
|
+---------------------------------------------+
|
|
| elt(Int64(3),Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+---------------------------------------------+
|
|
| c |
|
|
+---------------------------------------------+
|
|
|
|
SELECT ELT(0, 'a', 'b', 'c');
|
|
|
|
+---------------------------------------------+
|
|
| elt(Int64(0),Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+---------------------------------------------+
|
|
| |
|
|
+---------------------------------------------+
|
|
|
|
SELECT ELT(4, 'a', 'b', 'c');
|
|
|
|
+---------------------------------------------+
|
|
| elt(Int64(4),Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+---------------------------------------------+
|
|
| |
|
|
+---------------------------------------------+
|
|
|
|
SELECT ELT(NULL, 'a', 'b', 'c');
|
|
|
|
+-----------------------------------------+
|
|
| elt(NULL,Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+-----------------------------------------+
|
|
| |
|
|
+-----------------------------------------+
|
|
|
|
-- FIELD function tests
|
|
SELECT FIELD('b', 'a', 'b', 'c');
|
|
|
|
+------------------------------------------------+
|
|
| field(Utf8("b"),Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+------------------------------------------------+
|
|
| 2 |
|
|
+------------------------------------------------+
|
|
|
|
SELECT FIELD('d', 'a', 'b', 'c');
|
|
|
|
+------------------------------------------------+
|
|
| field(Utf8("d"),Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+------------------------------------------------+
|
|
| 0 |
|
|
+------------------------------------------------+
|
|
|
|
SELECT FIELD('a', 'a', 'b', 'c');
|
|
|
|
+------------------------------------------------+
|
|
| field(Utf8("a"),Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+------------------------------------------------+
|
|
| 1 |
|
|
+------------------------------------------------+
|
|
|
|
SELECT FIELD('A', 'a', 'b', 'c');
|
|
|
|
+------------------------------------------------+
|
|
| field(Utf8("A"),Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+------------------------------------------------+
|
|
| 0 |
|
|
+------------------------------------------------+
|
|
|
|
SELECT FIELD(NULL, 'a', 'b', 'c');
|
|
|
|
+-------------------------------------------+
|
|
| field(NULL,Utf8("a"),Utf8("b"),Utf8("c")) |
|
|
+-------------------------------------------+
|
|
| 0 |
|
|
+-------------------------------------------+
|
|
|
|
-- INSERT function tests
|
|
SELECT INSERT('Quadratic', 3, 4, 'What');
|
|
|
|
+----------------------------------------------------------+
|
|
| insert(Utf8("Quadratic"),Int64(3),Int64(4),Utf8("What")) |
|
|
+----------------------------------------------------------+
|
|
| QuWhattic |
|
|
+----------------------------------------------------------+
|
|
|
|
SELECT INSERT('Quadratic', 3, 100, 'What');
|
|
|
|
+------------------------------------------------------------+
|
|
| insert(Utf8("Quadratic"),Int64(3),Int64(100),Utf8("What")) |
|
|
+------------------------------------------------------------+
|
|
| QuWhat |
|
|
+------------------------------------------------------------+
|
|
|
|
SELECT INSERT('Quadratic', 0, 4, 'What');
|
|
|
|
+----------------------------------------------------------+
|
|
| insert(Utf8("Quadratic"),Int64(0),Int64(4),Utf8("What")) |
|
|
+----------------------------------------------------------+
|
|
| Quadratic |
|
|
+----------------------------------------------------------+
|
|
|
|
SELECT INSERT('hello', 1, 0, 'X');
|
|
|
|
+---------------------------------------------------+
|
|
| insert(Utf8("hello"),Int64(1),Int64(0),Utf8("X")) |
|
|
+---------------------------------------------------+
|
|
| Xhello |
|
|
+---------------------------------------------------+
|
|
|
|
SELECT INSERT('hello世界', 6, 1, 'の');
|
|
|
|
+--------------------------------------------------------+
|
|
| insert(Utf8("hello世界"),Int64(6),Int64(1),Utf8("の")) |
|
|
+--------------------------------------------------------+
|
|
| helloの界 |
|
|
+--------------------------------------------------------+
|
|
|
|
SELECT INSERT(NULL, 1, 1, 'X');
|
|
|
|
+------------------------------------------+
|
|
| insert(NULL,Int64(1),Int64(1),Utf8("X")) |
|
|
+------------------------------------------+
|
|
| |
|
|
+------------------------------------------+
|
|
|
|
-- SPACE function tests
|
|
SELECT SPACE(5);
|
|
|
|
+-----------------+
|
|
| space(Int64(5)) |
|
|
+-----------------+
|
|
| |
|
|
+-----------------+
|
|
|
|
SELECT SPACE(0);
|
|
|
|
+-----------------+
|
|
| space(Int64(0)) |
|
|
+-----------------+
|
|
| |
|
|
+-----------------+
|
|
|
|
SELECT SPACE(-1);
|
|
|
|
+------------------+
|
|
| space(Int64(-1)) |
|
|
+------------------+
|
|
| |
|
|
+------------------+
|
|
|
|
SELECT CONCAT('a', SPACE(3), 'b');
|
|
|
|
+---------------------------------------------+
|
|
| concat(Utf8("a"),space(Int64(3)),Utf8("b")) |
|
|
+---------------------------------------------+
|
|
| a b |
|
|
+---------------------------------------------+
|
|
|
|
SELECT SPACE(NULL);
|
|
|
|
+-------------+
|
|
| space(NULL) |
|
|
+-------------+
|
|
| |
|
|
+-------------+
|
|
|
|
-- FORMAT function tests
|
|
SELECT FORMAT(1234567.891, 2);
|
|
|
|
+---------------------------------------+
|
|
| format(Float64(1234567.891),Int64(2)) |
|
|
+---------------------------------------+
|
|
| 1,234,567.89 |
|
|
+---------------------------------------+
|
|
|
|
SELECT FORMAT(1234567.891, 0);
|
|
|
|
+---------------------------------------+
|
|
| format(Float64(1234567.891),Int64(0)) |
|
|
+---------------------------------------+
|
|
| 1,234,568 |
|
|
+---------------------------------------+
|
|
|
|
SELECT FORMAT(1234.5, 4);
|
|
|
|
+----------------------------------+
|
|
| format(Float64(1234.5),Int64(4)) |
|
|
+----------------------------------+
|
|
| 1,234.5000 |
|
|
+----------------------------------+
|
|
|
|
SELECT FORMAT(-1234567.891, 2);
|
|
|
|
+----------------------------------------+
|
|
| format(Float64(-1234567.891),Int64(2)) |
|
|
+----------------------------------------+
|
|
| -1,234,567.89 |
|
|
+----------------------------------------+
|
|
|
|
SELECT FORMAT(0.5, 2);
|
|
|
|
+-------------------------------+
|
|
| format(Float64(0.5),Int64(2)) |
|
|
+-------------------------------+
|
|
| 0.50 |
|
|
+-------------------------------+
|
|
|
|
SELECT FORMAT(123, 2);
|
|
|
|
+-----------------------------+
|
|
| format(Int64(123),Int64(2)) |
|
|
+-----------------------------+
|
|
| 123.00 |
|
|
+-----------------------------+
|
|
|
|
SELECT FORMAT(NULL, 2);
|
|
|
|
+-----------------------+
|
|
| format(NULL,Int64(2)) |
|
|
+-----------------------+
|
|
| |
|
|
+-----------------------+
|
|
|
|
-- Combined test with table
|
|
CREATE TABLE string_test(idx INT, val VARCHAR, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO string_test VALUES
|
|
(1, 'hello world', 1),
|
|
(2, 'foo bar baz', 2),
|
|
(3, 'hello世界', 3);
|
|
|
|
Affected Rows: 3
|
|
|
|
SELECT idx, val, LOCATE('o', val) as loc FROM string_test ORDER BY idx;
|
|
|
|
+-----+-------------+-----+
|
|
| idx | val | loc |
|
|
+-----+-------------+-----+
|
|
| 1 | hello world | 5 |
|
|
| 2 | foo bar baz | 2 |
|
|
| 3 | hello世界 | 5 |
|
|
+-----+-------------+-----+
|
|
|
|
SELECT idx, val, INSERT(val, 1, 5, 'hi') as inserted FROM string_test ORDER BY idx;
|
|
|
|
+-----+-------------+----------+
|
|
| idx | val | inserted |
|
|
+-----+-------------+----------+
|
|
| 1 | hello world | hi world |
|
|
| 2 | foo bar baz | hiar baz |
|
|
| 3 | hello世界 | hi世界 |
|
|
+-----+-------------+----------+
|
|
|
|
DROP TABLE string_test;
|
|
|
|
Affected Rows: 0
|
|
|