Bug #38387 DB function TRIM() returns byte[]
Submitted: 25 Jul 2008 21:16 Modified: 23 Jun 2009 15:55
Reporter: Ken Johanson Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version:mysql-connector-java-5.1.6 OS:Any
Assigned to: Jess Balint CPU Architecture:Any

[25 Jul 2008 21:16] Ken Johanson
Connector-j 5.1.6's ResultSet.getObject returns a byte[] for following:


Where 'rowid' is a type: int(11) primary key auto_increment

While the use of trim() to effect a to-char conversion is not fully proper, I believe that TRIM (and does?) should return a char/varchar/clob type nonetheless, which would map to a java.lang.String. Instead a byte[] is returned.

The database version is MySQL 5.1.24-rc-log. Prior mysql-connector-java-3.1.12 did not have this problem.

Additionally, I added &functionsNeverReturnBlobs=true to my JDBC URL params but the return type still did not change.

How to repeat:
ResultSet rs = stmt.executeQuery("SELECT TRIM(rowid) FROM tbl");

Suggested fix:
n/a, or verify that functionsNeverReturnBlobs indeed works so that backwards compatibility can be maintained.
[25 Jul 2008 21:29] Ken Johanson
ResultSetMetaData.getColumnTypeName(int) reports the type as VARBINARY, which is expected to be a byte[] java type. But is there way I can verify the database is indeed returning metatadata indicating VARBINARY and not the expected VARCHAR or CLOB? If VARBINARY is indded indicated by the DB then I feel this is a significant database problem. Consider also:

SELECT ''||rowid FROM tbl

