解析带括号里面的or条件无法解析 #279

boddiguo · 2017-07-06T03:37:12Z

Please answer these questions before submitting your issue. Thanks!

无法解析sql：
select * from (SELECT t.* FROM TABLE_XXX t where t.field1 is not null and (t.field1 < ? or t.field1 >= ?)) d where d.id=1

Which version of Sharding-Jdbc do you using?

1.5.0.M2

Expected behavior

正常解析

Actual behavior

com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingException: SQL syntax error, expected token is 'RIGHT_PAREN', actual token is 'IS', literals is 'is'.

at com.dangdang.ddframe.rdb.sharding.parsing.parser.AbstractParser.accept(AbstractParser.java:79)
at com.dangdang.ddframe.rdb.sharding.parsing.parser.statement.select.AbstractSelectParser.parseTable(AbstractSelectParser.java:291)
at com.dangdang.ddframe.rdb.sharding.parsing.parser.statement.select.AbstractSelectParser.parseFrom(AbstractSelectParser.java:280)
at com.dangdang.ddframe.rdb.sharding.parsing.parser.dialect.mysql.MySQLSelectParser.query(MySQLSelectParser.java:44)
at com.dangdang.ddframe.rdb.sharding.parsing.parser.statement.select.AbstractSelectParser.parse(AbstractSelectParser.java:76)
at com.dangdang.ddframe.rdb.sharding.parsing.SQLParsingEngine.parse(SQLParsingEngine.java:63)
at com.dangdang.ddframe.rdb.sharding.parsing.parser.statement.SelectStatementParserTest.parseWithoutCondition(SelectStatementParserTest.java:48)
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:497)
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.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
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.junit.runners.ParentRunner.run(ParentRunner.java:363)
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:51)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:237)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

Steps to reproduce the behavior

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

测试案例：
public final class SelectStatementParserTest extends AbstractStatementParserTest {

@Test
public void parseWithoutCondition() throws SQLException {
    ShardingRule shardingRule = createShardingRule();
    String sql = " select * from (SELECT"
        + "        t.*"
        + "        FROM `TABLE_XXX` t"
        + "        where t.`field1` is not null and (t.`field1` < ? or t.`field1` >= ?)) d "
        + "        where d.id=1";
    SQLStatement
        sqlStatement = new SQLParsingEngine(DatabaseType.MySQL, sql, shardingRule).parse();
    System.out.println(sqlStatement);
    Calendar calendar = Calendar.getInstance();
    calendar.add(Calendar.YEAR, -1);
    calendar.set(Calendar.MONTH, 4);
    List<Object> params = new ArrayList();
    params.add(calendar.getTime());
    calendar.set(Calendar.MONTH, 1);
    params.add(calendar.getTime());
    SQLRouteResult route = new ParsingSQLRouter(new ShardingContext(shardingRule, DatabaseType.MySQL, null))
        .route(sql, params, sqlStatement);
    Set<SQLExecutionUnit> executionUnits = route.getExecutionUnits();
    for (SQLExecutionUnit executionUnit : executionUnits) {
        System.out.println(executionUnit.getSql());
    }
}

}

The text was updated successfully, but these errors were encountered:

qingshunluo · 2017-07-11T11:34:18Z

不支持括号中带有多项or条件

Which version of Sharding-Jdbc do you using?

1.5.0.M1

Expected behavior

支持非分表字段多项or条件的解析

Error querying database. Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

The error may exist in file [/home/gzUp/tomcat-dev-1/webapps/notify-system-webapp/WEB-INF/classes/mappers/DBrefusedetailMapper.xml]

The error may involve DBrefusedetailMapper.selectCountByParam-Inline

The error occurred while setting parameters

SQL: select count(*) as totalCount, sum(CONVERT(trans_amount,DECIMAL)) as totalAcc from t_sysnotice_refusedetail where 1=1 and trans_init_time >= ? and trans_init_time <= ? and trans_type in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) and (cust_id = ? or expander_cust_id = ? or acquirer_cust_id = ? or operator_cust_id = ? or platform_cust_id = ? )

Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

