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

unknown's avatar
unknown committed
5
--disable_warnings
6
DROP TABLE IF EXISTS t1, t2;
unknown's avatar
unknown committed
7 8
--enable_warnings

unknown's avatar
unknown committed
9
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);
10 11 12 13

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

15 16 17 18
select hex(inet_aton('127'));
select hex(inet_aton('127.1'));
select hex(inet_aton('127.1.1'));

unknown's avatar
unknown committed
19 20
select length(uuid()), charset(uuid()), length(unhex(replace(uuid(),_utf8'-',_utf8'')));

21 22
# As we can assume we are the only user for the mysqld server, the difference
# between two calls should be -1
23 24 25
set @a= uuid_short();
set @b= uuid_short();
select cast(@a - @b as signed);
26

27 28 29 30
#
# Test for core dump with nan
#
select length(format('nan', 2)) > 0;
unknown's avatar
unknown committed
31 32 33 34 35

#
# Test for bug #628
#
select concat("$",format(2500,2));
36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

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

51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70

#
# Bug#16501: IS_USED_LOCK does not appear to work
#

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');
71 72 73
connection con1;
reap;
connection default;
74 75 76 77 78 79 80 81 82 83 84 85 86 87
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;

88 89 90 91 92 93
#
# 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
94

unknown's avatar
unknown committed
95 96

#
97
# Test for BUG#9535
unknown's avatar
unknown committed
98
#
99
--disable_warnings
100
create table t1 as select uuid(), length(uuid());
101
--enable_warnings
102 103
show create table t1;
drop table t1;
104

105
#
106
# Bug#6760: Add SLEEP() function (feature request)
107
#
108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124
#   Logics of original test:
#   Reveal that a query with SLEEP does not need less time than estimated.
#
# Bug#12689: SLEEP() gets incorrectly cached/optimized-away
#
#   Description from bug report (slightly modified)
#
#   Bug 1 (happened all time):
#      SELECT * FROM t1 WHERE SLEEP(1) will only result in a sleep of 1
#      second, regardless of the number of rows in t1.
#   Bug 2 (happened all time):
#      Such a query will also get cached by the query cache, but should not.
#
# Notes (mleich, 2008-05)
# =======================
#
# Experiments around
125
#    Bug#36345 Test 'func_misc' fails on RHAS3 x86_64
126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144
# showed that the tests for both bugs could produce in case of parallel
# artificial system time (like via ntpd)
# - decreases false alarm
# - increases false success
#
# We try here to circumvent these issues by reimplementation of the tests
# and sophisticated scripting, although the cause of the problems is a massive
# error within the setup of the testing environment.
# Tests relying on or checking derivates of the system time must never meet
# parallel manipulations of system time.
#
# Results of experiments with/without manipulation of system time,
# information_schema.processlist content, high load on testing box
# ----------------------------------------------------------------
# Definition: Predicted_cumulative_sleep_time =
#                #_of_result_rows * sleep_time_per_result_row
#
# 1. Total (real sleep time) ~= predicted_cumulative_sleep_time !!
# 2. The state of a session within the PROCESSLIST changes to 'User sleep'
145 146
#    if the sessions runs a statement containing the sleep function and the
#    processing of the statement is just within the phase where the sleep
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
#    is done. (*)
# 3. NOW() and processlist.time behave "synchronous" to system time and
#    show also the "jumps" caused by system time manipulations. (*)
# 4. processlist.time is unsigned, the "next" value below 0 is ~ 4G (*)
# 5. Current processlist.time ~= current real sleep time if the system time
#    was not manipulated. (*)
# 6. High system load can cause delays of <= 2 seconds.
# 7. Thanks to Davi for excellent hints and ideas.
#
#    (*)
#    - information_schema.processlist is not available before MySQL 5.1.
#    - Observation of processlist content requires a
#      - "worker" session sending the query with "send" and pulling results
#        with "reap"
#      - session observing the processlist parallel to the worker session
#      "send" and "reap" do not work in case of an embedded server.
#    Conclusion: Tests based on processlist have too many restrictions.
#
# Solutions for subtests based on TIMEDIFF of values filled via NOW()
# -------------------------------------------------------------------
# Run the following sequence three times
#    1. SELECT <start_time>
#    2. Query with SLEEP
#    3. SELECT <end_time>
# If TIMEDIFF(<end_time>,<start_time>) is at least two times within a
# reasonable range assume that we did not met errors we were looking for.
#
# It is extreme unlikely that we have two system time changes within the
# < 30 seconds runtime. Even if the unlikely happens, there are so
# frequent runs of this test on this or another testing box which will
# catch the problem.
#

