Bug #51322 | RIGHT() function returns a byte() ( system.byte[ ] ) | ||
---|---|---|---|
Submitted: | 19 Feb 2010 12:29 | Modified: | 19 Feb 2010 20:50 |
Reporter: | Jorge Bastos | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | Connector / NET | Severity: | S1 (Critical) |
Version: | 6.2.2 | OS: | Any (vs2008) |
Assigned to: | CPU Architecture: | Any |
[19 Feb 2010 12:29]
Jorge Bastos
[19 Feb 2010 12:34]
Jorge Bastos
Addicional info: If the field is a VARCHAR() it works OK, it seems that is only on INT() field types.
[19 Feb 2010 12:55]
Tonci Grgin
Hi Jorge and thanks for your report. Alas, you did not check how string functions work in server manual. If you had done that you would have seen that this is expected and intentional behavior: mysql> SELECT RIGHT(12345,3); Field 1: `RIGHT(12345,3)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) Length: 3 Max_length: 3 Decimals: 31 Flags: NOT_NULL BINARY +----------------+ | RIGHT(12345,3) | +----------------+ | 345 | +----------------+ 1 row in set (0.00 sec) So, server returns binary collation and sets the binary flag. To work around such issues (RIGHT is not the only function that can be used this way) all of the connector have "treat function results as string" option. In case of c/NET it is: FunctionsReturnString as described in http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html. Finally, this has been discussed hundreds of times throughout bugsdb and for each and every connector.
[19 Feb 2010 14:27]
Jorge Bastos
Hi Toncy, Thank you, understood and it's working! Allow me to remind you, in the past someone told me: "If it works on the mysql command line, it HAS to work on the connector" I guess you understand what I mean :) I reported it for that. Ok, it need the connection string to handle the value, no problem about. Thank you :)
[19 Feb 2010 19:19]
Tonci Grgin
No problem Jorge but as you can see it does not work in command line as you expected... However, this is not logical to me either and such casting raised a lot of discussion. Finally, all that matter is that you are able to continue your work :-)
[19 Feb 2010 20:50]
Jorge Bastos
Wait Wait, Toncy, It DOES work on the console: --- mysql> select right(numero,2) from mytable where numero=543; +-----------------+ | right(numero,2) | +-----------------+ | 43 | +-----------------+ 1 row in set (0.00 sec) mysql> --- Anyway I have the problem solved :)
[22 Feb 2010 7:17]
Tonci Grgin
Jorge, you are missing the point. What's important is the overall metadata on filed and those metadata, from connectors point of view, is inconclusive. Thus the need for additional connection string options in all of the connectors. mysql> SELECT RIGHT(12345,3); Field 1: `RIGHT(12345,3)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: VAR_STRING Collation: binary (63) <<<<<< Length: 3 Max_length: 3 Decimals: 31 Flags: NOT_NULL BINARY <<<<<<