Skip to content

query [select * from aly_test where id =ALL(select 1);] success but explain failed #796

@irene-coming

Description

@irene-coming
  • dble version:
    5.6.29-dble-9.9.9.9-359645c-20181029082136

  • preconditions :
    no

  • configs:

schema.xml

<table name="aly_test" dataNode="dn1,dn2,dn3,dn4" rule="hash-four" />
  • steps:
    step1. execute prepare sqls with dble:
mysql> drop table if exists aly_test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table aly_test(id int, name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into aly_test values(1,'a'),(2,'b'),(5,null);
Query OK, 3 rows affected (0.00 sec)
step2. execute sql with dble:
mysql> explain select * from aly_test where id =ALL(select 1);
ERROR 1105 (HY000): table node buildOwn exception! Error:columnValue:{ALL_SUB_QUERY_RESULTS} Please eliminate any quote and non number within it.
mysql> select * from aly_test where id =ALL(select 1);
+------+------+
| id   | name |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)
  • expect result:
    1. step2. query "explain xxx;" success
  • real result:
    1.step2 "explain xxx" gets error
  • supplements:
    1.dble.log segment:
2018-11-01 02:38:43.906  INFO [BusinessExecutor0] (com.actiontech.dble.route.impl.DefaultRouteStrategy.parserSQL(DefaultRouteStrategy.java:37)) - routeNormalSqlWithAST
com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'aly_test', expect IDENTIFIER, actual IDENTIFIER pos 8, line 1, column 9, token IDENTIFIER aly_test
        at com.alibaba.druid.sql.parser.SQLParser.printError(SQLParser.java:284) ~[druid-1.1.10.jar:1.1.10]
        at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:426) ~[druid-1.1.10.jar:1.1.10]
        at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatement(SQLStatementParser.java:2738) ~[druid-1.1.10.jar:1.1.10]
        at com.actiontech.dble.route.impl.DefaultRouteStrategy.parserSQL(DefaultRouteStrategy.java:35) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.route.impl.DefaultRouteStrategy.routeNormalSqlWithAST(DefaultRouteStrategy.java:73) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:50) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.route.RouteService.route(RouteService.java:112) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.server.handler.ExplainHandler.getRouteResultset(ExplainHandler.java:159) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.server.handler.ExplainHandler.handle(ExplainHandler.java:65) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.server.ServerQueryHandler.query(ServerQueryHandler.java:73) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.net.FrontendConnection.query(FrontendConnection.java:319) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.net.FrontendConnection.query(FrontendConnection.java:335) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.net.handler.FrontendCommandHandler.handleData(FrontendCommandHandler.java:77) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:55) [dble-9.9.9.9.jar:?]
        at com.actiontech.dble.net.handler.FrontEndHandlerRunnable.run(FrontEndHandlerRunnable.java:44) [dble-9.9.9.9.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_121]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_121]
        at java.lang.Thread.run(Thread.java:745) [?:1.8.0_121]
2018-11-01 02:38:43.907  INFO [BusinessExecutor0] (com.actiontech.dble.server.handler.ExplainHandler.getRouteResultset(ExplainHandler.java:170)) - ServerConnection [id=8, schema=mytest2, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=mytest2]aly_test error:java.sql.SQLSyntaxErrorException: syntax error, error in :'aly_test', expect IDENTIFIER, actual IDENTIFIER pos 8, line 1, column 9, token IDENTIFIER aly_test

/label ~BUG

Metadata

Metadata

Assignees

Labels

Explainresolveproblem has been fixed by developerverifiedissue's resolve was verified by tester

Type

No type

Projects

No projects

Milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions