| Bug #12962 | Type of column chages if the result is ordered or not | ||
|---|---|---|---|
| Submitted: | 3 Sep 2005 6:22 | Modified: | 30 Nov 2009 17:37 |
| Reporter: | x y | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 4.1.14 | OS: | Linux (Linux & Windows) |
| Assigned to: | CPU Architecture: | Any | |
[4 Sep 2005 10:46]
Valeriy Kravchuk
Simplified (C, not C++) test case
Attachment: 12962.c (text/x-csrc), 1.58 KiB.
[4 Sep 2005 10:51]
Valeriy Kravchuk
I tried to repeat the problem you described using test program in C (see Files), with both 4.0.15-BK and 5.0.13-BK on Linux, with both 5.0.13 and 4.1.15 client libraries, with no luck: [openxs@Fedora 5.0]$ ./12962 Client info: 5.0.13-beta Server info: 4.1.15 type=253 length=259 decimals=31 type=253 length=259 decimals=31 [openxs@Fedora 5.0]$ ./12962 Client info: 5.0.13-beta Server info: 5.0.13-beta type=253 length=259 decimals=31 type=253 length=259 decimals=31 [openxs@Fedora 5.0]$ ./12962 Client info: 4.1.15 Server info: 4.1.15 type=253 length=259 decimals=31 type=253 length=259 decimals=31 Types of both queries results are the same, as you can see.
[4 Sep 2005 14:03]
x y
Chaged the sample from so that the error is reproducable.
Attachment: 12963.c (text/x-csrc), 1.47 KiB.
[4 Sep 2005 14:06]
x y
With the sample form Valeriy Kravchuk it works for me to. If I change the sample (see file 12963.c) the error is reproducable egain. I get the following output: Client info: 4.1.14 Server info: 4.1.14-standard-log type=253 length=259 decimals=31 type=252 length=259 decimals=0
[30 Nov 2009 17:37]
Valeriy Kravchuk
Looks fixed to me in 5.0.89 and 6.0.14:
77-52-7-73:6.0-codebase openxs$ bin/mysql --column-type-info -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 6.0.14-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE t1 (id INT(11) NOT NULL, country VARCHAR(255) DEFAULT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.40 sec)
mysql> INSERT INTO t1 VALUES (1, 'Schweiz');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> CREATE TABLE t2 (id INT(11) NOT NULL DEFAULT 0) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO t2 VALUES (1);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT CONCAT('Hund', t1.country) AS result FROM t2 INNER JOIN t1 ON (t2.id =
-> t1.id);
Field 1: `result`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: utf8_general_ci (33)
Length: 777
Max_length: 11
Decimals: 31
Flags:
+-------------+
| result |
+-------------+
| HundSchweiz |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT('Hund', t1.country) AS result FROM t2 INNER JOIN t1 ON (t2.id = t1.id)
-> ORDER BY t1.id;
Field 1: `result`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: utf8_general_ci (33)
Length: 777
Max_length: 11
Decimals: 0
Flags:
+-------------+
| result |
+-------------+
| HundSchweiz |
+-------------+
1 row in set (0.00 sec)
Description: I execute the folowing query: SELECT CONCAT('Hund', `addresses`.`Country`) AS `Result` FROM `customers2addresses` INNER JOIN `addresses` ON (`customers2addresses`.`AddressID` = `addresses`.`ID`) ORDER BY `addresses`.`ID` The type of the column Result reported by libmysql is MYSQL_TYPE_BLOB. If I change any of the following things in the query the type chages to MYSQL_TYPE_VAR_STRING: * remove the ORDER BY * remove 'Hund' from the concat * remove the INNER JOIN The result type should never change. How to repeat: 1. Create a DB with the following script: DROP DATABASE IF EXISTS `Addresses`; CREATE DATABASE `Addresses`; USE `Addresses`; CREATE TABLE `addresses` ( `ID` int(11) NOT NULL, `Country` varchar(255) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `customers2addresses` ( `AddressID` int(11) NOT NULL default '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `addresses` (`ID`, `Country`) VALUES (1,'Schweiz'); INSERT INTO `customers2addresses` (`AddressID`) VALUES (1); COMMIT; 2. Run this code: #include <winsock2.h> #include <mysql.h> #include <iostream> using namespace std; int main(int argc, char* argv[]) { st_mysql* plMySQL = mysql_init(0); plMySQL = mysql_real_connect(plMySQL, "sql.office.tasix.ch", "GoferUser", "Gofer", "Addresses", 0, 0, 0); if(plMySQL == 0) { return -1; } { int alResult = mysql_query(plMySQL, "\ SELECT \ CONCAT('Hund', `addresses`.`Country`) AS `Result` \ FROM \ `customers2addresses` \ INNER JOIN `addresses` ON (`customers2addresses`.`AddressID` = `addresses`.`ID`)"); MYSQL_RES* plResult = mysql_store_result(plMySQL); MYSQL_FIELD* plField = mysql_fetch_field(plResult); cout << "field type: " << plField->type << "\n"; mysql_free_result(plResult); } { int alResult = mysql_query(plMySQL, "\ SELECT \ CONCAT('Hund', `addresses`.`Country`) AS `Result` \ FROM \ `customers2addresses` \ INNER JOIN `addresses` ON (`customers2addresses`.`AddressID` = `addresses`.`ID`) \ ORDER BY \ `addresses`.`ID`"); MYSQL_RES* plResult = mysql_store_result(plMySQL); MYSQL_FIELD* plField = mysql_fetch_field(plResult); cout << "field type: " << plField->type << "\n"; mysql_free_result(plResult); } mysql_close(plMySQL); return 0; } The output will be: field type: 253 field type: 252 Suggested fix: Make sure the result type stays the same.