MySQL Bugs: #38387: DB function TRIM() returns byte[]

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:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	mysql-connector-java-5.1.6	OS:	Any
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
Connector-j 5.1.6's ResultSet.getObject returns a byte[] for following:

SELECT TRIM(rowid) FROM tbl

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");
rs.next();
System.out.println(rs.getObject(1).getClass().getName());

Suggested fix:
n/a, or verify that functionsNeverReturnBlobs indeed works so that backwards compatibility can be maintained.

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.

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)

mysql>

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(?).

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.

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.

There are numerous bug reports about almost all functions, like Bug#34068 and so on.

Adding more related reports as a reference: Bug#28875.

Ken,

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.

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.

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.

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.

This is related to Serg's comment in Bug#24886. Waiting on Mark.

Mail sent to Mark & Jess as well as to Jim regarding Bug#27475.

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?

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.

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.

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.

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).

"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.

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:

SELECT TRIM(rowid) FROM tbl

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.