#At file:///work/bzr/mysql-6.0-qa/

2709 Horst Hunger 2009-01-15 [merge]
due to merge conflicts.
removed:
include/lf.h.moved
include/my_bit.h.moved
mysql-test/suite/sys_vars/t/rpl_slave_exec_mode_basic.test
mysys/lf_alloc-pin.c.moved
mysys/lf_dynarray.c.moved
mysys/lf_hash.c.moved
win/build-vs9.bat.moved
win/build-vs9_x64.bat.moved
added:
config/ac-macros/libmemcached.m4
config/ac-macros/search_for_lib.m4
mysql-test/r/innodb_bug34053.result
mysql-test/t/innodb_bug34053.test
modified:
.bzr-mysql/default.conf
BUILD/compile-dist
BUILD/compile-pentium-gcov
BUILD/compile-solaris-amd64
Makefile.am
configure.in
libmysqld/examples/test-run
mysql-test/create-test-result
mysql-test/mysql-test-run.pl
mysql-test/r/func_math.result
mysql-test/r/group_by.result
mysql-test/r/innodb_mrr.result
mysql-test/r/join_cache.result
mysql-test/r/subselect.result
mysql-test/r/subselect2.result
mysql-test/r/subselect3.result
mysql-test/r/subselect3_jcl6.result
mysql-test/r/subselect_mat.result
mysql-test/r/subselect_no_mat.result
mysql-test/r/subselect_no_opts.result
mysql-test/r/subselect_no_semijoin.result
mysql-test/r/subselect_sj.result
mysql-test/r/subselect_sj2.result
mysql-test/r/subselect_sj2_jcl6.result
mysql-test/r/subselect_sj_jcl6.result
mysql-test/r/type_varchar.result
mysql-test/suite/funcs_1/t/disabled.def
mysql-test/suite/maria/t/maria-lock.test
mysql-test/t/func_math.test
mysql-test/t/innodb_mrr.test
mysql-test/t/join_cache.test
mysql-test/t/partition_not_windows.test
mysql-test/t/subselect.test
mysql-test/t/subselect3.test
mysql-test/t/subselect_mat.test
netware/BUILD/nwbootstrap
sql/ha_ndbcluster.cc
sql/ha_partition.h
sql/handler.h
sql/item_cmpfunc.h
sql/item_func.cc
sql/item_subselect.cc
sql/item_subselect.h
sql/mysql_priv.h
sql/mysqld.cc
sql/opt_range.cc
sql/opt_range.h
sql/protocol.cc
sql/records.h
sql/sql_base.cc
sql/sql_class.h
sql/sql_join_cache.cc
sql/sql_lex.h
sql/sql_select.cc
sql/sql_select.h
sql/sql_test.cc
sql/sql_union.cc
sql/table.h
storage/archive/support/archive_read_test.slap
storage/falcon/TransformLib/StringTransform.cpp
storage/innobase/handler/ha_innodb.cc
storage/innobase/pars/make_bison.sh
storage/innobase/pars/make_flex.sh
storage/maria/ma_test_big.sh
storage/myisam/ha_myisam.cc
storage/myisam/ha_myisam.h
storage/myisam/mi_test_all.sh
storage/myisam/myisam_backup_engine.cc
storage/ndb/demos/run_demo1-PS.sh
storage/ndb/demos/run_demo1-SS.sh
support-files/my-small.cnf.sh
support-files/mysql.spec.sh

+++ b/.bzr-mysql/default.conf 2009-01-10 10:56:50 +0000
@@ -1,5 +1,5 @@
[MYSQL]
-tree_location = bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-maria
+tree_location = bzr+ssh://bk-internal.mysql.com/bzrroot/server/mysql-6.0-opt/
-tree_name = "mysql-6.0"
+tree_name = "mysql-6.0-opt"

+++ b/BUILD/compile-dist 2008-12-29 12:05:15 +0000
@@ -11,16 +11,33 @@ test -f Makefile && make maintainer-clea
path=`dirname $0`
. $path/autorun.sh

+gmake=
+for x in gmake gnumake make; do
+ if $x --version 2>/dev/null | grep GNU > /dev/null; then
+ gmake=$x
+ break;
+ fi
+done
+
+if [ -z "$gmake" ]; then
+ # Our build may not depend on GNU make, but I wouldn't count on it
+ echo "Please install GNU make, and ensure it is in your path as gnumake, gmake, or make" >&2
+ exit 2
+fi
+
# Default to gcc for CC and CXX
if test -z "$CXX" ; then
+ export CXX
CXX=gcc
# Set some required compile options
if test -z "$CXXFLAGS" ; then
+ export CXXFLAGS
CXXFLAGS="-felide-constructors -fno-exceptions -fno-rtti"
fi
fi

if test -z "$CC" ; then
+ export CC
CC=gcc
fi

@@ -28,32 +45,18 @@ fi
# Use ccache, if available
if ccache -V > /dev/null 2>&1
then
- if echo "$CC" | grep "ccache" > /dev/null
+ if echo "$CC" | grep -v ccache > /dev/null
then
- :
- else
+ export CC
CC="ccache $CC"
fi
- if echo "$CXX" | grep "ccache" > /dev/null
+ if echo "$CXX" | grep -v ccache > /dev/null
then
- :
- else
+ export CXX
CXX="ccache $CXX"
fi
fi

-if test -z "$MAKE"
-then
- if gmake -v > /dev/null 2>&1
- then
- MAKE="gmake"
- else
- MAKE="make"
- fi
-fi
-
-export CC CXX MAKE
-
# Make sure to enable all features that affect "make dist"
# Remember that configure restricts the man pages to the configured features !
./configure \
@@ -61,5 +64,5 @@ export CC CXX MAKE
--with-embedded-server \
--with-falcon \
--with-ndbcluster
-$MAKE
+$gmake


+++ b/BUILD/compile-pentium-gcov 2008-12-31 13:18:04 +0000
@@ -7,7 +7,7 @@ CCACHE_GCOV_VERSION_ENABLED=0
if ccache -V > /dev/null 2>&1
then
CCACHE_VER=`ccache -V | head -1 | sed s/"ccache version "//`
- if test "$CCACHE_VER" == "2.4-gcov"
+ if test "$CCACHE_VER" = "2.4-gcov"
then
CCACHE_GCOV_VERSION_ENABLED=1
else
@@ -20,7 +20,8 @@ export CCACHE_GCOV_VERSION_ENABLED
path=`dirname $0`
. "$path/SETUP.sh"

-export LDFLAGS="$gcov_link_flags"
+LDFLAGS="$gcov_link_flags"
+export LDFLAGS

extra_flags="$pentium_cflags $debug_cflags $max_cflags $gcov_compile_flags"
c_warnings="$c_warnings $debug_extra_warnings"

+++ b/BUILD/compile-solaris-amd64 2008-12-31 13:18:04 +0000
@@ -1,16 +1,18 @@
-#!/usr/bin/bash
+#!/bin/sh

