[
https://issues.apache.org/jira/browse/IGNITE-22967?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17872383#comment-17872383
]
Vladimir Steshin commented on IGNITE-22967:
-------------------------------------------
The offset (_LogicalSort_ with the _offset_) is removed in
_SqlToRelConverter#convertSelectImpl(Blackboard bb, SqlSelect select)_:
{code:java}
// Semantics example. Given the view definition
// CREATE VIEW v2 AS SELECT * FROM t ORDER BY x LIMIT 10
// we would never remove the ORDER BY, because "ORDER BY ... LIMIT" is about
// semantics. It is not a 'pure order'.
if (RelOptUtil.isPureOrder(castNonNull(bb.root))
&& config.isRemoveSortInSubQuery()) {
// Remove the Sort if the view is at the top level. Also remove the Sort
// if there are other nodes, which will cause the view to be in the
// sub-query.
if (!bb.top
|| validator().isAggregate(select)
|| select.isDistinct()
|| select.hasOrderBy()
|| select.getFetch() != null
|| select.getOffset() != null) {
bb.setRoot(castNonNull(bb.root).getInput(0), true);
}
}
{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)
