| Bug #31744 | UNION and implicit CAST causes character conversion problem | ||
|---|---|---|---|
| Submitted: | 22 Oct 2007 9:17 | Modified: | 19 Dec 2007 13:39 |
| Reporter: | Martin Kirchner | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.0.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[22 Oct 2007 9:17]
Martin Kirchner
[22 Oct 2007 9:26]
Tonci Grgin
Hi Martin and thanks for your report. I am sorry but there is a lot of info / data missing here... Can you please attach/provide: - MySQL server version / host OS - my.ini/my.cnf file used for starting server - JDK version - small but complete test case (incl. connect string!) - data dump for tables in question (preferably in format suitable for import via mysql cl client
[22 Oct 2007 10:53]
Martin Kirchner
My server version is 5.0.45 (community).
JDK: jdk1.5.0_06
Testcase:
final String select = "SELECT DISTINCT CHRISTIANNAME FROM m1.ADDRESS WHERE GGUID = 0x648E7A2F243736918DCCC3955DC6EE7C"
+ " UNION ALL "
+ "SELECT DISTINCT START_DT FROM m1.APPOINTMENT WHERE GGUID = 0x648E7A2F243736918DCCC3955DC6EE7C";
final String selectWithCast = "SELECT DISTINCT CHRISTIANNAME AS PLUSINFO FROM m1.ADDRESS WHERE GGUID = 0x648E7A2F243736918DCCC3955DC6EE7C"
+ " UNION ALL "
+ "SELECT DISTINCT CAST(START_DT AS CHAR) AS PLUSINFO FROM m1.APPOINTMENT WHERE GGUID = 0x648E7A2F243736918DCCC3955DC6EE7C";
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
Connection dbConn = null;
try {
dbConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/m1", "myUser", "myPassword");
final Statement statement = dbConn.createStatement();
final ResultSet rs = statement.executeQuery(select);
while (rs.next()) {
System.out.println("Without Cast: " + rs.getString(1));
}
final ResultSet rsWithCast = statement.executeQuery(selectWithCast);
while (rsWithCast.next()) {
System.out.println("With Cast: " + rsWithCast.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (dbConn != null) {
try {
dbConn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
[12 Dec 2007 15:10]
Florian Scandella
this is also a problem if you have a union over 2 same types of the same character encoding it it is the return value of a function call. it seems like union forgets the encoding/collation.
DROP TABLE IF EXISTS union_test;
CREATE TABLE union_test(
id integer,
abc varchar(100),
PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
insert into union_test(abc) values ('öäü');
delimiter |
DROP FUNCTION IF EXISTS get_text|
CREATE FUNCTION get_text(id integer) RETURNS VARCHAR(100) CHARACTER SET utf8
READS SQL DATA
BEGIN
DECLARE ret VARCHAR(100) CHARACTER SET utf8;
SELECT u.abc INTO ret FROM union_test u WHERE u.id=id;
RETURN ret;
END|
delimiter ;
select get_text(id) as name from union_test;
select get_text(id) as name from union_test union select get_text(id) as name from union_test;
select convert(get_text(id) using utf8) as name from union_test union select get_text(id) as name from union_test;
[12 Dec 2007 15:11]
Florian Scandella
the results for the last script .. +--------+ | name | +--------+ | öäü | +--------+ 1 row in set (0.01 sec) +------+ | name | +------+ | ��� | +------+ 1 row in set (0.01 sec) +--------+ | name | +--------+ | öäü | | | +--------+ 2 rows in set (0.01 sec)
[13 Dec 2007 6:50]
Tonci Grgin
Martin, I believe the source of this problem is described in Bug#28577 submitted by you. Let me check again.
[13 Dec 2007 12:52]
Tonci Grgin
Martin, Florian, can any of you try running your queries in cl client (mysql) please but with -T option? I would like to see the output.
[13 Dec 2007 21:00]
Florian Scandella
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 684532 Server version: 5.0.18 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> \. mysqlbug.sql Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.08 sec) Query OK, 1 row affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) Field 1: `name` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: latin1_swedish_ci (8) Length: 100 Max_length: 6 Decimals: 0 Flags: +--------+ | name | +--------+ | öäü | +--------+ 1 row in set (0.02 sec) Field 1: `name` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 100 Max_length: 3 Decimals: 0 Flags: BINARY +------+ | name | +------+ | ��� | +------+ 1 row in set (0.01 sec) Field 1: `name` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 300 Max_length: 6 Decimals: 0 Flags: +--------+ | name | +--------+ | öäü | | | +--------+ 2 rows in set (0.01 sec) mysql> Bye User time 0.01, System time 0.01 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 1111, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 88, Involuntary context switches 2
[13 Dec 2007 21:04]
Florian Scandella
ok, i just realized, it works on my local mysql installation (gentoo, mysql 5.0.44) .. so it seems my problem is allready fixed in newer versions. ----- Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.44-log Gentoo Linux mysql-5.0.44-r2 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> \. mysqlbug.sql Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 1 row affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Field 1: `name` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 300 Max_length: 6 Decimals: 0 Flags: +--------+ | name | +--------+ | öäü | +--------+ 1 row in set (0.00 sec) Field 1: `name` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 300 Max_length: 6 Decimals: 0 Flags: +--------+ | name | +--------+ | öäü | +--------+ 1 row in set (0.00 sec) Field 1: `name` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 900 Max_length: 6 Decimals: 0 Flags: +--------+ | name | +--------+ | öäü | +--------+ 1 row in set (0.00 sec)
[19 Dec 2007 13:39]
Tonci Grgin
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/ Explanation: Closing this as per Florian's last post.