2017-07-10 15:39:54.925 | [DubboServerHandler-10.0.0.184:20803-thread-196] ERROR com.unionpay.notify.dao.impl.RefuseTransDaoImpl - selectRefuseTrans Exception:nested exception is org.apache.ibatis.exceptions.PersistenceException:

Error querying database. Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

The error may exist in file [/home/gzUp/tomcat-dev-1/webapps/notify-system-webapp/WEB-INF/classes/mappers/DBrefusedetailMapper.xml]

The error may involve DBrefusedetailMapper.selectByParam-Inline

The error occurred while setting parameters

SQL: select * from t_sysnotice_refusedetail where 1=1 and trans_init_time >= ? and trans_init_time <= ? and trans_type in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) and (cust_id = ? or expander_cust_id = ? or acquirer_cust_id = ? or operator_cust_id = ? or platform_cust_id = ? ) order by trans_init_time DESC,id DESC limit ?,?

Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

terrymanu · 2017-07-12T02:40:59Z

不支持列表中已明确说明不支持or，不能用in代替的or在分库分表的查询是性能杀手，应避免使用。

qingshunluo · 2017-07-12T05:51:32Z

不进行分表的表中含有or条件，应该支持去默认的数据源

对于不进行分表的表中含有or条件，应该需要支持去默认数据源。否则使用sharding-jdbc后，对于不进行分表的表会出现很多不必要的限制。
请问，不这样实现是因为解析有困难还是有其他原因。

Error querying database. Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

The error may exist in file [/home/gzUp/tomcat-dev-1/webapps/notify-system-webapp/WEB-INF/classes/mappers/DBrefusedetailMapper.xml]

The error may involve DBrefusedetailMapper.selectCountByParam-Inline

The error occurred while setting parameters

SQL: select count(*) as totalCount, sum(CONVERT(trans_amount,DECIMAL)) as totalAcc from t_sysnotice_refusedetail where 1=1 and trans_init_time >= ? and trans_init_time <= ? and trans_type in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) and (cust_id = ? or expander_cust_id = ? or acquirer_cust_id = ? or operator_cust_id = ? or platform_cust_id = ? )

Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

terrymanu · 2017-07-14T12:26:21Z

Shariding-JDBC力图使用统一的行为反馈用户，而不是满足了xxx条件，xxx类型的SQL就可以使用，反之不行。这样的排列组合太多，让用户难于使用和掌握。这样用户的心思会放在如何配置才能支持何种SQL。
因此统一的行为是Sharding-JDBC的设计准则。

如果想同时使用一个不分库分表的数据源，可以将其放在另一个数据源中，与Sharidng-JDBC分别管理，比如使用spring支持的多数据源配置即是其中一个选择。

hadix-lin · 2017-09-22T09:28:18Z

我认为用"设计准则"来回复这个问题有点儿牵强.中间件的目标是为了简化业务开发人员的负担.任何一个业务系统都可能使用部分分表的策略,这是个基本需求.如果为了使用中间件不可避免要进行复杂的多数据源管理,这就增加负担了.

另外系统中可能使用了一些基于动态扫描的机制,扫描范围并不容易根据数据源划分.强制用户使用多数据源可能引发其他的易用性问题.

还有使用为什么分表就不支持OR了?�1.4.2还支持啊.

aaa87621990 · 2018-02-01T07:04:16Z

明显不合理，分开数据源，你来给我保证transcation哦，就算是底层可以用同一个transaction，也不符合设计原则，徒增开发人员代码，业务场景里不分表的table你完全可以在shardingjdbc内部自行绕开，直接交予数据库执行，为了一张分表，把99张部不分表的table全要按照你的模式来处理，你觉得合理吗？

alicgq · 2018-08-31T07:56:14Z

这解法太没追求了

hadix-lin · 2018-08-31T09:34:00Z

