使用表全名关联的JOIN解析不正确

[xuminwlt]

Please answer these questions before submitting your issue. Thanks!

Which version of Sharding-Jdbc do you using?

1.5.1

Expected behavior

92sql语法规范

@select("SELECT play.id as id, play.name as name, video_image.url as coverUrl , video_image.width as width, video_image.height as height FROM play left join video_image on play.cover_id = video_image.id WHERE play.id = #{id}")
得到列表结果

Actual behavior

报sql语法异常
### Error querying database.  Cause: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: java.sql.SQLSyntaxErrorException: Unknown column 'playplayer_id' in 'on clause'
### The error may exist in PlayMapper.java (best guess)
### The error may involve PlayMapper.getVideoPlay-Inline
### The error occurred while setting parameters
### SQL: SELECT play.id as id, play.name as name, video_image.url as coverUrl , video_image.width as width, video_image.height as height FROM play left join video_image on play.cover_id = video_image.id WHERE play.id = ?
### Cause: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: java.sql.SQLSyntaxErrorException: Unknown column 'playplayer_id' in 'on clause'
	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 org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
	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:434)
	... 39 more
Caused by: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: java.sql.SQLSyntaxErrorException: Unknown column 'playplayer_id' in 'on clause'
	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:109)
	at com.dangdang.ddframe.rdb.sharding.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:113)
	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)
	... 46 more
Caused by: com.dangdang.ddframe.rdb.sharding.exception.ShardingJdbcException: java.sql.SQLSyntaxErrorException: Unknown column 'playplayer_id' in 'on clause'
	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)
	... 57 more
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'playplayer_id' in 'on clause'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:686)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:663)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:653)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2041)
	at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1827)
	at com.mysql.cj.jdbc.PreparedStatement.execute(PreparedStatement.java:1156)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:494)
	at com.dangdang.ddframe.rdb.sharding.executor.type.prepared.PreparedStatementExecutor$3.execute(PreparedStatementExecutor.java:113)
	at com.dangdang.ddframe.rdb.sharding.executor.type.prepared.PreparedStatementExecutor$3.execute(PreparedStatementExecutor.java:109)
	at com.dangdang.ddframe.rdb.sharding.executor.ExecutorEngine.executeInternal(ExecutorEngine.java:175)
	... 59 more

Steps to reproduce the behavior

执行单元测试，输入id，解析出这个错误异常并没有这个字段。Unknown column 'playplayer_id' in 'on clause'

Please provide the reproduce example codes (such as github link) if possible.

@select("SELECT play.id as id, play.name as name, video_image.url as coverUrl , video_image.width as width, video_image.height as height FROM play left join video_image on play.cover_id = video_image.id WHERE play.id = #{id}")
VideoPlayDto getVideoPlay(@param("id") Long id);

使用另外一种89sql语法规范就可以了。

@Select("SELECT play.id as id, play.name as name, video_image.url as coverUrl , video_image.width as width, video_image.height as height FROM play, video_image WHERE play.cover_id = video_image.id and play.id = #{id}")

8-19更新：
已经打开show.sql

打印的sql如下：
Actual SQL: dbtbl_0 ::: SELECT play.id as id, play.name as name, video_image.url as coverUrl , video_image.width as width, video_image.height as height FROM play left join video_image on playplayer_id = video_image.id WHERE play.id = ? ::: [19]

mybatis配置的sql
SELECT play.id as id, play.name as name, video_image.url as coverUrl , video_image.width as width, video_image.height as height FROM play left join video_image on play.cover_id = video_image.id WHERE play.id = #{id}

[terrymanu]

这个异常是数据库抛出来的吧。Unknown column 'playplayer_id' in 'on clause'
检查一下建表语句吧。
好像报错的SQL信息和你贴出来的SQL不同，可以打开sql.show，将sharding-jdbc的info信息贴出来看看，是不是SQL改写时有错误

[xuminwlt]

好的，亮哥，今天晚些时候我调试一下。

亮哥已经提交了新的调试信息，发现应该是sql改写的bug

[terrymanu]

fixed at 1.5.2