[jira] [Comment Edited] (IGNITE-12901) SQL: Uncorrelated subquery should run only once.

["Ivan Daschinskiy (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-12901?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17110038#comment-17110038 ] 

Ivan Daschinskiy edited comment on IGNITE-12901 at 5/18/20, 8:33 AM:
---------------------------------------------------------------------

This is not bug, but feature request. Internally, H2 supports this feature, but it is intentionally disabled in initial implementation of ignite sql. I suppose this is disabled due to possible performance impact of node-local counter (specifically contention). In order to support this feature, special node-local update counter should be added and incremented every DML and index update. Patch to support this feature is attached. Performance impact is questionable, additional investigation should be performed.


was (Author: ivandasch):
This is not bug, but feature request. Internally, H2 supports this feature, but it is intentionally disabled in initial implementation of ignite sql. In order to support this feature, special node-local update counter should be added and incremented every DML and index update. Patch to support this feature is attached. Performance impact is questionable, additional investigation should be performed.

> SQL: Uncorrelated subquery should run only once.
> ------------------------------------------------
>
>                 Key: IGNITE-12901
>                 URL: https://issues.apache.org/jira/browse/IGNITE-12901
>             Project: Ignite
>          Issue Type: Improvement
>    Affects Versions: 2.8
>            Reporter: Alexey Kukushkin
>            Assignee: Ivan Daschinskiy
>            Priority: Minor
>              Labels: sbcf
>         Attachments: ignite-12901-subquery.patch
>
>
> Currently uncorrelated subqueries (where subquery is not depends on the outer query) are executed on each nested loop iteration in the org.h2.command.dml.Select#isConditionMet method. 
> We may avoid this, for example, using results caching.
> h2. Reproducer
> {code:java}
> public class SubQueryTest extends AbstractIndexingCommonTest {
>     /** Keys counts at the RIGHT table. */
>     private static final int RIGHT_CNT = 10;
>     /** Keys counts at the LEFT table. */
>     private static final int LEFT_CNT = 50;
>     /** {@inheritDoc} */
>     @SuppressWarnings("unchecked")
>     @Override protected void beforeTest() throws Exception {
>         super.beforeTest();
>         startGrids(1);
>         IgniteCache cacheA = grid(0).createCache(new CacheConfiguration<Long, Long>()
>             .setName("A")
>             .setSqlSchema("TEST")
>             .setQueryEntities(Collections.singleton(new QueryEntity(Long.class.getTypeName(), "A_VAL")
>                     .setTableName("A")
>                     .addQueryField("ID", Long.class.getName(), null)
>                     .addQueryField("JID", Long.class.getName(), null)
>                     .addQueryField("VAL", Long.class.getName(), null)
>                     .setKeyFieldName("ID")
>             )));
>         IgniteCache cacheB = grid(0).createCache(new CacheConfiguration()
>             .setCacheMode(CacheMode.REPLICATED)
>             .setName("B")
>             .setSqlSchema("TEST")
>             .setQueryEntities(Collections.singleton(new QueryEntity(Long.class.getName(), "B_VAL")
>                     .setTableName("B")
>                     .addQueryField("ID", Long.class.getName(), null)
>                     .addQueryField("A_JID", Long.class.getName(), null)
>                     .addQueryField("VAL0", String.class.getName(), null)
>                     .setKeyFieldName("ID")
>             )));
>         Map<Long, BinaryObject> batch = new HashMap<>();
>         for (long i = 0; i < LEFT_CNT; ++i) {
>             batch.put(i, grid(0).binary().builder("A_VAL")
>                 .setField("JID", i % RIGHT_CNT)
>                 .setField("VAL", i)
>                 .build());
>             if (batch.size() > 1000) {
>                 cacheA.putAll(batch);
>                 batch.clear();
>             }
>         }
>         if (batch.size() > 0) {
>             cacheA.putAll(batch);
>             batch.clear();
>         }
>         for (long i = 0; i < RIGHT_CNT; ++i)
>             cacheB.put(i, grid(0).binary().builder("B_VAL")
>                 .setField("A_JID", i)
>                 .setField("VAL0", String.format("val%03d", i))
>                 .build());
>     }
>     /** {@inheritDoc} */
>     @Override protected void afterTest() throws Exception {
>         stopAllGrids();
>         super.afterTest();
>     }
>     /**
>      * Test local query execution.
>      */
>     @Test
>     public void test() {
>         sql(true, "SELECT * FROM A WHERE A.JID IN (SELECT A_JID FROM B)").getAll();
>     }
>     /**
>      * @param enforceJoinOrder Enforce join order mode.
>      * @param sql SQL query.
>      * @param args Query parameters.
>      * @return Results cursor.
>      */
>     private FieldsQueryCursor<List<?>> sql(boolean enforceJoinOrder, String sql, Object... args) {
>         return grid(0).context().query().querySqlFields(new SqlFieldsQuery(sql)
>             .setSchema("TEST")
>             .setLazy(true)
>             .setEnforceJoinOrder(enforceJoinOrder)
>             .setArgs(args), false);
>     }
> }
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)