Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 postgresql的递归查询，提示不能确定SQL类型
发布于 2580天前 作者 qq_6c5a7e9a 2429 次浏览 复制 上一个帖子 下一个帖子
标签: 无

SQL:

with recursive tb1 as (
select tb2.parent_canton_id,tb2.canton_id,tb2.canton_name,tb2.canton_id=tb0.canton_id as selected
from canton as tb0
join canton as tb2 on tb0.canton_id= '510600' and tb2.parent_canton_id = tb0.parent_canton_id
union all select tb3.parent_canton_id,tb3.canton_id,tb3.canton_name,tb3.canton_id=tb2.canton_id as selected
from tb1
join canton as tb2 on tb1.selected and tb2.canton_id = tb1.parent_canton_id
join canton as tb3 on tb3.parent_canton_id = tb2.parent_canton_id
)
select * from tb1 where selected order by parent_canton_id,selected desc;

错误信息：

[INFO ] 17:29:33.812 org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:82) - Can't identify SQL type :   with recursive tb1 as (
select tb2.parent_canton_id,tb2.canton_id,tb2.canton_name,tb2.canton_id=tb0.canton_id as selected
from canton as tb0
join canton as tb2 on tb0.canton_id= '510600' and tb2.parent_canton_id = tb0.parent_canton_id
union all select tb3.parent_canton_id,tb3.canton_id,tb3.canton_name,tb3.canton_id=tb2.canton_id as selected
from tb1
join canton as tb2 on tb1.selected and tb2.canton_id = tb1.parent_canton_id
join canton as tb3 on tb3.parent_canton_id = tb2.parent_canton_id
)
select * from tb1 where selected order by parent_canton_id,selected desc;
[WARN ] 17:29:33.848 org.nutz.mvc.impl.processor.FailProcessor.process(FailProcessor.java:28) - Error@/api/util/getCantons :
org.nutz.dao.DaoException: java.lang.NullPointerException
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithTransaction(NutDaoRunner.java:115)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:88)
	at org.nutz.dao.impl.sql.run.NutDaoRunner$1.run(NutDaoRunner.java:74)
	at org.nutz.trans.Trans.exec(Trans.java:174)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:72)
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:240)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:252)
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:236)
	at org.nutz.dao.impl.NutDao.execute(NutDao.java:1008)
	at com.hetaro.dao.BaseDao.getArray(BaseDao.java:28)
	at com.hetaro.dao.UitilDao.getCantons(UitilDao.java:12)
	at com.hetaro.service.UitilService.getCantons(UitilService.java:25)
	at com.hetaro.action.api.UtilApi.getCantons(UtilApi.java:22)
	at com.hetaro.action.api.UtilApi$FM$getCantons$6199597ae6411c820dae9921d1ed2035.invoke(UtilApi.java)
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:31)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:30)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:58)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:123)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27)
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44)
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15)
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:44)
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:67)
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31)
	at org.nutz.mvc.NutFilter.doFilter(NutFilter.java:202)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
	at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
	at org.nutz.mvc.WhaleFilter.doFilter(WhaleFilter.java:159)
	at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1637)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:533)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:190)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)
	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
	at org.eclipse.jetty.server.Server.handle(Server.java:561)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:334)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:104)
	at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:247)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:140)
	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)
	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:243)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:679)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:597)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.NullPointerException
	at org.nutz.dao.impl.sql.callback.QueryMapCallback.invoke(QueryMapCallback.java:21)
	at org.nutz.dao.impl.sql.NutSql.onAfter(NutSql.java:137)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runPreparedStatement(NutDaoExecutor.java:310)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:90)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:66)
	at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22)
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:64)
	at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:139)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:158)
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithTransaction(NutDaoRunner.java:104)
	... 61 more
1 回复

强制设置为查询:

sql.forceExecQuery();
添加回复
请先登陆
回到顶部