~drizzle-trunk/drizzle/development

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment

2460

t1 InnoDB 10 Compact 2 8192 X X X X X X X X utf8_general_ci NULL

2461

t11 InnoDB 10 Compact 0 0 X X X X X X X X utf8_general_ci NULL

2461

t1 InnoDB 10 Compact 2 8192 X X X X X X X X latin1_swedish_ci NULL

2462

t11 InnoDB 10 Compact 0 0 X X X X X X X X latin1_swedish_ci NULL

2462

2463

select 123 as a from t1 where f1 is null;

2463

2464

2465

drop table t1,t11;

2600

2601

DROP TABLE t1, t2;

2601

2602

End of 4.1 tests

2602

2603

CREATE TABLE t1 (

2603

K2C4 varchar(4) collate utf8_bin NOT NULL default '',

2604

K4N4 varchar(4) collate utf8_bin NOT NULL default '0000',

2604

K2C4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '',

2605

K4N4 varchar(4) character set latin1 collate latin1_bin NOT NULL default '0000',

2605

2606

F2I4 int NOT NULL default '0'

2606

) ENGINE=MyISAM;

2607

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2607

2608

INSERT INTO t1 VALUES

2608

2609

('W%RT', '0100', 1),

2609

2610

('W-RT', '0100', 1),

2788

2789

drop table t1,t2,t3;

2789

2790

create table t1 (id int not null default '0');

2790

2791

insert into t1 values (123),(191),(192);

2791

create table t2 (id char(16) not null);

2792

create table t2 (id char(16) character set utf8 not null);

2792

2793

insert into t2 values ('58013'),('58014'),('58015'),('58016');

2793

create table t3 (a_id int not null, b_id char(16));

2794

create table t3 (a_id int not null, b_id char(16) character set utf8);

2794

2795

insert into t3 values (123,null),(123,null),(123,null),(123,null),(123,null),(123,'58013');

2795

2796

select count(*)

2796

2797

from t1 inner join (t3 left join t2 on t2.id = t3.b_id) on t1.id = t3.a_id;

2834

2835

f1 f2

2835

2836

4 2005-10-01

2836

2837

5 2005-12-30

2837

Warnings:

2838

Warning 1292 Incorrect date value: '2005-09-31' for column 'f2' at row 1

2839

2838

select * from t1 where f2 >= '2005-09-3a' order by f2;

2840

2839

f1 f2

2841

2840

3 2005-09-30

2848

2847

1 2005-01-01

2849

2848

2 2005-09-01

2850

2849

3 2005-09-30

2851

Warnings:

2852

Warning 1292 Incorrect date value: '2005-09-31' for column 'f2' at row 1

2853

2850

select * from t1 where f2 <= '2005-09-3a' order by f2;

2854

2851

f1 f2

2855

2852

1 2005-01-01

2956

2953

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

2957

2954

1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where

2958

2955

DROP TABLE t1,t2;

2956

CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);

2957

INSERT t1 SET i = 0;

2958

UPDATE t1 SET i = -1;

2959

Warnings:

2960

Warning 1264 Out of range value for column 'i' at row 1

2961

SELECT * FROM t1;

2962

2963

2964

UPDATE t1 SET i = CAST(i - 1 AS SIGNED);

2965

Warnings:

2966

Warning 1264 Out of range value for column 'i' at row 1

2967

SELECT * FROM t1;

2968

2969

2970

UPDATE t1 SET i = i - 1;

2971

Warnings:

2972

Warning 1264 Out of range value for column 'i' at row 1

2973

SELECT * FROM t1;

2974

2975

255

2976

DROP TABLE t1;

2959

2977

create table t1 (a int);

2960

2978

insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

2961

2979

create table t2 (a int, b int, c int, e int, primary key(a,b,c));

3061

3079

FROM t1 JOIN t2 ON t2.fk=t1.pk

3062

3080

WHERE t2.fk < 'c' AND t2.pk=t1.fk;

3063

3081

id select_type table type possible_keys key key_len ref rows Extra

3064

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3065

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where

3082

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where

3083

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where

3066

3084

EXPLAIN SELECT t2.*

3067

3085

FROM t1 JOIN t2 ON t2.fk=t1.pk

3068

3086

WHERE t2.fk BETWEEN 'a' AND 'b' AND t2.pk=t1.fk;

3069

3087

id select_type table type possible_keys key key_len ref rows Extra

3070

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3071

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where

3088

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where

3089

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where

3072

3090

EXPLAIN SELECT t2.*

3073

3091

FROM t1 JOIN t2 ON t2.fk=t1.pk

3074

3092

WHERE t2.fk IN ('a','b') AND t2.pk=t1.fk;

3075

3093

id select_type table type possible_keys key key_len ref rows Extra

3076

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3077

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 66 test.t1.fk 1 Using where

3094

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where

3095

1 SIMPLE t2 eq_ref PRIMARY PRIMARY 18 test.t1.fk 1 Using where

3078

3096

DROP TABLE t1,t2;

3079

3097

CREATE TABLE t1 (a int, b varchar(20) NOT NULL, PRIMARY KEY(a));

3080

3098

CREATE TABLE t2 (a int, b varchar(20) NOT NULL,

3084

3102

EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3;

3085

3103

id select_type table type possible_keys key key_len ref rows Extra

3086

3104

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3087

1 SIMPLE t2 const b b 82 const 1 Using index

3105

1 SIMPLE t2 const b b 22 const 1 Using index

3088

3106

DROP TABLE t1,t2;

3089

3107

CREATE TABLE t1(id int PRIMARY KEY, b int, e int);

3090

3108

CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a));

3116

3134

t3.a=t2.a AND t3.c IN ('bb','ee') ;

3117

3135

id select_type table type possible_keys key key_len ref rows Extra

