| Bug #40259 | implicit type casting for non string values is not allowed in v5.x connectors | ||
|---|---|---|---|
| Submitted: | 22 Oct 2008 15:40 | Modified: | 23 Oct 2008 7:04 |
| Reporter: | vathsalya sangam | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | v5.x | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[22 Oct 2008 15:40]
vathsalya sangam
[23 Oct 2008 7:04]
Tonci Grgin
Hi Vathsalya and thanks for your report. --<quote>-- Suggested fix: explictly convert non string values to CHAR type before performing any string functions on those values. ex:SELECT CONCAT(CAST(company_id AS CHAR),'/', CAST(location_id as CHAR)) AS compLoc from db_customer. --<quote>-- Again, as with your last report, I am puzzled by severity. This should also be S4 (feature request), right? --<quote>-- Description: the connector v5.1.6, doesn’t allow default type casting. Datetime, Integer and other types values should be explicitly converted to string ,if weare trying to use any string functions (ex :CONCAT)on them. v3.x does the type casting automatically. How to repeat: SELECT CONCAT(company_id,"/",location_id) as compLoc from db_customers. (if compnay_id and location_id are two integer or datetime values) --<quote>-- Old c/J did not respect BINARY flag in metadata thus it appeared to work correctly but only in this case. Your suggestion is not applicable always nor is it a good one. Check following test: (for 5.1 and up type: c:\mysql\bin>mysql -uroot -p --column-type-info test) c:\mysql\bin>mysql -uroot -p -T test Enter password: ******** Server version: 5.0.68-pb10-log MySQL Pushbuild Edition, build 10 mysql> SELECT CONCAT(12345, "/", 6789) AS Col1; Field 1: `Col1` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 10 Max_length: 10 Decimals: 31 Flags: NOT_NULL BINARY <<< +------------+ | Col1 | +------------+ | 12345/6789 | +------------+ 1 row in set (0.03 sec) So, result is a BINARY string without collation, which is a definition of BLOB... Ad hoc user queries do not allow connector to distinguish between "SHOW CREATE TABLE", for example, where it should treat BINARY as UTF8, and "SELECT varbinary_col FROM some_table", where it really should be binary. Similar problem can be observed above with function results (CONCAT) too. Now that we've established that this is a flaw in server metadata returned to client (although it is documented in manual) let's read manual for you once again. To bypass this shortcoming, c/J has special connection string property which you can set (as a matter of fact, all connectors have it!), functionsNeverReturnBlobs. Please set this to True and retest. You might also want to download c/J 5.1.7. Finally, this is *not* a bug but well documented and expected behaviour of both connector and server and thus !Bg.
