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

1795

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

1796

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;

1797

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

1798

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

1799

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

1800

1796

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

1801

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;

1802

1798

DROP TABLE t1,t2;

1829

1825

insert into t1 values ();

1830

1826

insert into t1 values ();

1831

1827

# ,

1832

--sorted_result

1833

1828

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

1834

1829

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

1835

1830

# stright_join

1836

1831

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

1837

1832

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

1838

1833

# inner join on

1839

--sorted_result

1840

1834

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

1841

1835

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

1842

1836

# inner join using

1865

1859

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

1866

1860

drop table t1;

1867

1861

1868

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;

1869

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

1870

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;

1871

1865

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

1872

1866

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

1873

1867

drop table t1,t2;

1925

1919

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

1926

1920

1927

1921

1928

CREATE TEMPORARY TABLE t1 (

1922

CREATE TABLE t1 (

1929

1923

i int NOT NULL default '0',

1930

1924

c char(10) NOT NULL default '',

1931

1925

PRIMARY KEY (i),

1973

1967

DROP TABLE t1;

1974

1968

1975

1969

1976

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

1970

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

1977

1971

1978

1972

1979

create table t1 (a int, b int);

1973

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

1980

1974

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

1981

1975

select a-b from t1 order by 1;

1982

1976

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

1983

1977

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

1984

select a - b from t1 order by 1;

1978

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

1985

1979

drop table t1;

1986

1980

1987

1981

1991

1985

create table t1 (a int);

1992

1986

select all all * from t1;

1993

1987

select distinct distinct * from t1;

1994

--error ER_WRONG_USAGE

1988

--error 1221

1995

1989

select all distinct * from t1;

1996

--error ER_WRONG_USAGE

1990

--error 1221

1997

1991

select distinct all * from t1;

1998

1992

drop table t1;

1999

1993

2001

1995

# Test for BUG#10095

2002

1996

2003

1997

CREATE TABLE t1 (

2004

kunde_intern_id int NOT NULL default '0',

2005

kunde_id int NOT NULL default '0',

2006

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',

2007

2001

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

2008

2002

vorname varchar(128) NOT NULL default '',

2009

2003

nachname varchar(128) NOT NULL default '',

2059

2053

2060

2054

2061

2055

2062

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

2056

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

2063

2057

2064

2058

2065

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

2066

INSERT INTO t1 VALUES (0x4000000000000000);

2067

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;

2068

2062

DROP TABLE t1;

2069

2063

2070

2064

2085

2079

# Test for BUG#11700

2086

2080

CREATE TABLE t1 (

2087

2081

acct_id int NOT NULL default '0',

2088

profile_id int default NULL,

2082

profile_id smallint default NULL,

2089

2083

UNIQUE KEY t1$acct_id (acct_id),

2090

2084

KEY t1$profile_id (profile_id)

2091

2085

);

2092

2086

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

2093

2087

2094

2088

CREATE TABLE t2 (

2095

profile_id int default NULL,

2089

profile_id smallint default NULL,

2096

2090

queue_id int default NULL,

2097

2091

seq int default NULL,

2098

2092

KEY t2$queue_id (queue_id)

2105

2099

seq int default NULL,

2106

2100

warn_lvl int default NULL,

2107

2101

crit_lvl int default NULL,

2108

rr1 int NOT NULL default '0',

2102

rr1 tinyint NOT NULL default '0',

2109

2103

rr2 int default NULL,

2110

default_queue int NOT NULL default '0',

2104

default_queue tinyint NOT NULL default '0',

2111

2105

KEY t3$qtype (qtype),

2112

2106

KEY t3$id (id)

2113

2107

);

2138

2132

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

2139

2133

# range analysis.

2140

2134

2141

create table t2 (a int);

2135

create table t2 (a tinyint unsigned);

2142

2136

create index t2i on t2(a);

2143

2137

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

2144

2138

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

2175

2169

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

2176

2170

create table t11 like t1;

2177

2171

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

2178

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

2179

2173

show table status like 't1%';

2180

2174

select 123 as a from t1 where f1 is null;

2181

2175

drop table t1,t11;

2239

2233

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

2240

2234

2241

2235

EXPLAIN SELECT * FROM t1 IGNORE INDEX (idx);

2242

--error ER_KEY_DOES_NOT_EXITS

2236

--error 1176

2243

2237

EXPLAIN SELECT * FROM t1 IGNORE INDEX (a);

2244

--error ER_KEY_DOES_NOT_EXITS

2238

--error 1176

2245

2239

EXPLAIN SELECT * FROM t1 FORCE INDEX (a);

2246

2240

2247

2241

DROP TABLE t1;

2305

2299

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

2306

2300

# 4.1 and 5.0

2307

2301

2308

CREATE TABLE t1 (i BIGINT NOT NULL);

2302

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2309

2303

INSERT INTO t1 VALUES (10);

2310

2304

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

2311

2305

DROP TABLE t1;

2322

2316

# 1. has integer type,

2323

2317

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

2324

2318

# 3. available in every server build

2325

INSERT INTO t1 VALUES(@@server_id);

2326

INSERT INTO t2 VALUES(@@server_id);

2319

INSERT INTO t1 VALUES(@@connect_timeout);

2320

INSERT INTO t2 VALUES(@@connect_timeout);

2327

2321

2328

2322

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

2329

2323

--replace_column 1 X 2 X

2330

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

2331

2325

2332

2326

DROP TABLE t1, t2;

2333

2327

2337

2331

# Test for bug #6474

2338

2332

2339

2333

2340

CREATE TEMPORARY TABLE t1 (

2341

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

2342

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',

2343

2337

F2I4 int NOT NULL default '0'

2344

2338

) ENGINE=MyISAM;

2345

2339

2390

2384

# Bug#9799

2391

2385

2392

2386

2393

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

2387

create table t1 (s1 int) engine=myisam;

2394

2388

insert into t1 values (0);

2395

2389

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

2396

2390

drop table t1;

2481

2475

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

2482

2476

2483

2477

2484

CREATE TABLE t1 (`id` int);

2485

CREATE TABLE t2 (`id` int);

2486

CREATE TABLE t3 (`id` int);

2478

CREATE TABLE t1 (`id` TINYINT);

2479

CREATE TABLE t2 (`id` TINYINT);

2480

CREATE TABLE t3 (`id` TINYINT);

2487

2481

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

2488

2482

INSERT INTO t2 VALUES (2);

2489

2483

INSERT INTO t3 VALUES (3);

2490

--error ER_NON_UNIQ_ERROR

2484

-- error 1052

2491

2485

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

2492

--error ER_NON_UNIQ_ERROR

2486

-- error 1052

2493

2487

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

2494

--error ER_NON_UNIQ_ERROR

2488

-- error 1052

2495

2489

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

2496

--error ER_NON_UNIQ_ERROR

2490

-- error 1052

2497

2491

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

2498

2492

2499

2493

drop table t1, t2, t3;

2528

2522

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

2529

2523

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

2530

2524

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

2531

--error ER_BAD_FIELD_ERROR

2525

-- error 1054

2532

2526

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

2533

2527

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

2534

2528

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

2553

2547

2554

2548

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

2555

2549

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

2556

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

2550

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

2557

2551

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

2558

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

2559

2553

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

2560

2554

2561

2555

# both queries are equivalent

2589

2583

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

2590

2584

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

2591

2585

# should return all records

2592

--error ER_INVALID_DATE_VALUE # Bad date

2593

2586

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

2594

--error ER_INVALID_DATETIME_VALUE # Bad date

2595

2587

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

2596

2588

# should return 4,5

2597

--error ER_INVALID_DATETIME_VALUE # Bad date

2598

2589

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

2599

--error ER_INVALID_DATETIME_VALUE # Bad date

2600

2590

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

2601

--error ER_INVALID_DATETIME_VALUE # Bad date

2591

# should return 1,2,3

2602

2592

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

2603

--error ER_INVALID_DATETIME_VALUE # Bad date

2604

2593

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

2605

2594

drop table t1;

2606

2595

2689

2678

2690

2679

# Bug#15538 unchecked table absense caused server crash.

2691

2680

2692

--error ER_PARSE_ERROR

2681

--error 1064

2693

2682

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

2694

2683

2695

2684

2720

2709

create table t1 (a int);

2721

2710

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

2722

2711

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

2723

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;

2724

2713

analyze table t2;

2725

2714

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

2726

2715

2749

2738

DROP TABLE t1, t2;

2750

2739

2751

2740

2752

# Bug #18895: Now tests for int

2741

# Bug #18895: BIT values cause joins to fail

2753

2742

2754

2743

create table t1 (

2755

a int not null auto_increment primary key,

2756

b int not null,

2757

c int not null

2744

a int unsigned not null auto_increment primary key,

2745

b bit not null,

2746

c bit not null

2758

2747

);

2759

2748

2760

2749

create table t2 (

2761

a int not null auto_increment primary key,

2762

b int not null,

2763

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,

2764

2753

d varchar(50)

2765

2754

);

2766

2755

2986

2975

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

2987

2976

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

2988

2977

2989

# PBXT: does not return a consistent row count

2990

--replace_column 9 #

2991

2978

EXPLAIN SELECT * FROM t1

2992

2979

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

2993

--replace_column 9 #

2994

2980

EXPLAIN SELECT * FROM t1

2995

2981

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

2996

--replace_column 9 #

2997

2982

EXPLAIN SELECT * FROM t1

2998

2983

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

2999

2984

3034

3019

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

3035

3020

3036

3021

DROP TABLE t1,t2;

3037

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

3022

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

3038

3023

3039

3024

3040

create table t1 (a bigint);

3025

create table t1 (a bigint unsigned);

3041

3026

insert into t1 values

3042

(if(1, 92233720368547758, 1)),

3043

(case when 1 then 92233720368547758 else 1 end),

3044

(coalesce(92233720368547758, 1));

3027

(if(1, 9223372036854775808, 1)),

3028

(case when 1 then 9223372036854775808 else 1 end),

3029

(coalesce(9223372036854775808, 1));

3045

3030

select * from t1;

3046

3031

drop table t1;

3047

3032

create table t1 select

3052

3037

drop table t1;

3053

3038

# Ensure we handle big values properly

3054

3039

select

3055

if(1, 1111111111111111111, 1) i,

3056

case when 1 then 1111111111111111111 else 1 end c,

3057

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;

3058

3043

3059

3044

3060

3045

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

3267

3252

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

3268

3253

3269

3254

3270

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

3271

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,

3272

3257

c22 INT DEFAULT NULL,

3273

3258

KEY(c21, c22));

3274

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3259

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

3275

3260

c32 INT DEFAULT NULL,

3276

3261

c33 INT NOT NULL,

3277

c34 INT DEFAULT 0,

3262

c34 INT UNSIGNED DEFAULT 0,

3278

3263

KEY (c33, c34, c32));

3279

3264

3280

3265

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

3281

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;

3282

3267

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

3283

3268

(2, 2, 0, 0),

3284

3269

(3, 3, 1, 0),

3303

3288

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

3304

3289

# than comparison

3305

3290

3306

3307

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

3308

3291

3309

3292

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

3310

3293

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

3311

3294

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

3312

3295

3313

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

3314

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

3315

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

3316

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

3317

3300

3318

3301

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

3319

3302

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

3320

3303

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

3321

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

3322

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

3323

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

3324

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

3325

3308

# no time at all:

3326

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

3327

3310

# partial time:

3328

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

3329

3312

# fail, different second part:

3330

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

3331

3314

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

3332

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

3333

3316

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

3334

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

3335

3318

# succeed:

3336

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

3337

3320

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

3338

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

3339

3322

# invalid date (Feb 30) succeeds

3340

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

3341

3324

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

3342

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

3343

3326

# 0-day, succeed

3344

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

3345

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

3346

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

3347

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

3348

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

3349

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

3350

#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') = '';

3351

3334

3352

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

3353

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

3354

#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') = '';

3355

3338

3356

3339

# these three should work!

3357

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

3358

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

3359

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

3360

3343

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

3361

3344

3362

3345

--echo

3456

3439

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

3457

3440

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

3458

3441

DROP TABLE t1;

3459

3460

3461

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

3462

3463

CREATE TABLE t1 (a INT);

3464

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

3465

CREATE TABLE t2 (b INT);

3466

INSERT INTO t2 VALUES (2);

3467

SELECT * FROM t1 WHERE a = 1 + 1;

3468

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

3469

SELECT * FROM t1 HAVING a = 1 + 1;

3470

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

3471

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

3472

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

3473

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

3474

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

3475

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

3476

3477

DROP TABLE t1, t2;

Older »