--echo #------------------------------------------------------------------------
--echo # Tests for Bug#6760 and Bug#12689
# Number of rows within the intended result set.
SET @row_count = 4;
# Parameter within SLEEP function
SET @sleep_time_per_result_row = 1;
# Maximum acceptable delay caused by high load on testing box
SET @max_acceptable_delay = 2;
# TIMEDIFF = time for query with sleep (mostly the time caused by SLEEP)
#            + time for delays caused by high load on testing box
190
# Ensure that at least a reasonable fraction of TIMEDIFF belongs to the SLEEP
191 192 193 194 195 196 197 198 199 200 201 202 203 204 205
# by appropriate setting of variables.
# Ensure that any "judging" has a base of minimum three attempts.
# (Test 2 uses all attempts except the first one.)
if (!` SELECT (@sleep_time_per_result_row * @row_count - @max_acceptable_delay >
              @sleep_time_per_result_row) AND (@row_count - 1 >= 3)`)
{
   --echo # Have to abort because of error in plausibility check
   --echo ######################################################
   --vertical_results
   SELECT @sleep_time_per_result_row * @row_count - @max_acceptable_delay >
               @sleep_time_per_result_row AS must_be_1,
               @row_count - 1 >= 3 AS must_be_also_1,
               @sleep_time_per_result_row, @row_count, @max_acceptable_delay;
   exit;
}
206 207
SET @@global.query_cache_size = 1024 * 64;
--disable_warnings
208 209
DROP TEMPORARY TABLE IF EXISTS t_history;
DROP TABLE IF EXISTS t1;
210
--enable_warnings
211 212 213
CREATE TEMPORARY TABLE t_history (attempt SMALLINT,
start_ts DATETIME, end_ts DATETIME,
start_cached INTEGER, end_cached INTEGER);
214
CREATE TABLE t1 (f1 BIGINT);
215
let $num = `SELECT @row_count`;
Michael Widenius's avatar
Michael Widenius committed
216 217
--disable_query_log
begin;
218 219 220 221 222
while ($num)
{
   INSERT INTO t1 VALUES (1);
   dec $num;
}
Michael Widenius's avatar
Michael Widenius committed
223 224
commit;
--enable_query_log
225

226 227 228
let $loops = 4;
let $num = $loops;
while ($num)
229
{
230 231 232 233 234 235 236 237 238 239
   let $Qcache_queries_in_cache =
       query_get_value(SHOW STATUS LIKE 'Qcache_queries_in_cache', Value, 1);
   eval
   INSERT INTO t_history
   SET attempt = $loops - $num + 1, start_ts = NOW(),
       start_cached = $Qcache_queries_in_cache;
   SELECT *, SLEEP(@sleep_time_per_result_row) FROM t1;
   #
   # Do not determine Qcache_queries_in_cache before updating end_ts. The SHOW
   # might cost too much time on an overloaded box.
240
   eval
241 242 243 244 245 246 247 248 249
   UPDATE t_history SET end_ts = NOW()
   WHERE attempt = $loops - $num + 1;
   let $Qcache_queries_in_cache =
          query_get_value(SHOW STATUS LIKE 'Qcache_queries_in_cache', Value, 1);
   eval
   UPDATE t_history SET end_cached = $Qcache_queries_in_cache
   WHERE attempt = $loops - $num + 1;
   # DEBUG eval SELECT * FROM t_history WHERE attempt = $loops - $num + 1;
   dec $num;
250
}
251 252 253 254 255 256 257

# 1. The majority of queries with SLEEP must need a reasonable time
#    -> SLEEP has an impact on runtime
#       = Replacement for original Bug#6760 test
#    -> total runtime is clear more needed than for one result row needed
#       = Replacement for one of the original Bug#12689 tests
--echo # Test 1: Does the query with SLEEP need a reasonable time?
258
eval SELECT COUNT(*) >= $loops - 1 INTO @aux1 FROM t_history
259 260 261 262 263 264 265 266 267 268 269 270 271 272
WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
      BETWEEN 0 AND @max_acceptable_delay;
