This is an automated email from the ASF dual-hosted git repository.
jooger pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new 7198bf0226 IGNITE-21979: Extend test coverage for SQL
F781(Self-referencing operations) (#3759)
7198bf0226 is described below
commit 7198bf0226fd6a04ab4b6d661dfdb15f43bfb650
Author: Max Zhuravkov <[email protected]>
AuthorDate: Fri May 17 13:06:53 2024 +0300
IGNITE-21979: Extend test coverage for SQL F781(Self-referencing
operations) (#3759)
---
.../sql/dml/test_self_referencing.test | 107 +++++++++++++++++++++
1 file changed, 107 insertions(+)
diff --git
a/modules/sql-engine/src/integrationTest/sql/dml/test_self_referencing.test
b/modules/sql-engine/src/integrationTest/sql/dml/test_self_referencing.test
new file mode 100644
index 0000000000..ab3e75ddce
--- /dev/null
+++ b/modules/sql-engine/src/integrationTest/sql/dml/test_self_referencing.test
@@ -0,0 +1,107 @@
+# Test for self-referencing table without using Feature F781
+statement ok
+CREATE TABLE test (a INTEGER, val VARCHAR)
+
+statement ok
+INSERT INTO test VALUES (1, 'A'), (2, 'B')
+
+# INSERT
+
+statement ok
+INSERT INTO test (a, val) SELECT a, 'C' FROM test WHERE a IN (SELECT a FROM
test WHERE val = 'A')
+
+query IT rowsort
+SELECT a, val FROM test
+----
+1 A
+1 C
+2 B
+
+statement ok
+INSERT INTO test (val, a) VALUES ('D', (SELECT COUNT(*) FROM test))
+
+query IT rowsort
+SELECT a, val FROM test
+----
+1 A
+1 C
+2 B
+3 D
+
+# Clean up
+
+statement ok
+DELETE FROM test
+
+statement ok
+INSERT INTO test VALUES (1, 'A'), (2, 'B')
+
+# UPDATE
+
+statement ok
+UPDATE test SET a = 11 WHERE a IN (SELECT a FROM test WHERE val = 'A')
+
+query IT rowsort
+SELECT a, val FROM test
+----
+11 A
+2 B
+
+statement ok
+UPDATE test SET a = (SELECT a + 1 FROM test WHERE val = 'A')
+
+query IT rowsort
+SELECT a, val FROM test
+----
+12 A
+12 B
+
+# Clean up
+
+statement ok
+DELETE FROM test
+
+statement ok
+INSERT INTO test VALUES (1, 'A'), (2, 'B')
+
+# MERGE
+
+statement ok
+MERGE INTO test dst USING test src ON dst.a = src.a WHEN MATCHED THEN UPDATE
SET val = src.val || dst.val
+
+query IT rowsort
+SELECT a, val FROM test
+----
+1 AA
+2 BB
+
+statement ok
+MERGE INTO test dst USING test src ON dst.a = src.a + 1 WHEN NOT MATCHED THEN
INSERT (a, val) VALUES (3, 'CC')
+
+query IT rowsort
+SELECT a, val FROM test
+----
+1 AA
+2 BB
+3 CC
+
+statement ok
+MERGE INTO test dst USING (SELECT * FROM test WHERE val = 'AA') src ON dst.a =
src.a WHEN MATCHED THEN UPDATE SET val = src.val || dst.val
+
+query IT rowsort
+SELECT a, val FROM test
+----
+1 AAAA
+2 BB
+3 CC
+
+# DELETE
+
+statement ok
+DELETE FROM test WHERE a IN (SELECT a FROM test WHERE val = 'AAAA')
+
+query IT rowsort
+SELECT a, val FROM test
+----
+2 BB
+3 CC
