You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/08/27 09:38:58 UTC
[GitHub] [shardingsphere] wushifeng opened a new issue #12056: V4.1.1 version, when use "LEFT JOIN" in sql, SQL rewrite error
wushifeng opened a new issue #12056:
URL: https://github.com/apache/shardingsphere/issues/12056
# reference to example under this project, settring t_oder to sharding below
<sharding:inline-strategy id="databaseStrategy" sharding-column="order_id" algorithm-expression="t_order_${order_id % 2}" />
<bean:properties id="properties">
123
</bean:properties>
<sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="order_id" props-ref="properties" />
<sharding:key-generator id="itemKeyGenerator" type="SNOWFLAKE" column="order_item_id" props-ref="properties" />
<sharding:data-source id="shardingDataSource">
<sharding:sharding-rule data-source-names="demo_ds_0" default-data-source-name="demo_ds_0">
sharding:table-rules
** <sharding:table-rule logic-table="t_order" table-strategy-ref="databaseStrategy" key-generator-ref="orderKeyGenerator" actual-data-nodes="demo_ds_0.t_order_${0..1}"/>**
</sharding:table-rules>
sharding:binding-table-rules
<sharding:binding-table-rule logic-tables="t_order"/>
</sharding:binding-table-rules>
sharding:broadcast-table-rules
<sharding:broadcast-table-rule table="t_address"/>
</sharding:broadcast-table-rules>
</sharding:sharding-rule>
sharding:props
true
</sharding:props>
</sharding:data-source>
# setting ordermapper.xml as below
SELECT t_order.order_id FROM t_order
LEFT JOIN t_order_item ON t_order_item.order_id = t_order.order_id
WHERE t_order.user_id = 1 and t_order.order_id in (2,3,4);
# when Search order, the error log is below：
[INFO ] 2021-08-26 18:08:15,572 --main-- [ShardingSphere-SQL] Logic SQL: SELECT t_order.order_id FROM t_order
LEFT JOIN t_order_item ON t_order_item.order_id = t_order.order_id
WHERE t_order.user_id = 1 and t_order.order_id in (2,3,4);
[INFO ] 2021-08-26 18:08:15,572 --main-- [ShardingSphere-SQL] SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1a0d96a5, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5a02bfe3), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5a02bfe3, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=22, distinctRow=false, projections=[ColumnProjection(owner=t_order, name=order_id, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@7a3e5cd3, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@3c79088e, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@4a37191a, containsSubquery=false)
[INFO ] 2021-08-26 18:08:15,572 --main-- [ShardingSphere-SQL] Actual SQL: demo_ds_0 ::: SELECT t_order_0.order_id FROM t_order_0
LEFT JOIN t_order_item ON t_order_item.order_id = t_order.order_id
WHERE t_order_0.user_id = 1 and t_order_0.order_id in (2,3,4);
[INFO ] 2021-08-26 18:08:15,572 --main-- [ShardingSphere-SQL] Actual SQL: demo_ds_0 ::: SELECT t_order_1.order_id FROM t_order_1
LEFT JOIN t_order_item ON t_order_item.order_id = t_order.order_id
WHERE t_order_1.user_id = 1 and t_order_1.order_id in (2,3,4);
[INFO ] 2021-08-26 18:08:15,595 --main-- [org.springframework.beans.factory.xml.XmlBeanDefinitionReader] Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
# but if change the SQL as below, everthing is ok
SELECT ord.order_id FROM t_order ord
LEFT JOIN t_order_item ON t_order_item.order_id = ord.order_id
WHERE ord.user_id = 1 and ord.order_id in (2,3,4);
# SQL REWITER ERROR
when there is "LEFT JOIN t_order_item ON t_order_item.order_id = t_order.order_id " in SQL, sql rewirte not change t_order.user_id to t_order_0.user_id
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12056: V4.1.1 version, when use "LEFT JOIN" in sql, SQL rewrite error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12056:
URL: https://github.com/apache/shardingsphere/issues/12056#issuecomment-937486511
This feature is already supported in the master branch, and you are welcome to test it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12056: V4.1.1 version, when use "LEFT JOIN" in sql, SQL rewrite error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12056:
URL: https://github.com/apache/shardingsphere/issues/12056#issuecomment-937486511
This feature is already supported in the master branch, and you are welcome to test it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #12056: V4.1.1 version, when use "LEFT JOIN" in sql, SQL rewrite error
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #12056:
URL: https://github.com/apache/shardingsphere/issues/12056
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wushifeng commented on issue #12056: V4.1.1 version, when use "LEFT JOIN" in sql, SQL rewrite error
Posted by GitBox <gi...@apache.org>.
wushifeng commented on issue #12056:
URL: https://github.com/apache/shardingsphere/issues/12056#issuecomment-907942429
If Use shardingsphere-5, sql gramamer file is too old:
for example: BaseRule.g4
In 5.0.0-beta version:
shardingsphere-5.0.0-beta\shardingsphere-sql-parser\shardingsphere-sql-parser-dialect\shardingsphere-sql-parser-mysql\src\main\antlr4\imports\mysql\BaseRule.g4
regularFunctionName
: IF | LOCALTIME | LOCALTIMESTAMP | REPLACE | INTERVAL | MOD
| DATABASE | SCHEMA | LEFT | RIGHT | DATE | DAY | GEOMETRYCOLLECTION
| LINESTRING | MULTILINESTRING | MULTIPOINT | MULTIPOLYGON | POINT | POLYGON
| TIME | TIMESTAMP | TIMESTAMP_ADD | TIMESTAMP_DIFF | DATE | CURRENT_TIMESTAMP | identifier
;
in 4.1.0-4.1.1 version:
shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/antlr4/imports/mysql/BaseRule.g4
regularFunctionName_ has many syntax.
So if use the sql below:
INSERT INTO TB_USR (END_DATE) VALUES ( STR_TO_DATE(?, '%Y-%m-%d %H:%i:%s.%f') )
4.1.0-4.1.1 version can work, but 5.0.0-beta not, how to deal with this situation?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #12056: V4.1.1 version, when use "LEFT JOIN" in sql, SQL rewrite error
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #12056:
URL: https://github.com/apache/shardingsphere/issues/12056
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #12056: V4.1.1 version, when use "LEFT JOIN" in sql, SQL rewrite error
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #12056:
URL: https://github.com/apache/shardingsphere/issues/12056#issuecomment-908027587
@strongduanmu Please have a look 👍
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12056: V4.1.1 version, when use "LEFT JOIN" in sql, SQL rewrite error
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12056:
URL: https://github.com/apache/shardingsphere/issues/12056#issuecomment-908043720
@wushifeng Thank you for your feedback, I will investigate this issue.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #12056: V4.1.1 version, when use "LEFT JOIN" in sql, SQL rewrite error
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #12056:
URL: https://github.com/apache/shardingsphere/issues/12056#issuecomment-907310225
@wushifeng
Thanks for your report, can you try the latest version 5.0.0-beta?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org