[
https://issues.apache.org/jira/browse/KYLIN-2813?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
JunQiangZhang updated KYLIN-2813:
---------------------------------
Description:
maping file:
<select id="***List" resultMap="***Map">
<![CDATA[
SELECT a.appversion,
a.os, a.recordtime
FROM *** AS a
WHERE a.recorddate=#{recordDate}
]]>
if test="os!=null">
AND a.os=#{os}
</if>
<if test="appVersion!=null">
AND a.appversion={appVersion}
</if>
ORDER BY a.recordtime DESC
<![CDATA[
LIMIT #{rowCount}
OFFSET #{offSet}
]]>
</select>
The rowCount and offSet is replace by params. when i query use this.
It cause:
2017-08-25 11:09:14,739 ERROR [Query c31e532a-e6c6-4af6-b5c8-7015a6082326-526]
service.QueryService:382 : Exception when execute sql
java.sql.SQLException: Error while preparing statement [SELECT a.appversion,
a.os, a.recordtime
FROM *** AS a
WHERE a.recorddate=?
AND a.os=?
ORDER BY a.recordtime DESC
LIMIT ?
OFFSET ?]
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:203)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:185)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:86)
at
org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:165)
at
org.apache.kylin.rest.service.QueryService.execute(QueryService.java:551)
at
org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:466)
at
org.apache.kylin.rest.service.QueryService.query(QueryService.java:153)
at
org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:357)
at
org.apache.kylin.rest.controller.QueryController.prepareQuery(QueryController.java:76)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: parse failed: Encountered "LIMIT ?" at
line 17, column 11.
Was expecting one of:
<EOF>
"LIMIT" <UNSIGNED_INTEGER_LITERAL> ...
"LIMIT" <DECIMAL_NUMERIC_LITERAL> ...
"LIMIT" <APPROX_NUMERIC_LITERAL> ...
"LIMIT" "ALL" ...
"OFFSET" ...
"FETCH" ...
"," ...
"NULLS" ...
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:750)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:632)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:602)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:196)
... 79 more
Caused by: org.apache.calcite.sql.parser.SqlParseException: Encountered "LIMIT
?" at line 17, column 11.
Was expecting one of:
<EOF>
... 83 more
Caused by: org.apache.calcite.sql.parser.impl.ParseException: Encountered
"LIMIT ?" at line 17, column 11.
Was expecting one of:
<EOF>
"LIMIT" <UNSIGNED_INTEGER_LITERAL> ...
"LIMIT" <DECIMAL_NUMERIC_LITERAL> ...
"LIMIT" <APPROX_NUMERIC_LITERAL> ...
"LIMIT" "ALL" ...
"OFFSET" ...
"FETCH" ...
"," ...
"NULLS" ...
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:21455)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:21278)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:843)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:185)
It seems that cannot pass the QueryService.execute conn.prepareStatement.
If I set the LIMIT and offset with a constant value. it will pass.
was:
maping file:
<select id="queryAPMList" resultMap="YanXuanAPMMap">
<![CDATA[
SELECT a.username, a.apmtype, a.appversion,
a.os, a.recordtime
FROM HOLMES.SRC_LOG_YANXUAN_APM AS a
WHERE a.recorddate=#{recordDate}
AND a.appid='11'
]]>
<if test="userName!=null">
AND a.username=#{userName}
</if>
<if test="apmType!=null">
AND a.apmtype=#{apmType}
</if>
<if test="os!=null">
AND a.os=#{os}
</if>
<if test="appVersion!=null">
AND a.appversion={appVersion}
</if>
ORDER BY a.recordtime DESC
<![CDATA[
LIMIT #{rowCount}
OFFSET #{offSet}
]]>
</select>
The rowCount and offSet is replace by params. when i query use this.
It cause:
2017-08-25 11:09:14,739 ERROR [Query c31e532a-e6c6-4af6-b5c8-7015a6082326-526]
service.QueryService:382 : Exception when execute sql
java.sql.SQLException: Error while preparing statement [SELECT a.username,
a.apmtype, a.appversion,
a.os, a.recordtime
FROM HOLMES.SRC_LOG_YANXUAN_APM AS a
WHERE a.recorddate=?
AND a.appid='11'
AND a.apmtype=?
AND a.os=?
ORDER BY a.recordtime DESC
LIMIT ?
OFFSET ?]
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:203)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:185)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:86)
at
org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:165)
at
org.apache.kylin.rest.service.QueryService.execute(QueryService.java:551)
at
org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:466)
at
org.apache.kylin.rest.service.QueryService.query(QueryService.java:153)
at
org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:357)
at
org.apache.kylin.rest.controller.QueryController.prepareQuery(QueryController.java:76)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: parse failed: Encountered "LIMIT ?" at
line 17, column 11.
Was expecting one of:
<EOF>
"LIMIT" <UNSIGNED_INTEGER_LITERAL> ...
"LIMIT" <DECIMAL_NUMERIC_LITERAL> ...
"LIMIT" <APPROX_NUMERIC_LITERAL> ...
"LIMIT" "ALL" ...
"OFFSET" ...
"FETCH" ...
"," ...
"NULLS" ...
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:750)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:632)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:602)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:196)
... 79 more
Caused by: org.apache.calcite.sql.parser.SqlParseException: Encountered "LIMIT
?" at line 17, column 11.
Was expecting one of:
<EOF>
... 83 more
Caused by: org.apache.calcite.sql.parser.impl.ParseException: Encountered
"LIMIT ?" at line 17, column 11.
Was expecting one of:
<EOF>
"LIMIT" <UNSIGNED_INTEGER_LITERAL> ...
"LIMIT" <DECIMAL_NUMERIC_LITERAL> ...
"LIMIT" <APPROX_NUMERIC_LITERAL> ...
"LIMIT" "ALL" ...
"OFFSET" ...
"FETCH" ...
"," ...
"NULLS" ...
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:21455)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:21278)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:843)
at
org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:185)
It seems that cannot pass the QueryService.execute conn.prepareStatement.
If I set the LIMIT and offset with a constant value. it will pass.
> SQL parse exception when 'LIMIT ?' is used in prepared statement
> ----------------------------------------------------------------
>
> Key: KYLIN-2813
> URL: https://issues.apache.org/jira/browse/KYLIN-2813
> Project: Kylin
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: v2.0.0
> Environment: kylin version:2.0.0
> org.mybatis.spring.boot:1.3.0
> Reporter: JunQiangZhang
> Assignee: liyang
>
> maping file:
> <select id="***List" resultMap="***Map">
> <![CDATA[
> SELECT a.appversion,
> a.os, a.recordtime
> FROM *** AS a
> WHERE a.recorddate=#{recordDate}
> ]]>
>
> if test="os!=null">
> AND a.os=#{os}
> </if>
> <if test="appVersion!=null">
> AND a.appversion={appVersion}
> </if>
> ORDER BY a.recordtime DESC
> <![CDATA[
> LIMIT #{rowCount}
> OFFSET #{offSet}
> ]]>
> </select>
> The rowCount and offSet is replace by params. when i query use this.
> It cause:
> 2017-08-25 11:09:14,739 ERROR [Query
> c31e532a-e6c6-4af6-b5c8-7015a6082326-526] service.QueryService:382 :
> Exception when execute sql
> java.sql.SQLException: Error while preparing statement [SELECT a.appversion,
> a.os, a.recordtime
> FROM *** AS a
> WHERE a.recorddate=?
> AND a.os=?
> ORDER BY a.recordtime DESC
>
> LIMIT ?
> OFFSET ?]
> at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:203)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:185)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:86)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:165)
> at
> org.apache.kylin.rest.service.QueryService.execute(QueryService.java:551)
> at
> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:466)
> at
> org.apache.kylin.rest.service.QueryService.query(QueryService.java:153)
> at
> org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:357)
> at
> org.apache.kylin.rest.controller.QueryController.prepareQuery(QueryController.java:76)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: java.lang.RuntimeException: parse failed: Encountered "LIMIT ?" at
> line 17, column 11.
> Was expecting one of:
> <EOF>
> "LIMIT" <UNSIGNED_INTEGER_LITERAL> ...
> "LIMIT" <DECIMAL_NUMERIC_LITERAL> ...
> "LIMIT" <APPROX_NUMERIC_LITERAL> ...
> "LIMIT" "ALL" ...
> "OFFSET" ...
> "FETCH" ...
> "," ...
> "NULLS" ...
>
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:750)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:632)
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:602)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement_(CalciteConnectionImpl.java:196)
> ... 79 more
> Caused by: org.apache.calcite.sql.parser.SqlParseException: Encountered
> "LIMIT ?" at line 17, column 11.
> Was expecting one of:
> <EOF>
> ... 83 more
> Caused by: org.apache.calcite.sql.parser.impl.ParseException: Encountered
> "LIMIT ?" at line 17, column 11.
> Was expecting one of:
> <EOF>
> "LIMIT" <UNSIGNED_INTEGER_LITERAL> ...
> "LIMIT" <DECIMAL_NUMERIC_LITERAL> ...
> "LIMIT" <APPROX_NUMERIC_LITERAL> ...
> "LIMIT" "ALL" ...
> "OFFSET" ...
> "FETCH" ...
> "," ...
> "NULLS" ...
>
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.generateParseException(SqlParserImpl.java:21455)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.jj_consume_token(SqlParserImpl.java:21278)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.SqlStmtEof(SqlParserImpl.java:843)
> at
> org.apache.calcite.sql.parser.impl.SqlParserImpl.parseSqlStmtEof(SqlParserImpl.java:185)
> It seems that cannot pass the QueryService.execute conn.prepareStatement.
> If I set the LIMIT and offset with a constant value. it will pass.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
