[jira] [Assigned] (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:all-tabpanel ]

Ivan Daschinskiy reassigned IGNITE-12901:
-----------------------------------------

    Assignee:     (was: Ivan Daschinskiy)

> 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
>            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)