Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

底层改写逻辑SQL后添加了ORDER BY key ASC, 未判断别名是否为关键字未加转义符导致了SQL异常 #419

Closed
Anricx opened this issue Oct 19, 2017 · 2 comments
Closed

底层改写逻辑SQL后添加了ORDER BY key ASC, 未判断别名是否为关键字未加转义符导致了SQL异常 #419

Anricx opened this issue Oct 19, 2017 · 2 comments
Labels
type: bug

Comments

@Anricx
Copy link

Anricx commented Oct 19, 2017

在聚合函数中别名为关键字key时，底层改写逻辑SQL后添加了ORDER BY key ASC, 未判断别名是否为关键字未加转义符导致了SQL异常！
应该是一个通病，底层在改写后拼接SQL应该主动转义。

Which version of Sharding-Jdbc do you using?

1.5.4.1

Expected behavior

正常返回结果

Actual behavior

17:23:54.166 INFO  [main] - Started ParkingRecordRepostoryTest in 21.433 seconds (JVM running for 23.256)
17:23:54.352 INFO  [main] - Logic SQL: SELECT
			DATE_FORMAT(pp.`result_time`, '%Y-%m') AS `key`,
			SUM(pp.`pay_value`) AS `value`
		FROM `_parking_payment` pp
		LEFT JOIN `_parking_order` po ON pp.`serial` = po.`self_order`
		 WHERE po.`status` = 1 AND
			pp.`result` = 1 AND
			DATE_FORMAT(pp.`result_time`, '%Y-%m') IN
				(
					DATE_FORMAT(?, '%Y-%m')
				,
					DATE_FORMAT(?, '%Y-%m')
				) 
		GROUP BY DATE_FORMAT(pp.`result_time`, '%Y-%m')
17:23:54.354 INFO  [main] - SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=_parking_payment, alias=Optional.of(pp)), Table(name=_parking_order, alias=Optional.of(po))]), conditions=Conditions(conditions={}), sqlTokens=[TableToken(beginPosition=100, originalLiterals=`_parking_payment`), TableToken(beginPosition=134, originalLiterals=`_parking_order`), com.dangdang.ddframe.rdb.sharding.parsing.parser.token.OrderByToken@2098d37d], parametersIndex=2)), containStar=false, selectListLastPosition=95, groupByLastPosition=410, items=[AggregationSelectItem(type=SUM, innerExpression=(pp.`pay_value`), alias=Optional.of(value), derivedAggregationSelectItems=[], index=-1), CommonSelectItem(expression=DATE_FORMAT(pp.result_time, %Y-%m), alias=Optional.of(key))], groupByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(DATE_FORMAT(pp.`result_time`, '%Y-%m')), type=ASC, nullOrderType=ASC, index=-1, alias=Optional.of(key))], orderByItems=[OrderItem(owner=Optional.absent(), name=Optional.of(DATE_FORMAT(pp.`result_time`, '%Y-%m')), type=ASC, nullOrderType=ASC, index=-1, alias=Optional.of(key))], limit=null, subQueryStatement=null)
17:23:54.354 INFO  [main] - Actual SQL: park_pay ::: SELECT
			DATE_FORMAT(pp.`result_time`, '%Y-%m') AS `key`,
			SUM(pp.`pay_value`) AS `value`
		FROM _parking_payment pp
		LEFT JOIN _parking_order po ON pp.`serial` = po.`self_order`
		 WHERE po.`status` = 1 AND
			pp.`result` = 1 AND
			DATE_FORMAT(pp.`result_time`, '%Y-%m') IN
				(
					DATE_FORMAT(?, '%Y-%m')
				,
					DATE_FORMAT(?, '%Y-%m')
				) 
		GROUP BY DATE_FORMAT(pp.`result_time`, '%Y-%m') ORDER BY key ASC  ::: [2017-01-01 00:00:00.0, 2016-12-01 00:00:00.0]

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key ASC' at line 14
### The error may exist in com/chinaroad/parking/persistence/mapper/ParkingPaymentMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT    DATE_FORMAT(pp.`result_time`, '%Y-%m') AS `key`,    SUM(pp.`pay_value`) AS `value`   FROM `_parking_payment` pp   LEFT JOIN `_parking_order` po ON pp.`serial` = po.`self_order`    WHERE po.`status` = 1 AND    pp.`result` = 1 AND    DATE_FORMAT(pp.`result_time`, '%Y-%m') IN     (      DATE_FORMAT(?, '%Y-%m')     ,      DATE_FORMAT(?, '%Y-%m')     )    GROUP BY DATE_FORMAT(pp.`result_time`, '%Y-%m')
### Cause: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key ASC' at line 14

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy65.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:137)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:75)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy72.statsByMonths(Unknown Source)
	at com.chinaroad.parking.persistence.mapper.ParkingRecordRepostoryTest.testStatsByMonth(ParkingRecordRepostoryTest.java:42)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:252)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:94)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:191)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key ASC' at line 14
