~drizzle-trunk/drizzle/development

CREATE TEMPORARY TABLE t1 (gvid int default NULL, hmid int default NULL, volid int default NULL, mmid int default NULL, hdid int default NULL, fsid int default NULL, ctid int default NULL, dtid int default NULL, cost int default NULL, performance int default NULL, serialnumber bigint default NULL, monitored int default '1', removed int default '0', target int default '0', dt_modified timestamp NOT NULL, name varchar(255) default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;

1967

CREATE TABLE t1 (gvid int default NULL, hmid int default NULL, volid int default NULL, mmid int default NULL, hdid int default NULL, fsid int default NULL, ctid int default NULL, dtid int default NULL, cost int default NULL, performance int default NULL, serialnumber bigint default NULL, monitored int default '1', removed int default '0', target int default '0', dt_modified timestamp NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) ENGINE=MyISAM;

1977

1968

INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL);

1978

CREATE TEMPORARY TABLE t2 ( hmid int default NULL, volid int default NULL, sampletid int default NULL, sampletime datetime default NULL, samplevalue bigint default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

1969

CREATE TABLE t2 ( hmid int default NULL, volid int default NULL, sampletid int default NULL, sampletime datetime default NULL, samplevalue bigint default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) ENGINE=MyISAM;

1979

1970

INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35);

1980

1971

SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'wrong-date-value' AND b.sampletime < 'wrong-date-value' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;

1981

ERROR HY000: Received an invalid datetime value 'wrong-date-value'.

1972

gvid the_success the_fail the_size the_time

1973

Warnings:

1974

Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1

1975

Warning 1292 Incorrect datetime value: 'wrong-date-value' for column 'sampletime' at row 1

1982

1976

SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= NULL AND b.sampletime < NULL AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid;

1983

1977

gvid the_success the_fail the_size the_time

1984

1978

DROP TABLE t1,t2;

2014

2008

select * from (t1 as t2 left join t1 as t3 using (a)), t1;

2015

2009

a a

2016

2010

1 1

2011

2 1

2012

3 1

2017

2013

1 2

2014

2 2

2015

3 2

2018

2016

1 3

2019

2 1

2020

2 2

2021

2017

2 3

2022

3 1

2023

3 2

2024

2018

3 3

2025

2019

select * from t1, (t1 as t2 left join t1 as t3 using (a));

2026

2020

a a

2166

2160

2167

2161

2168

2162

drop table t1;

2169

