MySQL Lists: commits: bk commit into 5.2 tree (timour:1.2558)

List:	Commits	« Previous Message Next Message »
From:	timour	Date:	August 30 2007 4:49pm
Subject:	bk commit into 5.2 tree (timour:1.2558)
View as plain text
Below is the list of changes that have just been committed into a local
5.2 repository of tkatchaounov. When tkatchaounov does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-08-30 17:49:23+03:00, timour@stripped +8 -0
  WL#1110: Subquery optimization: materialization
  
  - added support for the materialization and lookup of BIT fields in MEMORY tables,
  - added corresponding tests with BIT fields.

  mysql-test/r/subselect_mat.result@stripped, 2007-08-30 17:49:19+03:00, timour@stripped +47
-60
    - added tests for BIT fields
    - removed "show warnings" as warnigs during tests are shown by default

  mysql-test/t/subselect_mat.test@stripped, 2007-08-30 17:49:19+03:00, timour@stripped +52
-21
    - added tests for BIT fields
    - removed "show warnings" as warnigs during tests are shown by default

  sql/item_subselect.cc@stripped, 2007-08-30 17:49:19+03:00, timour@stripped +1 -1
    - added extra member to select_union::create_result_table to set
tmp_table_param.bit_fields_as_long inside the method

  sql/sql_class.h@stripped, 2007-08-30 17:49:19+03:00, timour@stripped +7 -1
    -  added extra member bit_fields_as_long to handle indexes over BIT fields for MEMORY
tables

  sql/sql_cursor.cc@stripped, 2007-08-30 17:49:19+03:00, timour@stripped +2 -1
    -  Added extra parameter bit_fields_as_long to handle indexes over BIT fields for
MEMORY tables.
       Here this parameter is not set.

  sql/sql_derived.cc@stripped, 2007-08-30 17:49:19+03:00, timour@stripped +3 -2
    -  Added extra parameter bit_fields_as_long to handle indexes over BIT fields for
MEMORY tables.
       Here this parameter is not set.

  sql/sql_select.cc@stripped, 2007-08-30 17:49:19+03:00, timour@stripped +16 -5
    - In order to handle indexes over BIT fields for MEMORY tables, pass the extra
      parameter param->bit_fields_as_long to create_tmp_field, so it converts all
      BIT fields to 64-bit longs.
    - Added comments.

  sql/sql_union.cc@stripped, 2007-08-30 17:49:19+03:00, timour@stripped +7 -3
    - In order to handle indexes over BIT fields for MEMORY tables, pass the extra
      parameter param->bit_fields_as_long to create_tmp_field, so it converts all
      BIT fields to 64-bit longs.
    - Added comments.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	timour
# Host:	zmeiux.home
# Root:	/home/tkatchaounov/mysql/src/5.2-1110

--- 1.372/sql/sql_class.h	2007-08-09 10:24:02 +03:00
+++ 1.373/sql/sql_class.h	2007-08-30 17:49:19 +03:00
@@ -2074,6 +2074,12 @@
   */
   bool precomputed_group_by;
   bool force_copy_fields;
+  /*
+    If TRUE, create_tmp_field called from create_tmp_table will convert
+    all BIT fields to 64-bit longs. This is a workaround the limitation
+    that MEMORY tables cannot index BIT columns.
+  */
+  bool bit_fields_as_long;
 
   TMP_TABLE_PARAM()
     :copy_field(0), group_parts(0),
@@ -2109,7 +2115,7 @@
   void cleanup();
   bool create_result_table(THD *thd, List<Item> *column_types,
                            bool is_distinct, ulonglong options,
-                           const char *alias);
+                           const char *alias, bool bit_fields_as_long);
 };
 
 /* Base subselect interface class */

