Bug in Union?!

Hi,

I'm not sure if this an error... if not it should be documented

how-to-repeat:

mysql> create table foo (bar int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> (select * from foo limit 1,1) union select * from foo limit 3,1;
Empty set (0.00 sec)

(select * from foo limit 1,1) union (select * from foo limit 3,1) works ok,
also (select * from foo limit 1,1) union (select * from foo) limit 3,1.


Regards

Georg






------------------------------------------------------------ ---------
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-thread13342@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in Union?!

Georg Richter writes:
> Hi,
>
> I'm not sure if this an error... if not it should be documented
>
> how-to-repeat:
>
> mysql> create table foo (bar int);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into foo values (1),(2),(3),(4),(5);
> Query OK, 5 rows affected (0.00 sec)
> Records: 5 Duplicates: 0 Warnings: 0
>
> mysql> (select * from foo limit 1,1) union select * from foo limit 3,1;
> Empty set (0.00 sec)
>
> (select * from foo limit 1,1) union (select * from foo limit 3,1) works ok,
> also (select * from foo limit 1,1) union (select * from foo) limit 3,1.
>
>
> Regards
>
> Georg
>

Hi!

I do not think that mixing UNION with and without braces should be
allowed. For many reasons, although standard is not clear on this
one.

Therefore, I will propose the following patch:

===== sql/sql_yacc.yy 1.209 vs edited =====
*** /tmp/sql_yacc.yy-1.209-23919 Wed Dec 18 16:26:45 2002
--- edited/sql/sql_yacc.yy Mon Dec 23 21:59:45 2002
***************
*** 1668,1679 ****
'(' SELECT_SYM select_part2 ')'
{
LEX *lex= Lex;
! SELECT_LEX_NODE * sel= lex->current_select;
if (sel->set_braces(1))
{
send_error(lex->thd, ER_SYNTAX_ERROR);
YYABORT;
}
/* select in braces, can't contain global parameters */
sel->master_unit()->global_parameters=
sel->master_unit();
--- 1668,1684 ----
'(' SELECT_SYM select_part2 ')'
{
LEX *lex= Lex;
! SELECT_LEX * sel= lex->current_select->select_lex();
if (sel->set_braces(1))
{
send_error(lex->thd, ER_SYNTAX_ERROR);
YYABORT;
}
+ if (sel->linkage == UNION_TYPE && !sel->master_unit()->first_select()->braces)
+ {
+ send_error(lex->thd, ER_SYNTAX_ERROR);
+ YYABORT;
+ }
/* select in braces, can't contain global parameters */
sel->master_unit()->global_parameters=
sel->master_unit();
***************
*** 1683,1693 ****
--- 1688,1704 ----
select_part2
{
LEX *lex= Lex;
+ SELECT_LEX * sel= lex->current_select->select_lex();
if (lex->current_select->set_braces(0))
{
send_error(lex->thd, ER_SYNTAX_ERROR);
YYABORT;
}
+ if (sel->linkage == UNION_TYPE && sel->master_unit()->first_select()->braces)
+ {
+ send_error(lex->thd, ER_SYNTAX_ERROR);
+ YYABORT;
+ }
}
union_clause
;
***************
*** 1695,1700 ****
--- 1706,1712 ----
select_part2:
{
LEX *lex=Lex;
+ SELECT_LEX * sel= lex->current_select->select_lex();
if (lex->current_select == &lex->select_lex)
lex->lock_option= TL_READ; /* Only for global SELECT */
mysql_init_select(lex);
***************
*** 2509,2515 ****
lex->current_select= unit->outer_select();
if (!($$= lex->current_select->
add_table_to_list(lex->thd, new Table_ident(unit), $5, 0,
! lex->lock_option)))
YYABORT;
};

--- 2521,2529 ----
lex->current_select= unit->outer_select();
if (!($$= lex->current_select->
add_table_to_list(lex->thd, new Table_ident(unit), $5, 0,
! lex->lock_option,(List *)0,
! (List *)0)))
!
YYABORT;
};


May be I fix this in 4.0 too, only fix will be different.


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

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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-thread13347@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in Union?!

Georg Richter writes:
> On Monday 23 December 2002 21:16, you wrote:
>
> >
> > Hi!
> >
> > I do not think that mixing UNION with and without braces should be
> > allowed. For many reasons, although standard is not clear on this
> > one.
> >
>
> Hello Sinisa,
>
> unfortunately currently I don't have the time to test your fix (Kirsten's
> family is here during the christmas time, so I have to try not to spend too
> much time in front of my PC :). I'll check your fix after Dec 25th.
>
> The sample also fails when I don't mix parenthesis/braces:
>
> SELECT foo from bar limit 1,1 union select foo from bar limit 3,1
> returns an empty resultset too.
>
> I wish you and your family a merry christmas!
>
> Regards
>
> Georg
>
>

Hi Georg,

You were quite right, as you have hit a veritable bug.

One more reason, why forcing consistent usage / not-usage of braces is
mandatory.

This is a fix for the bug.


==== sql/sql_union.cc 1.55 vs edited =====
*** /tmp/sql_union.cc-1.55-1877 Sat Dec 14 15:15:17 2002
--- edited/sql/sql_union.cc Tue Dec 24 20:36:12 2002
***************
*** 279,287 ****
}
if (!thd->fatal_error) // Check if EOM
{
! offset_limit_cnt= global_parameters->offset_limit;
! select_limit_cnt= global_parameters->select_limit+
! global_parameters->offset_limit;
if (select_limit_cnt < global_parameters->select_limit)
select_limit_cnt= HA_POS_ERROR; // no limit
if (select_limit_cnt == HA_POS_ERROR)
--- 279,288 ----
}
if (!thd->fatal_error) // Check if EOM
{
! SELECT_LEX *sl=thd->lex.current_select->master_unit()->first_select();
! offset_limit_cnt= (sl->braces) ? global_parameters->offset_limit : 0;
! select_limit_cnt= (sl->braces) ? global_parameters->select_limit+
! global_parameters->offset_limit : HA_POS_ERROR;
if (select_limit_cnt < global_parameters->select_limit)
select_limit_cnt= HA_POS_ERROR; // no limit
if (select_limit_cnt == HA_POS_ERROR)


For the filter:

How-to-repeat:

Fix:

Happy Christmas to you and your family too !!!

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

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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-thread13349@lists.mysql.com
To unsubscribe, e-mail