func_misc.test 5.97 KB
Newer Older
1 2 3 4
#
# Testing of misc functions
#

serg@serg.mylan's avatar
serg@serg.mylan committed
5
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);
6 7 8 9

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);
10

11 12 13 14
select hex(inet_aton('127'));
select hex(inet_aton('127.1'));
select hex(inet_aton('127.1.1'));

serg@serg.mylan's avatar
serg@serg.mylan committed
15 16
select length(uuid()), charset(uuid()), length(unhex(replace(uuid(),_utf8'-',_utf8'')));

17 18 19 20
#
# Test for core dump with nan
#
select length(format('nan', 2)) > 0;
hf@deer.(none)'s avatar
hf@deer.(none) committed
21 22 23 24 25

#
# Test for bug #628
#
select concat("$",format(2500,2));
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

# 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;

41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

#
# 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');
64 65 66
connection con1;
reap;
connection default;
67 68 69 70 71 72 73 74 75 76 77 78 79 80
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;

81 82 83 84 85 86
#
# 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
87

88 89

#
90
# Test for BUG#9535
91
#
92 93 94
create table t1 as select uuid(), length(uuid());
show create table t1;
drop table t1;
95

96
#
97
# Bug #6760: Add SLEEP() function
98
#
99 100 101 102 103 104 105
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;
106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127

#
# 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;

128 129 130 131 132 133 134
#
# 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;
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191

#
# 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;

192 193 194 195 196 197 198 199 200 201
#
# 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);
202
SELECT NAME_CONST('test', -1);
203
SELECT NAME_CONST('test', 1.0);
204
SELECT NAME_CONST('test', -1.0);
205 206
SELECT NAME_CONST('test', 'test');

igor@olga.mysql.com's avatar
igor@olga.mysql.com committed
207 208 209 210 211 212 213 214
#
# 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);

215
--error ER_WRONG_ARGUMENTS
igor@olga.mysql.com's avatar
igor@olga.mysql.com committed
216 217 218 219
SELECT NAME_CONST(x,2) FROM (SELECT a x FROM t1) t;

DROP TABLE t1;

220

221 222 223 224 225 226 227 228 229 230
#
# 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;
231

232 233 234 235 236 237 238 239
#
# 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;

240 241 242 243 244 245
#
# BUG#34289 - Incorrect NAME_CONST substitution in stored procedures breaks
# replication
#
SELECT NAME_CONST('var', 'value') COLLATE latin1_general_cs;

246
--echo End of 5.0 tests
247