1DROP TABLE IF EXISTS t1, t2; 2select 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); 3format(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) 42 123.6 1,234.56 12,345.556 123,456.5555 1,234,567.55550 12,345.24 5select inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")); 6inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")) 7NULL 8select inet_aton("255.255.255.255.255"),inet_aton("255.255.1.255"),inet_aton("0.1.255"); 9inet_aton("255.255.255.255.255") inet_aton("255.255.1.255") inet_aton("0.1.255") 101099511627775 4294902271 65791 11select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); 12inet_ntoa(1099511627775) inet_ntoa(4294902271) inet_ntoa(511) 13NULL 255.255.1.255 0.0.1.255 14select hex(inet_aton('127')); 15hex(inet_aton('127')) 167F 17select hex(inet_aton('127.1')); 18hex(inet_aton('127.1')) 197F000001 20select hex(inet_aton('127.1.1')); 21hex(inet_aton('127.1.1')) 227F010001 23select length(uuid()), charset(uuid()), length(unhex(replace(uuid(),_utf8'-',_utf8''))); 24length(uuid()) charset(uuid()) length(unhex(replace(uuid(),_utf8'-',_utf8''))) 2536 utf8 16 26set @a= uuid_short(); 27set @b= uuid_short(); 28select @b - @a; 29@b - @a 301 31select length(format('nan', 2)) > 0; 32length(format('nan', 2)) > 0 331 34Warnings: 35Warning 1292 Truncated incorrect DOUBLE value: 'nan' 36select concat("$",format(2500,2)); 37concat("$",format(2500,2)) 38$2,500.00 39create table t1 ( a timestamp ); 40insert into t1 values ( '2004-01-06 12:34' ); 41select a from t1 where left(a+0,6) in ( left(20040106,6) ); 42a 432004-01-06 12:34:00 44select a from t1 where left(a+0,6) = ( left(20040106,6) ); 45a 462004-01-06 12:34:00 47select a from t1 where right(a+0,6) in ( right(20040106123400,6) ); 48a 492004-01-06 12:34:00 50select a from t1 where right(a+0,6) = ( right(20040106123400,6) ); 51a 522004-01-06 12:34:00 53select a from t1 where mid(a+0,6,3) in ( mid(20040106123400,6,3) ); 54a 552004-01-06 12:34:00 56select a from t1 where mid(a+0,6,3) = ( mid(20040106123400,6,3) ); 57a 582004-01-06 12:34:00 59drop table t1; 60CREATE TABLE t1 (conn CHAR(7), connection_id INT); 61INSERT INTO t1 VALUES ('default', CONNECTION_ID()); 62SELECT GET_LOCK('bug16501',600); 63GET_LOCK('bug16501',600) 641 65INSERT INTO t1 VALUES ('con1', CONNECTION_ID()); 66SELECT IS_USED_LOCK('bug16501') = connection_id 67FROM t1 68WHERE conn = 'default'; 69IS_USED_LOCK('bug16501') = connection_id 701 71SELECT GET_LOCK('bug16501',600); 72SELECT IS_USED_LOCK('bug16501') = CONNECTION_ID(); 73IS_USED_LOCK('bug16501') = CONNECTION_ID() 741 75SELECT RELEASE_LOCK('bug16501'); 76RELEASE_LOCK('bug16501') 771 78GET_LOCK('bug16501',600) 791 80SELECT IS_USED_LOCK('bug16501') = connection_id 81FROM t1 82WHERE conn = 'con1'; 83IS_USED_LOCK('bug16501') = connection_id 841 85SELECT IS_USED_LOCK('bug16501') = CONNECTION_ID(); 86IS_USED_LOCK('bug16501') = CONNECTION_ID() 871 88SELECT RELEASE_LOCK('bug16501'); 89RELEASE_LOCK('bug16501') 901 91SELECT IS_USED_LOCK('bug16501'); 92IS_USED_LOCK('bug16501') 93NULL 94DROP TABLE t1; 95select export_set(3, _latin1'foo', _utf8'bar', ',', 4); 96export_set(3, _latin1'foo', _utf8'bar', ',', 4) 97foo,foo,bar,bar 98End of 4.1 tests 99create table t1 as select uuid(), length(uuid()); 100show create table t1; 101Table Create Table 102t1 CREATE TABLE `t1` ( 103 `uuid()` varchar(36) CHARACTER SET utf8 NOT NULL DEFAULT '', 104 `length(uuid())` int(10) NOT NULL DEFAULT '0' 105) ENGINE=MyISAM DEFAULT CHARSET=latin1 106drop table t1; 107#------------------------------------------------------------------------ 108# Tests for Bug#6760 and Bug#12689 109SET @row_count = 4; 110SET @sleep_time_per_result_row = 1; 111SET @max_acceptable_delay = 2; 112SET @@global.query_cache_size = 1024 * 64; 113DROP TEMPORARY TABLE IF EXISTS t_history; 114DROP TABLE IF EXISTS t1; 115CREATE TEMPORARY TABLE t_history (attempt SMALLINT, 116start_ts DATETIME, end_ts DATETIME, 117start_cached INTEGER, end_cached INTEGER); 118CREATE TABLE t1 (f1 BIGINT); 119INSERT INTO t1 VALUES (1); 120INSERT INTO t1 VALUES (1); 121INSERT INTO t1 VALUES (1); 122INSERT INTO t1 VALUES (1); 123INSERT INTO t_history 124SET attempt = 4 - 4 + 1, start_ts = NOW(), 125start_cached = 0; 126SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1; 127f1 SLEEP(@sleep_time_per_result_row) 1281 0 1291 0 1301 0 1311 0 132UPDATE t_history SET end_ts = NOW() 133WHERE attempt = 4 - 4 + 1; 134UPDATE t_history SET end_cached = 0 135WHERE attempt = 4 - 4 + 1; 136INSERT INTO t_history 137SET attempt = 4 - 3 + 1, start_ts = NOW(), 138start_cached = 0; 139SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1; 140f1 SLEEP(@sleep_time_per_result_row) 1411 0 1421 0 1431 0 1441 0 145UPDATE t_history SET end_ts = NOW() 146WHERE attempt = 4 - 3 + 1; 147UPDATE t_history SET end_cached = 0 148WHERE attempt = 4 - 3 + 1; 149INSERT INTO t_history 150SET attempt = 4 - 2 + 1, start_ts = NOW(), 151start_cached = 0; 152SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1; 153f1 SLEEP(@sleep_time_per_result_row) 1541 0 1551 0 1561 0 1571 0 158UPDATE t_history SET end_ts = NOW() 159WHERE attempt = 4 - 2 + 1; 160UPDATE t_history SET end_cached = 0 161WHERE attempt = 4 - 2 + 1; 162INSERT INTO t_history 163SET attempt = 4 - 1 + 1, start_ts = NOW(), 164start_cached = 0; 165SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1; 166f1 SLEEP(@sleep_time_per_result_row) 1671 0 1681 0 1691 0 1701 0 171UPDATE t_history SET end_ts = NOW() 172WHERE attempt = 4 - 1 + 1; 173UPDATE t_history SET end_cached = 0 174WHERE attempt = 4 - 1 + 1; 175# Test 1: Does the query with SLEEP need a reasonable time? 176SELECT COUNT(*) >= 4 - 1 INTO @aux1 FROM t_history 177WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count 178BETWEEN 0 AND @max_acceptable_delay; 179SELECT @aux1 AS "Expect 1"; 180Expect 1 1811 182# Test 2: Does the query with SLEEP need a reasonable time even in case 183# of the non first execution? 184SELECT COUNT(*) >= 4 - 1 - 1 INTO @aux2 FROM t_history 185WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count 186BETWEEN 0 AND @max_acceptable_delay 187AND attempt > 1; 188SELECT @aux2 AS "Expect 1"; 189Expect 1 1901 191# Test 3: The query with SLEEP must be not cached. 192SELECT COUNT(*) = 4 INTO @aux3 FROM t_history 193WHERE end_cached = start_cached; 194SELECT @aux3 AS "Expect 1"; 195Expect 1 1961 197DROP TABLE t1; 198DROP TEMPORARY TABLE t_history; 199SET @@global.query_cache_size = default; 200create table t1 select INET_ATON('255.255.0.1') as `a`; 201show create table t1; 202Table Create Table 203t1 CREATE TABLE `t1` ( 204 `a` bigint(21) unsigned DEFAULT NULL 205) ENGINE=MyISAM DEFAULT CHARSET=latin1 206drop table t1; 207drop table if exists table_26093; 208drop function if exists func_26093_a; 209drop function if exists func_26093_b; 210create table table_26093(a int); 211insert into table_26093 values 212(1), (2), (3), (4), (5), 213(6), (7), (8), (9), (10); 214create function func_26093_a(x int) returns int 215begin 216set @invoked := @invoked + 1; 217return x; 218end// 219create function func_26093_b(x int, y int) returns int 220begin 221set @invoked := @invoked + 1; 222return x; 223end// 224select avg(a) from table_26093; 225avg(a) 2265.5000 227select benchmark(100, (select avg(a) from table_26093)); 228benchmark(100, (select avg(a) from table_26093)) 2290 230set @invoked := 0; 231select benchmark(100, (select avg(func_26093_a(a)) from table_26093)); 232benchmark(100, (select avg(func_26093_a(a)) from table_26093)) 2330 234select @invoked; 235@invoked 23610 237set @invoked := 0; 238select benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093)); 239benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093)) 2400 241select @invoked; 242@invoked 2431000 244select benchmark(100, (select (a) from table_26093)); 245ERROR 21000: Subquery returns more than 1 row 246select benchmark(100, (select 1, 1)); 247ERROR 21000: Operand should contain 1 column(s) 248drop table table_26093; 249drop function func_26093_a; 250drop function func_26093_b; 251SELECT NAME_CONST('test', NOW()); 252ERROR HY000: Incorrect arguments to NAME_CONST 253SELECT NAME_CONST('test', UPPER('test')); 254ERROR HY000: Incorrect arguments to NAME_CONST 255SELECT NAME_CONST('test', NULL); 256test 257NULL 258SELECT NAME_CONST('test', 1); 259test 2601 261SELECT NAME_CONST('test', -1); 262test 263-1 264SELECT NAME_CONST('test', 1.0); 265test 2661.0 267SELECT NAME_CONST('test', -1.0); 268test 269-1.0 270SELECT NAME_CONST('test', 'test'); 271test 272test 273CREATE TABLE t1 (a INT); 274INSERT INTO t1 VALUES (1),(2),(3); 275SELECT NAME_CONST('flag',1) * MAX(a) FROM t1; 276NAME_CONST('flag',1) * MAX(a) 2773 278SELECT NAME_CONST('flag',1.5) * MAX(a) FROM t1; 279NAME_CONST('flag',1.5) * MAX(a) 2804.5 281SELECT NAME_CONST('flag',-1) * MAX(a) FROM t1; 282NAME_CONST('flag',-1) * MAX(a) 283-3 284SELECT NAME_CONST('flag',-1.5) * MAX(a) FROM t1; 285NAME_CONST('flag',-1.5) * MAX(a) 286-4.5 287SELECT NAME_CONST('flag', SQRT(4)) * MAX(a) FROM t1; 288ERROR HY000: Incorrect arguments to NAME_CONST 289SELECT NAME_CONST('flag',-SQRT(4)) * MAX(a) FROM t1; 290ERROR HY000: Incorrect arguments to NAME_CONST 291DROP TABLE t1; 292CREATE TABLE t1 (a int); 293INSERT INTO t1 VALUES (5), (2); 294SELECT NAME_CONST(x,2) FROM (SELECT a x FROM t1) t; 295ERROR HY000: Incorrect arguments to NAME_CONST 296DROP TABLE t1; 297CREATE TABLE t1(a INT); 298INSERT INTO t1 VALUES (), (), (); 299SELECT NAME_CONST(a, '1') FROM t1; 300ERROR HY000: Incorrect arguments to NAME_CONST 301SET INSERT_ID= NAME_CONST(a, a); 302ERROR HY000: Incorrect arguments to NAME_CONST 303DROP TABLE t1; 304create table t1 (a int not null); 305insert into t1 values (-1), (-2); 306select min(a) from t1 group by inet_ntoa(a); 307min(a) 308-2 309drop table t1; 310SELECT NAME_CONST('var', 'value') COLLATE latin1_general_cs; 311NAME_CONST('var', 'value') COLLATE latin1_general_cs 312value 313select @@session.time_zone into @save_tz; 314set @@session.time_zone='UTC'; 315select uuid() into @my_uuid; 316select mid(@my_uuid,15,1); 317mid(@my_uuid,15,1) 3181 319select 24 * 60 * 60 * 1000 * 1000 * 10 into @my_uuid_one_day; 320select concat('0',mid(@my_uuid,16,3),mid(@my_uuid,10,4),left(@my_uuid,8)) into @my_uuidate; 321select floor(conv(@my_uuidate,16,10)/@my_uuid_one_day) into @my_uuid_date; 322select 141427 + datediff(curdate(),'1970-01-01') into @my_uuid_synthetic; 323select @my_uuid_date - @my_uuid_synthetic; 324@my_uuid_date - @my_uuid_synthetic 3250 326set @@session.time_zone=@save_tz; 327CREATE TABLE t1 (a DATE); 328SELECT * FROM t1 WHERE a = NAME_CONST('reportDate', 329_binary'2009-01-09' COLLATE 'binary'); 330a 331DROP TABLE t1; 332select NAME_CONST('_id',1234) as id; 333id 3341234 335End of 5.0 tests 336SELECT NAME_CONST('a', -(1 OR 2)) OR 1; 337ERROR HY000: Incorrect arguments to NAME_CONST 338SELECT NAME_CONST('a', -(1 AND 2)) OR 1; 339ERROR HY000: Incorrect arguments to NAME_CONST 340SELECT NAME_CONST('a', -(1)) OR 1; 341NAME_CONST('a', -(1)) OR 1 3421 343select connection_id() > 0; 344connection_id() > 0 3451 346# 347# Bug #54461: crash with longblob and union or update with subquery 348# 349CREATE TABLE t1 (a INT, b LONGBLOB); 350INSERT INTO t1 VALUES (1, '2'), (2, '3'), (3, '2'); 351SELECT DISTINCT LEAST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1; 352LEAST(a, (SELECT b FROM t1 LIMIT 1)) 3531 3542 355SELECT DISTINCT GREATEST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1; 356GREATEST(a, (SELECT b FROM t1 LIMIT 1)) 3572 3583 3591 360DROP TABLE t1; 361SELECT INET_NTOA(0); 362INET_NTOA(0) 3630.0.0.0 364SELECT '1' IN ('1', INET_NTOA(0)); 365'1' IN ('1', INET_NTOA(0)) 3661 367# 368# Bug #52165: Assertion failed: file .\dtoa.c, line 465 369# 370CREATE TABLE t1 (a SET('a'), b INT); 371INSERT INTO t1 VALUES ('', 0); 372SELECT COALESCE(a) = COALESCE(b) FROM t1; 373COALESCE(a) = COALESCE(b) 3741 375DROP TABLE t1; 376# 377# Bug #54461: crash with longblob and union or update with subquery 378# 379CREATE TABLE t1 (a INT, b LONGBLOB); 380INSERT INTO t1 VALUES (1, '2'), (2, '3'), (3, '2'); 381SELECT DISTINCT LEAST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1; 382LEAST(a, (SELECT b FROM t1 LIMIT 1)) 3831 3842 385SELECT DISTINCT GREATEST(a, (SELECT b FROM t1 LIMIT 1)) FROM t1 UNION SELECT 1; 386GREATEST(a, (SELECT b FROM t1 LIMIT 1)) 3872 3883 3891 390DROP TABLE t1; 391SELECT INET_NTOA(0); 392INET_NTOA(0) 3930.0.0.0 394SELECT '1' IN ('1', INET_NTOA(0)); 395'1' IN ('1', INET_NTOA(0)) 3961 397# 398# End of 5.1 tests 399# 400# 401# Bug #58199: name_const in the having clause crashes 402# 403CREATE TABLE t1 (a INT); 404SELECT 1 from t1 HAVING NAME_CONST('', a); 405ERROR HY000: Incorrect arguments to NAME_CONST 406DROP TABLE t1; 407# 408# GET_LOCK, RELEASE_LOCK, IS_USED_LOCK functions test 409# 410# IS_USED_LOCK, IS_FREE_LOCK: the lock is not acquired 411# Note: IS_USED_LOCK returns NULL if the lock is unused 412select is_used_lock('test'); 413is_used_lock('test') 414NULL 415select is_free_lock('test'); 416is_free_lock('test') 4171 418# GET_LOCK returns 1 if it manages to acquire a lock 419select get_lock('test', 0); 420get_lock('test', 0) 4211 422# IS_USED_LOCK, IS_FREE_LOCK: the lock is acquired 423select is_free_lock('test'); 424is_free_lock('test') 4250 426select is_used_lock('test') = connection_id(); 427is_used_lock('test') = connection_id() 4281 429# -> Switching to connection 'con1' 430# IS_USED_LOCK, IS_FREE_LOCK: the lock is acquired in another 431# connection 432select is_used_lock('test') = connection_id(); 433is_used_lock('test') = connection_id() 4340 435select is_free_lock('test'); 436is_free_lock('test') 4370 438# GET_LOCK returns 0 if it can't acquire a lock (wait timeout) 439select get_lock('test', 0); 440get_lock('test', 0) 4410 442# RELEASE_LOCK returns 0 if the lock belongs to another connection 443select release_lock('test'); 444release_lock('test') 4450 446# -> Switching to connection 'default' 447# RELEASE_LOCK returns 1 if it successfully releases a lock 448select release_lock('test'); 449release_lock('test') 4501 451# RELEASE_LOCK returns NULL if it doesn't release a lock and there is no such lock 452select release_lock('test'); 453release_lock('test') 454NULL 455# Test that get_lock() returns NULL if error. 456select get_lock('test', 0); 457get_lock('test', 0) 4581 459# -> Switching to connection 'con1' 460create table t1 select connection_id() as id; 461select get_lock('test', 7200); 462# -> Switching to connection 'default' 463select (@id := id) - id from t1; 464(@id := id) - id 4650 466kill query @id; 467# -> Switching to connection 'con1' 468get_lock('test', 7200) 469NULL 470# -> Switching to connection 'default' 471# GET_LOCK() works recursively 472select get_lock('test', 0); 473get_lock('test', 0) 4741 475select get_lock('test', 0); 476get_lock('test', 0) 4771 478select get_lock('test', 0); 479get_lock('test', 0) 4801 481# RELEASE_LOCK() needs to be called recursively then, too 482select release_lock('test'); 483release_lock('test') 4841 485select release_lock('test'); 486release_lock('test') 4871 488select release_lock('test'); 489release_lock('test') 4901 491# Once the last instance of the lock is released, 492# the next call returns NULL 493select release_lock('test'); 494release_lock('test') 4951 496# Multiple locks in the same session are OK 497select get_lock('test1', 0); 498get_lock('test1', 0) 4991 500select get_lock('test2', 0); 501get_lock('test2', 0) 5021 503select get_lock('test3', 0); 504get_lock('test3', 0) 5051 506select release_lock('test1'); 507release_lock('test1') 5081 509select release_lock('test2'); 510release_lock('test2') 5111 512select release_lock('test3'); 513release_lock('test3') 5141 515# Deadlocks are detected e.g. in case of a mutual wait 516select get_lock('test1', 0); 517get_lock('test1', 0) 5181 519# -> Switching to connection 'con1' 520select get_lock('test2', 0); 521get_lock('test2', 0) 5221 523select get_lock('test1', 7200); 524# -> Switching to connection 'default' 525select get_lock('test2', 7200); 526ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 527select release_lock('test1'); 528release_lock('test1') 5291 530# -> Switching to connection 'con1' 531get_lock('test1', 7200) 5321 533select release_lock('test2'); 534release_lock('test2') 5351 536select release_lock('test1'); 537release_lock('test1') 5381 539# -> Switching to connection 'default' 540# LOCK/UNLOCK TABLES works fine with a user lock. 541lock table t1 write; 542select get_lock('test', 0); 543get_lock('test', 0) 5441 545unlock tables; 546commit; 547select release_lock('test'); 548release_lock('test') 5491 550# GLOBAL READ LOCK works with fine with user locks 551select get_lock('test1', 0); 552get_lock('test1', 0) 5531 554flush tables with read lock; 555select get_lock('test2', 0); 556get_lock('test2', 0) 5571 558unlock tables; 559commit; 560select release_lock('test1'); 561release_lock('test1') 5621 563select release_lock('test2'); 564release_lock('test2') 5651 566# BEGIN/COMMIT/ROLLBACK don't unlock user locks. 567begin; 568select get_lock('test1', 0); 569get_lock('test1', 0) 5701 571select get_lock('test2', 0); 572get_lock('test2', 0) 5731 574select count(*) from t1; 575count(*) 5761 577rollback; 578select release_lock('test1'); 579release_lock('test1') 5801 581select release_lock('test2'); 582release_lock('test2') 5831 584# Deadlocks between user locks and LOCK TABLES locks 585# are detected OK. 586select get_lock('test', 0); 587get_lock('test', 0) 5881 589# -> Switching to connection 'con1' 590lock table t1 write; 591select get_lock('test', 7200); 592# -> Switching to connection 'default' 593lock table t1 read; 594ERROR 40001: Deadlock found when trying to get lock; try restarting transaction 595select release_lock('test'); 596release_lock('test') 5971 598# -> Switching to connection 'con1' 599get_lock('test', 7200) 6001 601select release_lock('test'); 602release_lock('test') 6031 604unlock tables; 605# cleanup 606drop table t1; 607# check too long identifier names 608select get_lock(repeat('a', 192), 0); 609get_lock(repeat('a', 192), 0) 6101 611select is_used_lock(repeat('a', 192)) = connection_id(); 612is_used_lock(repeat('a', 192)) = connection_id() 6131 614select is_free_lock(repeat('a', 192)); 615is_free_lock(repeat('a', 192)) 6160 617select release_lock(repeat('a', 192)); 618release_lock(repeat('a', 192)) 6191 620select get_lock(repeat('a', 193), 0); 621ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long 622select is_used_lock(repeat('a', 193)); 623ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long 624select is_free_lock(repeat('a', 193)); 625ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long 626select release_lock(repeat('a', 193)); 627ERROR 42000: Identifier name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' is too long 628# 629# End of 5.5 tests 630# 631 632# -- 633# -- WL#5787: IPv6-capable INET_ATON and INET_NTOA functions. 634# -- 635 636# -- INET6_ATON: checking NULL, invalid types, out-of range values... 637 638SELECT INET6_ATON(NULL) IS NULL; 639INET6_ATON(NULL) IS NULL 6401 641SELECT INET6_ATON(123) IS NULL; 642INET6_ATON(123) IS NULL 6431 644SELECT INET6_ATON(123.45) IS NULL; 645INET6_ATON(123.45) IS NULL 6461 647SELECT INET6_ATON(NOW()) IS NULL; 648INET6_ATON(NOW()) IS NULL 6491 650SELECT INET6_ATON('1.2.3') IS NULL; 651INET6_ATON('1.2.3') IS NULL 6521 653SELECT INET6_ATON('1.2.3.') IS NULL; 654INET6_ATON('1.2.3.') IS NULL 6551 656SELECT INET6_ATON('1..3.4') IS NULL; 657INET6_ATON('1..3.4') IS NULL 6581 659SELECT INET6_ATON('-1.2.3.4') IS NULL; 660INET6_ATON('-1.2.3.4') IS NULL 6611 662SELECT INET6_ATON('1.2.3.256') IS NULL; 663INET6_ATON('1.2.3.256') IS NULL 6641 665SELECT INET6_ATON('1.2.3.4.5') IS NULL; 666INET6_ATON('1.2.3.4.5') IS NULL 6671 668SELECT INET6_ATON('0001.2.3.4') IS NULL; 669INET6_ATON('0001.2.3.4') IS NULL 6701 671SELECT INET6_ATON('0x1.2.3.4') IS NULL; 672INET6_ATON('0x1.2.3.4') IS NULL 6731 674SELECT INET6_ATON('a.2.3.4') IS NULL; 675INET6_ATON('a.2.3.4') IS NULL 6761 677SELECT INET6_ATON('1.2.3.4:80') IS NULL; 678INET6_ATON('1.2.3.4:80') IS NULL 6791 680SELECT INET6_ATON('1.2.3.4/32') IS NULL; 681INET6_ATON('1.2.3.4/32') IS NULL 6821 683SELECT INET6_ATON('mysql.com') IS NULL; 684INET6_ATON('mysql.com') IS NULL 6851 686SELECT INET6_ATON(':::') IS NULL; 687INET6_ATON(':::') IS NULL 6881 689SELECT INET6_ATON(':1:2:3') IS NULL; 690INET6_ATON(':1:2:3') IS NULL 6911 692SELECT INET6_ATON('1:2:3:') IS NULL; 693INET6_ATON('1:2:3:') IS NULL 6941 695SELECT INET6_ATON(':1::2:3') IS NULL; 696INET6_ATON(':1::2:3') IS NULL 6971 698SELECT INET6_ATON('1::2:3:') IS NULL; 699INET6_ATON('1::2:3:') IS NULL 7001 701SELECT INET6_ATON('::00001') IS NULL; 702INET6_ATON('::00001') IS NULL 7031 704SELECT INET6_ATON('::00001:2') IS NULL; 705INET6_ATON('::00001:2') IS NULL 7061 707SELECT INET6_ATON('::12345') IS NULL; 708INET6_ATON('::12345') IS NULL 7091 710SELECT INET6_ATON('1020::3040::5060') IS NULL; 711INET6_ATON('1020::3040::5060') IS NULL 7121 713SELECT INET6_ATON('::ABCZ') IS NULL; 714INET6_ATON('::ABCZ') IS NULL 7151 716SELECT INET6_ATON('::0x1.2.3.4') IS NULL; 717INET6_ATON('::0x1.2.3.4') IS NULL 7181 719SELECT INET6_ATON('::1.0x2.3.4') IS NULL; 720INET6_ATON('::1.0x2.3.4') IS NULL 7211 722SELECT INET6_ATON('::a.b.c.d') IS NULL; 723INET6_ATON('::a.b.c.d') IS NULL 7241 725SELECT INET6_ATON('::FFFF:0x1.2.3.4') IS NULL; 726INET6_ATON('::FFFF:0x1.2.3.4') IS NULL 7271 728SELECT INET6_ATON('::FFFF:1.0x2.3.4') IS NULL; 729INET6_ATON('::FFFF:1.0x2.3.4') IS NULL 7301 731SELECT INET6_ATON('::FFFF:a.b.c.d') IS NULL; 732INET6_ATON('::FFFF:a.b.c.d') IS NULL 7331 734SELECT INET6_ATON('::1.2.3.4:ABCD') IS NULL; 735INET6_ATON('::1.2.3.4:ABCD') IS NULL 7361 737# NOTE: such addresses are supported because getaddrinfo() supports them. 738# This is just to record the current behaviour. 739SELECT HEX(INET6_ATON('::ABCD:1.2.3.4')); 740HEX(INET6_ATON('::ABCD:1.2.3.4')) 74100000000000000000000ABCD01020304 742 743# -- INET6_ATON: checking binary representation... 744 745SELECT HEX(INET6_ATON('0.0.0.0')); 746HEX(INET6_ATON('0.0.0.0')) 74700000000 748SELECT HEX(INET6_ATON('00.00.00.00')); 749HEX(INET6_ATON('00.00.00.00')) 75000000000 751SELECT HEX(INET6_ATON('000.000.000.000')); 752HEX(INET6_ATON('000.000.000.000')) 75300000000 754SELECT HEX(INET6_ATON('1.2.3.4')); 755HEX(INET6_ATON('1.2.3.4')) 75601020304 757SELECT HEX(INET6_ATON('01.02.03.04')); 758HEX(INET6_ATON('01.02.03.04')) 75901020304 760SELECT HEX(INET6_ATON('001.002.003.004')); 761HEX(INET6_ATON('001.002.003.004')) 76201020304 763SELECT HEX(INET6_ATON('255.255.255.255')); 764HEX(INET6_ATON('255.255.255.255')) 765FFFFFFFF 766SELECT HEX(INET6_ATON('::')); 767HEX(INET6_ATON('::')) 76800000000000000000000000000000000 769SELECT HEX(INET6_ATON('0::0')); 770HEX(INET6_ATON('0::0')) 77100000000000000000000000000000000 772SELECT HEX(INET6_ATON('1::2')); 773HEX(INET6_ATON('1::2')) 77400010000000000000000000000000002 775SELECT HEX(INET6_ATON('0::')); 776HEX(INET6_ATON('0::')) 77700000000000000000000000000000000 778SELECT HEX(INET6_ATON('1::')); 779HEX(INET6_ATON('1::')) 78000010000000000000000000000000000 781SELECT HEX(INET6_ATON('::0')); 782HEX(INET6_ATON('::0')) 78300000000000000000000000000000000 784SELECT HEX(INET6_ATON('::1')); 785HEX(INET6_ATON('::1')) 78600000000000000000000000000000001 787SELECT HEX(INET6_ATON('1:2:3:4:5:6:7:8')); 788HEX(INET6_ATON('1:2:3:4:5:6:7:8')) 78900010002000300040005000600070008 790SELECT HEX(INET6_ATON('::2:3:4:5:6:7:8')); 791HEX(INET6_ATON('::2:3:4:5:6:7:8')) 79200000002000300040005000600070008 793SELECT HEX(INET6_ATON('1::3:4:5:6:7:8')); 794HEX(INET6_ATON('1::3:4:5:6:7:8')) 79500010000000300040005000600070008 796SELECT HEX(INET6_ATON('1:2::4:5:6:7:8')); 797HEX(INET6_ATON('1:2::4:5:6:7:8')) 79800010002000000040005000600070008 799SELECT HEX(INET6_ATON('1:2:3::5:6:7:8')); 800HEX(INET6_ATON('1:2:3::5:6:7:8')) 80100010002000300000005000600070008 802SELECT HEX(INET6_ATON('1:2:3:4::6:7:8')); 803HEX(INET6_ATON('1:2:3:4::6:7:8')) 80400010002000300040000000600070008 805SELECT HEX(INET6_ATON('1:2:3:4:5::7:8')); 806HEX(INET6_ATON('1:2:3:4:5::7:8')) 80700010002000300040005000000070008 808SELECT HEX(INET6_ATON('1:2:3:4:5:6::8')); 809HEX(INET6_ATON('1:2:3:4:5:6::8')) 81000010002000300040005000600000008 811SELECT HEX(INET6_ATON('1:2:3:4:5:6:7::')); 812HEX(INET6_ATON('1:2:3:4:5:6:7::')) 81300010002000300040005000600070000 814SELECT HEX(INET6_ATON('0000:0000::0000:0001')); 815HEX(INET6_ATON('0000:0000::0000:0001')) 81600000000000000000000000000000001 817SELECT HEX(INET6_ATON('1234:5678:9abc:def0:4321:8765:cba9:0fed')); 818HEX(INET6_ATON('1234:5678:9abc:def0:4321:8765:cba9:0fed')) 819123456789ABCDEF043218765CBA90FED 820SELECT HEX(INET6_ATON('0000:0000:0000:0000:0000:0000:0000:0001')); 821HEX(INET6_ATON('0000:0000:0000:0000:0000:0000:0000:0001')) 82200000000000000000000000000000001 823SELECT HEX(INET6_ATON('::C0A8:0102')); 824HEX(INET6_ATON('::C0A8:0102')) 825000000000000000000000000C0A80102 826SELECT HEX(INET6_ATON('::c0a8:0102')); 827HEX(INET6_ATON('::c0a8:0102')) 828000000000000000000000000C0A80102 829SELECT HEX(INET6_ATON('::192.168.1.2')); 830HEX(INET6_ATON('::192.168.1.2')) 831000000000000000000000000C0A80102 832SELECT HEX(INET6_ATON('::FfFf:C0a8:0102')); 833HEX(INET6_ATON('::FfFf:C0a8:0102')) 83400000000000000000000FFFFC0A80102 835SELECT HEX(INET6_ATON('::ffff:c0a8:0102')); 836HEX(INET6_ATON('::ffff:c0a8:0102')) 83700000000000000000000FFFFC0A80102 838SELECT HEX(INET6_ATON('::ffff:192.168.1.2')); 839HEX(INET6_ATON('::ffff:192.168.1.2')) 84000000000000000000000FFFFC0A80102 841SELECT HEX(INET6_ATON('::01.2.3.4')); 842HEX(INET6_ATON('::01.2.3.4')) 84300000000000000000000000001020304 844SELECT HEX(INET6_ATON('::1.02.3.4')); 845HEX(INET6_ATON('::1.02.3.4')) 84600000000000000000000000001020304 847SELECT HEX(INET6_ATON('::1.2.03.4')); 848HEX(INET6_ATON('::1.2.03.4')) 84900000000000000000000000001020304 850SELECT HEX(INET6_ATON('::1.2.3.04')); 851HEX(INET6_ATON('::1.2.3.04')) 85200000000000000000000000001020304 853SELECT HEX(INET6_ATON('::1.2.3.00')); 854HEX(INET6_ATON('::1.2.3.00')) 85500000000000000000000000001020300 856SELECT HEX(INET6_ATON('::FFFF:01.2.3.4')); 857HEX(INET6_ATON('::FFFF:01.2.3.4')) 85800000000000000000000FFFF01020304 859SELECT HEX(INET6_ATON('::FFFF:1.02.3.4')); 860HEX(INET6_ATON('::FFFF:1.02.3.4')) 86100000000000000000000FFFF01020304 862SELECT HEX(INET6_ATON('::FFFF:1.2.03.4')); 863HEX(INET6_ATON('::FFFF:1.2.03.4')) 86400000000000000000000FFFF01020304 865SELECT HEX(INET6_ATON('::FFFF:1.2.3.04')); 866HEX(INET6_ATON('::FFFF:1.2.3.04')) 86700000000000000000000FFFF01020304 868SELECT HEX(INET6_ATON('::FFFF:1.2.3.00')); 869HEX(INET6_ATON('::FFFF:1.2.3.00')) 87000000000000000000000FFFF01020300 871 872# -- INET6_ATON: checking the length is either 4 or 16... 873 874SELECT LENGTH(INET6_ATON('0.0.0.0')); 875LENGTH(INET6_ATON('0.0.0.0')) 8764 877SELECT LENGTH(INET6_ATON('255.255.255.255')); 878LENGTH(INET6_ATON('255.255.255.255')) 8794 880SELECT LENGTH(INET6_ATON('::')); 881LENGTH(INET6_ATON('::')) 88216 883SELECT LENGTH(INET6_ATON('1020:3040:5060:7080:90A0:B0C0:D0E0:F010')); 884LENGTH(INET6_ATON('1020:3040:5060:7080:90A0:B0C0:D0E0:F010')) 88516 886 887# -- INET6_NTOA: checking NULL, invalid types, out-of range values... 888 889SELECT INET6_NTOA(NULL); 890INET6_NTOA(NULL) 891NULL 892SELECT INET6_NTOA(123); 893INET6_NTOA(123) 894NULL 895SELECT INET6_NTOA(123.456); 896INET6_NTOA(123.456) 897NULL 898SELECT INET6_NTOA(NOW()); 899INET6_NTOA(NOW()) 900NULL 901SELECT INET6_NTOA(UNHEX('C0A801')); 902INET6_NTOA(UNHEX('C0A801')) 903NULL 904SELECT INET6_NTOA(UNHEX('C0A80102')); 905INET6_NTOA(UNHEX('C0A80102')) 906192.168.1.2 907SELECT INET6_NTOA(UNHEX('C0A8010203')); 908INET6_NTOA(UNHEX('C0A8010203')) 909NULL 910SELECT INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F')); 911INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F')) 912NULL 913SELECT INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F10')); 914INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F10')) 915102:304:506:708:90a:b0c:d0e:f10 916SELECT INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F1011')); 917INET6_NTOA(UNHEX('0102030405060708090A0B0C0D0E0F1011')) 918NULL 919SELECT INET6_NTOA('1234'), INET6_NTOA(BINARY('1234')); 920INET6_NTOA('1234') INET6_NTOA(BINARY('1234')) 921NULL 49.50.51.52 922SELECT INET6_NTOA('0123456789abcdef'), INET6_NTOA(BINARY('0123456789abcdef')); 923INET6_NTOA('0123456789abcdef') INET6_NTOA(BINARY('0123456789abcdef')) 924NULL 3031:3233:3435:3637:3839:6162:6364:6566 925 926# -- Checking double-conversion... 927 928SELECT INET6_NTOA(INET6_ATON('::')); 929INET6_NTOA(INET6_ATON('::')) 930:: 931SELECT INET6_NTOA(INET6_ATON('0::0')); 932INET6_NTOA(INET6_ATON('0::0')) 933:: 934SELECT INET6_NTOA(INET6_ATON('1::2')); 935INET6_NTOA(INET6_ATON('1::2')) 9361::2 937SELECT INET6_NTOA(INET6_ATON('0::')); 938INET6_NTOA(INET6_ATON('0::')) 939:: 940SELECT INET6_NTOA(INET6_ATON('1::')); 941INET6_NTOA(INET6_ATON('1::')) 9421:: 943SELECT INET6_NTOA(INET6_ATON('::0')); 944INET6_NTOA(INET6_ATON('::0')) 945:: 946SELECT INET6_NTOA(INET6_ATON('::1')); 947INET6_NTOA(INET6_ATON('::1')) 948::1 949SELECT INET6_NTOA(INET6_ATON('1:2:3:4:5:6:7:8')); 950INET6_NTOA(INET6_ATON('1:2:3:4:5:6:7:8')) 9511:2:3:4:5:6:7:8 952SELECT INET6_NTOA(INET6_ATON('::2:3:4:5:6:7:8')); 953INET6_NTOA(INET6_ATON('::2:3:4:5:6:7:8')) 954::2:3:4:5:6:7:8 955SELECT INET6_NTOA(INET6_ATON('1::3:4:5:6:7:8')); 956INET6_NTOA(INET6_ATON('1::3:4:5:6:7:8')) 9571::3:4:5:6:7:8 958SELECT INET6_NTOA(INET6_ATON('1:2::4:5:6:7:8')); 959INET6_NTOA(INET6_ATON('1:2::4:5:6:7:8')) 9601:2::4:5:6:7:8 961SELECT INET6_NTOA(INET6_ATON('1:2:3::5:6:7:8')); 962INET6_NTOA(INET6_ATON('1:2:3::5:6:7:8')) 9631:2:3::5:6:7:8 964SELECT INET6_NTOA(INET6_ATON('1:2:3:4::6:7:8')); 965INET6_NTOA(INET6_ATON('1:2:3:4::6:7:8')) 9661:2:3:4::6:7:8 967SELECT INET6_NTOA(INET6_ATON('1:2:3:4:5::7:8')); 968INET6_NTOA(INET6_ATON('1:2:3:4:5::7:8')) 9691:2:3:4:5::7:8 970SELECT INET6_NTOA(INET6_ATON('1:2:3:4:5:6::8')); 971INET6_NTOA(INET6_ATON('1:2:3:4:5:6::8')) 9721:2:3:4:5:6::8 973SELECT INET6_NTOA(INET6_ATON('1:2:3:4:5:6:7::')); 974INET6_NTOA(INET6_ATON('1:2:3:4:5:6:7::')) 9751:2:3:4:5:6:7:: 976SELECT INET6_NTOA(INET6_ATON('0000:0000::0000:0001')); 977INET6_NTOA(INET6_ATON('0000:0000::0000:0001')) 978::1 979SELECT INET6_NTOA(INET6_ATON('1234:5678:9abc:def0:4321:8765:cba9:0fed')); 980INET6_NTOA(INET6_ATON('1234:5678:9abc:def0:4321:8765:cba9:0fed')) 9811234:5678:9abc:def0:4321:8765:cba9:fed 982SELECT INET6_NTOA(INET6_ATON('0000:0000:0000:0000:0000:0000:0000:0001')); 983INET6_NTOA(INET6_ATON('0000:0000:0000:0000:0000:0000:0000:0001')) 984::1 985SELECT INET6_NTOA(INET6_ATON('::C0A8:0102')); 986INET6_NTOA(INET6_ATON('::C0A8:0102')) 987::192.168.1.2 988SELECT INET6_NTOA(INET6_ATON('::c0a8:0102')); 989INET6_NTOA(INET6_ATON('::c0a8:0102')) 990::192.168.1.2 991SELECT INET6_NTOA(INET6_ATON('::192.168.1.2')); 992INET6_NTOA(INET6_ATON('::192.168.1.2')) 993::192.168.1.2 994SELECT INET6_NTOA(INET6_ATON('::FfFf:C0a8:0102')); 995INET6_NTOA(INET6_ATON('::FfFf:C0a8:0102')) 996::ffff:192.168.1.2 997SELECT INET6_NTOA(INET6_ATON('::ffff:c0a8:0102')); 998INET6_NTOA(INET6_ATON('::ffff:c0a8:0102')) 999::ffff:192.168.1.2 1000SELECT INET6_NTOA(INET6_ATON('::ffff:192.168.1.2')); 1001INET6_NTOA(INET6_ATON('::ffff:192.168.1.2')) 1002::ffff:192.168.1.2 1003SELECT INET6_NTOA(INET6_ATON('::01.2.3.4')); 1004INET6_NTOA(INET6_ATON('::01.2.3.4')) 1005::1.2.3.4 1006SELECT INET6_NTOA(INET6_ATON('::1.02.3.4')); 1007INET6_NTOA(INET6_ATON('::1.02.3.4')) 1008::1.2.3.4 1009SELECT INET6_NTOA(INET6_ATON('::1.2.03.4')); 1010INET6_NTOA(INET6_ATON('::1.2.03.4')) 1011::1.2.3.4 1012SELECT INET6_NTOA(INET6_ATON('::1.2.3.04')); 1013INET6_NTOA(INET6_ATON('::1.2.3.04')) 1014::1.2.3.4 1015SELECT INET6_NTOA(INET6_ATON('::1.2.3.00')); 1016INET6_NTOA(INET6_ATON('::1.2.3.00')) 1017::1.2.3.0 1018SELECT INET6_NTOA(INET6_ATON('::FFFF:01.2.3.4')); 1019INET6_NTOA(INET6_ATON('::FFFF:01.2.3.4')) 1020::ffff:1.2.3.4 1021SELECT INET6_NTOA(INET6_ATON('::FFFF:1.02.3.4')); 1022INET6_NTOA(INET6_ATON('::FFFF:1.02.3.4')) 1023::ffff:1.2.3.4 1024SELECT INET6_NTOA(INET6_ATON('::FFFF:1.2.03.4')); 1025INET6_NTOA(INET6_ATON('::FFFF:1.2.03.4')) 1026::ffff:1.2.3.4 1027SELECT INET6_NTOA(INET6_ATON('::FFFF:1.2.3.04')); 1028INET6_NTOA(INET6_ATON('::FFFF:1.2.3.04')) 1029::ffff:1.2.3.4 1030SELECT INET6_NTOA(INET6_ATON('::FFFF:1.2.3.00')); 1031INET6_NTOA(INET6_ATON('::FFFF:1.2.3.00')) 1032::ffff:1.2.3.0 1033 1034# -- Comparing INET_ATON() and INET6_ATON()... 1035 1036SELECT HEX(INET_ATON('192.168.1.2')); 1037HEX(INET_ATON('192.168.1.2')) 1038C0A80102 1039SELECT HEX(INET6_ATON('192.168.1.2')); 1040HEX(INET6_ATON('192.168.1.2')) 1041C0A80102 1042SELECT HEX(INET_ATON('255.255.255.255')); 1043HEX(INET_ATON('255.255.255.255')) 1044FFFFFFFF 1045SELECT HEX(INET6_ATON('255.255.255.255')); 1046HEX(INET6_ATON('255.255.255.255')) 1047FFFFFFFF 1048SELECT HEX(INET_ATON('192.168.08.2')); 1049HEX(INET_ATON('192.168.08.2')) 1050C0A80802 1051SELECT HEX(INET6_ATON('192.168.08.2')); 1052HEX(INET6_ATON('192.168.08.2')) 1053C0A80802 1054SELECT HEX(INET_ATON('192.168.0x8.2')); 1055HEX(INET_ATON('192.168.0x8.2')) 1056NULL 1057SELECT HEX(INET6_ATON('192.168.0x8.2')); 1058HEX(INET6_ATON('192.168.0x8.2')) 1059NULL 1060SELECT HEX(INET_ATON('1.2.255')); 1061HEX(INET_ATON('1.2.255')) 106210200FF 1063SELECT HEX(INET6_ATON('1.2.255')); 1064HEX(INET6_ATON('1.2.255')) 1065NULL 1066SELECT HEX(INET_ATON('1.2.256')); 1067HEX(INET_ATON('1.2.256')) 1068NULL 1069SELECT HEX(INET6_ATON('1.2.256')); 1070HEX(INET6_ATON('1.2.256')) 1071NULL 1072SELECT HEX(INET_ATON('1.0002.3.4')); 1073HEX(INET_ATON('1.0002.3.4')) 10741020304 1075SELECT HEX(INET6_ATON('1.0002.3.4')); 1076HEX(INET6_ATON('1.0002.3.4')) 1077NULL 1078SELECT HEX(INET_ATON('1.2.3.4.5')); 1079HEX(INET_ATON('1.2.3.4.5')) 1080102030405 1081SELECT HEX(INET6_ATON('1.2.3.4.5')); 1082HEX(INET6_ATON('1.2.3.4.5')) 1083NULL 1084 1085# -- Checking mix of INET- and INET6- functions... 1086 1087SELECT HEX(INET6_ATON(INET_NTOA(INET_ATON('1.2.3.4')))) AS x; 1088x 108901020304 1090 1091# -- Checking IS_IPV4() / IS_IPV6()... 1092 1093SELECT IS_IPV4(NULL); 1094IS_IPV4(NULL) 10950 1096SELECT IS_IPV4(1); 1097IS_IPV4(1) 10980 1099SELECT IS_IPV4(1.0); 1100IS_IPV4(1.0) 11010 1102SELECT IS_IPV4('1.2.3.4'); 1103IS_IPV4('1.2.3.4') 11041 1105SELECT IS_IPV4('001.02.000.255'); 1106IS_IPV4('001.02.000.255') 11071 1108SELECT IS_IPV4('::1.2.0.255'); 1109IS_IPV4('::1.2.0.255') 11100 1111SELECT IS_IPV4('::1'); 1112IS_IPV4('::1') 11130 1114SELECT IS_IPV4(BINARY('1.2.3.4')); 1115IS_IPV4(BINARY('1.2.3.4')) 11161 1117SELECT IS_IPV6(NULL); 1118IS_IPV6(NULL) 11190 1120SELECT IS_IPV6(1); 1121IS_IPV6(1) 11220 1123SELECT IS_IPV6(1.0); 1124IS_IPV6(1.0) 11250 1126SELECT IS_IPV6('1.2.3.4'); 1127IS_IPV6('1.2.3.4') 11280 1129SELECT IS_IPV6('001.02.000.255'); 1130IS_IPV6('001.02.000.255') 11310 1132SELECT IS_IPV6('::001.02.000.255'); 1133IS_IPV6('::001.02.000.255') 11341 1135SELECT IS_IPV6('::1.2.0.255'); 1136IS_IPV6('::1.2.0.255') 11371 1138SELECT IS_IPV6('::1'); 1139IS_IPV6('::1') 11401 1141SELECT IS_IPV6('0000:0000:0000:0000:0000:0000:0000:0001'); 1142IS_IPV6('0000:0000:0000:0000:0000:0000:0000:0001') 11431 1144SELECT IS_IPV6(BINARY('0000:0000:0000:0000:0000:0000:0000:0001')); 1145IS_IPV6(BINARY('0000:0000:0000:0000:0000:0000:0000:0001')) 11461 1147 1148# -- Checking IS_IPV4_MAPPED() and IS_IPV4_COMPAT()... 1149 1150SELECT IS_IPV4_MAPPED(INET6_ATON('1.2.3.4')), 1151IS_IPV4_COMPAT(INET6_ATON('1.2.3.4')); 1152IS_IPV4_MAPPED(INET6_ATON('1.2.3.4')) IS_IPV4_COMPAT(INET6_ATON('1.2.3.4')) 11530 0 1154SELECT IS_IPV4_MAPPED(INET6_ATON('::1.2.3.4')), 1155IS_IPV4_COMPAT(INET6_ATON('::1.2.3.4')); 1156IS_IPV4_MAPPED(INET6_ATON('::1.2.3.4')) IS_IPV4_COMPAT(INET6_ATON('::1.2.3.4')) 11570 1 1158SELECT IS_IPV4_MAPPED(INET6_ATON('::FFFF:1.2.3.4')), 1159IS_IPV4_COMPAT(INET6_ATON('::FFFF:1.2.3.4')); 1160IS_IPV4_MAPPED(INET6_ATON('::FFFF:1.2.3.4')) IS_IPV4_COMPAT(INET6_ATON('::FFFF:1.2.3.4')) 11611 0 1162SELECT IS_IPV4_MAPPED(INET6_ATON('::ABCD:1.2.3.4')), 1163IS_IPV4_COMPAT(INET6_ATON('::ABCD:1.2.3.4')); 1164IS_IPV4_MAPPED(INET6_ATON('::ABCD:1.2.3.4')) IS_IPV4_COMPAT(INET6_ATON('::ABCD:1.2.3.4')) 11650 0 1166SELECT IS_IPV4_MAPPED(INET6_ATON('::1')), 1167IS_IPV4_COMPAT(INET6_ATON('::1')); 1168IS_IPV4_MAPPED(INET6_ATON('::1')) IS_IPV4_COMPAT(INET6_ATON('::1')) 11690 0 1170SELECT IS_IPV4_MAPPED(INET6_ATON('::')), 1171IS_IPV4_COMPAT(INET6_ATON('::')); 1172IS_IPV4_MAPPED(INET6_ATON('::')) IS_IPV4_COMPAT(INET6_ATON('::')) 11730 0 1174 1175# -- Checking IS_IPV4_COMPAT()... 1176 1177 1178# -- Working with a table... 1179 1180DROP TABLE IF EXISTS t1; 1181DROP TABLE IF EXISTS t2; 1182CREATE TABLE t1(ip INT UNSIGNED); 1183CREATE TABLE t2(ip VARBINARY(16)); 1184 1185INSERT INTO t1 VALUES 1186(INET_ATON('1.2.3.4')), (INET_ATON('255.255.255.255')); 1187SELECT INET_NTOA(ip) FROM t1; 1188INET_NTOA(ip) 11891.2.3.4 1190255.255.255.255 1191 1192INSERT INTO t2 SELECT INET6_ATON(INET_NTOA(ip)) FROM t1; 1193SELECT INET6_NTOA(ip), HEX(ip), LENGTH(ip) FROM t2; 1194INET6_NTOA(ip) HEX(ip) LENGTH(ip) 11951.2.3.4 01020304 4 1196255.255.255.255 FFFFFFFF 4 1197DELETE FROM t2; 1198 1199INSERT INTO t2 VALUES 1200(INET6_ATON('1.2.3.4')), (INET6_ATON('255.255.255.255')), 1201(INET6_ATON('::1.2.3.4')), (INET6_ATON('::ffff:255.255.255.255')), 1202(INET6_ATON('::')), (INET6_ATON('::1')), 1203(INET6_ATON('1020:3040:5060:7080:90A0:B0C0:D0E0:F010')); 1204SELECT INET6_NTOA(ip), HEX(ip), LENGTH(ip) FROM t2; 1205INET6_NTOA(ip) HEX(ip) LENGTH(ip) 12061.2.3.4 01020304 4 1207255.255.255.255 FFFFFFFF 4 1208::1.2.3.4 00000000000000000000000001020304 16 1209::ffff:255.255.255.255 00000000000000000000FFFFFFFFFFFF 16 1210:: 00000000000000000000000000000000 16 1211::1 00000000000000000000000000000001 16 12121020:3040:5060:7080:90a0:b0c0:d0e0:f010 102030405060708090A0B0C0D0E0F010 16 1213 1214DROP TABLE t1; 1215DROP TABLE t2; 1216 1217# -- Done. 1218 1219# 1220# End of tests 1221# 1222