[
https://issues.apache.org/jira/browse/IGNITE-22967?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vladimir Steshin updated IGNITE-22967:
--------------------------------------
Description:
Test
{code:java}
public class LimitOffsetIntegrationTest extends AbstractBasicIntegrationTest {
/** */
@Test
public void testLimitOffsetWithNestedUnion() {
sql("INSERT into TEST_REPL VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4,
'd')");
assertQuery("(SELECT id FROM TEST_REPL WHERE id = 2) UNION ALL " +
"SELECT id FROM (select id from (SELECT id FROM TEST_REPL OFFSET 2)
order by id OFFSET 1)"
).returns(2).returns(4).check();
}
/** {@inheritDoc} */
@Override protected IgniteConfiguration getConfiguration(String
igniteInstanceName) throws Exception {
QueryEntity eRepl = new QueryEntity()
.setTableName("TEST_REPL")
.setKeyType(Integer.class.getName())
.setValueType(String.class.getName())
.setKeyFieldName("id")
.setValueFieldName("val")
.addQueryField("id", Integer.class.getName(), null)
.addQueryField("val", String.class.getName(), null);
return super.getConfiguration(igniteInstanceName)
.setCacheConfiguration(
new CacheConfiguration<>(eRepl.getTableName())
.setCacheMode(CacheMode.REPLICATED)
.setQueryEntities(singletonList(eRepl))
.setSqlSchema("PUBLIC"));
}
}
{code}
Gives the result [2, 3, 4] instead of [2, 4]. Removing any of `UNION` or `ORDER
BY` fixes.
Plan without the `ORDER BY`:
{code:java}
IgniteUnionAll(all=[true])
IgniteIndexScan(table=[[PUBLIC, TEST_REPL]], index=[_key_PK_proxy],
filters=[=($t0, 2)], requiredColumns=[{2}], searchBounds=[[null, null,
ExactBounds [bound=2], null]], collation=[[2 ASC-nulls-first]])
IgniteLimit(offset=[1])
IgniteLimit(offset=[2])
IgniteTableScan(table=[[PUBLIC, TEST_REPL]], requiredColumns=[{2}])
{code}
Plan with the `ORDER BY`
{code:java}
IgniteUnionAll(all=[true])
IgniteIndexScan(table=[[PUBLIC, TEST_REPL]], index=[_key_PK_proxy],
filters=[=($t0, 2)], requiredColumns=[{2}], searchBounds=[[null, null,
ExactBounds [bound=2], null]], collation=[[2 ASC-nulls-first]])
IgniteLimit(offset=[2])
IgniteTableScan(table=[[PUBLIC, TEST_REPL]], requiredColumns=[{2}])
{code}
was:
Test
{code:java}
public class LimitOffsetIntegrationTest extends AbstractBasicIntegrationTest {
/** */
@Test
public void testLimitOffsetWithNestedUnion() {
sql("INSERT into TEST_REPL VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4,
'd')");
assertQuery("(SELECT id FROM TEST_REPL WHERE id = 2) UNION ALL " +
"SELECT id FROM (select id from (SELECT id FROM TEST_REPL OFFSET 2)
order by id OFFSET 1)"
).returns(2).returns(4).check();
}
/** {@inheritDoc} */
@Override protected IgniteConfiguration getConfiguration(String
igniteInstanceName) throws Exception {
QueryEntity eRepl = new QueryEntity()
.setTableName("TEST_REPL")
.setKeyType(Integer.class.getName())
.setValueType(String.class.getName())
.setKeyFieldName("id")
.setValueFieldName("val")
.addQueryField("id", Integer.class.getName(), null)
.addQueryField("val", String.class.getName(), null);
return super.getConfiguration(igniteInstanceName)
.setCacheConfiguration(
new CacheConfiguration<>(eRepl.getTableName())
.setCacheMode(CacheMode.REPLICATED)
.setQueryEntities(singletonList(eRepl))
.setSqlSchema("PUBLIC"));
}
}
{code}
Gives the result [2, 3, 4] instead of [2, 4]. Removing any of `UNION` or `ORDER
BY` fixes.
Plan without the `ORDER`:
{code:java}
IgniteUnionAll(all=[true])
IgniteIndexScan(table=[[PUBLIC, TEST_REPL]], index=[_key_PK_proxy],
filters=[=($t0, 2)], requiredColumns=[{2}], searchBounds=[[null, null,
ExactBounds [bound=2], null]], collation=[[2 ASC-nulls-first]])
IgniteLimit(offset=[1])
IgniteLimit(offset=[2])
IgniteTableScan(table=[[PUBLIC, TEST_REPL]], requiredColumns=[{2}])
{code}
Plan with the `ORDER`
{code:java}
IgniteUnionAll(all=[true])
IgniteIndexScan(table=[[PUBLIC, TEST_REPL]], index=[_key_PK_proxy],
filters=[=($t0, 2)], requiredColumns=[{2}], searchBounds=[[null, null,
ExactBounds [bound=2], null]], collation=[[2 ASC-nulls-first]])
IgniteLimit(offset=[2])
IgniteTableScan(table=[[PUBLIC, TEST_REPL]], requiredColumns=[{2}])
{code}
> Incorrect SQL result with UNION, ORDER BY and OFFSET
> ----------------------------------------------------
>
> Key: IGNITE-22967
> URL: https://issues.apache.org/jira/browse/IGNITE-22967
> Project: Ignite
> Issue Type: Bug
> Reporter: Vladimir Steshin
> Assignee: Vladimir Steshin
> Priority: Major
> Labels: calcite, ise, sql
>
> Test
> {code:java}
> public class LimitOffsetIntegrationTest extends AbstractBasicIntegrationTest {
> /** */
> @Test
> public void testLimitOffsetWithNestedUnion() {
> sql("INSERT into TEST_REPL VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4,
> 'd')");
> assertQuery("(SELECT id FROM TEST_REPL WHERE id = 2) UNION ALL " +
> "SELECT id FROM (select id from (SELECT id FROM TEST_REPL OFFSET
> 2) order by id OFFSET 1)"
> ).returns(2).returns(4).check();
> }
> /** {@inheritDoc} */
> @Override protected IgniteConfiguration getConfiguration(String
> igniteInstanceName) throws Exception {
> QueryEntity eRepl = new QueryEntity()
> .setTableName("TEST_REPL")
> .setKeyType(Integer.class.getName())
> .setValueType(String.class.getName())
> .setKeyFieldName("id")
> .setValueFieldName("val")
> .addQueryField("id", Integer.class.getName(), null)
> .addQueryField("val", String.class.getName(), null);
> return super.getConfiguration(igniteInstanceName)
> .setCacheConfiguration(
> new CacheConfiguration<>(eRepl.getTableName())
> .setCacheMode(CacheMode.REPLICATED)
> .setQueryEntities(singletonList(eRepl))
> .setSqlSchema("PUBLIC"));
> }
> }
> {code}
> Gives the result [2, 3, 4] instead of [2, 4]. Removing any of `UNION` or
> `ORDER BY` fixes.
> Plan without the `ORDER BY`:
> {code:java}
> IgniteUnionAll(all=[true])
> IgniteIndexScan(table=[[PUBLIC, TEST_REPL]], index=[_key_PK_proxy],
> filters=[=($t0, 2)], requiredColumns=[{2}], searchBounds=[[null, null,
> ExactBounds [bound=2], null]], collation=[[2 ASC-nulls-first]])
> IgniteLimit(offset=[1])
> IgniteLimit(offset=[2])
> IgniteTableScan(table=[[PUBLIC, TEST_REPL]], requiredColumns=[{2}])
> {code}
> Plan with the `ORDER BY`
> {code:java}
> IgniteUnionAll(all=[true])
> IgniteIndexScan(table=[[PUBLIC, TEST_REPL]], index=[_key_PK_proxy],
> filters=[=($t0, 2)], requiredColumns=[{2}], searchBounds=[[null, null,
> ExactBounds [bound=2], null]], collation=[[2 ASC-nulls-first]])
> IgniteLimit(offset=[2])
> IgniteTableScan(table=[[PUBLIC, TEST_REPL]], requiredColumns=[{2}])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
