Evgeny Stanilovsky created IGNITE-16580:
-------------------------------------------
Summary: Calcite. Merge returns unexpected results.
Key: IGNITE-16580
URL: https://issues.apache.org/jira/browse/IGNITE-16580
Project: Ignite
Issue Type: Improvement
Components: sql
Reporter: Evgeny Stanilovsky
{noformat}
@Test
public void testMerge0() {
sql("CREATE TABLE test1 (k1 int, k2 int, a int, b varchar, c varchar,
CONSTRAINT PK PRIMARY KEY (k1, k2))");
sql("INSERT INTO test1 VALUES (111, 111, 0, 'a', '0')");
sql("INSERT INTO test1 VALUES (222, 222, 1, 'b', '1')");
sql("CREATE TABLE test2 (k1 int, k2 int, a int, b varchar, c varchar,
CONSTRAINT PK PRIMARY KEY (k1, k2))");
sql("INSERT INTO test2 VALUES (333, 333, 0, '100', '')");
sql("INSERT INTO test2 VALUES (444, 444, 2, '200', '')");
String sql = "MERGE INTO test2 dst USING test1 src ON dst.a = src.a " +
"WHEN MATCHED THEN UPDATE SET b = src.b " +
"WHEN NOT MATCHED THEN INSERT (a, b) VALUES (src.a, src.b)";
assertQuery(sql).matches(QueryChecker.containsSubPlan("IgniteTableSpool")).check();
assertQuery("SELECT * FROM test2 ORDER BY k1")
.returns(222, 222, 1, "b", "1")
.returns(333, 333, 0, "a", "")
.returns(444, 444, 2, "200", "")
.check();
}
{noformat}
Caused by: java.lang.ClassNotFoundException:
SQL_PUBLIC_TEST1_3be0511e_d245_4726_b535_0514ab769875_KEY
{noformat}
public void testMerge1() {
sql("CREATE TABLE test1 (a int, b varchar, c varchar)");
sql("INSERT INTO test1 VALUES (0, 'a', '0')");
sql("INSERT INTO test1 VALUES (1, 'b', '1')");
sql("CREATE TABLE test2 (a int, b varchar, c varchar)");
sql("INSERT INTO test2 VALUES (0, '100', '')");
sql("INSERT INTO test2 VALUES (2, '200', '')");
String sql = "MERGE INTO test2 dst USING test1 src ON dst.a = src.a " +
"WHEN MATCHED THEN UPDATE SET b = src.b " +
"WHEN NOT MATCHED THEN INSERT (a, b) VALUES (src.a, src.b)";
assertQuery(sql).matches(QueryChecker.containsSubPlan("IgniteTableSpool")).check();
assertQuery("SELECT * FROM test2")
.returns(1, "b", "1")
.returns(0, "a", "")
.returns(2, "200", "")
.check();
}
{noformat}
Expected: [[0, a, ], [1, b, 1], [2, 200, ]]
Actual: [[0, a, ], [1, b, null], [2, 200, ]]
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
