# # Testing of misc functions # 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); select inet_ntoa(inet_aton("255.255.255.255.255.255.255.255")); select inet_aton("255.255.255.255.255"),inet_aton("255.255.1.255"),inet_aton("0.1.255"); select inet_ntoa(1099511627775),inet_ntoa(4294902271),inet_ntoa(511); select hex(inet_aton('127')); select hex(inet_aton('127.1')); select hex(inet_aton('127.1.1')); select length(uuid()), charset(uuid()), length(unhex(replace(uuid(),_utf8'-',_utf8''))); # # Test for core dump with nan # select length(format('nan', 2)) > 0; # # Test for bug #628 # select concat("$",format(2500,2)); # Test for BUG#7716 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) ); select a from t1 where left(a+0,6) = ( left(20040106,6) ); select a from t1 where right(a+0,6) in ( right(20040106123400,6) ); select a from t1 where right(a+0,6) = ( right(20040106123400,6) ); select a from t1 where mid(a+0,6,3) in ( mid(20040106123400,6,3) ); select a from t1 where mid(a+0,6,3) = ( mid(20040106123400,6,3) ); drop table t1; # # Bug#16501: IS_USED_LOCK does not appear to work # --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1 (conn CHAR(7), connection_id INT); INSERT INTO t1 VALUES ('default', CONNECTION_ID()); SELECT GET_LOCK('bug16501',600); connect (con1,localhost,root,,); INSERT INTO t1 VALUES ('con1', CONNECTION_ID()); SELECT IS_USED_LOCK('bug16501') = connection_id FROM t1 WHERE conn = 'default'; send SELECT GET_LOCK('bug16501',600); connection default; SELECT IS_USED_LOCK('bug16501') = CONNECTION_ID(); SELECT RELEASE_LOCK('bug16501'); connection con1; reap; connection default; SELECT IS_USED_LOCK('bug16501') = connection_id FROM t1 WHERE conn = 'con1'; connection con1; SELECT IS_USED_LOCK('bug16501') = CONNECTION_ID(); SELECT RELEASE_LOCK('bug16501'); SELECT IS_USED_LOCK('bug16501'); disconnect con1; connection default; DROP TABLE t1; # # Bug #21531: EXPORT_SET() doesn't accept args with coercible character sets # select export_set(3, _latin1'foo', _utf8'bar', ',', 4); --echo End of 4.1 tests # # Test for BUG#9535 # create table t1 as select uuid(), length(uuid()); show create table t1; drop table t1; # # Bug #6760: Add SLEEP() function # create table t1 (a timestamp default '2005-05-05 01:01:01', b timestamp default '2005-05-05 01:01:01'); insert into t1 set a = now(); select sleep(3); update t1 set b = now(); select timediff(b, a) >= '00:00:03' from t1; drop table t1; # # Bug #12689: SLEEP() gets incorrectly cached/optimized-away # set global query_cache_size=1355776; create table t1 (a int); insert into t1 values (1),(1),(1); create table t2 (a datetime default null, b datetime default null); insert into t2 set a = now(); select a from t1 where sleep(1); update t2 set b = now() where b is null; insert into t2 set a = now(); select a from t1 where sleep(a); update t2 set b = now() where b is null; insert into t2 set a = now(); select a from t1 where sleep(1); update t2 set b = now() where b is null; select timediff(b, a) >= '00:00:03' from t2; drop table t2; drop table t1; set global query_cache_size=default; # # Bug #21466: INET_ATON() returns signed, not unsigned # create table t1 select INET_ATON('255.255.0.1') as `a`; show create table t1; drop table t1; # # Bug#26093 (SELECT BENCHMARK() for SELECT statements does not produce # valid results) # --disable_warnings drop table if exists table_26093; drop function if exists func_26093_a; drop function if exists func_26093_b; --enable_warnings create table table_26093(a int); insert into table_26093 values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); delimiter //; 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// delimiter ;// select avg(a) from table_26093; select benchmark(100, (select avg(a) from table_26093)); set @invoked := 0; select benchmark(100, (select avg(func_26093_a(a)) from table_26093)); # Returns only 10, since intermediate results are cached. select @invoked; set @invoked := 0; select benchmark(100, (select avg(func_26093_b(a, rand())) from table_26093)); # Returns 1000, due to rand() preventing caching. select @invoked; --error ER_SUBQUERY_NO_1_ROW select benchmark(100, (select (a) from table_26093)); --error ER_OPERAND_COLUMNS select benchmark(100, (select 1, 1)); drop table table_26093; drop function func_26093_a; drop function func_26093_b; # # Bug #30832: Assertion + crash with select name_const('test',now()); # --error ER_WRONG_ARGUMENTS SELECT NAME_CONST('test', NOW()); --error ER_WRONG_ARGUMENTS SELECT NAME_CONST('test', UPPER('test')); SELECT NAME_CONST('test', NULL); SELECT NAME_CONST('test', 1); SELECT NAME_CONST('test', -1); SELECT NAME_CONST('test', 1.0); SELECT NAME_CONST('test', -1.0); SELECT NAME_CONST('test', 'test'); # # Bug #34749: Server crash when using NAME_CONST() with an aggregate function # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); # NAME_CONST() + aggregate. SELECT NAME_CONST('flag',1) * MAX(a) FROM t1; SELECT NAME_CONST('flag',1.5) * MAX(a) FROM t1; # Now, wrap the INT_ITEM in Item_func_neg and watch the pretty explosions SELECT NAME_CONST('flag',-1) * MAX(a) FROM t1; SELECT NAME_CONST('flag',-1.5) * MAX(a) FROM t1; --error ER_WRONG_ARGUMENTS SELECT NAME_CONST('flag', SQRT(4)) * MAX(a) FROM t1; --error ER_WRONG_ARGUMENTS SELECT NAME_CONST('flag',-SQRT(4)) * MAX(a) FROM t1; DROP TABLE t1; # # Bug #27545: erroneous usage of NAME_CONST with a name as the first parameter # resolved against a column name of a derived table hangs the client # CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (5), (2); --error ER_WRONG_ARGUMENTS SELECT NAME_CONST(x,2) FROM (SELECT a x FROM t1) t; DROP TABLE t1; # # Bug #32559: connection hangs on query with name_const # CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (), (), (); --error ER_WRONG_ARGUMENTS SELECT NAME_CONST(a, '1') FROM t1; --error ER_WRONG_ARGUMENTS SET INSERT_ID= NAME_CONST(a, a); DROP TABLE t1; # # Bug #31349: ERROR 1062 (23000): Duplicate entry '' for key 'group_key' # create table t1 (a int not null); insert into t1 values (-1), (-2); select min(a) from t1 group by inet_ntoa(a); drop table t1; # # BUG#34289 - Incorrect NAME_CONST substitution in stored procedures breaks # replication # SELECT NAME_CONST('var', 'value') COLLATE latin1_general_cs; --echo End of 5.0 tests