--- 1.554/sql/sql_select.cc	2007-08-17 12:45:05 +03:00
+++ 1.555/sql/sql_select.cc	2007-08-30 17:49:19 +03:00
@@ -11332,7 +11332,13 @@
       group=0;					// Can't use group key
     else for (ORDER *tmp=group ; tmp ; tmp=tmp->next)
     {
-      (*tmp->item)->marker=4;			// Store null in key
+      /*
+        marker == 4 means two things:
+        - store NULLs in the key, and
+        - convert BIT fields to 64-bit long, needed because MEMORY tables
+          can't index BIT fields.
+      */
+      (*tmp->item)->marker= 4;
       if ((*tmp->item)->max_length >= CONVERT_IF_BIGGER_TO_BLOB)
 	using_unique_constraint=1;
     }
@@ -11518,9 +11524,6 @@
 	write rows to the temporary table.
 	We here distinguish between UNION and multi-table-updates by the fact
 	that in the later case group is set to the row pointer.
-
-        The test for item->marker == 4 is ensure we don't create a group-by
-        key over a bit field as heap tables can't handle that.
       */
       Field *new_field= (param->schema_table) ?
         create_tmp_field_for_schema(thd, item, table) :
@@ -11529,7 +11532,15 @@
                          group != 0,
                          !force_copy_fields &&
                            (not_all_columns || group !=0),
-                         item->marker == 4, force_copy_fields,
+                         /*
+                           If item->marker == 4 then we force create_tmp_field
+                           to create a 64-bit longs for BIT fields because HEAP
+                           tables can't index BIT fields directly. We do the same
+                           for distinct, as we want the distinct index to be
+                           usable in this case too.
+                         */
+                         item->marker == 4 || param->bit_fields_as_long,
+                         force_copy_fields,
                          param->convert_blob_length);
 
       if (!new_field)

--- 1.83/sql/sql_derived.cc	2007-04-23 14:16:45 +03:00
+++ 1.84/sql/sql_derived.cc	2007-08-30 17:49:19 +03:00
@@ -137,8 +137,9 @@
       SELECT is last SELECT of UNION).
     */
     if ((res= derived_result->create_result_table(thd, &unit->types, FALSE,
-                                                 create_options,
-                                                 orig_table_list->alias)))
+                                                  create_options,
+                                                  orig_table_list->alias,
+                                                  FALSE)))
       goto exit;
 
     table= derived_result->table;

--- 1.155/sql/sql_union.cc	2007-08-09 10:24:02 +03:00
+++ 1.156/sql/sql_union.cc	2007-08-30 17:49:19 +03:00
@@ -101,6 +101,8 @@
       is_union_distinct  if set, the temporary table will eliminate
                          duplicates on insert
       options            create options
+      table_alias        name of the temporary table
+      bit_fields_as_long convert bit fields to ulonglong
 
   DESCRIPTION
     Create a temporary table that is used to store the result of a UNION,
@@ -114,15 +116,17 @@
 bool
 select_union::create_result_table(THD *thd_arg, List<Item> *column_types,
                                   bool is_union_distinct, ulonglong options,
-                                  const char *alias)
+                                  const char *table_alias,
+                                  bool bit_fields_as_long)
 {
   DBUG_ASSERT(table == 0);
   tmp_table_param.init();
   tmp_table_param.field_count= column_types->elements;
+  tmp_table_param.bit_fields_as_long= bit_fields_as_long;
 
   if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
                                  (ORDER*) 0, is_union_distinct, 1,
-                                 options, HA_POS_ERROR, (char*) alias)))
+                                 options, HA_POS_ERROR, (char*) table_alias)))
     return TRUE;
   table->file->extra(HA_EXTRA_WRITE_CACHE);
   table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
@@ -364,7 +368,7 @@
       create_options= create_options | TMP_TABLE_FORCE_MYISAM;
 
     if (union_result->create_result_table(thd, &types, test(union_distinct),
-                                          create_options, ""))
+                                          create_options, "", FALSE))
       goto err;
     bzero((char*) &result_table_list, sizeof(result_table_list));
     result_table_list.db= (char*) "";