-function _find_mysql_root () (
+_find_mysql_root ()
+{
+ (
while [ "x$PWD" != "x/" ]; do
# Check if some directories are present
if [ -d BUILD -a -d sql -a -d mysys ]; then
echo "$PWD"
- return 0
+ break
fi
cd ..
done
- return 1
)
+}

make -k clean || true
/bin/rm -f */.deps/*.P config.cache
@@ -28,7 +30,7 @@ CFLAGS="$warning_flags $compiler_flags"
CXXFLAGS=""
LDFLAGS="-O3 -g -static-libgcc"
LIBS=-lmtmalloc
-root=$(_find_mysql_root)
+root=`_find_mysql_root`

$root/configure \
--prefix=/usr/local/mysql \

+++ b/Makefile.am 2008-12-17 18:40:14 +0000
@@ -178,8 +178,8 @@ test-bt:
fi
-if [ -d mysql-test/suite/nist ] ; then \
cd mysql-test ; MTR_BUILD_THREAD=auto \
fi
-if [ -e bin/mysqltest_embedded -o -e libmysqld/examples/mysqltest_embedded ] ; then \
cd mysql-test ; MTR_BUILD_THREAD=auto \

+++ b/config/ac-macros/libmemcached.m4 2008-12-20 01:41:31 +0000
@@ -0,0 +1,29 @@
+dnl
+dnl Copyright (C) 2008 Sun Microsystems
+dnl
+dnl This program is free software; you can redistribute it and/or modify
+dnl it under the terms of the GNU General Public License as published by
+dnl the Free Software Foundation; version 2 of the License.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU General Public License
+dnl along with this program; if not, write to the Free Software
+dnl Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+dnl
+dnl Check to find libmemcached.
+
+AC_DEFUN([_SEARCH_FOR_LIBMEMCACHED],[
+ SEARCH_FOR_LIB(memcached,memcached_create,[libmemcached/memcached.h])
+ AM_CONDITIONAL([BUILD_MEMCACHED],[test "$ac_cv_have_memcached" = "yes"])
+])
+
+dnl Split this into a _hidden function and a public with a require. This way
+dnl any number of plugins can call the code and the real guts only get
+dnl called once.
+AC_DEFUN([WITH_LIBMEMCACHED],[
+ AC_REQUIRE([_SEARCH_FOR_LIBMEMCACHED])
+])

+++ b/config/ac-macros/search_for_lib.m4 2008-12-20 01:41:31 +0000
@@ -0,0 +1,107 @@
+dnl
+dnl Copyright (C) 2008 Sun Microsystems
+dnl
+dnl This program is free software; you can redistribute it and/or modify
+dnl it under the terms of the GNU General Public License as published by
+dnl the Free Software Foundation; version 2 of the License.
+dnl
+dnl This program is distributed in the hope that it will be useful,
+dnl but WITHOUT ANY WARRANTY; without even the implied warranty of
+dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+dnl GNU General Public License for more details.
+dnl
+dnl You should have received a copy of the GNU General Public License
+dnl along with this program; if not, write to the Free Software
+dnl Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
+dnl
+dnl Contributed with Love by Drizzle. If you make any modifications, they'd
+dnl love to know about them.
+dnl
+dnl SEARCH_FOR_LIB(LIB, FUNCTIONS, FUNCTION,
+dnl [ACTION-IF-NOT-FOUND],
+dnl [LIBS_TO_ADD])
+
+AC_DEFUN([SEARCH_FOR_LIB],
+[
+ AS_VAR_PUSHDEF([with_lib], [with_$1])
+ AS_VAR_PUSHDEF([ac_header], [ac_cv_header_$3])
+ AS_VAR_PUSHDEF([have_lib], [ac_cv_have_$1])
+ AS_VAR_PUSHDEF([libs_var], AS_TR_CPP([$1_LIBS]))
+ AS_VAR_PUSHDEF([cflags_var], AS_TR_CPP([$1_CFLAGS]))
+ AS_VAR_PUSHDEF([path_var], AS_TR_CPP([$1_PATH]))
+ AS_LITERAL_IF([$1],
+ [AS_VAR_PUSHDEF([ac_lib], [ac_cv_lib_$1_$2])],
+ [AS_VAR_PUSHDEF([ac_lib], [ac_cv_lib_$1''_$2])])
+
+ AS_IF([test "x$prefix" = "xNONE"],
+ [AS_VAR_SET([path_var],["$ac_default_prefix"])],
+ [AS_VAR_SET([path_var],["$prefix"])])
+
+
+ AC_ARG_WITH([$1],
+ [AS_HELP_STRING([--with-$1@<:@=DIR@:>@],
+ [Use lib$1 in DIR])],
+ [ AS_VAR_SET([with_lib], [$withval]) ],
+ [ AS_VAR_SET([with_lib], [yes]) ])
+
+ AS_IF([test AS_VAR_GET([with_lib]) = yes],[
+ AC_CHECK_HEADERS([$3])
+
+ my_save_LIBS="$LIBS"
+ LIBS="$5"
+ AC_CHECK_LIB($1, $2)
+ AS_VAR_SET([libs_var],[${LIBS}])
+ LIBS="${my_save_LIBS}"
+ AS_VAR_SET([cflags_var],[""])
+ AS_IF([test AS_VAR_GET([ac_header]) = "$3" -a AS_VAR_GET([ac_lib]) = yes],
+ [AS_VAR_SET([have_lib],[yes])
+ AS_VAR_SET([path_var],[$PATH])
+ ],
+ [AS_VAR_SET([have_lib],[no])
+ AS_VAR_SET([with_lib],["AS_VAR_GET([path_var]) /usr/local /opt/csw /opt/local"])
+ ])
+ ])
+ AS_IF([test "AS_VAR_GET([with_lib])" != yes],[
+ for libloc in AS_VAR_GET([with_lib])
+ do
+ AC_MSG_CHECKING(for $1 in $libloc)
+ if test -f $libloc/$3 -a -f $libloc/lib$1.a
+ then
+ owd=`pwd`
+ if cd $libloc; then libloc=`pwd`; cd $owd; fi
+ AS_VAR_SET([cflags_var],[-I$libloc])
+ AS_VAR_SET([libs_var],["-L$libloc -l$1"])
+ AS_VAR_SET([path_var],["$libloc:$PATH"])
+ AS_VAR_SET([have_lib],[yes])
+ AC_MSG_RESULT([yes])
+ break
+ elif test -f $libloc/include/$3 -a -f $libloc/lib/lib$1.a; then
+ owd=`pwd`
+ if cd $libloc; then libloc=`pwd`; cd $owd; fi
+ AS_VAR_SET([cflags_var],[-I$libloc/include])
+ AS_VAR_SET([libs_var],["-L$libloc/lib -l$1"])
+ AS_VAR_SET([path_var],["$libloc/bin:$PATH"])
+ AS_VAR_SET([have_lib],[yes])
+ AC_MSG_RESULT([yes])
+ break
+ else
+ AC_MSG_RESULT([no])
+ AS_VAR_SET([have_lib],[no])
+ fi
+ done
+ ])
+ AS_IF([test AS_VAR_GET([have_lib]) = no],[
+ AC_MSG_WARN([$3 or lib$1.a not found. Try installing $1 developement packages])
+ $4
+ ])
+ AC_SUBST(libs_var)
+ AC_SUBST(cflags_var)
+ AC_SUBST(path_var)
+ AS_VAR_POPDEF([with_lib])
+ AS_VAR_POPDEF([ac_header])
+ AS_VAR_POPDEF([libs_var])
+ AS_VAR_POPDEF([cflags_var])
+ AS_VAR_POPDEF([path_var])
+ AS_VAR_POPDEF([have_lib])
+ AS_VAR_POPDEF([ac_lib])
+])

+++ b/configure.in 2008-12-31 13:49:36 +0000
@@ -11,7 +11,7 @@ AC_CANONICAL_SYSTEM
# When changing major version number please also check switch statement
# in mysqlbinlog::check_master_version().

-AM_INIT_AUTOMAKE(mysql, 6.0.9-alpha)
+AM_INIT_AUTOMAKE(mysql, 6.0.10-alpha)
AM_CONFIG_HEADER([include/config.h:config.h.in])

NDB_VERSION_MAJOR=6
sinclude(config/ac-macros/ssl.m4)
sinclude(config/ac-macros/libevent.m4)
sinclude(config/ac-macros/zlib.m4)
+sinclude(config/search_for_lib.m4)
+sinclude(config/libmemcached.m4)

# Remember to add a directory sql/share/LANGUAGE
AVAILABLE_LANGUAGES="\
@@ -403,7 +405,7 @@ fi
MYSQL_PROG_AR

# libmysqlclient versioning when linked with GNU ld.
-if $LD --version 2>/dev/null| grep GNU >/dev/null 2>&1; then
+if $LD --version 2>/dev/null | grep GNU >/dev/null 2>&1; then
LD_VERSION_SCRIPT="-Wl,--version-script=\$(top_builddir)/libmysql/libmysql.ver"
AC_CONFIG_FILES(libmysql/libmysql.ver)
fi

+++ b/include/lf.h.moved 1970-01-01 00:00:00 +0000
@@ -1,260 +0,0 @@
-/* Copyright (C) 2007 MySQL AB
-
- This program is free software; you can redistribute it and/or modify
- it under the terms of the GNU General Public License as published by
- the Free Software Foundation; either version 2 of the License, or
- (at your option) any later version.
-
- This program is distributed in the hope that it will be useful,
- but WITHOUT ANY WARRANTY; without even the implied warranty of
- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
- GNU General Public License for more details.
-
- You should have received a copy of the GNU General Public License
- along with this program; if not, write to the Free Software
- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
-
-#ifndef _lf_h
-#define _lf_h
-
-#include
-
-/*
- Helpers to define both func() and _func(), where
- func() is a _func() protected by my_atomic_rwlock_wrlock()
-*/
-
-#define lock_wrap(f, t, proto_args, args, lock) \
-t _ ## f proto_args; \
-static inline t f proto_args \
-{ \
- t ret; \
- my_atomic_rwlock_wrlock(lock); \
- ret= _ ## f args; \
- my_atomic_rwlock_wrunlock(lock); \
- return ret; \
-}
-
-#define lock_wrap_void(f, proto_args, args, lock) \
-void _ ## f proto_args; \
-static inline void f proto_args \
-{ \
- my_atomic_rwlock_wrlock(lock); \
- _ ## f args; \
- my_atomic_rwlock_wrunlock(lock); \
-}
-
-#define nolock_wrap(f, t, proto_args, args) \
-t _ ## f proto_args; \
-static inline t f proto_args \
-{ \
- return _ ## f args; \
-}
-
-#define nolock_wrap_void(f, proto_args, args) \
-void _ ## f proto_args; \
-static inline void f proto_args \
-{ \
- _ ## f args; \
-}
-
-/*
- wait-free dynamic array, see lf_dynarray.c
-
- 4 levels of 256 elements each mean 4311810304 elements in an array - it
- should be enough for a while
-*/
-#define LF_DYNARRAY_LEVEL_LENGTH 256
-#define LF_DYNARRAY_LEVELS 4
-
-typedef struct {
- void * volatile level[LF_DYNARRAY_LEVELS];
- uint size_of_element;
- my_atomic_rwlock_t lock;
-} LF_DYNARRAY;
-
-typedef int (*lf_dynarray_func)(void *, void *);
-
-void lf_dynarray_init(LF_DYNARRAY *array, uint element_size);
-void lf_dynarray_destroy(LF_DYNARRAY *array);
-
-nolock_wrap(lf_dynarray_value, void *,
- (LF_DYNARRAY *array, uint idx),
- (array, idx))
-lock_wrap(lf_dynarray_lvalue, void *,
- (LF_DYNARRAY *array, uint idx),
- (array, idx),
- &array->lock)
-nolock_wrap(lf_dynarray_iterate, int,
- (LF_DYNARRAY *array, lf_dynarray_func func, void *arg),
- (array, func, arg))
-
-/*
- pin manager for memory allocator, lf_alloc-pin.c
-*/
-
-#define LF_PINBOX_PINS 4
-#define LF_PURGATORY_SIZE 10
-
-typedef void lf_pinbox_free_func(void *, void *, void*);
-
-typedef struct {
- LF_DYNARRAY pinarray;
- lf_pinbox_free_func *free_func;
- void *free_func_arg;
- uint free_ptr_offset;
- uint32 volatile pinstack_top_ver; /* this is a versioned pointer */
- uint32 volatile pins_in_array; /* number of elements in array */
-} LF_PINBOX;
-
-typedef struct {
- void * volatile pin[LF_PINBOX_PINS];
- LF_PINBOX *pinbox;
- void *stack_ends_here;
- void *purgatory;
- uint32 purgatory_count;
- uint32 volatile link;
-/* we want sizeof(LF_PINS) to be 128 to avoid false sharing */
- char pad[128-sizeof(uint32)*2
- -sizeof(LF_PINBOX *)
- -sizeof(void*)
- -sizeof(void *)*(LF_PINBOX_PINS+1)];
-} LF_PINS;
-
-/*
- shortcut macros to do an atomic_wrlock on a structure that uses pins
- (e.g. lf_hash).
-*/
-#define lf_rwlock_by_pins(PINS) \
- my_atomic_rwlock_wrlock(&(PINS)->pinbox->pinarray.lock)
-#define lf_rwunlock_by_pins(PINS) \
- my_atomic_rwlock_wrunlock(&(PINS)->pinbox->pinarray.lock)
-
-/*
- compile-time assert, to require "no less than N" pins
- it's enough if it'll fail on at least one compiler, so
- we'll enable it on GCC only, which supports zero-length arrays.
-*/
-#if defined(__GNUC__) && defined(MY_LF_EXTRA_DEBUG)
-#define LF_REQUIRE_PINS(N) \
- static const char require_pins[LF_PINBOX_PINS-N] \
- __attribute__ ((unused)); \
- static const int LF_NUM_PINS_IN_THIS_FILE= N;
-#define _lf_pin(PINS, PIN, ADDR) \
- ( \
- assert(PIN < LF_NUM_PINS_IN_THIS_FILE), \
- my_atomic_storeptr(&(PINS)->pin[PIN], (ADDR)) \
- )
-#else
-#define LF_REQUIRE_PINS(N)
-#define _lf_pin(PINS, PIN, ADDR) my_atomic_storeptr(&(PINS)->pin[PIN], (ADDR))
-#endif
-
-#define _lf_unpin(PINS, PIN) _lf_pin(PINS, PIN, NULL)
-#define lf_pin(PINS, PIN, ADDR) \
- do { \
- lf_rwlock_by_pins(PINS); \
- _lf_pin(PINS, PIN, ADDR); \
- lf_rwunlock_by_pins(PINS); \
- } while (0)
-#define lf_unpin(PINS, PIN) lf_pin(PINS, PIN, NULL)
-#define _lf_assert_pin(PINS, PIN) assert((PINS)->pin[PIN] != 0)
-#define _lf_assert_unpin(PINS, PIN) assert((PINS)->pin[PIN] == 0)
-
-void lf_pinbox_init(LF_PINBOX *pinbox, uint free_ptr_offset,
- lf_pinbox_free_func *free_func, void * free_func_arg);
-void lf_pinbox_destroy(LF_PINBOX *pinbox);
-
-lock_wrap(lf_pinbox_get_pins, LF_PINS *,
- (LF_PINBOX *pinbox, void *stack_end),
- (pinbox, stack_end),
- &pinbox->pinarray.lock)
-lock_wrap_void(lf_pinbox_put_pins,
- (LF_PINS *pins),
- (pins),
- &pins->pinbox->pinarray.lock)
-lock_wrap_void(lf_pinbox_free,
- (LF_PINS *pins, void *addr),
- (pins, addr),
- &pins->pinbox->pinarray.lock)
-
-/*
- memory allocator, lf_alloc-pin.c
-*/
-
-struct st_lf_alloc_node {
- struct st_lf_alloc_node *next;
-};
-
-typedef struct st_lf_allocator {
- LF_PINBOX pinbox;
- struct st_lf_alloc_node * volatile top;
- uint element_size;
- uint32 volatile mallocs;
-} LF_ALLOCATOR;
-
-void lf_alloc_init(LF_ALLOCATOR *allocator, uint size, uint free_ptr_offset);
-void lf_alloc_destroy(LF_ALLOCATOR *allocator);
-uint lf_alloc_pool_count(LF_ALLOCATOR *allocator);
-/*
- shortcut macros to access underlying pinbox functions from an LF_ALLOCATOR
- see _lf_pinbox_get_pins() and _lf_pinbox_put_pins()
-*/
-#define _lf_alloc_free(PINS, PTR) _lf_pinbox_free((PINS), (PTR))
-#define lf_alloc_free(PINS, PTR) lf_pinbox_free((PINS), (PTR))
-#define _lf_alloc_get_pins(A, ST) _lf_pinbox_get_pins(&(A)->pinbox, (ST))
-#define lf_alloc_get_pins(A, ST) lf_pinbox_get_pins(&(A)->pinbox, (ST))
-#define _lf_alloc_put_pins(PINS) _lf_pinbox_put_pins(PINS)
-#define lf_alloc_put_pins(PINS) lf_pinbox_put_pins(PINS)
-#define lf_alloc_direct_free(ALLOC, ADDR) my_free((uchar*)(ADDR), MYF(0))
-
-lock_wrap(lf_alloc_new, void *,
- (LF_PINS *pins),
- (pins),
- &pins->pinbox->pinarray.lock)
-
-/*
- extendible hash, lf_hash.c
-*/
-#include
-
-#define LF_HASH_UNIQUE 1
-
-typedef struct {
- LF_DYNARRAY array; /* hash itself */
- LF_ALLOCATOR alloc; /* allocator for elements */
- hash_get_key get_key; /* see HASH */
- CHARSET_INFO *charset; /* see HASH */
- uint key_offset, key_length; /* see HASH */
- uint element_size, flags; /* LF_HASH_UNIQUE, etc */
- int32 volatile size; /* size of array */
- int32 volatile count; /* number of elements in the hash */
-} LF_HASH;
-
-void lf_hash_init(LF_HASH *hash, uint element_size, uint flags,
- uint key_offset, uint key_length, hash_get_key get_key,
- CHARSET_INFO *charset);
-void lf_hash_destroy(LF_HASH *hash);
-int lf_hash_insert(LF_HASH *hash, LF_PINS *pins, const void *data);
-void *lf_hash_search(LF_HASH *hash, LF_PINS *pins, const void *key, uint keylen);
-int lf_hash_delete(LF_HASH *hash, LF_PINS *pins, const void *key, uint keylen);
-/*
- shortcut macros to access underlying pinbox functions from an LF_HASH
- see _lf_pinbox_get_pins() and _lf_pinbox_put_pins()
-*/
-#define _lf_hash_get_pins(HASH, ST) _lf_alloc_get_pins(&(HASH)->alloc, (ST))
-#define lf_hash_get_pins(HASH, ST) lf_alloc_get_pins(&(HASH)->alloc, (ST))
-#define _lf_hash_put_pins(PINS) _lf_pinbox_put_pins(PINS)
-#define lf_hash_put_pins(PINS) lf_pinbox_put_pins(PINS)
-#define lf_hash_search_unpin(PINS) lf_unpin((PINS), 2)
-/*
- cleanup
-*/
-
-#undef lock_wrap_void
-#undef lock_wrap
-#undef nolock_wrap_void
-#undef nolock_wrap
-
-#endif
-

+++ b/include/my_bit.h.moved 1970-01-01 00:00:00 +0000
@@ -1,109 +0,0 @@
-/*
- Some useful bit functions
-*/
-
-C_MODE_START
-#ifdef HAVE_INLINE
-
-extern const char _my_bits_nbits[256];
-extern const uchar _my_bits_reverse_table[256];
-
-/*
- Find smallest X in 2^X >= value
- This can be used to divide a number with value by doing a shift instead
-*/
-
-STATIC_INLINE uint my_bit_log2(ulong value)
-{
- uint bit;
- for (bit=0 ; value > 1 ; value>>=1, bit++) ;
- return bit;
-}
-
-STATIC_INLINE uint my_count_bits(ulonglong v)
-{
-#if SIZEOF_LONG_LONG > 4
- /* The following code is a bit faster on 16 bit machines than if we would
- only shift v */
- ulong v2=(ulong) (v >> 32);
- return (uint) (uchar) (_my_bits_nbits[(uchar) v] +
- _my_bits_nbits[(uchar) (v >> 8)] +
- _my_bits_nbits[(uchar) (v >> 16)] +
- _my_bits_nbits[(uchar) (v >> 24)] +
- _my_bits_nbits[(uchar) (v2)] +
- _my_bits_nbits[(uchar) (v2 >> 8)] +
- _my_bits_nbits[(uchar) (v2 >> 16)] +
- _my_bits_nbits[(uchar) (v2 >> 24)]);
-#else
- return (uint) (uchar) (_my_bits_nbits[(uchar) v] +
- _my_bits_nbits[(uchar) (v >> 8)] +
- _my_bits_nbits[(uchar) (v >> 16)] +
- _my_bits_nbits[(uchar) (v >> 24)]);
-#endif
-}
-
-STATIC_INLINE uint my_count_bits_ushort(ushort v)
-{
- return _my_bits_nbits[v];
-}
-
-
-/*
- Next highest power of two
-
- SYNOPSIS
- my_round_up_to_next_power()
- v Value to check
-
- RETURN
- Next or equal power of 2
- Note: 0 will return 0
-
- NOTES
- Algorithm by Sean Anderson, according to:
- http://graphics.stanford.edu/~seander/bithacks.html
- (Orignal code public domain)
-
- Comments shows how this works with 01100000000000000000000000001011
-*/
-
-STATIC_INLINE uint32 my_round_up_to_next_power(uint32 v)
-{
- v--; /* 01100000000000000000000000001010 */
- v|= v >> 1; /* 01110000000000000000000000001111 */
- v|= v >> 2; /* 01111100000000000000000000001111 */
- v|= v >> 4; /* 01111111110000000000000000001111 */
- v|= v >> 8; /* 01111111111111111100000000001111 */
- v|= v >> 16; /* 01111111111111111111111111111111 */
- return v+1; /* 10000000000000000000000000000000 */
-}
-
-STATIC_INLINE uint32 my_clear_highest_bit(uint32 v)
-{
- uint32 w=v >> 1;
- w|= w >> 1;
- w|= w >> 2;
- w|= w >> 4;
- w|= w >> 8;
- w|= w >> 16;
- return v & w;
-}
-
-STATIC_INLINE uint32 my_reverse_bits(uint32 key)
-{
- return
- (_my_bits_reverse_table[ key & 255] << 24) |
- (_my_bits_reverse_table[(key>> 8) & 255] << 16) |
- (_my_bits_reverse_table[(key>>16) & 255] << 8) |
- _my_bits_reverse_table[(key>>24) ];
-}
-
-#else /* HAVE_INLINE */
-extern uint my_bit_log2(ulong value);
-extern uint32 my_round_up_to_next_power(uint32 v);
-uint32 my_clear_highest_bit(uint32 v);
-uint32 my_reverse_bits(uint32 key);
-extern uint my_count_bits(ulonglong v);
-extern uint my_count_bits_ushort(ushort v);
-#endif /* HAVE_INLINE */
-C_MODE_END

+++ b/libmysqld/examples/test-run 2008-12-31 13:18:04 +0000
@@ -1,4 +1,4 @@
-#! /bin/sh
+#!/bin/bash

# This is slapped together as a quick way to run the tests and
# is not meant for prime time. Please hack at it and submit

+++ b/mysql-test/create-test-result 2008-12-31 13:18:04 +0000
@@ -10,13 +10,13 @@ if [ -z "$EDITOR" ] ; then
EDITOR=vi
fi

-function die()
+die()
{
echo $1
exit 1
}

-function usage()
+usage()
{
echo "Usage: $0 test_name"
exit 1

+++ b/mysql-test/mysql-test-run.pl 2008-12-17 19:46:23 +0000
@@ -1473,16 +1473,22 @@ sub executable_setup_ndb () {
"$glob_basedir/storage/ndb",
"$glob_basedir/bin");

+ # Some might be found in sbin, not bin.
+ my $daemon_path= mtr_file_exists("$glob_basedir/ndb",
+ "$glob_basedir/storage/ndb",
+ "$glob_basedir/sbin",
+ "$glob_basedir/bin");
+
$exe_ndbd=
mtr_exe_maybe_exists("$ndb_path/src/kernel/ndbd",
- "$ndb_path/ndbd",
+ "$daemon_path/ndbd",
"$glob_basedir/libexec/ndbd");
$exe_ndb_mgm=
mtr_exe_maybe_exists("$ndb_path/src/mgmclient/ndb_mgm",
"$ndb_path/ndb_mgm");
$exe_ndb_mgmd=
mtr_exe_maybe_exists("$ndb_path/src/mgmsrv/ndb_mgmd",
- "$ndb_path/ndb_mgmd",
+ "$daemon_path/ndb_mgmd",
"$glob_basedir/libexec/ndb_mgmd");
$exe_ndb_waiter=
mtr_exe_maybe_exists("$ndb_path/tools/ndb_waiter",

+++ b/mysql-test/r/func_math.result 2008-12-22 12:44:57 +0000
@@ -383,8 +383,10 @@ SELECT b DIV 900 y FROM t1 GROUP BY y;
y
0
Warnings:
-Warning 1292 Truncated incorrect INTEGER value: 'str1'
-Warning 1292 Truncated incorrect INTEGER value: 'str2'
+Warning 1366 Incorrect decimal value: '' for column '' at row -1
+Warning 1292 Truncated incorrect DECIMAL value: 'str1'
+Warning 1366 Incorrect decimal value: '' for column '' at row -1
+Warning 1292 Truncated incorrect DECIMAL value: 'str2'
SELECT c DIV 900 y FROM t1 GROUP BY y;
y
0
@@ -460,3 +462,8 @@ SELECT POW(10, 309);
POW(10, 309)
NULL
End of 5.1 tests
+select 123456789012345678901234567890.123456789012345678901234567890 div 1 as x;
+ERROR 22003: Out of range value for column 'x' at row 1
+select "123456789012345678901234567890.123456789012345678901234567890" div 1 as x;
+ERROR 22003: Out of range value for column 'x' at row 1
+End of 6.0 tests

+++ b/mysql-test/r/group_by.result 2008-11-26 13:04:00 +0000
@@ -1543,8 +1543,8 @@ id select_type table type possible_keys
EXPLAIN SELECT 1 FROM t1 WHERE a IN
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Start temporary
-1 PRIMARY t1 eq_ref PRIMARY,i2 PRIMARY 4 test.t1.a 1 Using index; End temporary
+1 PRIMARY t1 index PRIMARY,i2 PRIMARY 4 NULL 144 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t1)
CREATE TABLE t2 (a INT, b INT, KEY(a));
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
@@ -1556,8 +1556,8 @@ id select_type table type possible_keys
EXPLAIN SELECT 1 FROM t2 WHERE a IN
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Start temporary
-1 PRIMARY t2 index a a 5 NULL 4 Using where; Using index; End temporary; Using join buffer
+1 PRIMARY t2 index a a 5 NULL 4 Using index
+1 PRIMARY t1 ALL NULL NULL NULL NULL 144 Using where; FirstMatch(t2)
SHOW VARIABLES LIKE 'old';
Variable_name Value
old OFF

+++ b/mysql-test/r/innodb_bug34053.result 2008-09-15 21:33:05 +0000
@@ -0,0 +1 @@
+SET storage_engine=InnoDB;

+++ b/mysql-test/r/innodb_mrr.result 2008-12-29 03:42:30 +0000
@@ -351,3 +351,32 @@ pk
1
5
drop table t1;
+#
+# BUG#39447: Error with NOT NULL condition and LIMIT 1
+#
+CREATE TABLE t1 (
+id int(11) NOT NULL,
+parent_id int(11) DEFAULT NULL,
+name varchar(10) DEFAULT NULL,
+PRIMARY KEY (id),
+KEY ind_parent_id (parent_id)
+) ENGINE=InnoDB;
+insert into t1 (id, parent_id, name) values
+(10,NULL,'A'),
+(20,10,'B'),
+(30,10,'C'),
+(40,NULL,'D'),
+(50,40,'E'),
+(60,40,'F'),
+(70,NULL,'J');
+SELECT id FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+id
+60
+This must show type=index, extra=Using where
+explain SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index ind_parent_id PRIMARY 4 NULL 2 Using where
+SELECT * FROM t1 WHERE parent_id IS NOT NULL ORDER BY id DESC LIMIT 1;
+id parent_id name
+60 40 F
+drop table t1;

+++ b/mysql-test/r/join_cache.result 2009-01-08 05:47:10 +0000
@@ -3576,3 +3576,46 @@ AVG(c)
set join_buffer_size=default;
set join_cache_level=default;
DROP TABLE t1, t2;
+#
+# Bug #41894: big join buffer of level 7 used to join records
+# with null values in place of varchar strings
+#
+CREATE TABLE t1 (a int NOT NULL AUTO_INCREMENT PRIMARY KEY,
+b varchar(127) DEFAULT NULL);
+INSERT INTO t1(a) VALUES (1);
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+INSERT INTO t1(b) SELECT b FROM t1;
+CREATE TABLE t2 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL);
+INSERT INTO t2 SELECT * FROM t1;
+CREATE TABLE t3 (a int NOT NULL PRIMARY KEY, b varchar(127) DEFAULT NULL);
+INSERT INTO t3 SELECT * FROM t1;
+set join_cache_level=7;
+set join_buffer_size=1024*1024;
+EXPLAIN
+SELECT COUNT(*) FROM t1,t2,t3
+WHERE t1.a=t2.a AND t2.a=t3.a AND
+t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 16384 Using where
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer
+1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where; Using join buffer
+SELECT COUNT(*) FROM t1,t2,t3
+WHERE t1.a=t2.a AND t2.a=t3.a AND
+t1.b IS NULL AND t2.b IS NULL AND t3.b IS NULL;
+COUNT(*)
+16384
+set join_buffer_size=default;
+set join_cache_level=default;
+DROP TABLE t1,t2,t3;

+++ b/mysql-test/r/subselect.result 2008-12-29 16:40:21 +0000
@@ -1356,11 +1356,11 @@ a
3
explain extended select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index; Start temporary
-1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index
-1 PRIMARY t3 index a a 5 NULL 3 100.00 Using where; Using index; End temporary; Using join buffer
+1 PRIMARY t2 index a a 5 NULL 4 100.00 Using index
+1 PRIMARY t3 index a a 5 NULL 3 100.00 Using index
+1 PRIMARY t1 ref a a 10 test.t2.a,test.t3.a 116 100.00 Using index; FirstMatch(t2)
Warnings:
-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`))
+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`.`t1`.`b` = `test`.`t3`.`a`))
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
@@ -2818,8 +2818,8 @@ Warnings:
Note 1003 select `test`.`t1`.`one` AS `one`,`test`.`t1`.`two` AS `two`,((`test`.`t1`.`one`,`test`.`t1`.`two`),(select `test`.`t2`.`one` AS `one`,`test`.`t2`.`two` AS `two` from `test`.`t2` where ((`test`.`t2`.`flag` = '0') and trigcond((((`test`.`t1`.`one`) = `test`.`t2`.`one`) or isnull(`test`.`t2`.`one`))) and trigcond((((`test`.`t1`.`two`) = `test`.`t2`.`two`) or isnull(`test`.`t2`.`two`)))) having (trigcond((`test`.`t2`.`one`)) and trigcond((`test`.`t2`.`two`))))) AS `test` from `test`.`t1`
explain extended SELECT one,two from t1 where ROW(one,two) IN (SELECT one,two FROM t2 WHERE flag = 'N');
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00 Start temporary
-1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; End temporary; Using join buffer
+1 PRIMARY t1 ALL NULL NULL NULL NULL 8 100.00
+1 PRIMARY t2 ALL NULL NULL NULL NULL 9 100.00 Using where; FirstMatch(t1)
Warnings:
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'))
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;
@@ -4367,13 +4367,13 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`)))))
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key)))
+Note 1003 select 1 AS `1` from `test`.`t1` where (1,1 in ( (select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` ), (1 in on distinct_key where ((1 = `materialized subselect`.`1`)))))
DROP TABLE t1;
End of 5.0 tests.
create table t_out (subcase char(3),

+++ b/mysql-test/r/subselect2.result 2008-07-27 19:17:41 +0000
@@ -123,16 +123,16 @@ DOCID DOCNAME DOCTYPEID FOLDERID AUTHOR
c373e9f5ad07993f3859444553544200 Last Discussion c373e9f5ad079174ff17444553544200 c373e9f5ad0796c0eca4444553544200 Goldilocks 2003-06-09 11:21:06 Title: Last Discussion NULL Setting new abstract and keeping doc checked out 2003-06-09 10:51:26 2003-06-09 10:51:26 NULL NULL NULL 03eea05112b845949f3fd03278b5fe43 2003-06-09 11:21:06 admin 0 NULL Discussion NULL NULL
EXPLAIN EXTENDED SELECT t2.*, t4.DOCTYPENAME, t1.CONTENTSIZE,t1.MIMETYPE FROM t2 INNER JOIN t4 ON t2.DOCTYPEID = t4.DOCTYPEID LEFT OUTER JOIN t1 ON t2.DOCID = t1.DOCID WHERE t2.FOLDERID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID IN(SELECT t3.FOLDERID FROM t3 WHERE t3.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t3.FOLDERNAME = 'Level1') AND t3.FOLDERNAME = 'Level2') AND t3.FOLDERNAME = 'Level3') AND t3.FOLDERNAME = 'CopiedFolder') AND t3.FOLDERNAME = 'Movie Reviews') AND t2.DOCNAME = 'Last Discussion';
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 const 6 100.00 Using index condition; Using where
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 100.00 Using where
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 100.00 Using where
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 100.00 Using where
-1 PRIMARY t3 ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX CMFLDRPARNT_IDX 35 test.t3.FOLDERID 1 100.00 Using where
-1 PRIMARY t2 ALL DDOCTYPEID_IDX,DFOLDERID_IDX NULL NULL NULL 9 77.78 Using where; Using join buffer
+1 PRIMARY t2 ALL DDOCTYPEID_IDX NULL NULL NULL 9 100.00 Using where
1 PRIMARY t1 eq_ref PRIMARY PRIMARY 34 test.t2.DOCID 1 100.00
1 PRIMARY t4 eq_ref PRIMARY PRIMARY 34 test.t2.DOCTYPEID 1 100.00
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 func 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY,FFOLDERID_IDX,CMFLDRPARNT_IDX PRIMARY 34 test.t3.PARENTID 1 100.00 Using where
Warnings:
-Note 1003 select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t2` join `test`.`t4` left join !
`test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCTYPEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`FOLDERID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`PARENTID` = `test`.`t3`.`FOLDERID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and (`test`.`t2`.`DOCNAME` = 'Last Discussion'))
+Note 1003 select `test`.`t2`.`DOCID` AS `DOCID`,`test`.`t2`.`DOCNAME` AS `DOCNAME`,`test`.`t2`.`DOCTYPEID` AS `DOCTYPEID`,`test`.`t2`.`FOLDERID` AS `FOLDERID`,`test`.`t2`.`AUTHOR` AS `AUTHOR`,`test`.`t2`.`CREATED` AS `CREATED`,`test`.`t2`.`TITLE` AS `TITLE`,`test`.`t2`.`SUBTITLE` AS `SUBTITLE`,`test`.`t2`.`DOCABSTRACT` AS `DOCABSTRACT`,`test`.`t2`.`PUBLISHDATE` AS `PUBLISHDATE`,`test`.`t2`.`EXPIRATIONDATE` AS `EXPIRATIONDATE`,`test`.`t2`.`LOCKEDBY` AS `LOCKEDBY`,`test`.`t2`.`STATUS` AS `STATUS`,`test`.`t2`.`PARENTDOCID` AS `PARENTDOCID`,`test`.`t2`.`REPID` AS `REPID`,`test`.`t2`.`MODIFIED` AS `MODIFIED`,`test`.`t2`.`MODIFIER` AS `MODIFIER`,`test`.`t2`.`PUBLISHSTATUS` AS `PUBLISHSTATUS`,`test`.`t2`.`ORIGINATOR` AS `ORIGINATOR`,`test`.`t4`.`DOCTYPENAME` AS `DOCTYPENAME`,`test`.`t1`.`CONTENTSIZE` AS `CONTENTSIZE`,`test`.`t1`.`MIMETYPE` AS `MIMETYPE` from `test`.`t2` join `test`.`t4` left join `test`.`t1` on((`test`.`t1`.`DOCID` = `test`.`t2`.`DOCID`)) where ((`test`.`t4`.`DOCT!
YPEID` = `test`.`t2`.`DOCTYPEID`) and (`test`.`t2`.`DOCNAME` = 'Last Discussion') and (`test`.`t2`.`FOLDERID`,(select 1 AS `Not_used` from `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` join `test`.`t3` where ((`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERID` = `test`.`t3`.`PARENTID`) and (`test`.`t3`.`FOLDERNAME` = 'Level1') and (`test`.`t3`.`PARENTID` = '2f6161e879db43c1a5b82c21ddc49089') and (`test`.`t3`.`FOLDERNAME` = 'Level2') and (`test`.`t3`.`FOLDERNAME` = 'Level3') and (`test`.`t3`.`FOLDERNAME` = 'CopiedFolder') and (`test`.`t3`.`FOLDERNAME` = 'Movie Reviews') and ((`test`.`t2`.`FOLDERID`) = `test`.`t3`.`FOLDERID`)))))
drop table t1, t2, t3, t4;
CREATE TABLE t1 (a int(10) , PRIMARY KEY (a)) Engine=InnoDB;
INSERT INTO t1 VALUES (1),(2);

