~drizzle-trunk/drizzle/development

select fld3,period,price,price2 from t2,t3 where t2.fld1=t3.t2nr and period >= 1001 and period <= 1002 and t2.companynr = 37 order by fld3,period, price;

1173

1169

fld3 period price price2

1174

1170

admonishing 1002 28357832 8723648

1236

1232

18601 vacuuming 1001 5987435 234724

1237

1233

18801 inch 1001 5987435 234724

1238

1234

18811 repetitions 1001 5987435 234724

1239

create table t4 (

1240

companynr tinyint unsigned NOT NULL default '0',

1235

create temporary table t4 (

1236

companynr int NOT NULL default '0',

1241

1237

companyname char(30) NOT NULL default '',

1242

1238

PRIMARY KEY (companynr),

1243

1239

UNIQUE KEY companyname(companyname)

1244

) ENGINE=MyISAM MAX_ROWS=50 PACK_KEYS=1 COMMENT='companynames';

1240

) ENGINE=MyISAM COMMENT='companynames';

1245

1241

select STRAIGHT_JOIN t2.companynr,companyname from t4,t2 where t2.companynr=t4.companynr group by t2.companynr;

1246

1242

companynr companyname

1247

1243

0 Unknown

1933

1929

1934

1930

1935

1931

1936

1937

1932

show tables from test like "s%";

1938

1933

Tables_in_test (s%)

1939

1934

show tables from test like "t?";

1941

1936

show full columns from t2;

1942

1937

Field Type Collation Null Key Default Extra Privileges Comment

1943

1938

auto int NULL NO PRI NULL auto_increment #

1944

fld1 int unsigned NULL NO UNI NULL #

1945

companynr tinyint(3) unsigned NULL NO NULL #

1946

fld3 varchar(30) latin1_swedish_ci NO MUL NULL #

1947

fld4 varchar(35) latin1_swedish_ci NO NULL #

1948

fld5 varchar(35) latin1_swedish_ci NO NULL #

1949

fld6 varchar(4) latin1_swedish_ci NO NULL #

1939

fld1 int NULL NO UNI 0 #

1940

companynr int NULL NO 0 #

1941

fld3 varchar(30) utf8_general_ci NO MUL #

1942

fld4 varchar(35) utf8_general_ci NO #

1943

fld5 varchar(35) utf8_general_ci NO #

1944

fld6 varchar(4) utf8_general_ci NO #

1950

1945

show full columns from t2 from test like 'f%';

1951

1946

Field Type Collation Null Key Default Extra Privileges Comment

1952

fld1 int unsigned NULL NO UNI NULL #

1953

fld3 varchar(30) latin1_swedish_ci NO MUL NULL #

1954

fld4 varchar(35) latin1_swedish_ci NO NULL #

1955

fld5 varchar(35) latin1_swedish_ci NO NULL #

1956

fld6 varchar(4) latin1_swedish_ci NO NULL #

1947

fld1 int NULL NO UNI 0 #

1948

fld3 varchar(30) utf8_general_ci NO MUL #

1949

fld4 varchar(35) utf8_general_ci NO #

1950

fld5 varchar(35) utf8_general_ci NO #

1951

fld6 varchar(4) utf8_general_ci NO #

1957

1952

show full columns from t2 from test like 's%';

1958

1953

Field Type Collation Null Key Default Extra Privileges Comment

1959

1954

drop table t4, t3, t2, t1;

1960

1955