--- 1.165/sql/item_subselect.cc	2007-08-17 12:45:04 +03:00
+++ 1.166/sql/item_subselect.cc	2007-08-30 17:49:19 +03:00
@@ -2893,7 +2893,7 @@
   if (tmp_result_sink->create_result_table(
                          thd, tmp_columns, TRUE,
                          thd->options | TMP_TABLE_ALL_COLUMNS,
-                         "materialized subselect"))
+                         "materialized subselect", TRUE))
     DBUG_RETURN(TRUE);
 
   tmp_table= tmp_result_sink->table;

--- 1.12/sql/sql_cursor.cc	2006-12-31 02:06:36 +02:00
+++ 1.13/sql/sql_cursor.cc	2007-08-30 17:49:19 +03:00
@@ -662,7 +662,8 @@
 {
   DBUG_ASSERT(table == 0);
   if (create_result_table(unit->thd, unit->get_unit_column_types(),
-                          FALSE, thd->options | TMP_TABLE_ALL_COLUMNS, ""))
+                          FALSE, thd->options | TMP_TABLE_ALL_COLUMNS, "",
+                          FALSE))
     return TRUE;
   return FALSE;
 }

--- 1.2/mysql-test/r/subselect_mat.result	2007-08-17 17:22:41 +03:00
+++ 1.3/mysql-test/r/subselect_mat.result	2007-08-30 17:49:19 +03:00
@@ -74,9 +74,6 @@
 2	DEPENDENT SUBQUERY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in (select 1 AS `Not_used`
from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in (select 1 AS `Not_used`
from `test`.`t2_16` where ((`test`.`t2_16`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_16`.`a1`) = `test`.`t2_16`.`b1`))))
 select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
@@ -91,9 +88,6 @@
 2	DEPENDENT SUBQUERY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`)
in (select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from `test`.`t2_16`
where ((`test`.`t2_16`.`b1` > _latin1'0') and (<cache>(`test`.`t1_16`.`a1`) =
`test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>((`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`),(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a2`)
in (select `test`.`t2_16`.`b1` AS `b1`,`test`.`t2_16`.`b2` AS `b2` from `test`.`t2_16`
where ((`test`.`t2_16`.`b1` > _latin1'0') and (<cache>(`test`.`t1_16`.`a1`) =
`test`.`t2_16`.`b1`) and (<cache>(`test`.`t1_16`.`a2`) = `test`.`t2_16`.`b2`))))
 select left(a1,7), left(a2,7)
 from t1_16
 where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -108,9 +102,6 @@
 2	SUBQUERY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize>
(select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16`
where (`test`.`t2_16`.`b1` > _latin1'0') ),
<primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on
distinct_key)))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize>
(select substr(`test`.`t2_16`.`b1`,1,16) AS `substring(b1,1,16)` from `test`.`t2_16`
where (`test`.`t2_16`.`b1` > _latin1'0') ),
<primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on
distinct_key)))
 select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -125,9 +116,6 @@
 2	DEPENDENT SUBQUERY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in (select
group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16`
group by `test`.`t2_16`.`b2` having (<cache>(`test`.`t1_16`.`a1`) =
<ref_null_helper>(group_concat(`test`.`t2_16`.`b1` separator ',')))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in (select
group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from `test`.`t2_16`
group by `test`.`t2_16`.`b2` having (<cache>(`test`.`t1_16`.`a1`) =
<ref_null_helper>(group_concat(`test`.`t2_16`.`b1` separator ',')))))
 select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select group_concat(b1) from t2_16 group by b2);
@@ -143,9 +131,6 @@
 2	SUBQUERY	t2_16	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_16` group by `test`.`t2_16`.`b2` ),
<primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on
distinct_key)))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_16`.`a1`,7) AS `left(a1,7)`,left(`test`.`t1_16`.`a2`,7)
AS `left(a2,7)` from `test`.`t1_16` where
<in_optimizer>(`test`.`t1_16`.`a1`,`test`.`t1_16`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_16`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_16` group by `test`.`t2_16`.`b2` ),
<primary_index_lookup>(`test`.`t1_16`.`a1` in <temporary table> on
distinct_key)))
 select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select group_concat(b1) from t2_16 group by b2);
