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

[Alexey Kukushkin (Jira)]

Alexey Kukushkin created IGNITE-12901:
-----------------------------------------

             Summary: SQL: Uncorrelated subquery should run only once.
                 Key: IGNITE-12901
                 URL: https://issues.apache.org/jira/browse/IGNITE-12901
             Project: Ignite
          Issue Type: Bug
    Affects Versions: 2.8
            Reporter: Alexey Kukushkin


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)