diff options
Diffstat (limited to 'mysql-test/r/func_misc.result')
| -rw-r--r-- | mysql-test/r/func_misc.result | 1553 |
1 files changed, 0 insertions, 1553 deletions
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result deleted file mode 100644 index 804a563f401..00000000000 --- a/mysql-test/r/func_misc.result +++ /dev/null @@ -1,1553 +0,0 @@ -DROP TABLE IF EXISTS t1, t2; -select format(1.5555,0),format(123.5555,1),format(1234.5555,2),format(12345.55555,3),format(123456.5555,4),format(1234567.5555,5),format("12345.2399",2); -format(1.5555,0) format(123.5555,1) format(1234.5555,2) format(12345.55555,3) format(123456.5555,4) format(1234567.5555,5) format("12345.2399",2) -2 123.6 1,234.56 12,345.556 123,456.5555 1,234,567.55550 12,345.24 -select inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")); -inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")) -NULL -select inet_aton("255.255.255.255.255"),inet_aton("255.255.1.255"),inet_aton("0.1.255"); -inet_aton("255.255.255.255.255") inet_aton("255.255.1.255") inet_aton("0.1.255") -1099511627775 4294902271 65791 -select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); -inet_ntoa(1099511627775) inet_ntoa(4294902271) inet_ntoa(511) -NULL 255.255.1.255 0.0.1.255 -select hex(inet_aton('127')); -hex(inet_aton('127')) -7F -select hex(inet_aton('127.1')); -hex(inet_aton('127.1')) -7F000001 -select hex(inet_aton('127.1.1')); -hex(inet_aton('127.1.1')) -7F010001 -select length(uuid()), charset(uuid()), length(unhex(replace(uuid(),_utf8'-',_utf8''))); -length(uuid()) charset(uuid()) length(unhex(replace(uuid(),_utf8'-',_utf8''))) -36 utf8 16 -set @a= uuid_short(); -set @b= uuid_short(); -select @b - @a; -@b - @a -1 -select length(format('nan', 2)) > 0; -length(format('nan', 2)) > 0 -1 -Warnings: -Warning 1292 Truncated incorrect DOUBLE value: 'nan' -select concat("$",format(2500,2)); -concat("$",format(2500,2)) -$2,500.00 -create table t1 ( a timestamp ); -insert into t1 values ( '2004-01-06 12:34' ); -select a from t1 where left(a+0,6) in ( left(20040106,6) ); -a -2004-01-06 12:34:00 -select a from t1 where left(a+0,6) = ( left(20040106,6) ); -a -2004-01-06 12:34:00 -select a from t1 where right(a+0,6) in ( right(20040106123400,6) ); -a -2004-01-06 12:34:00 -select a from t1 where right(a+0,6) = ( right(20040106123400,6) ); -a -2004-01-06 12:34:00 -select a from t1 where mid(a+0,6,3) in ( mid(20040106123400,6,3) ); -a -2004-01-06 12:34:00 -select a from t1 where mid(a+0,6,3) = ( mid(20040106123400,6,3) ); -a -2004-01-06 12:34:00 -drop table t1; -CREATE TABLE t1 (conn CHAR(7), connection_id INT); -INSERT INTO t1 VALUES ('default', CONNECTION_ID()); -SELECT GET_LOCK('bug16501',600); -GET_LOCK('bug16501',600) -1 -connect con1,localhost,root,,; -INSERT INTO t1 VALUES ('con1', CONNECTION_ID()); -SELECT IS_USED_LOCK('bug16501') = connection_id -FROM t1 -WHERE conn = 'default'; -IS_USED_LOCK('bug16501') = connection_id -1 -SELECT GET_LOCK('bug16501',600); -connection default; -SELECT IS_USED_LOCK('bug16501') = CONNECTION_ID(); -IS_USED_LOCK('bug16501') = CONNECTION_ID() -1 -SELECT RELEASE_LOCK('bug16501'); -RELEASE_LOCK('bug16501') -1 -connection con1; -GET_LOCK('bug16501',600) -1 -connection default; -SELECT IS_USED_LOCK('bug16501') = connection_id -FROM t1 -WHERE conn = 'con1'; -IS_USED_LOCK('bug16501') = connection_id -1 -connection con1; -SELECT IS_USED_LOCK('bug16501') = CONNECTION_ID(); -IS_USED_LOCK('bug16501') = CONNECTION_ID() -1 -SELECT RELEASE_LOCK('bug16501'); -RELEASE_LOCK('bug16501') -1 -SELECT IS_USED_LOCK('bug16501'); -IS_USED_LOCK('bug16501') -NULL -disconnect con1; -connection default; -DROP TABLE t1; -select export_set(3, _latin1'foo', _utf8'bar', ',', 4); -export_set(3, _latin1'foo', _utf8'bar', ',', 4) -foo,foo,bar,bar -End of 4.1 tests -create table t1 as select uuid(), length(uuid()); -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `uuid()` varchar(36) CHARACTER SET utf8 DEFAULT NULL, - `length(uuid())` int(10) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop table t1; -create table t1 select INET_ATON('255.255.0.1') as `a`; -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` bigint(21) unsigned DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -drop table t1; -drop table if exists table_26093; -drop function if exists func_26093_a; -drop function if exists func_26093_b; -create table table_26093(a int); -insert into table_26093 values -(1), (2), (3), (4), (5), -(6), (7), (8), (9), (10); -create function func_26093_a(x int) returns int -begin -set @invoked := @invoked + 1; -return x; -end// -create function func_26093_b(x int, y int) returns int -begin -set @invoked := @invoked + 1; -return x; -end// -select avg(a) from table_26093; -avg(a) -5.5000 -select benchmark(100, (select avg(a) from table_26093)); -benchmark(100, (select avg(a) from table_26093)) -0 -set @invoked := 0; -select benchmark(100, (select avg(func_26093_a(a)) from table_26093)); -benchmark(100, (select avg(func_26093_a(a)) from table_26093)) -0 -select @invoked; -@invoked -10 -set @invoked := 0; -select benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093)); -benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093)) -0 -select @invoked; -@invoked -1000 -select benchmark(100, (select (a) from table_26093)); -ERROR 21000: Subquery returns more than 1 row -select benchmark(100, (select 1, 1)); -ERROR 21000: Operand should contain 1 column(s) -drop table table_26093; -drop function func_26093_a; -drop function func_26093_b; -SELECT NAME_CONST('test', NOW()); -ERROR HY000: Incorrect arguments to NAME_CONST -SELECT NAME_CONST('test', UPPER('test')); -ERROR HY000: Incorrect arguments to NAME_CONST -SELECT NAME_CONST('test', NULL); -test -NULL -SELECT NAME_CONST('test', 1); -test -1 -SELECT NAME_CONST('test', -1); -test --1 -SELECT NAME_CONST('test', 1.0); -test -1.0 -SELECT NAME_CONST('test', -1.0); -test --1.0 -SELECT NAME_CONST('test', 'test'); -test -test -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1),(2),(3); -SELECT NAME_CONST('flag',1) * MAX(a) FROM t1; -NAME_CONST('flag',1) * MAX(a) -3 -SELECT NAME_CONST('flag',1.5) * MAX(a) FROM t1; -NAME_CONST('flag',1.5) * MAX(a) -4.5 -SELECT NAME_CONST('flag',-1) * MAX(a) FROM t1; -NAME_CONST('flag',-1) * MAX(a) --3 -SELECT NAME_CONST('flag',-1.5) * MAX(a) FROM t1; -NAME_CONST('flag',-1.5) * MAX(a) --4.5 -SELECT NAME_CONST('flag', SQRT(4)) * MAX(a) FROM t1; -ERROR HY000: Incorrect arguments to NAME_CONST -SELECT NAME_CONST('flag',-SQRT(4)) * MAX(a) FROM t1; -ERROR HY000: Incorrect arguments to NAME_CONST -DROP TABLE t1; -CREATE TABLE t1 (a int); -INSERT INTO t1 VALUES (5), (2); -SELECT NAME_CONST(x,2) FROM (SELECT a x FROM t1) t; -ERROR HY000: Incorrect arguments to NAME_CONST -DROP TABLE t1; -CREATE TABLE t1(a INT); -INSERT INTO t1 VALUES (), (), (); -SELECT NAME_CONST(a, '1') FROM t1; -ERROR HY000: Incorrect arguments to NAME_CONST -SET INSERT_ID= NAME_CONST(a, a); -ERROR HY000: Incorrect arguments to NAME_CONST -DROP TABLE t1; -create table t1 (a int not null); -insert into t1 values (-1), (-2); -select min(a) from t1 group by inet_ntoa(a); -min(a) --2 -drop table t1; -SELECT NAME_CONST('var', 'value') COLLATE latin1_general_cs; -NAME_CONST('var', 'value') COLLATE latin1_general_cs -value -select @@session.time_zone into @save_tz; -set @@session.time_zone='UTC'; -select uuid() into @my_uuid; -select mid(@my_uuid,15,1); -mid(@my_uuid,15,1) -1 -select 24 * 60 * 60 * 1000 * 1000 * 10 into @my_uuid_one_day; -select concat('0',mid(@my_uuid,16,3),mid(@my_uuid,10,4),left(@my_uuid,8)) into @my_uuidate; -select floor(conv(@my_uuidate,16,10)/@my_uuid_one_day) into @my_uuid_date; -select 141427 + datediff(curdate(),'1970-01-01') into @my_uuid_synthetic; -select @my_uuid_date - @my_uuid_synthetic; -@my_uuid_date - @my_uuid_synthetic -0 -set @@session.time_zone=@save_tz; -CREATE TABLE t1 (a DATE); -SELECT * FROM t1 WHERE a = NAME_CONST('reportDate', -_binary'2009-01-09' COLLATE 'binary'); -a -DROP TABLE t1; -select NAME_CONST('_id',1234) as id; -id -1234 -End of 5.0 tests -select connection_id() > 0; -connection_id() > 0 -1 -# -# Bug #54461: crash with longblob and union or update with subquery -# -CREATE TABLE t1 (a INT, b LONGBLOB); -INSERT INTO t1 VALUES (1, '2'), (2, '3'), (3, '2'); -SELECT DISTINCT LEAST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1; -LEAST(a, (SELECT b FROM t1 LIMIT 1)) -1 -2 -SELECT DISTINCT GREATEST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1; -GREATEST(a, (SELECT b FROM t1 LIMIT 1)) -2 -3 -1 -DROP TABLE t1; -SELECT INET_NTOA(0); -INET_NTOA(0) -0.0.0.0 -SELECT '1' IN ('1', INET_NTOA(0)); -'1' IN ('1', INET_NTOA(0)) -1 -SELECT NAME_CONST('a', -(1 OR 2)) OR 1; -ERROR HY000: Incorrect arguments to NAME_CONST -SELECT NAME_CONST('a', -(1 AND 2)) AND 1; -ERROR HY000: Incorrect arguments to NAME_CONST -SELECT NAME_CONST('a', -(1)) OR 1; -NAME_CONST('a', -(1)) OR 1 -1 -# -#MDEV-5446: Assertion `!table || (!table->read_set || -#bitmap_is_set(table->read_set, field_index))' fails on -#EXPLAIN EXTENDED with VALUES function -# -CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1,10); -CREATE VIEW v1 AS SELECT * FROM t1; -EXPLAIN EXTENDED SELECT VALUE(b) FROM v1; -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00 -Warnings: -Note 1003 select value(10) AS `VALUE(b)` from dual -drop view v1; -drop table t1; -End of 5.3 tests -# -# Bug #52165: Assertion failed: file .\dtoa.c, line 465 -# -CREATE TABLE t1 (a SET('a'), b INT); -INSERT INTO t1 VALUES ('', 0); -SELECT COALESCE(a) = COALESCE(b) FROM t1; -COALESCE(a) = COALESCE(b) -1 -Warnings: -Warning 1292 Truncated incorrect DOUBLE value: '' -DROP TABLE t1; -# -# Bug #54461: crash with longblob and union or update with subquery -# -CREATE TABLE t1 (a INT, b LONGBLOB); -INSERT INTO t1 VALUES (1, '2'), (2, '3'), (3, '2'); -SELECT DISTINCT LEAST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1; -LEAST(a, (SELECT b FROM t1 LIMIT 1)) -1 -2 -SELECT DISTINCT GREATEST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1; -GREATEST(a, (SELECT b FROM t1 LIMIT 1)) -2 -3 -1 -DROP TABLE t1; -SELECT INET_NTOA(0); -INET_NTOA(0) -0.0.0.0 -SELECT '1' IN ('1', INET_NTOA(0)); -'1' IN ('1', INET_NTOA(0)) -1 -# -# End of 5.1 tests -# -# -# Bug #58199: name_const in the having clause crashes -# -CREATE TABLE t1 (a INT); -SELECT 1 from t1 HAVING NAME_CONST('', a); -ERROR HY000: Incorrect arguments to NAME_CONST -DROP TABLE t1; -# -# Test or correct maybe_null of last_value -# -CREATE TABLE t1 (a char(2) not null ); -INSERT INTO t1 VALUES (4),(7),(1); -set @optimizer_switch_save= @@optimizer_switch; -set optimizer_switch='materialization=off'; -CREATE TABLE tv (e char(2) not null ); -INSERT INTO tv VALUES (1); -CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM tv; -CREATE ALGORITHM=MERGE VIEW vm AS SELECT * FROM tv; -explain extended -select a from t1 left join v_merge on (a=e) where last_value(NULL,e) not in (select last_value(NULL,e) from vm); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY tv ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY tv system NULL NULL NULL NULL 1 100.00 -Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (`test`.`tv`) on(`test`.`tv`.`e` = `test`.`t1`.`a`) where !<expr_cache><last_value(NULL,`test`.`tv`.`e`)>(<in_optimizer>(last_value(NULL,`test`.`tv`.`e`),<exists>(/* select#2 */ select last_value(NULL,'1') from dual where trigcond(<cache>(last_value(NULL,`test`.`tv`.`e`)) = last_value(NULL,'1'))))) -explain extended -select a from t1 left join v_merge on (a=e) where e not in (select last_value(NULL,e) from vm); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY tv ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (flat, BNL join) -2 DEPENDENT SUBQUERY tv system NULL NULL NULL NULL 1 100.00 -Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` left join (`test`.`tv`) on(`test`.`tv`.`e` = `test`.`t1`.`a`) where !<expr_cache><`test`.`tv`.`e`>(<in_optimizer>(`test`.`tv`.`e`,<exists>(/* select#2 */ select last_value(NULL,'1') from dual where trigcond(<cache>(`test`.`tv`.`e`) = last_value(NULL,'1'))))) -set optimizer_switch=@optimizer_switch_save; -drop view v_merge, vm; -drop table t1,tv; -# -# MDEV-4017 - GET_LOCK() with negative timeouts has strange behavior -# -SELECT GET_LOCK('ul1', NULL); -GET_LOCK('ul1', NULL) -NULL -Warnings: -Warning 1411 Incorrect timeout value: 'NULL' for function get_lock -SELECT GET_LOCK('ul1', -1); -GET_LOCK('ul1', -1) -NULL -Warnings: -Warning 1411 Incorrect timeout value: '-1' for function get_lock -# -# MDEV-8624 MariaDB hangs on query with many logical condition -# -CREATE TABLE `t1` ( -`id` int(11) NOT NULL AUTO_INCREMENT, -`submitdate` datetime DEFAULT NULL, -`lastpage` int(11) DEFAULT NULL, -`startlanguage` varchar(20) COLLATE utf8_unicode_ci NOT NULL, -`token` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL, -`datestamp` datetime NOT NULL, -`startdate` datetime NOT NULL, -`ipaddr` text COLLATE utf8_unicode_ci, -`refurl` text COLLATE utf8_unicode_ci, -`57813X540X1723` text COLLATE utf8_unicode_ci, -`57813X540X1724` text COLLATE utf8_unicode_ci, -`57813X540X1725` text COLLATE utf8_unicode_ci, -`57813X540X1726` double DEFAULT NULL, -`57813X540X1909` double DEFAULT NULL, -`57813X541X17271` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X541X17272` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X541X17273` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X541X17274` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X541X17275` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X541X17276` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X541X17281` text COLLATE utf8_unicode_ci, -`57813X541X17282` text COLLATE utf8_unicode_ci, -`57813X541X17283` text COLLATE utf8_unicode_ci, -`57813X541X17284` text COLLATE utf8_unicode_ci, -`57813X541X17285` text COLLATE utf8_unicode_ci, -`57813X541X17286` text COLLATE utf8_unicode_ci, -`57813X542X18131` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X542X18132` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X542X18133` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X542X18134` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X542X18135` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X542X18136` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X542X18137` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X542X18138` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X542X18139` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X542X18141` text COLLATE utf8_unicode_ci, -`57813X542X18142` text COLLATE utf8_unicode_ci, -`57813X542X18143` text COLLATE utf8_unicode_ci, -`57813X542X18144` text COLLATE utf8_unicode_ci, -`57813X542X18145` text COLLATE utf8_unicode_ci, -`57813X542X18146` text COLLATE utf8_unicode_ci, -`57813X542X18147` text COLLATE utf8_unicode_ci, -`57813X542X18148` text COLLATE utf8_unicode_ci, -`57813X542X18149` text COLLATE utf8_unicode_ci, -`57813X543X18451` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X543X18452` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X543X18453` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X543X18454` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X543X18455` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X543X18456` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X543X18461` text COLLATE utf8_unicode_ci, -`57813X543X18462` text COLLATE utf8_unicode_ci, -`57813X543X18463` text COLLATE utf8_unicode_ci, -`57813X543X18464` text COLLATE utf8_unicode_ci, -`57813X543X18465` text COLLATE utf8_unicode_ci, -`57813X543X18466` text COLLATE utf8_unicode_ci, -`57813X544X18711` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X544X18712` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X544X18713` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X544X18714` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X544X18715` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X544X18716` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X544X18717` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X544X18718` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X544X18721` text COLLATE utf8_unicode_ci, -`57813X544X18722` text COLLATE utf8_unicode_ci, -`57813X544X18723` text COLLATE utf8_unicode_ci, -`57813X544X18724` text COLLATE utf8_unicode_ci, -`57813X544X18725` text COLLATE utf8_unicode_ci, -`57813X544X18726` text COLLATE utf8_unicode_ci, -`57813X544X18727` text COLLATE utf8_unicode_ci, -`57813X544X18728` text COLLATE utf8_unicode_ci, -`57813X546X1902` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X546X1903` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X546X1904` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, -`57813X545X1901` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, -PRIMARY KEY (`id`), -KEY `lime_survey_57813_idx` (`token`), -KEY `57813X540X1723` (`57813X540X1723`(100)), -KEY `57813X540X1724` (`57813X540X1724`(100)), -KEY `57813X540X1726` (`57813X540X1726`), -KEY `57813X540X1725` (`57813X540X1725`(100)), -KEY `57813X546X1902` (`57813X546X1902`), -KEY `57813X546X1903` (`57813X546X1903`), -KEY `57813X546X1904` (`57813X546X1904`) -); -SELECT -COUNT(*) as `N`, -ROUND( -( -SUM( -( -( -IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 57813X541X17271, 0 ) + -IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 57813X541X17272, 0 ) + -IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 57813X541X17273, 0 ) + -IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 57813X541X17274, 0 ) + -IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 57813X541X17275, 0 ) + -IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 57813X541X17276, 0 ) + -IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 57813X542X18131, 0 ) + -IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 57813X542X18132, 0 ) + -IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 57813X542X18133, 0 ) + -IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 57813X542X18134, 0 ) + -IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 57813X542X18135, 0 ) + -IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 57813X542X18136, 0 ) + -IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 57813X542X18137, 0 ) + -IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 57813X542X18138, 0 ) + -IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 57813X542X18139, 0 ) + -IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 57813X543X18451, 0 ) + -IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 57813X543X18452, 0 ) + -IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 57813X543X18453, 0 ) + -IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 57813X543X18454, 0 ) + -IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 57813X543X18455, 0 ) + -IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 57813X543X18456, 0 ) + -IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 57813X544X18711, 0 ) + -IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 57813X544X18712, 0 ) + -IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 57813X544X18713, 0 ) + -IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 57813X544X18714, 0 ) + -IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 57813X544X18715, 0 ) + -IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 57813X544X18716, 0 ) + -IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 57813X544X18717, 0 ) + -IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 57813X544X18718, 0 ) -) -/ -( -IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 1, 0 ) + -IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 1, 0 ) + -IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 1, 0 ) + -IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 1, 0 ) + -IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 1, 0 ) + -IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 1, 0 ) + -IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 1, 0 ) + -IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 1, 0 ) + -IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 1, 0 ) + -IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 1, 0 ) + -IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 1, 0 ) + -IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 1, 0 ) + -IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 1, 0 ) + -IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 1, 0 ) + -IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 1, 0 ) + -IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 1, 0 ) + -IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 1, 0 ) + -IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 1, 0 ) + -IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 1, 0 ) + -IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 1, 0 ) + -IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 1, 0 ) + -IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 1, 0 ) + -IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 1, 0 ) + -IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 1, 0 ) + -IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 1, 0 ) + -IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 1, 0 ) + -IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 1, 0 ) + -IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 1, 0 ) + -IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 1, 0 ) -) -) -) -/ COUNT(*) ), 4) as `AVG` -FROM `t1` -WHERE `submitdate` IS NOT NULL -AND ( -( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99' ) OR -( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99' ) OR -( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99' ) OR -( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99' ) OR -( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99' ) OR -( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99' ) OR -( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99' ) OR -( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99' ) OR -( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99' ) OR -( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99' ) OR -( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99' ) OR -( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99' ) OR -( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99' ) OR -( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99' ) OR -( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99' ) OR -( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99' ) OR -( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99' ) OR -( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99' ) OR -( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99' ) OR -( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99' ) OR -( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99' ) OR -( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99' ) OR -( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99' ) OR -( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99' ) OR -( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99' ) OR -( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99' ) OR -( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99' ) OR -( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99' ) OR -( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99' ) ) -AND 57813X540X1723 = 'Test'; -N AVG -0 NULL -drop table t1; -SELECT NAME_CONST('a', -(1 OR 2)) OR 1; -ERROR HY000: Incorrect arguments to NAME_CONST -SELECT NAME_CONST('a', -(1 AND 2)) OR 1; -ERROR HY000: Incorrect arguments to NAME_CONST -SELECT NAME_CONST('a', -(1)) OR 1; -NAME_CONST('a', -(1)) OR 1 -1 -# -# End of 5.5 tests -# -# -# GET_LOCK, RELEASE_LOCK, IS_USED_LOCK functions test -# -# IS_USED_LOCK, IS_FREE_LOCK: the lock is not acquired -# Note: IS_USED_LOCK returns NULL if the lock is unused -select is_used_lock('test'); -is_used_lock('test') -NULL -select is_free_lock('test'); -is_free_lock('test') -1 -# GET_LOCK returns 1 if it manages to acquire a lock -select get_lock('test', 0); -get_lock('test', 0) -1 -# IS_USED_LOCK, IS_FREE_LOCK: the lock is acquired -select is_free_lock('test'); -is_free_lock('test') -0 -select is_used_lock('test') = connection_id(); -is_used_lock('test') = connection_id() -1 -connect con1,localhost,root,,; -connection con1; -# IS_USED_LOCK, IS_FREE_LOCK: the lock is acquired in another -# connection -select is_used_lock('test') = connection_id(); -is_used_lock('test') = connection_id() -0 -select is_free_lock('test'); -is_free_lock('test') -0 -# GET_LOCK returns 0 if it can't acquire a lock (wait timeout) -select get_lock('test', 0); -get_lock('test', 0) -0 -# RELEASE_LOCK returns 0 if the lock belongs to another connection -select release_lock('test'); -release_lock('test') -0 -connection default; -# RELEASE_LOCK returns 1 if it successfully releases a lock -select release_lock('test'); -release_lock('test') -1 -# RELEASE_LOCK returns NULL if it doesn't release a lock and there is no such lock -select release_lock('test'); -release_lock('test') -NULL -# Test that get_lock() returns NULL if error. -select get_lock('test', 0); -get_lock('test', 0) -1 -connection con1; -create table t1 select connection_id() as id; -select get_lock('test', 7200); -connection default; -select (@id := id) - id from t1; -(@id := id) - id -0 -kill query @id; -connection con1; -get_lock('test', 7200) -NULL -connection default; -# GET_LOCK() works recursively -select get_lock('test', 0); -get_lock('test', 0) -1 -select get_lock('test', 0); -get_lock('test', 0) -1 -select get_lock('test', 0); -get_lock('test', 0) -1 -# RELEASE_LOCK() needs to be called recursively then, too -select release_lock('test'); -release_lock('test') -1 -select release_lock('test'); -release_lock('test') -1 -select release_lock('test'); -release_lock('test') -1 -# Once the last instance of the lock is released, -# the next call returns NULL -select release_lock('test'); -release_lock('test') -1 -# Multiple locks in the same session are OK -select get_lock('test1', 0); -get_lock('test1', 0) -1 -select get_lock('test2', 0); -get_lock('test2', 0) -1 -select get_lock('test3', 0); -get_lock('test3', 0) -1 -select release_lock('test1'); -release_lock('test1') -1 -select release_lock('test2'); -release_lock('test2') -1 -select release_lock('test3'); -release_lock('test3') -1 -# Deadlocks are detected e.g. in case of a mutual wait -select get_lock('test1', 0); -get_lock('test1', 0) -1 -connection con1; -select get_lock('test2', 0); -get_lock('test2', 0) -1 -select get_lock('test1', 7200); -connection default; -select get_lock('test2', 7200); -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -select release_lock('test1'); -release_lock('test1') -1 -connection con1; -get_lock('test1', 7200) -1 -select release_lock('test2'); -release_lock('test2') -1 -select release_lock('test1'); -release_lock('test1') -1 -connection default; -# LOCK/UNLOCK TABLES works fine with a user lock. -lock table t1 write; -select get_lock('test', 0); -get_lock('test', 0) -1 -unlock tables; -commit; -select release_lock('test'); -release_lock('test') -1 -# GLOBAL READ LOCK works with fine with user locks -select get_lock('test1', 0); -get_lock('test1', 0) -1 -flush tables with read lock; -select get_lock('test2', 0); -get_lock('test2', 0) -1 -unlock tables; -commit; -select release_lock('test1'); -release_lock('test1') -1 -select release_lock('test2'); -release_lock('test2') -1 -# BEGIN/COMMIT/ROLLBACK don't unlock user locks. -begin; -select get_lock('test1', 0); -get_lock('test1', 0) -1 -select get_lock('test2', 0); -get_lock('test2', 0) -1 -select count(*) from t1; -count(*) -1 -rollback; -select release_lock('test1'); -release_lock('test1') -1 -select release_lock('test2'); -release_lock('test2') -1 -# Deadlocks between user locks and LOCK TABLES locks -# are detected OK. -select get_lock('test', 0); -get_lock('test', 0) -1 -connection con1; -lock table t1 write; -select get_lock('test', 7200); -connection default; -lock table t1 read; -ERROR 40001: Deadlock found when trying to get lock; try restarting transaction -select release_lock('test'); -release_lock('test') -1 -connection con1; -get_lock('test', 7200) -1 -select release_lock('test'); -release_lock('test') -1 -unlock tables; -# cleanup -disconnect con1; -connection default; -drop table t1; -# check too long identifier names -select get_lock(repeat('a', 192), 0); -get_lock(repeat('a', 192), 0) -1 -select is_used_lock(repeat('a', 192)) = connection_id(); -is_used_lock(repeat('a', 192)) = connection_id() -1 -select is_free_lock(repeat('a', 192)); -is_free_lock(repeat('a', 192)) -0 -select release_lock(repeat('a', 192)); -release_lock(repeat('a', 192)) -1 -select get_lock(repeat('a', 193), 0); -ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long -select is_used_lock(repeat('a', 193)); -ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long -select is_free_lock(repeat('a', 193)); -ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long -select release_lock(repeat('a', 193)); -ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long - -# -- -# -- WL#5787: IPv6-capable INET_ATON and INET_NTOA functions. -# -- - -# -- INET6_ATON: checking NULL, invalid types, out-of range values... - -SELECT INET6_ATON(NULL) IS NULL; -INET6_ATON(NULL) IS NULL -1 -SELECT INET6_ATON(123) IS NULL; -INET6_ATON(123) IS NULL -1 -SELECT INET6_ATON(123.45) IS NULL; -INET6_ATON(123.45) IS NULL -1 -SELECT INET6_ATON(NOW()) IS NULL; -INET6_ATON(NOW()) IS NULL -1 -SELECT INET6_ATON('1.2.3') IS NULL; -INET6_ATON('1.2.3') IS NULL -1 -SELECT INET6_ATON('1.2.3.') IS NULL; -INET6_ATON('1.2.3.') IS NULL -1 -SELECT INET6_ATON('1..3.4') IS NULL; -INET6_ATON('1..3.4') IS NULL -1 -SELECT INET6_ATON('-1.2.3.4') IS NULL; -INET6_ATON('-1.2.3.4') IS NULL -1 -SELECT INET6_ATON('1.2.3.256') IS NULL; -INET6_ATON('1.2.3.256') IS NULL -1 -SELECT INET6_ATON('1.2.3.4.5') IS NULL; -INET6_ATON('1.2.3.4.5') IS NULL -1 -SELECT INET6_ATON('0001.2.3.4') IS NULL; -INET6_ATON('0001.2.3.4') IS NULL -1 -SELECT INET6_ATON('0x1.2.3.4') IS NULL; -INET6_ATON('0x1.2.3.4') IS NULL -1 -SELECT INET6_ATON('a.2.3.4') IS NULL; -INET6_ATON('a.2.3.4') IS NULL -1 -SELECT INET6_ATON('1.2.3.4:80') IS NULL; -INET6_ATON('1.2.3.4:80') IS NULL -1 -SELECT INET6_ATON('1.2.3.4/32') IS NULL; -INET6_ATON('1.2.3.4/32') IS NULL -1 -SELECT INET6_ATON('mysql.com') IS NULL; -INET6_ATON('mysql.com') IS NULL -1 -SELECT INET6_ATON(':::') IS NULL; -INET6_ATON(':::') IS NULL -1 -SELECT INET6_ATON(':1:2:3') IS NULL; -INET6_ATON(':1:2:3') IS NULL -1 -SELECT INET6_ATON('1:2:3:') IS NULL; -INET6_ATON('1:2:3:') IS NULL -1 -SELECT INET6_ATON(':1::2:3') IS NULL; -INET6_ATON(':1::2:3') IS NULL -1 -SELECT INET6_ATON('1::2:3:') IS NULL; -INET6_ATON('1::2:3:') IS NULL -1 -SELECT INET6_ATON('::00001') IS NULL; -INET6_ATON('::00001') IS NULL -1 -SELECT INET6_ATON('::00001:2') IS NULL; -INET6_ATON('::00001:2') IS NULL -1 -SELECT INET6_ATON('::12345') IS NULL; -INET6_ATON('::12345') IS NULL -1 -SELECT INET6_ATON('1020::3040::5060') IS NULL; -INET6_ATON('1020::3040::5060') IS NULL -1 -SELECT INET6_ATON('::ABCZ') IS NULL; -INET6_ATON('::ABCZ') IS NULL -1 -SELECT INET6_ATON('::0x1.2.3.4') IS NULL; -INET6_ATON('::0x1.2.3.4') IS NULL -1 -SELECT INET6_ATON('::1.0x2.3.4') IS NULL; -INET6_ATON('::1.0x2.3.4') IS NULL -1 -SELECT INET6_ATON('::a.b.c.d') IS NULL; -INET6_ATON('::a.b.c.d') IS NULL -1 -SELECT INET6_ATON('::FFFF:0x1.2.3.4') IS NULL; -INET6_ATON('::FFFF:0x1.2.3.4') IS NULL -1 -SELECT INET6_ATON('::FFFF:1.0x2.3.4') IS NULL; -INET6_ATON('::FFFF:1.0x2.3.4') IS NULL -1 -SELECT INET6_ATON('::FFFF:a.b.c.d') IS NULL; -INET6_ATON('::FFFF:a.b.c.d') IS NULL -1 -SELECT INET6_ATON('::1.2.3.4:ABCD') IS NULL; -INET6_ATON('::1.2.3.4:ABCD') IS NULL -1 -# NOTE: such addresses are supported because getaddrinfo() supports them. -# This is just to record the current behaviour. -SELECT HEX(INET6_ATON('::ABCD:1.2.3.4')); -HEX(INET6_ATON('::ABCD:1.2.3.4')) -00000000000000000000ABCD01020304 - -# -- INET6_ATON: checking binary representation... - -SELECT HEX(INET6_ATON('0.0.0.0')); -HEX(INET6_ATON('0.0.0.0')) -00000000 -SELECT HEX(INET6_ATON('00.00.00.00')); -HEX(INET6_ATON('00.00.00.00')) -00000000 -SELECT HEX(INET6_ATON('000.000.000.000')); -HEX(INET6_ATON('000.000.000.000')) -00000000 -SELECT HEX(INET6_ATON('1.2.3.4')); -HEX(INET6_ATON('1.2.3.4')) -01020304 -SELECT HEX(INET6_ATON('01.02.03.04')); -HEX(INET6_ATON('01.02.03.04')) -01020304 -SELECT HEX(INET6_ATON('001.002.003.004')); -HEX(INET6_ATON('001.002.003.004')) -01020304 -SELECT HEX(INET6_ATON('255.255.255.255')); -HEX(INET6_ATON('255.255.255.255')) -FFFFFFFF -SELECT HEX(INET6_ATON('::')); -HEX(INET6_ATON('::')) -00000000000000000000000000000000 -SELECT HEX(INET6_ATON('0::0')); -HEX(INET6_ATON('0::0')) -00000000000000000000000000000000 -SELECT HEX(INET6_ATON('1::2')); -HEX(INET6_ATON('1::2')) -00010000000000000000000000000002 -SELECT HEX(INET6_ATON('0::')); -HEX(INET6_ATON('0::')) -00000000000000000000000000000000 -SELECT HEX(INET6_ATON('1::')); -HEX(INET6_ATON('1::')) -00010000000000000000000000000000 -SELECT HEX(INET6_ATON('::0')); -HEX(INET6_ATON('::0')) -00000000000000000000000000000000 -SELECT HEX(INET6_ATON('::1')); -HEX(INET6_ATON('::1')) -00000000000000000000000000000001 -SELECT HEX(INET6_ATON('1:2:3:4:5:6:7:8')); -HEX(INET6_ATON('1:2:3:4:5:6:7:8')) -00010002000300040005000600070008 -SELECT HEX(INET6_ATON('::2:3:4:5:6:7:8')); -HEX(INET6_ATON('::2:3:4:5:6:7:8')) -00000002000300040005000600070008 -SELECT HEX(INET6_ATON('1::3:4:5:6:7:8')); -HEX(INET6_ATON('1::3:4:5:6:7:8')) -00010000000300040005000600070008 -SELECT HEX(INET6_ATON('1:2::4:5:6:7:8')); -HEX(INET6_ATON('1:2::4:5:6:7:8')) -00010002000000040005000600070008 -SELECT HEX(INET6_ATON('1:2:3::5:6:7:8')); -HEX(INET6_ATON('1:2:3::5:6:7:8')) -00010002000300000005000600070008 -SELECT HEX(INET6_ATON('1:2:3:4::6:7:8')); -HEX(INET6_ATON('1:2:3:4::6:7:8')) -00010002000300040000000600070008 -SELECT HEX(INET6_ATON('1:2:3:4:5::7:8')); -HEX(INET6_ATON('1:2:3:4:5::7:8')) -00010002000300040005000000070008 -SELECT HEX(INET6_ATON('1:2:3:4:5:6::8')); -HEX(INET6_ATON('1:2:3:4:5:6::8')) -00010002000300040005000600000008 -SELECT HEX(INET6_ATON('1:2:3:4:5:6:7::')); -HEX(INET6_ATON('1:2:3:4:5:6:7::')) -00010002000300040005000600070000 -SELECT HEX(INET6_ATON('0000:0000::0000:0001')); -HEX(INET6_ATON('0000:0000::0000:0001')) -00000000000000000000000000000001 -SELECT HEX(INET6_ATON('1234:5678:9abc:def0:4321:8765:cba9:0fed')); -HEX(INET6_ATON('1234:5678:9abc:def0:4321:8765:cba9:0fed')) -123456789ABCDEF043218765CBA90FED -SELECT HEX(INET6_ATON('0000:0000:0000:0000:0000:0000:0000:0001')); -HEX(INET6_ATON('0000:0000:0000:0000:0000:0000:0000:0001')) -00000000000000000000000000000001 -SELECT HEX(INET6_ATON('::C0A8:0102')); -HEX(INET6_ATON('::C0A8:0102')) -000000000000000000000000C0A80102 -SELECT HEX(INET6_ATON('::c0a8:0102')); -HEX(INET6_ATON('::c0a8:0102')) -000000000000000000000000C0A80102 -SELECT HEX(INET6_ATON('::192.168.1.2')); -HEX(INET6_ATON('::192.168.1.2')) -000000000000000000000000C0A80102 -SELECT HEX(INET6_ATON('::FfFf:C0a8:0102')); -HEX(INET6_ATON('::FfFf:C0a8:0102')) -00000000000000000000FFFFC0A80102 -SELECT HEX(INET6_ATON('::ffff:c0a8:0102')); -HEX(INET6_ATON('::ffff:c0a8:0102')) -00000000000000000000FFFFC0A80102 -SELECT HEX(INET6_ATON('::ffff:192.168.1.2')); -HEX(INET6_ATON('::ffff:192.168.1.2')) -00000000000000000000FFFFC0A80102 -SELECT HEX(INET6_ATON('::01.2.3.4')); -HEX(INET6_ATON('::01.2.3.4')) -00000000000000000000000001020304 -SELECT HEX(INET6_ATON('::1.02.3.4')); -HEX(INET6_ATON('::1.02.3.4')) -00000000000000000000000001020304 -SELECT HEX(INET6_ATON('::1.2.03.4')); -HEX(INET6_ATON('::1.2.03.4')) -00000000000000000000000001020304 -SELECT HEX(INET6_ATON('::1.2.3.04')); -HEX(INET6_ATON('::1.2.3.04')) -00000000000000000000000001020304 -SELECT HEX(INET6_ATON('::1.2.3.00')); -HEX(INET6_ATON('::1.2.3.00')) -00000000000000000000000001020300 -SELECT HEX(INET6_ATON('::FFFF:01.2.3.4')); -HEX(INET6_ATON('::FFFF:01.2.3.4')) -00000000000000000000FFFF01020304 -SELECT HEX(INET6_ATON('::FFFF:1.02.3.4')); -HEX(INET6_ATON('::FFFF:1.02.3.4')) -00000000000000000000FFFF01020304 -SELECT HEX(INET6_ATON('::FFFF:1.2.03.4')); -HEX(INET6_ATON('::FFFF:1.2.03.4')) -00000000000000000000FFFF01020304 -SELECT HEX(INET6_ATON('::FFFF:1.2.3.04')); -HEX(INET6_ATON('::FFFF:1.2.3.04')) -00000000000000000000FFFF01020304 -SELECT HEX(INET6_ATON('::FFFF:1.2.3.00')); -HEX(INET6_ATON('::FFFF:1.2.3.00')) -00000000000000000000FFFF01020300 - -# -- INET6_ATON: checking the length is either 4 or 16... - -SELECT LENGTH(INET6_ATON('0.0.0.0')); -LENGTH(INET6_ATON('0.0.0.0')) -4 -SELECT LENGTH(INET6_ATON('255.255.255.255')); -LENGTH(INET6_ATON('255.255.255.255')) -4 -SELECT LENGTH(INET6_ATON('::')); -LENGTH(INET6_ATON('::')) -16 -SELECT LENGTH(INET6_ATON('1020:3040:5060:7080:90A0:B0C0:D0E0:F010')); -LENGTH(INET6_ATON('1020:3040:5060:7080:90A0:B0C0:D0E0:F010')) -16 - -# -- INET6_NTOA: checking NULL, invalid types, out-of range values... - -SELECT INET6_NTOA(NULL); -INET6_NTOA(NULL) -NULL -SELECT INET6_NTOA(123); -INET6_NTOA(123) -NULL -SELECT INET6_NTOA(123.456); -INET6_NTOA(123.456) -NULL -SELECT INET6_NTOA(NOW()); -INET6_NTOA(NOW()) -NULL -SELECT INET6_NTOA(UNHEX('C0A801')); -INET6_NTOA(UNHEX('C0A801')) -NULL -SELECT INET6_NTOA(UNHEX('C0A80102')); -INET6_NTOA(UNHEX('C0A80102')) -192.168.1.2 -SELECT INET6_NTOA(UNHEX('C0A8010203')); -INET6_NTOA(UNHEX('C0A8010203')) -NULL -SELECT INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F')); -INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F')) -NULL -SELECT INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F10')); -INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F10')) -102:304:506:708:90a:b0c:d0e:f10 -SELECT INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F1011')); -INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F1011')) -NULL -SELECT INET6_NTOA('1234'), INET6_NTOA(BINARY('1234')); -INET6_NTOA('1234') INET6_NTOA(BINARY('1234')) -NULL 49.50.51.52 -SELECT INET6_NTOA('0123456789abcdef'), INET6_NTOA(BINARY('0123456789abcdef')); -INET6_NTOA('0123456789abcdef') INET6_NTOA(BINARY('0123456789abcdef')) -NULL 3031:3233:3435:3637:3839:6162:6364:6566 - -# -- Checking double-conversion... - -SELECT INET6_NTOA(INET6_ATON('::')); -INET6_NTOA(INET6_ATON('::')) -:: -SELECT INET6_NTOA(INET6_ATON('0::0')); -INET6_NTOA(INET6_ATON('0::0')) -:: -SELECT INET6_NTOA(INET6_ATON('1::2')); -INET6_NTOA(INET6_ATON('1::2')) -1::2 -SELECT INET6_NTOA(INET6_ATON('0::')); -INET6_NTOA(INET6_ATON('0::')) -:: -SELECT INET6_NTOA(INET6_ATON('1::')); -INET6_NTOA(INET6_ATON('1::')) -1:: -SELECT INET6_NTOA(INET6_ATON('::0')); -INET6_NTOA(INET6_ATON('::0')) -:: -SELECT INET6_NTOA(INET6_ATON('::1')); -INET6_NTOA(INET6_ATON('::1')) -::1 -SELECT INET6_NTOA(INET6_ATON('1:2:3:4:5:6:7:8')); -INET6_NTOA(INET6_ATON('1:2:3:4:5:6:7:8')) -1:2:3:4:5:6:7:8 -SELECT INET6_NTOA(INET6_ATON('::2:3:4:5:6:7:8')); -INET6_NTOA(INET6_ATON('::2:3:4:5:6:7:8')) -::2:3:4:5:6:7:8 -SELECT INET6_NTOA(INET6_ATON('1::3:4:5:6:7:8')); -INET6_NTOA(INET6_ATON('1::3:4:5:6:7:8')) -1::3:4:5:6:7:8 -SELECT INET6_NTOA(INET6_ATON('1:2::4:5:6:7:8')); -INET6_NTOA(INET6_ATON('1:2::4:5:6:7:8')) -1:2::4:5:6:7:8 -SELECT INET6_NTOA(INET6_ATON('1:2:3::5:6:7:8')); -INET6_NTOA(INET6_ATON('1:2:3::5:6:7:8')) -1:2:3::5:6:7:8 -SELECT INET6_NTOA(INET6_ATON('1:2:3:4::6:7:8')); -INET6_NTOA(INET6_ATON('1:2:3:4::6:7:8')) -1:2:3:4::6:7:8 -SELECT INET6_NTOA(INET6_ATON('1:2:3:4:5::7:8')); -INET6_NTOA(INET6_ATON('1:2:3:4:5::7:8')) -1:2:3:4:5::7:8 -SELECT INET6_NTOA(INET6_ATON('1:2:3:4:5:6::8')); -INET6_NTOA(INET6_ATON('1:2:3:4:5:6::8')) -1:2:3:4:5:6::8 -SELECT INET6_NTOA(INET6_ATON('1:2:3:4:5:6:7::')); -INET6_NTOA(INET6_ATON('1:2:3:4:5:6:7::')) -1:2:3:4:5:6:7:: -SELECT INET6_NTOA(INET6_ATON('0000:0000::0000:0001')); -INET6_NTOA(INET6_ATON('0000:0000::0000:0001')) -::1 -SELECT INET6_NTOA(INET6_ATON('1234:5678:9abc:def0:4321:8765:cba9:0fed')); -INET6_NTOA(INET6_ATON('1234:5678:9abc:def0:4321:8765:cba9:0fed')) -1234:5678:9abc:def0:4321:8765:cba9:fed -SELECT INET6_NTOA(INET6_ATON('0000:0000:0000:0000:0000:0000:0000:0001')); -INET6_NTOA(INET6_ATON('0000:0000:0000:0000:0000:0000:0000:0001')) -::1 -SELECT INET6_NTOA(INET6_ATON('::C0A8:0102')); -INET6_NTOA(INET6_ATON('::C0A8:0102')) -::192.168.1.2 -SELECT INET6_NTOA(INET6_ATON('::c0a8:0102')); -INET6_NTOA(INET6_ATON('::c0a8:0102')) -::192.168.1.2 -SELECT INET6_NTOA(INET6_ATON('::192.168.1.2')); -INET6_NTOA(INET6_ATON('::192.168.1.2')) -::192.168.1.2 -SELECT INET6_NTOA(INET6_ATON('::FfFf:C0a8:0102')); -INET6_NTOA(INET6_ATON('::FfFf:C0a8:0102')) -::ffff:192.168.1.2 -SELECT INET6_NTOA(INET6_ATON('::ffff:c0a8:0102')); -INET6_NTOA(INET6_ATON('::ffff:c0a8:0102')) -::ffff:192.168.1.2 -SELECT INET6_NTOA(INET6_ATON('::ffff:192.168.1.2')); -INET6_NTOA(INET6_ATON('::ffff:192.168.1.2')) -::ffff:192.168.1.2 -SELECT INET6_NTOA(INET6_ATON('::01.2.3.4')); -INET6_NTOA(INET6_ATON('::01.2.3.4')) -::1.2.3.4 -SELECT INET6_NTOA(INET6_ATON('::1.02.3.4')); -INET6_NTOA(INET6_ATON('::1.02.3.4')) -::1.2.3.4 -SELECT INET6_NTOA(INET6_ATON('::1.2.03.4')); -INET6_NTOA(INET6_ATON('::1.2.03.4')) -::1.2.3.4 -SELECT INET6_NTOA(INET6_ATON('::1.2.3.04')); -INET6_NTOA(INET6_ATON('::1.2.3.04')) -::1.2.3.4 -SELECT INET6_NTOA(INET6_ATON('::1.2.3.00')); -INET6_NTOA(INET6_ATON('::1.2.3.00')) -::1.2.3.0 -SELECT INET6_NTOA(INET6_ATON('::FFFF:01.2.3.4')); -INET6_NTOA(INET6_ATON('::FFFF:01.2.3.4')) -::ffff:1.2.3.4 -SELECT INET6_NTOA(INET6_ATON('::FFFF:1.02.3.4')); -INET6_NTOA(INET6_ATON('::FFFF:1.02.3.4')) -::ffff:1.2.3.4 -SELECT INET6_NTOA(INET6_ATON('::FFFF:1.2.03.4')); -INET6_NTOA(INET6_ATON('::FFFF:1.2.03.4')) -::ffff:1.2.3.4 -SELECT INET6_NTOA(INET6_ATON('::FFFF:1.2.3.04')); -INET6_NTOA(INET6_ATON('::FFFF:1.2.3.04')) -::ffff:1.2.3.4 -SELECT INET6_NTOA(INET6_ATON('::FFFF:1.2.3.00')); -INET6_NTOA(INET6_ATON('::FFFF:1.2.3.00')) -::ffff:1.2.3.0 - -# -- Comparing INET_ATON() and INET6_ATON()... - -SELECT HEX(INET_ATON('192.168.1.2')); -HEX(INET_ATON('192.168.1.2')) -C0A80102 -SELECT HEX(INET6_ATON('192.168.1.2')); -HEX(INET6_ATON('192.168.1.2')) -C0A80102 -SELECT HEX(INET_ATON('255.255.255.255')); -HEX(INET_ATON('255.255.255.255')) -FFFFFFFF -SELECT HEX(INET6_ATON('255.255.255.255')); -HEX(INET6_ATON('255.255.255.255')) -FFFFFFFF -SELECT HEX(INET_ATON('192.168.08.2')); -HEX(INET_ATON('192.168.08.2')) -C0A80802 -SELECT HEX(INET6_ATON('192.168.08.2')); -HEX(INET6_ATON('192.168.08.2')) -C0A80802 -SELECT HEX(INET_ATON('192.168.0x8.2')); -HEX(INET_ATON('192.168.0x8.2')) -NULL -SELECT HEX(INET6_ATON('192.168.0x8.2')); -HEX(INET6_ATON('192.168.0x8.2')) -NULL -SELECT HEX(INET_ATON('1.2.255')); -HEX(INET_ATON('1.2.255')) -10200FF -SELECT HEX(INET6_ATON('1.2.255')); -HEX(INET6_ATON('1.2.255')) -NULL -SELECT HEX(INET_ATON('1.2.256')); -HEX(INET_ATON('1.2.256')) -NULL -SELECT HEX(INET6_ATON('1.2.256')); -HEX(INET6_ATON('1.2.256')) -NULL -SELECT HEX(INET_ATON('1.0002.3.4')); -HEX(INET_ATON('1.0002.3.4')) -1020304 -SELECT HEX(INET6_ATON('1.0002.3.4')); -HEX(INET6_ATON('1.0002.3.4')) -NULL -SELECT HEX(INET_ATON('1.2.3.4.5')); -HEX(INET_ATON('1.2.3.4.5')) -102030405 -SELECT HEX(INET6_ATON('1.2.3.4.5')); -HEX(INET6_ATON('1.2.3.4.5')) -NULL - -# -- Checking mix of INET- and INET6- functions... - -SELECT HEX(INET6_ATON(INET_NTOA(INET_ATON('1.2.3.4')))) AS x; -x -01020304 - -# -- Checking IS_IPV4() / IS_IPV6()... - -SELECT IS_IPV4(NULL); -IS_IPV4(NULL) -0 -SELECT IS_IPV4(1); -IS_IPV4(1) -0 -SELECT IS_IPV4(1.0); -IS_IPV4(1.0) -0 -SELECT IS_IPV4('1.2.3.4'); -IS_IPV4('1.2.3.4') -1 -SELECT IS_IPV4('001.02.000.255'); -IS_IPV4('001.02.000.255') -1 -SELECT IS_IPV4('::1.2.0.255'); -IS_IPV4('::1.2.0.255') -0 -SELECT IS_IPV4('::1'); -IS_IPV4('::1') -0 -SELECT IS_IPV4(BINARY('1.2.3.4')); -IS_IPV4(BINARY('1.2.3.4')) -1 -SELECT IS_IPV6(NULL); -IS_IPV6(NULL) -0 -SELECT IS_IPV6(1); -IS_IPV6(1) -0 -SELECT IS_IPV6(1.0); -IS_IPV6(1.0) -0 -SELECT IS_IPV6('1.2.3.4'); -IS_IPV6('1.2.3.4') -0 -SELECT IS_IPV6('001.02.000.255'); -IS_IPV6('001.02.000.255') -0 -SELECT IS_IPV6('::001.02.000.255'); -IS_IPV6('::001.02.000.255') -1 -SELECT IS_IPV6('::1.2.0.255'); -IS_IPV6('::1.2.0.255') -1 -SELECT IS_IPV6('::1'); -IS_IPV6('::1') -1 -SELECT IS_IPV6('0000:0000:0000:0000:0000:0000:0000:0001'); -IS_IPV6('0000:0000:0000:0000:0000:0000:0000:0001') -1 -SELECT IS_IPV6(BINARY('0000:0000:0000:0000:0000:0000:0000:0001')); -IS_IPV6(BINARY('0000:0000:0000:0000:0000:0000:0000:0001')) -1 - -# -- Checking IS_IPV4_MAPPED() and IS_IPV4_COMPAT()... - -SELECT IS_IPV4_MAPPED(INET6_ATON('1.2.3.4')), -IS_IPV4_COMPAT(INET6_ATON('1.2.3.4')); -IS_IPV4_MAPPED(INET6_ATON('1.2.3.4')) IS_IPV4_COMPAT(INET6_ATON('1.2.3.4')) -0 0 -SELECT IS_IPV4_MAPPED(INET6_ATON('::1.2.3.4')), -IS_IPV4_COMPAT(INET6_ATON('::1.2.3.4')); -IS_IPV4_MAPPED(INET6_ATON('::1.2.3.4')) IS_IPV4_COMPAT(INET6_ATON('::1.2.3.4')) -0 1 -SELECT IS_IPV4_MAPPED(INET6_ATON('::FFFF:1.2.3.4')), -IS_IPV4_COMPAT(INET6_ATON('::FFFF:1.2.3.4')); -IS_IPV4_MAPPED(INET6_ATON('::FFFF:1.2.3.4')) IS_IPV4_COMPAT(INET6_ATON('::FFFF:1.2.3.4')) -1 0 -SELECT IS_IPV4_MAPPED(INET6_ATON('::ABCD:1.2.3.4')), -IS_IPV4_COMPAT(INET6_ATON('::ABCD:1.2.3.4')); -IS_IPV4_MAPPED(INET6_ATON('::ABCD:1.2.3.4')) IS_IPV4_COMPAT(INET6_ATON('::ABCD:1.2.3.4')) -0 0 -SELECT IS_IPV4_MAPPED(INET6_ATON('::1')), -IS_IPV4_COMPAT(INET6_ATON('::1')); -IS_IPV4_MAPPED(INET6_ATON('::1')) IS_IPV4_COMPAT(INET6_ATON('::1')) -0 0 -SELECT IS_IPV4_MAPPED(INET6_ATON('::')), -IS_IPV4_COMPAT(INET6_ATON('::')); -IS_IPV4_MAPPED(INET6_ATON('::')) IS_IPV4_COMPAT(INET6_ATON('::')) -0 0 - -# -- Checking IS_IPV4_COMPAT()... - - -# -- Working with a table... - -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; -CREATE TABLE t1(ip INT UNSIGNED); -CREATE TABLE t2(ip VARBINARY(16)); - -INSERT INTO t1 VALUES -(INET_ATON('1.2.3.4')), (INET_ATON('255.255.255.255')); -SELECT INET_NTOA(ip) FROM t1; -INET_NTOA(ip) -1.2.3.4 -255.255.255.255 - -INSERT INTO t2 SELECT INET6_ATON(INET_NTOA(ip)) FROM t1; -SELECT INET6_NTOA(ip), HEX(ip), LENGTH(ip) FROM t2; -INET6_NTOA(ip) HEX(ip) LENGTH(ip) -1.2.3.4 01020304 4 -255.255.255.255 FFFFFFFF 4 -DELETE FROM t2; - -INSERT INTO t2 VALUES -(INET6_ATON('1.2.3.4')), (INET6_ATON('255.255.255.255')), -(INET6_ATON('::1.2.3.4')), (INET6_ATON('::ffff:255.255.255.255')), -(INET6_ATON('::')), (INET6_ATON('::1')), -(INET6_ATON('1020:3040:5060:7080:90A0:B0C0:D0E0:F010')); -SELECT INET6_NTOA(ip), HEX(ip), LENGTH(ip) FROM t2; -INET6_NTOA(ip) HEX(ip) LENGTH(ip) -1.2.3.4 01020304 4 -255.255.255.255 FFFFFFFF 4 -::1.2.3.4 00000000000000000000000001020304 16 -::ffff:255.255.255.255 00000000000000000000FFFFFFFFFFFF 16 -:: 00000000000000000000000000000000 16 -::1 00000000000000000000000000000001 16 -1020:3040:5060:7080:90a0:b0c0:d0e0:f010 102030405060708090A0B0C0D0E0F010 16 - -DROP TABLE t1; -DROP TABLE t2; -# -# MDEV-4018 : Microseconds in GET_LOCK() -# -connection default; -select is_used_lock('test') = connection_id(); -is_used_lock('test') = connection_id() -NULL -# GET_LOCK returns 1 if it manages to acquire a lock -select get_lock('test', 0); -get_lock('test', 0) -1 -connect con1,localhost,root,,; -connection con1; -select is_used_lock('test') = connection_id(); -is_used_lock('test') = connection_id() -0 -select get_lock('test', 0); -get_lock('test', 0) -0 -select get_lock('test', 1.0); -get_lock('test', 1.0) -0 -select get_lock('test', 1.5); -get_lock('test', 1.5) -0 -select get_lock('test', 0.1); -get_lock('test', 0.1) -0 -select get_lock('test', 0.000001); -get_lock('test', 0.000001) -0 -select get_lock('test', 0.0000000000000001); -get_lock('test', 0.0000000000000001) -0 -connection default; -select is_used_lock('test') = connection_id(); -is_used_lock('test') = connection_id() -1 -select release_lock('test'); -release_lock('test') -1 - -# -- Done. - -# -# MDEV-13685 Can not replay binary log due to Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' -# -SET NAMES utf8; -SELECT COERCIBILITY(NAME_CONST('name','test')); -COERCIBILITY(NAME_CONST('name','test')) -2 -SELECT COERCIBILITY(NAME_CONST('name',TIME'00:00:00')); -COERCIBILITY(NAME_CONST('name',TIME'00:00:00')) -5 -SELECT COERCIBILITY(NAME_CONST('name',15)); -COERCIBILITY(NAME_CONST('name',15)) -5 -SELECT CONCAT(NAME_CONST('name',15),'오'); -CONCAT(NAME_CONST('name',15),'오') -15오 -SET NAMES latin1; -# -# MDEV-14116 INET6_NTOA output is set as null to varchar(39) variable -# -CREATE PROCEDURE p1() -BEGIN -DECLARE ip_full_addr varchar(39) DEFAULT ""; -SELECT INET6_NTOA(UNHEX('20000000000000000000000000000000')) into ip_full_addr; -SELECT ip_full_addr; -END; -$$ -CALL p1(); -ip_full_addr -2000:: -DROP PROCEDURE p1; -# -# Start of 10.2 tests -# -# -# MDEV-10134 Add full support for DEFAULT -# -CREATE TABLE t1 ( -a VARCHAR(30), -b BIGINT DEFAULT INET_ATON(a), -a1 VARCHAR(30) DEFAULT INET_NTOA(b), -c INT DEFAULT IS_IPV4(a), -d INT DEFAULT IS_IPV6(a) -); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` varchar(30) DEFAULT NULL, - `b` bigint(20) DEFAULT inet_aton(`a`), - `a1` varchar(30) DEFAULT inet_ntoa(`b`), - `c` int(11) DEFAULT is_ipv4(`a`), - `d` int(11) DEFAULT is_ipv6(`a`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -INSERT INTO t1 (a) VALUES ('192.168.001.001'),('::1'),('xxx'); -SELECT * FROM t1; -a b a1 c d -192.168.001.001 3232235777 192.168.1.1 1 0 -::1 NULL NULL 0 1 -xxx NULL NULL 0 0 -DROP TABLE t1; -CREATE TABLE t1 ( -str VARCHAR(128), -addr VARBINARY(16) DEFAULT INET6_ATON(str), -str1 VARCHAR(128) DEFAULT INET6_NTOA(addr), -b INT DEFAULT IS_IPV4_COMPAT(addr), -c INT DEFAULT IS_IPV4_MAPPED(addr) -); -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `str` varchar(128) DEFAULT NULL, - `addr` varbinary(16) DEFAULT inet6_aton(`str`), - `str1` varchar(128) DEFAULT inet6_ntoa(`addr`), - `b` int(11) DEFAULT is_ipv4_compat(`addr`), - `c` int(11) DEFAULT is_ipv4_mapped(`addr`) -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -INSERT INTO t1 (str) VALUES ('::FFFF:192.168.0.1'),('::10.0.5.9'); -SELECT str, str1, b,c FROM t1; -str str1 b c -::FFFF:192.168.0.1 ::ffff:192.168.0.1 0 1 -::10.0.5.9 ::10.0.5.9 1 0 -DROP TABLE t1; -# -# MDEV-14613: Assertion `fixed == 0' failed in Item_func::fix_fields -# -CREATE TABLE `t1` ( -`numgtfmt` char(10) COLLATE utf8_bin NOT NULL -) DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -create view v1(numgtfmt) -as -select 'x' from t1 -union -select 'x' from t1 ; -SELECT * FROM v1 WHERE numgtfmt = NAME_CONST('wnumgtfmt',_utf8'QEDITIONS' COLLATE 'utf8_bin'); -numgtfmt -DROP VIEW v1; -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 |