CREATE TEMPORARY TABLE t1 ( aa char(2), id int NOT NULL auto_increment, t2_id int NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;

2163

CREATE TABLE t1 ( aa char(2), id int NOT NULL auto_increment, t2_id int NOT NULL default '0', PRIMARY KEY (id), KEY replace_id (t2_id)) ENGINE=MyISAM;

2170

2164

INSERT INTO t1 VALUES ("1",8264,2506),("2",8299,2517),("3",8301,2518),("4",8302,2519),("5",8303,2520),("6",8304,2521),("7",8305,2522);

2171

CREATE TEMPORARY TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

2165

CREATE TABLE t2 ( id int NOT NULL auto_increment, PRIMARY KEY (id)) ENGINE=MyISAM;

2172

2166

INSERT INTO t2 VALUES (2517), (2518), (2519), (2520), (2521), (2522);

2173

2167

select * from t1, t2 WHERE t1.t2_id = t2.id and t1.t2_id > 0 order by t1.id LIMIT 0, 5;

2174

2168

aa id t2_id id

2224

2218

3 2 3 4

2225

2219

4 2 4 4

2226

2220

drop table t1, t2;

2227

CREATE TEMPORARY TABLE t1 (

2221

CREATE TABLE t1 (

2228

2222

i int NOT NULL default '0',

2229

2223

c char(10) NOT NULL default '',

2230

2224

PRIMARY KEY (i),

2458

2452

create table t11 like t1;

2459

2453

insert into t1 values(1,""),(2,"");

2460

2454

show table status like 't1%';

2461

Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment

2462

# test t1 STANDARD InnoDB # # # # #

2455

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

2456

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

2457

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

2463

2458

select 123 as a from t1 where f1 is null;

2464

2459

2465

2460

drop table t1,t11;

2593

2588

DROP TABLE t1;

2594

2589

CREATE TABLE t1 (c0 int);

2595

2590

CREATE TABLE t2 (c0 int);

2596

INSERT INTO t1 VALUES(@@server_id);

2597

INSERT INTO t2 VALUES(@@server_id);

2598

SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@server_id);

2591

INSERT INTO t1 VALUES(@@connect_timeout);

2592

INSERT INTO t2 VALUES(@@connect_timeout);

2593

SELECT * FROM t1 JOIN t2 ON t1.c0 = t2.c0 WHERE (t1.c0 <=> @@connect_timeout);

2599

2594

c0 c0

2600

2595

X X

2601

2596

DROP TABLE t1, t2;

2602

2597

End of 4.1 tests

2603

CREATE TEMPORARY TABLE t1 (

2598

CREATE TABLE t1 (

2604

2599

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

2605

2600

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

2606

2601

F2I4 int NOT NULL default '0'

2649

2644

Wall

2650

2645

Wall

2651

2646

drop table t1;

2652

create temporary table t1 (s1 int) engine=myisam;

2647

create table t1 (s1 int) engine=myisam;

2653

2648

insert into t1 values (0);

2654

2649

select avg(distinct s1) from t1 group by s1 with rollup;

2655

2650

avg(distinct s1)

2818

2813

insert into t1 values(1,'2005-01-01'),(2,'2005-09-01'),(3,'2005-09-30'),

2819

2814

(4,'2005-10-01'),(5,'2005-12-30');

2820

2815

select * from t1 where f2 >= 0 order by f2;

2821

ERROR HY000: Received an invalid datetime value '0'.

2816

f1 f2

2817

1 2005-01-01

2818

2 2005-09-01

2819

3 2005-09-30

2820

4 2005-10-01

2821

5 2005-12-30

2822

select * from t1 where f2 >= '0000-00-00' order by f2;

2823

ERROR HY000: Received an invalid datetime value '0000-00-00'.

2823

f1 f2

2824

1 2005-01-01

2825

2 2005-09-01

2826

3 2005-09-30

2827

4 2005-10-01

2828

5 2005-12-30

2824

2829

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

2825

ERROR HY000: Received an invalid datetime value '2005-09-31'.

2830

f1 f2

2831

4 2005-10-01

2832

5 2005-12-30

2833

Warnings:

2834

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

2826

2835

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

2827

ERROR HY000: Received an invalid datetime value '2005-09-3a'.

2836

f1 f2

2837

3 2005-09-30

2838

4 2005-10-01

2839

5 2005-12-30

2840

Warnings:

2841

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

2828

2842

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

2829

ERROR HY000: Received an invalid datetime value '2005-09-31'.

2843

f1 f2

2844

1 2005-01-01

2845

2 2005-09-01

2846

3 2005-09-30

2847

Warnings:

2848

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

2830

2849

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

2831

ERROR HY000: Received an invalid datetime value '2005-09-3a'.

2850

f1 f2

2851

1 2005-01-01

2852

2 2005-09-01

2853

Warnings:

2854

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

2832

2855

drop table t1;

2833

2856

CREATE TABLE t1(key_a int4 NOT NULL, optimus varchar(32), PRIMARY KEY(key_a));

2834

2857

CREATE TABLE t2(key_a int4 NOT NULL, prime varchar(32), PRIMARY KEY(key_a));

2952

2975

SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6;

2953

2976

id select_type table type possible_keys key key_len ref rows Extra

2954

2977

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where; Using index

2955

1 SIMPLE t2 ref c c 5 test.t1.a 1 Using where

2978

1 SIMPLE t2 ref c c 5 test.t1.a 1

2956

2979

EXPLAIN

2957

2980

SELECT a, c, d, f FROM t1,t2 WHERE a=c AND b BETWEEN 4 AND 6 AND a > 0;

2958

2981

id select_type table type possible_keys key key_len ref rows Extra

2959

2982

1 SIMPLE t1 range PRIMARY,b b 5 NULL 3 Using where; Using index

2960

1 SIMPLE t2 ref c c 5 test.t1.a 1 Using where

2983

1 SIMPLE t2 ref c c 5 test.t1.a 1

2961

2984

DROP TABLE t1, t2;

2962

2985

create table t1 (

2963

2986

a int not null auto_increment primary key,

2993

3016

EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=1;

2994

3017

id select_type table type possible_keys key key_len ref rows Extra

2995

3018

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

2996

1 SIMPLE t2 ref idx idx 4 const 8 Using index

3019

1 SIMPLE t2 ref idx idx 4 const 7 Using index

2997

3020

EXPLAIN SELECT b FROM t1, t2 WHERE b=c AND a=4;

2998

3021

id select_type table type possible_keys key key_len ref rows Extra

2999

3022

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3011

3034

id select_type table type possible_keys key key_len ref rows Extra

3012

3035

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3013

3036

1 SIMPLE t2 const idx1 NULL NULL NULL 1

3014

1 SIMPLE t3 ALL idx1 NULL NULL NULL 5 Using where

3037

1 SIMPLE t3 ref idx1 idx1 5 const 2

3015

3038

SELECT * FROM t1 LEFT JOIN t2 ON t2.b=t1.a INNER JOIN t3 ON t3.d=t1.id

3016

3039

WHERE t1.id=2;

3017

3040

id a b c d e

3081

3104

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

3082

3105

id select_type table type possible_keys key key_len ref rows Extra

3083

3106

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3084

1 SIMPLE t2 range si si 5 NULL 4 Using where

3107

1 SIMPLE t2 range si si 5 NULL 4 Using where; Using MRR

3085

3108

1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where

3086

3109

EXPLAIN

3087

3110

SELECT t3.a FROM t1,t2,t3

3097

3120

t3.c IN ('bb','ee');

3098

3121

id select_type table type possible_keys key key_len ref rows Extra

3099

3122

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3100

1 SIMPLE t2 range si si 5 NULL 2 Using where

3123

1 SIMPLE t2 range si si 5 NULL 2 Using where; Using MRR

3101

3124

1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where

3102

3125

EXPLAIN

3103

3126

SELECT t3.a FROM t1,t2,t3

3105

3128

t3.c IN ('bb','ee');

3106

3129

id select_type table type possible_keys key key_len ref rows Extra

3107

3130

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3108

1 SIMPLE t2 range si,ai si 5 NULL 2 Using where

3131

1 SIMPLE t2 range si,ai si 5 NULL 2 Using where; Using MRR

3109

3132

1 SIMPLE t3 eq_ref PRIMARY,ci PRIMARY 4 test.t2.a 1 Using where

3110

3133

DROP TABLE t1,t2,t3;

3111

3134

CREATE TABLE t1 ( f1 int primary key, f2 int, f3 int, f4 int, f5 int, f6 int, checked_out int);

3225

3248

AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";

3226

3249

id select_type table type possible_keys key key_len ref rows Extra

3227

3250

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

3228

1 SIMPLE t1 range ts ts 9 NULL 2 Using where

3251

1 SIMPLE t1 range ts ts 4 NULL 1 Using where; Using MRR

3252

Warnings:

3253

Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1

3229

3254

SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30

3230

3255

AND t1.ts BETWEEN t2.dt1 AND t2.dt2

3231

3256

AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31";

3232

3257

a ts a dt1 dt2

3233

3258

30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00

3259

Warnings:

3260

Warning 1292 Incorrect datetime value: '2999-12-31 00:00:00' for column 'ts' at row 1

3234

3261

DROP TABLE t1,t2;

3235

3262

create table t1 (a bigint);

3236

3263

insert into t1 values

3250

3277

show create table t1;

3251

3278

Table Create Table

3252

3279

t1 CREATE TABLE `t1` (

3253

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

3254

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

3255

`co` DECIMAL(19,0) NOT NULL

3256

) ENGINE=InnoDB COLLATE = utf8_general_ci

3280

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

3281

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

3282

`co` decimal(19,0) NOT NULL

3283

) ENGINE=InnoDB

3257

3284

drop table t1;

3258

3285

select

3259

3286

if(1, 1111111111111111111, 1) i,

3584

3611

3585

3612

3586

3613

DROP TABLE t1, t2, t3;

3614

select str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'

3615

and '2007/10/20 00:00:00 GMT';

3616

str_to_date('2007-10-09','%Y-%m-%d') between '2007/10/01 00:00:00 GMT'

3617

and '2007/10/20 00:00:00 GMT'

3618

3619

Warnings:

3620

Warning 1292 Truncated incorrect datetime value: '2007/10/01 00:00:00 GMT'

3621

Warning 1292 Truncated incorrect datetime value: '2007/10/20 00:00:00 GMT'

3622

select str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6';

3623

str_to_date('2007-10-09','%Y-%m-%d') > '2007/10/01 00:00:00 GMT-6'

3624

3625

Warnings:

3626

Warning 1292 Truncated incorrect date value: '2007/10/01 00:00:00 GMT-6'

3627

select str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6';

3628

str_to_date('2007-10-09','%Y-%m-%d') <= '2007/10/2000:00:00 GMT-6'

3629

3630

Warnings:

3631

Warning 1292 Truncated incorrect date value: '2007/10/2000:00:00 GMT-6'

3632

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6';

3633

str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-1 00:00:00 GMT-6'

3634

3635

Warnings:

3636

Warning 1292 Truncated incorrect date value: '2007-10-1 00:00:00 GMT-6'

3637

select str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6';

3638

str_to_date('2007-10-01','%Y-%m-%d') = '2007-10-01 x00:00:00 GMT-6'

3639

3640

Warnings:

3641

Warning 1292 Truncated incorrect date value: '2007-10-01 x00:00:00 GMT-6'

3642

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6';

3643

str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:00:00 GMT-6'

3644

3645

Warnings:

3646

Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:00:00 GMT-6'

3647

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6';

3648

str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 00:x00:00 GMT-6'

3649

3650

Warnings:

3651

Warning 1292 Truncated incorrect datetime value: '2007-10-01 00:x00:00 GMT-6'

3652

select str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6';

3653

str_to_date('2007-10-01','%Y-%m-%d %H:%i:%s') = '2007-10-01 x12:34:56 GMT-6'

3654

3655

Warnings:

3656

Warning 1292 Truncated incorrect datetime value: '2007-10-01 x12:34:56 GMT-6'

3657

select str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3658

str_to_date('2007-10-01 12:34:00','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'

3659

3660

Warnings:

3661

Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'

3662

select str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6';

3663

str_to_date('2007-10-01 12:34:56','%Y-%m-%d %H:%i:%s') = '2007-10-01 12:34x:56 GMT-6'

3664

3665

Warnings:

3666

Warning 1292 Truncated incorrect datetime value: '2007-10-01 12:34x:56 GMT-6'

3667

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

3668

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

3669

3670

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

3671

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

3672

3673

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

3674

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

3675

3676

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

3677

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

3678

3679

Warnings:

3680

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

3681

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

3682

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

3683

3684

Warnings:

3685

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

3686

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

3687

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

3688

3689

select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'

3690

and '2007/10/20 00:00:00';

3691

str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00'

3692

and '2007/10/20 00:00:00'

3693

3694

select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20';

3695

str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'

3696

3697

Warnings:

3698

Warning 1292 Truncated incorrect datetime value: ''

3699

select str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20';

3700

str_to_date('','%Y-%m-%d') between '2007/10/01' and '2007/10/20'

3701

3702

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

3703

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

3704

3705

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

3706

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

3707

NULL

3708

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

3709

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

3710

3711

Warnings:

3712

Warning 1292 Truncated incorrect datetime value: ''

3713

select str_to_date('1','%Y-%m-%d') = '1';

3714

str_to_date('1','%Y-%m-%d') = '1'

3715

3716

Warnings:

3717

Warning 1292 Truncated incorrect date value: '1'

3718

select str_to_date('1','%Y-%m-%d') = '1';

3719

str_to_date('1','%Y-%m-%d') = '1'

3720

3721

Warnings:

3722

Warning 1292 Truncated incorrect date value: '1'

3723

select str_to_date('','%Y-%m-%d') = '';

3724

str_to_date('','%Y-%m-%d') = ''

3725

3726

Warnings:

3727

Warning 1292 Truncated incorrect date value: ''

3728

select str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL;

3729

str_to_date('1000-01-01','%Y-%m-%d') between '0000-00-00' and NULL

3730

3731

select str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00';

3732

str_to_date('1000-01-01','%Y-%m-%d') between NULL and '2000-00-00'

3733

3734

select str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL;

3735

str_to_date('1000-01-01','%Y-%m-%d') between NULL and NULL

3736

3587

3737

3588

3738

3589

3739

# Bug#30736: Row Size Too Large Error Creating a Table and

3670

3820

3671

3821

3672

3822

DROP TABLE t1;

3673

CREATE TABLE t1 (a INT);

3674

INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

3675

CREATE TABLE t2 (b INT);

3676

INSERT INTO t2 VALUES (2);

3677

SELECT * FROM t1 WHERE a = 1 + 1;

3678

3679

3680

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a = 1 + 1;

3681

id select_type table type possible_keys key key_len ref rows filtered Extra

3682

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where

3683

Warnings:

3684

Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = <cache>((1 + 1)))

3685

SELECT * FROM t1 HAVING a = 1 + 1;

3686

3687

3688

EXPLAIN EXTENDED SELECT * FROM t1 HAVING a = 1 + 1;

3689

id select_type table type possible_keys key key_len ref rows filtered Extra

3690

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00

3691

Warnings:

3692

Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` having (`test`.`t1`.`a` = <cache>((1 + 1)))

3693

SELECT * FROM t1, t2 WHERE a = b + (1 + 1);

3694

a b

3695

4 2

3696

EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a = b + (1 + 1);

3697

id select_type table type possible_keys key key_len ref rows filtered Extra

3698

1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00

3699

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer

3700

Warnings:

3701

Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`a` = (`test`.`t2`.`b` + <cache>((1 + 1))))

3702

SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;

3703

b a

3704

2 3

3705

EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 1;

3706

id select_type table type possible_keys key key_len ref rows filtered Extra

3707

1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00

3708

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00

3709

Warnings:

3710

Note 1003 select `test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = (`test`.`t2`.`b` + 1))) where 1

3711

EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');

3712

id select_type table type possible_keys key key_len ref rows filtered Extra

3713

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where

3714

Warnings:

3715

Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` > <cache>(unix_timestamp('2009-03-10 00:00:00')))

3716

DROP TABLE t1, t2;

Older »