MySQL Bugs: #27862: Function return incorrect SQL_COLUMN

Bug #27862	Function return incorrect SQL_COLUMN_SIZE
Submitted:	16 Apr 2007 21:08	Modified:	3 Aug 2007 10:12
Reporter:	Todd Weiler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.14, 5.0.12	OS:	Windows
Assigned to:	Jim Winstead	CPU Architecture:	Any
Tags:	SQLAtribute

Description:
In previous versions functions in select statements would return character columns, example:

select concat(Dealer_Type,Name) from Dealers

In the ODBC log you can see the call to SQL_COLUMN_TYPE returning 12 (varchar)
and SQL_COLUMN_DISPLAY_SIZE returning a size that is the sum of the two column lengths being concatenated. SQL_COLUMN_PRECISION also returns the sum of the two column lengths being concatenated.

In 3.51.14 SQL_COLUMN_TYPE is returning -1, SQL_COLUMN_DISPLAY_SIZE and SQL_COLUMN_PRECISION are returning 16777215.

The front end tool I am using interprets this as a TEXT type rather than as a CHAR type - I believe this is based on SQL_COLUMN_DISPLAY_SIZE being so huge.

This appears to happen for all functions (if, case, etc.)

How to repeat:
Any query tath uses functions example:

select concat(Dealer_Type,Name) from Dealers

Hi Todd and thanks for your report. Unfortunately I think I know what this is about... Can you please paste otput of query in question from mysql cl client using -T option like this:
mysql -uuser -p -T your_db
select concat(Dealer_Type,Name) AS FullDesc from Dealers;

and check if field FullDesc has BINARY flag set.

Thanks for the quick reply, here are my results (binary flag does not appear to be set):

mysql> select concat(Dealer_Type,Name) AS FullDesc from Dealers;
Field   1:  `FullDesc`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     69
Max_length: 44
Decimals:   31
Flags:

+----------------------------------------------+
| FullDesc                                     |
+----------------------------------------------+
| InsuranceArmstrong & Quaile Insurance Agency |
| RegularArmstrong & Quaile Associates Inc.    |
+----------------------------------------------+
2 rows in set (0.00 sec)

Todd, thanks. I was on the wrong track... Back to analyzing.

Test case VS2005

Attachment: Bug27862.cpp (text/x-c++), 5.69 KiB.

Hi Todd.
Let me put it this way, 3.51 is wrong in Type (thus error in size). V5.0 shows 3x times the size but that's probably due to UNICODE suppport. Still, it can cause troubles. Also, v5.0 returns empty string for "SQL_DESC_BASE_COLUMN_NAME". 

Verified as described with test case attached. 3.51.14 is GA, c/ODBC 5 is 2007-04-11.

C:\mysql507\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.38-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bug27862 (
    -> Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Str1 VARCHAR(50) NOT NULL,
    -> Str2 VARCHAR(50) NOT NULL);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO bug27862 VALUES (NULL, "InsuranceArmstrong ", "& Quaile Insur
ance Agency"), (NULL, "RegularArmstrong & ", "Quaile Associates Inc.");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> quit
Bye

C:\mysql507\bin>mysql -uroot -T test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.38-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM bug27862;
Field   1:  `Id`
Catalog:    `def`
Database:   `test`
Table:      `bug27862`
Org_table:  `bug27862`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 1
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `Str1`
Catalog:    `def`
Database:   `test`
Table:      `bug27862`
Org_table:  `bug27862`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     50
Max_length: 19
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

Field   3:  `Str2`
Catalog:    `def`
Database:   `test`
Table:      `bug27862`
Org_table:  `bug27862`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     50
Max_length: 25
Decimals:   0
Flags:      NOT_NULL NO_DEFAULT_VALUE

+----+---------------------+---------------------------+
| Id | Str1                | Str2                      |
+----+---------------------+---------------------------+
|  1 | InsuranceArmstrong  | & Quaile Insurance Agency |
|  2 | RegularArmstrong &  | Quaile Associates Inc.    |
+----+---------------------+---------------------------+
2 rows in set (0.02 sec)

mysql> SELECT CONCAT(Str1, Str2) AS FullDesc FROM bug27862;
Field   1:  `FullDesc`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     100
Max_length: 44
Decimals:   31
Flags:      NOT_NULL

+----------------------------------------------+
| FullDesc                                     |
+----------------------------------------------+
| InsuranceArmstrong & Quaile Insurance Agency |
| RegularArmstrong & Quaile Associates Inc.    |
+----------------------------------------------+
2 rows in set (0.02 sec)

mysql> quit
Bye

3.51
SQL_DESC_BASE_COLUMN_NAME -> FullDesc
SQL_DESC_LABEL -> FullDesc
SQL_DESC_TYPE_NAME -> text
SQL_DESC_DISPLAY_SIZE  -> 16777215
SQL_DESC_LENGTH  -> 16777215
SQL_DESC_OCTET_LENGTH   -> 16777215

5.0
SQL_DESC_BASE_COLUMN_NAME ->
SQL_DESC_LABEL -> FullDesc
SQL_DESC_TYPE_NAME -> varchar
SQL_DESC_DISPLAY_SIZE  -> 300
SQL_DESC_LENGTH  -> 300
SQL_DESC_OCTET_LENGTH   -> 300

Appears to be ALMOST fixed in 3.51.15

The original example I gave:

select concat(Dealer_Type,Name) from Dealers)

now works as expected.

However the following still has the problem:

select concat_ws(' - ',date_format(Processing_Date,'%b-%d-%y'),Sequence) as seq from Transactions limit 1

Processing_Date is a Date column
Sequence is an int

Changing the select statement to include casting the int to a char gives the expected result:

select concat_ws(' - ',date_format(Processing_Date,'%b-%d-%y'),cast(Sequence as char)) as seq from Transactions limit 1

Note, expected result is: Mar-24-87 - 1

Thanks,
Todd.

3.51 is actually still returning the wrong value for SQL_DESC_LENGTH. The patch for Bug #19345 will fix the lengths returned in the CONCAT() example.

The bug demonstrated by the CONCAT_WS() code is a server bug, in which functions that combine strings and numerics are returned as binary data. This is Bug #12030.

The fix for this bug has been committed (along with the patch for Bug #19345), and will be in 3.51.18.

Thanks for the bug report.

A note has been added to the 3.51.18 changelog: 

The SQL_COLUMN_TYPE, SQL_COLUMN_DISPLAY and SQL_COLUMN_PRECISION values would be returned incorrectly when accessing character columns, especially those generated through concat().