~drizzle-trunk/drizzle/development

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

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

sleep(3)

0

update t1 set b = now();

select timediff(b, a) >= '00:00:03' from t1;

timediff(b, a) >= '00:00:03'

1

drop table t1;

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

a

update t2 set b = now() where b is null;

insert into t2 set a = now();

select a from t1 where sleep(a);

a

update t2 set b = now() where b is null;

insert into t2 set a = now();

select a from t1 where sleep(1);

a

update t2 set b = now() where b is null;

select timediff(b, a) >= '00:00:03' from t2;

timediff(b, a) >= '00:00:03'

1

1

1

drop table t2;

drop table t1;

set global query_cache_size=default;

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

End of 5.0 tests

select connection_id() > 0;

connection_id() > 0

1

End of tests