Bug in (SELECT ...) UNION (SELECT ...) ORDER BY ... in MySQL-4.1 ?

Hi,

How-to-repeat :

CREATE TABLE `t` (
`id` int(11) default NULL,
KEY `id` (`id`)
) TYPE=MyISAM;

With MySQL-4.0.x :

(SELECT t.id FROM t) UNION (SELECT t.id FROM t) ORDER BY t.id;
Empty set (0.00 sec)

=> This is OK

With MySQL-4.1 :

(SELECT t.id FROM t) UNION (SELECT t.id FROM t) ORDER BY t.id;
ERROR 1109: Unknown table 't' in order clause

=> This is a bug ?

However :

(SELECT t.id FROM t) UNION (SELECT t.id FROM t) ORDER BY id;
Empty set (0.00 sec)

works in MySQL-4.1 (and 4.0.x as well)

BTW, EXPLAIN result with MySQL-4.0.6 is strange :

mysql> EXPLAIN (SELECT t.id FROM t) UNION (SELECT t.id FROM t) ORDER BY
t.id;
+-------+--------+---------------+------+---------+------+-- ----+-----------
----------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+--------+---------------+------+---------+------+-- ----+-----------
----------+
| t | system | NULL | NULL | NULL | NULL | 0 | const row
not found |
| t | system | NULL | NULL | NULL | NULL | 0 | const row
not found |
| t | system | NULL | NULL | NULL | NULL | 0 | const row
not found |
+-------+--------+---------------+------+---------+------+-- ----+-----------
----------+
3 rows in set (0.00 sec)

Why three rows are printed when the ORDER BY is added ?

Regards,
Jocelyn



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13124@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in SELECT ... USING (a)

Jocelyn Fournier writes:
> Hi,
>
> How-to-repeat :
>
>
> Regards,
> Jocelyn

Thank you for your bug report involving queries with derived tables of
the type:

select * from (select 1 as a) a left join (select 2 as a) b using(a)


This is a patch that fixes a problem:

===== sql/sql_base.cc 1.134 vs edited =====
*** /tmp/sql_base.cc-1.134-891 Wed Nov 27 17:04:41 2002
--- edited/sql/sql_base.cc Thu Nov 28 20:36:40 2002
***************
*** 1894,1900 ****
const char *name=item->field_name;
uint length=(uint) strlen(name);

! if (table_name)
{ /* Qualified field */
bool found_table=0;
for (; tables ; tables=tables->next)
--- 1894,1900 ----
const char *name=item->field_name;
uint length=(uint) strlen(name);

! if (table_name && table_name[0])
{ /* Qualified field */
bool found_table=0;
for (; tables ; tables=tables->next)


--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13146@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in (SELECT ...) UNION (SELECT ...) ORDER BY ... in MySQL-4.1 ?

Jocelyn Fournier writes:
> Hi,
>
> How-to-repeat :
>
> CREATE TABLE `t` (
> `id` int(11) default NULL,
> KEY `id` (`id`)
> ) TYPE=MyISAM;
>
> With MySQL-4.0.x :
>
> (SELECT t.id FROM t) UNION (SELECT t.id FROM t) ORDER BY t.id;
> Empty set (0.00 sec)
>
> => This is OK
>
> With MySQL-4.1 :
>
> (SELECT t.id FROM t) UNION (SELECT t.id FROM t) ORDER BY t.id;
> ERROR 1109: Unknown table 't' in order clause
>
> => This is a bug ?

Yes, it is .

Thank you for your bug report. Here is a patch that fixes it:


/item.cc: 1.51 1.52 Sinisa 02/11/30 21:02:56 (modified, needs delta)

@@ -535,9 +535,12 @@
SELECT_LEX *last= 0;

Item **refer= (Item **)not_found_item;
+ SELECT_LEX *cursel=(SELECT_LEX *) thd->lex.current_select;
// Prevent using outer fields in subselects, that is not supported now
- if (thd->lex.current_select->linkage != DERIVED_TABLE_TYPE)
- for (SELECT_LEX *sl= thd->lex.current_select->outer_select();
+ if (cursel->linkage != DERIVED_TABLE_TYPE)
+ for (SELECT_LEX *sl= (cursel->get_master()->linkage == GLOBAL_OPTIONS_TYPE &&
+ cursel->get_master()->order_list.elements) ? cursel->select_lex()
+ : cursel->outer_select();
sl;
sl= sl->outer_select())
{


--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13168@lists.mysql.com
To unsubscribe, e-mail