~drizzle-trunk/drizzle/development

#

# Hash semi-join regression tests

# (WL#1110: Subquery optimization: materialization)

#

--disable_warnings

drop table if exists t1, t2, t3, t1i, t2i, t3i;

--enable_warnings

create table t1 (a1 char(8), a2 char(8));

create temporary table t2 (b1 char(8), b2 char(8)) ENGINE=MyISAM;

create table t3 (c1 char(8), c2 char(8));

insert into t1 values ('1 - 00', '2 - 00');

insert into t1 values ('1 - 01', '2 - 01');

insert into t1 values ('1 - 02', '2 - 02');

insert into t2 values ('1 - 01', '2 - 01');

insert into t2 values ('1 - 01', '2 - 01');

insert into t2 values ('1 - 02', '2 - 02');

insert into t2 values ('1 - 02', '2 - 02');

insert into t2 values ('1 - 03', '2 - 03');

insert into t3 values ('1 - 01', '2 - 01');

insert into t3 values ('1 - 02', '2 - 02');

insert into t3 values ('1 - 03', '2 - 03');

insert into t3 values ('1 - 04', '2 - 04');

# Indexed columns

create temporary table t1i (a1 char(8), a2 char(8)) ENGINE=MyISAM;

create table t2i (b1 char(8), b2 char(8));

create temporary table t3i (c1 char(8), c2 char(8)) ENGINE=MyISAM;

create index it1i1 on t1i (a1);

create index it1i2 on t1i (a2);

create index it1i3 on t1i (a1, a2);

create index it2i1 on t2i (b1);

create index it2i2 on t2i (b2);

create index it2i3 on t2i (b1, b2);

create index it3i1 on t3i (c1);

create index it3i2 on t3i (c2);

create index it3i3 on t3i (c1, c2);

insert into t1i select * from t1;

insert into t2i select * from t2;

insert into t3i select * from t3;

--echo /********************************************************************

--echo  * Simple tests.

--echo  ********************************************************************/

--echo # non-indexed nullable fields

--replace_column 9 #

explain extended

select * from t1 where a1 in (select b1 from t2 where b1 > '0');

select * from t1 where a1 in (select b1 from t2 where b1 > '0');

--replace_column 9 #

explain extended

select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);

select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);

--replace_column 9 #

explain extended

select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);

select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);

--replace_column 9 #

explain extended

select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);

select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);

# indexed columns

--replace_column 9 #

explain extended

select * from t1i where a1 in (select b1 from t2i where b1 > '0');

select * from t1i where a1 in (select b1 from t2i where b1 > '0');

--replace_column 9 #

explain extended

select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);

select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);

--replace_column 9 #

explain extended

select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');

select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');

--replace_column 9 #

explain extended

select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);

select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);

--replace_column 9 #

explain extended

select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);

select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);

# materialize the result of ORDER BY

# non-indexed fields

--replace_column 9 #

explain extended

select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);

select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);

# indexed fields

--replace_column 9 #

explain extended

select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);

select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);

# nested subqueries, views

--replace_column 9 #

explain extended

select * from t1