CREATE TABLE t1 (

1961

id bigint unsigned NOT NULL auto_increment,

1956

id bigint NOT NULL auto_increment,

1962

1957

pseudo varchar(35) NOT NULL default '',

1963

1958

PRIMARY KEY (id),

1964

1959

UNIQUE KEY pseudo (pseudo)

1968

1963

SELECT 1 as rnd1 from t1 where rand() > 2;

1969

1964

rnd1

1970

1965

DROP TABLE t1;

1971

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;

1966

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;

1972

1967

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

1973

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;

1968

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;

1974

1969

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

1975

1970

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;

1976

gvid the_success the_fail the_size the_time

1977

Warnings:

1978

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

1979

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

1971

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

1980

1972

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;

1981

1973

gvid the_success the_fail the_size the_time

1982

1974

DROP TABLE t1,t2;

2164

2156

2165

2157

2166

2158

drop table t1;

2167

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;

2159

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;

2168

2160

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

2169

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

2161

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

2170

2162

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

2171

2163

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

2172

2164

aa id t2_id id

2222

2214

3 2 3 4

2223

2215

4 2 4 4

2224

2216

drop table t1, t2;

2225

CREATE TABLE t1 (

2217

CREATE TEMPORARY TABLE t1 (

2226

2218

i int NOT NULL default '0',

2227

2219

c char(10) NOT NULL default '',

2228

2220

PRIMARY KEY (i),

2270

2262

city

2271

2263

London

2272

2264

DROP TABLE t1;

2273

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

2265

create table t1 (a int, b int);

2274

2266

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

2275

2267

select a-b from t1 order by 1;

2276

2268

a-b

2269

-1

2277

2270

2278

2271

2279

18446744073709551615

2280

2272

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

2281

2273

a-b (a-b < 0)

2274

-1 1

2282

2275

0 0

2283

2276

1 0

2284

18446744073709551615 0

2285

2277

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

2286

2278

d (a-b >= 0) b

2287

2279

1 1 0

2288

2280

0 1 1

2289

18446744073709551615 1 2

2290

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

2291

cast((a - b) as unsigned)

2281

select a - b from t1 order by 1;

2282

a - b

2283

-1

2292

2284

2293

2285

2294

18446744073709551615

2295

2286

drop table t1;

2296

2287

create table t1 (a int);

2297

2288

select all all * from t1;

2304

2295

ERROR HY000: Incorrect usage of ALL and DISTINCT

2305

2296

drop table t1;

2306

2297

CREATE TABLE t1 (

2307

kunde_intern_id int unsigned NOT NULL default '0',

2308

kunde_id int unsigned NOT NULL default '0',

2309

FK_firma_id int unsigned NOT NULL default '0',

2298

kunde_intern_id int NOT NULL default '0',

2299

kunde_id int NOT NULL default '0',

2300

FK_firma_id int NOT NULL default '0',

2310

2301

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

2311

2302

vorname varchar(128) NOT NULL default '',

2312

2303

nachname varchar(128) NOT NULL default '',

2357

2348

COUNT(*)

2358

2349

2359

2350

drop table t1;

2360

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

2361

INSERT INTO t1 VALUES (0x8000000000000000);

2362

SELECT b FROM t1 WHERE b=0x8000000000000000;

2351

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

2352

INSERT INTO t1 VALUES (0x4000000000000000);

2353

SELECT b FROM t1 WHERE b=0x4000000000000000;

2363

2354

2364

9223372036854775808

2355

4611686018427387904

2365

2356

DROP TABLE t1;

2366

2357

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

2367

2358

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

2374

2365

drop table t1,t2,t3;

2375

2366

CREATE TABLE t1 (

2376

2367

acct_id int NOT NULL default '0',

2377

profile_id smallint default NULL,

2368

profile_id int default NULL,

2378

2369

UNIQUE KEY t1$acct_id (acct_id),

2379

2370

KEY t1$profile_id (profile_id)

2380

2371

);

2381

2372

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

2382

2373

CREATE TABLE t2 (

2383

profile_id smallint default NULL,

2374

profile_id int default NULL,

2384

2375

queue_id int default NULL,

2385

2376

seq int default NULL,

2386

2377

KEY t2$queue_id (queue_id)

2392

2383

seq int default NULL,

2393

2384

warn_lvl int default NULL,

2394

2385

crit_lvl int default NULL,

2395

rr1 tinyint NOT NULL default '0',

2386

rr1 int NOT NULL default '0',

2396

2387

rr2 int default NULL,

2397

default_queue tinyint NOT NULL default '0',

2388

default_queue int NOT NULL default '0',

2398

2389

KEY t3$qtype (qtype),

2399

2390

KEY t3$id (id)

2400

2391

);

2418

2409

2419

2410

NULL

2420

2411

drop table t1,t2;

2421

create table t2 (a tinyint unsigned);

2412

create table t2 (a int);

2422

2413

create index t2i on t2(a);

2423

2414

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

2424

2415

select * from t2 where a > -1;

2457

2448

create table t11 like t1;

2458

2449

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

2459

2450

show table status like 't1%';

2460

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

2461

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

2462

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

2451

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 Plugin_name

2452

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

2453

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

2463

2454

select 123 as a from t1 where f1 is null;

2464

2455

2465

2456

drop table t1,t11;

2585

2576

min(key1)

2586

2577

0.3762

2587

2578

DROP TABLE t1,t2;

2588

CREATE TABLE t1 (i BIGINT UNSIGNED NOT NULL);

2579

CREATE TABLE t1 (i BIGINT NOT NULL);

2589

2580

INSERT INTO t1 VALUES (10);

2590

2581

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

2591

2582

i='1e+01' i=1e+01 i in (1e+01,1e+01) i in ('1e+01','1e+01')

2593

2584

DROP TABLE t1;

2594

2585

CREATE TABLE t1 (c0 int);

2595

2586

CREATE TABLE t2 (c0 int);

2596

INSERT INTO t1 VALUES(@@connect_timeout);

2597

INSERT INTO t2 VALUES(@@connect_timeout);

2598

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

2587

INSERT INTO t1 VALUES(@@server_id);

2588

INSERT INTO t2 VALUES(@@server_id);

2589

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

2599

2590

c0 c0

2600

2591

X X

2601

2592

DROP TABLE t1, t2;

2602

2593

End of 4.1 tests

2603

CREATE TABLE t1 (

2604

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

2605

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

2594

CREATE TEMPORARY TABLE t1 (

2595

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

2596

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

2606

2597

F2I4 int NOT NULL default '0'

2607

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2598

) ENGINE=MyISAM;

2608

2599

INSERT INTO t1 VALUES

2609

2600

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

2610

2601

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

2649

2640

Wall

2650

2641

Wall

2651

2642

drop table t1;

2652

create table t1 (s1 int) engine=myisam;

2643

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

2653

2644

insert into t1 values (0);

2654

2645

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

2655

2646

avg(distinct s1)

2724

2715

2725

2716

2726

2717

drop table t1, t2;

2727

CREATE TABLE t1 (`id` TINYINT);

2728

CREATE TABLE t2 (`id` TINYINT);

2729

CREATE TABLE t3 (`id` TINYINT);

2718

CREATE TABLE t1 (`id` int);

2719

CREATE TABLE t2 (`id` int);

2720

CREATE TABLE t3 (`id` int);

2730

2721

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

2731

2722

INSERT INTO t2 VALUES (2);

2732

2723

INSERT INTO t3 VALUES (3);

2789

2780

drop table t1,t2,t3;

2790

2781

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

2791

2782

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

2792

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

2783

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

2793

2784

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

2794

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

2785

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

2795

2786

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

2796

2787

select count(*)

2797

2788

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

2818

2809

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

2819

2810

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

2820

2811

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

2821

f1 f2

2822

1 2005-01-01

2823

2 2005-09-01

2824

3 2005-09-30

2825

4 2005-10-01

2826

5 2005-12-30

2812

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

2827

2813

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

2828

f1 f2

2829

1 2005-01-01

2830

2 2005-09-01

2831

3 2005-09-30

2832

4 2005-10-01

2833

5 2005-12-30

2814

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

2834

2815

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

2835

f1 f2

2836

4 2005-10-01

2837

5 2005-12-30

2816

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

2838

2817

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

2839

f1 f2

2840

3 2005-09-30

2841

4 2005-10-01

2842

5 2005-12-30

2843

Warnings:

2844

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

2818

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

2845

2819

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

2846

f1 f2

2847

1 2005-01-01

2848

2 2005-09-01

2849

3 2005-09-30

2820

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

2850

2821

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

2851

f1 f2

2852

1 2005-01-01

2853

2 2005-09-01

2854

Warnings:

2855

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

2822

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

2856

2823

drop table t1;

2857

2824

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

2858

2825

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

2933

2900

NULL a NULL

2934

2901

drop table t1,t2;

2935

2902

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

2936

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on f1=f2) tt' at line 1

2903

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'on f1=f2) tt' at line 1

2937

2904

CREATE TABLE t1 (sku int PRIMARY KEY, pr int);

2938

2905

CREATE TABLE t2 (sku int PRIMARY KEY, sppr int, name varchar(255));

2939

2906

INSERT INTO t1 VALUES

2953

2920

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

2954

2921

1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 2 Using where

2955

2922

DROP TABLE t1,t2;

2956

CREATE TABLE t1 (i TINYINT UNSIGNED NOT NULL);

2957

INSERT t1 SET i = 0;

2958

UPDATE t1 SET i = -1;

2959

Warnings:

2960

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

2961

SELECT * FROM t1;

2962

2963

2964

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

2965

Warnings:

2966

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

2967

SELECT * FROM t1;

2968

2969

2970

UPDATE t1 SET i = i - 1;

2971

Warnings:

2972

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

2973

SELECT * FROM t1;

2974

2975

255

2976

DROP TABLE t1;

2977

2923

create table t1 (a int);

2978

2924

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

2979

2925

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

3005

2951

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

3006

2952

DROP TABLE t1, t2;

3007

2953

create table t1 (

3008

a int unsigned not null auto_increment primary key,

3009

b bit not null,

3010

c bit not null

2954

a int not null auto_increment primary key,

2955

b int not null,

2956

c int not null

3011

2957

);

3012

2958

create table t2 (

3013

a int unsigned not null auto_increment primary key,

3014

b bit not null,

3015

c int unsigned not null,

2959

a int not null auto_increment primary key,

2960

b int not null,

2961

c int not null,

3016

2962

d varchar(50)

3017

2963

);

3018

2964

insert into t1 (b,c) values (0,1), (0,1);

3079

3025

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

3080

3026

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

3081

3027

id select_type table type possible_keys key key_len ref rows Extra

3082

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where

3083

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

3028

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3029

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

3084

3030

EXPLAIN SELECT t2.*

3085

3031

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

3086

3032

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

3087

3033

id select_type table type possible_keys key key_len ref rows Extra

3088

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where

3089

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

3034

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3035

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

3090

3036

EXPLAIN SELECT t2.*

3091

3037

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

3092

3038

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

3093

3039

id select_type table type possible_keys key key_len ref rows Extra

3094

1 SIMPLE t1 range PRIMARY PRIMARY 12 NULL 2 Using where

3095

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

3040

1 SIMPLE t1 range PRIMARY PRIMARY 42 NULL 2 Using where

3041

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

3096

3042

DROP TABLE t1,t2;

3097

3043

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

3098

3044

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

3102

3048

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

3103

3049

id select_type table type possible_keys key key_len ref rows Extra

3104

3050

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3105

1 SIMPLE t2 const b b 22 const 1 Using index

3051

1 SIMPLE t2 const b b 82 const 1 Using index

3106

3052

DROP TABLE t1,t2;

3107

3053

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

3108

3054

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

3126

3072

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

3127

3073

id select_type table type possible_keys key key_len ref rows Extra

3128

3074

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3129

1 SIMPLE t2 range si si 5 NULL 4 Using index condition; Using MRR

3075

1 SIMPLE t2 range si si 5 NULL 4 Using where

3130

3076

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

3131

3077

EXPLAIN

3132

3078

SELECT t3.a FROM t1,t2,t3

3134

3080

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

3135

3081

id select_type table type possible_keys key key_len ref rows Extra

3136

3082

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3137

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

3083

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

3138

3084

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

3139

3085

EXPLAIN

3140

3086

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

3142

3088

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

3143

3089

id select_type table type possible_keys key key_len ref rows Extra

3144

3090

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3145

1 SIMPLE t2 range si si 5 NULL 2 Using index condition; Using MRR

3091

1 SIMPLE t2 range si si 5 NULL 2 Using where

3146

3092

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

3147

3093

EXPLAIN

3148

3094

SELECT t3.a FROM t1,t2,t3

3150

3096

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

3151

3097

id select_type table type possible_keys key key_len ref rows Extra

3152

3098

1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1

3153

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

3099

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

3154

3100

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

3155

3101

DROP TABLE t1,t2,t3;

3156

3102

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

3165

3111

CREATE TABLE t1(a int);

3166

3112

INSERT into t1 values (1), (2), (3);

3167

3113

SELECT * FROM t1 LIMIT 2, -1;

3168

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1

3114

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '-1' at line 1

3169

3115

DROP TABLE t1;

3170

3116

CREATE TABLE t1 (

3171

3117

ID_with_null int NULL,

3224

3170

EXPLAIN SELECT * FROM t1

3225

3171

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

3226

3172

id select_type table type possible_keys key key_len ref rows Extra

3227

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL 1 Using intersect(idx2,idx1); Using where; Using index

3173

1 SIMPLE t1 index_merge idx1,idx2 idx2,idx1 4,10 NULL # Using intersect(idx2,idx1); Using where; Using index

3228

3174

EXPLAIN SELECT * FROM t1

3229

3175

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

3230

3176

id select_type table type possible_keys key key_len ref rows Extra

3231

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3177

1 SIMPLE t1 ref idx1,idx2 idx2 4 const # Using where

3232

3178

EXPLAIN SELECT * FROM t1

3233

3179

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

3234

3180

id select_type table type possible_keys key key_len ref rows Extra

3235

1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where

3181

1 SIMPLE t1 ref idx1,idx2 idx2 4 const # Using where

3236

3182

DROP INDEX idx1 ON t1;

3237

3183

CREATE UNIQUE INDEX idx1 ON t1(ID1_with_null,ID2_with_null);

3238

3184

EXPLAIN SELECT * FROM t1

3270

3216

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

3271

3217

id select_type table type possible_keys key key_len ref rows Extra

3272

3218

1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1

3273

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

3274

Warnings:

3275

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

3219

1 SIMPLE t1 range ts ts 5 NULL 1 Using where

3276

3220

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

3277

3221

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

3278

3222

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

3279

3223

a ts a dt1 dt2

3280

3224

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

3281

Warnings:

3282

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

3283

3225

DROP TABLE t1,t2;

3284

create table t1 (a bigint unsigned);

3226

create table t1 (a bigint);

3285

3227

insert into t1 values

3286

(if(1, 9223372036854775808, 1)),

3287

(case when 1 then 9223372036854775808 else 1 end),

3288

(coalesce(9223372036854775808, 1));

3228

(if(1, 92233720368547758, 1)),

3229

(case when 1 then 92233720368547758 else 1 end),

3230

(coalesce(92233720368547758, 1));

3289

3231

select * from t1;

3290

3232

3291

9223372036854775808

3292

9223372036854775808

3293

9223372036854775808

3233

92233720368547758

3234

92233720368547758

3235

92233720368547758

3294

3236

drop table t1;

3295

3237

create table t1 select

3296

3238

if(1, 9223372036854775808, 1) i,

3298

3240

coalesce(9223372036854775808, 1) co;

3299

3241

show create table t1;

3300

3242

Table Create Table

3301

t1 CREATE TABLE "t1" (

3302

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

3303

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

3304

"co" decimal(19,0) NOT NULL

3305

) ENGINE=InnoDB DEFAULT CHARSET=latin1

3243

t1 CREATE TABLE `t1` (

3244

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

3245

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

3246

`co` decimal(19,0) NOT NULL

3247

) ENGINE=InnoDB

3306

3248

drop table t1;

3307

3249

select

3308

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

3309

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

3310

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

3250

if(1, 1111111111111111111, 1) i,

3251

case when 1 then 1111111111111111111 else 1 end c,

3252

coalesce(1111111111111111111, 1) co;

3311

3253

i c co

3312

3254

1111111111111111111 1111111111111111111 1111111111111111111

3313

3255

CREATE TABLE t1 (name varchar(255));

3372

3314

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

3373

3315

id select_type table type possible_keys key key_len ref rows Extra

3374

3316

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3375

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

3317

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

3376

3318

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

3377

3319

name name n

3378

3320

ccc NULL NULL

3446

3388

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

3447

3389

id select_type table type possible_keys key key_len ref rows Extra

3448

3390

1 SIMPLE t1 ALL NULL NULL NULL NULL 5

3449

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

3391

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

3450

3392

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

3451

3393

name name n

3452

3394

ccc NULL NULL

3574

3516

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

3575

3517

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

3576

3518

Warnings:

3577

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

3519

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

3578

3520

SHOW WARNINGS;

3579

3521

Level Code Message

3580

3522

3581

3523

DROP TABLE t1;

3582

3524

SELECT 1 AS ` `;

3583

3525

3594

3536

3595

3537

Warnings:

3596

3538

Warning 1466 Leading spaces are removed from name ' x'

3597

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

3598

CREATE TABLE t2 (c21 INT UNSIGNED NOT NULL,

3539

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

3540

CREATE TABLE t2 (c21 INT NOT NULL,

3599

3541

c22 INT DEFAULT NULL,

3600

3542

KEY(c21, c22));

3601

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

3543

CREATE TABLE t3 (c31 INT NOT NULL DEFAULT 0,

3602

3544

c32 INT DEFAULT NULL,

3603

3545

c33 INT NOT NULL,

3604

c34 INT UNSIGNED DEFAULT 0,

3546

c34 INT DEFAULT 0,

3605

3547

KEY (c33, c34, c32));

3606

3548

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

3607

3549

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

3633

3575

3634

3576

3635

3577

DROP TABLE t1, t2, t3;

3636

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

3637

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

3638

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

3639

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

3640

3641

Warnings:

3642

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

3643

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

3644

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

3645

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

3646

3647

Warnings:

3648

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

3649

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

3650

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

3651

3652

Warnings:

3653

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

3654

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

3655

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

3656

3657

Warnings:

3658

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

3659

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

3660

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

3661

3662

Warnings:

3663

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

3664

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

3665

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

3666

3667

Warnings:

3668

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

3669

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

3670

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

3671

3672

Warnings:

3673

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

3674

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

3675

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

3676

3677

Warnings:

3678

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

3679

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

3680

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

3681

3682

Warnings:

3683

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

3684

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

3685

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

3686

3687

Warnings:

3688

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

3689

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

3690

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

3691

3692

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

3693

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

3694

3695

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

3696

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

3697

3698

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

3699

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

3700

3701

Warnings:

3702

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

3703

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

3704

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

3705

3706

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

3707

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

3708

3709

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

3710

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

3711

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

3712

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

3713

3714

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

3715

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

3716

3717

Warnings:

3718

Warning 1292 Truncated incorrect datetime value: ''

3719

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

3720

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

3721

3722

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

3723

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

3724

3725

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

3726

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

3727

NULL

3728

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

3729

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

3730

3731

Warnings:

3732

Warning 1292 Truncated incorrect datetime value: ''

3733

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

3734

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

3735

3736

Warnings:

3737

Warning 1292 Truncated incorrect date value: '1'

3738

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

3739

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

3740

3741

Warnings:

3742

Warning 1292 Truncated incorrect date value: '1'

3743

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

3744

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

3745

3746

Warnings:

3747

Warning 1292 Truncated incorrect date value: ''

3748

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

3749

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

3750

3751

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

3752

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

3753

3754

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

3755

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

3756

3757

3578

3758

3579

3759

3580

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

3840

3661

3841

3662

3842

3663

DROP TABLE t1;

3664

CREATE TABLE t1 (a INT);

3665

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

3666

CREATE TABLE t2 (b INT);

3667

INSERT INTO t2 VALUES (2);

3668

SELECT * FROM t1 WHERE a = 1 + 1;

3669

3670

3671

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

3672

id select_type table type possible_keys key key_len ref rows filtered Extra

3673

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

3674

Warnings:

3675

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

3676

SELECT * FROM t1 HAVING a = 1 + 1;

3677

3678

3679

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

3680

id select_type table type possible_keys key key_len ref rows filtered Extra

3681

1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00

3682

Warnings:

3683

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

3684

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

3685

a b

3686

4 2

3687

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

3688

id select_type table type possible_keys key key_len ref rows filtered Extra

3689

1 SIMPLE t2 ALL NULL NULL NULL NULL 1 100.00

3690

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

3691

Warnings:

3692

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

3693

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

3694

b a

3695

2 3

3696

EXPLAIN EXTENDED SELECT * FROM t2 LEFT JOIN t1 ON a = b + 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

3700

Warnings:

3701

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

3702

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

3703

id select_type table type possible_keys key key_len ref rows filtered Extra

3704

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

3705

Warnings:

3706

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

Older »