3118

3136

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3119

1 SIMPLE t3 range PRIMARY,ci ci 19 NULL 6 Using where; Using index

3137

1 SIMPLE t3 range PRIMARY,ci ci 7 NULL 6 Using where; Using index

3120

3138

1 SIMPLE t2 ref si,ai ai 5 test.t3.a 1 Using where

3121

3139

EXPLAIN

3122

3140

SELECT t3.a FROM t1,t2 FORCE INDEX (si),t3

3280

3298

coalesce(9223372036854775808, 1) co;

3281

3299

show create table t1;

3282

3300

Table Create Table

3283

t1 CREATE TABLE `t1` (

3284

`i` decimal(19,0) NOT NULL,

3285

`c` decimal(19,0) NOT NULL,

3286

`co` decimal(19,0) NOT NULL

3287

) ENGINE=InnoDB

3301

t1 CREATE TABLE "t1" (

3302

"i" decimal(19,0) NOT NULL,

3303

"c" decimal(19,0) NOT NULL,

3304

"co" decimal(19,0) NOT NULL

3305

) ENGINE=InnoDB DEFAULT CHARSET=latin1

3288

3306

drop table t1;

3289

3307

select

3290

3308

if(1, cast(1111111111111111111 as unsigned), 1) i,

3354

3372

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3355

3373

id select_type table type possible_keys key key_len ref rows Extra

3356

3374

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3357

1 SIMPLE t2 ref name name 15 test.t1.name 1

3375

1 SIMPLE t2 ref name name 6 test.t1.name 1

3358

3376

SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3359

3377

name name n

3360

3378

ccc NULL NULL

3428

3446

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3429

3447

id select_type table type possible_keys key key_len ref rows Extra

3430

3448

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3431

1 SIMPLE t2 ref name name 15 test.t1.name 1

3449

1 SIMPLE t2 ref name name 6 test.t1.name 1

3432

3450

SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name;

3433

3451

name name n

3434

3452

ccc NULL NULL

3556

3574

1 SIMPLE join_6 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where

3557

3575

1 SIMPLE join_7 eq_ref PRIMARY PRIMARY 4 test.join_5.c1 1 100.00 Using where

3558

3576

Warnings:

3559

Note 1003 select `test`.`join_2`.`c1` AS `c1` from `test`.`t1` `join_0` join `test`.`t1` `join_1` join `test`.`t1` `join_2` join `test`.`t1` `join_3` join `test`.`t1` `join_4` join `test`.`t1` `join_5` join `test`.`t1` `join_6` join `test`.`t1` `join_7` where (((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`)) or ((`test`.`join_1`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_2`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_3`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_4`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_5`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_6`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_7`.`c1` = `test`.`join_0`.`c1`) and (`test`.`join_0`.`c2` < '?') and (`test`.`join_1`.`c2` < '?') and (`test`.`join_2`.`c2` > '?') and (`test`.`join_2`.`c2` < '!') and (`test`.`join_3`.`c2` > '?') and (`test`.`join_4`.`c2` = '?') and (`test`.`join_5`.`c2` <> '?') and (`test`.`join_6`.`c2` <> '?') and (`test`.`join_7`.`c2` >= '?'))) group by `test`.`join_3`.`c1`,`test`.`join_2`.`c1`,`test`.`join_1`.`c1`,`test`.`join_0`.`c1`

3577

Note 1003 select "test"."join_2"."c1" AS "c1" from "test"."t1" "join_0" join "test"."t1" "join_1" join "test"."t1" "join_2" join "test"."t1" "join_3" join "test"."t1" "join_4" join "test"."t1" "join_5" join "test"."t1" "join_6" join "test"."t1" "join_7" where ((("test"."join_1"."c1" = "test"."join_0"."c1") and ("test"."join_2"."c1" = "test"."join_0"."c1") and ("test"."join_3"."c1" = "test"."join_0"."c1") and ("test"."join_4"."c1" = "test"."join_0"."c1") and ("test"."join_5"."c1" = "test"."join_0"."c1") and ("test"."join_6"."c1" = "test"."join_0"."c1") and ("test"."join_7"."c1" = "test"."join_0"."c1")) or (("test"."join_1"."c1" = "test"."join_0"."c1") and ("test"."join_2"."c1" = "test"."join_0"."c1") and ("test"."join_3"."c1" = "test"."join_0"."c1") and ("test"."join_4"."c1" = "test"."join_0"."c1") and ("test"."join_5"."c1" = "test"."join_0"."c1") and ("test"."join_6"."c1" = "test"."join_0"."c1") and ("test"."join_7"."c1" = "test"."join_0"."c1") and ("test"."join_4"."c2" = '?') and ("test"."join_0"."c2" < '?') and ("test"."join_1"."c2" < '?') and ("test"."join_2"."c2" > '?') and ("test"."join_2"."c2" < '!') and ("test"."join_3"."c2" > '?') and ("test"."join_5"."c2" <> '?') and ("test"."join_6"."c2" <> '?') and ("test"."join_7"."c2" >= '?'))) group by "test"."join_3"."c1","test"."join_2"."c1","test"."join_1"."c1","test"."join_0"."c1"

3560

3578

SHOW WARNINGS;

3561

3579

Level Code Message

3562

3580

3563

3581

DROP TABLE t1;

3564

3582

SELECT 1 AS ` `;

3565

3583

3684

3702

Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34'

3685

3703

select str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34';

3686

3704

str_to_date('2007-02-30 12:34','%Y-%m-%d %H:%i') = '2007-02-30 12:34'

3687

3688

Warnings:

3689

Warning 1292 Truncated incorrect datetime value: '2007-02-30 12:34'

3705

3690

3706

select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34';

3691

3707

str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'

3692

3708

Older »