modules/sql-engine/src/integrationTest/sql/group1/syscolumns/system_columns.test

# name: test/sql/basic_queries/system_columns.test
# description:  Test Ignite 3 system columns.
# group: [basic_queries]

statement ok
CREATE TABLE T0( ID BIGINT PRIMARY KEY, VAL VARCHAR );

# Add
statement ok
insert into t0 values (101, 'val1'), (102, 'val2');

statement ok
insert into t0 values (103, 'val3');

# statement error: Failed to validate query:
statement error
----
insert into t0 (id, val, __part) values (104, 'val4', 1)

# statement error: Failed to validate query:
statement error
----
insert into t0 (id, val, "__part") values (104, 'val4', 1)

# statement error: Failed to validate query:
statement error
----
insert into t0 (id, val, __partition_id) values (104, 'val4', 1)

# Select partition system column
query IT rowsort
select * from t0
----
101	val1
102	val2
103	val3

query I rowsort
select __partition_id from t0
----
19
17
11

query II rowsort
select __partition_id, id from t0
----
19	101
17	102
11	103

# Select legacy partition system column
query III rowsort
select __partition_id, __part, "__part" from t0
----
19	19	19
17	17	17
11	11	11

query IIII rowsort
select __partition_id, __part, "__part", id from t0
----
19	19	19	101
17	17	17	102
11	11	11	103

# Partition system column in WHERE
query IT
select * from t0 where __partition_id = 17
----
102	val2

query IT rowsort
select * from t0 where __partition_id IN (11, 19)
----
101	val1
103	val3