| Bug #28577 | Server loosing metadata in UNION | ||
|---|---|---|---|
| Submitted: | 22 May 2007 6:23 | Modified: | 13 Jan 2008 12:48 |
| Reporter: | Martin Kirchner | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.38BK + 5.0.41 + 5.0.27 | OS: | Any |
| Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[22 May 2007 14:55]
Tonci Grgin
Hi Martin and thanks for your report. This is actually a server bug, you can check in cl client by adding -T when invoking it.
C:\mysql507\bin>mysql -uroot -T test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.38-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `appointment0` (
-> `GGUID` binary(16) NOT NULL,
-> `Keyword` varchar(40) default NULL,
-> PRIMARY KEY (`GGUID`),
-> KEY `IDX_Keyword41` (`Keyword`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.13 sec)
mysql> SELECT GGUID FROM Appointment0 UNION SELECT GGUID FROM Appointment0;
Field 1: `GGUID`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: STRING
Collation: binary (63)
Length: 16
Max_length: 0
Decimals: 0
Flags: NOT_NULL BINARY
0 rows in set (0.03 sec)
mysql>
[23 May 2007 8:57]
Martin Kirchner
Thanks for verifying the bug and passing it to the server development. Today I tried version 5.0.41 and got the same results. Best regards Martin
[23 May 2007 12:37]
Tonci Grgin
Martin, thanks for your interest in MySQL.
[1 Jun 2007 11:15]
Evgeny Potemkin
mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.44-debug | +--------------+ 1 row in set (0.01 sec) mysql> create table t1(f1 binary (16), f2 char(16)); Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; Field 1: `f1` Catalog: `def` Database: `test` Table: `t1` Org_table: `t1` Type: STRING Collation: binary (63) Length: 16 Max_length: 0 Decimals: 0 Flags: BINARY Field 2: `f2` Catalog: `def` Database: `test` Table: `t1` Org_table: `t1` Type: STRING Collation: latin1_swedish_ci (8) Length: 16 Max_length: 0 Decimals: 0 Flags: 0 rows in set (0.00 sec) mysql> select * from t1 union select * from t1; Field 1: `f1` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: binary (63) Length: 16 Max_length: 0 Decimals: 0 Flags: BINARY Field 2: `f2` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: latin1_swedish_ci (8) Length: 16 Max_length: 0 Decimals: 0 Flags: 0 rows in set (0.00 sec)
[1 Jun 2007 11:20]
Evgeny Potemkin
BINARY fields internally represented as STRING fields with the BINARY flag set and collation set to "binary". You can see that both the flag and the collation are kept in union. Thus either the bug was fixed or it isn't a server bug.
[6 Jun 2007 6:34]
Martin Kirchner
Hi all! I got the same result like Evgeny. The BINARY flag is set. I tried the current JDBC-connector (mysql-connector-java-5.0.6-bin.jar) and its still returned as CHAR. What can we do? Best regards Martin
[8 Jun 2007 8:05]
Tonci Grgin
Igor, I'll have to open another report for this obviously... mysql> CREATE TABLE `bug28577` ( -> `GGUID` CHAR (16) BINARY NOT NULL, -> `Keyword` varchar(40) default NULL, -> PRIMARY KEY (`GGUID`), -> KEY `IDX_Keyword41` (`Keyword`) -> ); Query OK, 0 rows affected (0.02 sec) mysql> select * from bug28577 union select * from bug28577; Field 1: `GGUID` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: latin1_swedish_ci (8) Length: 16 Max_length: 0 Decimals: 0 Flags: NOT_NULL BINARY This (Collation: latin1_swedish_ci (8)) is both wrong (logically and in regards of manual) and confusing. It disables connectors from trusting server on types returned. I was hoping we can deal with it here instead opening new report. This is a part of larger problem described in Bug#10491 which has been constantly ignored.
[8 Jun 2007 14:30]
Tonci Grgin
Bug#28974 has some more info added.
[10 Jun 2007 16:07]
Tonci Grgin
Although there are problems with metadata returned from server here truly we have c/J problem... C:\mysql507\bin>mysql -uroot -T test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.0.44-max-nt-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT GGUID FROM bug28577 UNION SELECT GGUID FROM bug28577; Field 1: `GGUID` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: binary (63) Length: 16 Max_length: 0 Decimals: 0 Flags: NOT_NULL BINARY Notice "Org_table: ``". Now in Field.Java ln. 720 we have boolean isOpaqueBinary() throws SQLException { // // Detect CHAR(n) CHARACTER SET BINARY which is a synonym for // fixed-length binary types // if (this.charsetIndex == 63 && isBinary() && (this.getMysqlType() == MysqlDefs.FIELD_TYPE_STRING || this.getMysqlType() == MysqlDefs.FIELD_TYPE_VAR_STRING)) { if (this.originalTableNameLength == 0) { return false; // Probably from function } which in turn returns FALSE to ResultSetMetaData, Ln 179 return getClassNameForJavaType(f.getSQLType(), f.isUnsigned(), f.getMysqlType(), f.isBinary() || f.isBlob(), f.isOpaqueBinary()); making the field of CHAR type as can be seen in attached test case. .Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Done. java.vm.version : 1.5.0_11-b03 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_11-b03 os.name : Windows XP os.version : null sun.management.compiler : HotSpot Client Compiler Connected to 5.0.44-max-nt-log 1 .fld: GGUID, MD: java.lang.String CHAR 1 F Time: 44,422 There was 1 failure: 1) testBug28577(testsuite.simple.TestBug28577)junit.framework.AssertionFailedError: expected:<-2> but was:<1> at testsuite.simple.TestBug28577.testBug28577(TestBug28577.java:36) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at testsuite.simple.TestBug28577.main(TestBug28577.java:49) FAILURES!!! Tests run: 1, Failures: 1, Errors: 0
[10 Jun 2007 16:09]
Tonci Grgin
Test case lates version of c/J 5.0 SVN
Attachment: TestBug28577.java (text/x-java), 1.97 KiB.
[11 Jun 2007 14:54]
Tonci Grgin
After careful review and much discussion I've decided that this is a server bug as there is no reason for server to alter metadata in a union that is only touching table columns (Notice "Org_table: ``" and Database... ). There is no way c/J can work around this short of parsing every single query that comes through the driver... Regarding my remarks in driver code, the driver can't possibly determine if this is a UNION or if the user called a function to get "GUUID". This is similar to Bug#24886 in the way that info is lost... C:\mysql507\bin>mysql -uroot -T test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.0.44-max-nt-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT GGUID FROM bug28577 UNION SELECT GGUID FROM bug28577; Field 1: `GGUID` Catalog: `def` Database: `` Table: `` Org_table: `` Type: STRING Collation: binary (63) Length: 16 Max_length: 0 Decimals: 0 Flags: NOT_NULL BINARY
[18 Jul 2007 12:47]
Martin Kirchner
Hello everybody! Sorry to bother you again. As it would be quite a lot of work for me to develop a workaround for that problem I'd like to ask you if this issue will be fixed soon. Can you make any comments on that? BTW: Is there a publicly available release plan for MySQL server versions? Thanks a lot! Best regards Martin
[12 Nov 2007 13:57]
Sergei Golubchik
I don't see a bug here. All the test cases above show correct behavior (in the server, at least). Please show one single test case that contains three SQL statements: 1. CREATE TABLE t1 ... 2. SELECT ... FROM t1 ... 3. same as 2 in a union where metadata (as shown by mysql -T) in the 3rd statement are different from the 2nd.
[12 Nov 2007 14:26]
Martin Kirchner
I get the following output: GGUID ColumnDisplaySize: 16 ColumnTypeName: BINARY ColumnType: -2 Precision: 16 Scale: 0 GGUID ColumnDisplaySize: 16 ColumnTypeName: CHAR ColumnType: 1 Precision: 16 Scale: 0
[13 Dec 2007 12:48]
Tonci Grgin
Running same test case attached here (TestBug28577) I am unable to reproduce the error any more:
- MySQL server 5.0.54BK on WinXP Pro SP2 localhost
- latest c/J 5.0 sources
Besides loosing metadata on original table/table/database I was unable to see any problem running test like Serg requested (i.e. no change of metadata occurred). However, c/J 5.0 code changed in offending function:
boolean isOpaqueBinary() throws SQLException {
//
// Detect CHAR(n) CHARACTER SET BINARY which is a synonym for
// fixed-length binary types
//
if (this.charsetIndex == 63 && isBinary()
&& (this.getMysqlType() == MysqlDefs.FIELD_TYPE_STRING ||
this.getMysqlType() == MysqlDefs.FIELD_TYPE_VAR_STRING)) {
if (this.originalTableNameLength == 0 && (
this.connection != null && !this.connection.versionMeetsMinimum(5, 0, 25))) {
return false; // Probably from function
}
// Okay, queries resolved by temp tables also have this 'signature',
// check for that
return !isImplicitTemporaryTable();
}
return (this.connection.versionMeetsMinimum(4, 1, 0) && "binary"
.equalsIgnoreCase(getCharacterSet()));
}
producing correct test output:
Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.
Done.
java.vm.version : 1.5.0_12-b04Connected to 5.0.54-max-nt-log
java.vm.vendor : Sun Microsystems Inc.
java.runtime.version : 1.5.0_12-b04
os.name : Windows XP
os.version : null
sun.management.compiler : HotSpot Client Compiler
1 .fld: GGUID, MD: [B BINARY -2
Georgi, Timour, Serg, seems all that could be done in c/J is done. If absence of Org_table/table/database metadata is expected/acceptable then I suggest to close this report (even though I don't think it is) as primary problem reported is solved.
[14 Dec 2007 19:59]
Paul DuBois
Accoding to Bug#30749, the db/table/org_table fields should be empty for UNION statements.
[14 Jan 2008 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Hello! I have a database table with a binary(16) field like this: CREATE TABLE `appointment0` ( `GGUID` binary(16) NOT NULL, `Keyword` varchar(40) default NULL PRIMARY KEY (`GGUID`), KEY `IDX_Keyword41` (`Keyword`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; If I retrive data from this table using the statement SELECT GGUID FROM Appointment0 UNION SELECT GGUID FROM Appointment0 it returns the ColumnTypeName CHAR (SqlType 1) with precision 16. If I retrive data from that table using the statement SELECT GGUID FROM Appointment0 it returns the ColumnTypeName BINARY (SqlType -2) with precision 16 as expected. Code example: Connection dbConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db", "user", "PW"); String sql = "SELECT GGUID FROM Appointment0 UNION SELECT GGUID FROM Appointment0"; Statement statement = dbConn.createStatement(); ResultSet rs = statement.executeQuery(sql); ResultSetMetaData metaData = rs.getMetaData(); System.out.println(metaData.getColumnName(1) + " " + metaData.getColumnTypeName(1) + " " + metaData.getColumnType(1) + " " + metaData.getPrecision(1)); How to repeat: Use UNION on a binary(16) field Suggested fix: binary(16) should be returned