+++ b/mysql-test/r/subselect3.result 2008-12-28 15:00:21 +0000
@@ -1,4 +1,4 @@
-drop table if exists t0, t1, t2, t3, t4, t5;
+drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
create table t1 (oref int, grp int, ie int) ;
insert into t1 (oref, grp, ie) values
(1, 1, 1),
@@ -99,7 +99,7 @@ oref a
1 1
show status like '%Handler_read_rnd_next';
Variable_name Value
-Handler_read_rnd_next 11
+Handler_read_rnd_next 5
delete from t2;
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
flush status;
@@ -337,8 +337,8 @@ dd NULL 0
bb NULL NULL
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
oref a
-aa 1
ff 2
+aa 1
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
oref a
bb 2
@@ -421,8 +421,8 @@ dd NULL 0
bb NULL NULL
select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
oref a
-aa 1
ff 2
+aa 1
select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
oref a
bb 2
@@ -515,8 +515,8 @@ aa 1 1 1
dd 1 NULL 0
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
oref a b
-aa 1 1
ff 2 2
+aa 1 1
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
oref a b
bb 2 1
@@ -560,8 +560,8 @@ aa 1 1 1
dd 1 NULL 0
select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
oref a b
-aa 1 1
ff 2 2
+aa 1 1
select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
oref a b
bb 2 1
from t4, t5 limit 2));
ERROR 21000: Subquery returns more than 1 row
drop table t0, t1, t2, t3, t4, t5;
+CREATE TABLE t1 (
+a int(11) NOT NULL,
+b int(11) NOT NULL,
+c datetime default NULL,
+PRIMARY KEY (a),
+KEY idx_bc (b,c)
+);
+INSERT INTO t1 VALUES
+(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'),
+(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'),
+(406994,67,'2006-02-27 11:26:46'), (256,67,NULL),
+(398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL),
+(406988,67,'2006-02-23 17:07:22'), (255,67,NULL),
+(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'),
+(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'),
+(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'),
+(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'),
+(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'),
+(154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'),
+(223456,67,NULL),(4101,67,NULL),(1133,67,NULL),
+(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'),
+(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'),
+(154503,67,'2005-10-28 11:52:38');
+create table t11 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc;
+create table t12 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc;
+create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc;
+create table t22 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
+update t22 set c = '2005-12-08 15:58:27' where a = 255;
+explain select t21.* from t21,t22 where t21.a = t22.a and
+t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan
+1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize; Using join buffer
+1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
+1 PRIMARY t22 ALL NULL NULL NULL NULL 32 Using where; Using join buffer
+select t21.* from t21,t22 where t21.a = t22.a and
+t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
+a b c
+256 67 NULL
+drop table t1, t11, t12, t21, t22;
+create table t1(a int);
+insert into t1 values (0),(1);
+set @@optimizer_switch='no_firstmatch';
+explain
+select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY X ALL NULL NULL NULL NULL 2
+2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY Z ALL NULL NULL NULL NULL 2 Materialize
+select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
+subq
+NULL
+0
+set @@optimizer_switch='';
+drop table t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 as select * from t0;
+insert into t1 select a+10 from t0;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+insert into t0 values(2);
+explain select * from t1 where 2 in (select a from t0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; Start temporary; End temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer
+select * from t1 where 2 in (select a from t0);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+set @@optimizer_switch='no_materialization';
+explain select * from t1 where 2 in (select a from t0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; FirstMatch
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer
+select * from t1 where 2 in (select a from t0);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+set @@optimizer_switch='';
+explain select * from (select a from t0) X where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ALL NULL NULL NULL NULL 11
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch()
+2 DERIVED t0 ALL NULL NULL NULL NULL 11
+drop table t0, t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2));
+insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C;
+insert into t1 select * from t1 where kp1 < 20;
+create table t3 (a int);
+insert into t3 select A.a + 10*B.a from t0 A, t0 B;
+explain select * from t3 where a in (select kp1 from t1 where kp1<20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+create table t4 (pk int primary key);
+insert into t4 select a from t3;
+explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
+and t4.pk=t1.c);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using MRR; LooseScan
+1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+drop table t1, t3, t4;
+create table t1 (a int) as select * from t0 where a < 5;
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+set @@max_heap_table_size= 16384;
+explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY C ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer
+flush status;
+select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+count(*)
+4999
+show status like 'Created_tmp_disk_tables';
+Variable_name Value
+Created_tmp_disk_tables 1
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @@optimizer_switch=default;
+drop table t0, t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(a int);
+insert into t2 values (1),(2);
+create table t3 ( a int , filler char(100), key(a));
+insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
+explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t2.a 1
+select * from t3 where a in (select a from t2);
+a filler
+1 filler
+2 filler
+drop table t0, t2, t3;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+create table t1 (a date);
+insert into t1 values ('2008-01-01'),('2008-01-01'),('2008-02-01'),('2008-02-01');
+create table t2 (a int);
+insert into t2 values (1),(2);
+create table t3 (a char(10));
+insert into t3 select * from t1;
+insert into t3 values (1),(2);
+explain select * from t2 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer
+explain select * from t2 where a in (select a from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
+explain select * from t2 where a in (select a from t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer
+explain select * from t1 where a in (select a from t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer
+drop table t1, t2, t3;
+create table t1 (a decimal);
+insert into t1 values (1),(2);
+explain select * from t1 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
+drop table t1;
+set @@optimizer_switch=default;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 as select * from t1;
+create table t3 (a int, b int, filler char(100), key(a));
+insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C;
+explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t2.a 10
+explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 10
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY A ALL NULL NULL NULL NULL 10
+2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY A ALL NULL NULL NULL NULL 10
+2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+explain select straight_join * from t2 X, t2 Y
+where X.a in (select straight_join A.a from t1 A, t1 B);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer
+2 SUBQUERY A ALL NULL NULL NULL NULL 10
+2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+create table t0 (a int, b int);
+insert into t0 values(1,1);
+explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t2.a 10
+create table t4 as select a as x, a as y from t1;
+explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 system NULL NULL NULL NULL 1
+1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where
+drop table t0,t1,t2,t3,t4;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, filler char(100), key(a,b));
+insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
+create table t2 as select * from t1;
+explain select * from t2 where a in (select b from t1 where a=3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+drop table t1,t2;
+create table t1 (a int, b int);
+insert into t1 select a,a from t0;
+create table t2 (a int, b int);
+insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B;
+set @@optimizer_switch='no_firstmatch';
+explain select * from t1 where (a,b) in (select a,b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize
+set @save_optimizer_search_depth=@@optimizer_search_depth;
+set @@optimizer_search_depth=63;
+explain select * from t1 where (a,b) in (select a,b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize
+set @@optimizer_search_depth=@save_optimizer_search_depth;
+set @@optimizer_switch='';
+drop table t0, t1, t2;
+create table t0 (a decimal(4,2));
+insert into t0 values (10.24), (22.11);
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0)
+select * from t0 where a in (select a from t1);
+a
+10.24
+22.11
+drop table t0, t1;
+create table t0(a date);
+insert into t0 values ('2008-01-01'),('2008-02-02');
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0)
+select * from t0 where a in (select a from t1);
+a
+2008-01-01
+2008-02-02
+drop table t0, t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 as select a as a, a as b, a as c from t0 where a < 3;
+create table t2 as select a as a, a as b from t0 where a < 3;
+insert into t2 select * from t2;
+explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start materialize
+1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using join buffer
+1 PRIMARY Z ALL NULL NULL NULL NULL 6 End materialize; Using join buffer
+drop table t0,t1,t2;

BUG#40118 Crash when running Batched Key Access and requiring one match for each key

@@ -864,3 +1190,36 @@ a
9
set join_cache_level=@save_join_cache_level;
drop table t0, t1;
+#
+# BUG#32665 Query with dependent subquery is too slow
+#
+create table t1 (
+idIndividual int primary key
+);
+insert into t1 values (1),(2);
+create table t2 (
+idContact int primary key,
+contactType int,
+idObj int
+);
+insert into t2 values (1,1,1),(2,2,2),(3,3,3);
+create table t3 (
+idAddress int primary key,
+idContact int,
+postalStripped varchar(100)
+);
+insert into t3 values (1,1, 'foo'), (2,2,'bar');
+The following must be converted to a semi-join:
+explain extended SELECT a.idIndividual FROM t1 a
+WHERE a.idIndividual IN
+( SELECT c.idObj FROM t3 cona
+INNER JOIN t2 c ON c.idContact=cona.idContact
+WHERE cona.postalStripped='T2H3B2'
+ );
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start materialize; Scan
+1 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 End materialize
+1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+Warnings:
+Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2'))
+drop table t1,t2,t3;

+++ b/mysql-test/r/subselect3_jcl6.result 2008-12-28 15:00:21 +0000
@@ -2,7 +2,7 @@ set join_cache_level=6;
show variables like 'join_cache_level';
Variable_name Value
join_cache_level 6
-drop table if exists t0, t1, t2, t3, t4, t5;
+drop table if exists t0, t1, t2, t3, t4, t5, t11, t12, t21, t22;
create table t1 (oref int, grp int, ie int) ;
insert into t1 (oref, grp, ie) values
(1, 1, 1),
@@ -103,7 +103,7 @@ oref a
1 1
show status like '%Handler_read_rnd_next';
Variable_name Value
-Handler_read_rnd_next 11
+Handler_read_rnd_next 5
delete from t2;
insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
flush status;
from t4, t5 limit 2));
ERROR 21000: Subquery returns more than 1 row
drop table t0, t1, t2, t3, t4, t5;
+CREATE TABLE t1 (
+a int(11) NOT NULL,
+b int(11) NOT NULL,
+c datetime default NULL,
+PRIMARY KEY (a),
+KEY idx_bc (b,c)
+);
+INSERT INTO t1 VALUES
+(406989,67,'2006-02-23 17:08:46'), (150078,67,'2005-10-26 11:17:45'),
+(406993,67,'2006-02-27 11:20:57'), (245655,67,'2005-12-08 15:59:08'),
+(406994,67,'2006-02-27 11:26:46'), (256,67,NULL),
+(398341,67,'2006-02-20 04:48:44'), (254,67,NULL),(1120,67,NULL),
+(406988,67,'2006-02-23 17:07:22'), (255,67,NULL),
+(398340,67,'2006-02-20 04:38:53'),(406631,67,'2006-02-23 10:49:42'),
+(245653,67,'2005-12-08 15:59:07'),(406992,67,'2006-02-24 16:47:18'),
+(245654,67,'2005-12-08 15:59:08'),(406995,67,'2006-02-28 11:55:00'),
+(127261,67,'2005-10-13 12:17:58'),(406991,67,'2006-02-24 16:42:32'),
+(245652,67,'2005-12-08 15:58:27'),(398545,67,'2006-02-20 04:53:13'),
+(154504,67,'2005-10-28 11:53:01'),(9199,67,NULL),(1,67,'2006-02-23 15:01:35'),
+(223456,67,NULL),(4101,67,NULL),(1133,67,NULL),
+(406990,67,'2006-02-23 18:01:45'),(148815,67,'2005-10-25 15:34:17'),
+(148812,67,'2005-10-25 15:30:01'),(245651,67,'2005-12-08 15:58:27'),
+(154503,67,'2005-10-28 11:52:38');
+create table t11 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 asc;
+create table t12 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) order by 3 desc;
+create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc;
+create table t22 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
+update t22 set c = '2005-12-08 15:58:27' where a = 255;
+explain select t21.* from t21,t22 where t21.a = t22.a and
+t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t11 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort; Start materialize; Scan
+1 PRIMARY t12 ALL NULL NULL NULL NULL 8 Using where; End materialize; Using join buffer
+1 PRIMARY t21 ALL NULL NULL NULL NULL 26 Using where; Using join buffer
+1 PRIMARY t22 ALL NULL NULL NULL NULL 32 Using where; Using join buffer
+select t21.* from t21,t22 where t21.a = t22.a and
+t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
+a b c
+256 67 NULL
+256 67 NULL
+drop table t1, t11, t12, t21, t22;
+create table t1(a int);
+insert into t1 values (0),(1);
+set @@optimizer_switch='no_firstmatch';
+explain
+select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY X ALL NULL NULL NULL NULL 2
+2 DEPENDENT SUBQUERY Y ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY Z ALL NULL NULL NULL NULL 2 Materialize
+select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
+subq
+NULL
+0
+set @@optimizer_switch='';
+drop table t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 as select * from t0;
+insert into t1 select a+10 from t0;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+insert into t0 values(2);
+explain select * from t1 where 2 in (select a from t0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; Start temporary; End temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer
+select * from t1 where 2 in (select a from t0);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+set @@optimizer_switch='no_materialization';
+explain select * from t1 where 2 in (select a from t0);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 11 Using where; FirstMatch
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using join buffer
+select * from t1 where 2 in (select a from t0);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+set @@optimizer_switch='';
+explain select * from (select a from t0) X where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ALL NULL NULL NULL NULL 11
+1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where; FirstMatch(); Using join buffer
+2 DERIVED t0 ALL NULL NULL NULL NULL 11
+drop table t0, t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2));
+insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C;
+insert into t1 select * from t1 where kp1 < 20;
+create table t3 (a int);
+insert into t3 select A.a + 10*B.a from t0 A, t0 B;
+explain select * from t3 where a in (select kp1 from t1 where kp1<20);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using where; Using index; LooseScan
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+create table t4 (pk int primary key);
+insert into t4 select a from t3;
+explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
+and t4.pk=t1.c);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range kp1 kp1 5 NULL 48 Using index condition; Using MRR; LooseScan
+1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; FirstMatch(t1)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+drop table t1, t3, t4;
+create table t1 (a int) as select * from t0 where a < 5;
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+set @@max_heap_table_size= 16384;
+explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY E ALL NULL NULL NULL NULL 5 Start temporary
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY C ALL NULL NULL NULL NULL 10 Using join buffer
+1 PRIMARY D ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer
+flush status;
+select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+count(*)
+4999
+show status like 'Created_tmp_disk_tables';
+Variable_name Value
+Created_tmp_disk_tables 1
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @@optimizer_switch=default;
+drop table t0, t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(a int);
+insert into t2 values (1),(2);
+create table t3 ( a int , filler char(100), key(a));
+insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
+explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t2.a 1 Using join buffer
+select * from t3 where a in (select a from t2);
+a filler
+1 filler
+2 filler
+drop table t0, t2, t3;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+create table t1 (a date);
+insert into t1 values ('2008-01-01'),('2008-01-01'),('2008-02-01'),('2008-02-01');
+create table t2 (a int);
+insert into t2 values (1),(2);
+create table t3 (a char(10));
+insert into t3 select * from t1;
+insert into t3 values (1),(2);
+explain select * from t2 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; End temporary; Using join buffer
+explain select * from t2 where a in (select a from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
+explain select * from t2 where a in (select a from t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer
+explain select * from t1 where a in (select a from t3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Start temporary
+1 PRIMARY t3 ALL NULL NULL NULL NULL 6 Using where; End temporary; Using join buffer
+drop table t1, t2, t3;
+create table t1 (a decimal);
+insert into t1 values (1),(2);
+explain select * from t1 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
+drop table t1;
+set @@optimizer_switch=default;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 as select * from t1;
+create table t3 (a int, b int, filler char(100), key(a));
+insert into t3 select A.a + 10*B.a, A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C;
+explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30) and t1.a =3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer
+explain select straight_join * from t1 A, t1 B where A.a in (select a from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY B ALL NULL NULL NULL NULL 10 Using join buffer
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 10
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY A ALL NULL NULL NULL NULL 10
+2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+explain select * from t2 where a in (select straight_join A.a from t1 A, t1 B);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where
+2 SUBQUERY A ALL NULL NULL NULL NULL 10
+2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+explain select straight_join * from t2 X, t2 Y
+where X.a in (select straight_join A.a from t1 A, t1 B);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY X ALL NULL NULL NULL NULL 10 Using where
+1 PRIMARY Y ALL NULL NULL NULL NULL 10 Using join buffer
+2 SUBQUERY A ALL NULL NULL NULL NULL 10
+2 SUBQUERY B ALL NULL NULL NULL NULL 10 Using join buffer
+create table t0 (a int, b int);
+insert into t0 values(1,1);
+explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 system NULL NULL NULL NULL 1
+1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t2.a 10 Using join buffer
+create table t4 as select a as x, a as y from t1;
+explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 system NULL NULL NULL NULL 1
+1 PRIMARY t4 ALL NULL NULL NULL NULL 10 Using where; Materialize; Scan
+1 PRIMARY t3 ref a a 5 test.t4.x 10 Using where; Using join buffer
+drop table t0,t1,t2,t3,t4;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int, b int, filler char(100), key(a,b));
+insert into t1 select A.a, B.a, 'filler' from t0 A, t0 B;
+create table t2 as select * from t1;
+explain select * from t2 where a in (select b from t1 where a=3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 range a a 5 NULL 8 Using where; Using index; LooseScan
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Using join buffer
+drop table t1,t2;
+create table t1 (a int, b int);
+insert into t1 select a,a from t0;
+create table t2 (a int, b int);
+insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B;
+set @@optimizer_switch='no_firstmatch';
+explain select * from t1 where (a,b) in (select a,b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize
+set @save_optimizer_search_depth=@@optimizer_search_depth;
+set @@optimizer_search_depth=63;
+explain select * from t1 where (a,b) in (select a,b from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 10
+1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Materialize
+set @@optimizer_search_depth=@save_optimizer_search_depth;
+set @@optimizer_switch='';
+drop table t0, t1, t2;
+create table t0 (a decimal(4,2));
+insert into t0 values (10.24), (22.11);
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer
+select * from t0 where a in (select a from t1);
+a
+10.24
+22.11
+drop table t0, t1;
+create table t0(a date);
+insert into t0 values ('2008-01-01'),('2008-02-02');
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where; FirstMatch(t0); Using join buffer
+select * from t0 where a in (select a from t1);
+a
+2008-01-01
+2008-02-02
+drop table t0, t1;
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 as select a as a, a as b, a as c from t0 where a < 3;
+create table t2 as select a as a, a as b from t0 where a < 3;
+insert into t2 select * from t2;
+explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z where X.b=33);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
+1 PRIMARY X ALL NULL NULL NULL NULL 6 Using where; Start materialize
+1 PRIMARY Y ALL NULL NULL NULL NULL 6 Using join buffer
+1 PRIMARY Z ALL NULL NULL NULL NULL 6 End materialize; Using join buffer
+drop table t0,t1,t2;

BUG#40118 Crash when running Batched Key Access and requiring one match for each key

@@ -868,6 +1195,39 @@ a
9
set join_cache_level=@save_join_cache_level;
drop table t0, t1;
+#
+# BUG#32665 Query with dependent subquery is too slow
+#
+create table t1 (
+idIndividual int primary key
+);
+insert into t1 values (1),(2);
+create table t2 (
+idContact int primary key,
+contactType int,
+idObj int
+);
+insert into t2 values (1,1,1),(2,2,2),(3,3,3);
+create table t3 (
+idAddress int primary key,
+idContact int,
+postalStripped varchar(100)
+);
+insert into t3 values (1,1, 'foo'), (2,2,'bar');
+The following must be converted to a semi-join:
+explain extended SELECT a.idIndividual FROM t1 a
+WHERE a.idIndividual IN
+( SELECT c.idObj FROM t3 cona
+INNER JOIN t2 c ON c.idContact=cona.idContact
+WHERE cona.postalStripped='T2H3B2'
+ );
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY cona ALL NULL NULL NULL NULL 2 100.00 Using where; Start materialize; Scan
+1 PRIMARY c eq_ref PRIMARY PRIMARY 4 test.cona.idContact 1 100.00 End materialize; Using join buffer
+1 PRIMARY a index PRIMARY PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer
+Warnings:
+Note 1003 select `test`.`a`.`idIndividual` AS `idIndividual` from `test`.`t1` `a` semi join (`test`.`t3` `cona` join `test`.`t2` `c`) where ((`test`.`c`.`idContact` = `test`.`cona`.`idContact`) and (`test`.`a`.`idIndividual` = `test`.`c`.`idObj`) and (`test`.`cona`.`postalStripped` = 'T2H3B2'))
+drop table t1,t2,t3;
set join_cache_level=default;
show variables like 'join_cache_level';
Variable_name Value

+++ b/mysql-test/r/subselect_mat.result 2008-11-26 14:36:11 +0000
@@ -41,7 +41,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), (`test`.`t1`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`)))))
select * from t1 where a1 in (select b1 from t2 where b1 > '0');
a1 a2
1 - 01 2 - 01
@@ -52,7 +52,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), (`test`.`t1`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (`test`.`t1`.`a1`,`test`.`t1`.`a1` in ( (select `test`.`t2`.`b1` AS `b1` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`)))))
select * from t1 where a1 in (select b1 from t2 where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -63,7 +63,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`)))))
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 > '0' group by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -74,7 +74,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where; Using temporary; Using filesort
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS `min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), (`test`.`t1`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,min(`test`.`t2`.`b2`) AS `min(b2)` from `test`.`t2` where (`test`.`t2`.`b1` > '0') group by `test`.`t2`.`b1` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`min(b2)`)))))
select * from t1 where (a1, a2) in (select b1, min(b2) from t2 where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -85,7 +85,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i index it2i1,it2i3 it2i1 9 NULL 5 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`)))))
select * from t1i where a1 in (select b1 from t2i where b1 > '0');
a1 a2
1 - 01 2 - 01
@@ -96,7 +96,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i range it2i1,it2i3 it2i1 9 NULL 3 100.00 Using where; Using index for group-by
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1i`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (`test`.`t1i`.`a1`,`test`.`t1i`.`a1` in ( (select `test`.`t2i`.`b1` AS `b1` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`)))))
select * from t1i where a1 in (select b1 from t2i where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -107,7 +107,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`)))))
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0');
a1 a2
1 - 01 2 - 01
@@ -118,7 +118,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), (`test`.`t1i`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`)))))
select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0' group by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -129,7 +129,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1i`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`min(b2)`)))))
select * from t1i where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -140,7 +140,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2i range NULL it2i3 9 NULL 3 100.00 Using index for group-by
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1` ), (`test`.`t1`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`max(b2)`)))))
select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
a1 a2
1 - 01 2 - 01
@@ -169,7 +169,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`min(b2)`)))))
select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
a1 a2
1 - 01 2 - 01
@@ -209,7 +209,7 @@ id select_type table type possible_keys
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` order by `test`.`t2`.`b1`,`test`.`t2`.`b2` ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`)))))
select * from t1 where (a1, a2) in (select b1, b2 from t2 order by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -220,7 +220,7 @@ id select_type table type possible_keys
1 PRIMARY t1i index NULL it1i3 18 NULL 3 100.00 Using where; Using index
2 SUBQUERY t2i index NULL it2i3 18 NULL 5 100.00 Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), (`test`.`t1i`.`a1` in on distinct_key)))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` order by `test`.`t2i`.`b1`,`test`.`t2i`.`b2` ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`)))))
select * from t1i where (a1, a2) in (select b1, b2 from t2i order by b1, b2);
a1 a2
1 - 01 2 - 01
@@ -275,7 +275,7 @@ id select_type table type possible_keys
4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), (`test`.`t1`.`a1` in on distinct_key))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key))) ), (`test`.`t1`.`a1` in on distinct_key))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (`test`.`t2`.`b1` > '0') ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key w!
here ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`))))))
select * from t1
where (a1, a2) in (select b1, b2 from t2 where b1 > '0') and
(a1, a2) in (select c1, c2 from t3
@@ -294,7 +294,7 @@ id select_type table type possible_keys
4 SUBQUERY t2i index it2i2 it2i3 18 NULL 5 100.00 Using where; Using index
2 SUBQUERY t2i index it2i1,it2i3 it2i3 18 NULL 5 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key))) and ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where ((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3i`.`c1` in on distinct_key))) ), (`test`.`t1i`.`a1` in on distinct_key))))
+Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` where (((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`b1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`b2`))))) and ((`test`.`t1i`.`a1`,`test`.`t1i`.`a2`),(`test`.`t1i`.`a1`,`test`.`t1i`.`a2`) in ( (select `test`.`t3i`.`c1` AS `c1`,`test`.`t3i`.`c2` AS `c2` from `test`.`t3i` where ((`test`.`t3i`.`c1`,`test`.`t3i`.`c2`),(`test`.`t3i`.`c1`,`test`.`t3i`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3i`.`c1` in on distinct_key where ((`test`.`t3i`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3i`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1i`.`a1` in on distinct_key where ((`test`.`t1i`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1i`.`a2` = `materialized subselect`.`c2`))))))
select * from t1i
where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and
(a1, a2) in (select c1, c2 from t3i
@@ -317,7 +317,7 @@ id select_type table type possible_keys
4 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
3 SUBQUERY t3 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), (`test`.`t2`.`b2` in on distinct_key))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key)))) ), (`test`.`t1`.`a1` in on distinct_key))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test!
`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key))) ), (`test`.`t1`.`a1` in on distinct_key))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where ((`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%02') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`))))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3`.`c2` AS `c2` from `test`.`t3` where (`test`.`t3`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `mater!
ialized subselect`.`b1`) and (`test`.`t1`.`a2` = `materialized subselect`.`b2`))))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3`.`c1` AS `c1`,`test`.`t3`.`c2` AS `c2` from `test`.`t3` where ((`test`.`t3`.`c1`,`test`.`t3`.`c2`),(`test`.`t3`.`c1`,`test`.`t3`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3`.`c1` in on distinct_key where ((`test`.`t3`.`c1` = `materialized subselect`.`b1`) and (`test`.`t3`.`c2` = `materialized subselect`.`b2`))))) ), (`test`.`t1`.`a1` in on distinct_key where ((`test`.`t1`.`a1` = `materialized subselect`.`c1`) and (`test`.`t1`.`a2` = `materialized subselect`.`c2`))))))
select * from t1
where (a1, a2) in (select b1, b2 from t2
where b2 in (select c2 from t3 where c2 LIKE '%02') or
@@ -342,7 +342,7 @@ id select_type table type possible_keys
3 DEPENDENT SUBQUERY t3a ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.a1' of SELECT #3 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (((`test`.`t2`.`b2`,(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key)))) and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( (select `test`.`t3c`.`c1` AS `c1`,`test`.`t3c`.`c2` AS `c2` from `test`.`t3` `t3c` where !
((`test`.`t3c`.`c1`,`test`.`t3c`.`c2`),(`test`.`t3c`.`c1`,`test`.`t3c`.`c2`) in ( (select `test`.`t2i`.`b1` AS `b1`,`test`.`t2i`.`b2` AS `b2` from `test`.`t2i` where (`test`.`t2i`.`b2` > '0') ), (`test`.`t3c`.`c1` in on distinct_key))) ), (`test`.`t1`.`a1` in on distinct_key))))
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where (((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(select `test`.`t2`.`b1` AS `b1`,`test`.`t2`.`b2` AS `b2` from `test`.`t2` where (((`test`.`t2`.`b2`,(select 1 AS `Not_used` from `test`.`t3` `t3a` where ((`test`.`t3a`.`c1` = `test`.`t1`.`a1`) and ((`test`.`t2`.`b2`) = `test`.`t3a`.`c2`)))) or (`test`.`t2`.`b2`,`test`.`t2`.`b2` in ( (select `test`.`t3b`.`c2` AS `c2` from `test`.`t3` `t3b` where (`test`.`t3b`.`c2` like '%03') ), (`test`.`t2`.`b2` in on distinct_key where ((`test`.`t2`.`b2` = `materialized subselect`.`c2`)))))) and ((`test`.`t1`.`a1`) = `test`.`t2`.`b1`) and ((`test`.`t1`.`a2`) = `test`.`t2`.`b2`)))) and ((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in (

Currently Viewing : This Article

Horst Hunger

2009-01-15 09:45:49

---