Re:Re: Join with subquery in 1.9 or 2.0
Hi, Your demo is different from my.Mine is an example of recursive,using "with as"function.My test demo is in the attachment.package userlist; import java.util.List; import org.apache.ignite.Ignite; import org.apache.ignite.IgniteCache; import org.apache.ignite.cache.CacheAtomicityMode; import org.apache.ignite.cache.CacheMode; import org.apache.ignite.cache.query.SqlFieldsQuery; import org.apache.ignite.cache.query.annotations.QuerySqlField; import org.apache.ignite.configuration.CacheConfiguration; import org.apache.ignite.configuration.IgniteConfiguration; import org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi; import org.apache.ignite.spi.discovery.tcp.ipfinder.TcpDiscoveryIpFinder; import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder; import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest; /** * */ public class JoinTest extends GridCommonAbstractTest { /** */ private static final TcpDiscoveryIpFinder IP_FINDER = new TcpDiscoveryVmIpFinder(true); /** */ private static final String DEFAULT_CACHE_NAME = "MYCACHE"; /** */ private static final int NODES_COUNT = 2; /** */ private static final int CUSTOMER_COUNT = 23; /** */ private static final int DETAILS_PER_CUSTOMER = 37; /** */ private static String SQL = "with RECURSIVE children(typeId, pTypeId) AS ( "+ " SELECT typeId, pTypeId FROM ProcessDefTypePo WHERE pTypeId = ? "+ " UNION ALL "+ " SELECT ProcessDefTypePo.typeId, ProcessDefTypePo.pTypeId "+ " FROM children INNER JOIN ProcessDefTypePo ON children.typeId = ProcessDefTypePo.pTypeId "+ ") "+ "select t1.typeId,t1.pTypeId,t1.typeName,t1.description, t2.typeName as pTypeName from ProcessDefTypePo t1 left join ProcessDefTypePo t2 on t1.pTypeId=t2.typeId where t1.typeId not in ( select typeId from children )"; /** {@inheritDoc} */ @Override protected IgniteConfiguration getConfiguration(String gridName) throws Exception { IgniteConfiguration cfg = super.getConfiguration(gridName); cfg.setPeerClassLoadingEnabled(false); TcpDiscoverySpi disco = new TcpDiscoverySpi(); disco.setIpFinder(IP_FINDER); cfg.setDiscoverySpi(disco); return cfg; } /** {@inheritDoc} */ @Override protected void beforeTestsStarted() throws Exception { startGridsMultiThreaded(NODES_COUNT, true); } /** {@inheritDoc} */ @Override protected void afterTestsStopped() throws Exception { stopAllGrids(); } /** * @param name Cache name. * @param idxTypes Indexed types. * @return Cache configuration. */ private CacheConfiguration cacheConfig(String name, Class... idxTypes) { return new CacheConfiguration(DEFAULT_CACHE_NAME) .setName(name) .setCacheMode(CacheMode.PARTITIONED) .setAtomicityMode(CacheAtomicityMode.ATOMIC) .setBackups(1) .setIndexedTypes(idxTypes); } /** */ public void testJoinQuery() throws Exception { CacheConfiguration ccfg1 = cacheConfig("processDefTypeCache", String.class, ProcessDefTypePo.class); final IgniteCache c1 = ignite(0).getOrCreateCache(ccfg1); try { populateDataIntoCaches(c1); final SqlFieldsQuery qry = new SqlFieldsQuery(SQL); qry.setDistributedJoins(true); assertEquals(CUSTOMER_COUNT,c1.query(qry).getAll().size()); // Ignite client = startGrid("client", getConfiguration("client").setClientMode(true)); IgniteCache c = client.cache("processDefTypeCache"); assertEquals(CUSTOMER_COUNT,c.query(qry).getAll().size()); } finally { c1.destroy(); } } /** * @param c1 Cache1. */ private void populateDataIntoCaches(IgniteCache c1) { ProcessDefTypePo processDefTypePo=new ProcessDefTypePo(); processDefTypePo.setTypeId("0"); processDefTypePo.setPTypeId("-1"); processDefTypePo.setTypeName(0); c1.put("0", processDefTypePo); for (int j = 1; j < 10; j++) { ProcessDefTypePo dtls = new ProcessDefTypePo(); dtls.setTypeId(j); dtls.setPTypeId(j-1); dtls.setTypeName("" + j); c1.put(j, dtls); } } /** * */ private static class ProcessDefTypePo { /** */ @QuerySqlField(index = true) private String typeId; /** */ @QuerySqlField(index = true) private String pTypeId; @QuerySqlField private String typeName; public void setTypeId(String
Re: Join with subquery in 1.9 or 2.0
Hi, I can't reproduce the issue neither on 1.9 nor 2.0 nor 2.1 version. PFA repro attached. Would you please check if I've missed smth? On Wed, Sep 20, 2017 at 1:41 PM, Andrey Mashenkov < andrey.mashen...@gmail.com> wrote: > Hi, > > Looks like a bug. > > Would you please share a full stacktrace and a reproducer if possible? > > You can try to rewrite query without join to smth like this: > Select .. from A, B Where A.id = B.id; > > On Mon, Sep 18, 2017 at 7:36 PM, acet wrote: > >> Hello, >> I was looking to do something similar to: >> >> SELECT a.customerid, h.name, h.address >> FROM >> "customer_cache".CUSTOMER as a >> JOIN >> (select min(id) as id, name, address, cust_id from "second_cache".DETAILS >> group by name, address, cust_id) as h on a.customerid = h.cust_id >> >> This seems to work fine in the debug console but when trying it with >> ignite >> I get: >> >> javax.cache.CacheException: class org.apache.ignite.IgniteException: >> org.apache.ignite.internal.processors.query.h2.sql.GridSqlJoin cannot be >> cast to org.apache.ignite.internal.processors.query.h2.sql.GridSqlAlias >> >> >> Is there any way to achieve this? >> Thanks >> >> >> >> -- >> Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >> > > > > -- > Best regards, > Andrey V. Mashenkov > -- Best regards, Andrey V. Mashenkov package userlist; import java.util.List; import org.apache.ignite.Ignite; import org.apache.ignite.IgniteCache; import org.apache.ignite.cache.CacheAtomicityMode; import org.apache.ignite.cache.CacheMode; import org.apache.ignite.cache.query.SqlFieldsQuery; import org.apache.ignite.cache.query.annotations.QuerySqlField; import org.apache.ignite.configuration.CacheConfiguration; import org.apache.ignite.configuration.IgniteConfiguration; import org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi; import org.apache.ignite.spi.discovery.tcp.ipfinder.TcpDiscoveryIpFinder; import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder; import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest; /** * */ public class JoinTest extends GridCommonAbstractTest { /** */ private static final TcpDiscoveryIpFinder IP_FINDER = new TcpDiscoveryVmIpFinder(true); /** */ private static final String DEFAULT_CACHE_NAME = "MYCACHE"; /** */ private static final int NODES_COUNT = 2; /** */ private static final int CUSTOMER_COUNT = 23; /** */ private static final int DETAILS_PER_CUSTOMER = 37; /** */ private static final String SQL = "SELECT a.customerid, h.name, h.address \n" + "FROM \n" + "\"customer_cache\".CUSTOMER as a \n" + "JOIN \n" + "(select min(id) as id, name, address, cust_id from \"second_cache\".DETAILS \n" + "group by name, address, cust_id) as h on a.customerid = h.cust_id"; /** {@inheritDoc} */ @Override protected IgniteConfiguration getConfiguration(String gridName) throws Exception { IgniteConfiguration cfg = super.getConfiguration(gridName); cfg.setPeerClassLoadingEnabled(false); TcpDiscoverySpi disco = new TcpDiscoverySpi(); disco.setIpFinder(IP_FINDER); cfg.setDiscoverySpi(disco); return cfg; } /** {@inheritDoc} */ @Override protected void beforeTestsStarted() throws Exception { startGridsMultiThreaded(NODES_COUNT, true); } /** {@inheritDoc} */ @Override protected void afterTestsStopped() throws Exception { stopAllGrids(); } /** * @param name Cache name. * @param idxTypes Indexed types. * @return Cache configuration. */ private CacheConfiguration cacheConfig(String name, Class... idxTypes) { return new CacheConfiguration(DEFAULT_CACHE_NAME) .setName(name) .setCacheMode(CacheMode.PARTITIONED) .setAtomicityMode(CacheAtomicityMode.ATOMIC) .setBackups(1) .setIndexedTypes(idxTypes); } /** */ public void testJoinQuery() throws Exception { CacheConfiguration ccfg1 = cacheConfig("second_cache", Long.class, Details.class); CacheConfiguration ccfg2 = cacheConfig("customer_cache", Long.class, Customer.class); final IgniteCache c1 = ignite(0).getOrCreateCache(ccfg1); final IgniteCache c2 = ignite(0).getOrCreateCache(ccfg2); try { populateDataIntoCaches(c1, c2); final SqlFieldsQuery qry = new SqlFieldsQuery(SQL); qry.setDistributedJoins(true); assertEquals(CUSTOMER_COUNT,c1.query(qry).getAll().size()); // Ignite client = startGrid("client", getConfiguration("client").setClientMode(true)); IgniteCache c = client.cache("customer_cache"); assertEquals(CUSTOMER_COUNT,c.query(qry).getAll().size()); } finally { c1.destroy(); c2.destroy(); } } /** * @param c1 Cache1. * @para
Re: Join with subquery in 1.9 or 2.0
Hi, Looks like a bug. Would you please share a full stacktrace and a reproducer if possible? You can try to rewrite query without join to smth like this: Select .. from A, B Where A.id = B.id; On Mon, Sep 18, 2017 at 7:36 PM, acet wrote: > Hello, > I was looking to do something similar to: > > SELECT a.customerid, h.name, h.address > FROM > "customer_cache".CUSTOMER as a > JOIN > (select min(id) as id, name, address, cust_id from "second_cache".DETAILS > group by name, address, cust_id) as h on a.customerid = h.cust_id > > This seems to work fine in the debug console but when trying it with ignite > I get: > > javax.cache.CacheException: class org.apache.ignite.IgniteException: > org.apache.ignite.internal.processors.query.h2.sql.GridSqlJoin cannot be > cast to org.apache.ignite.internal.processors.query.h2.sql.GridSqlAlias > > > Is there any way to achieve this? > Thanks > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ > -- Best regards, Andrey V. Mashenkov
Join with subquery in 1.9 or 2.0
Hello, I was looking to do something similar to: SELECT a.customerid, h.name, h.address FROM "customer_cache".CUSTOMER as a JOIN (select min(id) as id, name, address, cust_id from "second_cache".DETAILS group by name, address, cust_id) as h on a.customerid = h.cust_id This seems to work fine in the debug console but when trying it with ignite I get: javax.cache.CacheException: class org.apache.ignite.IgniteException: org.apache.ignite.internal.processors.query.h2.sql.GridSqlJoin cannot be cast to org.apache.ignite.internal.processors.query.h2.sql.GridSqlAlias Is there any way to achieve this? Thanks -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/