You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Alexey Kukushkin (Jira)" <ji...@apache.org> on 2020/04/15 13:59:00 UTC
[jira] [Created] (IGNITE-12901) SQL: Uncorrelated subquery should
run only once.
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)