Details
-
Improvement
-
Status: Resolved
-
Major
-
Resolution: Implemented
-
2.8
-
None
-
None
-
Docs Required, Release Notes Required
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); } }
Attachments
Attachments
Issue Links
- is fixed by
-
IGNITE-10306 SQL: Transform subqueries to JOINs when possible
- Resolved