[IGNITE-12901] SQL: Uncorrelated subquery should run only once.

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Implemented
• Affects Version/s: 2.8
• Labels: sbcf
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Ivan Daschinsky
• Reporter: Alexey Kukushkin
• Created: 15/Apr/20 13:58
• Updated: 17/Dec/20 13:17
• Resolved: 17/Dec/20 13:16

Description

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.

Reproducer

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

Issue Links

• is fixed by: IGNITE-10306 SQL: Transform subqueries to JOINs when possible