@@ -187,9 +172,6 @@
 2	DEPENDENT SUBQUERY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in (select 1 AS `Not_used`
from `test`.`t2_512` where ((`test`.`t2_512`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_512`.`a1`) = `test`.`t2_512`.`b1`))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in (select 1 AS `Not_used`
from `test`.`t2_512` where ((`test`.`t2_512`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_512`.`a1`) = `test`.`t2_512`.`b1`))))
 select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
@@ -204,9 +186,6 @@
 2	DEPENDENT SUBQUERY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>((`test`.`t1_512`.`a1`,`test`.`t1_512`.`a2`),(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a2`)
in (select `test`.`t2_512`.`b1` AS `b1`,`test`.`t2_512`.`b2` AS `b2` from `test`.`t2_512`
where ((`test`.`t2_512`.`b1` > _latin1'0') and (<cache>(`test`.`t1_512`.`a1`) =
`test`.`t2_512`.`b1`) and (<cache>(`test`.`t1_512`.`a2`) = `test`.`t2_512`.`b2`))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>((`test`.`t1_512`.`a1`,`test`.`t1_512`.`a2`),(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a2`)
in (select `test`.`t2_512`.`b1` AS `b1`,`test`.`t2_512`.`b2` AS `b2` from `test`.`t2_512`
where ((`test`.`t2_512`.`b1` > _latin1'0') and (<cache>(`test`.`t1_512`.`a1`) =
`test`.`t2_512`.`b1`) and (<cache>(`test`.`t1_512`.`a2`) = `test`.`t2_512`.`b2`))))
 select left(a1,7), left(a2,7)
 from t1_512
 where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -221,9 +200,6 @@
 2	SUBQUERY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512`
where (`test`.`t2_512`.`b1` > _latin1'0') ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key)))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select substr(`test`.`t2_512`.`b1`,1,512) AS `substring(b1,1,512)` from `test`.`t2_512`
where (`test`.`t2_512`.`b1` > _latin1'0') ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key)))
 select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -238,9 +214,6 @@
 2	SUBQUERY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_512` group by `test`.`t2_512`.`b2` ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key)))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_512` group by `test`.`t2_512`.`b2` ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key)))
 select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select group_concat(b1) from t2_512 group by b2);
@@ -256,9 +229,6 @@
 2	SUBQUERY	t2_512	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_512` group by `test`.`t2_512`.`b2` ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key)))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_512`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_512`.`a2`,7) AS `left(a2,7)` from `test`.`t1_512` where
<in_optimizer>(`test`.`t1_512`.`a1`,`test`.`t1_512`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_512`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_512` group by `test`.`t2_512`.`b2` ),
<primary_index_lookup>(`test`.`t1_512`.`a1` in <temporary table> on
distinct_key)))
 select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select group_concat(b1) from t2_512 group by b2);
@@ -300,9 +270,6 @@
 2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in (select 1 AS
`Not_used` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1024`.`a1`) = `test`.`t2_1024`.`b1`))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in (select 1 AS
`Not_used` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1024`.`a1`) = `test`.`t2_1024`.`b1`))))
 select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
