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

1788

CREATE TABLE t1 (gvid int unsigned default NULL, hmid int unsigned default NULL, volid int unsigned default NULL, mmid int unsigned default NULL, hdid int unsigned default NULL, fsid int unsigned default NULL, ctid int unsigned default NULL, dtid int unsigned default NULL, cost int unsigned default NULL, performance int unsigned default NULL, serialnumber bigint unsigned default NULL, monitored tinyint unsigned default '1', removed tinyint unsigned default '0', target tinyint unsigned 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;

1794

1789

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

1795

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;

1790

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

1796

1791

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

1797

--error ER_INVALID_DATETIME_VALUE # bad datetime

1792

# Disable PS becasue we get more warnings from PS than from normal execution

1793

--disable_ps_protocol

1798

1794

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;

1795

--enable_ps_protocol

1799

1796

# Testing the same select with NULL's instead of invalid datetime values

1800

1797

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;

1801

1798

DROP TABLE t1,t2;

1828

1825

insert into t1 values ();

1829

1826

insert into t1 values ();

1830

1827

# ,

1831

--sorted_result

1832

1828

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

1833

1829

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

1834

1830

# stright_join

1835

1831

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

1836

1832

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

1837

1833

# inner join on

1838

--sorted_result

1839

1834

select * from (t1 as t2 left join t1 as t3 using (a)) inner join t1 on t1.a>1;

1840

1835

select * from t1 inner join (t1 as t2 left join t1 as t3 using (a)) on t1.a>1;

1841

1836

# inner join using

1864

1859

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

1865

1860

drop table t1;

1866

1861

1867

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;

1862

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;

1868

1863

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

1869

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

1864

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

1870

1865

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

1871

1866

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

1872

1867

drop table t1,t2;

1924

1919

# Covering index is mentioned in EXPLAIN output for const tables (bug #5333)

1925

1920

1926

1921

1927

CREATE TEMPORARY TABLE t1 (

1922

CREATE TABLE t1 (

1928

1923

i int NOT NULL default '0',

1929

1924

c char(10) NOT NULL default '',

1930

1925

PRIMARY KEY (i),

1972

1967

DROP TABLE t1;

1973

1968

1974

1969

1975

# Bug#7425 inconsistent sort order on columns result of substraction

1970

# Bug#7425 inconsistent sort order on unsigned columns result of substraction

1976

1971

1977

1972

1978

create table t1 (a int, b int);

1973

create table t1 (a int unsigned, b int unsigned);

1979

1974

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

1980

1975

select a-b from t1 order by 1;

1981

1976

select a-b , (a-b < 0) from t1 order by 1;

1982

1977

select a-b as d, (a-b >= 0), b from t1 group by b having d >= 0;

1983

select a - b from t1 order by 1;

1978

select cast((a - b) as unsigned) from t1 order by 1;

1984

1979

drop table t1;

1985

1980

1986

1981

1990

1985

create table t1 (a int);

1991

1986

select all all * from t1;

1992

1987

select distinct distinct * from t1;

1993

--error ER_WRONG_USAGE

1988

--error 1221

1994

1989

select all distinct * from t1;

1995

--error ER_WRONG_USAGE

1990

--error 1221

1996

1991

select distinct all * from t1;

1997

1992

drop table t1;

1998

1993

2000

1995

# Test for BUG#10095

2001

1996

2002

1997

CREATE TABLE t1 (

2003

kunde_intern_id int NOT NULL default '0',

2004

kunde_id int NOT NULL default '0',

2005

FK_firma_id int NOT NULL default '0',

1998

kunde_intern_id int unsigned NOT NULL default '0',

1999

kunde_id int unsigned NOT NULL default '0',

2000

FK_firma_id int unsigned NOT NULL default '0',

2006

2001

aktuell enum('Ja','Nein') NOT NULL default 'Ja',

2007

2002

vorname varchar(128) NOT NULL default '',

2008

2003

nachname varchar(128) NOT NULL default '',

2058

2053

2059

2054

2060

2055

2061

# Test for Bug#8009, SELECT failed on bigint when using HEX

2056

# Test for Bug#8009, SELECT failed on bigint unsigned when using HEX

2062

2057

2063

2058

2064

CREATE TABLE t1 (b BIGINT NOT NULL, PRIMARY KEY (b));

2065

INSERT INTO t1 VALUES (0x4000000000000000);

2066

SELECT b FROM t1 WHERE b=0x4000000000000000;

2059

CREATE TABLE t1 (b BIGINT UNSIGNED NOT NULL, PRIMARY KEY (b));

2060

INSERT INTO t1 VALUES (0x8000000000000000);

2061

SELECT b FROM t1 WHERE b=0x8000000000000000;

2067

2062

DROP TABLE t1;

2068

2063

2069

2064

2084

2079

# Test for BUG#11700

2085

2080

CREATE TABLE t1 (

2086

2081

acct_id int NOT NULL default '0',

2087

profile_id int default NULL,

2082

profile_id smallint default NULL,

2088

2083

UNIQUE KEY t1$acct_id (acct_id),

2089

2084

KEY t1$profile_id (profile_id)

2090

2085

);

2091

2086

INSERT INTO t1 VALUES (132,17),(133,18);

2092

2087

2093

2088

CREATE TABLE t2 (

2094

profile_id int default NULL,

2089

profile_id smallint default NULL,

2095

2090

queue_id int default NULL,

2096

2091

seq int default NULL,

2097

2092

KEY t2$queue_id (queue_id)

2104

2099

seq int default NULL,

2105

2100

warn_lvl int default NULL,

2106

2101

crit_lvl int default NULL,

2107

rr1 int NOT NULL default '0',

2102

rr1 tinyint NOT NULL default '0',

2108

2103

rr2 int default NULL,

2109

default_queue int NOT NULL default '0',

2104

default_queue tinyint NOT NULL default '0',

2110

2105

KEY t3$qtype (qtype),

2111

2106

KEY t3$id (id)

2112

2107

);

2137

2132

# Bug #11521 Negative integer keys incorrectly substituted for 0 during

2138

2133

# range analysis.

2139

2134

2140

create table t2 (a int);

2135

create table t2 (a tinyint unsigned);

2141

2136

create index t2i on t2(a);

2142

2137

insert into t2 values (0), (254), (255);

2143

2138

#explain select * from t2 where a > -1;

2174

2169

create table t1 (f1 int not null auto_increment primary key, f2 varchar(10));

2175

2170

create table t11 like t1;

2176

2171

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

2177

--replace_column 1 # 6 # 7 # 8 # 9 # 10 #

2172

--replace_column 7 X 8 X 9 X 10 X 11 X 12 X 13 X 14 X

2178

2173

show table status like 't1%';

2179

2174

select 123 as a from t1 where f1 is null;

2180

2175

drop table t1,t11;

2238

2233

INSERT INTO t1 VALUES (2), (3), (1);

2239

2234

2240

2235

EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);

2241

--error ER_KEY_DOES_NOT_EXITS

2236

--error 1176

2242

2237

EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);

2243

--error ER_KEY_DOES_NOT_EXITS

2238

--error 1176

2244

2239

EXPLAIN SELECT * FROM t1 FORCE INDEX (a);

2245

2240

2246

2241

DROP TABLE t1;

2304

2299

# This test is here only to make sure that behavior is not changed in

2305

2300

# 4.1 and 5.0

2306

2301

2307

CREATE TABLE t1 (i BIGINT NOT NULL);

2302

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2308

2303

INSERT INTO t1 VALUES (10);

2309

2304

SELECT i='1e+01',i=1e+01, i in (1e+01,1e+01), i in ('1e+01','1e+01') FROM t1;

2310

2305

DROP TABLE t1;

2321

2316

# 1. has integer type,

2322

2317

# 2. can be used with the "@@name" syntax

2323

2318

# 3. available in every server build

2324

INSERT INTO t1 VALUES(@@server_id);

2325

INSERT INTO t2 VALUES(@@server_id);

2319

INSERT INTO t1 VALUES(@@connect_timeout);

2320

INSERT INTO t2 VALUES(@@connect_timeout);

2326

2321

2327

2322

# We only need to ensure 1 row is returned to validate the results

2328

2323

--replace_column 1 X 2 X

2329

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

2324

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

2330

2325

2331

2326

DROP TABLE t1, t2;

2332

2327

2336

2331

# Test for bug #6474

2337

2332

2338

2333

2339

CREATE TEMPORARY TABLE t1 (

2340

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

2341

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

2334

CREATE TABLE t1 (

2335

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

2336

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

2342

2337

F2I4 int NOT NULL default '0'

2343

2338

) ENGINE=MyISAM;

2344

2339

2389

2384

# Bug#9799

2390

2385

2391

2386

2392

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

2387

create table t1 (s1 int) engine=myisam;

2393

2388

insert into t1 values (0);

2394

2389

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

2395

2390

drop table t1;

2480

2475

# for base tables, search all nested join operands of natural joins.

2481

2476

2482

2477

2483

CREATE TABLE t1 (`id` int);

2484

CREATE TABLE t2 (`id` int);

2485

CREATE TABLE t3 (`id` int);

2478

CREATE TABLE t1 (`id` TINYINT);

2479

CREATE TABLE t2 (`id` TINYINT);

2480

CREATE TABLE t3 (`id` TINYINT);

2486

2481

INSERT INTO t1 VALUES (1),(2),(3);

2487

2482

INSERT INTO t2 VALUES (2);

2488

2483

INSERT INTO t3 VALUES (3);

2489

--error ER_NON_UNIQ_ERROR

2484

-- error 1052

2490

2485

SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);

2491

--error ER_NON_UNIQ_ERROR

2486

-- error 1052

2492

2487

SELECT t1.id,t3.id FROM t1 JOIN t2 ON (t2.notacolumn=t1.id) LEFT JOIN t3 USING (id);

2493

--error ER_NON_UNIQ_ERROR

2488

-- error 1052

2494

2489

SELECT id,t3.id FROM t1 JOIN t2 ON (t2.id=t1.id) LEFT JOIN t3 USING (id);

2495

--error ER_NON_UNIQ_ERROR

2490

-- error 1052

2496

2491

SELECT id,t3.id FROM (t1 JOIN t2 ON (t2.id=t1.id)) LEFT JOIN t3 USING (id);

2497

2492

2498

2493

drop table t1, t2, t3;

2527

2522

select * from t1 join t2 join t3 on (t2.b = t3.b and t1.a = t3.a);

2528

2523

# Notice that ',' has lower priority than 'join', thus we have that:

2529

2524

# t1, t2 join t3 <==> t1, (t2 join t3).

2530

--error ER_BAD_FIELD_ERROR

2525

-- error 1054

2531

2526

select * from t1, t2 join t3 on (t2.b = t3.b and t1.a = t3.a);

2532

2527

select * from t1 join t2 join t3 join t4 on (t1.a = t4.c and t2.b = t4.c);

2533

2528

select * from t1 join t2 join t4 using (c);

2552

2547

2553

2548

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

2554

2549

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

2555

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

2550

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

2556

2551

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

2557

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

2552

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

2558

2553

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

2559

2554

2560

2555

# both queries are equivalent

2588

2583

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

2589

2584

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

2590

2585

# should return all records

2591

--error ER_INVALID_DATE_VALUE # Bad date

2592

2586

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

2593

--error ER_INVALID_DATETIME_VALUE # Bad date

2594

2587

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

2595

2588

# should return 4,5

2596

--error ER_INVALID_DATETIME_VALUE # Bad date

2597

2589

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

2598

--error ER_INVALID_DATETIME_VALUE # Bad date

2599

2590

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

2600

--error ER_INVALID_DATETIME_VALUE # Bad date

2591

# should return 1,2,3

2601

2592

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

2602

--error ER_INVALID_DATETIME_VALUE # Bad date

2603

2593

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

2604

2594

drop table t1;

2605

2595

2688

2678

2689

2679

# Bug#15538 unchecked table absense caused server crash.

2690

2680

2691

--error ER_PARSE_ERROR

2681

--error 1064

2692

2682

select * from (select * left join t on f1=f2) tt;

2693

2683

2694

2684

2719

2709

create table t1 (a int);

2720

2710

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

2721

2711

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

2722

insert into t2 select A.a, B.a, C.a, C.a from t1 A CROSS JOIN t1 B CROSS JOIN t1 C;

2712

insert into t2 select A.a, B.a, C.a, C.a from t1 A, t1 B, t1 C;

2723

2713

analyze table t2;

2724

2714

select 'In next EXPLAIN, B.rows must be exactly 10:' Z;

2725

2715

2748

2738

DROP TABLE t1, t2;

2749

2739

2750

2740

2751

# Bug #18895: Now tests for int

2741

# Bug #18895: BIT values cause joins to fail

2752

2742

2753

2743

create table t1 (

2754

a int not null auto_increment primary key,

2755

b int not null,

2756

c int not null

2744

a int unsigned not null auto_increment primary key,

2745

b bit not null,

2746

c bit not null

2757

2747

);

2758

2748

2759

2749

create table t2 (

2760

a int not null auto_increment primary key,

2761

b int not null,

2762

c int not null,

2750

a int unsigned not null auto_increment primary key,

2751

b bit not null,

2752

c int unsigned not null,

2763

2753

d varchar(50)

2764

2754

);

2765

2755

2985

2975

SELECT COUNT(*) FROM t1 WHERE ID1_with_null IS NULL AND ID2_with_null IS NULL;

2986

2976

SELECT COUNT(*) FROM t1 WHERE ID_better=1;

2987

2977

2988

# PBXT: does not return a consistent row count

2989

--replace_column 9 #

2990

2978

EXPLAIN SELECT * FROM t1

2991

2979

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null=3 ;

2992

--replace_column 9 #

2993

2980

EXPLAIN SELECT * FROM t1

2994

2981

WHERE ID_better=1 AND ID1_with_null=3 AND ID2_with_null=3 IS NULL ;

2995

--replace_column 9 #

2996

2982

EXPLAIN SELECT * FROM t1

2997

2983

WHERE ID_better=1 AND ID1_with_null IS NULL AND ID2_with_null IS NULL;

2998

2984

3033

3019

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

3034

3020

3035

3021

DROP TABLE t1,t2;

3036

# Bug #22026: Warning when using IF statement and large bigint

3022

# Bug #22026: Warning when using IF statement and large unsigned bigint

3037

3023

3038

3024

3039

create table t1 (a bigint);

3025

create table t1 (a bigint unsigned);

3040

3026

insert into t1 values

3041

(if(1, 92233720368547758, 1)),

3042

(case when 1 then 92233720368547758 else 1 end),

3043

(coalesce(92233720368547758, 1));

3027

(if(1, 9223372036854775808, 1)),

3028

(case when 1 then 9223372036854775808 else 1 end),

3029

(coalesce(9223372036854775808, 1));

3044

3030

select * from t1;

3045

3031

drop table t1;

3046

3032

create table t1 select

3051

3037

drop table t1;

3052

3038

# Ensure we handle big values properly

3053

3039

select

3054

if(1, 1111111111111111111, 1) i,

3055

case when 1 then 1111111111111111111 else 1 end c,

3056

coalesce(1111111111111111111, 1) co;

3040

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

3041

case when 1 then cast(1111111111111111111 as unsigned) else 1 end c,

3042

coalesce(cast(1111111111111111111 as unsigned), 1) co;

3057

3043

3058

3044

3059

3045

# Bug #22971: indexes on text columns are ignored for ref accesses

3266

3252

# Bug #30666: Incorrect order when using range conditions on 2 tables or more

3267

3253

3268

3254

3269

CREATE TABLE t1 (c11 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

3270

CREATE TABLE t2 (c21 INT NOT NULL,

3255

CREATE TABLE t1 (c11 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

3256

CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,

3271

3257

c22 INT DEFAULT NULL,

3272

3258

KEY(c21, c22));

3273

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3259

CREATE TABLE t3 (c31 INT UNSIGNED NOT NULL DEFAULT 0,

3274

3260

c32 INT DEFAULT NULL,

3275

3261

c33 INT NOT NULL,

3276

c34 INT DEFAULT 0,

3262

c34 INT UNSIGNED DEFAULT 0,

3277

3263

KEY (c33, c34, c32));

3278

3264

3279

3265

INSERT INTO t1 values (),(),(),(),();

3280

INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a CROSS JOIN t1 b;

3266

INSERT INTO t2 SELECT a.c11, b.c11 FROM t1 a, t1 b;

3281

3267

INSERT INTO t3 VALUES (1, 1, 1, 0),

3282

3268

(2, 2, 0, 0),

3283

3269

(3, 3, 1, 0),

3302

3288

# Bug#31800: Date comparison fails with timezone and slashes for greater

3303

3289

# than comparison

3304

3290

3305

3306

# @TODO Commenting all str_to_date out for now...should move to a plugin.

3307

3291

3308

3292

# On DATETIME-like literals with trailing garbage, BETWEEN fudged in a

3309

3293

# DATETIME comparator, while greater/less-than used bin-string comparisons.

3310

3294

# Should correctly be compared as DATE or DATETIME, but throw a warning:

3311

3295

3312

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

3313

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

3314

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

3315

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

3296

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

3297

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

3298

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

3299

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

3316

3300

3317

3301

# We have all we need -- and trailing garbage:

3318

3302

# (leaving out a leading zero in first example to prove it's a

3319

3303

# value-comparison, not a string-comparison!)

3320

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

3321

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

3322

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

3323

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

3304

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

3305

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

3306

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

3307

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

3324

3308

# no time at all:

3325

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

3309

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

3326

3310

# partial time:

3327

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

3311

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

3328

3312

# fail, different second part:

3329

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

3313

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

3330

3314

# correct syntax, no trailing nonsense -- this one must throw no warning:

3331

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

3315

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

3332

3316

# no warning, but failure (different hour parts):

3333

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

3317

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

3334

3318

# succeed:

3335

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

3319

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

3336

3320

# succeed, but warn for "trailing garbage" (":34"):

3337

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

3321

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

3338

3322

# invalid date (Feb 30) succeeds

3339

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

3323

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

3340

3324

# 0-day for both, just works in default SQL mode.

3341

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

3325

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

3342

3326

# 0-day, succeed

3343

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

3344

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

3345

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

3346

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

3347

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

3348

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

3349

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

3327

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

3328

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

3329

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

3330

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

3331

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

3332

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

3333

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

3350

3334

3351

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

3352

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

3353

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

3335

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

3336

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

3337

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

3354

3338

3355

3339

# these three should work!

3356

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

3357

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

3358

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

3340

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

3341

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

3342

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

3359

3343

###########################################################################

3360

3344

3361

3345

--echo

3455

3439

SELECT a FROM t1 ORDER BY a LIMIT 2,4294967296;

3456

3440

SELECT a FROM t1 ORDER BY a LIMIT 2,4294967297;

3457

3441

DROP TABLE t1;

3458

3459

3460

# MySQL Bug#33546: Slowdown on re-evaluation of constant expressions.

3461

3462

CREATE TABLE t1 (a INT);

3463

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

3464

CREATE TABLE t2 (b INT);

3465

INSERT INTO t2 VALUES (2);

3466

SELECT * FROM t1 WHERE a = 1 + 1;

3467

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

3468

SELECT * FROM t1 HAVING a = 1 + 1;

3469

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

3470

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

3471

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

3472

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

3473

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

3474

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

3475

3476

DROP TABLE t1, t2;

Older »