| Bug #1082 | Using ORDER BY RAND() and CONCAT causes wrong datatype | ||
|---|---|---|---|
| Submitted: | 18 Aug 2003 13:12 | Modified: | 16 Oct 2003 22:31 |
| Reporter: | alex | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 4.0.14nt | OS: | Microsoft Windows (Windows 2000 Advanced Server) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[22 Aug 2003 9:33]
MySQL Verification Team
I got in both cases results like strings.
[22 Aug 2003 9:37]
alex
This happens when using MySQLFront v2.5, and also when using MyODBC 2.5 on Windows 2000 Advanced Server.
[22 Aug 2003 11:25]
Sergei Golubchik
I did repeat it! thanks
[22 Aug 2003 12:27]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php No they won't be of type BINARY. Compare the queries: SELECT CONCAT(testing, 'anything at all') FROM test1 ORDER BY RAND() SELECT CONCAT(testing, 'anything at all') FROM test1 SELECT CONCATtesting FROM test1 To check whether the field is BINARY you should check not for field's type (which is FIELD_TYPE_BLOB for the first and last query and FIELD_TYPE_VARSTRING for the second one) but for (field->flag & BINARY_FLAG) - you'll see that for all three queries the field is not binary. http://www.mysql.com/doc/en/C_API_datatypes.html
[22 Aug 2003 12:28]
Sergei Golubchik
sorry for typo: the third query should be of course SELECT testing FROM test1
[22 Aug 2003 12:32]
alex
Well, that's great, but your own MyODBC plugin tells me they are binary, which means when i try to retrive the output from the recordset, it just gives me a bunch of crap in ASP.
[22 Aug 2003 13:20]
Sergei Golubchik
If you'd said it's MyODBC issue, it wouldn't take that many rounds :) Anyway, I'm assigning it to our MyODBC developer.
[6 Oct 2003 1:16]
Venu Anuganti
Hi !! I am not able to reproduce this. I tried from both 3.51.06 and .07, with 4.0.16. It just works as expected. Here is the test case .. /* Test for misc CONVERT bug #1082 */ static void t_convert(SQLHDBC hdbc, SQLHSTMT hstmt) { SQLRETURN rc; SQLINTEGER data_len; SQLCHAR data[50]; myheader("t_convert"); tmysql_exec(hstmt,"drop table t_convert"); rc = SQLTransact(NULL,hdbc,SQL_COMMIT); mycon(hdbc,rc); rc = tmysql_exec(hstmt,"CREATE TABLE t_convert(testing tinytext)"); mystmt(hstmt,rc); rc = tmysql_exec(hstmt,"INSERT INTO t_convert VALUES('record1')"); mystmt(hstmt,rc); rc = tmysql_exec(hstmt,"INSERT INTO t_convert VALUES('record2')"); mystmt(hstmt,rc); rc = SQLTransact(NULL,hdbc,SQL_COMMIT); mycon(hdbc,rc); rc = SQLFreeStmt(hstmt,SQL_CLOSE); mystmt(hstmt,rc); rc = tmysql_exec(hstmt,"SELECT CONCAT(testing, '-must be string') FROM t_convert ORDER BY RAND()"); mystmt(hstmt,rc); rc = SQLBindCol(hstmt,1,SQL_C_CHAR, &data, 100, &data_len); mystmt(hstmt,rc); rc = SQLFetch(hstmt); mystmt(hstmt,rc); myassert(strcmp(data,"record1-must be string") == 0 || strcmp(data,"record2-must be string") == 0); rc = SQLFetch(hstmt); mystmt(hstmt,rc); myassert(strcmp(data,"record1-must be string") == 0 || strcmp(data,"record2-must be string") == 0); rc = SQLFetch(hstmt); myassert( rc == SQL_NO_DATA); rc = SQLFreeStmt(hstmt,SQL_UNBIND); mystmt(hstmt,rc); rc = SQLFreeStmt(hstmt,SQL_CLOSE); mystmt(hstmt,rc); }
[17 Oct 2003 8:16]
alex
I'm also using MyISAM tables, and no transactions, does this help?
Description: When performing a query containing an ORDER BY RAND(), with a CONCAT on a text column, the datatype returned is BINARY rather than CHAR. This occurs with columns of type TINYTEXT, TEXT, VARCHAR, MEDIUMTEXT, and LONGTEXT. How to repeat: Execute these commands to see it happen (tested on 3 different servers) CREATE TABLE test1 (Testing TINYTEXT NOT NULL) INSERT INTO test1 (Testing) VALUES ('record 1') INSERT INTO test1 (Testing) VALUES ('record 2') SELECT CONCAT(testing, 'anything at all') FROM test1 ORDER BY RAND() The two records returned will be of type BINARY, instead of string. Removing the ORDER BY RAND() removes the problem. Suggested fix: Make it return the proper column type!