@@ -317,9 +284,6 @@
 2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>((`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a2`),(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a2`)
in (select `test`.`t2_1024`.`b1` AS `b1`,`test`.`t2_1024`.`b2` AS `b2` from
`test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1024`.`a1`) = `test`.`t2_1024`.`b1`) and
(<cache>(`test`.`t1_1024`.`a2`) = `test`.`t2_1024`.`b2`))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>((`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a2`),(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a2`)
in (select `test`.`t2_1024`.`b1` AS `b1`,`test`.`t2_1024`.`b2` AS `b2` from
`test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1024`.`a1`) = `test`.`t2_1024`.`b1`) and
(<cache>(`test`.`t1_1024`.`a2`) = `test`.`t2_1024`.`b2`))))
 select left(a1,7), left(a2,7)
 from t1_1024
 where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -334,9 +298,6 @@
 2	DEPENDENT SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in (select 1 AS
`Not_used` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1024`.`a1`) = substr(`test`.`t2_1024`.`b1`,1,1024)))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in (select 1 AS
`Not_used` from `test`.`t2_1024` where ((`test`.`t2_1024`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1024`.`a1`) = substr(`test`.`t2_1024`.`b1`,1,1024)))))
 select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -351,9 +312,6 @@
 2	SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1024` group by `test`.`t2_1024`.`b2` ),
<primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on
distinct_key)))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1024` group by `test`.`t2_1024`.`b2` ),
<primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on
distinct_key)))
 select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select group_concat(b1) from t2_1024 group by b2);
@@ -369,9 +327,6 @@
 2	SUBQUERY	t2_1024	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1024` group by `test`.`t2_1024`.`b2` ),
<primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on
distinct_key)))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1024`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1024`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1024` where
<in_optimizer>(`test`.`t1_1024`.`a1`,`test`.`t1_1024`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1024`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1024` group by `test`.`t2_1024`.`b2` ),
<primary_index_lookup>(`test`.`t1_1024`.`a1` in <temporary table> on
distinct_key)))
 select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select group_concat(b1) from t2_1024 group by b2);
@@ -413,9 +368,6 @@
 2	DEPENDENT SUBQUERY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in (select 1 AS
`Not_used` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1025`.`a1`) = `test`.`t2_1025`.`b1`))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in (select 1 AS
`Not_used` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1025`.`a1`) = `test`.`t2_1025`.`b1`))))
 select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
@@ -430,9 +382,6 @@
 2	DEPENDENT SUBQUERY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>((`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a2`),(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a2`)
in (select `test`.`t2_1025`.`b1` AS `b1`,`test`.`t2_1025`.`b2` AS `b2` from
`test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1025`.`a1`) = `test`.`t2_1025`.`b1`) and
(<cache>(`test`.`t1_1025`.`a2`) = `test`.`t2_1025`.`b2`))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>((`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a2`),(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a2`)
in (select `test`.`t2_1025`.`b1` AS `b1`,`test`.`t2_1025`.`b2` AS `b2` from
`test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1025`.`a1`) = `test`.`t2_1025`.`b1`) and
(<cache>(`test`.`t1_1025`.`a2`) = `test`.`t2_1025`.`b2`))))
 select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -447,9 +396,6 @@
 2	DEPENDENT SUBQUERY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 Warnings:
 Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in (select 1 AS
`Not_used` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1025`.`a1`) = substr(`test`.`t2_1025`.`b1`,1,1025)))))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in (select 1 AS
`Not_used` from `test`.`t2_1025` where ((`test`.`t2_1025`.`b1` > _latin1'0') and
(<cache>(`test`.`t1_1025`.`a1`) = substr(`test`.`t2_1025`.`b1`,1,1025)))))
 select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -464,9 +410,6 @@
 2	SUBQUERY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1025` group by `test`.`t2_1025`.`b2` ),
<primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on
distinct_key)))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1025` group by `test`.`t2_1025`.`b2` ),
<primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on
distinct_key)))
 select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select group_concat(b1) from t2_1025 group by b2);
@@ -482,9 +425,6 @@
 2	SUBQUERY	t2_1025	ALL	NULL	NULL	NULL	NULL	3	100.00	Using filesort
 Warnings:
 Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1025` group by `test`.`t2_1025`.`b2` ),
<primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on
distinct_key)))
-show warnings;
-Level	Code	Message
-Note	1003	select left(`test`.`t1_1025`.`a1`,7) AS
`left(a1,7)`,left(`test`.`t1_1025`.`a2`,7) AS `left(a2,7)` from `test`.`t1_1025` where
<in_optimizer>(`test`.`t1_1025`.`a1`,`test`.`t1_1025`.`a1` in ( <materialize>
(select group_concat(`test`.`t2_1025`.`b1` separator ',') AS `group_concat(b1)` from
`test`.`t2_1025` group by `test`.`t2_1025`.`b2` ),
<primary_index_lookup>(`test`.`t1_1025`.`a1` in <temporary table> on
distinct_key)))
 select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select group_concat(b1) from t2_1025 group by b2);
