Re:Re: Join with subquery in 1.9 or 2.0

2017-09-22 Thread 贺波 
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

Re: Join with subquery in 1.9 or 2.0

2017-09-22 Thread Andrey Mashenkov 
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());

}

Re: Join with subquery in 1.9 or 2.0

2017-09-20 Thread Andrey Mashenkov 
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

2017-09-18 Thread acet 
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/