From: Date: August 18 2006 1:38pm Subject: bk commit into 5.0 tree (gkodinov:1.2263) BUG#14654 List-Archive: http://lists.mysql.com/commits/10618 X-Bug: 14654 Message-Id: <20060818113803.E00863E433E@macbook.gmz> Below is the list of changes that have just been committed into a local 5.0 repository of kgeorge. When kgeorge does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2006-08-18 14:37:48+03:00, gkodinov@stripped +3 -0 Bug#14654 : Cannot select from the same table twice within a UNION statement Made the parser to support parenthesis around UNION branches. This is done by amending the rules of the parser so it generates the correct structure. Currently it supports arbitrary subquery/join/parenthesis operations in the EXISTS clause. In the IN/scalar subquery case it will allow adding nested parenthesis only if there is an UNION clause after the parenthesis. Otherwise it will just treat the multiple nested parenthesis as a scalar expression. It adds extra lex level for ((SELECT ...) UNION ...) to accommodate for the UNION clause. mysql-test/r/subselect2.result@stripped, 2006-08-18 14:37:42+03:00, gkodinov@stripped +13 -0 Bug#14654 : Cannot select from the same table twice within a UNION statement - test case mysql-test/t/subselect2.test@stripped, 2006-08-18 14:37:42+03:00, gkodinov@stripped +18 -0 Bug#14654 : Cannot select from the same table twice within a UNION statement - test case sql/sql_yacc.yy@stripped, 2006-08-18 14:37:43+03:00, gkodinov@stripped +122 -70 Bug#14654 : Cannot select from the same table twice within a UNION statement - shuffle around the rules for the parenthesis in subselect # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: gkodinov # Host: macbook.gmz # Root: /Users/kgeorge/mysql/work/B14654-5.0-opt --- 1.479/sql/sql_yacc.yy 2006-08-18 14:38:03 +03:00 +++ 1.480/sql/sql_yacc.yy 2006-08-18 14:38:03 +03:00 @@ -707,7 +707,7 @@ bool my_yyoverflow(short **a, YYSTYPE ** delete_option opt_temporary all_or_any opt_distinct opt_ignore_leaves fulltext_options spatial_type union_option start_transaction_opts opt_chain opt_release - union_opt select_derived_init option_type2 + union_opt select_derived_init option_type2 scalar_comp_subselect_union %type ulong_num raid_types merge_insert_types @@ -725,8 +725,7 @@ bool my_yyoverflow(short **a, YYSTYPE ** predicate bit_expr bit_term bit_factor value_expr term factor table_wild simple_expr udf_expr expr_or_default set_expr_or_default interval_expr - param_marker singlerow_subselect singlerow_subselect_init - exists_subselect exists_subselect_init geometry_function + param_marker geometry_function signed_literal now_or_signed_literal opt_escape sp_opt_default simple_ident_nospvar simple_ident_q @@ -791,8 +790,8 @@ bool my_yyoverflow(short **a, YYSTYPE ** %type internal_variable_name -%type in_subselect in_subselect_init - get_select_lex +%type subselect subselect_init + get_select_lex scalar_comp_subselect %type comp_op @@ -3914,12 +3913,14 @@ select_paren: yyerror(ER(ER_SYNTAX_ERROR)); YYABORT; } - if (sel->linkage == UNION_TYPE && - !sel->master_unit()->first_select()->braces) - { - yyerror(ER(ER_SYNTAX_ERROR)); - YYABORT; - } + if (sel->linkage == UNION_TYPE && + !sel->master_unit()->first_select()->braces && + sel->master_unit()->first_select()->linkage == + UNION_TYPE) + { + yyerror(ER(ER_SYNTAX_ERROR)); + YYABORT; + } /* select in braces, can't contain global parameters */ if (sel->master_unit()->fake_select_lex) sel->master_unit()->global_parameters= @@ -4177,37 +4178,37 @@ bool_pri: | bool_pri EQUAL_SYM predicate { $$= new Item_func_equal($1,$3); } | bool_pri comp_op predicate %prec EQ { $$= (*$2)(0)->create($1,$3); } - | bool_pri comp_op all_or_any in_subselect %prec EQ - { $$= all_any_subquery_creator($1, $2, $3, $4); } + | bool_pri comp_op all_or_any '(' subselect ')' %prec EQ + { $$= all_any_subquery_creator($1, $2, $3, $5); } | predicate ; predicate: - bit_expr IN_SYM '(' expr_list ')' + bit_expr IN_SYM '(' scalar_comp_subselect ')' + { $$= new Item_in_subselect($1, $4); } + | bit_expr not IN_SYM '(' scalar_comp_subselect ')' + { $$= negate_expression(YYTHD, new Item_in_subselect($1, $5)); } + | bit_expr IN_SYM '(' expr ')' + { + $$= new Item_func_eq($1, $4); + } + | bit_expr IN_SYM '(' expr ',' expr_list ')' { - if ($4->elements == 1) - $$= new Item_func_eq($1, $4->head()); - else - { - $4->push_front($1); - $$= new Item_func_in(*$4); - } + $6->push_front($4); + $6->push_front($1); + $$= new Item_func_in(*$6); } - | bit_expr not IN_SYM '(' expr_list ')' + | bit_expr not IN_SYM '(' expr ')' { - if ($5->elements == 1) - $$= new Item_func_ne($1, $5->head()); - else - { - $5->push_front($1); - Item_func_in *item = new Item_func_in(*$5); + $$= new Item_func_ne($1, $5); + } + | bit_expr not IN_SYM '(' expr ',' expr_list ')' + { + $7->push_front($5); + $7->push_front($1); + Item_func_in *item = new Item_func_in(*$7); item->negate(); $$= item; - } } - | bit_expr IN_SYM in_subselect - { $$= new Item_in_subselect($1, $3); } - | bit_expr not IN_SYM in_subselect - { $$= negate_expression(YYTHD, new Item_in_subselect($1, $4)); } | bit_expr BETWEEN_SYM bit_expr AND_SYM predicate { $$= new Item_func_between($1,$3,$5); } | bit_expr not BETWEEN_SYM bit_expr AND_SYM predicate @@ -4340,8 +4341,14 @@ simple_expr: $5->push_front($3); $$= new Item_row(*$5); } - | EXISTS exists_subselect { $$= $2; } - | singlerow_subselect { $$= $1; } + | EXISTS '(' subselect ')' + { + $$= new Item_exists_subselect($3); + } + | '(' scalar_comp_subselect ')' + { + $$= new Item_singlerow_subselect($2); + } | '{' ident expr '}' { $$= $3; } | MATCH ident_list_arg AGAINST '(' bit_expr fulltext_options ')' { $2->push_front($5); @@ -8863,49 +8870,95 @@ union_option: | ALL { $$=0; } ; -singlerow_subselect: - subselect_start singlerow_subselect_init - subselect_end - { - $$= $2; - }; - -singlerow_subselect_init: - select_init2 - { - $$= new Item_singlerow_subselect(Lex->current_select-> - master_unit()->first_select()); - }; - -exists_subselect: - subselect_start exists_subselect_init - subselect_end - { - $$= $2; - }; +subselect: + subselect_start subselect_init subselect_end + { + $$= $2; + } + | '(' + { + LEX *lex= Lex; + if (lex->sql_command == (int)SQLCOM_HA_READ || + lex->sql_command == (int)SQLCOM_KILL) + { + yyerror(ER(ER_SYNTAX_ERROR)); + YYABORT; + } + /* + we are making a "derived table" for the parenthesis + as we need to have a lex level to fit the union + after the parenthesis, e.g. + (SELECT .. ) UNION ... becomes + SELECT * FROM ((SELECT ...) UNION ...) + */ + if (mysql_new_select(lex, 1)) + YYABORT; + } + subselect ')' + { + LEX *lex= Lex; + THD *thd= YYTHD; + List_iterator it($3->item_list); + Item *item; + /* + we must fill the items list for the "derived table". + */ + while ((item= it++)) + add_item_to_list(thd, item); + } + union_clause subselect_end { $$= $3; }; -exists_subselect_init: - select_init2 - { - $$= new Item_exists_subselect(Lex->current_select->master_unit()-> - first_select()); - }; +scalar_comp_subselect_union: + union_list { $$= 1; } + | union_order_or_limit { $$= 1; } + ; -in_subselect: - subselect_start in_subselect_init - subselect_end - { - $$= $2; - }; +scalar_comp_subselect: + subselect_start subselect_init subselect_end + { + $$= $2; + } + | '(' + { + LEX *lex= Lex; + if (lex->sql_command == (int)SQLCOM_HA_READ || + lex->sql_command == (int)SQLCOM_KILL) + { + yyerror(ER(ER_SYNTAX_ERROR)); + YYABORT; + } + /* + we are making a "derived table" for the parenthesis + as we need to have a lex level to fit the union + after the parenthesis, e.g. + (SELECT .. ) UNION ... becomes + SELECT * FROM ((SELECT ...) UNION ...) + */ + if (mysql_new_select(lex, 1)) + YYABORT; + } + scalar_comp_subselect ')' + { + LEX *lex= Lex; + THD *thd= YYTHD; + List_iterator it($3->item_list); + Item *item; + /* + we must fill the items list for the "derived table". + */ + while ((item= it++)) + add_item_to_list(thd, item); + } + scalar_comp_subselect_union subselect_end { $$= $3; }; -in_subselect_init: +subselect_init: select_init2 { $$= Lex->current_select->master_unit()->first_select(); }; subselect_start: - '(' SELECT_SYM + SELECT_SYM { LEX *lex=Lex; if (lex->sql_command == (int)SQLCOM_HA_READ || @@ -8919,7 +8972,6 @@ subselect_start: }; subselect_end: - ')' { LEX *lex=Lex; lex->pop_context(); --- 1.13/mysql-test/r/subselect2.result 2006-08-18 14:38:03 +03:00 +++ 1.14/mysql-test/r/subselect2.result 2006-08-18 14:38:03 +03:00 @@ -144,3 +144,16 @@ and t2.a='1' AND t1.a=t3.b) > 0; a 2 DROP TABLE t1,t2,t3; +CREATE TABLE t1 (i INT); +(SELECT i FROM t1) UNION (SELECT i FROM t1); +i +SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS +( +(SELECT i FROM t1) UNION +(SELECT i FROM t1) +); +i +SELECT * FROM t1 +WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); +i +DROP TABLE t1; --- 1.7/mysql-test/t/subselect2.test 2006-08-18 14:38:03 +03:00 +++ 1.8/mysql-test/t/subselect2.test 2006-08-18 14:38:03 +03:00 @@ -168,3 +168,21 @@ SELECT t1.* FROM t1 WHERE (SELECT COUNT( and t2.a='1' AND t1.a=t3.b) > 0; DROP TABLE t1,t2,t3; + +# +# Bug#14654 : Cannot select from the same table twice within a UNION +# statement +# +CREATE TABLE t1 (i INT); + +(SELECT i FROM t1) UNION (SELECT i FROM t1); +SELECT sql_no_cache * FROM t1 WHERE NOT EXISTS + ( + (SELECT i FROM t1) UNION + (SELECT i FROM t1) + ); + +SELECT * FROM t1 +WHERE NOT EXISTS (((SELECT i FROM t1) UNION (SELECT i FROM t1))); + +DROP TABLE t1;