mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-13 16:52:56 +00:00
86 lines
7.8 KiB
Plaintext
86 lines
7.8 KiB
Plaintext
-- Licensed to the Apache Software Foundation (ASF) under one
|
|
-- or more contributor license agreements. See the NOTICE file
|
|
-- distributed with this work for additional information
|
|
-- regarding copyright ownership. The ASF licenses this file
|
|
-- to you under the Apache License, Version 2.0 (the
|
|
-- "License"); you may not use this file except in compliance
|
|
-- with the License. You may obtain a copy of the License at
|
|
--
|
|
-- http://www.apache.org/licenses/LICENSE-2.0
|
|
--
|
|
-- Unless required by applicable law or agreed to in writing,
|
|
-- software distributed under the License is distributed on an
|
|
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
|
|
-- KIND, either express or implied. See the License for the
|
|
-- specific language governing permissions and limitations
|
|
-- under the License.
|
|
--
|
|
-- Unnest tests from Apache DataFusion
|
|
-- Basic unnest tests
|
|
SELECT unnest([1,2,3]);
|
|
|
|
+------------------------------------------------+
|
|
| UNNEST(make_array(Int64(1),Int64(2),Int64(3))) |
|
|
+------------------------------------------------+
|
|
| 1 |
|
|
| 2 |
|
|
| 3 |
|
|
+------------------------------------------------+
|
|
|
|
SELECT unnest(struct(1,2,3));
|
|
|
|
+-------------------------------------------------------------+-------------------------------------------------------------+-------------------------------------------------------------+
|
|
| __unnest_placeholder(struct(Int64(1),Int64(2),Int64(3))).c0 | __unnest_placeholder(struct(Int64(1),Int64(2),Int64(3))).c1 | __unnest_placeholder(struct(Int64(1),Int64(2),Int64(3))).c2 |
|
|
+-------------------------------------------------------------+-------------------------------------------------------------+-------------------------------------------------------------+
|
|
| 1 | 2 | 3 |
|
|
+-------------------------------------------------------------+-------------------------------------------------------------+-------------------------------------------------------------+
|
|
|
|
-- Table function is not supported for now
|
|
-- SELECT * FROM unnest([1,2,3]);
|
|
-- SELECT * FROM unnest(struct(1,2,3));
|
|
-- SELECT * FROM unnest(struct(1,2,3)), unnest([4,5,6]);
|
|
-- Multiple unnest levels
|
|
SELECT unnest([1,2,3]), unnest(unnest([[1,2,3]]));
|
|
|
|
+------------------------------------------------+--------------------------------------------------------------------+
|
|
| UNNEST(make_array(Int64(1),Int64(2),Int64(3))) | UNNEST(UNNEST(make_array(make_array(Int64(1),Int64(2),Int64(3))))) |
|
|
+------------------------------------------------+--------------------------------------------------------------------+
|
|
| 1 | 1 |
|
|
| 2 | 2 |
|
|
| 3 | 3 |
|
|
+------------------------------------------------+--------------------------------------------------------------------+
|
|
|
|
SELECT unnest([1,2,3]) + unnest([1,2,3]), unnest([1,2,3]) + unnest([4,5]);
|
|
|
|
+-------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------+
|
|
| UNNEST(make_array(Int64(1),Int64(2),Int64(3))) + UNNEST(make_array(Int64(1),Int64(2),Int64(3))) | UNNEST(make_array(Int64(1),Int64(2),Int64(3))) + UNNEST(make_array(Int64(4),Int64(5))) |
|
|
+-------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------+
|
|
| 2 | 5 |
|
|
| 4 | 7 |
|
|
| 6 | |
|
|
+-------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------+
|
|
|
|
SELECT unnest(unnest([[1,2,3]])) + unnest(unnest([[1,2,3]])),
|
|
unnest(unnest([[1,2,3]])) + unnest([4,5]),
|
|
unnest([4,5]);
|
|
|
|
+-----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+---------------------------------------+
|
|
| UNNEST(UNNEST(make_array(make_array(Int64(1),Int64(2),Int64(3))))) + UNNEST(UNNEST(make_array(make_array(Int64(1),Int64(2),Int64(3))))) | UNNEST(UNNEST(make_array(make_array(Int64(1),Int64(2),Int64(3))))) + UNNEST(make_array(Int64(4),Int64(5))) | UNNEST(make_array(Int64(4),Int64(5))) |
|
|
+-----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+---------------------------------------+
|
|
| 2 | 5 | 4 |
|
|
| 4 | 7 | 5 |
|
|
| 6 | | |
|
|
+-----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------+---------------------------------------+
|
|
|
|
-- Unnest with expressions
|
|
SELECT unnest([1,2,3]) + 1, unnest(unnest([[1,2,3]]))* 2;
|
|
|
|
+-----------------------------------------------------------+-------------------------------------------------------------------------------+
|
|
| UNNEST(make_array(Int64(1),Int64(2),Int64(3))) + Int64(1) | UNNEST(UNNEST(make_array(make_array(Int64(1),Int64(2),Int64(3))))) * Int64(2) |
|
|
+-----------------------------------------------------------+-------------------------------------------------------------------------------+
|
|
| 2 | 2 |
|
|
| 3 | 4 |
|
|
| 4 | 6 |
|
|
+-----------------------------------------------------------+-------------------------------------------------------------------------------+
|
|
|