[
https://issues.apache.org/jira/browse/IGNITE-20835?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Maksim Zhuravkov updated IGNITE-20835:
--------------------------------------
Description:
Update statements incorrectly use scalar subqueries in target columns, the
issues stems from the fact that scalar subquery in UPDATE are not wrapped in
`SINGLE_VAL` aggregate.
Examples:
{code:java}
@Test
public void ddml2() {
sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
INTEGER);");
sql("INSERT INTO vals VALUES (1, 0);");
sql("INSERT INTO vals VALUES (2, 0);");
sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
// works
sql("INSERT INTO other_vals VALUES (3, (SELECT other_val FROM
other_vals WHERE id = 1));");
// works
sql("UPDATE vals SET val = (SELECT other_val FROM other_vals LIMIT 1)
WHERE id = 1");
// Should fail, because a subquery returns more than 1 result.
sql("UPDATE vals SET val = (SELECT other_val FROM other_vals) WHERE id
>= 2");
}
UPDATEs in MERGE statement are also affect by this issue:
{code:java}
@Test
public void ddml2_1() {
sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
INTEGER);");
sql("INSERT INTO vals VALUES (1, 0);");
sql("INSERT INTO vals VALUES (2, 0);");
sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
// works
sql("INSERT INTO other_vals VALUES (3, (SELECT other_val FROM
other_vals WHERE id = 1));");
// Fails with PK unique constraint is violated
sql("MERGE INTO vals USING other_vals ON vals.id = other_vals.id "
+ "WHEN MATCHED THEN UPDATE SET val = (SELECT other_val FROM
other_vals WHERE id > 1) "
+ "WHEN NOT MATCHED THEN INSERT (id, val) VALUES (100, -1)");
}
{code}
{code}
If we change val type to BIGINT we get another error:
{code:java}
@Test
public void ddml3() {
sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
BIGINT);");
sql("INSERT INTO vals VALUES (1, 0);");
sql("INSERT INTO vals VALUES (2, 0);");
sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
// works
sql("INSERT INTO other_vals VALUES (3, (SELECT other_val FROM
other_vals WHERE id = 1));");
// works
sql("UPDATE vals SET val = (SELECT other_val FROM other_vals LIMIT 1)
WHERE id = 1");
// Error
sql("UPDATE vals SET val = (SELECT other_val FROM other_vals) WHERE id
>= 2");
}
{code}
Error:
{code:java}
Caused by: java.lang.AssertionError
at
org.apache.calcite.sql.validate.implicit.AbstractTypeCoercion.needToCast(AbstractTypeCoercion.java:297)
at
org.apache.calcite.sql.validate.implicit.AbstractTypeCoercion.needToCast(AbstractTypeCoercion.java:250)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.needToCast(IgniteTypeCoercion.java:294)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.doCoerceColumnType(IgniteTypeCoercion.java:453)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.coerceColumnType(IgniteTypeCoercion.java:360)
at
org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.coerceSourceRowType(TypeCoercionImpl.java:676)
at
org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.querySourceCoercion(TypeCoercionImpl.java:646)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.querySourceCoercion(IgniteTypeCoercion.java:241)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.checkTypeAssignment(SqlValidatorImpl.java:5112)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateUpdate(SqlValidatorImpl.java:5219)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validateUpdate(IgniteSqlValidator.java:187)
at org.apache.calcite.sql.SqlUpdate.validate(SqlUpdate.java:190)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1090)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:796)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validate(IgniteSqlValidator.java:161)
at
org.apache.ignite.internal.sql.engine.prepare.IgnitePlanner.validate(IgnitePlanner.java:204)
{code}
Insert statement behaves correctly:
{code:java}
@Test
public void ddml0() {
sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val INTEGER);");
sql("INSERT INTO vals VALUES (1, 0);");
sql("INSERT INTO vals VALUES (2, 0);");
sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
// Fails with `Subquery returned more than 1 value`
sql("INSERT INTO vals VALUES (3, (SELECT other_val FROM other_vals WHERE id
> 0));");
}
{code}
was:
Update statements incorrectly use scalar subqueries in target columns, the
issues stems from the fact that scalar subquery in UPDATE are not wrapped in
`SINGLE_VAL` aggregate.
Examples:
{code:java}
@Test
public void ddml2() {
sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
INTEGER);");
sql("INSERT INTO vals VALUES (1, 0);");
sql("INSERT INTO vals VALUES (2, 0);");
sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
// works
sql("INSERT INTO other_vals VALUES (3, (SELECT other_val FROM
other_vals WHERE id = 1));");
// works
sql("UPDATE vals SET val = (SELECT other_val FROM other_vals LIMIT 1)
WHERE id = 1");
// Should fail, because a subquery returns more than 1 result.
sql("UPDATE vals SET val = (SELECT other_val FROM other_vals) WHERE id
>= 2");
}
UPDATEs in MERGE statement are also affect by this issue:
{code:java}
@Test
public void ddml2_1() {
sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
INTEGER);");
sql("INSERT INTO vals VALUES (1, 0);");
sql("INSERT INTO vals VALUES (2, 0);");
sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
// works
sql("INSERT INTO other_vals VALUES (3, (SELECT other_val FROM
other_vals WHERE id = 1));");
// Fails with PK unique constraint is violated
sql("MERGE INTO vals USING other_vals ON vals.id = other_vals.id "
+ "WHEN MATCHED THEN UPDATE SET val = (SELECT other_val FROM
other_vals WHERE id > 1) "
+ "WHEN NOT MATCHED THEN INSERT (id, val) VALUES (100, -1)");
}
{code}
{code}
If we change val type to BIGINT we get another error:
{code:java}
@Test
public void ddml3() {
sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
BIGINT);");
sql("INSERT INTO vals VALUES (1, 0);");
sql("INSERT INTO vals VALUES (2, 0);");
sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
// works
sql("INSERT INTO other_vals VALUES (3, (SELECT other_val FROM
other_vals WHERE id = 1));");
// works
sql("UPDATE vals SET val = (SELECT other_val FROM other_vals LIMIT 1)
WHERE id = 1");
// Error
sql("UPDATE vals SET val = (SELECT other_val FROM other_vals) WHERE id
>= 2");
}
{code}
Error:
{code:java}
Caused by: java.lang.AssertionError
at
org.apache.calcite.sql.validate.implicit.AbstractTypeCoercion.needToCast(AbstractTypeCoercion.java:297)
at
org.apache.calcite.sql.validate.implicit.AbstractTypeCoercion.needToCast(AbstractTypeCoercion.java:250)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.needToCast(IgniteTypeCoercion.java:294)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.doCoerceColumnType(IgniteTypeCoercion.java:453)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.coerceColumnType(IgniteTypeCoercion.java:360)
at
org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.coerceSourceRowType(TypeCoercionImpl.java:676)
at
org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.querySourceCoercion(TypeCoercionImpl.java:646)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.querySourceCoercion(IgniteTypeCoercion.java:241)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.checkTypeAssignment(SqlValidatorImpl.java:5112)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateUpdate(SqlValidatorImpl.java:5219)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validateUpdate(IgniteSqlValidator.java:187)
at org.apache.calcite.sql.SqlUpdate.validate(SqlUpdate.java:190)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1090)
at
org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:796)
at
org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validate(IgniteSqlValidator.java:161)
at
org.apache.ignite.internal.sql.engine.prepare.IgnitePlanner.validate(IgnitePlanner.java:204)
{code}
Insert statement works correctly:
{code:java}
@Test
public void ddml0() {
sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
INTEGER);");
sql("INSERT INTO vals VALUES (1, 0);");
sql("INSERT INTO vals VALUES (2, 0);");
sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
// Fails with `Subquery returned more than 1 value`
sql("INSERT INTO vals VALUES (3, (SELECT other_val FROM other_vals
WHERE id > 0));");
}
{code}
> Sql. Update statements incorrectly use scalar subqueries in target columns
> ----------------------------------------------------------------------------
>
> Key: IGNITE-20835
> URL: https://issues.apache.org/jira/browse/IGNITE-20835
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Reporter: Maksim Zhuravkov
> Priority: Minor
> Labels: ignite-3
> Fix For: 3.0.0-beta2
>
>
> Update statements incorrectly use scalar subqueries in target columns, the
> issues stems from the fact that scalar subquery in UPDATE are not wrapped in
> `SINGLE_VAL` aggregate.
> Examples:
> {code:java}
> @Test
> public void ddml2() {
> sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
> sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
> INTEGER);");
> sql("INSERT INTO vals VALUES (1, 0);");
> sql("INSERT INTO vals VALUES (2, 0);");
> sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
> // works
> sql("INSERT INTO other_vals VALUES (3, (SELECT other_val FROM
> other_vals WHERE id = 1));");
> // works
> sql("UPDATE vals SET val = (SELECT other_val FROM other_vals LIMIT 1)
> WHERE id = 1");
> // Should fail, because a subquery returns more than 1 result.
> sql("UPDATE vals SET val = (SELECT other_val FROM other_vals) WHERE
> id >= 2");
> }
> UPDATEs in MERGE statement are also affect by this issue:
> {code:java}
> @Test
> public void ddml2_1() {
> sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
> sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
> INTEGER);");
> sql("INSERT INTO vals VALUES (1, 0);");
> sql("INSERT INTO vals VALUES (2, 0);");
> sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
> // works
> sql("INSERT INTO other_vals VALUES (3, (SELECT other_val FROM
> other_vals WHERE id = 1));");
> // Fails with PK unique constraint is violated
> sql("MERGE INTO vals USING other_vals ON vals.id = other_vals.id "
> + "WHEN MATCHED THEN UPDATE SET val = (SELECT other_val FROM
> other_vals WHERE id > 1) "
> + "WHEN NOT MATCHED THEN INSERT (id, val) VALUES (100, -1)");
> }
> {code}
> {code}
> If we change val type to BIGINT we get another error:
> {code:java}
> @Test
> public void ddml3() {
> sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
> sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
> BIGINT);");
> sql("INSERT INTO vals VALUES (1, 0);");
> sql("INSERT INTO vals VALUES (2, 0);");
> sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
> // works
> sql("INSERT INTO other_vals VALUES (3, (SELECT other_val FROM
> other_vals WHERE id = 1));");
> // works
> sql("UPDATE vals SET val = (SELECT other_val FROM other_vals LIMIT 1)
> WHERE id = 1");
> // Error
> sql("UPDATE vals SET val = (SELECT other_val FROM other_vals) WHERE
> id >= 2");
> }
> {code}
> Error:
> {code:java}
> Caused by: java.lang.AssertionError
> at
> org.apache.calcite.sql.validate.implicit.AbstractTypeCoercion.needToCast(AbstractTypeCoercion.java:297)
> at
> org.apache.calcite.sql.validate.implicit.AbstractTypeCoercion.needToCast(AbstractTypeCoercion.java:250)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.needToCast(IgniteTypeCoercion.java:294)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.doCoerceColumnType(IgniteTypeCoercion.java:453)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.coerceColumnType(IgniteTypeCoercion.java:360)
> at
> org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.coerceSourceRowType(TypeCoercionImpl.java:676)
> at
> org.apache.calcite.sql.validate.implicit.TypeCoercionImpl.querySourceCoercion(TypeCoercionImpl.java:646)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteTypeCoercion.querySourceCoercion(IgniteTypeCoercion.java:241)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.checkTypeAssignment(SqlValidatorImpl.java:5112)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateUpdate(SqlValidatorImpl.java:5219)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validateUpdate(IgniteSqlValidator.java:187)
> at org.apache.calcite.sql.SqlUpdate.validate(SqlUpdate.java:190)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1090)
> at
> org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:796)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgniteSqlValidator.validate(IgniteSqlValidator.java:161)
> at
> org.apache.ignite.internal.sql.engine.prepare.IgnitePlanner.validate(IgnitePlanner.java:204)
> {code}
> Insert statement behaves correctly:
> {code:java}
> @Test
> public void ddml0() {
> sql("CREATE TABLE vals (id INTEGER PRIMARY KEY, val INTEGER);");
> sql("CREATE TABLE other_vals (id INTEGER PRIMARY KEY, other_val
> INTEGER);");
> sql("INSERT INTO vals VALUES (1, 0);");
> sql("INSERT INTO vals VALUES (2, 0);");
> sql("INSERT INTO other_vals VALUES (1, 1), (2, 2);");
> // Fails with `Subquery returned more than 1 value`
> sql("INSERT INTO vals VALUES (3, (SELECT other_val FROM other_vals WHERE
> id > 0));");
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
