| Bug #118089 | COALESCE with BIT field may return wrong values | ||
|---|---|---|---|
| Submitted: | 29 Apr 14:56 | Modified: | 30 Apr 13:04 |
| Reporter: | 策 吕 | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.42, 8.4.5, 9.3.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[30 Apr 11:34]
MySQL Verification Team
Hello 策 吕, Thank you for the report and feedback. I quickly tried to check at my end but not seeing any issues. Am I missing something? Please let me know. -- import java.sql.*; public class Bug118089 { public static String bytesToHex(byte[] bytes) { StringBuilder result = new StringBuilder(); for (byte b : bytes) { result.append(String.format("%02x", b)); } return result.toString(); } public static void main(String[] args) { String url = "jdbc:mysql://xx.yy.zz.aa/test?user=ushastry"; String oburl = "jdbc:oceanbase://49.52.27.61:2881/test?user=root@test&password=1234"; try (Connection conn = DriverManager.getConnection(url)) { try (Statement stmt = conn.createStatement()) { DatabaseMetaData meta = conn.getMetaData(); System.out.println("MySQL Server: " + meta.getDatabaseProductVersion()); System.out.println("Driver Name & Ver: " + meta.getDriverName() + " " + meta.getDriverVersion()); stmt.execute("DROP TABLE IF EXISTS t0"); stmt.execute("DROP TABLE IF EXISTS t1"); stmt.execute("CREATE TABLE t0 (c0 BIT)"); stmt.execute("INSERT INTO t0 VALUES (0)"); //stmt.execute("SELECT COALESCE(t0.c0, t0.c1) FROM t0"); // 直接查询 COALESCE 的结果 System.out.println("Result of direct SELECT:"); try (ResultSet rs = stmt.executeQuery("SELECT (COALESCE(t0.c0)) AS c0 FROM t0")) { while (rs.next()) { byte[] result = rs.getBytes("c0"); System.out.println("c0 = 0x" + bytesToHex(result)); } } stmt.execute("CREATE TABLE t1 AS (SELECT (COALESCE(t0.c0)) AS c0 FROM t0)"); System.out.println("Result from t1:"); try (ResultSet rs = stmt.executeQuery("SELECT * FROM t1")) { while (rs.next()) { byte[] result = rs.getBytes("c0"); System.out.println("c0 = 0x" + bytesToHex(result)); } } } } catch (SQLException e) { } } } -- cd /Users/umshastr/Work/Connectors/ConnectorJ/Bug118089; JAVA_HOME=/opt/homebrew/Cellar/openjdk@17/17.0.15/libexec/openjdk.jdk/Contents/Home "/Applications/Apache NetBeans.app/Contents/Resources/netbeans/java/maven/bin/mvn" -Dexec.vmArgs= "-Dexec.args=${exec.vmArgs} -classpath %classpath ${exec.mainClass} ${exec.appArgs}" -Dexec.appArgs= -Dexec.mainClass=Bug118089 -Dexec.executable=/opt/homebrew/Cellar/openjdk@17/17.0.15/libexec/openjdk.jdk/Contents/Home/bin/java --no-transfer-progress process-classes org.codehaus.mojo:exec-maven-plugin:3.1.0:exec Scanning for projects... --------------------------< Oracle:Bug118089 >-------------------------- Building Bug118089 1.0-SNAPSHOT from pom.xml --------------------------------[ jar ]--------------------------------- --- resources:3.3.1:resources (default-resources) @ Bug118089 --- skip non existing resourceDirectory /Users/umshastr/Work/Connectors/ConnectorJ/Bug118089/src/main/resources --- compiler:3.13.0:compile (default-compile) @ Bug118089 --- Recompiling the module because of changed source code. Compiling 1 source file with javac [debug release 17] to target/classes --- exec:3.1.0:exec (default-cli) @ Bug118089 --- MySQL Server: 9.3.0 Driver Name & Ver: MySQL Connector/J mysql-connector-j-9.3.0 (Revision: 20ef8ee9eb4294a03858acccea0ddad3525f1ff9) Result of direct SELECT: c0 = 0x30 Result from t1: c0 = 0x30 ------------------------------------------------------------------------ BUILD SUCCESS ------------------------------------------------------------------------ Total time: 5.679 s Finished at: 2025-04-30T16:59:39+05:30 ------------------------------------------------------------------------ regards, Umesh
[30 Apr 11:51]
策 吕
Hi there,I'm reporting this bug for the following reasons: When running the same code,the MYSQL Connector/J returns a result of 0x30,whereas the OceanBase Connector/J returns a result of 0x0.After analyzing the situation,I believe the result returned by MYSQL does not comply with the expected behavior. The result shows that the value of the c0 field is 0x30. The COALESCE function is designed to return the first non-null value.Since c0 is a BIT data type,it is intended to store binary data rather than character data.When querying a BIT field,the getBytes method of the ResultSet object should return the byte data directly.In this specific case,COALESCE(t0.c0)is expected to return 0,which corresponds to the byte data 0x00.This is in contrast to the ASCII value of the character'0'(0x30),which should not be the correct result in this context.
[30 Apr 13:04]
MySQL Verification Team
Thank you for your feedback. I'll verify for now and leave it for development to decide on this. regards, Umesh
[7 May 18:27]
Axyoan Marcelo
Posted by developer: This seems to be a server bug, not a Connector/J one. Here is a testcase using MySQL command line tool (with --column-type-info): mysql> CREATE TABLE t0(c0 BIT); Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO t0 VALUES (0), (1); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT c0, COALESCE(c0) FROM t0; Field 1: `c0` Catalog: `def` Database: `test` Table: `t0` Org_table: `t0` Type: BIT Collation: binary (63) Length: 1 Max_length: 1 Decimals: 0 Flags: UNSIGNED Field 2: `COALESCE(c0)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: BIT Collation: binary (63) Length: 1 Max_length: 1 Decimals: 0 Flags: UNSIGNED +------------+----------------------------+ | c0 | COALESCE(c0) | +------------+----------------------------+ | 0x00 | 0x30 | | 0x01 | 0x31 | +------------+----------------------------+ 2 rows in set (0.00 sec) Both columns are still of type BIT, however COALESCE seems to change the value from 0x00 and 0x01 (raw byte data) to 0x30 and 0x31, the corresponding ascii values for the characters '0' and '1'. We can also analyze the protocol packets themselves: -- column count -- 0000 01 00 00 01 02 ..... -- field 1-- 0000 22 00 00 02 03 64 65 66 04 74 65 73 74 02 74 30 "....def.test.t0 0010 02 74 30 02 63 30 02 63 30 0c 3f 00 01 00 00 00 .t0.c0.c0.?..... 0020 10 20 00 00 00 00 . .... -- field 2 -- 0000 22 00 00 03 03 64 65 66 00 00 00 0c 43 4f 41 4c "....def....COAL 0010 45 53 43 45 28 63 30 29 00 0c 3f 00 01 00 00 00 ESCE(c0)..?..... 0020 10 20 00 00 00 00 . .... -- data row 1 -- 0000 04 00 00 04 01 00 01 30 .......0 -- data row 2 -- 0000 04 00 00 05 01 01 01 31 .......1 We can contrast this behavior with that of a column of type binary: mysql> CREATE TABLE t1(c0 BINARY); Query OK, 0 rows affected (0.09 sec) mysql> INSERT INTO t1 VALUES (b'0'), (b'1'); Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT c0, COALESCE(c0) FROM t1; Field 1: `c0` Catalog: `def` Database: `test` Table: `t1` Org_table: `t1` Type: STRING Collation: binary (63) Length: 1 Max_length: 1 Decimals: 0 Flags: BINARY Field 2: `COALESCE(c0)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: binary (63) Length: 1 Max_length: 1 Decimals: 31 Flags: BINARY +------------+----------------------------+ | c0 | COALESCE(c0) | +------------+----------------------------+ | 0x00 | 0x00 | | 0x01 | 0x01 | +------------+----------------------------+ 2 rows in set (0.00 sec) The values remain the same in this case.
[8 May 13:46]
MySQL Verification Team
Changing the synopsis
Description: I noticed the difference when I put the same test sample on OceanBase Connector/J and MYSQL Connector/J. MYSQL returned a result of 0x30 OceanBase returned a result of 0x00 How to repeat: public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/test?user=root&password=1234"; String oburl = "jdbc:oceanbase://49.52.27.61:2881/test?user=root@test&password=1234"; try (Connection conn = DriverManager.getConnection(url)) { try (Statement stmt = conn.createStatement()) { stmt.execute("DROP TABLE IF EXISTS t0"); stmt.execute("DROP TABLE IF EXISTS t1"); stmt.execute("CREATE TABLE t0 (c0 BIT)"); stmt.execute("INSERT INTO t0 VALUES (0)"); //stmt.execute("SELECT COALESCE(t0.c0, t0.c1) FROM t0"); // 直接查询 COALESCE 的结果 System.out.println("Result of direct SELECT:"); try (ResultSet rs = stmt.executeQuery("SELECT (COALESCE(t0.c0)) AS c0 FROM t0")) { while (rs.next()) { byte[] result = rs.getBytes("c0"); System.out.println("c0 = 0x" + bytesToHex(result)); } } stmt.execute("CREATE TABLE t1 AS (SELECT (COALESCE(t0.c0)) AS c0 FROM t0)"); System.out.println("Result from t1:"); try (ResultSet rs = stmt.executeQuery("SELECT * FROM t1")) { while (rs.next()) { byte[] result = rs.getBytes("c0"); System.out.println("c0 = 0x" + bytesToHex(result)); } } } } catch (SQLException e) { e.printStackTrace(); }