我觉得做工具支持,应该保证开发人员使用增强能力只引入低成本负担,使用原始能力不需要额外成本,且尽量保证提供原始能力.
例如sharding-jdbc提供的分表分库能力属于增强能力,可以让开发人员做额外的配置工作.但是开发人员要使用原始的方式访问单表时属于原始能力,因该不受增强功能的影响无负担使用.

youjiannuo · 2018-09-11T03:00:50Z

既然是一个第三方的工具，应该是给开发人员减少代码量，反而阉割了一些数据库自带的字段，给我们带来加大更大的逻辑处理，那你这样的第三方工具还有意义

terrymanu · 2018-09-11T03:34:06Z

this issue had fixed at #501, it had expired because it created more than one year. So I just lock it

boddiguo mentioned this issue Jul 6, 2017

fixed 子查询中带有多个括号，Or条件等sql无法解析的问题 #281

Closed

terrymanu added the type: bug label Jul 7, 2017

terrymanu assigned haocao and terrymanu and unassigned haocao Jul 10, 2017

terrymanu closed this as completed Jul 12, 2017

terrymanu added status: invalid and removed type: bug labels Jul 12, 2017

apache locked as resolved and limited conversation to collaborators Sep 11, 2018

clambertus unassigned terrymanu Jan 15, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

解析带括号里面的or条件无法解析 #279

解析带括号里面的or条件无法解析 #279

boddiguo commented Jul 6, 2017

qingshunluo commented Jul 11, 2017

terrymanu commented Jul 12, 2017

qingshunluo commented Jul 12, 2017

terrymanu commented Jul 14, 2017 •

edited

Loading

hadix-lin commented Sep 22, 2017

aaa87621990 commented Feb 1, 2018

alicgq commented Aug 31, 2018

hadix-lin commented Aug 31, 2018

youjiannuo commented Sep 11, 2018

terrymanu commented Sep 11, 2018 •

edited

Loading

解析带括号里面的or条件无法解析 #279

解析带括号里面的or条件无法解析 #279

Comments

boddiguo commented Jul 6, 2017

Which version of Sharding-Jdbc do you using?

Expected behavior

Actual behavior

Steps to reproduce the behavior

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

qingshunluo commented Jul 11, 2017

不支持括号中带有多项or条件

Which version of Sharding-Jdbc do you using?

Expected behavior

Error querying database. Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

The error may exist in file [/home/gzUp/tomcat-dev-1/webapps/notify-system-webapp/WEB-INF/classes/mappers/DBrefusedetailMapper.xml]

The error may involve DBrefusedetailMapper.selectCountByParam-Inline

The error occurred while setting parameters

Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

Error querying database. Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

The error may exist in file [/home/gzUp/tomcat-dev-1/webapps/notify-system-webapp/WEB-INF/classes/mappers/DBrefusedetailMapper.xml]

The error may involve DBrefusedetailMapper.selectByParam-Inline

The error occurred while setting parameters

Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

terrymanu commented Jul 12, 2017

qingshunluo commented Jul 12, 2017

不进行分表的表中含有or条件，应该支持去默认的数据源

Error querying database. Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

The error may exist in file [/home/gzUp/tomcat-dev-1/webapps/notify-system-webapp/WEB-INF/classes/mappers/DBrefusedetailMapper.xml]

The error may involve DBrefusedetailMapper.selectCountByParam-Inline

The error occurred while setting parameters

Cause: com.dangdang.ddframe.rdb.sharding.parsing.parser.exception.SQLParsingUnsupportedException: Not supported token 'OR'.

terrymanu commented Jul 14, 2017 • edited Loading

hadix-lin commented Sep 22, 2017

aaa87621990 commented Feb 1, 2018

alicgq commented Aug 31, 2018

hadix-lin commented Aug 31, 2018

youjiannuo commented Sep 11, 2018

terrymanu commented Sep 11, 2018 • edited Loading

terrymanu commented Jul 14, 2017 •

edited

Loading

terrymanu commented Sep 11, 2018 •

edited

Loading