#
# This file contains tests covering the parser
#

#=============================================================================
# LEXICAL PARSER (lex)
#=============================================================================

#
# Maintainer: these tests are for the lexical parser, so every character,
# even whitespace or comments, is significant here.
#

SET @save_sql_mode=@@sql_mode;

#
# Documenting the current behavior, to detect incompatible changes.
# In each cases:
# - no error is the correct result
# - an error is the expected result with the current implementation,
#   and is a limitation.

set SQL_MODE='';

create table ADDDATE(a int);
drop table ADDDATE;
create table ADDDATE (a int);
drop table ADDDATE;

--error ER_PARSE_ERROR
create table BIT_AND(a int);
create table BIT_AND (a int);
drop table BIT_AND;

--error ER_PARSE_ERROR
create table BIT_OR(a int);
create table BIT_OR (a int);
drop table BIT_OR;

--error ER_PARSE_ERROR
create table BIT_XOR(a int);
create table BIT_XOR (a int);
drop table BIT_XOR;

--error ER_PARSE_ERROR
create table CAST(a int);
create table CAST (a int);
drop table CAST;

--error ER_PARSE_ERROR
create table COUNT(a int);
create table COUNT (a int);
drop table COUNT;

--error ER_PARSE_ERROR
create table CURDATE(a int);
create table CURDATE (a int);
drop table CURDATE;

--error ER_PARSE_ERROR
create table CURTIME(a int);
create table CURTIME (a int);
drop table CURTIME;

--error ER_PARSE_ERROR
create table DATE_ADD(a int);
create table DATE_ADD (a int);
drop table DATE_ADD;

--error ER_PARSE_ERROR
create table DATE_SUB(a int);
create table DATE_SUB (a int);
drop table DATE_SUB;

--error ER_PARSE_ERROR
create table EXTRACT(a int);
create table EXTRACT (a int);
drop table EXTRACT;

--error ER_PARSE_ERROR
create table GROUP_CONCAT(a int);
create table GROUP_CONCAT (a int);
drop table GROUP_CONCAT;

# Limitation removed in 5.1
create table GROUP_UNIQUE_USERS(a int);
drop table GROUP_UNIQUE_USERS;
create table GROUP_UNIQUE_USERS (a int);
drop table GROUP_UNIQUE_USERS;

--error ER_PARSE_ERROR
create table MAX(a int);
create table MAX (a int);
drop table MAX;

--error ER_PARSE_ERROR
create table MID(a int);
create table MID (a int);
drop table MID;

--error ER_PARSE_ERROR
create table MIN(a int);
create table MIN (a int);
drop table MIN;

--error ER_PARSE_ERROR
create table NOW(a int);
create table NOW (a int);
drop table NOW;

--error ER_PARSE_ERROR
create table POSITION(a int);
create table POSITION (a int);
drop table POSITION;

create table SESSION_USER(a int);
drop table SESSION_USER;
create table SESSION_USER (a int);
drop table SESSION_USER;

--error ER_PARSE_ERROR
create table STD(a int);
create table STD (a int);
drop table STD;

--error ER_PARSE_ERROR
create table STDDEV(a int);
create table STDDEV (a int);
drop table STDDEV;

--error ER_PARSE_ERROR
create table STDDEV_POP(a int);
create table STDDEV_POP (a int);
drop table STDDEV_POP;

--error ER_PARSE_ERROR
create table STDDEV_SAMP(a int);
create table STDDEV_SAMP (a int);
drop table STDDEV_SAMP;

create table SUBDATE(a int);
drop table SUBDATE;
create table SUBDATE (a int);
drop table SUBDATE;

--error ER_PARSE_ERROR
create table SUBSTR(a int);
create table SUBSTR (a int);
drop table SUBSTR;

--error ER_PARSE_ERROR
create table SUBSTRING(a int);
create table SUBSTRING (a int);
drop table SUBSTRING;

--error ER_PARSE_ERROR
create table SUM(a int);
create table SUM (a int);
drop table SUM;

--error ER_PARSE_ERROR
create table SYSDATE(a int);
create table SYSDATE (a int);
drop table SYSDATE;

create table SYSTEM_USER(a int);
drop table SYSTEM_USER;
create table SYSTEM_USER (a int);
drop table SYSTEM_USER;

--error ER_PARSE_ERROR
create table TRIM(a int);
create table TRIM (a int);
drop table TRIM;

# Limitation removed in 5.1
create table UNIQUE_USERS(a int);
drop table UNIQUE_USERS;
create table UNIQUE_USERS (a int);
drop table UNIQUE_USERS;

--error ER_PARSE_ERROR
create table VARIANCE(a int);
create table VARIANCE (a int);
drop table VARIANCE;

--error ER_PARSE_ERROR
create table VAR_POP(a int);
create table VAR_POP (a int);
drop table VAR_POP;

--error ER_PARSE_ERROR
create table VAR_SAMP(a int);
create table VAR_SAMP (a int);
drop table VAR_SAMP;

set SQL_MODE='IGNORE_SPACE';

create table ADDDATE(a int);
drop table ADDDATE;
create table ADDDATE (a int);
drop table ADDDATE;

--error ER_PARSE_ERROR
create table BIT_AND(a int);
--error ER_PARSE_ERROR
create table BIT_AND (a int);

--error ER_PARSE_ERROR
create table BIT_OR(a int);
--error ER_PARSE_ERROR
create table BIT_OR (a int);

--error ER_PARSE_ERROR
create table BIT_XOR(a int);
--error ER_PARSE_ERROR
create table BIT_XOR (a int);

--error ER_PARSE_ERROR
create table CAST(a int);
--error ER_PARSE_ERROR
create table CAST (a int);

--error ER_PARSE_ERROR
create table COUNT(a int);
--error ER_PARSE_ERROR
create table COUNT (a int);

--error ER_PARSE_ERROR
create table CURDATE(a int);
--error ER_PARSE_ERROR
create table CURDATE (a int);

--error ER_PARSE_ERROR
create table CURTIME(a int);
--error ER_PARSE_ERROR
create table CURTIME (a int);

--error ER_PARSE_ERROR
create table DATE_ADD(a int);
--error ER_PARSE_ERROR
create table DATE_ADD (a int);

--error ER_PARSE_ERROR
create table DATE_SUB(a int);
--error ER_PARSE_ERROR
create table DATE_SUB (a int);

--error ER_PARSE_ERROR
create table EXTRACT(a int);
--error ER_PARSE_ERROR
create table EXTRACT (a int);

--error ER_PARSE_ERROR
create table GROUP_CONCAT(a int);
--error ER_PARSE_ERROR
create table GROUP_CONCAT (a int);

# Limitation removed in 5.1
create table GROUP_UNIQUE_USERS(a int);
drop table GROUP_UNIQUE_USERS;
create table GROUP_UNIQUE_USERS (a int);
drop table GROUP_UNIQUE_USERS;

--error ER_PARSE_ERROR
create table MAX(a int);
--error ER_PARSE_ERROR
create table MAX (a int);

--error ER_PARSE_ERROR
create table MID(a int);
--error ER_PARSE_ERROR
create table MID (a int);

--error ER_PARSE_ERROR
create table MIN(a int);
--error ER_PARSE_ERROR
create table MIN (a int);

--error ER_PARSE_ERROR
create table NOW(a int);
--error ER_PARSE_ERROR
create table NOW (a int);

--error ER_PARSE_ERROR
create table POSITION(a int);
--error ER_PARSE_ERROR
create table POSITION (a int);

create table SESSION_USER(a int);
drop table SESSION_USER;
create table SESSION_USER (a int);
drop table SESSION_USER;

--error ER_PARSE_ERROR
create table STD(a int);
--error ER_PARSE_ERROR
create table STD (a int);

--error ER_PARSE_ERROR
create table STDDEV(a int);
--error ER_PARSE_ERROR
create table STDDEV (a int);

--error ER_PARSE_ERROR
create table STDDEV_POP(a int);
--error ER_PARSE_ERROR
create table STDDEV_POP (a int);

--error ER_PARSE_ERROR
create table STDDEV_SAMP(a int);
--error ER_PARSE_ERROR
create table STDDEV_SAMP (a int);

create table SUBDATE(a int);
drop table SUBDATE;
create table SUBDATE (a int);
drop table SUBDATE;

--error ER_PARSE_ERROR
create table SUBSTR(a int);
--error ER_PARSE_ERROR
create table SUBSTR (a int);

--error ER_PARSE_ERROR
create table SUBSTRING(a int);
--error ER_PARSE_ERROR
create table SUBSTRING (a int);

--error ER_PARSE_ERROR
create table SUM(a int);
--error ER_PARSE_ERROR
create table SUM (a int);

--error ER_PARSE_ERROR
create table SYSDATE(a int);
--error ER_PARSE_ERROR
create table SYSDATE (a int);

create table SYSTEM_USER(a int);
drop table SYSTEM_USER;
create table SYSTEM_USER (a int);
drop table SYSTEM_USER;

--error ER_PARSE_ERROR
create table TRIM(a int);
--error ER_PARSE_ERROR
create table TRIM (a int);

# Limitation removed in 5.1
create table UNIQUE_USERS(a int);
drop table UNIQUE_USERS;
create table UNIQUE_USERS (a int);
drop table UNIQUE_USERS;

--error ER_PARSE_ERROR
create table VARIANCE(a int);
--error ER_PARSE_ERROR
create table VARIANCE (a int);

--error ER_PARSE_ERROR
create table VAR_POP(a int);
--error ER_PARSE_ERROR
create table VAR_POP (a int);

--error ER_PARSE_ERROR
create table VAR_SAMP(a int);
--error ER_PARSE_ERROR
create table VAR_SAMP (a int);

--echo #
--echo # Test "UNIQUE KEY" and "UNIQUE" "KEY" grammar ambiguity
--echo #

CREATE TABLE t1 (i INT KEY);
SHOW CREATE TABLE t1;

CREATE TABLE t2 (i INT UNIQUE);
SHOW CREATE TABLE t2;

CREATE TABLE t3 (i INT UNIQUE KEY);
--echo # Should output "UNIQUE KEY `i` (`i`)" only:
SHOW CREATE TABLE t3;

DROP TABLE t1, t2, t3;

--echo #

#
# Bug#25930 (CREATE TABLE x SELECT ... parses columns wrong when ran with
#            ANSI_QUOTES mode)
#

--disable_warnings
DROP TABLE IF EXISTS table_25930_a;
DROP TABLE IF EXISTS table_25930_b;
--enable_warnings

SET SQL_MODE = 'ANSI_QUOTES';
CREATE TABLE table_25930_a ( "blah" INT );
CREATE TABLE table_25930_b SELECT "blah" - 1 FROM table_25930_a;

# The lexer used to chop the first <">,
# not marking the start of the token "blah" correctly.
desc table_25930_b;

DROP TABLE table_25930_a;
DROP TABLE table_25930_b;


SET @@sql_mode=@save_sql_mode;

#
# Bug#26030 (Parsing fails for stored routine w/multi-statement execution
# enabled)
#

--disable_warnings
DROP PROCEDURE IF EXISTS p26030;
--enable_warnings

delimiter $$;

select "non terminated"$$
select "terminated";$$
select "non terminated, space"      $$
select "terminated, space";      $$
select "non terminated, comment" /* comment */$$
select "terminated, comment"; /* comment */$$

select "stmt 1";select "stmt 2 non terminated"$$
select "stmt 1";select "stmt 2 terminated";$$
select "stmt 1";select "stmt 2 non terminated, space"      $$
select "stmt 1";select "stmt 2 terminated, space";      $$
select "stmt 1";select "stmt 2 non terminated, comment" /* comment */$$
select "stmt 1";select "stmt 2 terminated, comment"; /* comment */$$