@@ -492,6 +432,53 @@
 1 - 01x	2 - 01x
 1 - 02x	2 - 02x
 drop table t1_1025, t2_1025, t3_1025;
+create table t1bit (a1 bit(3), a2 bit(3));
+create table t2bit (b1 bit(3), b2 bit(3));
+insert into t1bit values (b'000', b'100');
+insert into t1bit values (b'001', b'101');
+insert into t1bit values (b'010', b'110');
+insert into t2bit values (b'001', b'101');
+insert into t2bit values (b'010', b'110');
+insert into t2bit values (b'110', b'111');
+set @@optimizer_switch=no_semijoin;
+explain extended select bin(a1), bin(a2)
+from t1bit
+where (a1, a2) in (select b1, b2 from t2bit);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1bit	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	SUBQUERY	t2bit	ALL	NULL	NULL	NULL	NULL	3	100.00	
+Warnings:
+Note	1003	select conv(`test`.`t1bit`.`a1`,10,2) AS
`bin(a1)`,conv(`test`.`t1bit`.`a2`,10,2) AS `bin(a2)` from `test`.`t1bit` where
<in_optimizer>((`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`),(`test`.`t1bit`.`a1`,`test`.`t1bit`.`a2`)
in ( <materialize> (select `test`.`t2bit`.`b1` AS `b1`,`test`.`t2bit`.`b2` AS `b2`
from `test`.`t2bit` ), <primary_index_lookup>(`test`.`t1bit`.`a1` in <temporary
table> on distinct_key)))
+select bin(a1), bin(a2)
+from t1bit
+where (a1, a2) in (select b1, b2 from t2bit);
+bin(a1)	bin(a2)
+1	101
+10	110
+drop table t1bit, t2bit;
+create table t1bb (a1 bit(3), a2 blob(3));
+create table t2bb (b1 bit(3), b2 blob(3));
+insert into t1bb values (b'000', '100');
+insert into t1bb values (b'001', '101');
+insert into t1bb values (b'010', '110');
+insert into t2bb values (b'001', '101');
+insert into t2bb values (b'010', '110');
+insert into t2bb values (b'110', '111');
+explain extended select bin(a1), a2
+from t1bb
+where (a1, a2) in (select b1, b2 from t2bb);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1bb	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+2	DEPENDENT SUBQUERY	t2bb	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
+Warnings:
+Note	1003	select conv(`test`.`t1bb`.`a1`,10,2) AS `bin(a1)`,`test`.`t1bb`.`a2` AS `a2`
from `test`.`t1bb` where
<in_optimizer>((`test`.`t1bb`.`a1`,`test`.`t1bb`.`a2`),(`test`.`t1bb`.`a1`,`test`.`t1bb`.`a2`)
in (select `test`.`t2bb`.`b1` AS `b1`,`test`.`t2bb`.`b2` AS `b2` from `test`.`t2bb` where
((<cache>(`test`.`t1bb`.`a1`) = `test`.`t2bb`.`b1`) and
(<cache>(`test`.`t1bb`.`a2`) = `test`.`t2bb`.`b2`))))
+select bin(a1), a2
+from t1bb
+where (a1, a2) in (select b1, b2 from t2bb);
+bin(a1)	a2
+1	101
+10	110
+drop table t1bb, t2bb;
 /******************************************************************************
 * IN=>EXISTS transformation produces 'ref' lookups.                           *
 ******************************************************************************/

--- 1.2/mysql-test/t/subselect_mat.test	2007-08-17 17:22:41 +03:00
+++ 1.3/mysql-test/t/subselect_mat.test	2007-08-30 17:49:19 +03:00
@@ -38,6 +38,12 @@
 set @@optimizer_switch=no_semijoin;
 -- run query
 
