[
https://issues.apache.org/jira/browse/IGNITE-22967?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vladimir Steshin updated IGNITE-22967:
--------------------------------------
Labels: calcite ignite-2 ise sql (was: calcite ise sql)
> 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, ignite-2, ise, sql
> Fix For: 2.17
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> 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)
