diff options
Diffstat (limited to 'mysql-test')
23 files changed, 494 insertions, 26 deletions
diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index 79080bde8ed..0afe5298411 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -382,7 +382,7 @@ create table t1 as select concat(sign(0.5)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(21) DEFAULT NULL + `c1` varbinary(2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 as select concat(rand()) as c1; @@ -419,7 +419,7 @@ create table t1 as select concat(bit_length('a')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(10) DEFAULT NULL + `c1` varbinary(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(coercibility('a'))); @@ -469,7 +469,7 @@ create table t1 as select concat(ord(61)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(21) DEFAULT NULL + `c1` varbinary(7) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(find_in_set('b','a,b,c,d'))); @@ -696,7 +696,7 @@ create table t1 as select concat(sleep(0)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(21) DEFAULT NULL + `c1` varbinary(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(is_free_lock('xxxx'))); @@ -1447,7 +1447,7 @@ create table t1 as select concat(microsecond('12:00:00.123456')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varbinary(21) DEFAULT NULL + `c1` varbinary(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(month(20090224))); diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index f4f54d84e64..fabd7fa484c 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -791,7 +791,7 @@ create table t1 as select concat(sign(0.5)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(2) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 as select concat(rand()) as c1; @@ -828,7 +828,7 @@ create table t1 as select concat(bit_length('a')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(10) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(11) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(coercibility('a'))); @@ -878,7 +878,7 @@ create table t1 as select concat(ord(61)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(7) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(find_in_set('b','a,b,c,d'))); @@ -1105,7 +1105,7 @@ create table t1 as select concat(sleep(0)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(1) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(is_free_lock('xxxx'))); @@ -1856,7 +1856,7 @@ create table t1 as select concat(microsecond('12:00:00.123456')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET cp1251 DEFAULT NULL + `c1` varchar(6) CHARACTER SET cp1251 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(month(20090224))); diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index dab6bfc61df..94b25849a2a 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -1088,7 +1088,7 @@ create table t1 as select concat(sign(0.5)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) DEFAULT NULL + `c1` varchar(2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 as select concat(rand()) as c1; @@ -1125,7 +1125,7 @@ create table t1 as select concat(bit_length('a')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(10) DEFAULT NULL + `c1` varchar(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(coercibility('a'))); @@ -1175,7 +1175,7 @@ create table t1 as select concat(ord(61)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) DEFAULT NULL + `c1` varchar(7) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(find_in_set('b','a,b,c,d'))); @@ -1402,7 +1402,7 @@ create table t1 as select concat(sleep(0)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) DEFAULT NULL + `c1` varchar(1) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(is_free_lock('xxxx'))); @@ -2153,7 +2153,7 @@ create table t1 as select concat(microsecond('12:00:00.123456')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) DEFAULT NULL + `c1` varchar(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(month(20090224))); diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 101cfc47cf1..32095ff599b 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -1967,7 +1967,7 @@ create table t1 as select concat(sign(0.5)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(2) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 as select concat(rand()) as c1; @@ -2004,7 +2004,7 @@ create table t1 as select concat(bit_length('a')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(10) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(11) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(coercibility('a'))); @@ -2054,7 +2054,7 @@ create table t1 as select concat(ord(61)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(7) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(find_in_set('b','a,b,c,d'))); @@ -2281,7 +2281,7 @@ create table t1 as select concat(sleep(0)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(1) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(is_free_lock('xxxx'))); @@ -3032,7 +3032,7 @@ create table t1 as select concat(microsecond('12:00:00.123456')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET ucs2 DEFAULT NULL + `c1` varchar(6) CHARACTER SET ucs2 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(month(20090224))); diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 87ca13cd743..532bfa6d9e5 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -2839,7 +2839,7 @@ create table t1 as select concat(sign(0.5)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(2) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 as select concat(rand()) as c1; @@ -2876,7 +2876,7 @@ create table t1 as select concat(bit_length('a')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(10) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(11) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(coercibility('a'))); @@ -2926,7 +2926,7 @@ create table t1 as select concat(ord(61)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(7) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(find_in_set('b','a,b,c,d'))); @@ -3153,7 +3153,7 @@ create table t1 as select concat(sleep(0)) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(1) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(is_free_lock('xxxx'))); @@ -3904,7 +3904,7 @@ create table t1 as select concat(microsecond('12:00:00.123456')) as c1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c1` varchar(21) CHARACTER SET utf8 DEFAULT NULL + `c1` varchar(6) CHARACTER SET utf8 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; select hex(concat(month(20090224))); diff --git a/mysql-test/r/func_json.result b/mysql-test/r/func_json.result index 997d3e4d062..03e4c48a3e1 100644 --- a/mysql-test/r/func_json.result +++ b/mysql-test/r/func_json.result @@ -642,3 +642,29 @@ SELECT JSON_KEYS(f) FROM t1 ORDER BY 1; JSON_KEYS(f) NULL DROP TABLE t1; +# +# Start of 10.3 tests +# +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +SELECT +JSON_VALID('{"id": 1, "name": "Monty"}') AS json_valid, +JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") AS json_exists, +JSON_CONTAINS('{"A": 0, "B": {"C": 1}, "D": 2}', '2', '$.A') AS ison_contains, +JSON_CONTAINS_PATH('{"A": 1, "B": [2], "C": [3, 4]}', 'one', '$.A', '$.D') AS json_contains_path; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def json_valid 3 1 1 Y 32896 0 63 +def json_exists 3 1 1 Y 32896 0 63 +def ison_contains 3 1 1 Y 32896 0 63 +def json_contains_path 3 1 1 Y 32896 0 63 +json_valid json_exists ison_contains json_contains_path +1 1 0 1 +SELECT +JSON_LENGTH('{"a": 1, "b": {"c": 30}}') AS json_length, +JSON_DEPTH('[10, {"a": 20}]') AS json_depnth; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def json_length 3 10 1 Y 32896 0 63 +def json_depnth 3 10 1 N 32897 0 63 +json_length json_depnth +2 3 diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result index a90071021b8..39f8f41d1e7 100644 --- a/mysql-test/r/func_misc.result +++ b/mysql-test/r/func_misc.result @@ -1494,3 +1494,14 @@ DROP TABLE t1; # # End of 10.2 tests # +# +# Start of 10.3 tests +# +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +SELECT INET_ATON("255.255.255.255.255.255.255.255"); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def INET_ATON("255.255.255.255.255.255.255.255") 8 21 20 Y 32928 0 63 +INET_ATON("255.255.255.255.255.255.255.255") +18446744073709551615 diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index df773a15ec2..5e6716b94de 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -4318,5 +4318,28 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; # +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +SELECT +ST_ISSIMPLE(POINT(1,1)), +ST_ISRING(POINT(1,1)), +ST_ISCLOSED(POINT(1,1)), +ST_DIMENSION(POINT(1,1)), +ST_NUMGEOMETRIES(POINT(1,1)), +ST_NUMINTERIORRINGS(POINT(1,1)), +ST_NUMPOINTS(POINT(1,1)), +ST_SRID(POINT(1,1)); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def ST_ISSIMPLE(POINT(1,1)) 3 2 1 Y 32896 0 63 +def ST_ISRING(POINT(1,1)) 3 2 2 Y 32896 0 63 +def ST_ISCLOSED(POINT(1,1)) 3 2 2 Y 32896 0 63 +def ST_DIMENSION(POINT(1,1)) 3 10 1 Y 32896 0 63 +def ST_NUMGEOMETRIES(POINT(1,1)) 3 10 0 Y 32896 0 63 +def ST_NUMINTERIORRINGS(POINT(1,1)) 3 10 0 Y 32896 0 63 +def ST_NUMPOINTS(POINT(1,1)) 3 10 0 Y 32896 0 63 +def ST_SRID(POINT(1,1)) 3 10 1 Y 32896 0 63 +ST_ISSIMPLE(POINT(1,1)) ST_ISRING(POINT(1,1)) ST_ISCLOSED(POINT(1,1)) ST_DIMENSION(POINT(1,1)) ST_NUMGEOMETRIES(POINT(1,1)) ST_NUMINTERIORRINGS(POINT(1,1)) ST_NUMPOINTS(POINT(1,1)) ST_SRID(POINT(1,1)) +1 -1 -1 0 NULL NULL NULL 0 +# # End of 10.3 tests # diff --git a/mysql-test/r/metadata.result b/mysql-test/r/metadata.result index cc249a7eab7..bebd97610d4 100644 --- a/mysql-test/r/metadata.result +++ b/mysql-test/r/metadata.result @@ -400,3 +400,139 @@ def cast('01:01:01' as time) 11 10 8 Y 128 0 63 def cast('01:01:01' as time(2)) 11 13 11 Y 128 2 63 cast('01:01:01' as time) cast('01:01:01' as time(2)) 01:01:01 01:01:01.00 +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +SELECT +STRCMP('a','b'), +OCTET_LENGTH('a'), +CHAR_LENGTH('a'), +COERCIBILITY('a'), +ASCII('a'), +ORD('a'), +CRC32('a'), +UNCOMPRESSED_LENGTH(COMPRESS('a')); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def STRCMP('a','b') 3 2 2 N 32897 0 63 +def OCTET_LENGTH('a') 3 10 1 N 32897 0 63 +def CHAR_LENGTH('a') 3 10 1 N 32897 0 63 +def COERCIBILITY('a') 3 10 1 N 32897 0 63 +def ASCII('a') 3 3 2 N 32897 0 63 +def ORD('a') 3 7 2 N 32897 0 63 +def CRC32('a') 3 10 10 N 32929 0 63 +def UNCOMPRESSED_LENGTH(COMPRESS('a')) 3 10 1 Y 32896 0 63 +STRCMP('a','b') OCTET_LENGTH('a') CHAR_LENGTH('a') COERCIBILITY('a') ASCII('a') ORD('a') CRC32('a') UNCOMPRESSED_LENGTH(COMPRESS('a')) +-1 1 1 4 97 97 3904355907 1 +SELECT +INTERVAL(2,1,2,3), +REGEXP_INSTR('a','a'), +LOCATE('a','a'), +FIND_IN_SET('b','a,b,c,d'), +FIELD('a','a','b'); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def INTERVAL(2,1,2,3) 3 2 1 N 32897 0 63 +def REGEXP_INSTR('a','a') 3 11 1 N 32897 0 63 +def LOCATE('a','a') 3 11 1 N 32897 0 63 +def FIND_IN_SET('b','a,b,c,d') 3 3 1 N 32897 0 63 +def FIELD('a','a','b') 3 3 1 N 32897 0 63 +INTERVAL(2,1,2,3) REGEXP_INSTR('a','a') LOCATE('a','a') FIND_IN_SET('b','a,b,c,d') FIELD('a','a','b') +2 1 1 2 1 +SELECT +SIGN(1), +BIT_COUNT(1); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def SIGN(1) 3 2 1 N 32897 0 63 +def BIT_COUNT(1) 3 2 1 N 32897 0 63 +SIGN(1) BIT_COUNT(1) +1 1 +SELECT +BENCHMARK(0,0), +SLEEP(0); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def BENCHMARK(0,0) 3 1 1 N 32897 0 63 +def SLEEP(0) 3 1 1 N 32897 0 63 +BENCHMARK(0,0) SLEEP(0) +0 0 +SELECT +GET_LOCK('metadata',0), +IS_FREE_LOCK('metadata'), +RELEASE_LOCK('metadata'); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def GET_LOCK('metadata',0) 3 1 1 Y 32896 0 63 +def IS_FREE_LOCK('metadata') 3 1 1 Y 32896 0 63 +def RELEASE_LOCK('metadata') 3 1 1 Y 32896 0 63 +GET_LOCK('metadata',0) IS_FREE_LOCK('metadata') RELEASE_LOCK('metadata') +1 0 1 +SELECT +PERIOD_ADD(200801,2), +PERIOD_DIFF(200802,200703), +TO_DAYS('2007-10-07'), +DAYOFMONTH('2007-02-03'), +DAYOFWEEK('2007-02-03'), +TO_SECONDS('2013-06-13'); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def PERIOD_ADD(200801,2) 3 6 6 N 32897 0 63 +def PERIOD_DIFF(200802,200703) 3 6 2 N 32897 0 63 +def TO_DAYS('2007-10-07') 3 6 6 Y 32896 0 63 +def DAYOFMONTH('2007-02-03') 3 2 1 Y 32896 0 63 +def DAYOFWEEK('2007-02-03') 3 1 1 Y 32896 0 63 +def TO_SECONDS('2013-06-13') 3 6 11 Y 32896 0 63 +PERIOD_ADD(200801,2) PERIOD_DIFF(200802,200703) TO_DAYS('2007-10-07') DAYOFMONTH('2007-02-03') DAYOFWEEK('2007-02-03') TO_SECONDS('2013-06-13') +200803 11 733321 3 7 63538300800 +SELECT +YEAR('2001-02-03 04:05:06.000007'), +DAY('2001-02-03 04:05:06.000007'), +HOUR('2001-02-03 04:05:06.000007'), +MINUTE('2001-02-03 04:05:06.000007'), +SECOND('2001-02-03 04:05:06.000007'), +MICROSECOND('2001-02-03 04:05:06.000007'); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def YEAR('2001-02-03 04:05:06.000007') 3 4 4 Y 32896 0 63 +def DAY('2001-02-03 04:05:06.000007') 3 2 1 Y 32896 0 63 +def HOUR('2001-02-03 04:05:06.000007') 3 2 1 Y 32896 0 63 +def MINUTE('2001-02-03 04:05:06.000007') 3 2 1 Y 32896 0 63 +def SECOND('2001-02-03 04:05:06.000007') 3 2 1 Y 32896 0 63 +def MICROSECOND('2001-02-03 04:05:06.000007') 3 6 1 Y 32896 0 63 +YEAR('2001-02-03 04:05:06.000007') DAY('2001-02-03 04:05:06.000007') HOUR('2001-02-03 04:05:06.000007') MINUTE('2001-02-03 04:05:06.000007') SECOND('2001-02-03 04:05:06.000007') MICROSECOND('2001-02-03 04:05:06.000007') +2001 3 4 5 6 7 +SELECT +WEEK('2001-02-03 04:05:06.000007'), +QUARTER('2001-02-03 04:05:06.000007'), +YEARWEEK('2001-02-03 04:05:06.000007'); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def WEEK('2001-02-03 04:05:06.000007') 3 2 1 Y 32896 0 63 +def QUARTER('2001-02-03 04:05:06.000007') 3 1 1 Y 32896 0 63 +def YEARWEEK('2001-02-03 04:05:06.000007') 3 6 6 Y 32896 0 63 +WEEK('2001-02-03 04:05:06.000007') QUARTER('2001-02-03 04:05:06.000007') YEARWEEK('2001-02-03 04:05:06.000007') +4 1 200104 +SELECT BIT_LENGTH(10); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def BIT_LENGTH(10) 8 11 2 N 32897 0 63 +BIT_LENGTH(10) +16 +SELECT 1|2, 1&2, 1<<2, 1>>2, ~0, 1^2; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def 1|2 8 21 1 N 32929 0 63 +def 1&2 8 21 1 N 32929 0 63 +def 1<<2 8 21 1 N 32929 0 63 +def 1>>2 8 21 1 N 32929 0 63 +def ~0 8 21 20 N 32929 0 63 +def 1^2 8 21 1 N 32929 0 63 +1|2 1&2 1<<2 1>>2 ~0 1^2 +3 0 4 0 18446744073709551615 3 +SELECT LAST_INSERT_ID(); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def LAST_INSERT_ID() 8 21 1 N 32929 0 63 +LAST_INSERT_ID() +0 +SELECT ROW_COUNT(), FOUND_ROWS(); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def ROW_COUNT() 8 21 2 N 32897 0 63 +def FOUND_ROWS() 8 21 1 N 32897 0 63 +ROW_COUNT() FOUND_ROWS() +-1 1 +SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') 8 21 1 Y 32896 0 63 +TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') +3 diff --git a/mysql-test/suite/compat/oracle/r/func_misc.result b/mysql-test/suite/compat/oracle/r/func_misc.result index 4ea1a87b69a..0e2ba0c6f50 100644 --- a/mysql-test/suite/compat/oracle/r/func_misc.result +++ b/mysql-test/suite/compat/oracle/r/func_misc.result @@ -306,3 +306,14 @@ DROP TABLE t1; # # End of MDEV-10578 sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM # +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +BEGIN +SELECT SQLCODE; +END +$$ +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def SQLCODE 3 11 1 N 32897 0 63 +SQLCODE +0 diff --git a/mysql-test/suite/compat/oracle/r/sp-cursor.result b/mysql-test/suite/compat/oracle/r/sp-cursor.result index f50fb2c84e1..03211509f8b 100644 --- a/mysql-test/suite/compat/oracle/r/sp-cursor.result +++ b/mysql-test/suite/compat/oracle/r/sp-cursor.result @@ -160,7 +160,7 @@ CALL p1(); Table Create Table t2 CREATE TABLE "t2" ( "c%ISOPEN" int(1) NOT NULL, - "c%ROWCOUNT" bigint(21) NOT NULL, + "c%ROWCOUNT" bigint(21) DEFAULT NULL, "c%FOUND" int(1) DEFAULT NULL, "c%NOTFOUND" int(1) DEFAULT NULL ) @@ -927,3 +927,25 @@ x0 x1.a x1.b 100 10 Tbl-t1.b0 DROP PROCEDURE p1; DROP TABLE t1; +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +DECLARE +CURSOR c IS SELECT 1 AS c FROM DUAL; +BEGIN +OPEN c; +SELECT +c%ISOPEN, +c%NOTFOUND, +c%FOUND, +c%ROWCOUNT; +CLOSE c; +END; +$$ +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def c%ISOPEN 3 1 1 N 32897 0 63 +def c%NOTFOUND 3 1 0 Y 32896 0 63 +def c%FOUND 3 1 0 Y 32896 0 63 +def c%ROWCOUNT 8 21 1 Y 32896 0 63 +c%ISOPEN c%NOTFOUND c%FOUND c%ROWCOUNT +1 NULL NULL 0 diff --git a/mysql-test/suite/compat/oracle/r/sp.result b/mysql-test/suite/compat/oracle/r/sp.result index 5dd628da39d..a9158259f4a 100644 --- a/mysql-test/suite/compat/oracle/r/sp.result +++ b/mysql-test/suite/compat/oracle/r/sp.result @@ -2274,3 +2274,11 @@ This is p2 DROP PROCEDURE p3; DROP PROCEDURE p2; DROP PROCEDURE p1; +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +SELECT SQL%ROWCOUNT; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def SQL%ROWCOUNT 8 21 1 N 32897 0 63 +SQL%ROWCOUNT +0 diff --git a/mysql-test/suite/compat/oracle/t/func_misc.test b/mysql-test/suite/compat/oracle/t/func_misc.test index a356d7040d4..c5b42134f89 100644 --- a/mysql-test/suite/compat/oracle/t/func_misc.test +++ b/mysql-test/suite/compat/oracle/t/func_misc.test @@ -329,3 +329,18 @@ DROP TABLE t1; --echo # --echo # End of MDEV-10578 sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM --echo # + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +DELIMITER $$; +BEGIN + SELECT SQLCODE; +END +$$ +DELIMITER ;$$ +--enable_ps_protocol +--disable_metadata diff --git a/mysql-test/suite/compat/oracle/t/sp-cursor.test b/mysql-test/suite/compat/oracle/t/sp-cursor.test index ff9ad78e680..5a8b7b69f67 100644 --- a/mysql-test/suite/compat/oracle/t/sp-cursor.test +++ b/mysql-test/suite/compat/oracle/t/sp-cursor.test @@ -929,3 +929,26 @@ DELIMITER ;$$ CALL p1(); DROP PROCEDURE p1; DROP TABLE t1; + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +DELIMITER $$; +DECLARE + CURSOR c IS SELECT 1 AS c FROM DUAL; +BEGIN + OPEN c; + SELECT + c%ISOPEN, + c%NOTFOUND, + c%FOUND, + c%ROWCOUNT; + CLOSE c; +END; +$$ +DELIMITER ;$$ +--enable_ps_protocol +--disable_metadata diff --git a/mysql-test/suite/compat/oracle/t/sp.test b/mysql-test/suite/compat/oracle/t/sp.test index 9e137405878..4717ebef8eb 100644 --- a/mysql-test/suite/compat/oracle/t/sp.test +++ b/mysql-test/suite/compat/oracle/t/sp.test @@ -2120,3 +2120,14 @@ CALL p3; DROP PROCEDURE p3; DROP PROCEDURE p2; DROP PROCEDURE p1; + + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +SELECT SQL%ROWCOUNT; +--enable_ps_protocol +--disable_metadata diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result index 84f91bc0bdd..1e0b7e28a10 100644 --- a/mysql-test/suite/sql_sequence/next.result +++ b/mysql-test/suite/sql_sequence/next.result @@ -412,3 +412,16 @@ next value for t1 select next value for t1, min_value; ERROR 42S22: Unknown column 'min_value' in 'field list' drop sequence t1; +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +CREATE SEQUENCE s1; +SELECT +NEXT VALUE FOR s1, +PREVIOUS VALUE FOR s1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def NEXT VALUE FOR s1 8 20 1 Y 32896 0 63 +def PREVIOUS VALUE FOR s1 8 20 1 Y 32896 0 63 +NEXT VALUE FOR s1 PREVIOUS VALUE FOR s1 +1 1 +DROP SEQUENCE s1; diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test index 472feafb2c6..8ba9ba1277c 100644 --- a/mysql-test/suite/sql_sequence/next.test +++ b/mysql-test/suite/sql_sequence/next.test @@ -200,3 +200,17 @@ select next value for t1; --error ER_BAD_FIELD_ERROR select next value for t1, min_value; drop sequence t1; + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +CREATE SEQUENCE s1; +SELECT + NEXT VALUE FOR s1, + PREVIOUS VALUE FOR s1; +DROP SEQUENCE s1; +--enable_ps_protocol +--disable_metadata diff --git a/mysql-test/suite/sql_sequence/setval.result b/mysql-test/suite/sql_sequence/setval.result index 2fe46ff90c2..8e68a717cbf 100644 --- a/mysql-test/suite/sql_sequence/setval.result +++ b/mysql-test/suite/sql_sequence/setval.result @@ -244,3 +244,13 @@ create table t1 (a int); select setval(t1,10); ERROR 42S02: 'test.t1' is not a SEQUENCE drop table t1; +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +CREATE SEQUENCE s1; +SELECT SETVAL(s1,10); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def SETVAL(s1,10) 8 20 2 Y 32896 0 63 +SETVAL(s1,10) +10 +DROP SEQUENCE s1; diff --git a/mysql-test/suite/sql_sequence/setval.test b/mysql-test/suite/sql_sequence/setval.test index fe0c0669494..dbb2620d4e4 100644 --- a/mysql-test/suite/sql_sequence/setval.test +++ b/mysql-test/suite/sql_sequence/setval.test @@ -124,3 +124,16 @@ create table t1 (a int); --error ER_NOT_SEQUENCE select setval(t1,10); drop table t1; + + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +CREATE SEQUENCE s1; +SELECT SETVAL(s1,10); +DROP SEQUENCE s1; +--enable_ps_protocol +--disable_metadata diff --git a/mysql-test/t/func_json.test b/mysql-test/t/func_json.test index 0fc8e97b284..ee7e51241d4 100644 --- a/mysql-test/t/func_json.test +++ b/mysql-test/t/func_json.test @@ -296,3 +296,26 @@ INSERT INTO t1 VALUES (0); SELECT JSON_KEYS(f) FROM t1 ORDER BY 1; DROP TABLE t1; +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol + +SELECT + JSON_VALID('{"id": 1, "name": "Monty"}') AS json_valid, + JSON_EXISTS('{"key1":"xxxx", "key2":[1, 2, 3]}', "$.key2") AS json_exists, + JSON_CONTAINS('{"A": 0, "B": {"C": 1}, "D": 2}', '2', '$.A') AS ison_contains, + JSON_CONTAINS_PATH('{"A": 1, "B": [2], "C": [3, 4]}', 'one', '$.A', '$.D') AS json_contains_path; + +SELECT + JSON_LENGTH('{"a": 1, "b": {"c": 30}}') AS json_length, + JSON_DEPTH('[10, {"a": 20}]') AS json_depnth; + +--enable_ps_protocol +--disable_metadata diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index f931352c0e2..c661819424a 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -1127,3 +1127,19 @@ DROP TABLE t1; --echo # --echo # End of 10.2 tests --echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol + +SELECT INET_ATON("255.255.255.255.255.255.255.255"); + +--enable_ps_protocol +--disable_metadata diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index ab9c792d523..b38e68fe5e7 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -2307,5 +2307,24 @@ DROP TABLE t1; --echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +--disable_ps_protocol +SELECT + ST_ISSIMPLE(POINT(1,1)), + ST_ISRING(POINT(1,1)), + ST_ISCLOSED(POINT(1,1)), + ST_DIMENSION(POINT(1,1)), + ST_NUMGEOMETRIES(POINT(1,1)), + ST_NUMINTERIORRINGS(POINT(1,1)), + ST_NUMPOINTS(POINT(1,1)), + ST_SRID(POINT(1,1)); +--enable_ps_protocol +--disable_metadata + + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/t/metadata.test b/mysql-test/t/metadata.test index 60c7470cb67..0bd1b98c07d 100644 --- a/mysql-test/t/metadata.test +++ b/mysql-test/t/metadata.test @@ -252,3 +252,77 @@ drop table t1; --enable_metadata select cast('01:01:01' as time), cast('01:01:01' as time(2)); --disable_metadata + + +--echo # +--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +--echo # + +--enable_metadata +SELECT + STRCMP('a','b'), + OCTET_LENGTH('a'), + CHAR_LENGTH('a'), + COERCIBILITY('a'), + ASCII('a'), + ORD('a'), + CRC32('a'), + UNCOMPRESSED_LENGTH(COMPRESS('a')); + +SELECT + INTERVAL(2,1,2,3), + REGEXP_INSTR('a','a'), + LOCATE('a','a'), + FIND_IN_SET('b','a,b,c,d'), + FIELD('a','a','b'); + +SELECT + SIGN(1), + BIT_COUNT(1); + + +SELECT + BENCHMARK(0,0), + SLEEP(0); + +SELECT + GET_LOCK('metadata',0), + IS_FREE_LOCK('metadata'), + RELEASE_LOCK('metadata'); + +# Metadata the following functions is not deterministic +#SELECT CONNECTION_ID(); +#SELECT IS_FREE_LOCK('metadata'); +#SELECT UUID_SHORT(); + + +SELECT + PERIOD_ADD(200801,2), + PERIOD_DIFF(200802,200703), + TO_DAYS('2007-10-07'), + DAYOFMONTH('2007-02-03'), + DAYOFWEEK('2007-02-03'), + TO_SECONDS('2013-06-13'); + +SELECT + YEAR('2001-02-03 04:05:06.000007'), + DAY('2001-02-03 04:05:06.000007'), + HOUR('2001-02-03 04:05:06.000007'), + MINUTE('2001-02-03 04:05:06.000007'), + SECOND('2001-02-03 04:05:06.000007'), + MICROSECOND('2001-02-03 04:05:06.000007'); + +SELECT + WEEK('2001-02-03 04:05:06.000007'), + QUARTER('2001-02-03 04:05:06.000007'), + YEARWEEK('2001-02-03 04:05:06.000007'); + +--disable_metadata + +--enable_metadata +SELECT BIT_LENGTH(10); +SELECT 1|2, 1&2, 1<<2, 1>>2, ~0, 1^2; +SELECT LAST_INSERT_ID(); +SELECT ROW_COUNT(), FOUND_ROWS(); +SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); +--disable_metadata |