mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-08 22:32:55 +00:00
8a2371a05c
* feat: supports large string Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: add doc for extract_string_vector_values Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com> Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: refactor by cr comments Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: changes by cr comments Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * refactor: extract_string_vector_values Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * feat: remove large string type and refactor string vector Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: revert some changes Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * feat: adds large string type Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: impl default for StringSizeType Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: tests and test compatibility Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * test: update sqlness tests Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: remove panic Signed-off-by: Dennis Zhuang <killme2008@gmail.com> --------- Signed-off-by: Dennis Zhuang <killme2008@gmail.com> Co-authored-by: Copilot <175728472+Copilot@users.noreply.github.com>
148 lines
8.8 KiB
Plaintext
148 lines
8.8 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/aggregate/aggregates/test_string_agg.test
|
|
-- Test STRING_AGG operator
|
|
-- test string aggregation on scalar values
|
|
SELECT STRING_AGG('a',',');
|
|
|
|
+---------------------------------+
|
|
| string_agg(Utf8("a"),Utf8(",")) |
|
|
+---------------------------------+
|
|
| a |
|
|
+---------------------------------+
|
|
|
|
-- test string aggregation on scalar values with NULL
|
|
SELECT STRING_AGG('a',','), STRING_AGG(NULL,','), STRING_AGG('a', NULL), STRING_AGG(NULL,NULL);
|
|
|
|
+---------------------------------+----------------------------+----------------------------+-----------------------+
|
|
| string_agg(Utf8("a"),Utf8(",")) | string_agg(NULL,Utf8(",")) | string_agg(Utf8("a"),NULL) | string_agg(NULL,NULL) |
|
|
+---------------------------------+----------------------------+----------------------------+-----------------------+
|
|
| a | | a | |
|
|
+---------------------------------+----------------------------+----------------------------+-----------------------+
|
|
|
|
-- test string aggregation on a set of values
|
|
CREATE TABLE strings(g INTEGER, x VARCHAR, y VARCHAR, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO strings VALUES
|
|
(1,'a','/', 1000), (1,'b','-', 2000),
|
|
(2,'i','/', 3000), (2,NULL,'-', 4000), (2,'j','+', 5000),
|
|
(3,'p','/', 6000),
|
|
(4,'x','/', 7000), (4,'y','-', 8000), (4,'z','+', 9000);
|
|
|
|
Affected Rows: 9
|
|
|
|
SELECT g, STRING_AGG(x,'|') FROM strings GROUP BY g ORDER BY g;
|
|
|
|
+---+---------------------------------+
|
|
| g | string_agg(strings.x,Utf8("|")) |
|
|
+---+---------------------------------+
|
|
| 1 | a|b |
|
|
| 2 | i|j |
|
|
| 3 | p |
|
|
| 4 | x|y|z |
|
|
+---+---------------------------------+
|
|
|
|
-- test agg on empty set
|
|
SELECT STRING_AGG(x,',') FROM strings WHERE g > 100;
|
|
|
|
+---------------------------------+
|
|
| string_agg(strings.x,Utf8(",")) |
|
|
+---------------------------------+
|
|
| |
|
|
+---------------------------------+
|
|
|
|
-- string_agg can be used instead of group_concat
|
|
SELECT string_agg('a', ',');
|
|
|
|
+---------------------------------+
|
|
| string_agg(Utf8("a"),Utf8(",")) |
|
|
+---------------------------------+
|
|
| a |
|
|
+---------------------------------+
|
|
|
|
SELECT string_agg('a', ',');
|
|
|
|
+---------------------------------+
|
|
| string_agg(Utf8("a"),Utf8(",")) |
|
|
+---------------------------------+
|
|
| a |
|
|
+---------------------------------+
|
|
|
|
SELECT g, string_agg(x, ',') FROM strings GROUP BY g ORDER BY g;
|
|
|
|
+---+---------------------------------+
|
|
| g | string_agg(strings.x,Utf8(",")) |
|
|
+---+---------------------------------+
|
|
| 1 | a,b |
|
|
| 2 | i,j |
|
|
| 3 | p |
|
|
| 4 | x,y,z |
|
|
+---+---------------------------------+
|
|
|
|
-- Test ORDER BY
|
|
-- Single group
|
|
SELECT STRING_AGG(x, '' ORDER BY x ASC), STRING_AGG(x, '|' ORDER BY x ASC) FROM strings;
|
|
|
|
+--------------------------------------------------------------------+---------------------------------------------------------------------+
|
|
| string_agg(strings.x,Utf8("")) ORDER BY [strings.x ASC NULLS LAST] | string_agg(strings.x,Utf8("|")) ORDER BY [strings.x ASC NULLS LAST] |
|
|
+--------------------------------------------------------------------+---------------------------------------------------------------------+
|
|
| abijpxyz | a|b|i|j|p|x|y|z |
|
|
+--------------------------------------------------------------------+---------------------------------------------------------------------+
|
|
|
|
SELECT STRING_AGG(x, '' ORDER BY x DESC), STRING_AGG(x,'|' ORDER BY x DESC) FROM strings;
|
|
|
|
+----------------------------------------------------------------------+-----------------------------------------------------------------------+
|
|
| string_agg(strings.x,Utf8("")) ORDER BY [strings.x DESC NULLS FIRST] | string_agg(strings.x,Utf8("|")) ORDER BY [strings.x DESC NULLS FIRST] |
|
|
+----------------------------------------------------------------------+-----------------------------------------------------------------------+
|
|
| zyxpjiba | z|y|x|p|j|i|b|a |
|
|
+----------------------------------------------------------------------+-----------------------------------------------------------------------+
|
|
|
|
-- Grouped with ORDER BY
|
|
SELECT g, STRING_AGG(x, '' ORDER BY x ASC), STRING_AGG(x, '|' ORDER BY x ASC) FROM strings GROUP BY g ORDER BY g;
|
|
|
|
+---+--------------------------------------------------------------------+---------------------------------------------------------------------+
|
|
| g | string_agg(strings.x,Utf8("")) ORDER BY [strings.x ASC NULLS LAST] | string_agg(strings.x,Utf8("|")) ORDER BY [strings.x ASC NULLS LAST] |
|
|
+---+--------------------------------------------------------------------+---------------------------------------------------------------------+
|
|
| 1 | ab | a|b |
|
|
| 2 | ij | i|j |
|
|
| 3 | p | p |
|
|
| 4 | xyz | x|y|z |
|
|
+---+--------------------------------------------------------------------+---------------------------------------------------------------------+
|
|
|
|
SELECT g, STRING_AGG(x, '' ORDER BY x DESC), STRING_AGG(x,'|' ORDER BY x DESC) FROM strings GROUP BY g ORDER BY g;
|
|
|
|
+---+----------------------------------------------------------------------+-----------------------------------------------------------------------+
|
|
| g | string_agg(strings.x,Utf8("")) ORDER BY [strings.x DESC NULLS FIRST] | string_agg(strings.x,Utf8("|")) ORDER BY [strings.x DESC NULLS FIRST] |
|
|
+---+----------------------------------------------------------------------+-----------------------------------------------------------------------+
|
|
| 1 | ba | b|a |
|
|
| 2 | ji | j|i |
|
|
| 3 | p | p |
|
|
| 4 | zyx | z|y|x |
|
|
+---+----------------------------------------------------------------------+-----------------------------------------------------------------------+
|
|
|
|
-- Test with DISTINCT
|
|
SELECT STRING_AGG(DISTINCT x, '' ORDER BY x), STRING_AGG(DISTINCT x, '|' ORDER BY x) FROM strings;
|
|
|
|
+-----------------------------------------------------------------------------+------------------------------------------------------------------------------+
|
|
| string_agg(DISTINCT strings.x,Utf8("")) ORDER BY [strings.x ASC NULLS LAST] | string_agg(DISTINCT strings.x,Utf8("|")) ORDER BY [strings.x ASC NULLS LAST] |
|
|
+-----------------------------------------------------------------------------+------------------------------------------------------------------------------+
|
|
| abijpxyz | a|b|i|j|p|x|y|z |
|
|
+-----------------------------------------------------------------------------+------------------------------------------------------------------------------+
|
|
|
|
SELECT g, STRING_AGG(DISTINCT x, '' ORDER BY x) FROM strings GROUP BY g ORDER BY g;
|
|
|
|
+---+-----------------------------------------------------------------------------+
|
|
| g | string_agg(DISTINCT strings.x,Utf8("")) ORDER BY [strings.x ASC NULLS LAST] |
|
|
+---+-----------------------------------------------------------------------------+
|
|
| 1 | ab |
|
|
| 2 | ij |
|
|
| 3 | p |
|
|
| 4 | xyz |
|
|
+---+-----------------------------------------------------------------------------+
|
|
|
|
-- cleanup
|
|
DROP TABLE strings;
|
|
|
|
Affected Rows: 0
|
|
|