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 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); SELECT IS_USED_LOCK('bug16501') = CONNECTION_ID(); IS_USED_LOCK('bug16501') = CONNECTION_ID() 1 SELECT RELEASE_LOCK('bug16501'); RELEASE_LOCK('bug16501') 1 GET_LOCK('bug16501',600) 1 SELECT IS_USED_LOCK('bug16501') = connection_id FROM t1 WHERE conn = 'con1'; IS_USED_LOCK('bug16501') = connection_id 1 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 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 NOT NULL DEFAULT '', `length(uuid())` int(10) NOT NULL DEFAULT '0' ) 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 # # 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 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 ) engine=mysql; Warnings: Warning 1286 Unknown storage engine 'mysql' Warning 1266 Using storage engine MyISAM for table 'tv' 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 `test`.`t1`.`a` AS `a` from `test`.`t1` left join (`test`.`tv`) on((`test`.`tv`.`e` = `test`.`t1`.`a`)) where (not(((last_value(NULL,`test`.`tv`.`e`),(select last_value(NULL,'1') from dual where trigcond(((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 `test`.`t1`.`a` AS `a` from `test`.`t1` left join (`test`.`tv`) on((`test`.`tv`.`e` = `test`.`t1`.`a`)) where (not(<`test`.`tv`.`e`>((`test`.`tv`.`e`,(select last_value(NULL,'1') from dual where trigcond(((`test`.`tv`.`e`) = last_value(NULL,'1')))))))) set optimizer_switch=@optimizer_switch_save; drop view v_merge, vm; drop table t1,tv; # # 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 # -> Switching to 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 # -> Switching to 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 # -> Switching to connection 'con1' create table t1 select connection_id() as id; select get_lock('test', 7200); # -> Switching to connection 'default' select (@id := id) - id from t1; (@id := id) - id 0 kill query @id; # -> Switching to connection 'con1' get_lock('test', 7200) NULL # -> Switching to 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 # -> Switching to connection 'con1' select get_lock('test2', 0); get_lock('test2', 0) 1 select get_lock('test1', 7200); # -> Switching to 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 # -> Switching to connection 'con1' get_lock('test1', 7200) 1 select release_lock('test2'); release_lock('test2') 1 select release_lock('test1'); release_lock('test1') 1 # -> Switching to 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 # -> Switching to connection 'con1' lock table t1 write; select get_lock('test', 7200); # -> Switching to 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 # -> Switching to connection 'con1' get_lock('test', 7200) 1 select release_lock('test'); release_lock('test') 1 unlock tables; # cleanup 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 # # End of 5.5 tests # # # End of tests #