+-- materialize the result of temp tables
+-- GROUP BY a1,a2 ORDER BY a2
+
+-- materialize the result of subquery over temp-table view
+ 
+
 /******************************************************************************
 * Test NULL semantics.                                                        *
 ******************************************************************************/
@@ -105,7 +111,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_16
 where a1 in (select b1 from t2_16 where b1 > '0');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_16
@@ -115,7 +120,6 @@
 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');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_16
@@ -125,7 +129,6 @@
 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');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_16
@@ -137,7 +140,6 @@
 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);
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_16
@@ -148,7 +150,6 @@
 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);
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_16
@@ -195,7 +196,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_512
 where a1 in (select b1 from t2_512 where b1 > '0');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_512
@@ -205,7 +205,6 @@
 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');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_512
@@ -215,7 +214,6 @@
 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');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_512
@@ -227,7 +225,6 @@
 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);
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_512
@@ -238,7 +235,6 @@
 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);
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_512
@@ -285,7 +281,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select b1 from t2_1024 where b1 > '0');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1024
@@ -295,7 +290,6 @@
 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');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1024
@@ -305,7 +299,6 @@
 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');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1024
@@ -317,7 +310,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select group_concat(b1) from t2_1024 group by b2);
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1024
@@ -328,7 +320,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_1024
 where a1 in (select group_concat(b1) from t2_1024 group by b2);
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1024
@@ -375,7 +366,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select b1 from t2_1025 where b1 > '0');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1025
@@ -385,7 +375,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1025
@@ -395,7 +384,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1025
@@ -407,7 +395,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select group_concat(b1) from t2_1025 group by b2);
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1025
@@ -418,7 +405,6 @@
 explain extended select left(a1,7), left(a2,7)
 from t1_1025
 where a1 in (select group_concat(b1) from t2_1025 group by b2);
-show warnings;
 
 select left(a1,7), left(a2,7)
 from t1_1025
@@ -426,7 +412,52 @@
 
 drop table t1_1025, t2_1025, t3_1025;
 
--- test for BIT fields - possibly problem with MEMORY
+-- test for BIT fields
+create table t1bit (a1 bit(3), a2 bit(3));
+create table t2bit (b1 bit(3), b2 bit(3));
+
+insert into t1bit values (b'000', b'100');
+insert into t1bit values (b'001', b'101');
+insert into t1bit values (b'010', b'110');
+
+insert into t2bit values (b'001', b'101');
+insert into t2bit values (b'010', b'110');
+insert into t2bit values (b'110', b'111');
+
+set @@optimizer_switch=no_semijoin;
+
+explain extended select bin(a1), bin(a2)
+from t1bit
+where (a1, a2) in (select b1, b2 from t2bit);
+
+select bin(a1), bin(a2)
+from t1bit
+where (a1, a2) in (select b1, b2 from t2bit);
+
+drop table t1bit, t2bit;
+
+-- test mixture of BIT and BLOB
+create table t1bb (a1 bit(3), a2 blob(3));
+create table t2bb (b1 bit(3), b2 blob(3));
+
+insert into t1bb values (b'000', '100');
+insert into t1bb values (b'001', '101');
+insert into t1bb values (b'010', '110');
+
+insert into t2bb values (b'001', '101');
+insert into t2bb values (b'010', '110');
+insert into t2bb values (b'110', '111');
+
+explain extended select bin(a1), a2
+from t1bb
+where (a1, a2) in (select b1, b2 from t2bb);
+
+select bin(a1), a2
+from t1bb
+where (a1, a2) in (select b1, b2 from t2bb);
+
+drop table t1bb, t2bb;
+
 
 /******************************************************************************
 * IN=>EXISTS transformation produces 'ref' lookups.                           *
Thread
• bk commit into 5.2 tree (timour:1.2558)	timour	30 Aug