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 <
[email protected]> 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 <[email protected]> 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 <K, V> CacheConfiguration<K, V> cacheConfig(String name, Class<?>... idxTypes) {
return new CacheConfiguration<K, V>(DEFAULT_CACHE_NAME)
.setName(name)
.setCacheMode(CacheMode.PARTITIONED)
.setAtomicityMode(CacheAtomicityMode.ATOMIC)
.setBackups(1)
.setIndexedTypes(idxTypes);
}
/** */
public void testJoinQuery() throws Exception {
CacheConfiguration<Long, Details> ccfg1 = cacheConfig("second_cache", Long.class, Details.class);
CacheConfiguration<Long, Customer> ccfg2 = cacheConfig("customer_cache", Long.class, Customer.class);
final IgniteCache<Long, Details> c1 = ignite(0).getOrCreateCache(ccfg1);
final IgniteCache<Long, Customer> 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<Object, Object> c = client.cache("customer_cache");
assertEquals(CUSTOMER_COUNT,c.query(qry).getAll().size());
}
finally {
c1.destroy();
c2.destroy();
}
}
/**
* @param c1 Cache1.
* @param c2 Cache2.
*/
private void populateDataIntoCaches(IgniteCache<Long, Details> c1, IgniteCache<Long, Customer> c2) {
long personId = 0;
for (long i = 0; i < CUSTOMER_COUNT; i++) {
Customer cust = new Customer();
cust.setCustomerid(i);
cust.setName("Customer #" + i);
c2.put(i, cust);
for (int j = 0; j < DETAILS_PER_CUSTOMER; j++) {
Details dtls = new Details();
dtls.setId(personId);
dtls.setCustId(i);
dtls.setName("Details name #" + i);
dtls.setAddress("Address #" + i);
c1.put(personId, dtls);
personId++;
}
}
}
/**
*
*/
private static class Details {
/** */
@QuerySqlField(index = true)
private Long id;
/** */
@QuerySqlField(index = true)
private Long cust_id;
/** */
@QuerySqlField
private String name;
/** */
@QuerySqlField
private String address;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Long getCustId() {
return cust_id;
}
public void setCustId(Long cust_id) {
this.cust_id = cust_id;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
/**
*
*/
private static class Customer {
/** */
@QuerySqlField(index = true)
private Long customerid;
/** */
@QuerySqlField(index = true)
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Long getCustomerid() {
return customerid;
}
public void setCustomerid(Long customerid) {
this.customerid = customerid;
}
}
}
