[ https://issues.apache.org/jira/browse/IGNITE-12901?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Ivan Daschinskiy resolved IGNITE-12901. --------------------------------------- Resolution: Implemented > 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: Major > 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)