struct.slt

# 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.

#############
## Struct Expressions Tests
#############

statement ok
CREATE TABLE values(
    a INT,
    b FLOAT,
    c VARCHAR,
    n VARCHAR
) AS VALUES
  (1, 1.1, 'a', NULL),
  (2, 2.2, 'b', NULL),
  (3, 3.3, 'c', NULL)
;


# named and named less struct fields
statement ok
CREATE TABLE struct_values (
    s1 struct<INT>,
    s2 struct<a INT,b VARCHAR>
) AS VALUES
  (struct(1), struct(1, 'string1')),
  (struct(2), struct(2, 'string2')),
  (struct(3), struct(3, 'string3'))
;

query ??
select * from struct_values;
----
{c0: 1} {a: 1, b: string1}
{c0: 2} {a: 2, b: string2}
{c0: 3} {a: 3, b: string3}

query TT
select arrow_typeof(s1), arrow_typeof(s2) from struct_values;
----
Struct([Field { name: "c0", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]) Struct([Field { name: "a", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])
Struct([Field { name: "c0", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]) Struct([Field { name: "a", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])
Struct([Field { name: "c0", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]) Struct([Field { name: "a", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])


# struct[i]
query IRT
select struct(1, 3.14, 'h')['c0'], struct(3, 2.55, 'b')['c1'], struct(2, 6.43, 'a')['c2'];
----
1 2.55 a

# struct[i] with columns
query R
select struct(a, b, c)['c1'] from values;
----
1.1
2.2
3.3

# explicit invocation of get_field
query R
select get_field(struct(a, b, c), 'c1') from values;
----
1.1
2.2
3.3

# struct scalar function #1
query ?
select struct(1, 3.14, 'e');
----
{c0: 1, c1: 3.14, c2: e}

# struct scalar function with named values
query ?
select struct(1 as "name0", 3.14 as name1, 'e', true as 'name3');
----
{name0: 1, name1: 3.14, c2: e, name3: true}

# struct scalar function with mixed named and unnamed values
query ?
select struct(1, 3.14 as name1, 'e', true);
----
{c0: 1, name1: 3.14, c2: e, c3: true}

# struct scalar function with columns #1
query ?
select struct(a, b, c) from values;
----
{c0: 1, c1: 1.1, c2: a}
{c0: 2, c1: 2.2, c2: b}
{c0: 3, c1: 3.3, c2: c}

# struct scalar function with columns and scalars
query ?
select struct(a, 'foo') from values;
----
{c0: 1, c1: foo}
{c0: 2, c1: foo}
{c0: 3, c1: foo}


# explain struct scalar function with columns #1
query TT
explain select struct(a, b, c) from values;
----
logical_plan
01)Projection: struct(values.a, values.b, values.c)
02)--TableScan: values projection=[a, b, c]
physical_plan
01)ProjectionExec: expr=[struct(a@0, b@1, c@2) as struct(values.a,values.b,values.c)]
02)--MemoryExec: partitions=1, partition_sizes=[1]

# error on 0 arguments
query error
select named_struct();

# error on duplicate field names
query error
select named_struct('c0': 1, 'c1': 2, 'c1': 3);

# error on odd number of arguments #1
query error DataFusion error: Execution error: named_struct requires an even number of arguments, got 1 instead
select named_struct('a');

# error on odd number of arguments #2
query error DataFusion error: Execution error: named_struct requires an even number of arguments, got 1 instead
select named_struct(1);

# error on odd number of arguments #3
query error DataFusion error: Execution error: named_struct requires an even number of arguments, got 1 instead
select named_struct(values.a) from values;

# error on odd number of arguments #4
query error DataFusion error: Execution error: named_struct requires an even number of arguments, got 3 instead
select named_struct('a', 1, 'b');

# error on even argument not a string literal #1
query error DataFusion error: Execution error: named_struct requires 0\-th \(0\-indexed\) field name as non\-empty constant string
select named_struct(1, 'a');

# error on even argument not a string literal #2
query error DataFusion error: Execution error: named_struct requires 2\-th \(0\-indexed\) field name as non\-empty constant string
select named_struct('corret', 1, 0, 'wrong');

# error on even argument not a string literal #3
query error DataFusion error: Execution error: named_struct requires 0\-th \(0\-indexed\) field name as non\-empty constant string
select named_struct(values.a, 'a') from values;

# error on even argument not a string literal #4
query error DataFusion error: Execution error: named_struct requires 0\-th \(0\-indexed\) field name as non\-empty constant string
select named_struct(values.c, 'c') from values;

# named_struct with mixed scalar and array values #1
query ?
select named_struct('scalar', 27, 'array', values.a, 'null', NULL) from values;
----
{scalar: 27, array: 1, null: NULL}
{scalar: 27, array: 2, null: NULL}
{scalar: 27, array: 3, null: NULL}

query ?
select {'scalar': 27, 'array': values.a, 'null': NULL} from values;
----
{scalar: 27, array: 1, null: NULL}
{scalar: 27, array: 2, null: NULL}
{scalar: 27, array: 3, null: NULL}

# named_struct with mixed scalar and array values #2
query ?
select named_struct('array', values.a, 'scalar', 27, 'null', NULL) from values;
----
{array: 1, scalar: 27, null: NULL}
{array: 2, scalar: 27, null: NULL}
{array: 3, scalar: 27, null: NULL}

query ?
select {'array': values.a, 'scalar': 27, 'null': NULL} from values;
----
{array: 1, scalar: 27, null: NULL}
{array: 2, scalar: 27, null: NULL}
{array: 3, scalar: 27, null: NULL}

# named_struct with mixed scalar and array values #3
query ?
select named_struct('null', NULL, 'array', values.a, 'scalar', 27) from values;
----
{null: NULL, array: 1, scalar: 27}
{null: NULL, array: 2, scalar: 27}
{null: NULL, array: 3, scalar: 27}

# named_struct with mixed scalar and array values #4
query ?
select named_struct('null_array', values.n, 'array', values.a, 'scalar', 27, 'null', NULL) from values;
----
{null_array: NULL, array: 1, scalar: 27, null: NULL}
{null_array: NULL, array: 2, scalar: 27, null: NULL}
{null_array: NULL, array: 3, scalar: 27, null: NULL}

# named_struct arrays only
query ?
select named_struct('field_a', a, 'field_b', b) from values;
----
{field_a: 1, field_b: 1.1}
{field_a: 2, field_b: 2.2}
{field_a: 3, field_b: 3.3}

# named_struct scalars only
query ?
select named_struct('field_a', 1, 'field_b', 2);
----
{field_a: 1, field_b: 2}

query T
select arrow_typeof(named_struct('first', 1, 'second', 2, 'third', 3));
----
Struct([Field { name: "first", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "second", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "third", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])

query T
select arrow_typeof({'first': 1, 'second': 2, 'third': 3});
----
Struct([Field { name: "first", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "second", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "third", data_type: Int64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])

# test nested struct literal
query ?
select {'animal': {'cat': 1, 'dog': 2, 'bird': {'parrot': 3, 'canary': 1}}, 'genre': {'fiction': ['mystery', 'sci-fi', 'fantasy'], 'non-fiction': {'biography': 5, 'history': 7, 'science': {'physics': 2, 'biology': 3}}}, 'vehicle': {'car': {'sedan': 4, 'suv': 2}, 'bicycle': 3, 'boat': ['sailboat', 'motorboat']}, 'weather': {'sunny': True, 'temperature': 25.5, 'wind': {'speed': 10, 'direction': 'NW'}}};
----
{animal: {cat: 1, dog: 2, bird: {parrot: 3, canary: 1}}, genre: {fiction: [mystery, sci-fi, fantasy], non-fiction: {biography: 5, history: 7, science: {physics: 2, biology: 3}}}, vehicle: {car: {sedan: 4, suv: 2}, bicycle: 3, boat: [sailboat, motorboat]}, weather: {sunny: true, temperature: 25.5, wind: {speed: 10, direction: NW}}}

# test tuple as struct
query B
select ('x', 'y') = ('x', 'y');
----
true

query B
select ('x', 'y') = ('y', 'x');
----
false

query error DataFusion error: Error during planning: Cannot infer common argument type for comparison operation Struct.*
select ('x', 'y') = ('x', 'y', 'z');

query B
select ('x', 'y') IN (('x', 'y'));
----
true

query B
select ('x', 'y') IN (('x', 'y'), ('y', 'x'));
----
true

query I
select a from values where (a, c) = (1, 'a');
----
1

query I
select a from values where (a, c) IN ((1, 'a'), (2, 'b'));
----
1
2

statement ok
drop table values;

statement ok
drop table struct_values;

statement ok
CREATE OR REPLACE VIEW complex_view AS 
SELECT {
    'user': {
        'info': {
            'personal': {
                'name': 'John Doe',
                'age': 30,
                'email': 'john.doe@example.com'
            },
            'address': {
                'street': '123 Main St',
                'city': 'Anytown',
                'country': 'Countryland',
                'coordinates': [40.7128, -74.0060]
            }
        },
        'preferences': {
            'theme': 'dark',
            'notifications': true,
            'languages': ['en', 'es', 'fr']
        },
        'stats': {
            'logins': 42,
            'last_active': '2023-09-15',
            'scores': [85, 92, 78, 95],
            'achievements': {
                'badges': ['early_bird', 'top_contributor'],
                'levels': {
                    'beginner': true,
                    'intermediate': true,
                    'advanced': false
                }
            }
        }
    },
    'metadata': {
        'version': '1.0',
        'created_at': '2023-09-01T12:00:00Z'
    },
    'deep_nested': {
        'level1': {
            'level2': {
                'level3': {
                    'level4': {
                        'level5': {
                            'level6': {
                                'level7': {
                                    'level8': {
                                        'level9': {
                                            'level10': 'You reached the bottom!'
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
} AS complex_data;

query T
SELECT complex_data.user.info.personal.name FROM complex_view;
----
John Doe

query I
SELECT complex_data.user.info.personal.age FROM complex_view;
----
30

query T
SELECT complex_data.user.info.address.city FROM complex_view;
----
Anytown

query T
SELECT complex_data.user.preferences.languages[2] FROM complex_view;
----
es

query T
SELECT complex_data.deep_nested.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10 FROM complex_view;
----
You reached the bottom!

statement ok
drop view complex_view;

# struct with different keys r1 and r2 is not valid
statement ok
create table t(a struct<r1 varchar, c int>, b struct<r2 varchar, c float>) as values (struct('red', 1), struct('blue', 2.3));

# Expect same keys for struct type but got mismatched pair r1,c and r2,c
query error
select [a, b] from t;

statement ok
drop table t;

# struct with the same key
statement ok
create table t(a struct<r varchar, c int>, b struct<r varchar, c float>) as values (struct('red', 1), struct('blue', 2.3));

query T
select arrow_typeof([a, b]) from t;
----
List(Field { name: "item", data_type: Struct([Field { name: "r", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "c", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} })

query ?
select [a, b] from t;
----
[{r: red, c: 1.0}, {r: blue, c: 2.3}]

statement ok
drop table t;

# Test row alias

query ?
select row('a', 'b');
----
{c0: a, c1: b}

##################################
# Switch Dialect to DuckDB
##################################

statement ok
set datafusion.sql_parser.dialect = 'DuckDB';

statement ok
CREATE TABLE struct_values (
    s1 struct(a int, b varchar),
    s2 struct(a int, b varchar)
) AS VALUES
  (row(1, 'red'), row(1, 'string1')),
  (row(2, 'blue'), row(2, 'string2')),
  (row(3, 'green'), row(3, 'string3'))
;

statement ok
drop table struct_values;

statement ok
create table t (c1 struct(r varchar, b int), c2 struct(r varchar, b float)) as values (
    row('red', 2),
    row('blue', 2.3)
);

query ??
select * from t;
----
{r: red, b: 2} {r: blue, b: 2.3}

query T
select arrow_typeof(c1) from t;
----
Struct([Field { name: "r", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])

query T
select arrow_typeof(c2) from t;
----
Struct([Field { name: "r", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])

statement ok
drop table t;

statement ok
create table t as values({r: 'a', c: 1}), ({r: 'b', c: 2.3});

query ?
select * from t;
----
{c0: a, c1: 1.0}
{c0: b, c1: 2.3}

query T
select arrow_typeof(column1) from t;
----
Struct([Field { name: "c0", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "c1", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])
Struct([Field { name: "c0", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "c1", data_type: Float64, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])

statement ok
drop table t;

query error DataFusion error: Arrow error: Cast error: Cannot cast string 'a' to value of Float64 type
create table t as values({r: 'a', c: 1}), ({c: 2.3, r: 'b'});

##################################
## Test Coalesce with Struct
##################################

statement ok
CREATE TABLE t (
    s1 struct(a int, b varchar),
    s2 struct(a float, b varchar)
) AS VALUES
  (row(1, 'red'), row(1.1, 'string1')),
  (row(2, 'blue'), row(2.2, 'string2')),
  (row(3, 'green'), row(33.2, 'string3'))
;

query ?
select coalesce(s1) from t;
----
{a: 1, b: red}
{a: 2, b: blue}
{a: 3, b: green}

query T
select arrow_typeof(coalesce(s1, s2)) from t;
----
Struct([Field { name: "a", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])
Struct([Field { name: "a", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])
Struct([Field { name: "a", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])

statement ok
drop table t;

statement ok
CREATE TABLE t (
    s1 struct(a int, b varchar),
    s2 struct(a float, b varchar)
) AS VALUES
  (row(1, 'red'), row(1.1, 'string1')),
  (null, row(2.2, 'string2')),
  (row(3, 'green'), row(33.2, 'string3'))
;

query ?
select coalesce(s1, s2) from t;
----
{a: 1.0, b: red}
{a: 2.2, b: string2}
{a: 3.0, b: green}

query T
select arrow_typeof(coalesce(s1, s2)) from t;
----
Struct([Field { name: "a", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])
Struct([Field { name: "a", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])
Struct([Field { name: "a", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "b", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])

statement ok
drop table t;

# row() with incorrect order
statement error DataFusion error: Arrow error: Cast error: Cannot cast string 'blue' to value of Float32 type
create table t(a struct(r varchar, c int), b struct(r varchar, c float)) as values 
    (row('red', 1), row(2.3, 'blue')),
    (row('purple', 1), row('green', 2.3));

# out of order struct literal
# TODO: This query should not fail
statement error DataFusion error: Arrow error: Cast error: Cannot cast string 'b' to value of Int32 type
create table t(a struct(r varchar, c int)) as values ({r: 'a', c: 1}), ({c: 2, r: 'b'});

##################################
## Test Array of Struct
##################################

query ?
select [{r: 'a', c: 1}, {r: 'b', c: 2}];
----
[{r: a, c: 1}, {r: b, c: 2}]

# Can't create a list of struct with different field types
query error
select [{r: 'a', c: 1}, {c: 2, r: 'b'}];

statement ok
create table t(a struct(r varchar, c int), b struct(r varchar, c float)) as values (row('a', 1), row('b', 2.3));

query T
select arrow_typeof([a, b]) from t;
----
List(Field { name: "item", data_type: Struct([Field { name: "r", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "c", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }]), nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} })

statement ok
drop table t;

# create table with different struct type is fine
statement ok
create table t(a struct(r varchar, c int), b struct(c float, r varchar)) as values (row('a', 1), row(2.3, 'b'));

# create array with different struct type is not valid
query error
select arrow_typeof([a, b]) from t;

statement ok
drop table t;

statement ok
create table t(a struct(r varchar, c int, g float), b struct(r varchar, c float, g int)) as values (row('a', 1, 2.3), row('b', 2.3, 2));

# type of each column should not coerced but perserve as it is
query T
select arrow_typeof(a) from t;
----
Struct([Field { name: "r", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "c", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "g", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])

# type of each column should not coerced but perserve as it is
query T
select arrow_typeof(b) from t;
----
Struct([Field { name: "r", data_type: Utf8, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "c", data_type: Float32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }, Field { name: "g", data_type: Int32, nullable: true, dict_id: 0, dict_is_ordered: false, metadata: {} }])

statement ok
drop table t;