[GitHub] [shardingsphere] sandynz edited a comment on issue #9127: `SELECT DISTINCT` SQL fails when useServerPrepStmts=true

[GitBox <gi...@apache.org>]

sandynz edited a comment on issue #9127:
URL: https://github.com/apache/shardingsphere/issues/9127#issuecomment-768224083


   I found a similar problem when PR integration test, e.g. `Integration Test / MySQL-Proxy with DB`, part of error output:
   ```
   Error:  assertExecute[proxy: db -> MySQL -> Placeholder -> SELECT SUM(DISTINCT order_id) FROM t_order WHERE order_id < 1100](org.apache.shardingsphere.test.integration.engine.it.dql.GeneralDQLIT)  Time elapsed: 0.007 s  <<< FAILURE!
   java.lang.AssertionError: 
   Size of actual result set is different with size of expected dat set rows.
   Expected: is <1>
        but: was <0>
   ```
   
   Steps to reproduce on local environment:
   
   1, Prepare configurations for proxy, copy config files from `shardingsphere-integration-test-suite/src/test/resrouces/docker/db/proxy/conf/` into `shardingsphere-proxy-bootstrap/src/main/resources/`
   - copy `config-db.yaml` into `config-sharding.yaml`
   - copy `server.yaml` into `server.yaml`
   
   2, Update `shardingsphere-integration-test-suite/src/test/resources/env/engine-env.properties`
   ```
   it.adapters=proxy
   
   #it.scenarios=db,tbl,dbtbl_with_replica_query,replica_query,shadow
   it.scenarios=db
   
   #it.databases=H2,MySQL,Oracle,SQLServer,PostgreSQL
   it.databases=MySQL
   ```
   
   Update `shardingsphere-integration-test-suite/src/test/resources/env/db/scenario-env.properties`, `mysql` and `proxy` related configuration, e.g.
   ```
   it.db.mysql.host=127.0.0.1
   it.db.mysql.port=3306
   it.db.mysql.username=root
   it.db.mysql.password=
   
   it.db.proxy.host=127.0.0.1
   it.db.proxy.port=3307
   ```
   
   3, Copy `org.apache.shardingsphere.proxy.Bootstrap.java` to `shardingsphere-integration-test-suite` as `ProxyBootstrapTest.java`, since `STANDARD_TEST` in `config-db.yaml` is a customized algorithm that is implemented in unit test
   
   4, Run `ProxyBootstrapTest.java`
   
   5, Prepare  an test client and start it, e.g. `TempTest.java`
   ```
   public class TempTest {
       
       public static void main(String[] args) throws Exception {
           String sql;
           sql = "SELECT SUM(DISTINCT order_id) s FROM t_order WHERE order_id < 1100"; // result missed in response
   //        sql = "SELECT SUM(order_id) s FROM t_order WHERE order_id < 1100"; //ok
   //        sql = "SELECT COUNT(DISTINCT order_id) s FROM t_order WHERE order_id < 1100"; //ok
           String jdbcUrl = "jdbc:mysql://localhost:3307/db?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&useLocalSessionState=true&characterEncoding=utf-8";
           try (Connection connection = DriverManager.getConnection(jdbcUrl, "root", "root");
                PreparedStatement statement = connection.prepareStatement(sql)) {
               try (ResultSet resultSet = statement.executeQuery()) {
                   while (resultSet.next()) {
                       System.out.println("c1=" + resultSet.getString(1));
                   }
               }
           }
       }
   }
   ```
   
   6, Run `GeneralDQLIT.java` to verify all test cases
   


----------------------------------------------------------------
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.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org