~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/subselect_no_mat.result

Committer: Brian Aker
Date: 2009-07-29 06:03:18 UTC
mfrom: (1100.2.3 semi-goin)
mto: This revision was merged to the branch mainline in revision 1104.
Revision ID: brian@gaz-20090729060318-skvg04cqa67f87pq

Removal of SJ

Show diffs side-by-side

added added

removed removed

tests/r/subselect_no_mat.result

1270

1271

explain extended select * from t2 where t2.a in (select a from t1);

1272

id select_type table type possible_keys key key_len ref rows filtered Extra

1273

1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index

1274

1 PRIMARY t1 index PRIMARY PRIMARY 4 NULL 4 75.00 Using where; Using index; Using join buffer

1273

1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index

1274

2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index

1275

Warnings:

1276

Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)

1276

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY)))

1277

select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

1278

1279

1280

1281

explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

1282

id select_type table type possible_keys key key_len ref rows filtered Extra

1283

1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index

1284

1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer

1283

1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index

1284

2 DEPENDENT SUBQUERY t1 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using where

1285

Warnings:

1286

Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))

1286

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on PRIMARY where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))

1287

select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);

1288

1289

1290

1291

explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);

1292

id select_type table type possible_keys key key_len ref rows filtered Extra

1293

1 PRIMARY t2 index PRIMARY PRIMARY 4 NULL 4 100.00 Using index

1294

1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 4 75.00 Using where; Using join buffer

1295

1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index

1293

1 PRIMARY t2 index NULL PRIMARY 4 NULL 4 100.00 Using where; Using index

1294

2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 100.00

1295

2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 100.00 Using index

1296

Warnings:

1297

Note 1003 select `test`.`t2`.`a` AS `a` from (`test`.`t1` join `test`.`t3`) join `test`.`t2` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))

1297

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t3` where ((`test`.`t3`.`a` = `test`.`t1`.`b`) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`))))

1298

drop table t1, t2, t3;

1299

create temporary table t1 (a int, b int, index a (a,b)) ENGINE=MyISAM;

1300

create temporary table t2 (a int, index a (a)) ENGINE=MyISAM;

1310

1311

explain extended select * from t2 where t2.a in (select a from t1);

1312

id select_type table type possible_keys key key_len ref rows filtered Extra

1313

1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index

1314

1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)

1313

1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index

1314

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index

1315

Warnings:

1316

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)

1316

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a)))

1317

select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

1318

1319

1320

1321

explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

1322

id select_type table type possible_keys key key_len ref rows filtered Extra

1323

1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index

1324

1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)

1323

1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index

1324

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where

1325

Warnings:

1326

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))

1326

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a where ((`test`.`t1`.`b` <> 30) and (<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`)))))

1327

select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);

1328

1329

1330

1331

explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);

1332

id select_type table type possible_keys key key_len ref rows filtered Extra

1333

1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index; Start temporary

1334

1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; FirstMatch(t2)

1335

1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary; Using join buffer

1333

1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index

1334

2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 100.00 Using index

1335

2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 100.00 Using where; Using index; Using join buffer

1336

Warnings:

1337

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1` join `test`.`t3`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t3`.`a` = `test`.`t1`.`b`))

1337

1338

insert into t1 values (3,31);

1339

select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

1340

1347

1348

explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);

1349

id select_type table type possible_keys key key_len ref rows filtered Extra

1350

1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index

1351

1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; FirstMatch(t2)

1350

1 PRIMARY t2 index NULL a 5 NULL 4 100.00 Using where; Using index

1351

2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 1001 100.00 Using index; Using where

1352

Warnings:

1353

Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))

1353

1354

drop table t1, t2, t3;

1355

1356

# alloc_group_fields() working

2817

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,<in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),<exists>(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond(((<cache>(`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond(((<cache>(`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond(<is_not_null_test>(`test`.`t2`.`one`)) and trigcond(<is_not_null_test>(`test`.`t2`.`two`))))) AS `test` from `test`.`t1`

2818

explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');

2819

id select_type table type possible_keys key key_len ref rows filtered Extra

2820

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary

2821

1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer

2820

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Using where

2821

2 SUBQUERY t2 ALL NULL NULL NULL NULL 9 100.00 Using where

2822

Warnings:

2823

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`two` = `test`.`t1`.`two`) and (`test`.`t2`.`one` = `test`.`t1`.`one`) and (`test`.`t2`.`flag` = 'N'))

2823

Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`one`,`test`.`t1`.`two`),(`test`.`t1`.`one`,`test`.`t1`.`two`) in ( <materialize> (select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where (`test`.`t2`.`flag` = 'N') ), <primary_index_lookup>(`test`.`t1`.`one` in <temporary table> on distinct_key)))

2824

explain extended SELECT one,two,ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = '0' group by one,two) as 'test' from t1;

2825

id select_type table type possible_keys key key_len ref rows filtered Extra

2826

1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00

4117

INSERT INTO t1 VALUES ('a', 'aaa');

4118

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

4119

a b

4120

a aa

4121

a aaa

4120

4122

CREATE INDEX I1 ON t1 (a);

4121

4123

CREATE INDEX I2 ON t1 (b);

4122

4124

EXPLAIN SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

4123

4125

id select_type table type possible_keys key key_len ref rows Extra

4124

1 PRIMARY t1 index I1 I1 7 NULL # Using index; LooseScan

4125

1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where

4126

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

4127

2 SUBQUERY t1 index NULL I1 7 NULL # Using index

4126

4128

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1);

4127

4129

a b

4130

a aa

4131

a aaa

4128

4132

CREATE TABLE t2 (a VARCHAR(1), b VARCHAR(10));

4129

4133

INSERT INTO t2 SELECT * FROM t1;

4130

4134

CREATE INDEX I1 ON t2 (a);

4131

4135

CREATE INDEX I2 ON t2 (b);

4132

4136

EXPLAIN SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

4133

4137

id select_type table type possible_keys key key_len ref rows Extra

4134

1 PRIMARY t2 index I1 I1 7 NULL # Using index; LooseScan

4135

1 PRIMARY t2 ref I2 I2 43 test.t2.a # Using where

4138

1 PRIMARY t2 ALL NULL NULL NULL NULL # Using where

4139

2 SUBQUERY t2 index NULL I1 7 NULL # Using index

4136

4140

SELECT a,b FROM t2 WHERE b IN (SELECT a FROM t2);

4137

4141

a b

4142

a aa

4143

a aaa

4138

4144

EXPLAIN

4139

4145

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4140

4146

id select_type table type possible_keys key key_len ref rows Extra

4141

1 PRIMARY t1 index I1 I1 7 NULL # Using where; Using index; LooseScan

4142

1 PRIMARY t1 ref I2 I2 43 test.t1.a # Using where

4147

1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where

4148

2 SUBQUERY t1 index NULL I1 7 NULL # Using where; Using index

4143

4149

SELECT a,b FROM t1 WHERE b IN (SELECT a FROM t1 WHERE LENGTH(a)<500);

4144

4150

a b

4151

a aa

4152

a aaa

4145

4153

DROP TABLE t1,t2;

4146

4154

4147

4155

# Bug #32400: Complex SELECT query returns correct result only on some

Older »