where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and

      (a1, a2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

select * from t1

where (a1, a2) in (select b1, b2 from t2 where b1 >  '0') and

      (a1, a2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

--replace_column 9 #

explain extended

select * from t1i

where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and

      (a1, a2) in (select c1, c2 from t3i

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

select * from t1i

where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and

      (a1, a2) in (select c1, c2 from t3i

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

--replace_column 9 #

explain extended

select * from t1

where (a1, a2) in (select b1, b2 from t2

                   where b2 in (select c2 from t3 where c2 LIKE '%02') or

                         b2 in (select c2 from t3 where c2 LIKE '%03')) and

      (a1, a2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

select * from t1

where (a1, a2) in (select b1, b2 from t2

                   where b2 in (select c2 from t3 where c2 LIKE '%02') or

                         b2 in (select c2 from t3 where c2 LIKE '%03')) and

      (a1, a2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

# as above with correlated innermost subquery

--replace_column 9 #

explain extended

select * from t1

where (a1, a2) in (select b1, b2 from t2

                   where b2 in (select c2 from t3 t3a where c1 = a1) or

                         b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and

      (a1, a2) in (select c1, c2 from t3 t3c

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

select * from t1

where (a1, a2) in (select b1, b2 from t2

                   where b2 in (select c2 from t3 t3a where c1 = a1) or

                         b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and

      (a1, a2) in (select c1, c2 from t3 t3c

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

# multiple levels of nesting subqueries, unions

--replace_column 9 #

explain extended

(select * from t1

where (a1, a2) in (select b1, b2 from t2

                   where b2 in (select c2 from t3 where c2 LIKE '%02') or

                         b2 in (select c2 from t3 where c2 LIKE '%03')

                   group by b1, b2) and

      (a1, a2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))

UNION

(select * from t1i

where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and

      (a1, a2) in (select c1, c2 from t3i

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));

(select * from t1

where (a1, a2) in (select b1, b2 from t2

                   where b2 in (select c2 from t3 where c2 LIKE '%02') or

                         b2 in (select c2 from t3 where c2 LIKE '%03')

                   group by b1, b2) and

      (a1, a2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')))

UNION

(select * from t1i

where (a1, a2) in (select b1, b2 from t2i where b1 >  '0') and

      (a1, a2) in (select c1, c2 from t3i

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')));

# UNION of subqueries as a subquery (thus it is not computed via materialization)

--replace_column 9 #

explain extended

select * from t1

where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and

      (a1, a2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

select * from t1

where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and

      (a1, a2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0'));

# as above, with a join conditon between the outer references

--replace_column 9 #

explain extended

select * from t1, t3

where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and

      (c1, c2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and

       a1 = c1;

select * from t1, t3

where (a1, a2) in (select * from t1 where a1 > '0' UNION select * from t2 where b1 < '9') and

      (c1, c2) in (select c1, c2 from t3

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0')) and

       a1 = c1;

--echo /*********************************************************************

--echo * Negative tests, where materialization should not be applied.

--echo **********************************************************************/

--echo # UNION in a subquery

--replace_column 9 #

explain extended

select * from t3

where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');

select * from t3

where c1 in (select a1 from t1 where a1 > '0' UNION select b1 from t2 where b1 < '9');

# correlation

--replace_column 9 #

explain extended

select * from t1

where (a1, a2) in (select b1, b2 from t2

                   where b2 in (select c2 from t3 t3a where c1 = a1) or

                         b2 in (select c2 from t3 t3b where c2 LIKE '%03')) and

      (a1, a2) in (select c1, c2 from t3 t3c

                   where (c1, c2) in (select b1, b2 from t2i where b2 > '0' or b2 = a2));

DROP TABLE t1i, t2i, t3i;

# subquery has no tables

--replace_column 9 #

explain extended

select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');

select * from t1 where (a1, a2) in (select '1 - 01', '2 - 01');

/******************************************************************************

* Subqueries in other uncovered clauses.

******************************************************************************/

/* SELECT clause */

select ((a1,a2) IN (select * from t2 where b2 > '0')) IS NULL from t1;

/* GROUP BY clause */

create temporary table columns (col int key) ENGINE=MyISAM;

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

--replace_column 9 #

explain extended

select * from t1 group by (select col from columns limit 1);

select * from t1 group by (select col from columns limit 1);

--replace_column 9 #

explain extended

select * from t1 group by (a1 in (select col from columns));

select * from t1 group by (a1 in (select col from columns));

--echo /* ORDER BY clause */

--replace_column 9 #

explain extended

select * from t1 order by (select col from columns limit 1);

select * from t1 order by (select col from columns limit 1);

DROP TABLE columns;

/******************************************************************************

* Column types/sizes that affect materialization.

******************************************************************************/

/*

  Test that BLOBs are not materialized (except when arguments of some functions).

*/

# force materialization to be always considered

set @prefix_len = 6;

# BLOB == 16 (small blobs that could be stored in MEMORY tables)

set @blob_len = 16;

set @suffix_len = @blob_len - @prefix_len;

create temporary table t1_16 (a1 blob, a2 blob) ENGINE=MyISAM;

create temporary table t2_16 (b1 blob, b2 blob) ENGINE=MyISAM;

create temporary table t3_16 (c1 blob, c2 blob) ENGINE=MyISAM;

insert into t1_16 values

 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));

insert into t1_16 values

 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

insert into t1_16 values

 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

insert into t2_16 values

 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

insert into t2_16 values

 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

insert into t2_16 values

 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

insert into t3_16 values

 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

insert into t3_16 values

 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

insert into t3_16 values

 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

insert into t3_16 values

 (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));

# single value transformer

explain extended select left(a1,7), left(a2,7)

from t1_16

where a1 in (select b1 from t2_16 where b1 > '0');

select left(a1,7), left(a2,7)

from t1_16

where a1 in (select b1 from t2_16 where b1 > '0');

# row value transformer

explain extended select left(a1,7), left(a2,7)

from t1_16

where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');

select left(a1,7), left(a2,7)

from t1_16

where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');

# string function with a blob argument, the return type may be != blob

explain extended select left(a1,7), left(a2,7)

from t1_16

where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');

select left(a1,7), left(a2,7)

from t1_16

where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');

# group_concat with a blob argument - depends on

# the variable group_concat_max_len, and

# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB

explain extended select left(a1,7), left(a2,7)

from t1_16

where a1 in (select group_concat(b1) from t2_16 group by b2);

select left(a1,7), left(a2,7)

from t1_16

where a1 in (select group_concat(b1) from t2_16 group by b2);

set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512)

explain extended select left(a1,7), left(a2,7)

from t1_16

where a1 in (select group_concat(b1) from t2_16 group by b2);

select left(a1,7), left(a2,7)

from t1_16

where a1 in (select group_concat(b1) from t2_16 group by b2);

# BLOB column at the second (intermediate) level of nesting

explain extended

select * from t1

where concat(a1,'x') IN

      (select left(a1,8) from t1_16

       where (a1, a2) IN

             (select t2_16.b1, t2_16.b2 from t2_16, t2

              where t2.b2 = substring(t2_16.b2,1,6) and

                    t2.b1 IN (select c1 from t3 where c2 > '0')));

drop table t1_16, t2_16, t3_16;

# BLOB == 512 (CONVERT_IF_BIGGER_TO_BLOB == 512)

set @blob_len = 512;

set @suffix_len = @blob_len - @prefix_len;

create temporary table t1_512 (a1 blob, a2 blob) ENGINE=MyISAM;

create temporary table t2_512 (b1 blob, b2 blob) ENGINE=MyISAM;

create temporary table t3_512 (c1 blob, c2 blob) ENGINE=MyISAM;

insert into t1_512 values

 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));

insert into t1_512 values

 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

insert into t1_512 values

 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

insert into t2_512 values

 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

insert into t2_512 values

 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

insert into t2_512 values

 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

insert into t3_512 values

 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

insert into t3_512 values

 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

insert into t3_512 values

 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

insert into t3_512 values

 (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));

# single value transformer

explain extended select left(a1,7), left(a2,7)

from t1_512

where a1 in (select b1 from t2_512 where b1 > '0');

select left(a1,7), left(a2,7)

from t1_512

where a1 in (select b1 from t2_512 where b1 > '0');

# row value transformer

explain extended select left(a1,7), left(a2,7)

from t1_512

where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');

select left(a1,7), left(a2,7)

from t1_512

where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');

# string function with a blob argument, the return type may be != blob

explain extended select left(a1,7), left(a2,7)

from t1_512

where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');

select left(a1,7), left(a2,7)

from t1_512

where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');

# group_concat with a blob argument - depends on

# the variable group_concat_max_len, and

# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB

explain extended select left(a1,7), left(a2,7)

from t1_512

where a1 in (select group_concat(b1) from t2_512 group by b2);

select left(a1,7), left(a2,7)

from t1_512

where a1 in (select group_concat(b1) from t2_512 group by b2);

set @@group_concat_max_len = 256; # anything < (CONVERT_IF_BIGGER_TO_BLOB = 512)

explain extended select left(a1,7), left(a2,7)

from t1_512

where a1 in (select group_concat(b1) from t2_512 group by b2);

select left(a1,7), left(a2,7)

from t1_512

where a1 in (select group_concat(b1) from t2_512 group by b2);

drop table t1_512, t2_512, t3_512;

# BLOB == 1024 (group_concat_max_len == 1024)

set @blob_len = 1024;

set @suffix_len = @blob_len - @prefix_len;

create temporary table t1_1024 (a1 blob, a2 blob) ENGINE=MyISAM;

create temporary table t2_1024 (b1 blob, b2 blob) ENGINE=MyISAM;

create temporary table t3_1024 (c1 blob, c2 blob) ENGINE=MyISAM;

insert into t1_1024 values

 (concat('1 - 00', repeat('x', @suffix_len)), concat('2 - 00', repeat('x', @suffix_len)));

insert into t1_1024 values

 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

insert into t1_1024 values

 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

insert into t2_1024 values

 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

insert into t2_1024 values

 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

insert into t2_1024 values

 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

insert into t3_1024 values

 (concat('1 - 01', repeat('x', @suffix_len)), concat('2 - 01', repeat('x', @suffix_len)));

insert into t3_1024 values

 (concat('1 - 02', repeat('x', @suffix_len)), concat('2 - 02', repeat('x', @suffix_len)));

insert into t3_1024 values

 (concat('1 - 03', repeat('x', @suffix_len)), concat('2 - 03', repeat('x', @suffix_len)));

insert into t3_1024 values

 (concat('1 - 04', repeat('x', @suffix_len)), concat('2 - 04', repeat('x', @suffix_len)));

# single value transformer

explain extended select left(a1,7), left(a2,7)

from t1_1024

where a1 in (select b1 from t2_1024 where b1 > '0');

select left(a1,7), left(a2,7)

from t1_1024

where a1 in (select b1 from t2_1024 where b1 > '0');

# row value transformer

explain extended select left(a1,7), left(a2,7)

from t1_1024

where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');

select left(a1,7), left(a2,7)

from t1_1024

where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');

# string function with a blob argument, the return type may be != blob

explain extended select left(a1,7), left(a2,7)

from t1_1024

where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');

select left(a1,7), left(a2,7)

from t1_1024

where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');

# group_concat with a blob argument - depends on

# the variable group_concat_max_len, and

# convert_blob_length == max_len*collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB