| Bug #15867 | Union is causing at least one varchar column to be returned as binary | ||
|---|---|---|---|
| Submitted: | 19 Dec 2005 20:31 | Modified: | 14 Jun 2006 14:10 |
| Reporter: | Paul Rony | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.18-BK, 5.0.16 | OS: | Linux (Linux, Windows 2003) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[20 Dec 2005 14:03]
Valeriy Kravchuk
Thank you for a bug report. I have a much simpler test case, verified on 5.0.18-BK (ChangeSet@1.1981, 2005-12-15 02:08:52-03:00) on Linux:
mysql> create table tv(c1 varchar(100));
Query OK, 0 rows affected (0.01 sec)
mysql> create function f1(p1 varchar(100)) returns varchar(100) return p1;
Query OK, 0 rows affected (0.02 sec)
mysql> select f1('abc');
+-----------+
| f1('abc') |
+-----------+
| abc |
+-----------+
1 row in set (0.00 sec)
mysql> create table tv1 as select f1(c1) from tv union select f1(c1) from tv;
ERROR 1100 (HY000): Table 'tv1' was not locked with LOCK TABLES
mysql> create view tv1 as select f1(c1) from tv union select f1(c1) from tv;
Query OK, 0 rows affected (0.00 sec)
mysql> desc tv1;
+--------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------+------+-----+---------+-------+
| f1(c1) | varbinary(100) | YES | | NULL | |
+--------+----------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.18 |
+-----------+
1 row in set (0.00 sec)
In fact, even union may be not a problem here (although, take a look at bug #12185 also...). Look:
mysql> create view v2 as select f1('abc');
Query OK, 0 rows affected (0.00 sec)
mysql> desc v2;
+-----------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------------+------+-----+---------+-------+
| f1('abc') | varbinary(100) | YES | | NULL | |
+-----------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)
There was similar but report already, but I can't find it. So, I'll mark this one as verified.
[14 Jun 2006 14:10]
Evgeny Potemkin
mysql> create table tv(c1 varchar(100));
Query OK, 0 rows affected (0.00 sec)
mysql> create function f1(p1 varchar(100)) returns varchar(100) return p1;
Query OK, 0 rows affected (0.00 sec)
mysql> select f1('abc');
+-----------+
| f1('abc') |
+-----------+
| abc |
+-----------+
1 row in set (0.00 sec)
mysql> create view tv1 as select f1(c1) from tv union select f1(c1) from tv;
Query OK, 0 rows affected (0.00 sec)
mysql> desc tv1;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| f1(c1) | varchar(100) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> create view v2 as select f1('abc');
Query OK, 0 rows affected (0.00 sec)
mysql> desc v2;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| f1('abc') | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.22-debug |
+--------------+
1 row in set (0.00 sec)
Description: The following select statements work fine until they are used in a union. Then they return a binary value for the CONTACT_NAME. It may have something to do with the function in the output column. "Heather Morris" is returned as to 48656174686572204D6F72726973. If the fnFullName is replaced by its actual concat_ws operation, the result works fine. Create Function fnFullName(in_FIRST_NAME varchar(100), in_LAST_NAME varchar(100)) returns varchar(200) begin declare out_FULL_NAME varchar(200); set out_FULL_NAME = rtrim(concat_ws(N' ', in_FIRST_NAME, in_LAST_NAME)); return out_FULL_NAME; end How to repeat: 1) Create two tables 2) Combine with a union. 3) Use a user-defined function to combine two text values. select TASKS.ID as ACTIVITY_ID , N'Tasks' as ACTIVITY_TYPE , TASKS.NAME as ACTIVITY_NAME , convert(TASKS.STATUS using utf8) as STATUS , N'none' as DIRECTION , TASKS.DATE_DUE as DATE_DUE , TASKS.DATE_MODIFIED as DATE_MODIFIED , TASKS.PARENT_ID as ACCOUNT_ID , ACCOUNTS.NAME as ACCOUNT_NAME , CONTACTS.ID as CONTACT_ID , fnFullName(CONTACTS.FIRST_NAME, CONTACTS.LAST_NAME) as CONTACT_NAME , (case TASKS.STATUS when N'Not Started' then 1 when N'In Progress' then 1 when N'Pending Input' then 1 else 0 end) as IS_OPEN from ACCOUNTS inner join TASKS on TASKS.PARENT_ID = ACCOUNTS.ID and TASKS.PARENT_TYPE = N'Accounts' and TASKS.DELETED = 0 left outer join CONTACTS on CONTACTS.ID = TASKS.CONTACT_ID and CONTACTS.DELETED = 0 where ACCOUNTS.DELETED = 0 and ACCOUNTS.ID = '5c89f696-5f91-7d21-834b-437d810683c2' union all select MEETINGS.ID as ACTIVITY_ID , N'Meetings' as ACTIVITY_TYPE , MEETINGS.NAME as ACTIVITY_NAME , convert(MEETINGS.STATUS using utf8) as STATUS , N'none' as DIRECTION , MEETINGS.DATE_START as DATE_DUE , MEETINGS.DATE_MODIFIED as DATE_MODIFIED , MEETINGS.PARENT_ID as ACCOUNT_ID , ACCOUNTS.NAME as ACCOUNT_NAME , CONTACTS.ID as CONTACT_ID , fnFullName(CONTACTS.FIRST_NAME, CONTACTS.LAST_NAME) as CONTACT_NAME , (case MEETINGS.STATUS when N'Planned' then 1 else 0 end) as IS_OPEN from ACCOUNTS inner join MEETINGS on MEETINGS.PARENT_ID = ACCOUNTS.ID and MEETINGS.PARENT_TYPE = N'Accounts' and MEETINGS.DELETED = 0 left outer join MEETINGS_CONTACTS on MEETINGS_CONTACTS.MEETING_ID = MEETINGS.ID and MEETINGS_CONTACTS.DELETED = 0 left outer join CONTACTS on CONTACTS.ID = MEETINGS_CONTACTS.CONTACT_ID and CONTACTS.DELETED = 0 where ACCOUNTS.DELETED = 0 and ACCOUNTS.ID = '5c89f696-5f91-7d21-834b-437d810683c2'