~drizzle-trunk/drizzle/development

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

1793

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;

1789

1794

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

1790

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

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;

1791

1796

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

1792

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

1793

--disable_ps_protocol

1797

--error 1686 # bad datetime

1794

1798

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

1796

1799

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

1797

1800

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;

1798

1801

DROP TABLE t1,t2;

1801

1804

# Test of bigint comparision

1802

1805

1803

1806

1804

create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0');

1807

create table t1 ( A_Id bigint NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint NOT NULL default '0', A_UpdateSerial int NOT NULL default '0', other_types bigint NOT NULL default '0', wss_type bigint NOT NULL default '0');

1805

1808

INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093);

1806

1809

select wss_type from t1 where wss_type ='102935229216544106';

1807

1810

select wss_type from t1 where wss_type ='102935229216544105';

1831

1834

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

1832

1835

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

1833

1836

# inner join on

1837

--sorted_result

1834

1838

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

1835

1839

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

1836

1840

# inner join using

1859

1863

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

1860

1864

drop table t1;

1861

1865

1862

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

1866

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;

1863

1867

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

1864

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

1868

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

1865

1869

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

1866

1870

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

1867

1871

drop table t1,t2;

1919

1923

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

1920

1924

1921

1925

1922

CREATE TABLE t1 (

1923

i int(11) NOT NULL default '0',

1926

CREATE TEMPORARY TABLE t1 (

1927

i int NOT NULL default '0',

1924

1928

c char(10) NOT NULL default '',

1925

1929

PRIMARY KEY (i),

1926

1930

UNIQUE KEY c (c)

1967

1971

DROP TABLE t1;

1968

1972

1969

1973

1970

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

1974

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

1971

1975

1972

1976

1973

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

1977

create table t1 (a int, b int);

1974

1978

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

1975

1979

select a-b from t1 order by 1;

1976

1980

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

1977

1981

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

1978

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

1982

select a - b from t1 order by 1;

1979

1983

drop table t1;

1980

1984

1981

1985

1982

1986

1983

1987

# Bug#8733 server accepts malformed query (multiply mentioned distinct)

1984

1988

1985

create table t1 (a int(11));

1989

create table t1 (a int);

1986

1990

select all all * from t1;

1987

1991

select distinct distinct * from t1;

1988

1992

--error 1221

1995

1999

# Test for BUG#10095

1996

2000

1997

2001

CREATE TABLE t1 (

1998

kunde_intern_id int(10) unsigned NOT NULL default '0',

1999

kunde_id int(10) unsigned NOT NULL default '0',

2000

FK_firma_id int(10) unsigned NOT NULL default '0',

2002

kunde_intern_id int NOT NULL default '0',

2003

kunde_id int NOT NULL default '0',

2004

FK_firma_id int NOT NULL default '0',

2001

2005

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

2002

2006

vorname varchar(128) NOT NULL default '',

2003

2007

nachname varchar(128) NOT NULL default '',

2053

2057

2054

2058

2055

2059

2056

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

2060

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

2057

2061

2058

2062

2059

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

2060

INSERT INTO t1 VALUES (0x8000000000000000);

2061

SELECT b FROM t1 WHERE b=0x8000000000000000;

2063

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

2064

INSERT INTO t1 VALUES (0x4000000000000000);

2065

SELECT b FROM t1 WHERE b=0x4000000000000000;

2062

2066

DROP TABLE t1;

2063

2067

2064

2068

2065

2069

# IN with outer join condition (BUG#9393)

2066

2070

2067

CREATE TABLE `t1` ( `gid` int(11) default NULL, `uid` int(11) default NULL);

2071

CREATE TABLE `t1` ( `gid` int default NULL, `uid` int default NULL);

2068

2072

2069

CREATE TABLE `t2` ( `ident` int(11) default NULL, `level` char(16) default NULL);

2073

CREATE TABLE `t2` ( `ident` int default NULL, `level` char(16) default NULL);

2070

2074

INSERT INTO `t2` VALUES (0,'READ');

2071

2075

2072

CREATE TABLE `t3` ( `id` int(11) default NULL, `name` char(16) default NULL);

2076

CREATE TABLE `t3` ( `id` int default NULL, `name` char(16) default NULL);

2073

2077

INSERT INTO `t3` VALUES (1,'fs');

2074

2078

2075

2079

select * from t3 left join t1 on t3.id = t1.uid, t2 where t2.ident in (0, t1.gid, t3.id, 0);

2078

2082

2079

2083

# Test for BUG#11700

2080

2084

CREATE TABLE t1 (

2081

acct_id int(11) NOT NULL default '0',

2082

profile_id smallint(6) default NULL,

2085

acct_id int NOT NULL default '0',

2086

profile_id int default NULL,

2083

2087

UNIQUE KEY t1$acct_id (acct_id),

2084

2088

KEY t1$profile_id (profile_id)

2085

2089

);

2086

2090

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

2087

2091

2088

2092

CREATE TABLE t2 (

2089

profile_id smallint(6) default NULL,

2090

queue_id int(11) default NULL,

2091

seq int(11) default NULL,

2093

profile_id int default NULL,

2094

queue_id int default NULL,

2095

seq int default NULL,

2092

2096

KEY t2$queue_id (queue_id)

2093

2097

);

2094

2098

INSERT INTO t2 VALUES (17,31,4),(17,30,3),(17,36,2),(17,37,1);

2095

2099

2096

2100

CREATE TABLE t3 (

2097

id int(11) NOT NULL default '0',

2098

qtype int(11) default NULL,

2099

seq int(11) default NULL,

2100

warn_lvl int(11) default NULL,

2101

crit_lvl int(11) default NULL,

2102

rr1 tinyint(4) NOT NULL default '0',

2103

rr2 int(11) default NULL,

2104

default_queue tinyint(4) NOT NULL default '0',

2101

id int NOT NULL default '0',

2102

qtype int default NULL,

2103

seq int default NULL,

2104

warn_lvl int default NULL,

2105

crit_lvl int default NULL,

2106

rr1 int NOT NULL default '0',

2107

rr2 int default NULL,

2108

default_queue int NOT NULL default '0',

2105

2109

KEY t3$qtype (qtype),

2106

2110

KEY t3$id (id)

2107

2111

);

2132

2136

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

2133

2137

# range analysis.

2134

2138

2135

create table t2 (a tinyint unsigned);

2139

create table t2 (a int);

2136

2140

create index t2i on t2(a);

2137

2141

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

2138

2142

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

2169

2173

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

2170

2174

create table t11 like t1;

2171

2175

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

2172

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

2176

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

2173

2177

show table status like 't1%';

2174

2178

select 123 as a from t1 where f1 is null;

2175

2179

drop table t1,t11;

2299

2303

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

2300

2304

# 4.1 and 5.0

2301

2305

2302

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2306

CREATE TABLE t1 (i BIGINT NOT NULL);

2303

2307

INSERT INTO t1 VALUES (10);

2304

2308

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

2305

2309

DROP TABLE t1;

2316

2320

# 1. has integer type,

2317

2321

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

2318

2322

# 3. available in every server build

2319

INSERT INTO t1 VALUES(@@connect_timeout);

2320

INSERT INTO t2 VALUES(@@connect_timeout);

2323

INSERT INTO t1 VALUES(@@server_id);

2324

INSERT INTO t2 VALUES(@@server_id);

2321

2325

2322

2326

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

2323

2327

--replace_column 1 X 2 X

2324

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

2328

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

2325

2329

2326

2330

DROP TABLE t1, t2;

2327

2331

2335

# Test for bug #6474

2332

2336

2333

2337

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

2337

F2I4 int(11) NOT NULL default '0'

2338

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2338

CREATE TEMPORARY TABLE t1 (

2339

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

2340

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

2341

F2I4 int NOT NULL default '0'

2342

) ENGINE=MyISAM;

2339

2343

2340

2344

INSERT INTO t1 VALUES

2341

2345

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

2384

2388

# Bug#9799

2385

2389

2386

2390

2387

create table t1 (s1 int) engine=myisam;

2391

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

2388

2392

insert into t1 values (0);

2389

2393

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

2390

2394

drop table t1;

2424

2428

2425

2429

# Bug #11398 Bug in field_conv() results in wrong result of join with index

2426

2430

2427

create table t1 (f1 varchar(6) default NULL, f2 int(6) primary key not null);

2431

create table t1 (f1 varchar(6) default NULL, f2 int primary key not null);

2428

2432

create table t2 (f3 varchar(5) not null, f4 varchar(5) not null, UNIQUE KEY UKEY (f3,f4));

2429

2433

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

2430

2434

insert into t2 values (" 2", " one "),(" 2", " two ");

2446

2450

# incorrect transformation to join ... on.

2447

2451

2448

2452

2449

create table t1 (a int(10), t1_val int(10));

2450

create table t2 (b int(10), t2_val int(10));

2451

create table t3 (a int(10), b int(10));

2453

create table t1 (a int, t1_val int);

2454

create table t2 (b int, t2_val int);

2455

create table t3 (a int, b int);

2452

2456

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

2453

2457

insert into t2 values (1,1),(2,2),(3,3);

2454

2458

insert into t3 values (1,1),(2,1),(3,1),(4,1);

2475

2479

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

2476

2480

2477

2481

2478

CREATE TABLE t1 (`id` TINYINT);

2479

CREATE TABLE t2 (`id` TINYINT);

2480

CREATE TABLE t3 (`id` TINYINT);

2482

CREATE TABLE t1 (`id` int);

2483

CREATE TABLE t2 (`id` int);

2484

CREATE TABLE t3 (`id` int);

2481

2485

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

2482

2486

INSERT INTO t2 VALUES (2);

2483

2487

INSERT INTO t3 VALUES (3);

2496

2500

# Bug #13067 JOIN xxx USING is case sensitive

2497

2501

2498

2502

2499

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

2500

create table t2 (a int(10),b int(10));

2503

create table t1 (a int,b int);

2504

create table t2 (a int,b int);

2501

2505

insert into t1 values (1,10),(2,20),(3,30);

2502

2506

insert into t2 values (1,10);

2503

2507

# both queries should produce the same result

2545

2549

# nested right join.

2546

2550

2547

2551

2548

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

2552

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

2549

2553

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

2550

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

2554

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

2551

2555

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

2552

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

2556

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

2553

2557

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

2554

2558

2555

2559

# both queries are equivalent

2583

2587

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

2584

2588

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

2585

2589

# should return all records

2590

--error 1686 # Bad date

2586

2591

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

2592

--error 1686 # Bad date

2587

2593

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

2588

2594

# should return 4,5

2595

--error 1686 # Bad date

2589

2596

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

2597

--error 1686 # Bad date

2590

2598

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

2591

# should return 1,2,3

2599

--error 1686 # Bad date

2592

2600

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

2601

--error 1686 # Bad date

2593

2602

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

2594

2603

drop table t1;

2595

2604

2704

2713

2705

2714

DROP TABLE t1,t2;

2706

2715

2707

2708

# Bug#18712: Truncation problem (needs just documenting and test

2709

# cases to prevent fixing this accidently. It is intended behaviour)

2710

2711

2712

CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);

2713

INSERT t1 SET i = 0;

2714

UPDATE t1 SET i = -1;

2715

SELECT * FROM t1;

2716

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

2717

SELECT * FROM t1;

2718

UPDATE t1 SET i = i - 1;

2719

SELECT * FROM t1;

2720

DROP TABLE t1;

2721

2722

2716

# BUG#17379

2723

2717

2724

2718

create table t1 (a int);

2753

2747

DROP TABLE t1, t2;

2754

2748

2755

2749

2756

# Bug #18895: BIT values cause joins to fail

2750

# Bug #18895: Now tests for int

2757

2751

2758

2752

create table t1 (

2759

a int unsigned not null auto_increment primary key,

2760

b bit not null,

2761

c bit not null

2753

a int not null auto_increment primary key,

2754

b int not null,

2755

c int not null

2762

2756

);

2763

2757

2764

2758

create table t2 (

2765

a int unsigned not null auto_increment primary key,

2766

b bit not null,

2767

c int unsigned not null,

2759

a int not null auto_increment primary key,

2760

b int not null,

2761

c int not null,

2768

2762

d varchar(50)

2769

2763

);

2770

2764

2990

2984

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

2991

2985

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

2992

2986

2987

# PBXT: does not return a consistent row count

2988

--replace_column 9 #

2993

2989

EXPLAIN SELECT * FROM t1

2994

2990

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

2991

--replace_column 9 #

2995

2992

EXPLAIN SELECT * FROM t1

2996

2993

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

2994

--replace_column 9 #

2997

2995

EXPLAIN SELECT * FROM t1

2998

2996

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

2999

2997

3034

3032

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

3035

3033

3036

3034

DROP TABLE t1,t2;

3037

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

3035

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

3038

3036

3039

3037

3040

create table t1 (a bigint unsigned);

3038

create table t1 (a bigint);

3041

3039

insert into t1 values

3042

(if(1, 9223372036854775808, 1)),

3043

(case when 1 then 9223372036854775808 else 1 end),

3044

(coalesce(9223372036854775808, 1));

3040

(if(1, 92233720368547758, 1)),

3041

(case when 1 then 92233720368547758 else 1 end),

3042

(coalesce(92233720368547758, 1));

3045

3043

select * from t1;

3046

3044

drop table t1;

3047

3045

create table t1 select

3052

3050

drop table t1;

3053

3051

# Ensure we handle big values properly

3054

3052

select

3055

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

3056

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

3057

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

3053

if(1, 1111111111111111111, 1) i,

3054

case when 1 then 1111111111111111111 else 1 end c,

3055

coalesce(1111111111111111111, 1) co;

3058

3056

3059

3057

3060

3058

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

3198

3196

3199

3197

3200

3198

CREATE TABLE t1 (

3201

c1 int(11) NOT NULL AUTO_INCREMENT,

3199

c1 int NOT NULL AUTO_INCREMENT,

3202

3200

c2 varchar(1000) DEFAULT NULL,

3203

c3 bigint(20) DEFAULT NULL,

3204

c4 bigint(20) DEFAULT NULL,

3201

c3 bigint DEFAULT NULL,

3202

c4 bigint DEFAULT NULL,

3205

3203

PRIMARY KEY (c1)

3206

3204

);

3207

3205

3267

3265

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

3268

3266

3269

3267

3270

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

3271

CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,

3268

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

3269

CREATE TABLE t2 (c21 INT NOT NULL,

3272

3270

c22 INT DEFAULT NULL,

3273

3271

KEY(c21, c22));

3274

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

3272

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3275

3273

c32 INT DEFAULT NULL,

3276

3274

c33 INT NOT NULL,

3277

c34 INT UNSIGNED DEFAULT 0,

3275

c34 INT DEFAULT 0,

3278

3276

KEY (c33, c34, c32));

3279

3277

3280

3278

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

3303

3301

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

3304

3302

# than comparison

3305

3303

3306

3304

3305

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

3306

3307

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

3308

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

3309

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

3310

3311

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

3312

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

3313

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

3314

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

3311

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

3312

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

3313

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

3314

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

3315

3316

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

3317

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

3318

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

3319

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

3320

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

3321

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

3322

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

3319

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

3320

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

3321

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

3322

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

3323

# no time at all:

3324

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

3324

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

3325

# partial time:

3326

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

3326

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

3327

# fail, different second part:

3328

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

3328

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

3329

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

3330

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

3330

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

3331

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

3332

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

3332

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

3333

# succeed:

3334

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

3334

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

3335

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

3336

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

3336

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

3337

# invalid date (Feb 30) succeeds

3338

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

3338

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

3339

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

3340

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

3340

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

3341

# 0-day, succeed

3342

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

3343

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

3344

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

3345

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

3346

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

3347

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

3348

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

3342

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

3343

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

3344

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

3345

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

3346

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

3347

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

3348

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

3349

3350

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

3351

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

3352

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

3350

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

3351

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

3352

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

3353

3354

# these three should work!

3355

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

3356

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

3357

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

3355

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

3356

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

3357

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

3358

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

3359

3360

--echo

3454

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

3455

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

3456

DROP TABLE t1;

3457

3458

3459

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

3460

3461

CREATE TABLE t1 (a INT);

3462

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

3463

CREATE TABLE t2 (b INT);

3464

INSERT INTO t2 VALUES (2);

3465

SELECT * FROM t1 WHERE a = 1 + 1;

3466

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

3467

SELECT * FROM t1 HAVING a = 1 + 1;

3468

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

3469

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

3470

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

3471

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

3472

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

3473

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

b'\\ No newline at end of file'

Older »