You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Ivan Daschinskiy (Jira)" <ji...@apache.org> on 2020/04/20 13:50:00 UTC
[jira] [Comment Edited] (IGNITE-12901) SQL: Uncorrelated subquery
should run only once.
[ https://issues.apache.org/jira/browse/IGNITE-12901?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17087750#comment-17087750 ]
Ivan Daschinskiy edited comment on IGNITE-12901 at 4/20/20, 1:49 PM:
---------------------------------------------------------------------
[~kukushal][~nizhikov] I found, that this feature is supported in H2 by default, but intentionally switched off. However, it's possible to enable it in similar manner, as {{SqlFieldsQuery#setLazy}} or {{SqlFieldsQuery#setEnforceJoinOrder}} manner and switched off by default. WDYT?
was (Author: ivandasch):
[~kukushal][~nizhikov] I found, that this feature is supported in H2 by default, but intentionally switched off. However, it's possible to enable it in similar manner, as {{ SqlFieldsQuery#setLazy }} or {{ SqlFieldsQuery#setEnforceJoinOrder }} manner and switched off by default. WDYT?
> 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
> Assignee: Ivan Daschinskiy
> Priority: Major
> Labels: sbcf
>
> 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)