[I] Firebird Proxy loses the fractional part of DECIMAL input parameters [shardingsphere]

["makssent (via GitHub)" <gi...@apache.org>]

makssent opened a new issue, #40410:
URL: https://github.com/apache/shardingsphere/issues/40410

   ### Before reporting a bug
   
   - [x] I have searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
   - [x] I have read the [ShardingSphere documentation](https://shardingsphere.apache.org/document/current/en/overview).
   
   ### ShardingSphere version
   
   master, commit [`68f85339472`](https://github.com/apache/shardingsphere/commit/68f85339472dc0496c28a6177a1ba0c22c2c3c18)
   
   ### ShardingSphere project
   
   ShardingSphere-Proxy
   
   ### Expected behavior
   
   A DECIMAL(18,4) input parameter should have scale 4, and inserting 1.2345 should store 1.2345.
   
   ### Actual behavior
   
   The same operation was executed directly on the server and through the proxy.
   
   | Steps | Firebird server | Proxy |
   | --- | --- | --- |
   | Insert 1.2345 and 12.34 | Scales 4 and 2, stored 1.2345 and 12.34 | Scales 0 and 0, stored 1.0000 and 12.00 |
   
   The application receives no error while its stored data is rounded. The related #40273 concerns numeric literals returned by SELECT; this case concerns input parameters and stored data.
   
   ### Reason analysis
   
   [`FirebirdReturnColumnPacket#write`](https://github.com/apache/shardingsphere/blob/68f85339472dc0496c28a6177a1ba0c22c2c3c18/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/prepare/FirebirdReturnColumnPacket.java#L72-L74) always returns zero for scale. [`FirebirdBlrRowMetadata#parse`](https://github.com/apache/shardingsphere/blob/68f85339472dc0496c28a6177a1ba0c22c2c3c18/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/FirebirdBlrRowMetadata.java#L60-L65) skips the scale, and [`FirebirdInt8BinaryProtocolValue#read`](https://github.com/apache/shardingsphere/blob/68f85339472dc0496c28a6177a1ba0c22c2c3c18/database/protocol/dialect/firebird/src/main/java/org/apache/shardingsphere/database/protocol/firebird/packet/command/query/statement/execute/protocol/FirebirdInt8BinaryProtocolValue.java#L30-L32) reads an unscaled integ
 er.
   
   ### Steps to reproduce
   
   Use a Firebird-compatible server and Jaybird 6.0.7. Create the following table:
   
   ```sql
   CREATE TABLE T_DECIMAL (ID INTEGER NOT NULL PRIMARY KEY, D DECIMAL(18,4), N NUMERIC(4,2));
   ```
   
   Start ShardingSphere-Proxy with this configuration:
   
   ```yaml
   # global.yaml
   authority:
     users:
       - user: root@%
         password: root
     privilege:
       type: ALL_PERMITTED
   props:
     proxy-frontend-database-protocol-type: Firebird
   ```
   
   ```yaml
   # database-firebird.yaml
   databaseName: firebird_db
   dataSources:
     ds_0:
       url: jdbc:firebirdsql://<host>/<database file>?encoding=UTF8
       username: <user>
       password: <password>
   rules:
     - !SINGLE
       tables:
         - "*.*"
   ```
   
   Set the environment variables used by the example for the server and proxy connections, run the example once against each target and compare the results. Remove the rows with ID 913 and 914, or ID 3903 for the batch case, before repeating the case.
   
   ### Example code
   
   Set TARGET_URL, TARGET_USER and TARGET_PASSWORD for the selected target. For the numeric example also set SERVER_URL, SERVER_USER and SERVER_PASSWORD for the direct read.
   
   ```java
   import java.math.BigDecimal;
   import java.sql.*;
   
   public class NumericParameter {
       public static void main(String[] args) throws Exception {
           runCase();
       }
   
       // Direct: scales 4 and 2, stored 1.2345 and 12.34. Proxy: scales 0 and 0, stored 1.0000 and 12.00.
       static void runCase() throws Exception {
           try (Connection direct = DriverManager.getConnection(System.getenv("SERVER_URL"), System.getenv("SERVER_USER"), System.getenv("SERVER_PASSWORD"));
                Connection target = DriverManager.getConnection(System.getenv("TARGET_URL"), System.getenv("TARGET_USER"), System.getenv("TARGET_PASSWORD"))) {
               try (Statement statement = direct.createStatement()) {
                   statement.executeUpdate("DELETE FROM T_DECIMAL WHERE ID = 913");
               }
               try (PreparedStatement statement = target.prepareStatement("INSERT INTO T_DECIMAL (ID, D, N) VALUES (?, ?, ?)")) {
                   System.out.println("scales=" + statement.getParameterMetaData().getScale(2) + "," + statement.getParameterMetaData().getScale(3));
                   statement.setInt(1, 913);
                   statement.setBigDecimal(2, new BigDecimal("1.2345"));
                   statement.setBigDecimal(3, new BigDecimal("12.34"));
                   statement.executeUpdate();
               }
               try (Statement statement = direct.createStatement(); ResultSet result = statement.executeQuery("SELECT D, N FROM T_DECIMAL WHERE ID = 913")) {
                   while (result.next()) System.out.println("stored=" + result.getBigDecimal(1) + "," + result.getBigDecimal(2));
               }
           }
       }
   }
   ```


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

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