mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-13 00:32:56 +00:00
d8563ba56d
* feat: adds format, regex_extract function and more type tests Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: forgot functions Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: forgot null type Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * test: forgot date type Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * feat: remove format function Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * test: update results after upgrading datafusion Signed-off-by: Dennis Zhuang <killme2008@gmail.com> --------- Signed-off-by: Dennis Zhuang <killme2008@gmail.com>
174 lines
6.3 KiB
Plaintext
174 lines
6.3 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/function/string/test_substring.test
|
|
-- Substring function tests
|
|
CREATE TABLE strings(s VARCHAR, "off" INTEGER, length INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO strings VALUES
|
|
('hello', 1, 2, 1000),
|
|
('world', 2, 3, 2000),
|
|
('b', 1, 1, 3000),
|
|
(NULL, 2, 2, 4000);
|
|
|
|
Affected Rows: 4
|
|
|
|
-- Test zero length
|
|
SELECT SUBSTRING('🦆ab', 1, 0), SUBSTRING('abc', 1, 0);
|
|
|
|
+----------------------------------------+---------------------------------------+
|
|
| substr(Utf8("🦆ab"),Int64(1),Int64(0)) | substr(Utf8("abc"),Int64(1),Int64(0)) |
|
|
+----------------------------------------+---------------------------------------+
|
|
| | |
|
|
+----------------------------------------+---------------------------------------+
|
|
|
|
-- Normal substring with constant offset/length
|
|
SELECT SUBSTRING(s, 1, 2) FROM strings ORDER BY ts;
|
|
|
|
+-------------------------------------+
|
|
| substr(strings.s,Int64(1),Int64(2)) |
|
|
+-------------------------------------+
|
|
| he |
|
|
| wo |
|
|
| b |
|
|
| |
|
|
+-------------------------------------+
|
|
|
|
-- Substring out of range
|
|
SELECT SUBSTRING(s, 2, 2) FROM strings ORDER BY ts;
|
|
|
|
+-------------------------------------+
|
|
| substr(strings.s,Int64(2),Int64(2)) |
|
|
+-------------------------------------+
|
|
| el |
|
|
| or |
|
|
| |
|
|
| |
|
|
+-------------------------------------+
|
|
|
|
-- Variable length offset/length
|
|
SELECT SUBSTRING(s, "off", "length") FROM strings ORDER BY ts;
|
|
|
|
+----------------------------------------------+
|
|
| substr(strings.s,strings.off,strings.length) |
|
|
+----------------------------------------------+
|
|
| he |
|
|
| orl |
|
|
| b |
|
|
| |
|
|
+----------------------------------------------+
|
|
|
|
SELECT SUBSTRING(s, "off", 2) FROM strings ORDER BY ts;
|
|
|
|
+----------------------------------------+
|
|
| substr(strings.s,strings.off,Int64(2)) |
|
|
+----------------------------------------+
|
|
| he |
|
|
| or |
|
|
| b |
|
|
| |
|
|
+----------------------------------------+
|
|
|
|
SELECT SUBSTRING(s, 1, length) FROM strings ORDER BY ts;
|
|
|
|
+-------------------------------------------+
|
|
| substr(strings.s,Int64(1),strings.length) |
|
|
+-------------------------------------------+
|
|
| he |
|
|
| wor |
|
|
| b |
|
|
| |
|
|
+-------------------------------------------+
|
|
|
|
SELECT SUBSTRING('hello', "off", length) FROM strings ORDER BY ts;
|
|
|
|
+--------------------------------------------------+
|
|
| substr(Utf8("hello"),strings.off,strings.length) |
|
|
+--------------------------------------------------+
|
|
| he |
|
|
| ell |
|
|
| h |
|
|
| el |
|
|
+--------------------------------------------------+
|
|
|
|
-- Test with NULL values
|
|
SELECT SUBSTRING(NULL, "off", length) FROM strings ORDER BY ts;
|
|
|
|
+-----------------------------------------+
|
|
| substr(NULL,strings.off,strings.length) |
|
|
+-----------------------------------------+
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
+-----------------------------------------+
|
|
|
|
SELECT SUBSTRING(s, NULL, length) FROM strings ORDER BY ts;
|
|
|
|
+---------------------------------------+
|
|
| substr(strings.s,NULL,strings.length) |
|
|
+---------------------------------------+
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
+---------------------------------------+
|
|
|
|
SELECT SUBSTRING(s, "off", NULL) FROM strings ORDER BY ts;
|
|
|
|
+------------------------------------+
|
|
| substr(strings.s,strings.off,NULL) |
|
|
+------------------------------------+
|
|
| |
|
|
| |
|
|
| |
|
|
| |
|
|
+------------------------------------+
|
|
|
|
-- Test negative offsets
|
|
SELECT SUBSTRING('hello', -1, 3);
|
|
|
|
+------------------------------------------+
|
|
| substr(Utf8("hello"),Int64(-1),Int64(3)) |
|
|
+------------------------------------------+
|
|
| h |
|
|
+------------------------------------------+
|
|
|
|
SELECT SUBSTRING('hello', 0, 3);
|
|
|
|
+-----------------------------------------+
|
|
| substr(Utf8("hello"),Int64(0),Int64(3)) |
|
|
+-----------------------------------------+
|
|
| he |
|
|
+-----------------------------------------+
|
|
|
|
-- Test with Unicode characters
|
|
CREATE TABLE unicode_strings(s VARCHAR, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO unicode_strings VALUES
|
|
('Hello 世界', 1000),
|
|
('🦆🦀🐧', 2000),
|
|
('café', 3000);
|
|
|
|
Affected Rows: 3
|
|
|
|
SELECT s, SUBSTRING(s, 1, 5), SUBSTRING(s, 7, 2) FROM unicode_strings ORDER BY ts;
|
|
|
|
+------------+---------------------------------------------+---------------------------------------------+
|
|
| s | substr(unicode_strings.s,Int64(1),Int64(5)) | substr(unicode_strings.s,Int64(7),Int64(2)) |
|
|
+------------+---------------------------------------------+---------------------------------------------+
|
|
| Hello 世界 | Hello | 世界 |
|
|
| 🦆🦀🐧 | 🦆🦀🐧 | |
|
|
| café | café | |
|
|
+------------+---------------------------------------------+---------------------------------------------+
|
|
|
|
DROP TABLE strings;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE unicode_strings;
|
|
|
|
Affected Rows: 0
|
|
|