### The error may exist in com/chinaroad/parking/persistence/mapper/ParkingPaymentMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT    DATE_FORMAT(pp.`result_time`, '%Y-%m') AS `key`,    SUM(pp.`pay_value`) AS `value`   FROM `_parking_payment` pp   LEFT JOIN `_parking_order` po ON pp.`serial` = po.`self_order`    WHERE po.`status` = 1 AND    pp.`result` = 1 AND    DATE_FORMAT(pp.`result_time`, '%Y-%m') IN     (      DATE_FORMAT(?, '%Y-%m')     ,      DATE_FORMAT(?, '%Y-%m')     )    GROUP BY DATE_FORMAT(pp.`result_time`, '%Y-%m')
### Cause: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key ASC' at line 14
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 35 more
Caused by: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key ASC' at line 14
	at com.dangdang.ddframe.rdb.sharding.executor.threadlocal.ExecutorExceptionHandler.handleException(ExecutorExceptionHandler.java:61)
	at com.dangdang.ddframe.rdb.sharding.executor.ExecutorEngine.execute(ExecutorEngine.java:129)
	at com.dangdang.ddframe.rdb.sharding.executor.ExecutorEngine.executePreparedStatement(ExecutorEngine.java:96)
	at com.dangdang.ddframe.rdb.sharding.executor.type.prepared.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:93)
	at com.dangdang.ddframe.rdb.sharding.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:140)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
	... 41 more
Caused by: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key ASC' at line 14
	at com.dangdang.ddframe.rdb.sharding.executor.threadlocal.ExecutorExceptionHandler.handleException(ExecutorExceptionHandler.java:61)
	at com.dangdang.ddframe.rdb.sharding.executor.ExecutorEngine.executeInternal(ExecutorEngine.java:181)
	at com.dangdang.ddframe.rdb.sharding.executor.ExecutorEngine.syncExecute(ExecutorEngine.java:155)
	at com.dangdang.ddframe.rdb.sharding.executor.ExecutorEngine.execute(ExecutorEngine.java:124)
	... 52 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key ASC' at line 14
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:494)
	at com.dangdang.ddframe.rdb.sharding.executor.type.prepared.PreparedStatementExecutor$3.execute(PreparedStatementExecutor.java:97)
	at com.dangdang.ddframe.rdb.sharding.executor.type.prepared.PreparedStatementExecutor$3.execute(PreparedStatementExecutor.java:93)
	at com.dangdang.ddframe.rdb.sharding.executor.ExecutorEngine.executeInternal(ExecutorEngine.java:175)
	... 54 more

Steps to reproduce the behavior

逻辑SQL:

SELECT
			DATE_FORMAT(pp.`result_time`, '%Y-%m') AS `key`,
			SUM(pp.`pay_value`) AS `value`
		FROM `_parking_payment` pp
		LEFT JOIN `_parking_order` po ON pp.`serial` = po.`self_order`
		 WHERE po.`status` = 1 AND
			pp.`result` = 1 AND
			DATE_FORMAT(pp.`result_time`, '%Y-%m') IN
				(
					DATE_FORMAT(?, '%Y-%m')
				,
					DATE_FORMAT(?, '%Y-%m')
				) 
		GROUP BY DATE_FORMAT(pp.`result_time`, '%Y-%m')

Please provide the reproduce example codes (such as github link) if possible.
terrymanu added a commit that referenced this issue Oct 20, 2017
@terrymanu
fix #419
8c112ed
@terrymanu
Copy link
Member

谢谢提供这么清晰的异常分析。
fixed at 2.0.0.M2

@terrymanu
Copy link
Member

目前的修改方式并非最优，是在改写时判断是否为关键字，是的话则加上转义符。
未来应该改为在解析时记录下原identifier，这样从兼容性和性能上都会好一些。
未来会再更新。

@terrymanu terrymanu self-assigned this Aug 8, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@Anricx @terrymanu