SELECT @aux1 AS "Expect 1";
#
# 2. The majority of queries (the first one must be ignored) with SLEEP must
#    need a reasonable time
#    -> If we assume that the result of a cached query will be sent back
#       immediate, without any sleep, than the query with SLEEP cannot be cached
#       (current and intended behaviour for queries with SLEEP).
#    -> It could be also not excluded that the query was cached but the server
#       honoured somehow the SLEEP. Such a behaviour would be also acceptable.
#    = Replacement for one of the original Bug#12689 tests
--echo # Test 2: Does the query with SLEEP need a reasonable time even in case
--echo #         of the non first execution?
273
eval SELECT COUNT(*) >= $loops - 1 - 1 INTO @aux2 FROM t_history
274 275 276 277 278 279 280 281 282 283 284 285 286 287 288
WHERE TIMEDIFF(end_ts,start_ts) - @sleep_time_per_result_row * @row_count
      BETWEEN 0 AND @max_acceptable_delay
      AND attempt > 1;
SELECT @aux2 AS "Expect 1";
#
# 3. The query with SLEEP should be not cached.
#    -> SHOW STATUS Qcache_queries_in_cache must be not incremented after
#       the execution of the query with SLEEP
--echo # Test 3: The query with SLEEP must be not cached.
eval SELECT COUNT(*) = $loops INTO @aux3 FROM t_history
WHERE end_cached = start_cached;
SELECT @aux3 AS "Expect 1";
#
# Dump the content of t_history if one of the tests failed.
if (`SELECT @aux1 + @aux2 + @aux3 <> 3`)
289
{
290 291
   --echo # Some tests failed, dumping the content of t_history
   SELECT * FROM t_history;
292 293
}
DROP TABLE t1;
294 295
DROP TEMPORARY TABLE t_history;
SET @@global.query_cache_size = default;
296

297 298 299 300 301 302 303
#
# 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;
304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360

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

361 362 363 364 365 366 367 368 369 370
#
# 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);
371
SELECT NAME_CONST('test', -1);
372
SELECT NAME_CONST('test', 1.0);
373
SELECT NAME_CONST('test', -1.0);
374 375
SELECT NAME_CONST('test', 'test');

376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393
#
# 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;

unknown's avatar
unknown committed
394
#
395
# Bug #27545: erroneous usage of NAME_CONST with a name as the first parameter
unknown's avatar
unknown committed
396 397 398 399 400 401
#             resolved against a column name of a derived table hangs the client
#

CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (5), (2);

unknown's avatar
unknown committed
402
--error ER_WRONG_ARGUMENTS
unknown's avatar
unknown committed
403 404 405 406
SELECT NAME_CONST(x,2) FROM (SELECT a x FROM t1) t;

DROP TABLE t1;

407

408 409 410 411 412 413 414 415 416 417
#
# 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;
418

419 420 421 422 423 424 425 426
#
# 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;

427 428 429 430 431 432
#
# BUG#34289 - Incorrect NAME_CONST substitution in stored procedures breaks
# replication
#
SELECT NAME_CONST('var', 'value') COLLATE latin1_general_cs;

433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451
#
# Bug #35848: UUID() returns UUIDs with the wrong time
#
select @@session.time_zone into @save_tz;

# make sure all times are UTC so the DayNr won't differ
set @@session.time_zone='UTC';
select uuid() into @my_uuid;
# if version nibble isn't 1, the following calculations will be rubbish
select mid(@my_uuid,15,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;
# these should be identical; date part of UUID should be current date
select @my_uuid_date - @my_uuid_synthetic;

set @@session.time_zone=@save_tz;

452 453 454 455 456 457 458 459 460

#
# Bug#42014: Crash, name_const with collate
#
CREATE TABLE t1 (a DATE);
SELECT * FROM t1 WHERE a = NAME_CONST('reportDate',
  _binary'2009-01-09' COLLATE 'binary');
DROP TABLE t1;

461 462 463 464 465
#
# Bug#35515: Aliases of variables in binary log are ignored with NAME_CONST
#
select NAME_CONST('_id',1234) as id;

466
--echo End of 5.0 tests
467 468 469 470 471 472 473 474

#
# Bug #30389: connection_id() always return 0 in embedded server
#

select connection_id() > 0;

--echo End of tests