Hi!
I have executed the recursive sql statement on H2 database,it executes
correctly.But it executes error since version 2.0.0.So I think it's a bug of
Ingite,not H2.My test demo is in the attachment.
At 2017-09-23 00:20:12, "Denis Mekhanikov" <dmekhani...@gmail.com> wrote:
Hi!
Internally Ignite uses H2 to process SQL queries. Recursive queries is an
experimental feature of H2, so I wouldn't recommend you to use it in production
for now.
Ignite 2.0 and 2.1 don't seem to support this kind of queries, so the best
option for you is to modify your query if possible to avoid recursive
constructs, or retrieve data from cache directly, without use of SQL.
Denis
пт, 22 сент. 2017 г. в 12:37, 贺波 <hebo...@163.com>:
Hi,I used Apache Ignite in my project for more than a year,from version 1.8.0
to 2.2.0.I use Ignite In-Menory Sql Grid in my project.I use “with as” sql
function in my sql,it executes correctly in version 1.9.0,but executes error
since version 2.0.0.My sql statement is:
with RECURSIVE children(typeId, pTypeId) AS (
SELECT typeId, pTypeId FROM ProcessDefTypePo WHERE pTypeId = '1'
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 )
The execution error in version 2.2.0 is:
Caused by: class org.apache.ignite.IgniteCheckedException: Unknown query type:
null
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2316)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820)
... 165 more
Caused by: java.lang.UnsupportedOperationException: Unknown query type: null
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1225)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTable(GridSqlQueryParser.java:501)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTableFilter(GridSqlQueryParser.java:465)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:565)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQueryExpression(GridSqlQueryParser.java:452)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1436)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression0(GridSqlQueryParser.java:1378)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseExpression(GridSqlQueryParser.java:1267)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseSelect(GridSqlQueryParser.java:536)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseQuery(GridSqlQueryParser.java:1220)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parse(GridSqlQueryParser.java:1181)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.parse(GridSqlQuerySplitter.java:1604)
at
org.apache.ignite.internal.processors.query.h2.sql.GridSqlQuerySplitter.split(GridSqlQuerySplitter.java:197)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1307)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813)
at
org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
Can you help me with this problem?Thanks.
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 <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<String, ProcessDefTypePo> ccfg1 =
cacheConfig("processDefTypeCache", String.class, ProcessDefTypePo.class);
final IgniteCache<String, ProcessDefTypePo> 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<Object, Object> c = client.cache("processDefTypeCache");
assertEquals(CUSTOMER_COUNT,c.query(qry).getAll().size());
}
finally {
c1.destroy();
}
}
/**
* @param c1 Cache1.
*/
private void populateDataIntoCaches(IgniteCache<String, ProcessDefTypePo>
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 typeId) {
this.typeId = typeId;
}
public String getTypeId(){
return typeId;
}
public void setPTypeId(String pTypeId) {
this.pTypeId = pTypeId;
}
public String getPTypeId(){
return pTypeId;
}
public void setTypeName(String typeName) {
this.typeName = typeName;
}
public String getTypeName(){
return typeName;
}
}
}
