/** {@inheritDoc} */ @Override public SqlFieldsQueryEx setDistributedJoins(boolean distributedJoins) { super.setDistributedJoins(distributedJoins); return this; }
/** * Creates SqlFieldsQuery with enabled distributed joins, * specified enforce join order flag and specified sql from template. * * @param enforceJoinOrder don't let engine to reorder tables in join. * @param tpl query template in java format. * @param args arguments for the template. */ static SqlFieldsQuery distributedJoinQry(boolean enforceJoinOrder, String tpl, Object... args) { return joinQry(enforceJoinOrder, tpl, args).setDistributedJoins(true); } }
/** * @param sql SQL. * @param cache Cache. * @param enforceJoinOrder Enforce join order flag. * @return Query plan. */ private String plan(String sql, IgniteCache<?, ?> cache, boolean enforceJoinOrder) { return (String)cache.query(new SqlFieldsQuery("explain " + sql) .setDistributedJoins(true) .setEnforceJoinOrder(enforceJoinOrder)) .getAll().get(0).get(0); }
@Override public Void call() throws Exception { SqlFieldsQuery qry = new SqlFieldsQuery(sql); qry.setDistributedJoins(true); cache.query(qry).getAll(); return null; } }, CacheException.class, null);
/** * @param cache Cache. * @param sql SQL. * @param enforceJoinOrder Enforce join order flag. */ private void checkQueryFails(final IgniteCache<Object, Object> cache, String sql, boolean enforceJoinOrder) { final SqlFieldsQuery qry = new SqlFieldsQuery(sql); qry.setDistributedJoins(true); qry.setEnforceJoinOrder(enforceJoinOrder); Throwable err = GridTestUtils.assertThrows(log, new Callable<Void>() { @Override public Void call() throws Exception { cache.query(qry); return null; } }, CacheException.class, null); assertTrue("Unexpected error message: " + err.getMessage(), err.getMessage().contains("can not use distributed joins for cache with custom AffinityKeyMapper configured.")); }
/** * @param s Statement. * @param c Cache. * @param qry Query. * @throws SQLException If failed. */ private <Z extends X> void checkSameResult(Statement s, IgniteCache<Integer, Z> c, String qry) throws SQLException { s.executeUpdate("SET SCHEMA " + c.getName()); try ( ResultSet rs1 = s.executeQuery(qry); QueryCursor<List<?>> rs2 = c.query(new SqlFieldsQuery(qry).setDistributedJoins(true)) ) { Iterator<List<?>> iter = rs2.iterator(); for (;;) { if (!rs1.next()) { assertFalse(iter.hasNext()); return; } assertTrue(iter.hasNext()); List<?> row = iter.next(); for (int i = 0; i < row.size(); i++) assertEquals(rs1.getLong(i + 1), row.get(i)); } } }
/** * @param cache Cache. * @param sql SQL. * @param enforceJoinOrder Enforce join order flag. */ private void checkQueryFails(final IgniteCache<Object, Object> cache, String sql, boolean enforceJoinOrder) { final SqlFieldsQuery qry = new SqlFieldsQuery(sql); qry.setDistributedJoins(true); qry.setEnforceJoinOrder(enforceJoinOrder); GridTestUtils.assertThrows(log, new Callable<Void>() { @Override public Void call() throws Exception { cache.query(qry); return null; } }, CacheException.class, null); }
/** * @param sql SQL. * @param cache Cache. * @param enforceJoinOrder Enforce join order flag. * @param expSize Expected results size. */ private void checkQuery(String sql, IgniteCache<Object, Object> cache, boolean enforceJoinOrder, int expSize) { String plan = (String)cache.query(new SqlFieldsQuery("explain " + sql) .setDistributedJoins(true) .setEnforceJoinOrder(enforceJoinOrder)) .getAll().get(0).get(0); log.info("Plan: " + plan); SqlFieldsQuery qry = new SqlFieldsQuery(sql); qry.setDistributedJoins(true); qry.setEnforceJoinOrder(enforceJoinOrder); QueryCursor<List<?>> cur = cache.query(qry); List<List<?>> res = cur.getAll(); if (expSize != res.size()) log.info("Results: " + res); assertEquals(expSize, res.size()); } /**
/** * @param sql SQL. * @param cache Cache. * @param enforceJoinOrder Enforce join order flag. * @param args Arguments. * @throws Exception If failed. */ private void checkQuery(String sql, IgniteCache<Object, Object> cache, boolean enforceJoinOrder, Object... args) throws Exception { String plan = (String)cache.query(new SqlFieldsQuery("explain " + sql) .setArgs(args) .setDistributedJoins(true) .setEnforceJoinOrder(enforceJoinOrder)) .getAll().get(0).get(0); log.info("Plan: " + plan); compareQueryRes0(cache, sql, true, enforceJoinOrder, args, Ordering.RANDOM); }
/** * Execute SQL command and return resulting dataset. * @param cache Cache to initiate query from. * @param sql Statement. * @return result. */ protected List<List<?>> run(IgniteCache<?, ?> cache, String sql, Object... args) { SqlFieldsQuery qry = new SqlFieldsQuery(sql).setSchema(QueryUtils.DFLT_SCHEMA).setArgs(args) .setDistributedJoins(true); return cache.query(qry).getAll(); }
/** * @param sql SQL. * @param cache Cache. * @param expSize Expected results size. * @param args Arguments. * @return Results. */ private List<List<?>> checkQuery(String sql, IgniteCache<Object, Object> cache, int expSize, Object... args) { SqlFieldsQuery qry = new SqlFieldsQuery(sql); qry.setDistributedJoins(true); qry.setArgs(args); log.info("Plan: " + queryPlan(cache, qry)); QueryCursor<List<?>> cur = cache.query(qry); List<List<?>> res = cur.getAll(); if (expSize != res.size()) log.info("Results: " + res); assertEquals(expSize, res.size()); return res; }
/** * @param cache Cache. * @param enforceJoinOrder Enforce join order flag. * @param expBatchedJoins Expected batched joins count. * @param qry Query. * @param expText Expected text to find in plan. */ private void checkQueryPlan(IgniteCache<Object, Object> cache, boolean enforceJoinOrder, int expBatchedJoins, SqlFieldsQuery qry, String... expText) { qry.setEnforceJoinOrder(enforceJoinOrder); qry.setDistributedJoins(true); String plan = queryPlan(cache, qry); log.info("\n Plan:\n" + plan); assertEquals("Unexpected number of batched joins in plan [plan=" + plan + ", qry=" + qry + ']', expBatchedJoins, StringUtils.countOccurrencesOf(plan, "batched")); int startIdx = 0; for (String exp : expText) { int idx = plan.indexOf(exp, startIdx); if (idx == -1) { fail("Plan does not contain expected string [startIdx=" + startIdx + ", plan=" + plan + ", exp=" + exp + ']'); } startIdx = idx + 1; } }
/** * @param sql SQL. * @param cache Cache. * @param enforceJoinOrder Enforce join order flag. * @param expSize Expected results size. * @param args Arguments. */ private void checkQuery(String sql, IgniteCache<Object, Object> cache, boolean enforceJoinOrder, int expSize, Object... args) { SqlFieldsQuery qry = new SqlFieldsQuery(sql); qry.setDistributedJoins(true); qry.setEnforceJoinOrder(enforceJoinOrder); qry.setArgs(args); log.info("Plan: " + queryPlan(cache, qry)); QueryCursor<List<?>> cur = cache.query(qry); List<List<?>> res = cur.getAll(); if (expSize != res.size()) log.info("Results: " + res); assertEquals(expSize, res.size()); }
/** * @param sql SQL. * @param cache Cache. * @param enforceJoinOrder Enforce join order flag. * @param expSize Expected results size. * @param args Arguments. * @return Results. */ private List<List<?>> checkQuery(String sql, IgniteCache<Object, Object> cache, boolean enforceJoinOrder, int expSize, Object... args) { SqlFieldsQuery qry = new SqlFieldsQuery(sql); qry.setDistributedJoins(true); qry.setEnforceJoinOrder(enforceJoinOrder); qry.setArgs(args); log.info("Plan: " + queryPlan(cache, qry)); QueryCursor<List<?>> cur = cache.query(qry); List<List<?>> res = cur.getAll(); if (expSize != res.size()) log.info("Results: " + res); assertEquals(expSize, res.size()); return res; }
/** * Test different query modes. */ @Test public void testQueryModes() throws Exception { Ignite ignite = startGrid(0); startGrid(1); UUID nodeId = ignite.cluster().localNode().id(); IgniteCache cache = ignite.getOrCreateCache(DEFAULT_CACHE_NAME); String sql = "SELECT ID FROM IGNITE.NODES WHERE NODE_ORDER = 1"; SqlFieldsQuery qry; qry = new SqlFieldsQuery(sql).setDistributedJoins(true); assertEquals(nodeId, ((List<?>)cache.query(qry).getAll().get(0)).get(0)); qry = new SqlFieldsQuery(sql).setReplicatedOnly(true); assertEquals(nodeId, ((List<?>)cache.query(qry).getAll().get(0)).get(0)); qry = new SqlFieldsQuery(sql).setLocal(true); assertEquals(nodeId, ((List<?>)cache.query(qry).getAll().get(0)).get(0)); }
/** * Check distributed joins. * * @throws Exception If failed. */ public void checkDistributedQueryWithSegmentedIndex() throws Exception { for (int i = 0; i < nodesCount(); i++) { IgniteCache<Integer, Person> c1 = ignite(i).cache(PERSON_CAHE_NAME); long expPersons = 0; for (Cache.Entry<Integer, Person> e : c1) { final Integer orgId = e.getValue().orgId; // We have as orphan ORG rows as orphan PERSON rows. if (ORPHAN_ROWS <= orgId && orgId < 500) expPersons++; } String select0 = "select o.name n1, p.name n2 from \"pers\".Person p, \"org\".Organization o where p.orgId = o._key"; List<List<?>> res = c1.query(new SqlFieldsQuery(select0).setDistributedJoins(true)).getAll(); assertEquals(expPersons, res.size()); } }
/** * @throws Exception If failed. */ @Test public void testBigQuery() throws Exception { X.println(); X.println(bigQry); X.println(); X.println(" Plan: \n" + cacheCustOrd.query(new SqlFieldsQuery("EXPLAIN " + bigQry) .setDistributedJoins(distributedJoins())).getAll()); List<List<?>> res = compareQueryRes0(cacheCustOrd, bigQry, distributedJoins(), new Object[0], Ordering.RANDOM); X.println(" Result size: " + res.size()); assertTrue(!res.isEmpty()); // Ensure we set good testing data at database. }
/** * @param cache Cache. * @param qry Query. * @return Query plan. */ protected final String queryPlan(IgniteCache<?, ?> cache, SqlFieldsQuery qry) { return (String)cache.query(new SqlFieldsQuery("explain " + qry.getSql()) .setArgs(qry.getArgs()) .setLocal(qry.isLocal()) .setCollocated(qry.isCollocated()) .setPageSize(qry.getPageSize()) .setDistributedJoins(qry.isDistributedJoins()) .setEnforceJoinOrder(qry.isEnforceJoinOrder())) .getAll().get(0).get(0); }
/** * JUnit. * * @throws Exception In case of error. */ @Test public void testDistributedJoinCustomTableName() throws Exception { IgniteCache<Integer, Object> cache = jcache(Integer.class, Object.class); cache.put(10, new Type1(1, "Type1 record #1")); cache.put(20, new Type1(2, "Type1 record #2")); cache.put(30, new Type2(1, "Type2 record #1")); cache.put(40, new Type2(2, "Type2 record #2")); cache.put(50, new Type2(3, "Type2 record #3")); QueryCursor<List<?>> query = cache.query( new SqlFieldsQuery("SELECT t2.name, t1.name FROM Type2 as t2 LEFT JOIN Type1 as t1 ON t1.id = t2.id") .setDistributedJoins(cacheMode() == PARTITIONED)); assertEquals(2, query.getAll().size()); query = cache.query( new SqlFieldsQuery("SELECT t2.name, t1.name FROM Type2 as t2 RIGHT JOIN Type1 as t1 ON t1.id = t2.id") .setDistributedJoins(cacheMode() == PARTITIONED)); assertEquals(3, query.getAll().size()); }
/** {@inheritDoc} */ @SuppressWarnings("deprecation") @Override public SqlFieldsQuery generateFieldsQuery(String cacheName, SqlQuery qry) { String schemaName = schema(cacheName); String type = qry.getType(); H2TableDescriptor tblDesc = schemaMgr.tableForType(schemaName, cacheName, type); if (tblDesc == null) throw new IgniteSQLException("Failed to find SQL table for type: " + type, IgniteQueryErrorCode.TABLE_NOT_FOUND); String sql; try { sql = H2Utils.generateFieldsQueryString(qry.getSql(), qry.getAlias(), tblDesc); } catch (IgniteCheckedException e) { throw new IgniteException(e); } SqlFieldsQuery res = new SqlFieldsQuery(sql); res.setArgs(qry.getArgs()); res.setDistributedJoins(qry.isDistributedJoins()); res.setLocal(qry.isLocal()); res.setPageSize(qry.getPageSize()); res.setPartitions(qry.getPartitions()); res.setReplicatedOnly(qry.isReplicatedOnly()); res.setSchema(schemaName); res.setSql(sql); res.setDataPageScanEnabled(qry.isDataPageScanEnabled()); if (qry.getTimeout() > 0) res.setTimeout(qry.getTimeout(), TimeUnit.MILLISECONDS); return res; }