select "stmt 1";             select "space, stmt 2"$$
select "stmt 1";/* comment */select "comment, stmt 2"$$

DROP PROCEDURE IF EXISTS p26030; CREATE PROCEDURE p26030() BEGIN SELECT 1; END; CALL p26030()
$$

DROP PROCEDURE IF EXISTS p26030; CREATE PROCEDURE p26030() SELECT 1; CALL p26030()
$$

delimiter ;$$
DROP PROCEDURE p26030;

#=============================================================================
# SYNTACTIC PARSER (bison)
#=============================================================================

#
#
# Bug#21114 (Foreign key creation fails to table with name format)
# 

# Test coverage with edge conditions

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select pi(3.14);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select tan();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select tan(1, 2);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select makedate(1);
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select makedate(1, 2, 3);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select maketime();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select maketime(1);
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select maketime(1, 2);
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select maketime(1, 2, 3, 4);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select atan();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select atan2(1, 2, 3);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select concat();
select concat("foo");

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select concat_ws();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select concat_ws("foo");

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select elt();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select elt(1);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select export_set();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select export_set("p1");
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select export_set("p1", "p2");
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select export_set("p1", "p2", "p3", "p4", "p5", "p6");

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select field();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select field("p1");

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select from_unixtime();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select from_unixtime(1, 2, 3);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select unix_timestamp(1, 2);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select greatest();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select greatest(12);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select last_insert_id(1, 2);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select least();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select least(12);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select locate();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select locate(1);
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select locate(1, 2, 3, 4);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select log();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select log(1, 2, 3);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select make_set();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select make_set(1);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select source_pos_wait();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select source_pos_wait(1);

--disable_result_log
--error 0,ER_WRONG_ARGUMENTS
select source_pos_wait('binlog.999999', 4, -1);
--enable_result_log

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select rand(1, 2, 3);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select round(1, 2, 3);

-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select yearweek();
-- error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
select yearweek(1, 2, 3);

#
# Bug#24736: UDF functions parsed as Stored Functions
#

# Verify that the syntax for calling UDF : foo(expr AS param, ...)
# can not be used when calling native functions

# Native function with 1 argument

select abs(3);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select abs(3 AS three);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select abs(3 three);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select abs(3 AS "three");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select abs(3 "three");

# Native function with 2 arguments

set @bar="bar";
set @foobar="foobar";

select instr("foobar", "bar");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select instr("foobar" AS p1, "bar");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select instr("foobar" p1, "bar");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select instr("foobar" AS "p1", "bar");
## String concatenation, valid syntax
select instr("foobar" "p1", "bar");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select instr(@foobar "p1", "bar");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select instr("foobar", "bar" AS p2);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select instr("foobar", "bar" p2);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select instr("foobar", "bar" AS "p2");
## String concatenation, valid syntax
select instr("foobar", "bar" "p2");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select instr("foobar", @bar "p2");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select instr("foobar" AS p1, "bar" AS p2);

# Native function with 3 arguments

select conv(255, 10, 16);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255 AS p1, 10, 16);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255 p1, 10, 16);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255 AS "p1", 10, 16);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255 "p1", 10, 16);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255, 10 AS p2, 16);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255, 10 p2, 16);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255, 10 AS "p2", 16);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255, 10 "p2", 16);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255, 10, 16 AS p3);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255, 10, 16 p3);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255, 10, 16 AS "p3");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255, 10, 16 "p3");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select conv(255 AS p1, 10 AS p2, 16 AS p3);

# Native function with a variable number of arguments

# Bug in libm.so on Solaris:
#   atan(10) from 32-bit version returns 1.4711276743037347
#   atan(10) from 64-bit version returns 1.4711276743037345
--replace_result 1.4711276743037345 1.4711276743037347
select atan(10);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10 AS p1);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10 p1);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10 AS "p1");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10 "p1");

select atan(10, 20);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10 AS p1, 20);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10 p1, 20);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10 AS "p1", 20);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10 "p1", 20);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10, 20 AS p2);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10, 20 p2);
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10, 20 AS "p2");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10, 20 "p2");
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select atan(10 AS p1, 20 AS p2);

#
# Bug#22312 Syntax error in expression with INTERVAL()
#

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
SELECT STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL (INTERVAL(1,2,3) + 1) MINUTE;
SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
SELECT 1 + INTERVAL(1,0,1,2) + 1;
SELECT INTERVAL(1^1,0,1,2) + 1;
SELECT INTERVAL(1,0+1,2,3) * 5.5;
SELECT INTERVAL(3,3,1+3,4+4) / 0.5;
SELECT (INTERVAL(1,0,1,2) + 5) * 7 + INTERVAL(1,0,1,2) / 2;
SELECT INTERVAL(1,0,1,2) + 1, 5 * INTERVAL(1,0,1,2);
SELECT INTERVAL(0,(1*5)/2) + INTERVAL(5,4,3);

--disable_warnings
SELECT 1^1 + INTERVAL 1+1 SECOND & 1 + INTERVAL 1+1 SECOND;
SELECT 1%2 - INTERVAL 1^1 SECOND | 1%2 - INTERVAL 1^1 SECOND;
--enable_warnings

CREATE TABLE t1 (a INT, b DATETIME);
INSERT INTO t1 VALUES (INTERVAL(3,2,1) + 1, "1997-12-31 23:59:59" + INTERVAL 1 SECOND);
SELECT * FROM t1 WHERE a = INTERVAL(3,2,1) + 1;
DROP TABLE t1;
SET sql_mode = default;
#
# Bug#28317 Left Outer Join with {oj outer-join}
#

--disable_warnings
DROP TABLE IF EXISTS t1,t2,t3;
--enable_warnings
CREATE TABLE t1 (a1 INT, a2 INT, a3 INT, a4 DATETIME);
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
SELECT t1.* FROM t1 AS t0, { OJ t2 INNER JOIN t1 ON (t1.a1=t2.a1) } WHERE t0.a3=2;
SELECT t1.*,t2.* FROM { OJ ((t1 INNER JOIN t2 ON (t1.a1=t2.a2)) LEFT OUTER JOIN t3 ON t3.a3=t2.a1)};
SELECT t1.*,t2.* FROM { OJ ((t1 LEFT OUTER JOIN t2 ON t1.a3=t2.a2) INNER JOIN t3 ON (t3.a1=t2.a2))};
SELECT t1.*,t2.* FROM { OJ (t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a2) CROSS JOIN t3 ON (t3.a2=t2.a3)};
SELECT * FROM {oj t1 LEFT OUTER JOIN t2 ON t1.a1=t2.a3} WHERE t1.a2 > 10;
SELECT {fn CONCAT(a1,a2)} FROM t1;
UPDATE t3 SET a4={d '1789-07-14'} WHERE a1=0;
SELECT a1, a4 FROM t2 WHERE a4 LIKE {fn UCASE('1789-07-14')};
DROP TABLE t1, t2, t3;

--echo #
--echo # End of 5.1 tests
--echo #