a byte array is still returned.
[25 Jul 2008 21:43] Mark Matthews
Seems like a server types bug to me...(from mysql --column-type-info)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66467
Server version: 5.1.25-rc MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table foo (pk int(11) primary key auto_increment);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into foo values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select trim(pk) from foo;
Field   1:  `trim(pk)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     11
Max_length: 1
Decimals:   31
Flags:      NOT_NULL BINARY 

| trim(pk) |
| 1        | 
1 row in set (0.06 sec)

[25 Jul 2008 22:20] Ken Johanson
If the 'type' being returned is a VAR_STRING, does that imply that the protocol is working OK but the driver is not returning the correct type? 

Also there remains an apparent issue of functionsNeverReturnBlobs not overriding the type(?).
[28 Jul 2008 16:12] Ken Johanson
Some further digging on the intended type for VAR_STRING, shows some conversation where the type was interpreted as binary, although it appears that STRING and VAR_STRING (char and varchar respectively) should map to java.lang.String, and BINARY VAR_BINARY would be byte[].

So with possible exception of the collation type, the server and protocol seem to be fine.

Changing to severity 'serious' because some O-R wrappers and Map/Tree APIs on top of JDBC will use the returned getObject() type as a hashtable key, and hashtable operations that expect String fail.
[29 Jul 2008 7:17] Tonci Grgin
Hi Ken and thanks for your report.

Quote from manual: "If you use a number in string context, the number automatically is converted to a BINARY string."
This is a grave issue for all of the connectors, please see details in Bug#10491 and especially Bug#24886.

However, I will ping Mark once again to see if he can provide a workaround until this is fixed in server.
[29 Jul 2008 7:42] Tonci Grgin
There are numerous bug reports about almost all functions, like Bug#34068 and so on.
[29 Jul 2008 7:49] Tonci Grgin
Adding more related reports as a reference: Bug#28875.
[29 Jul 2008 12:45] Mark Matthews

It's not just the type that determines the ultimate java type used by the JDBC driver. Also notice that the *server* is returning "Collation:  binary (63)" and "Flags:      NOT_NULL BINARY" in the metadata. This is the *same* metadata used for the VARBINARY type (there is no VARBINARY type passed over the wire, some types are determined by a combination of type, collation and/or flags).

Unfortunately, in this case, there is no information that allows the driver to determine that this isn't actually a VARBINARY.
[29 Jul 2008 14:25] Ken Johanson
Mark, can you reference the rules/spec that show how the flags and type together should be used to infer type? My first instinct is that the 'type' should always take precedence (especially if is more specific than... binary which ANY type can be coded-as); if type is reported as STRING or VAR_STRING then the type should be such, unless you can show that coding 'type' to have precedence will break something else.
[29 Jul 2008 16:02] Ken Johanson
Also could you please address the fact that functionsNeverReturnBlobs does not override the binary return, or please indicate if it not to work in the ResultSet.getObject() context.
[30 Jul 2008 7:48] Tonci Grgin
Ken, from what I see in code (Field.java):
if (this.mysqlType == MysqlDefs.FIELD_TYPE_BLOB) {
	    boolean isFromFunction = this.originalTableNameLength == 0;
"functionsNeverReturnBlobs" is introduced to fix specific problem with GROUP BY (I presume it's temporary table related which is totally different from your case) and it just doesn't help here as "SELECT TRIM(1)" just doesn't produce BLOB as oputput (sorry I trimmed down your test SQL to this.rs = this.stmt.executeQuery("SELECT TRIM(1) AS Rslt");).

Sidenote: System.out.println(this.rs.getString("Rslt")); works just fine.

Will have to leave this to Mark for final ruling.
[20 Feb 2009 13:07] Tonci Grgin
This is related to Serg's comment in Bug#24886. Waiting on Mark.
[10 Apr 2009 11:30] Tonci Grgin
Mail sent to Mark & Jess as well as to Jim regarding Bug#27475.
[10 Apr 2009 20:12] Ken Johanson
I actually had to try updating to mysql-connector-java-5.1.7 today to see if it would resolve bug#18719 (my newer OS and JVM now allow deadlock detection).

(I've been scraping by on mysql-connector-java-3.1.12 which doesn't have this problem with TRIM() and an int type). Of course in 5.1.7 this has not been reolved yet. 

Is there a consensus on if this is a server or driver or protocol bug? Who do we need to nudge for help?
[17 Apr 2009 10:20] Tonci Grgin
Ken, I tried getting more info on this from devs but no response so far. This is not a bug per-se as it is documented in server. The fact that connectors have problems with that particular implementation is of no relevance right now. However it will get fixed in server protocol sooner or later.

The main concern here, from connectors point of you, is massing of "dirty" code and various options that will become obsolete once protocol improves in this specific area. Thus the fact that 3.x works and new connector does not. This is only true in your particular case/application but you're missing many other things that do not work in 3.x.

I am leaving this in analyzing until I hear back from Mark or Jess.
[17 Apr 2009 10:22] Tonci Grgin
My last comment was referring to: "Of course in 5.1.7 this has not been reolved yet."

There is absolutely *nothing* to be fixed in new c/J that was "working" in old c/J! 3.1.x worked by chance in this particular example but it was *not* working correctly which you can check by trying c/ODBC, c/NET or any other connector.
[20 Apr 2009 11:06] Tonci Grgin
I stand corrected. Somewhere about Line 211 in Field.java things go wrong. For TRIM(IntValue) server returns VAR_STRING and not BLOB thus some checks in code fail. So it might be necessary to change the way "functionsNeverReturnBlobs" behaves or to change type-discovery completely.
However, one is free to fetch value with .GetString as in my private test case.

Assigning to Jess.
[20 Apr 2009 15:41] Ken Johanson
Tonci, that's GREAT news. I wish I had more time to test and conlcude this myself much earlier, I just don't have enough time. I am _VERY_ anxious to test a patch that can be applied to release-version source, or built-in to a distro (this seems worthy fix to go into distros), but NOT one applied to a beta code tree, since I need to run this on production systems (to test/resolve the other deadlock problem).
[28 May 2009 21:23] Mark Matthews
"functionsNeverReturnBlobs=true" now works for SQL functions that return binary/binary collation VAR_STRINGS.

Any fancier fixes need changes to the server and how it reports types.
[23 Jun 2009 15:55] Tony Bedford
An entry was added to the 5.1.8 changelog:

Using Connector/J 5.1.6 the method ResultSet.getObject returned a BYTE[] for following:


Where rowid had a type of INT(11) PRIMARY KEY AUTO_INCREMENT.

The expected return type was one of CHAR, VARCHAR, CLOB, however, a BYTE[] was returned.

Further, adding functionsNeverReturnBlobs=true to the connection string did not have any effect on the return type.