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

5 files changed, 57 insertions, 16 deletions

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 ;|