#
# Bug#12546960 - 60993: NAME QUOTED WITH QUOTE INSTEAD OF BACKTICK 
#                       GIVES NO SYNTAX ERROR
#
CREATE TABLE t (id INT PRIMARY KEY);
--error ER_PARSE_ERROR
--query ALTER TABLE t RENAME TO `t1';
DROP TABLE t;

--echo #
--echo # Bug#13819100 BROKEN SYNTAX ACCEPTED FOR START SLAVE, STOP SLAVE
--echo #

--error ER_PARSE_ERROR
STOP SLAVE ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ;
--error ER_PARSE_ERROR
STOP SLAVE ,,,,,,,,,,,,, sql_thread, ,,,,,,,,,,,,,,,,,,, ;
--error ER_PARSE_ERROR
STOP SLAVE ,,,,,,,,,,,,, io_thread, ,,,,,,,,,,,,,,,,,,, ;

--echo #
--echo # Bug#13819132 BROKEN SYNTAX ACCEPTED FOR START TRANSACTION
--echo #

--error ER_PARSE_ERROR
START TRANSACTION ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ;

--echo #
--echo # Test of collective fix for three parser bugs:
--echo #
--echo # Bug #17727401, Bug #17426017, Bug #17473479:
--echo #   The server accepts wrong syntax and then fails in different ways
--echo #

CREATE TABLE t1 (i INT);

--echo # bug #17426017
--error ER_PARSE_ERROR
SELECT (SELECT EXISTS(SELECT * LIMIT 1 ORDER BY VALUES (c00)));

--echo # bug#17473479
CREATE TABLE a(a int);
CREATE TABLE b(a int);
--error ER_PARSE_ERROR
DELETE FROM b ORDER BY(SELECT 1 FROM a ORDER BY a ORDER BY a);
DROP TABLE a, b;

--echo # bug #17727401
--error ER_PARSE_ERROR
SELECT '' IN (SELECT '1' c FROM t1 ORDER BY '' ORDER BY '') FROM t1;

--echo # regression & coverage tests

--echo # uniform syntax for FROM DUAL clause:

SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
  FOR UPDATE;

SELECT 1 FROM
  (SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
   FOR UPDATE) a;

SELECT 1 FROM t1
  WHERE EXISTS(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
               FOR UPDATE);

SELECT 1 FROM t1
UNION
SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
  FOR UPDATE;

(SELECT 1 FROM t1)
UNION 
(SELECT 1 FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1
 FOR UPDATE);

--echo # "FOR UPDATE" tests

SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
--error ER_PARSE_ERROR
SELECT 1 FROM t1 FOR UPDATE UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 FOR UPDATE;


--echo # "INTO" clause tests

SELECT 1 FROM t1 INTO @var17727401;
SELECT 1 FROM DUAL INTO @var17727401;
SELECT 1 INTO @var17727401;

SELECT 1 INTO @var17727401 FROM t1;
SELECT 1 INTO @var17727401 FROM DUAL;

# Double "INTO" clause: parse error is "near 'INTO @var17727401_2' at line 1"
--error ER_MULTIPLE_INTO_CLAUSES
SELECT 1 INTO @var17727401_1 FROM t1 INTO @var17727401_2;

# Double "INTO" clause: parse error is "near 'INTO @var17727401_2' at line 2"
--error ER_MULTIPLE_INTO_CLAUSES
SELECT 1 INTO @var17727401_1 FROM DUAL
  INTO @var17727401_2;

SELECT 1 INTO @var17727401 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;
SELECT 1 FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1 INTO @var17727401;

--error ER_PARSE_ERROR
SELECT 1 FROM t1 WHERE 1 INTO @var17727401 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1;

--error ER_MULTIPLE_INTO_CLAUSES
SELECT 1 INTO @var17727401_1
  FROM t1 WHERE 1 GROUP BY 1 HAVING 1 ORDER BY 1 LIMIT 1
  INTO @var17727401_2;

--error ER_PARSE_ERROR
SELECT (SELECT 1 FROM t1 INTO @var17727401);
--error ER_PARSE_ERROR
SELECT 1 FROM (SELECT 1 FROM t1 INTO @var17727401) a;
--error ER_PARSE_ERROR
SELECT EXISTS(SELECT 1 FROM t1 INTO @var17727401);

--error ER_PARSE_ERROR
SELECT 1 FROM t1 INTO @var17727401 UNION SELECT 1 FROM t1 INTO t1;
--error ER_PARSE_ERROR
(SELECT 1 FROM t1 INTO @var17727401) UNION (SELECT 1 FROM t1 INTO t1);

SELECT 1 FROM t1 UNION SELECT 1 FROM t1 INTO @var17727401;

--echo # ORDER and LIMIT clause combinations

(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;
(SELECT 1 FROM t1 LIMIT 1) LIMIT 1;

((SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1) ORDER BY 1;
((SELECT 1 FROM t1 LIMIT 1) LIMIT 1) LIMIT 1;

(SELECT 1 FROM t1 ORDER BY 1) LIMIT 1;
(SELECT 1 FROM t1 LIMIT 1) ORDER BY 1;

--error ER_PARSE_ERROR
((SELECT 1 FROM t1 ORDER BY 1) LIMIT 1) ORDER BY 1);
--error ER_PARSE_ERROR
((SELECT 1 FROM t1 LIMIT 1) ORDER BY 1) LIMIT 1);

# ORDER/LIMIT and UNION:

let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1;
eval $q;
eval SELECT ($q);
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1;
eval $q;
eval SELECT ($q);
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 ORDER BY 1 LIMIT 1;
eval $q;
eval SELECT ($q);
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 UNION SELECT 1 FROM t1 LIMIT 1 ORDER BY 1;
--error ER_PARSE_ERROR
eval $q;
--error ER_PARSE_ERROR
eval SELECT ($q);
--error ER_PARSE_ERROR
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1;
--error ER_PARSE_ERROR
eval $q;
--error ER_PARSE_ERROR
eval SELECT ($q);
--error ER_PARSE_ERROR
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1;
--error ER_PARSE_ERROR
eval $q;
--error ER_PARSE_ERROR
eval SELECT ($q);
--error ER_PARSE_ERROR
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 ORDER BY 1 LIMIT 1 UNION SELECT 1 FROM t1;
--error ER_PARSE_ERROR
eval $q;
--error ER_PARSE_ERROR
eval SELECT ($q);
--error ER_PARSE_ERROR
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 LIMIT 1 ORDER BY 1 UNION SELECT 1 FROM t1;
--error ER_PARSE_ERROR
eval $q;
--error ER_PARSE_ERROR
eval SELECT ($q);
--error ER_PARSE_ERROR
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 ORDER BY 1;
--error ER_PARSE_ERROR
eval $q;
--error ER_PARSE_ERROR
eval SELECT ($q);
--error ER_PARSE_ERROR
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 LIMIT 1;
--error ER_PARSE_ERROR
eval $q;
--error ER_PARSE_ERROR
eval SELECT ($q);
--error ER_PARSE_ERROR
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 LIMIT 1 UNION SELECT 1 FROM t1 ORDER BY 1;
--error ER_PARSE_ERROR
eval $q;
--error ER_PARSE_ERROR
eval SELECT ($q);
--error ER_PARSE_ERROR
eval SELECT 1 FROM ($q) a;

let $q=SELECT 1 FROM t1 ORDER BY 1 UNION SELECT 1 FROM t1 LIMIT 1;
--error ER_PARSE_ERROR
eval $q;
--error ER_PARSE_ERROR
eval SELECT ($q);
--error ER_PARSE_ERROR
eval SELECT 1 FROM ($q) a;

DROP TABLE t1;

--echo #
--echo # Bug #18106014: RECENT REGRESSION: MORE CASES OF ASSERTION FAILED:
--echo #                !JOIN->PLAN_IS_CONST()
--echo #

SELECT COUNT(1) FROM DUAL GROUP BY '1' ORDER BY 1 ;
SELECT COUNT(1)           GROUP BY '1' ORDER BY 1 ;

DO(SELECT 1 c           GROUP BY 1 HAVING 1 ORDER BY COUNT(1));
DO(SELECT 1 c FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT(1));

SELECT (SELECT 1 c                   GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
  'null is not expected';
SELECT (SELECT 1 c FROM DUAL         GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
  'null is not expected';
SELECT (SELECT 1 c                   GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
  'null is expected';
SELECT (SELECT 1 c FROM DUAL         GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
  'null is expected';

SELECT (SELECT 1 c           WHERE 1 GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
  'null is not expected';
SELECT (SELECT 1 c FROM DUAL WHERE 1 GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
  'null is not expected';
SELECT (SELECT 1 c           WHERE 1 GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
  'null is expected';
SELECT (SELECT 1 c FROM DUAL WHERE 1 GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
  'null is expected';

SELECT (SELECT 1 c           WHERE 0 GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
  'null is expected';
SELECT (SELECT 1 c FROM DUAL WHERE 0 GROUP BY 1 HAVING 1 ORDER BY COUNT(1)) AS
  'null is expected';
SELECT (SELECT 1 c           WHERE 0 GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
  'null is expected';
SELECT (SELECT 1 c FROM DUAL WHERE 0 GROUP BY 1 HAVING 0 ORDER BY COUNT(1)) AS
  'null is expected';

SELECT 1 c FROM DUAL GROUP BY 1 HAVING 1 ORDER BY COUNT(1);
SELECT 1 c FROM DUAL GROUP BY 1 HAVING 0 ORDER BY COUNT(1);
SELECT 1 c           GROUP BY 1 HAVING 1 ORDER BY COUNT(1);

--echo #
--echo # Bug #18106058: RECENT REGRESSION: CRASH IN JOIN::MAKE_TMP_TABLES_INFO
--echo #

CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1);

SELECT ((SELECT 1 AS f           HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
  ORDER BY f));
SELECT ((SELECT 1 AS f FROM DUAL HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
  ORDER BY f));

SELECT 1 AS f          FROM DUAL HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
  ORDER BY f;
SELECT 1 AS f                   HAVING EXISTS(SELECT 1 FROM t1) IS TRUE
  ORDER BY f;

DROP TABLE t1;

--echo #
--echo # Bug#17075846 : unquoted file names for variable values are
--echo #                accepted but parsed incorrectly
--echo #
--error ER_WRONG_TYPE_FOR_VAR 
SET default_storage_engine=a.myisam;
--error ER_PARSE_ERROR
SET default_storage_engine = .a.MyISAM;
--error ER_WRONG_TYPE_FOR_VAR 
SET default_storage_engine = a.b.MyISAM;
--error ER_WRONG_TYPE_FOR_VAR 
SET default_storage_engine = `a`.MyISAM;
--error ER_WRONG_TYPE_FOR_VAR 
SET default_storage_engine = `a`.`MyISAM`; 
--error ER_UNKNOWN_STORAGE_ENGINE 
set default_storage_engine = "a.MYISAM";
--error ER_UNKNOWN_STORAGE_ENGINE 
set default_storage_engine = 'a.MYISAM';
--error ER_UNKNOWN_STORAGE_ENGINE 
set default_storage_engine = `a.MYISAM`;
CREATE TABLE t1 (s VARCHAR(100));
--ERROR ER_BAD_FIELD_ERROR
CREATE TRIGGER trigger1 BEFORE INSERT ON t1 FOR EACH ROW
SET default_storage_engine = NEW.INNODB;
DROP TABLE t1;

--echo #
--echo # Some additional coverage tests for WL#7199 and friends
--echo #

CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1), (2);
CREATE TABLE t2 (i INT);
INSERT INTO t2 VALUES (10), (20);

SELECT i FROM t1 WHERE i = 1
UNION
SELECT i FROM t2 WHERE i = 10
ORDER BY i;

SELECT i FROM t1 WHERE i = 1
UNION
SELECT i FROM t2 WHERE i = 10
LIMIT 100;

SELECT i FROM t1 WHERE i = 1
UNION
SELECT i FROM t2 GROUP BY i HAVING i = 10
ORDER BY i;

SELECT i FROM t1 WHERE i = 1
UNION
SELECT i FROM t2 GROUP BY i HAVING i = 10
LIMIT 100;

(SELECT i FROM t1 WHERE i = 1) ORDER BY i;
(SELECT i FROM t1 WHERE i = 1) LIMIT 100;

(SELECT i FROM t1 GROUP BY i HAVING i = 1) ORDER BY i;
(SELECT i FROM t1 GROUP BY i HAVING i = 1) LIMIT 100;

DROP TABLE t1, t2;

--echo #
--echo # Bug#18486460 ASSERTION FAILED: N < M_SIZE AFTER FIX_INNER_REFS
--echo #

CREATE TABLE t1(b INT);
CREATE TABLE t2(a INT, b INT, c INT, d INT);

--source include/turn_off_only_full_group_by.inc
EXPLAIN SELECT
(
  SELECT
  ROW(t1.b, a) = ROW( ROW(1, t2.c) = ROW(1, d), c) = a
  FROM t1
)
FROM t2 GROUP BY a;
--source include/restore_sql_mode_after_turn_off_only_full_group_by.inc

DROP TABLE t1, t2;

--echo #
--echo # Bug#18498344: SELECT WITH ALIAS NOT WORKING IN 5.7
--echo #

CREATE TABLE t1 (
  a INT
);

INSERT INTO t1 VALUES ( 2 );

--echo # Should succeed
SELECT *
FROM ( SELECT a FROM t1 UNION SELECT 1 ORDER BY a ) AS a1
WHERE a1.a = 1 OR a1.a = 2;

DROP TABLE t1;

--echo #
--echo # Bug #18484088: PROBLEMS IN CREATE_FUNC_CAST ON QUERY ERRORS...
--echo #

# original bugreport:
--error ER_TOO_BIG_PRECISION
DO(CONVERT(CONVERT('',DECIMAL(66,0)), DECIMAL(66,0))), CAST(CONVERT(1,DECIMAL(65,31)) AS DATE);

# test an error message for the complex "column" name: "CONVERT('',DECIMAL(65,0))"
--error ER_TOO_BIG_PRECISION
SELECT CONVERT(CONVERT('',DECIMAL(65,0)), DECIMAL(66,0));

--echo #
--echo # Bug #18759387: PROBLEM IN ITEM_FUNC_XOR::NEG_TRANSFORMER
--echo #

--error ER_PARSE_ERROR
SELECT 1<
!(1 XOR TO_BASE64()));

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT 1<
!(1 XOR TO_BASE64());

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT  !('' XOR LENGTH());

--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
SELECT  !((UNHEX() IS NULL));

--echo #
--echo # Bug #20086997: PARSER CONFUSES WITH 7BIT-CHARACTER STRING DETECTION
--echo #

--character_set latin2
CREATE DATABASE mysqltest1 CHARACTER SET LATIN2;
USE mysqltest1;
CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET LATIN2);
SET CHARACTER SET cp1250_latin2;
INSERT INTO t1 VALUES ('');
INSERT INTO t1 VALUES ('' '');
SELECT HEX(a) FROM t1;
DROP DATABASE mysqltest1;
USE test;
--character_set utf8mb4

--echo #
--echo # WL #7201, WL #7202 and WL#8062 coverage tests
--echo #

CREATE TABLE t1 (i INT);

INSERT INTO t1 () SELECT * FROM t1;
INSERT INTO t1 SELECT HIGH_PRIORITY * FROM t1;
--error ER_WRONG_USAGE
INSERT INTO t1 SELECT DISTINCT ALL * FROM t1;
--error ER_WRONG_TABLE_NAME
REPLACE INTO `` SELECT * FROM ``;
DELETE QUICK FROM t1 WHERE i = 0;

DROP TABLE t1;

--echo #
--echo # Bug #21035515: PARSE_GCOL_EXPR SHOULD BE A KIND OF RESERVED WORD,
--echo #                NOT A KEYWORD

SET @parse_gcol_expr = 1;

SELECT 1 AS parse_gcol_expr;

CREATE TABLE parse_gcol_expr (i INT);
DROP TABLE parse_gcol_expr;

DELIMITER |;
--echo # parse_gcol_expr can't be a label:
CREATE PROCEDURE p1()
BEGIN
parse_gcol_expr: LOOP
  SELECT 1;
END LOOP parse_gcol_expr;
END|
DELIMITER ;|

DROP PROCEDURE p1;

--echo # PARSE_GCOL_EXPR is not a valid statement:
--error ER_PARSE_ERROR
PARSE_GCOL_EXPR (1);

--echo #
--echo # Bug #17400320	ALGORITHM= IS NOT SUPPORTED FOR ALTER TABLE WITH <PARTITION_OPTIONS>
--echo #

# Test some variant of dummy ALTER TABLE statements.

CREATE TABLE t1 (x INT PRIMARY KEY);
ALTER TABLE t1;
ALTER TABLE t1 ALGORITHM=DEFAULT;
ALTER TABLE t1 ALGORITHM=COPY;
ALTER TABLE t1 ALGORITHM=INPLACE;
ALTER TABLE t1 LOCK=DEFAULT;
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t1 LOCK=NONE;
--error ER_ALTER_OPERATION_NOT_SUPPORTED
ALTER TABLE t1 LOCK=SHARED;
ALTER TABLE t1 LOCK=EXCLUSIVE;
ALTER TABLE t1 LOCK=SHARED, ALGORITHM=COPY,
               LOCK=NONE, ALGORITHM=DEFAULT,
               LOCK=EXCLUSIVE, ALGORITHM=INPLACE;
--error ER_WRONG_USAGE
ALTER TABLE t1 WITH VALIDATION;
--error ER_WRONG_USAGE
ALTER TABLE t1 WITHOUT VALIDATION;
--error ER_WRONG_USAGE
ALTER TABLE t1 LOCK=SHARED, WITH VALIDATION, ALGORITHM=COPY,
               LOCK=EXCLUSIVE, WITHOUT VALIDATION, ALGORITHM=INPLACE;
DROP TABLE t1;

--echo #
--echo # WL#8083: Introduce <query expression> parser rule
--echo # Bug#14743786: PARSE ERROR WITH UNION PARENTHESES ON TOP LEVEL
--echo #

--echo #
--echo # Part1: Regression Testing.
--echo #
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES ( 1 );

CREATE TABLE t2 ( a INT );
INSERT INTO t2 VALUES ( 2 ), ( 2 );

CREATE TABLE t3 ( a INT );
INSERT INTO t3 VALUES ( 3 ), ( 3 ), ( 3 );

SELECT 1 UNION SELECT 2;
(SELECT 1) UNION SELECT 2;
SELECT 1 UNION (SELECT 2);
(SELECT 1) UNION (SELECT 2);

  SELECT 2 FROM t1 UNION ((SELECT 3 FROM t1));
 (SELECT 2 FROM t1)  UNION SELECT 3 FROM t1;
((SELECT 2 FROM t1)) UNION SELECT 3 FROM t1;

(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;

(SELECT 1 FROM t1 LIMIT 1) LIMIT 1;

--error ER_PARSE_ERROR
SELECT a FROM t1 LIMIT 1 UNION ALL SELECT a FROM t1;

(SELECT 1) UNION (SELECT 2 UNION SELECT 3);

(SELECT a FROM t1 LIMIT 1) UNION ALL (SELECT a FROM t1 ORDER BY a) LIMIT 2;

(SELECT a FROM t1 LIMIT 1) UNION ALL ((SELECT a FROM t1 ORDER BY a)) LIMIT 2;

--error ER_BAD_FIELD_ERROR
SELECT 1 UNION SELECT 2 FROM t1 ORDER BY a LIMIT 1;
SELECT 1 UNION (SELECT 2 FROM t1 ORDER BY a LIMIT 1);
--error ER_BAD_FIELD_ERROR
(SELECT 1 FROM t1 LIMIT 2) UNION  SELECT 2 FROM t1 ORDER BY a LIMIT 1;
(SELECT 1 FROM t1 LIMIT 2) UNION (SELECT 2 FROM t1 ORDER BY a LIMIT 1);
(SELECT a FROM t1 LIMIT 2) LIMIT 1;
((SELECT a FROM t1 LIMIT 2)) LIMIT 1;
(SELECT a FROM t1 LIMIT 2) ORDER BY 1;
(SELECT 1 FROM t1 LIMIT 2) UNION (SELECT 2 FROM t1 ORDER BY a LIMIT 1) LIMIT 1;

--error ER_BAD_FIELD_ERROR
(SELECT 1 FROM t1 LIMIT 2) UNION (SELECT 2 FROM t1 ORDER BY a LIMIT 1)
  ORDER BY a;

--error ER_BAD_FIELD_ERROR
(SELECT 1 FROM t1 LIMIT 1) UNION ((SELECT 2 FROM t1 ORDER BY a LIMIT 2))
  ORDER BY a;

(SELECT a FROM t2 LIMIT 1) UNION (SELECT a FROM t3 LIMIT 2) LIMIT 1;

(SELECT 1 FROM t1 ORDER BY 1) ORDER BY 1;

--error ER_TABLENAME_NOT_ALLOWED_HERE
(SELECT a FROM t1 LIMIT 1) ORDER BY t1.a;

--error ER_TABLENAME_NOT_ALLOWED_HERE
((SELECT a FROM t1 LIMIT 1)) ORDER BY t1.a;

--error ER_TABLENAME_NOT_ALLOWED_HERE
(SELECT a FROM t1 LIMIT 1) UNION ALL (SELECT 2) ORDER BY t1.b;

--error ER_TABLENAME_NOT_ALLOWED_HERE
(SELECT a FROM t1 LIMIT 1) UNION ALL ((SELECT 2)) ORDER BY t1.b;

--error ER_TABLENAME_NOT_ALLOWED_HERE
(SELECT a FROM t1 LIMIT 1) UNION ALL (SELECT a FROM t1 ORDER BY a LIMIT 2)
  ORDER BY t1.b;

# [ before WL11350: Generally speaking, we don't support nested unions]
# We do now.

SELECT 1 UNION ( SELECT 1 UNION SELECT 1 );
# OTOH union expressions are left-associative by their nature,
# so "s1 UNION s2 UNION ..." and "(s1 UNION s2) UNION ..." are same,
# thus the parser accepts nested query expressions at the left hand side:
( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;
( (SELECT 1 UNION SELECT 1 ) UNION SELECT 1 ) UNION SELECT 1;
# [ But: ] now ok
( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1 );

# Missing ")":
--error ER_PARSE_ERROR
( SELECT 1 UNION ( SELECT 1 UNION SELECT 1 ) UNION SELECT 1;

--error ER_PARSE_ERROR
SELECT a FROM t1 ORDER BY a UNION SELECT a FROM t1;

(SELECT * FROM t1 LIMIT 1) UNION SELECT * FROM t1;
(SELECT * FROM t1 ORDER BY a) UNION SELECT * FROM t1;

--error ER_PARSE_ERROR
SELECT a FROM t1
UNION
SELECT a FROM t1 ORDER BY a
UNION
SELECT a FROM t1;

(SELECT SQL_CALC_FOUND_ROWS a FROM t1 LIMIT 2)
UNION
(SELECT a FROM t2 ORDER BY a) LIMIT 2;

DROP TABLE t1, t2, t3;

delimiter |;

CREATE PROCEDURE p1() BEGIN IF whatever THEN SELECT 1; END IF; END|

delimiter ;|

--error 1054
CALL p1();

DROP PROCEDURE p1;

--echo #
--echo # Part 2: Test of changed (fixed) behavior.
--echo #

CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES ( 1 );

CREATE TABLE t2 ( a INT );
INSERT INTO t2 VALUES ( 2 ), ( 2 );

CREATE TABLE t3 ( a INT );
INSERT INTO t3 VALUES ( 3 ), ( 3 ), ( 3 );

   (SELECT 1 FROM t1   UNION   SELECT 2 FROM t1);
  ((SELECT 1 FROM t1   UNION   SELECT 2 FROM t1));
   (SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1));
  ((SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1)));
  ((SELECT 1 FROM t1   UNION ((SELECT 2 FROM t1))));
  ((SELECT 1 FROM t1)  UNION   SELECT 2 FROM t1);
 (((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1);
((((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1));
  ((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1));
 (((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1)));
((((SELECT 1 FROM t1)) UNION  (SELECT 2 FROM t1)));
 (((SELECT 1 FROM t1)  UNION ((SELECT 2 FROM t1))));
((((SELECT 1 FROM t1)) UNION ((SELECT 2 FROM t1))));

(SELECT 1 UNION SELECT 2) ORDER BY 1;
((SELECT 1 UNION SELECT 2)) ORDER BY 1;
((SELECT 1) ORDER BY 1);
((SELECT 1) LIMIT 1);
(SELECT 1 UNION SELECT 2) LIMIT 1;
((SELECT 1 UNION SELECT 2)) LIMIT 1;

--error ER_PARSE_ERROR
(SELECT a FROM t1) LIMIT 1 UNION ALL ((SELECT a FROM t1 ORDER BY a)) LIMIT 2;

DROP TABLE t1, t2, t3;

--echo #
--echo # Part 3: The <table factor> syntax.
--echo #
CREATE TABLE t1 ( a INT );
INSERT INTO t1 VALUES ( 1 );

CREATE TABLE t2 ( a INT );
INSERT INTO t2 VALUES ( 2 );

CREATE TABLE t3 ( a INT );
INSERT INTO t3 VALUES ( 3 );

CREATE TABLE t4 ( a INT );
INSERT INTO t4 VALUES ( 3 );

SELECT * FROM    (SELECT 1 FROM t1   UNION   SELECT 2 FROM t1) dt;
SELECT * FROM   ((SELECT 1 FROM t1   UNION   SELECT 2 FROM t1)) dt;
SELECT * FROM    (SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1)) dt;
SELECT * FROM   ((SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1))) dt;
SELECT * FROM   ((SELECT 1 FROM t1   UNION ((SELECT 2 FROM t1)))) dt;
SELECT * FROM   ((SELECT 1 FROM t1)  UNION   SELECT 2 FROM t1) dt;
SELECT * FROM  (((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1) dt;
SELECT * FROM ((((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1)) dt;
SELECT * FROM   ((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1)) dt;
SELECT * FROM  (((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1))) dt;
SELECT * FROM ((((SELECT 1 FROM t1)) UNION  (SELECT 2 FROM t1))) dt;
SELECT * FROM  (((SELECT 1 FROM t1)  UNION ((SELECT 2 FROM t1)))) dt;
SELECT * FROM ((((SELECT 1 FROM t1)) UNION ((SELECT 2 FROM t1)))) dt;

--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM    (SELECT 1 FROM t1   UNION   SELECT 2 FROM t1);
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM   ((SELECT 1 FROM t1   UNION   SELECT 2 FROM t1));
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM    (SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1));
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM   ((SELECT 1 FROM t1   UNION  (SELECT 2 FROM t1)));
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM   ((SELECT 1 FROM t1   UNION ((SELECT 2 FROM t1))));
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM   ((SELECT 1 FROM t1)  UNION   SELECT 2 FROM t1);
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM  (((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1);
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM ((((SELECT 1 FROM t1)) UNION   SELECT 2 FROM t1));
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM   ((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1));
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM  (((SELECT 1 FROM t1)  UNION  (SELECT 2 FROM t1)));
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM ((((SELECT 1 FROM t1)) UNION  (SELECT 2 FROM t1)));
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM  (((SELECT 1 FROM t1)  UNION ((SELECT 2 FROM t1))));
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM ((((SELECT 1 FROM t1)) UNION ((SELECT 2 FROM t1))));

SELECT * FROM  ( t1 JOIN t2 ON TRUE );
SELECT * FROM (( t1 JOIN t2 ON TRUE ));

SELECT * FROM ( t1 JOIN t2 ON TRUE  JOIN t3 ON TRUE );
SELECT * FROM ((t1 JOIN t2 ON TRUE) JOIN t3 ON TRUE );

SELECT * FROM (t1 INNER JOIN t2 ON (t1.a = t2.a));

--error ER_PARSE_ERROR
SELECT 1 FROM (SELECT 1 FROM t1 INTO @v) a;

SELECT 1 FROM (t1);
SELECT 1 FROM ((t1));

SELECT 1 UNION SELECT 2 FROM (t2);

SELECT 1 FROM  (SELECT 2  ORDER BY 1) AS res;

--echo #
--echo # This syntax is no longer allowed
--echo #
#--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT 1 FROM ((SELECT 2) ORDER BY 1) AS res;
--error ER_PARSE_ERROR
SELECT 1 FROM ((SELECT 2) a ORDER BY 1) AS res;
#--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT 1 FROM ((SELECT 2) LIMIT 1) AS res;
--error ER_PARSE_ERROR
SELECT 1 FROM ((SELECT 2) a LIMIT 1) AS res;

SELECT * FROM ( t1 AS alias1 );
SELECT * FROM   t1 AS alias1, t2 AS alias2;
SELECT * FROM ( t1 AS alias1, t2 AS alias2 );

--error ER_NONUNIQ_TABLE
SELECT * FROM ( t1 JOIN t2 ON TRUE, t1 JOIN t3 ON TRUE );
--error ER_PARSE_ERROR
SELECT * FROM ( t1 JOIN t2 ON TRUE, t1 t11 JOIN t3 ON TRUE ) t1a;
--error ER_PARSE_ERROR
SELECT * FROM ( t1 JOIN t2 ON TRUE, SELECT 1 FROM DUAL );
SELECT * FROM ( t1 JOIN t2 ON TRUE, (SELECT 1 FROM DUAL) t1a );
SELECT * FROM t1 JOIN t2 ON TRUE, (SELECT 1 FROM DUAL) t1a;
--error ER_DERIVED_MUST_HAVE_ALIAS
SELECT * FROM ( SELECT 1 FROM DUAL );

SELECT * FROM ( SELECT 1 FROM DUAL ) t1a;

SELECT * FROM  ( t1, t2 );
SELECT * FROM (( t1, t2 ));

SELECT * FROM  ( (t1),   t2  );
SELECT * FROM  (((t1)),  t2  );
SELECT * FROM  ( (t1),  (t2) );
SELECT * FROM  (  t1,   (t2) );

((SELECT 1 UNION SELECT 1) UNION SELECT 1);
SELECT * FROM ((SELECT 1 UNION SELECT 1) UNION SELECT 1) a;

SELECT * FROM (t1, t2) JOIN (t3, t4) ON TRUE;
SELECT * FROM ((t1, t2) JOIN t3 ON TRUE);
--error ER_NON_UNIQ_ERROR
SELECT * FROM t1 JOIN ( t2, t3 ) USING ( a );

DROP TABLE t1, t2, t3, t4;

--echo # Check the format in the note.
CREATE TABLE t1 (a INT);
EXPLAIN SELECT 1 FROM (SELECT 1 FROM t1) t;
DROP TABLE t1;

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );
CREATE TABLE t3 ( c INT );
CREATE TABLE t4 ( d INT );
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (2);
INSERT INTO t4 VALUES (2);

SELECT * FROM t1 LEFT JOIN ( t2, t3, t4 ) ON a = c;

--error ER_NONUNIQ_TABLE
SELECT * FROM t1 NATURAL JOIN ((t1 NATURAL JOIN t1), (t1 NATURAL JOIN t1));

DROP TABLE t1, t2, t3, t4;

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );
CREATE TABLE t3 ( c INT );
CREATE TABLE t4 ( d INT );
CREATE TABLE t5 ( d INT );

SELECT * FROM t5 NATURAL JOIN ((t1 NATURAL JOIN t2), (t3 NATURAL JOIN t4));

SELECT * FROM ((t1 NATURAL JOIN t2), (t3 NATURAL JOIN t4)) NATURAL JOIN t5;

SELECT * FROM t1 JOIN ( t2, t3 ) ON TRUE;

SELECT * FROM ( t1, t2 , t3 );
SELECT * FROM ( ( t1, t2 ), t3 );
SELECT * FROM ( ((t1, t2)), t3 );
SELECT * FROM ( t1, ( t2, t3 ) );
SELECT * FROM ( t1, ((t2, t3)) );

SELECT * FROM ((( t1, t2 ), t3));
SELECT * FROM ((((t1, t2)), t3));
SELECT * FROM ((t1, ( t2, t3 )));
SELECT * FROM ((t1, ((t2, t3))));

--error ER_VIEW_SELECT_CLAUSE
CREATE VIEW v1 AS SELECT 1 INTO @v;

CREATE VIEW v1 AS SELECT 1 FROM ( SELECT 1 FROM t1 ) my_table;

DROP TABLE t1, t2, t3, t4, t5;
DROP VIEW v1;

CREATE TABLE t1( a INT );
INSERT INTO t1 VALUES (1);

SELECT 1 INTO @v;
(SELECT 1 INTO @v);
((SELECT 1 INTO @v));

SELECT 1 FROM t1 INTO @v;
(SELECT 1 FROM t1 INTO @v);
((SELECT 1 FROM t1 INTO @v));

--error ER_PARSE_ERROR
SELECT 1 FROM t1 INTO @v UNION SELECT 1;
--error ER_PARSE_ERROR
(SELECT 1 FROM t1 INTO @v) UNION SELECT 1;
--error ER_PARSE_ERROR
SELECT 1 FROM t1 INTO @v UNION (SELECT 1);
--error ER_PARSE_ERROR
((SELECT 1 FROM t1 INTO @v) UNION (SELECT 1));

--error ER_MISPLACED_INTO
SELECT 1 INTO @v UNION SELECT 1;
--error ER_MISPLACED_INTO
(SELECT 1 INTO @v) UNION SELECT 1;
--error ER_MISPLACED_INTO
SELECT 1 INTO @v UNION (SELECT 1);
--error ER_MISPLACED_INTO
((SELECT 1 INTO @v) UNION (SELECT 1));

--error ER_TOO_MANY_ROWS
SELECT 1 UNION SELECT 2 INTO @v;
--error ER_TOO_MANY_ROWS
(SELECT 1) UNION SELECT 2 INTO @v;
--error ER_TOO_MANY_ROWS
(SELECT 1) UNION (SELECT 2 INTO @v);
--error ER_TOO_MANY_ROWS
((SELECT 1) UNION (SELECT 2 INTO @v));

--error ER_TOO_MANY_ROWS
SELECT 1 UNION SELECT 2 INTO @v FROM t1;
--error ER_TOO_MANY_ROWS
(SELECT 1) UNION SELECT 2 INTO @v FROM t1;
--error ER_TOO_MANY_ROWS
(SELECT 1) UNION (SELECT 2 INTO @v FROM t1);
--error ER_TOO_MANY_ROWS
((SELECT 1) UNION (SELECT 2 INTO @v FROM t1));

SELECT 1 UNION SELECT 1 INTO @v FROM t1;
(SELECT 1) UNION SELECT 1 INTO @v FROM t1;
(SELECT 1) UNION (SELECT 1 INTO @v FROM t1);
((SELECT 1) UNION (SELECT 1 INTO @v FROM t1));

SELECT 1 UNION SELECT 2 INTO OUTFILE 'parser.test.file1';
SELECT 1 UNION (SELECT 2 INTO OUTFILE 'parser.test.file2');
(SELECT 1) UNION SELECT 2 INTO OUTFILE 'parser.test.file3';
(SELECT 1) UNION (SELECT 2 INTO OUTFILE 'parser.test.file4');
((SELECT 1) UNION (SELECT 2 INTO OUTFILE 'parser.test.file5'));
--let $datadir=`select @@datadir`
--remove_file $datadir/test/parser.test.file1
--remove_file $datadir/test/parser.test.file2
--remove_file $datadir/test/parser.test.file3
--remove_file $datadir/test/parser.test.file4
--remove_file $datadir/test/parser.test.file5

--error ER_MISPLACED_INTO
SELECT * FROM (SELECT a INTO @v FROM t1) t1a;
--error ER_MISPLACED_INTO
SELECT * FROM (SELECT a INTO @v) t1a;

DROP TABLE t1;

--echo #
--echo # Part 4: The <joined table> syntax.
--echo #
CREATE TABLE t1( a INT );
CREATE TABLE t2( b INT );
CREATE TABLE t3( c INT );
CREATE TABLE t4( d INT );
CREATE TABLE t5( e INT );

--echo # We use EXPLAIN so we can get the parser's interpretation of the
--echo # nesting. We don't really care about the execution plan.

SET optimizer_switch = 'block_nested_loop=off';

SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 JOIN t2 ON a = b;
SELECT * FROM t1 t11 JOIN t1 t12 USING ( a );
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 INNER JOIN t2 ON a = b;
SELECT * FROM t1 t11 INNER JOIN t1 t12 USING ( a );

SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1 CROSS JOIN t2 ON a = b;
SELECT * FROM t1 t11 CROSS JOIN t1 t12 USING ( a );

SELECT * FROM t1 STRAIGHT_JOIN t2;
SELECT * FROM t1 STRAIGHT_JOIN t2 ON a = b;
SELECT * FROM t1 t11 STRAIGHT_JOIN t1 t12 USING ( a );

SELECT * FROM t1 t11 NATURAL JOIN t1 t12;
--error ER_PARSE_ERROR
SELECT * FROM t1 t11 NATURAL JOIN t1 t12 ON t11.a = t12.a;
--error ER_PARSE_ERROR
SELECT * FROM t1 t11 NATURAL JOIN t1 t12 USING ( a );
SELECT * FROM t1 t11 NATURAL INNER JOIN t1 t12;

--error ER_PARSE_ERROR
SELECT * FROM t1 LEFT JOIN t2;
SELECT * FROM t1 LEFT JOIN t2 ON a = b;
SELECT * FROM t1 t11 LEFT JOIN t1 t12 USING ( a );
SELECT * FROM t1 NATURAL LEFT JOIN t2;
--error ER_PARSE_ERROR
SELECT * FROM t1 LEFT OUTER JOIN t2;
SELECT * FROM t1 LEFT OUTER JOIN t2 ON a = b;
SELECT * FROM t1 t11 LEFT OUTER JOIN t1 t12 USING ( a );
SELECT * FROM t1 NATURAL LEFT OUTER JOIN t2;

--error ER_PARSE_ERROR
SELECT * FROM t1 RIGHT JOIN t2;
SELECT * FROM t1 RIGHT JOIN t2 ON a = b;
SELECT * FROM t1 t11 RIGHT JOIN t1 t12 USING ( a );
SELECT * FROM t1 NATURAL RIGHT JOIN t2;
--error ER_PARSE_ERROR
SELECT * FROM t1 RIGHT OUTER JOIN t2;
SELECT * FROM t1 RIGHT OUTER JOIN t2 ON a = b;
SELECT * FROM t1 t11 RIGHT OUTER JOIN t1 t12 USING ( a );
SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;


--echo # Right-deep join nesting.
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t2.b = t3.c ON t1.a = t2.b;
--echo # Right-deep join nesting from t2 and on.
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t3.c = t4.d ON t2.b = t3.c;
--echo # Right-deep join nesting from t2 and on.
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t3.c = t4.d ON t1.a = t2.b;
--echo # Left-deep join nesting.
EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.b JOIN t3 ON t2.b = t3.c;
--echo # A cross join joined with an inner join (i.e. with a join condition).
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t1.a = t2.b;
EXPLAIN SELECT * FROM t1 t11 JOIN t2 JOIN t1 t12 USING ( a );
--echo # A left-deep cross join tree joined with an inner join.
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t1.a = t2.b;
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 JOIN t5 ON t1.a = t2.b;

--echo # The different kinds of <table_ref> when used with context-dependent
--echo # join. Tests the top-down build of the parse tree.
EXPLAIN SELECT * FROM t1 JOIN (t2) JOIN t3;
EXPLAIN SELECT * FROM t1 JOIN (SELECT 1 AS b) a JOIN t3;
EXPLAIN SELECT * FROM t1 JOIN (t2 JOIN t3) JOIN t4;
EXPLAIN SELECT * FROM t1 JOIN (t2, t3) JOIN t4;

EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t2.b = t3.c JOIN t4 ON t1.a = t4.d;
EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 ON t2.b = t3.c JOIN t4 ON t1.a = t2.b;
EXPLAIN SELECT * FROM t1 t11 JOIN t2 JOIN t1 t12 USING(a) JOIN t1 t13 USING(a);
EXPLAIN SELECT * FROM t2 JOIN t1 t11 JOIN t1 t12 USING(a) JOIN t1 t13 USING(a);

EXPLAIN SELECT * FROM t1 JOIN t2 JOIN t3 JOIN t4 ON t3.c = t4.d;

SET optimizer_switch = DEFAULT;

DROP TABLE t1, t2, t3, t4, t5;

--echo # Testing correct nesting of natural joins.
CREATE TABLE t1( a INT, b int );
CREATE TABLE t2( a INT, c int );
CREATE TABLE t3( a INT, d int );

INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t2 VALUES         (2, 2), (3, 3), (4, 4);
INSERT INTO t3 VALUES                 (3, 3), (4, 4), (5, 5);

--echo # The two queries below should produce identical result sets.
--sorted_result
SELECT * FROM t1 NATURAL LEFT JOIN t2 NATURAL RIGHT JOIN t3;
--sorted_result
SELECT * FROM (t1 NATURAL LEFT JOIN t2) NATURAL RIGHT JOIN t3;
--echo # This result should differ from the result sets above.
--sorted_result
SELECT * FROM t1 NATURAL LEFT JOIN (t2 NATURAL RIGHT JOIN t3);

DROP TABLE t1, t2, t3;


--echo #
--echo # Bug#22995438: BUILD INDEX DEFINITION SYNTAX BOTTOM-UP
--echo #

--echo # This is regression testing for the refactoring.

CREATE TABLE t1 (
  a INT,
  b INT,
  c INT,
  d INT,
  e INT,
  f INT,
  g INT,
  h INT,
  i INT,
  j INT,
  k INT,
  l INT,
  m INT,
  n INT,
  o INT
 );

CREATE        INDEX a_index             ON t1( a );
CREATE UNIQUE INDEX b_index             ON t1( b );
CREATE        INDEX c_index USING btree ON t1( c );
--error ER_PARSE_ERROR
CREATE        INDEX c_index USING btree USING btree ON t1( c );
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
CREATE        INDEX d_index USING rtree ON t1( d );
CREATE        INDEX e_index TYPE  btree ON t1( e );
CREATE        INDEX type TYPE  btree ON t1( f );
--error ER_PARSE_ERROR
CREATE        INDEX TYPE  btree ON t1( g );
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
CREATE        INDEX h_index TYPE  rtree ON t1( h );
CREATE        INDEX i_index             ON t1( i ) KEY_BLOCK_SIZE = 1;
CREATE        INDEX j_index             ON t1( j ) KEY_BLOCK_SIZE = 1 KEY_BLOCK_SIZE = 1;
CREATE        INDEX k_index             ON t1( k ) COMMENT 'A comment';
CREATE        INDEX k_index2            ON t1( k ) COMMENT 'A comment' COMMENT 'Another comment';
CREATE        INDEX l_index             ON t1( l ) USING btree;
CREATE        INDEX m_index             ON t1( m ) TYPE btree;
CREATE        INDEX n_index USING btree ON t1( n ) USING btree;
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
CREATE        INDEX x_index USING btree ON t1( o ) USING rtree;
CREATE        INDEX o_index USING rtree ON t1( o ) USING btree;

ANALYZE TABLE t1;
SHOW INDEXES FROM t1;

DROP TABLE t1;

--echo #
--echo # WL#8907: Parser refactoring: merge all SELECT rules into one.
--echo #

--echo # Warning on hints in CREATE or ALTER VIEW:

CREATE VIEW v1 AS SELECT /*+ QB_NAME(a) */ 1;
ALTER VIEW v1 AS SELECT /*+ QB_NAME(a) */ 1;
SELECT * FROM v1;
DROP VIEW v1;

--echo # The "Hints aren't supported in CREATE or ALTER VIEW" warning is masked:

CREATE VIEW v1 AS SELECT /*+ BAD_HINT */ 1;
ALTER VIEW v1 AS SELECT /*+ BAD_HINT */ 1;
SELECT * FROM v1;
DROP VIEW v1;

--echo #
--echo #
--echo # Bug#23321895: CRASH IN IS_VIEW_OR_DERIVED OR
--echo # TABLE_LIST::IS_LEAF_FOR_NAME_RESOLUTION
--echo #
CREATE TABLE t1( a INT );
CREATE TABLE t2( a INT );
CREATE TABLE t3( a INT );
CREATE TABLE t4( a INT );

--error ER_UNKNOWN_SYSTEM_VARIABLE
SELECT 1
FROM ( SELECT 1 FROM t1 JOIN t2 ON @@q ) AS d
JOIN t3 LEFT JOIN t4 ON 1;

DROP TABLE t1, t2, t3, t4;



###########################################################################
--echo #
--echo # Bug#39559: dump of stored procedures / functions with C-style
--echo #     comment can't be read back
--echo #

--write_file $MYSQLTEST_VARDIR/tmp/bug39559.sql
select 2 as expected, /*!01000/**/*/ 2 as result;
select 1 as expected, /*!99998/**/*/ 1 as result;
select 3 as expected, /*!01000 1 + */ 2 as result;
select 2 as expected, /*!99990 1 + */ 2 as result;
select 7 as expected, /*!01000 1 + /* 8 + */ 2 + */ 4 as result;
select 8 as expected, /*!99998 1 + /* 2 + */ 4 + */ 8 as result;
select 7 as expected, /*!01000 1 + /*!01000 8 + */ 2 + */ 4 as result;
select 7 as expected, /*!01000 1 + /*!99998 8 + */ 2 + */ 4 as result;
select 4 as expected, /*!99998 1 + /*!99998 8 + */ 2 + */ 4 as result;
select 4 as expected, /*!99998 1 + /*!01000 8 + */ 2 + */ 4 as result;
select 7 as expected, /*!01000 1 + /*!01000 8 + /*!01000 error */ 16 + */ 2 + */ 4 as result;
select 4 as expected, /* 1 + /*!01000 8 + */ 2 + */ 4;
EOF

--exec $MYSQL --comments --force --table test <$MYSQLTEST_VARDIR/tmp/bug39559.sql
--remove_file $MYSQLTEST_VARDIR/tmp/bug39559.sql

--echo # Bug#46527 "COMMIT AND CHAIN RELEASE does not make sense"
--echo #
--error ER_PARSE_ERROR
COMMIT AND CHAIN RELEASE;

COMMIT AND NO CHAIN RELEASE;
disconnect default;
connect(default, localhost, root,,);

COMMIT RELEASE;
disconnect default;
connect(default, localhost, root,,);

--error ER_PARSE_ERROR
COMMIT CHAIN RELEASE;

--error ER_PARSE_ERROR
COMMIT NO CHAIN RELEASE;

--error ER_PARSE_ERROR
COMMIT AND NO RELEASE;
--error ER_PARSE_ERROR
COMMIT AND RELEASE;

COMMIT NO RELEASE;
--error ER_PARSE_ERROR
COMMIT CHAIN NO RELEASE;
--error ER_PARSE_ERROR
COMMIT NO CHAIN NO RELEASE;

--error ER_PARSE_ERROR
COMMIT AND RELEASE CHAIN;

COMMIT AND NO CHAIN NO RELEASE;

--error ER_PARSE_ERROR
ROLLBACK AND CHAIN RELEASE;

ROLLBACK AND NO CHAIN RELEASE;
disconnect default;
connect(default, localhost, root,,);

ROLLBACK RELEASE;
disconnect default;
connect(default, localhost, root,,);

--error ER_PARSE_ERROR
ROLLBACK CHAIN RELEASE;

--error ER_PARSE_ERROR
ROLLBACK NO CHAIN RELEASE;
disconnect default;
connect(default, localhost, root,,);

--error ER_PARSE_ERROR
ROLLBACK AND NO RELEASE;

--error ER_PARSE_ERROR
ROLLBACK AND RELEASE;

ROLLBACK NO RELEASE;

--error ER_PARSE_ERROR
ROLLBACK CHAIN NO RELEASE;

--error ER_PARSE_ERROR
ROLLBACK NO CHAIN NO RELEASE;
--error ER_PARSE_ERROR
ROLLBACK AND RELEASE CHAIN;

ROLLBACK AND NO CHAIN NO RELEASE;

--echo #
--echo # Bug#26132947: SERVER CAN EXIT ON ALTER TABLE ADD PARTITION SYNTAX
--echo #
CREATE TABLE t1 (a INT PRIMARY KEY) PARTITION BY HASH (a) PARTITIONS 1;
--error ER_ADD_PARTITION_NO_NEW_PARTITION
ALTER TABLE t1 ADD PARTITION;
DROP TABLE t1;

--echo #

--echo #
--echo # Bug#25717617: Wrong syntax error line numbers when sql_mode has the
--echo #               IGNORE_SPACE flag
--echo #

SET @save_sql_mode=@@sql_mode;
SET sql_mode='IGNORE_SPACE';

--echo # Expected error line number is 2:
--error ER_PARSE_ERROR
CREATE
TABLE;

--echo # Expected error line number is 5:
--error ER_PARSE_ERROR
CREATE
#
#
#
TABLE;

--echo #
--echo # Regression test added in WL#8657
--echo #

CREATE TEMPORARY TABLE t1(a INT);
SHOW COLUMNS FROM t1 WHERE FIELD='a';
DROP TABLE t1;

--error ER_WRONG_DB_NAME
ALTER TABLE t1 RENAME TO ``.t1;

--error ER_PARSE_ERROR
CREATE TABLE t1 (a INT) PARTITION BY KEY ALGORITHM = 10 () PARTITIONS 3;

--error ER_PARSE_ERROR
ALTER EVENT ev1;

--error ER_PARSE_ERROR
ALTER INSTANCE ROTATE MyISAM MASTER KEY;

--error ER_PARSE_ERROR
REVOKE SELECT(c1) ON FUNCTION *.* FROM r1;

--error ER_PARSE_ERROR
GRANT SELECT(c1) ON FUNCTION *.* TO r1;

--error ER_WRONG_TABLE_NAME
CREATE INDEX idx1 ON `` (c1);

--error ER_WRONG_TABLE_NAME
ALTER TABLE t1 EXCHANGE PARTITION p0 WITH TABLE `` WITH VALIDATION;

--error ER_WRONG_TABLE_NAME
CHECK TABLE ``;

--error ER_WRONG_TABLE_NAME
DROP INDEX idx1 ON ``;

--error ER_WRONG_TABLE_NAME
CACHE INDEX `` IN c;

--error ER_WRONG_TABLE_NAME
CACHE INDEX `` PARTITION (ALL) IN c;

--error ER_WRONG_TABLE_NAME
LOAD INDEX INTO CACHE `` PARTITION (ALL);

--error ER_WRONG_TABLE_NAME
LOAD INDEX INTO CACHE ``;


SET @@sql_mode=@save_sql_mode;

--echo #
--echo # Bug#24756971: REMOVE OBSOLETE LEXER HACK AROUND SELECT LIST
--echo #

--error ER_PARSE_ERROR
SELECT 1,,2;

--error ER_PARSE_ERROR
SELECT ,,1;

--error ER_PARSE_ERROR
SELECT ,,,;


--echo #
--echo # Removal of undocumented syntax, see bug#27389878.
--echo #

--error ER_PARSE_ERROR
CREATE DATABASE db CHARSET DEFAULT;

--error ER_PARSE_ERROR
CREATE DATABASE db COLLATE DEFAULT;

--error ER_PARSE_ERROR
CREATE TABLE t (i INT) CHARSET DEFAULT;

--error ER_PARSE_ERROR
CREATE TABLE t (i INT) COLLATE DEFAULT;

--error ER_PARSE_ERROR
ALTER DATABASE db CHARSET DEFAULT;

--error ER_PARSE_ERROR
ALTER DATABASE db COLLATE DEFAULT;

--error ER_PARSE_ERROR
ALTER TABLE t COLLATE DEFAULT;

--error ER_PARSE_ERROR
SET NAMES utf8 COLLATE DEFAULT;

--error ER_PARSE_ERROR
SET NAMES DEFAULT COLLATE DEFAULT;

DELIMITER //;

--error ER_PARSE_ERROR
CREATE PROCEDURE p1()
BEGIN
  DECLARE c CHAR(1) CHARSET DEFAULT;
END//

--error ER_PARSE_ERROR
CREATE PROCEDURE p1()
BEGIN
  DECLARE c CHAR(1) COLLATE DEFAULT;
END//

DELIMITER ;//

--error ER_PARSE_ERROR
CREATE PROCEDURE p1(c CHAR(1) CHARSET DEFAULT) BEGIN END;

--error ER_PARSE_ERROR
CREATE PROCEDURE p1(c CHAR(1) COLLATE DEFAULT) BEGIN END;

--error ER_PARSE_ERROR
CREATE FUNCTION f1(c CHAR(1) CHARSET DEFAULT) RETURNS INT RETURN 1;

--error ER_PARSE_ERROR
CREATE FUNCTION f1(c CHAR(1) COLLATE DEFAULT) RETURNS INT RETURN 1;

--error ER_PARSE_ERROR
CREATE FUNCTION f1() RETURNS CHAR(1) CHARSET DEFAULT RETURN '';

--error ER_PARSE_ERROR
CREATE FUNCTION f1() RETURNS CHAR(1) COLLATE DEFAULT RETURN '';


--echo #
--echo # Bug#27814204: THE "ADMIN" WORD SHOULD BE A NON-RESERVED WORD IN THE
--echo #               SQL GRAMMAR
--echo #

CREATE TEMPORARY TABLE admin (admin INT);
DROP TABLE admin;


--echo #
--echo # Bug#27760787: ERROR IN SQL SYNTAX WHEN USING "DEFAULT" KEYWORD IN
--echo #               ALTER TABLE COMMAND

SELECT @@default_collation_for_utf8mb4;

--echo #########################################################################
--echo #
--echo # 1. @@default_collation_for_utf8mb4 does not matter:
--echo #
--echo #########################################################################

CREATE DATABASE db1 CHARSET cp1251 COLLATE cp1251_general_ci;
USE db1;
CREATE TABLE t1 (i INT) CHARSET utf8mb4;

--echo #
--echo # Implicit COLLATE:
--echo #

ALTER TABLE t1 CONVERT TO CHARACTER SET DEFAULT;
SHOW CREATE TABLE t1;

--echo #
--echo # Explicit COLLATE:
--echo #

ALTER TABLE t1 CONVERT TO CHARACTER SET DEFAULT COLLATE cp1251_bin;
SHOW CREATE TABLE t1;

DROP DATABASE db1;

--echo #########################################################################
--echo #
--echo # 2. @@default_collation_for_utf8mb4 == utf8mb4_general_ci
--echo #    @@collation_database            == utf8mb4_0900_ai_ci
--echo #
--echo #########################################################################

SET @@default_collation_for_utf8mb4 = utf8mb4_general_ci;

CREATE DATABASE db2 COLLATE utf8mb4_0900_ai_ci;
USE db2;

CREATE TABLE t2 (i INT) CHARSET latin1;

--echo #
--echo # Implicit COLLATE result in utf8mb4_0900_ai_ci (@@collation_database):
--echo #

ALTER TABLE t2 CONVERT TO CHARACTER SET DEFAULT;
SHOW CREATE TABLE t2;

--echo # Cleanup:
ALTER TABLE t2 CONVERT TO CHARACTER SET latin1;

--echo #
--echo # Explicit COLLATE should result in utf8mb4_bin:
--echo #

ALTER TABLE t2 CONVERT TO CHARACTER SET DEFAULT COLLATE utf8mb4_bin;
SHOW CREATE TABLE t2;

DROP DATABASE db2;

--echo #########################################################################
--echo #
--echo # 3. @@default_collation_for_utf8mb4 == utf8mb4_0900_ai_ci
--echo #    @@collation_database            == utf8mb4_general_ci
--echo #
--echo #########################################################################

SET @@default_collation_for_utf8mb4 = DEFAULT;
SELECT @@default_collation_for_utf8mb4;

CREATE DATABASE db3 COLLATE utf8mb4_general_ci;
USE db3;

CREATE TABLE t3 (i INT) CHARSET latin1;

--echo #
--echo # Implicit COLLATE should result in utf8mb4_general_ci (@@collation_database):
--echo #

ALTER TABLE t3 CONVERT TO CHARACTER SET DEFAULT;
SHOW CREATE TABLE t3;

--echo #
--echo # Explicit COLLATE should result in utf8mb4_bin:
--echo #

ALTER TABLE t3 CONVERT TO CHARACTER SET DEFAULT COLLATE utf8mb4_bin;
SHOW CREATE TABLE t3;

--echo #########################################################################
--echo #
--echo # 4.  Incompatible character set in @@character_set_database and COLLATE should fail:
--echo #
--echo #########################################################################

--error ER_COLLATION_CHARSET_MISMATCH
ALTER TABLE t3 CONVERT TO CHARACTER SET DEFAULT COLLATE cp1251_general_cs;

DROP DATABASE db3;

--echo # Cleanup

USE test;

--echo
--echo Bug #27714748: @@PARSER_MAX_MEM_SIZE DOES NOT WORK FOR ROUTINES
--echo

SET parser_max_mem_size = 10000000; # minimum allowed value
--let $s = `SELECT REPEAT('x', @@parser_max_mem_size)`
--disable_query_log
--error ER_CAPACITY_EXCEEDED
--eval CREATE PROCEDURE p() SELECT 1 FROM (SELECT '$s') a
--enable_query_log
SET parser_max_mem_size = default;

--echo #
--echo # Bug #28968848: SIMPLIFY MYSQL_YYABORT
--echo #

--error ER_PARSE_ERROR
CREATE PROCEDURE p1 () wrong syntax;

--echo #
--echo # Bug #25220656: THE "PERSIST" EXTENSION IS A RESERVED KEYWORD
--echo #

# PERSIST and PERSIST_ONLY are not reserved words any more:

SELECT 1 AS PERSIST, 2 AS PERSIST_ONLY;

# Negative checks for non-reserved words not allowed as role names:

--error ER_PARSE_ERROR
CREATE ROLE EVENT;
--error ER_PARSE_ERROR
CREATE ROLE FILE;
--error ER_PARSE_ERROR
CREATE ROLE NONE;
--error ER_PARSE_ERROR
CREATE ROLE PROCESS;
--error ER_PARSE_ERROR
CREATE ROLE PROXY;
--error ER_PARSE_ERROR
CREATE ROLE RELOAD;
--error ER_PARSE_ERROR
CREATE ROLE REPLICATION;
--error ER_PARSE_ERROR
CREATE ROLE RESOURCE;
--error ER_PARSE_ERROR
CREATE ROLE SUPER;

# Negative checks for non-reserved words not allowed as system variable names:

--error ER_PARSE_ERROR
SET GLOBAL = DEFAULT;
--error ER_PARSE_ERROR
SET LOCAL = DEFAULT;
--error ER_PARSE_ERROR
SET PERSIST = DEFAULT;
--error ER_PARSE_ERROR
SET PERSIST_ONLY = DEFAULT;
--error ER_PARSE_ERROR
SET SESSION = DEFAULT;
--echo #
--echo # Bug#29033659: SOME NON-RESERVED WORDS CAN'T BE USED AS SP LABELS
--echo #

DELIMITER |;

CREATE FUNCTION f1() RETURNS INT
BEGIN
  ACCOUNT: LOOP RETURN 1; END LOOP;
  ALWAYS: LOOP RETURN 1; END LOOP;
  BACKUP: LOOP RETURN 1; END LOOP;
  CLOSE: LOOP RETURN 1; END LOOP;
  FORMAT: LOOP RETURN 1; END LOOP;
  GROUP_REPLICATION: LOOP RETURN 1; END LOOP;
  HOST: LOOP RETURN 1; END LOOP;
  INVISIBLE: LOOP RETURN 1; END LOOP;
  OPEN: LOOP RETURN 1; END LOOP;
  OPTIONS: LOOP RETURN 1; END LOOP;
  OWNER: LOOP RETURN 1; END LOOP;
  PARSER: LOOP RETURN 1; END LOOP;
  PORT: LOOP RETURN 1; END LOOP;
  REMOVE: LOOP RETURN 1; END LOOP;
  RESTORE: LOOP RETURN 1; END LOOP;
  ROLE: LOOP RETURN 1; END LOOP;
  SECONDARY: LOOP RETURN 1; END LOOP;
  SECONDARY_ENGINE: LOOP RETURN 1; END LOOP;
  SECONDARY_LOAD: LOOP RETURN 1; END LOOP;
  SECONDARY_UNLOAD: LOOP RETURN 1; END LOOP;
  SECURITY: LOOP RETURN 1; END LOOP;
  SERVER: LOOP RETURN 1; END LOOP;
  SOCKET: LOOP RETURN 1; END LOOP;
  SONAME: LOOP RETURN 1; END LOOP;
  UPGRADE: LOOP RETURN 1; END LOOP;
  VISIBLE: LOOP RETURN 1; END LOOP;
  WRAPPER: LOOP RETURN 1; END LOOP;
END|

DELIMITER ;|

DROP FUNCTION f1;

--echo #
--echo # Bug#29205289: PARSER ACCEPTS UNDOCUMENTED SYNTAX: `... = TABLE_ALIAS`
--echo #

CREATE TABLE t1 (i INT);
--error ER_PARSE_ERROR
LOCK TABLES t1=a READ;
--error ER_PARSE_ERROR
HANDLER t1 OPEN=a;
--error ER_PARSE_ERROR
SELECT * FROM t1=a;
--error ER_PARSE_ERROR
SELECT * FROM (SELECT 1)=a;
--error ER_PARSE_ERROR
SELECT * FROM t1 JOIN t1=a;
--error ER_PARSE_ERROR
UPDATE t1=a SET i=0;
DROP TABLE t1;

--echo #
--echo # Bug #22320942: ODBC OUTER JOIN ESCAPE SEQUENCE SYNTAX IS BROKEN
--echo #

CREATE TEMPORARY TABLE t1 (i INT);
CREATE TEMPORARY TABLE t2 (i INT);

SELECT * FROM { OJ t1 LEFT JOIN t2 ON TRUE };

--error ER_PARSE_ERROR
SELECT * FROM { `OJ` t1 LEFT JOIN t2 ON TRUE };

--error ER_PARSE_ERROR
SELECT * FROM { random_identifier t1 LEFT JOIN t2 ON TRUE };

DROP TABLE t1, t2;

--echo #
--echo # Bug #28997518: "COLLATE X GENERATED ALWAYS ... COLLATE Y": X HAS
--echo #                 A PRIORITY OVER Y
--echo #

# Even compatible multiple COLLATE clauses should fail:

--error ER_PARSE_ERROR
CREATE TABLE t1 (
  x VARCHAR(10)
  COLLATE ascii_bin
  COLLATE ascii_bin
);

# Incompatible COLLATE clauses should fail with the same error message:

--error ER_PARSE_ERROR
CREATE TABLE t2 (
  x VARCHAR(10)
  COLLATE ascii_bin
  COLLATE ascii_general_ci
);

# Multiple COLLATE clauses mixed with other column attributes should fail too:

--error ER_PARSE_ERROR
CREATE TABLE t3 (
  x VARCHAR(10)
  COLLATE ascii_bin
  NOT NULL
  COLLATE ascii_bin
);

# Multiple COLLATE clauses should fail in gcol definitions (syntax 1):

--error ER_PARSE_ERROR
CREATE TABLE t3 (
  x VARCHAR(10)
  COLLATE ascii_bin
  GENERATED ALWAYS AS(NULL)
  COLLATE ascii_bin
);

# Multiple COLLATE clauses should fail in gcol definitions (syntax 2):

--error ER_PARSE_ERROR
CREATE TABLE t4 (
  x VARCHAR(10)
  GENERATED ALWAYS AS(NULL)
  COLLATE ascii_bin
  COLLATE ascii_bin
);

--echo #
--echo # Bug#28961997: REMOVE PARENTHESES INFORMATION FROM AST
--echo #

CREATE VIEW v1 AS (SELECT 1 ORDER BY 1) UNION (SELECT 3 ORDER BY 1) ORDER BY 1;
SHOW CREATE VIEW v1;
DROP VIEW v1;

--echo #
--echo # Bug#29871803: REQUIRED PARENTHESES NO LONGER PRINTED FOR CERTAIN UNION
--echo # STATEMENTS
--echo #
CREATE TABLE t1(a INTEGER);
CREATE TABLE t2(a INTEGER);
CREATE TABLE t3(a INTEGER, b INTEGER, c INTEGER);
INSERT INTO t3 VALUES(1, 10, 100), (2, 20, 200), (3, 30, 300), (4, 40, 400);
ANALYZE TABLE t1, t2, t3;

EXPLAIN (SELECT a FROM t1 ORDER BY a LIMIT 1) UNION SELECT a FROM t2;
EXPLAIN SELECT a FROM t1 UNION (SELECT a FROM t2 LIMIT 1);

CREATE VIEW v1 as (SELECT a FROM t1 ORDER BY a LIMIT 1) UNION SELECT a FROM t2;
SHOW CREATE VIEW v1;

CREATE VIEW v2 as SELECT a FROM t1 UNION (SELECT a FROM t2 ORDER BY a LIMIT 1);
SHOW CREATE VIEW v2;

EXPLAIN (SELECT * FROM t3 ORDER BY a LIMIT 3) ORDER BY b DESC LIMIT 2;

EXPLAIN ((SELECT * FROM t3 ORDER BY a LIMIT 3)
            ORDER BY b DESC LIMIT 2)
                ORDER BY c LIMIT 1;

DROP VIEW v1;
DROP VIEW v2;
DROP TABLE t1, t2, t3;

--echo #
--echo # Bug#30131161: SYNTAX ERROR WITH || WHEN USING PIPES_AS_CONCAT AFTER
--echo #               UPGRADING TO 8.0.17
--echo #

SET sql_mode=(SELECT CONCAT(@@sql_mode, ',PIPES_AS_CONCAT'));

# `||` should have higher precedence than `LIKE` and `ESCAPE`:

SELECT 'ab' LIKE 'a%', 'ab' LIKE 'a' || '%';
SELECT 'ab' NOT LIKE 'a%', 'ab' NOT LIKE 'a' || '%';

SELECT 'ab' LIKE 'ac', 'ab' LIKE 'a' || 'c';
SELECT 'ab' NOT LIKE 'ac', 'ab' NOT LIKE 'a' || 'c';

SELECT 'a%' LIKE 'a!%' ESCAPE '!', 'a%' LIKE 'a!' || '%' ESCAPE '!';
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '!', 'a%' NOT LIKE 'a!' || '%' ESCAPE '!';

SELECT 'a%' LIKE 'a!%' ESCAPE '$', 'a%' LIKE 'a!' || '%' ESCAPE '$';
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '$', 'a%' NOT LIKE 'a!' || '%' ESCAPE '$';

SELECT 'a%' LIKE 'a!%' ESCAPE '!', 'a%' LIKE 'a!%' ESCAPE '' || '!';
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '!', 'a%' NOT LIKE 'a!%' ESCAPE '' || '!';

SELECT 'a%' LIKE 'a!%' ESCAPE '' || '$', 'a%' LIKE 'a!%' ESCAPE '' || '$';
SELECT 'a%' NOT LIKE 'a!%' ESCAPE '' || '$', 'a%' NOT LIKE 'a!%' ESCAPE '' || '$';

# `||` should have a higher precedence than `^`:

SELECT 1 ^ 100, 1 ^ '10' || '0';

# `||` should have a lower precedence than the unary `-`:

SELECT -1 || '0';

SET sql_mode=DEFAULT;

--echo #
--echo # WL#13559: Deprecate syntax: confusing combinations of UNION and INTO
--echo #

--echo #
--echo # No warning expected:
--echo #

SELECT 1 UNION SELECT 1 INTO @var;
(SELECT 1 UNION SELECT 1 INTO @var);

SELECT 1 UNION SELECT 1 FROM DUAL INTO @var;
(SELECT 1 UNION SELECT 1 FROM DUAL INTO @var);

SELECT 1 UNION SELECT 1 FROM DUAL FOR UPDATE INTO @var;
(SELECT 1 UNION SELECT 1 FROM DUAL FOR UPDATE INTO @var);

--echo # Check that this also works with flatten_equal_set_ops
--echo # (minimum three operands to check this)
SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var;
(SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var);

SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL INTO @var;
(SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL INTO @var);

SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL FOR UPDATE INTO @var;
(SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL FOR UPDATE INTO @var);

--echo #
--echo # Deprecation warning expected:
--echo #

SELECT 1 UNION SELECT 1 INTO @var FROM DUAL;
SELECT 1 UNION (SELECT 1 INTO @var FROM DUAL);

SELECT 1 UNION SELECT 1 FROM DUAL INTO @var FOR UPDATE;
(SELECT 1 UNION SELECT 1 FROM DUAL INTO @var FOR UPDATE);

SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE;
(SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE);

--echo # Check that warning also works with flatten_equal_set_ops
--echo # (minimum three operands to check this)
SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var FROM DUAL;
SELECT 1 UNION SELECT 1 UNION (SELECT 1 INTO @var FROM DUAL);

SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL INTO @var FOR UPDATE;
(SELECT 1 UNION SELECT 1 UNION SELECT 1 FROM DUAL INTO @var FOR UPDATE);

SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE;
(SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var FOR UPDATE);

--echo # Check that PT_set_operation::has_into_clause works correctly
--echo # for more than two operands after flatten_equal_set_ops
--error ER_MISPLACED_INTO
SELECT 1 UNION (SELECT 1 INTO @var FROM DUAL) UNION SELECT 1;
--error ER_MISPLACED_INTO
SELECT 1 UNION SELECT 1 UNION SELECT * FROM (SELECT 2 UNION SELECT 1 INTO @var FROM DUAL) t;
--error ER_MISPLACED_INTO
SELECT 1 UNION SELECT 1 UNION SELECT * FROM (SELECT 1 UNION SELECT 1 UNION SELECT 1 INTO @var FROM DUAL) t;

--echo #
--echo # Syntax error expected:
--echo #

--error ER_PARSE_ERROR
SELECT 1 UNION (SELECT 1 FROM DUAL INTO @var);

--echo #
--echo # Bug#30871301: CTE CAN FAIL WITH A PARSE ERROR ON PSEUDO-COMMENTS
--echo #

WITH cte AS (SELECT 0 /*! ) */ SELECT * FROM cte a, cte b;
WITH cte AS /*! ( */ SELECT 0) SELECT * FROM cte a, cte b;

--echo #
--echo # Bug#30528450: SPECIAL SYMBOL NAMED COLUMN NOT HONORED IN A SELECT
--echo #

CREATE TABLE t1 (c1 INT, `*` INT, c3 INT);
INSERT INTO t1 VALUES (1, 2, 3);

SELECT `*` FROM t1;
SELECT t1.`*`, t1.* FROM t1;
SELECT test.t1.`*`, test.t1.* FROM t1;

DROP TABLE t1;

--echo #
--echo # Bug#30592703: UNDOCUMENTED SYNTAX
--echo #               '('<QUERY BLOCK>')' {LIMIT|ORDER [LIMIT]} INTO ...
--echo #

(SELECT 1) LIMIT 1 INTO @var;
SELECT @var;

(SELECT 2 AS c) ORDER BY c INTO @var;
SELECT @var;

(SELECT 3 AS c) ORDER BY c LIMIT 1 INTO @var;
SELECT @var;

(SELECT 4) INTO @var;
SELECT @var;

--echo #
--echo # <time zone> syntax.
--echo #

SET time_zone = '+01:00';

--error ER_NOT_SUPPORTED_YET
SELECT cast( DATE'2019-10-10' AT LOCAL AS DATETIME );
--error ER_INVALID_CAST
SELECT cast( DATE'2019-10-10' AT TIME ZONE 'UTC' AS DATETIME );

--error ER_NOT_SUPPORTED_YET
SELECT cast( TIME'10:10' AT LOCAL AS DATETIME );
--error ER_INVALID_CAST
SELECT cast( TIME'10:10' AT TIME ZONE '+01:00' AS DATETIME );
--error ER_INVALID_CAST
SELECT cast( TIME'10:10' AT TIME ZONE INTERVAL 'UTC' AS DATETIME );
--error ER_INVALID_CAST
SELECT cast( TIME'10:10' AT TIME ZONE '' AS DATETIME );
--error ER_INVALID_CAST
SELECT cast( TIME'10:10' AT TIME ZONE INTERVAL '' AS DATETIME );

--error ER_NOT_SUPPORTED_YET
SELECT cast( TIMESTAMP'2019-10-10 10:11:12' AT LOCAL AS DATETIME );

SELECT cast( NULL AT TIME ZONE 'UTC' AS DATETIME );

--echo # Casting to prohibited types
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS BINARY );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS CHAR );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS DATE );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS DECIMAL );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS DOUBLE );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS FLOAT );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS SIGNED INTEGER );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS TIME );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS UNSIGNED INTEGER );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS JSON );
--error ER_PARSE_ERROR
SELECT cast( TIMESTAMP'2019-10-10 10:40:00' AT TIME ZONE 'UTC' AS REAL );

--echo #
--echo # Bug#32230229: MISMATCH EXPLAIN RESULT TO ACTUAL RUN WITH
--echo #               USER-DEFINED VARIABLE
--echo #

# The next two statements used to give no error when running with
# EXPLAIN. Verify that they give the expected error.
--error ER_MULTIPLE_INTO_CLAUSES
EXPLAIN SELECT 1 INTO @x FROM DUAL INTO @y;
--error ER_MULTIPLE_INTO_CLAUSES
EXPLAIN SELECT 1 INTO DUMPFILE 'file1' FROM DUAL INTO DUMPFILE 'file2';
# The next statement gave the expected error also before the bug was
# fixed. Verify that it still does.
--error ER_MULTIPLE_INTO_CLAUSES
EXPLAIN SELECT 1 INTO OUTFILE 'file1' FROM DUAL INTO OUTFILE 'file2';

--echo #
--echo # Deprecate usage of "full" as unquoted identfier
--echo #
CREATE TABLE full(i INT);
DROP TABLE full;
CREATE TABLE `full`(i INT);
SELECT * from `full`;
SELECT * from `full` AS full;
SELECT * from `full` AS `full`;
SELECT * from full;
SELECT * from full as full;
SELECT * from `full` full;
--error ER_PARSE_ERROR
SELECT * from full outer;

SET @save_sql_mode=@@sql_mode;
SET SQL_MODE = 'ANSI_QUOTES';
SELECT * from `full`;
SELECT * from "full";
SELECT * from full;
SET @@sql_mode=@save_sql_mode;

DROP TABLE `full`;