parser cleanup: don't store field properties in LEX, use Create_field directly

length/dec/charset are still in LEX, because they're also used
for CAST and dynamic columns.

also
1. fix "MDEV-7041 COLLATION(CAST('a' AS CHAR BINARY)) returns a wrong result"
2. allow BINARY modifier in stored function RETURN clause
3. allow "COLLATION without CHARSET" in SP/SF (parameters, RETURN, DECLARE)
4. print correct variable name in error messages for stored routine parameters

12 files changed, 128 insertions, 37 deletions

diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index c81af134add..29f5f0cf2d5 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -796,3 +796,26 @@ DATE("foo")
 NULL
 Warnings:
 Warning	1292	Incorrect datetime value: 'foo'
+create table t1 (a int, b char(5) as (cast("a" as char(10) binary) + a) );
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` char(5) AS (cast("a" as char(10) binary) + a) VIRTUAL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table t1;
+select collation(cast("a" as char(10) binary));
+collation(cast("a" as char(10) binary))
+latin1_bin
+select collation(cast("a" as char(10) charset utf8 binary));
+collation(cast("a" as char(10) charset utf8 binary))
+utf8_bin
+select collation(cast("a" as char(10) ascii binary));
+collation(cast("a" as char(10) ascii binary))
+latin1_bin
+select collation(cast("a" as char(10) binary charset utf8));
+collation(cast("a" as char(10) binary charset utf8))
+utf8_bin
+select collation(cast("a" as char(10) binary ascii));
+collation(cast("a" as char(10) binary ascii))
+latin1_bin
diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
index 3250d3cb838..740319a9dcd 100644
--- a/mysql-test/r/ctype_ucs.result
+++ b/mysql-test/r/ctype_ucs.result
@@ -5535,3 +5535,12 @@ a	aa
 #
 # End of 10.0 tests
 #
+select collation(cast("a" as char(10) unicode binary));
+collation(cast("a" as char(10) unicode binary))
+ucs2_bin
+select collation(cast("a" as char(10) binary unicode));
+collation(cast("a" as char(10) binary unicode))
+ucs2_bin
+#
+# End of 10.1 tests
+#
diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result
index 71fab8c9654..7569673c68c 100644
--- a/mysql-test/r/sp-error.result
+++ b/mysql-test/r/sp-error.result
@@ -1411,7 +1411,7 @@ end|
 ERROR 0A000: Not allowed to return a result set from a function
 drop function if exists bug20701;
 create function bug20701() returns varchar(25) binary return "test";
-ERROR 42000: This version of MariaDB doesn't yet support 'return value collation'
+drop function bug20701;
 create function bug20701() returns varchar(25) return "test";
 drop function bug20701;
 create procedure proc_26503_error_1()
diff --git a/mysql-test/r/sp-ucs2.result b/mysql-test/r/sp-ucs2.result
index 605fe4ed151..c7a3ed6e46a 100644
--- a/mysql-test/r/sp-ucs2.result
+++ b/mysql-test/r/sp-ucs2.result
@@ -106,20 +106,20 @@ RETURNS VARCHAR(64) CHARACTER SET ucs2
 BEGIN
 RETURN 'str';
 END|
-ERROR 42000: This version of MariaDB doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'
+ERROR 42000: COLLATION 'ucs2_unicode_ci' is not valid for CHARACTER SET 'latin1'
 CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
 RETURNS VARCHAR(64) COLLATE ucs2_unicode_ci
 BEGIN
 RETURN 'str';
 END|
-ERROR 42000: This version of MariaDB doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'
+ERROR 42000: COLLATION 'ucs2_unicode_ci' is not valid for CHARACTER SET 'latin1'
 CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
 RETURNS VARCHAR(64) CHARACTER SET ucs2
 BEGIN
 DECLARE f2 VARCHAR(64) COLLATE ucs2_unicode_ci;
 RETURN 'str';
 END|
-ERROR 42000: This version of MariaDB doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'
+ERROR 42000: COLLATION 'ucs2_unicode_ci' is not valid for CHARACTER SET 'latin1'
 SET NAMES utf8;
 DROP FUNCTION IF EXISTS bug48766;
 CREATE FUNCTION bug48766 ()
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 56d8ea62f44..4fa43e82a1d 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -3548,7 +3548,10 @@ begin
 set f1= concat( 'hello', f1 );
 return f1;
 end|
-ERROR 42000: This version of MariaDB doesn't yet support 'return value collation'
+select collation(bug9048("foo"))|
+collation(bug9048("foo"))
+latin1_bin
+drop function bug9048|
 drop procedure if exists bug12849_1|
 create procedure bug12849_1(inout x char) select x into x|
 set @var='a'|
@@ -7858,3 +7861,22 @@ v1
 DROP PROCEDURE p1;
 DROP TABLE t1;
 # End of 5.5 test
+CREATE FUNCTION f(f1 VARCHAR(64) COLLATE latin1_german2_ci)
+RETURNS VARCHAR(64)
+BEGIN
+RETURN 'str';
+END|
+DROP FUNCTION f|
+CREATE FUNCTION f(f1 VARCHAR(64))
+RETURNS VARCHAR(64) COLLATE latin1_german2_ci
+BEGIN
+RETURN 'str';
+END|
+DROP FUNCTION f|
+CREATE FUNCTION f(f1 VARCHAR(64))
+RETURNS VARCHAR(64)
+BEGIN
+DECLARE f2 VARCHAR(64) COLLATE latin1_german2_ci;
+RETURN 'str';
+END|
+DROP FUNCTION f|
diff --git a/mysql-test/suite/funcs_1/r/storedproc.result b/mysql-test/suite/funcs_1/r/storedproc.result
index 4ec9bfbd512..f316bdb7ba2 100644
--- a/mysql-test/suite/funcs_1/r/storedproc.result
+++ b/mysql-test/suite/funcs_1/r/storedproc.result
@@ -142,7 +142,7 @@ BEGIN
 SET @v1 = f1;
 SELECT @v1;
 END//
-ERROR 42000: Too big precision 256 specified for ''. Maximum is 65.
+ERROR 42000: Too big precision 256 specified for 'f1'. Maximum is 65.
 DROP PROCEDURE IF EXISTS sp1//
 Warnings:
 Note	1305	PROCEDURE db_storedproc.sp1 does not exist
@@ -152,7 +152,7 @@ BEGIN
 SET @v1 = f1;
 SELECT @v1;
 END//
-ERROR 42000: Too big precision 66 specified for ''. Maximum is 65.
+ERROR 42000: Too big precision 66 specified for 'f1'. Maximum is 65.
 DROP PROCEDURE IF EXISTS sp1//
 Warnings:
 Note	1305	PROCEDURE db_storedproc.sp1 does not exist
@@ -1407,12 +1407,12 @@ BEGIN
 SELECT f1;
 END//
 Warnings:
-Note	1291	Column '' has duplicated value 'value1' in ENUM
+Note	1291	Column 'f1' has duplicated value 'value1' in ENUM
 CALL sp1( "value1" );
 f1
 value1
 Warnings:
-Note	1291	Column '' has duplicated value 'value1' in ENUM
+Note	1291	Column 'f1' has duplicated value 'value1' in ENUM
 SHOW PROCEDURE STATUS WHERE db = 'db_storedproc';
 Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
 db_storedproc	sp1	PROCEDURE	root@localhost	<modified>	<created>	INVOKER	this is simple	latin1	latin1_swedish_ci	latin1_swedish_ci
@@ -1423,12 +1423,12 @@ BEGIN
 SELECT f1;
 END//
 Warnings:
-Note	1291	Column '' has duplicated value 'value1' in SET
+Note	1291	Column 'f1' has duplicated value 'value1' in SET
 CALL sp1( "value1, value1" );
 f1
 value1
 Warnings:
-Note	1291	Column '' has duplicated value 'value1' in SET
+Note	1291	Column 'f1' has duplicated value 'value1' in SET
 Warning	1265	Data truncated for column 'f1' at row 1
 SHOW PROCEDURE STATUS WHERE db = 'db_storedproc';
 Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
@@ -1440,12 +1440,12 @@ BEGIN
 SELECT f1;
 END//
 Warnings:
-Note	1291	Column '' has duplicated value 'value1' in ENUM
+Note	1291	Column 'f1' has duplicated value 'value1' in ENUM
 CALL sp1( "value1" );
 f1
 value1
 Warnings:
-Note	1291	Column '' has duplicated value 'value1' in ENUM
+Note	1291	Column 'f1' has duplicated value 'value1' in ENUM
 SHOW PROCEDURE STATUS WHERE db = 'db_storedproc';
 Db	Name	Type	Definer	Modified	Created	Security_type	Comment	character_set_client	collation_connection	Database Collation
 db_storedproc	sp1	PROCEDURE	root@localhost	<modified>	<created>	INVOKER	this is simple	latin1	latin1_swedish_ci	latin1_swedish_ci
@@ -1548,7 +1548,7 @@ BEGIN
 SET f1 = 1000000 + f1;
 RETURN f1;
 END//
-ERROR 42000: Too big scale 31 specified for ''. Maximum is 30.
+ERROR 42000: Too big scale 31 specified for 'f1'. Maximum is 30.
 SELECT fn1( 1.3326e+8 );
 ERROR 42000: FUNCTION db_storedproc.fn1 does not exist
 CREATE FUNCTION fn1( f1 DECIMAL(63, 30) ) RETURNS DECIMAL(63, 30)
@@ -1570,7 +1570,7 @@ BEGIN
 RETURN f1;
 END//
 Warnings:
-Note	1291	Column '' has duplicated value 'value1' in ENUM
+Note	1291	Column 'f1' has duplicated value 'value1' in ENUM
 SELECT fn1( "value1" );
 fn1( "value1" )
 1.000000000000000000000000000000
@@ -1584,7 +1584,7 @@ BEGIN
 RETURN f1;
 END//
 Warnings:
-Note	1291	Column '' has duplicated value 'value1' in SET
+Note	1291	Column 'f1' has duplicated value 'value1' in SET
 SELECT fn1( "value1, value1" );
 fn1( "value1, value1" )
 1.000000000000000000000000000000
@@ -3119,10 +3119,7 @@ return f1;
 DROP FUNCTION IF EXISTS fn1;
 CREATE FUNCTION fn1(f1 char binary ) returns char binary
 return f1;
-ERROR 42000: This version of MariaDB doesn't yet support 'return value collation'
 DROP FUNCTION IF EXISTS fn1;
-Warnings:
-Note	1305	FUNCTION db_storedproc.fn1 does not exist
 CREATE FUNCTION fn1(f1 char ascii ) returns char ascii
 return f1;
 DROP FUNCTION IF EXISTS fn1;
@@ -5836,7 +5833,7 @@ fetch cur1 into e;
 SELECT x, y, z, a, b, c, d, e;
 close cur1;
 END//
-ERROR 42000: Too big scale 255 specified for ''. Maximum is 30.
+ERROR 42000: Too big scale 255 specified for 'b'. Maximum is 30.
 CALL sp6();
 ERROR 42000: PROCEDURE db_storedproc.sp6 does not exist
 DROP PROCEDURE IF EXISTS sp6;
diff --git a/mysql-test/suite/funcs_1/t/storedproc.test b/mysql-test/suite/funcs_1/t/storedproc.test
index 69406a57a00..ff417957c85 100644
--- a/mysql-test/suite/funcs_1/t/storedproc.test
+++ b/mysql-test/suite/funcs_1/t/storedproc.test
@@ -2155,7 +2155,6 @@ CREATE FUNCTION fn1(f1 char ) returns char
     return f1;
 
 DROP FUNCTION IF EXISTS fn1;
---error ER_NOT_SUPPORTED_YET
 CREATE FUNCTION fn1(f1 char binary ) returns char binary
     return f1;
 
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index b6c37cacd8a..58f91571ebb 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -456,3 +456,19 @@ SELECT CAST(TIME('10:20:30') AS DATE) + INTERVAL 1 DAY;
 SET SQL_MODE=ALLOW_INVALID_DATES;
 SELECT DATE("foo");
 
+#
+# CAST and field definition using same fields in LEX
+#
+create table t1 (a int, b char(5) as (cast("a" as char(10) binary) + a) );
+show create table t1;
+drop table t1;
+
+#
+# CAST (... BINARY)
+#
+select collation(cast("a" as char(10) binary));
+select collation(cast("a" as char(10) charset utf8 binary));
+select collation(cast("a" as char(10) ascii binary));
+select collation(cast("a" as char(10) binary charset utf8));
+select collation(cast("a" as char(10) binary ascii));
+
diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test
index 33d41e9bc4c..1dee393b90b 100644
--- a/mysql-test/t/ctype_ucs.test
+++ b/mysql-test/t/ctype_ucs.test
@@ -914,7 +914,16 @@ SELECT CONCAT(CONVERT('pi=' USING ucs2),PI()) AS PI;
 SET NAMES utf8, character_set_connection=ucs2;
 SELECT 'a','aa';
 
-
 --echo #
 --echo # End of 10.0 tests
 --echo #
+
+#
+# CAST (... BINARY)
+#
+select collation(cast("a" as char(10) unicode binary));
+select collation(cast("a" as char(10) binary unicode));
+
+--echo #
+--echo # End of 10.1 tests
+--echo #
diff --git a/mysql-test/t/sp-error.test b/mysql-test/t/sp-error.test
index 711e639191e..a16ef1205b9 100644
--- a/mysql-test/t/sp-error.test
+++ b/mysql-test/t/sp-error.test
@@ -2051,13 +2051,8 @@ delimiter ;|
 --disable_warnings
 drop function if exists bug20701;
 --enable_warnings
-#
-# This was disabled in 5.1.12. See bug #20701
-# When collation support in SP is implemented, then this test should
-# be removed.
-#
---error ER_NOT_SUPPORTED_YET
 create function bug20701() returns varchar(25) binary return "test";
+drop function bug20701;
 create function bug20701() returns varchar(25) return "test";
 drop function bug20701;
 
diff --git a/mysql-test/t/sp-ucs2.test b/mysql-test/t/sp-ucs2.test
index f6f70c3c103..3532169e5a1 100644
--- a/mysql-test/t/sp-ucs2.test
+++ b/mysql-test/t/sp-ucs2.test
@@ -114,7 +114,7 @@ DROP FUNCTION f1|
 #
 # COLLATE with no CHARACTER SET in IN param
 #
---error ER_NOT_SUPPORTED_YET
+--error ER_COLLATION_CHARSET_MISMATCH
 CREATE FUNCTION f(f1 VARCHAR(64) COLLATE ucs2_unicode_ci)
   RETURNS VARCHAR(64) CHARACTER SET ucs2
 BEGIN
@@ -125,7 +125,7 @@ END|
 #
 # COLLATE with no CHARACTER SET in RETURNS
 #
---error ER_NOT_SUPPORTED_YET
+--error ER_COLLATION_CHARSET_MISMATCH
 CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
   RETURNS VARCHAR(64) COLLATE ucs2_unicode_ci
 BEGIN
@@ -136,7 +136,7 @@ END|
 #
 # COLLATE with no CHARACTER SET in DECLARE
 #
---error ER_NOT_SUPPORTED_YET
+--error ER_COLLATION_CHARSET_MISMATCH
 CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
   RETURNS VARCHAR(64) CHARACTER SET ucs2
 BEGIN
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 5179bb1b03d..3f635acaac9 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -4355,17 +4355,14 @@ begin
   return f1;
 end|
 drop function bug9048|
-#
-# This was disabled in 5.1.12. See bug #20701
-# When collation support in SP is implemented, then this test should
-# be removed.
-#
---error ER_NOT_SUPPORTED_YET
+
 create function bug9048(f1 char binary) returns char binary
 begin
   set f1= concat( 'hello', f1 );
   return f1;
 end|
+select collation(bug9048("foo"))|
+drop function bug9048|
 
 # Bug #12849 Stored Procedure: Crash on procedure call with CHAR type
 # 'INOUT' parameter
@@ -9303,3 +9300,27 @@ DROP PROCEDURE p1;
 DROP TABLE t1;
 
 --echo # End of 5.5 test
+
+DELIMITER |;
+CREATE FUNCTION f(f1 VARCHAR(64) COLLATE latin1_german2_ci)
+  RETURNS VARCHAR(64)
+BEGIN
+  RETURN 'str';
+END|
+DROP FUNCTION f|
+
+CREATE FUNCTION f(f1 VARCHAR(64))
+  RETURNS VARCHAR(64) COLLATE latin1_german2_ci
+BEGIN
+  RETURN 'str';
+END|
+DROP FUNCTION f|
+
+CREATE FUNCTION f(f1 VARCHAR(64))
+  RETURNS VARCHAR(64)
+BEGIN
+  DECLARE f2 VARCHAR(64) COLLATE latin1_german2_ci;
+  RETURN 'str';
+END|
+DROP FUNCTION f|
+DELIMITER ;|