[
https://issues.apache.org/jira/browse/IGNITE-12901?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17110038#comment-17110038
]
Ivan Daschinskiy commented on IGNITE-12901:
-------------------------------------------
This is not bug, but feature request. Internally, H2 supports this feature, but
it is intentionally disabled in initial implementation of ignite sql. In order
to support this feature, special node-local update counter should be added and
incremented every DML and index update. Patch to support this feature is
attached. Performance impact is questionable, additional investigation should
be performed.
> 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
> 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)
