- developers - lists.mariadb.org

[Maria-developers] Updated (by Guest): Add EXPLAIN for UPDATE/DELETE (51)
by worklog-noreply＠askmonty.org 02 Jan '10

02 Jan '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add EXPLAIN for UPDATE/DELETE CREATION DATE..: Tue, 18 Aug 2009, 16:24 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 51 (http://askmonty.org/worklog/?tid=51) VERSION........: Server-9.x STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Sat, 02 Jan 2010, 21:33)=-=- Version updated. --- /tmp/wklog.51.old.21935 2010-01-02 19:33:25.000000000 +0000 +++ /tmp/wklog.51.new.21935 2010-01-02 19:33:25.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-9.x -=-=(Guest - Sat, 02 Jan 2010, 21:33)=-=- Status updated. --- /tmp/wklog.51.old.21935 2010-01-02 19:33:25.000000000 +0000 +++ /tmp/wklog.51.new.21935 2010-01-02 19:33:25.000000000 +0000 @@ -1 +1 @@ -Un-Assigned +Assigned -=-=(Psergey - Tue, 18 Aug 2009, 16:25)=-=- High-Level Specification modified. --- /tmp/wklog.51.old.12524 2009-08-18 16:25:52.000000000 +0300 +++ /tmp/wklog.51.new.12524 2009-08-18 16:25:52.000000000 +0300 @@ -1 +1,22 @@ +User interface +-------------- +EXPLAIN UPDATE ... and EXPLAIN DELETE ... statements will work and produce +a tabular output similar to EXPLAIN SELECT. + +Implementation +-------------- +The primary challenge will be to change UPDATE/DELETE code to first produce +action plan and then act on it (and not make decisions on the go as it +currently does). + +What EXPLAIN will show +---------------------- +* multi-table one will show the SELECT part as usual +* single-table statements will show an equivalent of access method. + +Besides that, we want to know +- if sorting will be used +- whether the UPDATE will occur on the fly or not. + +(print or not "Using filesort"...) DESCRIPTION: Add support for EXPLAIN UPDATE/DELETE (we'd like support for all potentially long statements, e.g. for ALTER TABLE, but this WL entry is limited to UPDATE/DELETE as they are most often requested, and code that handles them is similar and distinct from other statements). HIGH-LEVEL SPECIFICATION: User interface -------------- EXPLAIN UPDATE ... and EXPLAIN DELETE ... statements will work and produce a tabular output similar to EXPLAIN SELECT. Implementation -------------- The primary challenge will be to change UPDATE/DELETE code to first produce action plan and then act on it (and not make decisions on the go as it currently does). What EXPLAIN will show ---------------------- * multi-table one will show the SELECT part as usual * single-table statements will show an equivalent of access method. Besides that, we want to know - if sorting will be used - whether the UPDATE will occur on the fly or not. (print or not "Using filesort"...) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Rev 2744: Backport of subquery optimizations to 5.3 in file:///home/psergey/dev/maria-5.3-subqueries-r2-commit/
by Sergey Petrunya 01 Jan '10

01 Jan '10

At file:///home/psergey/dev/maria-5.3-subqueries-r2-commit/ ------------------------------------------------------------ revno: 2744 revision-id: psergey(a)askmonty.org-20100101083655-abhuypjpebzvk33p parent: psergey(a)askmonty.org-20091227202422-fs9fgo8x2wk9ry15 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.3-subqueries-r2-commit timestamp: Fri 2010-01-01 10:36:55 +0200 message: Backport of subquery optimizations to 5.3 Diff too large for email (7544 lines, the limit is 1000).

1 0

[Maria-developers] Rev 4: Added wrapper scripts for automated runs of sql-bench. in file:///Users/hakan/work/monty_program/mariadb-tools/
by Hakan Kuecuekyilmaz 30 Dec '09

30 Dec '09

At file:///Users/hakan/work/monty_program/mariadb-tools/ ------------------------------------------------------------ revno: 4 revision-id: hakan(a)askmonty.org-20091229134253-zdbgd0ym9m7cokii parent: knielsen(a)knielsen-hq.org-20091218103524-nof6wfpuku60r9rn committer: Hakan Kuecuekyilmaz <hakan(a)askmonty.org> branch nick: mariadb-tools timestamp: Tue 2009-12-29 14:42:53 +0100 message: Added wrapper scripts for automated runs of sql-bench. === added directory 'sql-bench' === added file 'sql-bench/run-sql-bench.sh' --- a/sql-bench/run-sql-bench.sh 1970-01-01 00:00:00 +0000 +++ b/sql-bench/run-sql-bench.sh 2009-12-29 13:42:53 +0000 @@ -0,0 +1,236 @@ +#!/bin/bash +# +# Run sql-bench for every given configuration file +# we find in the directory $SQL_BENCH_CONFIGS. +# +# Note: Do not run this script with root privileges. +# We use killall -9, which can cause severe side effects! +# +# Hakan Kuecuekyilmaz <hakan at askmonty dot org> 2009-12-05. +# + +if [ $# != 2 ]; then + echo '[ERROR]: Please provide exactly two options.' + echo " Example: $0 [/path/to/bzr/repository] [name_without_spaces]" + echo ' [name_without_spaces] is used as identifier in the result file.' + + exit 1 +else + REPOSITORY="$1" + REPOSITORY_NAME="$2" +fi + +# +# Directories. +# +SQL_BENCH_CONFIGS='/home/hakan/sql-bench-configurations' +SQL_BENCH_RESULTS='/home/hakan/sql-bench-results' +WORK_DIR='/tmp' + +# +# Variables. +# +# We need at least 1 GB disk space in our $WORK_DIR. +SPACE_LIMIT=1000000 +MYSQLADMIN_OPTIONS='--no-defaults' +MACHINE=$(hostname -s) +RUN_DATE=$(date +%Y-%m-%d) + +# Timeout in seconds for waiting for mysqld to start. +TIMEOUT=100 + +# +# Binaries. +# +BZR='/usr/local/bin/bzr' +MYSQLADMIN='client/mysqladmin' + +# +# Check system. +# +# We should at least have $SPACE_LIMIT in $WORKDIR. +AVAILABLE=$(df $WORK_DIR | grep -v Filesystem | awk '{ print $4 }') + +if [ $AVAILABLE -lt $SPACE_LIMIT ]; then + echo "[ERROR]: We need at least $ONE_GB space in $WORK_DIR." + echo 'Exiting.' + + exit 1 +fi + +# +# Run sql-bench. +# +for i in ${SQL_BENCH_CONFIGS}/*.inc + do + # Set configuration and check that all required parameters are set. + source $i + + if [ x"$MARIADB_CONFIG" == x"" ]; then + echo '[ERROR]: $MARIADB_CONFIG is not set.' + echo 'Exiting.' + + exit 1 + fi + + if [ x"$SQLBENCH_OPTIONS" == x"" ]; then + echo '[ERROR]: $SQLBENCH_OPTIONS is not set.' + echo 'Exiting.' + + exit 1 + fi + + if [ x"$MARIADB_OPTIONS" == x"" ]; then + echo '[ERROR]: $MARIADB_OPTIONS is not set.' + echo 'Exiting.' + + exit 1 + fi + + # Check out and compile. + REVISION_ID=$($BZR version-info $REPOSITORY | grep revision-id) + if [ $? != 0 ]; then + echo '[ERROR]: bzr version-info failed. Please provide' + echo ' a working bzr repository' + echo 'Exiting.' + + exit 1 + fi + + cd $WORK_DIR + # Clean up of previous runs + killall -9 mysqld + + TEMP_DIR=$(mktemp --directory) + if [ $? != 0 ]; then + echo "[ERROR]: mktemp in $WORK_DIR failed." + echo 'Exiting.' + + exit 1 + fi + + # bzr export refuses to export to an existing directory, + # therefore we use a build directory. + echo "Branching from $REPOSITORY to ${TEMP_DIR}/build" + + $BZR export --format=dir ${TEMP_DIR}/build $REPOSITORY + if [ $? != 0 ]; then + echo '[ERROR]: bzr export failed.' + echo 'Exiting.' + + exit 1 + fi + + cd ${TEMP_DIR}/build + BUILD/autorun.sh + if [ $? != 0 ]; then + echo '[ERROR]: BUILD/autorun.sh failed.' + echo ' Please check your development environment.' + echo 'Exiting.' + + exit 1 + fi + + ./configure $MARIADB_CONFIG + if [ $? != 0 ]; then + echo "[ERROR]: ./configure $MARIADB_CONFIG failed." + echo " Please check your MARIADB_CONFIG in $i." + echo 'Exiting.' + + exit 1 + fi + + make -j4 + if [ $? != 0 ]; then + echo '[ERROR]: make failed.' + echo ' Please check your build logs.' + echo 'Exiting.' + + exit 1 + fi + + # Start mysqld. + MARIADB_SOCKET="${TEMP_DIR}/mysql.sock" + MARIADB_OPTIONS="$MARIADB_OPTIONS \ + --datadir=$TEMP_DIR \ + --tmpdir=$TEMP_DIR \ + --socket=$MARIADB_SOCKET" + + MYSQLADMIN_OPTIONS="$MYSQLADMIN_OPTIONS \ + --socket=$MARIADB_SOCKET" + + sql/mysqld $MARIADB_OPTIONS & + + j=0 + STARTED=-1 + while [ $j -le $TIMEOUT ] + do + $MYSQLADMIN $MYSQLADMIN_OPTIONS -uroot ping > /dev/null 2>&1 + if [ $? = 0 ]; then + STARTED=0 + + break + fi + + sleep 1 + j=$(($j + 1)) + done + + if [ $STARTED != 0 ]; then + echo '[ERROR]: Start of mysqld failed.' + echo ' Please check your error log.' + echo 'Exiting.' + + exit 1 + fi + + $MYSQLADMIN $MYSQLADMIN_OPTIONS create test + if [ $? != 0 ]; then + echo '[ERROR]: Create schema test failed.' + echo ' Please check your mysqld error log.' + echo 'Exiting.' + + exit 1 + fi + + # Run sql-bench. + cd sql-bench + COMMENTS="Revision used: $REVISION_ID \ + Configure: $MARIADB_CONFIG \ + Server options: $MARIADB_OPTIONS" + + # TODO: Adding --comments="$COMMENTS" does not work + SQLBENCH_OPTIONS="$SQLBENCH_OPTIONS \ + --socket=$MARIADB_SOCKET" + + ./run-all-tests $SQLBENCH_OPTIONS + if [ $? != 0 ]; then + echo '[ERROR]: run-all-tests produced errors.' + echo ' Please check your sql-bench error logs.' + echo 'Exiting.' + + exit 1 + fi + + # Save result file for later usage and comparison. + RESULT_FILE=$(ls output/RUN-*) + if [ x"$RESULT_FILE" != x"" ]; then + NEW_FILE_NAME="RUN-${REPOSITORY_NAME}-$(basename $RESULT_FILE | awk -F 'RUN-' '{ print $2 }')" + + CONFIGURATION=$(basename "$i" | awk -F . '{ print $1 }') + ARCHIVE_DIR="${SQL_BENCH_RESULTS}/${MACHINE}/${RUN_DATE}/${CONFIGURATION}" + mkdir -p $ARCHIVE_DIR + + # Add comment to result file. + sed -e "s%Comments:%Comments: ${COMMENTS}%" $RESULT_FILE > foo.tmp + mv foo.tmp $RESULT_FILE + # TODO: check for failures and copy the logs in question. + cp $RESULT_FILE ${ARCHIVE_DIR}/$NEW_FILE_NAME + + # Clean up for next round. + rm -rf $TEMP_DIR + else + echo '[ERROR]: Cannot find result file after sql-bench run!' + fi + +done === added directory 'sql-bench/sql-bench-configurations' === added file 'sql-bench/sql-bench-configurations/sql-bench-default-config.inc' --- a/sql-bench/sql-bench-configurations/sql-bench-default-config.inc 1970-01-01 00:00:00 +0000 +++ b/sql-bench/sql-bench-configurations/sql-bench-default-config.inc 2009-12-29 13:42:53 +0000 @@ -0,0 +1,34 @@ +# Template configuration file for running sqlbench. +# +# We are using this configuration file for the wrapper +# script run-sql-bench.sh. For every copy of this file we +# are starting a sqlbench run. +# +# Hakan Kuecuekyilmaz <hakan at askmonty dot org> 2009-12-05. +# + +# +# Configure line for compiling MariaDB. +# +export MARIADB_CONFIG="--enable-thread-safe-client \ + --enable-local-infile" + +# +# Options for starting mysqld. +# +# Attention: Do not set --datadir, --tmpdir, and --socket. +# +export MARIADB_OPTIONS="--no-defaults \ + --skip-grant-tables \ + --language=./sql/share/english" + +# +# Options for sql-bench. +# +# Attention: Do not set --socket. +# +export SQLBENCH_OPTIONS="--log \ + --fast \ + --server=mysql \ + --small-test \ + --user=root"

3 3

[Maria-developers] commit performance when the binlog is enabled
by MARK CALLAGHAN 30 Dec '09

30 Dec '09

InnoDB fixed group commit in the InnoDB plugin. This performs as expected when the binlog is disabled. This does not perform as I expect when the binlog is enabled. Is this a problem for PBXT? The problems for InnoDB are: 1) commit is serialized on the binlog write/fsync 2) row locks are not released until the commit step of XA prepare/commit 3) per-table auto inc locks not released until the commit step of XA I think that 2) and 3) can be fixed without significant changes. They cause a lot of convoys today for high-throughput OLTP -- too many connections needlessly wait on row locks and the per-table auto-inc lock. Doing the binlog fsync one connection at a time also causes a lot of convoys. This makes MySQL much slower than it should be for some workloads even with battery backed RAID write caches. Problem 1) occurs because: * there is no group commit for the binlog fsync * InnoDB locks prepare_commit_mutex in the prepare step Even if there were group commit for the binlog fsync, it would be useless for InnoDB because prepare_commit_mutex is locked in the prepare step and not unlocked until the commit step and the binlog write/fsync is done between these two steps. There is a MySQL worklog for this (4007) that: * doesn't intend to add group commit for the binlog fsync * doesn't mention the problem of prepare_commit_mutex I have started to work on this, but don't have any code to share yet. Pseudo-code for commit with the InnoDB plugin when the binlog is enabled: ha_commit_trans() * ht->prepare() == innobase_xa_prepare() o trx_prepare_for_mysql(trx) + force to disk the trx log buffer for all changes from this trx + fsync done here, group prepare may amortize that o lock prepare_commit_mutex * tc_log->log_xid(thd, xid) o writes SQL to binlog, XID to binlog, optionally fsync binlog * ha_commit_one_phase() o ht->commit() == innobase_commit() + innobase_commit_low(() # write commit record to trx log buffer, release locks from this trx # for auto-commit statements, the per-table auto-inc lock is released here + unlock prepare_commit_mutex + trx_commit_complete_for_mysql() # force to disk the trx log buffer including commit record for this trx # fsync done here, group commit may amortize that -- Mark Callaghan mdcallag(a)gmail.com

3 8

[Maria-developers] 2PC between two engines + binlog
by Arjen Lentz 29 Dec '09

29 Dec '09

Hi Hakan, all With PBXT and the latest merge, this should now technically be enabled (again). That is, a transaction should be consistent between InnoDB+PBXT +binlog, that is either all or none should commit. But as I understand, when this was last tried with Falcon it went kaboom in MySQL, so likely there are some bugs. I think it would be good to at least create/enable some tests for this, then we can see what the exact code status is, and hopefully get it all working as well. Right now I don't think anybody really expects something like this to work, but in theory it should work so presumptions *may* be made (I know this sentence appears kinda contradictory, hope it makes sense anyhow ;-). Thus it's important for us to know what happens, so that we can tell people "don't do this now, please track bug #", or "please try this". Thanks Cheers, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Exceptional Services for MySQL at a fixed budget. Follow our blog at http://openquery.com/blog/ OurDelta: packages for MySQL and MariaDB @ http://ourdelta.org

4 4

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2786)
by Michael Widenius 29 Dec '09

29 Dec '09

#At lp:maria based on revid:knielsen@knielsen-hq.org-20091221162636-z5ycgu2uunkw8hvh 2786 Michael Widenius 2009-12-27 Fix test suite so that it works when doing configure --without-uca Simplify testing of needed characterset Remove ndb from --with-plugins=max build mysqlbug now sends email to maria-developers(a)lists.launchpad.net removed: mysql-test/r/have_big5.require mysql-test/r/have_cp1250_ch.require mysql-test/r/have_cp1251.require mysql-test/r/have_cp866.require mysql-test/r/have_cp932.require mysql-test/r/have_eucjpms.require mysql-test/r/have_euckr.require mysql-test/r/have_gb2312.require mysql-test/r/have_gbk.require mysql-test/r/have_koi8r.require mysql-test/r/have_latin2_ch.require mysql-test/r/have_sjis.require mysql-test/r/have_tis620.require mysql-test/r/have_ucs2.require mysql-test/r/have_ujis.require mysql-test/r/have_utf8.require added: mysql-test/include/have_collation.inc mysql-test/r/create-uca.result mysql-test/r/innodb_utf8.result mysql-test/t/create-uca.test mysql-test/t/innodb_utf8.test modified: client/mysqltest.cc mysql-test/include/have_big5.inc mysql-test/include/have_cp1250_ch.inc mysql-test/include/have_cp1251.inc mysql-test/include/have_cp866.inc mysql-test/include/have_cp932.inc mysql-test/include/have_eucjpms.inc mysql-test/include/have_euckr.inc mysql-test/include/have_gb2312.inc mysql-test/include/have_gbk.inc mysql-test/include/have_koi8r.inc mysql-test/include/have_latin2_ch.inc mysql-test/include/have_sjis.inc mysql-test/include/have_tis620.inc mysql-test/include/have_ucs2.inc mysql-test/include/have_ujis.inc mysql-test/include/have_utf8.inc mysql-test/r/create.result mysql-test/r/innodb.result mysql-test/suite/rpl/t/rpl_ignore_table.test mysql-test/t/create.test mysql-test/t/ctype_utf8.test mysql-test/t/ddl_i18n_koi8r.test mysql-test/t/ddl_i18n_utf8.test mysql-test/t/fulltext.test mysql-test/t/fulltext2.test mysql-test/t/innodb.test mysql-test/t/query_cache_ps_no_prot.test mysql-test/t/query_cache_ps_ps_prot.test scripts/mysqlbug.sh storage/ndb/plug.in per-file messages: client/mysqltest.cc SKIP now expands variables (for better error messages) mysql-test/include/have_big5.inc Simplify by using have_collation.inc mysql-test/include/have_collation.inc Test if '$collation' is supported mysql-test/include/have_cp1250_ch.inc Simplify by using have_collation.inc mysql-test/include/have_cp1251.inc Simplify by using have_collation.inc mysql-test/include/have_cp866.inc Simplify by using have_collation.inc mysql-test/include/have_cp932.inc Simplify by using have_collation.inc mysql-test/include/have_eucjpms.inc Simplify by using have_collation.inc mysql-test/include/have_euckr.inc Simplify by using have_collation.inc mysql-test/include/have_gb2312.inc Simplify by using have_collation.inc mysql-test/include/have_gbk.inc Simplify by using have_collation.inc mysql-test/include/have_koi8r.inc Simplify by using have_collation.inc mysql-test/include/have_latin2_ch.inc Simplify by using have_collation.inc mysql-test/include/have_sjis.inc Simplify by using have_collation.inc mysql-test/include/have_tis620.inc Simplify by using have_collation.inc mysql-test/include/have_ucs2.inc Simplify by using have_collation.inc mysql-test/include/have_ujis.inc Simplify by using have_collation.inc mysql-test/include/have_utf8.inc Simplify by using have_collation.inc mysql-test/r/create-uca.result Create tests that uses unicode mysql-test/r/create.result Move test with unicode to create-uca.test mysql-test/r/have_big5.require Not needed anymore mysql-test/r/have_cp1250_ch.require Not needed anymore mysql-test/r/have_cp1251.require Not needed anymore mysql-test/r/have_cp866.require Not needed anymore mysql-test/r/have_cp932.require Not needed anymore mysql-test/r/have_eucjpms.require Not needed anymore mysql-test/r/have_euckr.require Not needed anymore mysql-test/r/have_gb2312.require Not needed anymore mysql-test/r/have_gbk.require Not needed anymore mysql-test/r/have_koi8r.require Not needed anymore mysql-test/r/have_latin2_ch.require Not needed anymore mysql-test/r/have_sjis.require Not needed anymore mysql-test/r/have_tis620.require Not needed anymore mysql-test/r/have_ucs2.require Not needed anymore mysql-test/r/have_ujis.require Not needed anymore mysql-test/r/have_utf8.require Not needed anymore mysql-test/r/innodb.result Move tests that depends on unicode to innodb_utf8.test mysql-test/r/innodb_utf8.result Test moved from innodb.test mysql-test/suite/rpl/t/rpl_ignore_table.test Test for required collations mysql-test/t/create-uca.test Create tests that uses unicode mysql-test/t/create.test Move test with unicode to create-uca.test mysql-test/t/ctype_utf8.test Test that require unicode mysql-test/t/ddl_i18n_koi8r.test Test for required collations mysql-test/t/ddl_i18n_utf8.test Test for required collations mysql-test/t/fulltext.test Test for required collations mysql-test/t/fulltext2.test Test for required collations mysql-test/t/innodb.test Move tests that depends on unicode to innodb_utf8.test mysql-test/t/innodb_utf8.test Tests that uses unicode mysql-test/t/query_cache_ps_no_prot.test Test for required collations mysql-test/t/query_cache_ps_ps_prot.test Test for required collations scripts/mysqlbug.sh Send emails to maria-developers(a)lists.launchpad.net storage/ndb/plug.in Don't include ndb in 'max' builds === modified file 'client/mysqltest.cc' --- a/client/mysqltest.cc 2009-12-03 11:19:05 +0000 +++ b/client/mysqltest.cc 2009-12-27 13:54:41 +0000 @@ -1267,6 +1267,7 @@ void abort_not_supported_test(const char DBUG_ENTER("abort_not_supported_test"); /* Print include filestack */ + fflush(stdout); fprintf(stderr, "The test '%s' is not supported by this installation\n", file_stack->file_name); fprintf(stderr, "Detected in file %s at line %d\n", @@ -8098,7 +8099,10 @@ int main(int argc, char **argv) abort_flag= 1; break; case Q_SKIP: - abort_not_supported_test("%s", command->first_argument); + /* Eval the query, thus replacing all environment variables */ + dynstr_set(&ds_res, 0); + do_eval(&ds_res, command->first_argument, command->end, FALSE); + abort_not_supported_test("%s",ds_res.str); break; case Q_RESULT: === modified file 'mysql-test/include/have_big5.inc' --- a/mysql-test/include/have_big5.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_big5.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_big5.require -disable_query_log; -show collation like 'big5_chinese_ci'; -enable_query_log; +let collation=big5_chinese_ci; +--source include/have_collation.inc === added file 'mysql-test/include/have_collation.inc' --- a/mysql-test/include/have_collation.inc 1970-01-01 00:00:00 +0000 +++ b/mysql-test/include/have_collation.inc 2009-12-27 13:54:41 +0000 @@ -0,0 +1,3 @@ +if (!`SELECT count(*) AS 'true' FROM information_schema.collations WHERE collation_name LIKE '$collation'`) { + skip Test needs character set '$collation'; +} === modified file 'mysql-test/include/have_cp1250_ch.inc' --- a/mysql-test/include/have_cp1250_ch.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_cp1250_ch.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_cp1250_ch.require -disable_query_log; -show collation like 'cp1250_czech_cs'; -enable_query_log; +let collation=cp1250_czech_cs; +--source include/have_collation.inc === modified file 'mysql-test/include/have_cp1251.inc' --- a/mysql-test/include/have_cp1251.inc 2007-06-28 17:34:54 +0000 +++ b/mysql-test/include/have_cp1251.inc 2009-12-27 13:54:41 +0000 @@ -1,7 +1,2 @@ ---require r/have_cp1251.require - ---disable_query_log - -SHOW COLLATION LIKE 'cp1251_general_ci'; - ---enable_query_log +let collation=cp1251_general_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_cp866.inc' --- a/mysql-test/include/have_cp866.inc 2007-06-28 17:34:54 +0000 +++ b/mysql-test/include/have_cp866.inc 2009-12-27 13:54:41 +0000 @@ -1,7 +1,2 @@ ---require r/have_cp866.require - ---disable_query_log - -SHOW COLLATION LIKE 'cp866_general_ci'; - ---enable_query_log +let collation=cp866_general_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_cp932.inc' --- a/mysql-test/include/have_cp932.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_cp932.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_cp932.require -disable_query_log; -show collation like 'cp932_japanese_ci'; -enable_query_log; +let collation=cp932_japanese_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_eucjpms.inc' --- a/mysql-test/include/have_eucjpms.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_eucjpms.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_eucjpms.require -disable_query_log; -show collation like 'eucjpms_japanese_ci'; -enable_query_log; +let collation=eucjpms_japanese_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_euckr.inc' --- a/mysql-test/include/have_euckr.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_euckr.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_euckr.require -disable_query_log; -show collation like 'euckr_korean_ci'; -enable_query_log; +let collation=euckr_korean_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_gb2312.inc' --- a/mysql-test/include/have_gb2312.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_gb2312.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_gb2312.require -disable_query_log; -show collation like 'gb2312_chinese_ci'; -enable_query_log; +let collation=gb2312_chinese_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_gbk.inc' --- a/mysql-test/include/have_gbk.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_gbk.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_gbk.require -disable_query_log; -show collation like 'gbk_chinese_ci'; -enable_query_log; +let collation=gbk_chinese_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_koi8r.inc' --- a/mysql-test/include/have_koi8r.inc 2007-06-28 17:34:54 +0000 +++ b/mysql-test/include/have_koi8r.inc 2009-12-27 13:54:41 +0000 @@ -1,7 +1,2 @@ ---require r/have_koi8r.require - ---disable_query_log - -SHOW COLLATION LIKE 'koi8r_general_ci'; - ---enable_query_log +let collation=koi8r_general_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_latin2_ch.inc' --- a/mysql-test/include/have_latin2_ch.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_latin2_ch.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_latin2_ch.require -disable_query_log; -show collation like 'latin2_czech_cs'; -enable_query_log; +let collation=latin2_czech_cs; +--source include/have_collation.inc === modified file 'mysql-test/include/have_sjis.inc' --- a/mysql-test/include/have_sjis.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_sjis.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_sjis.require -disable_query_log; -show collation like 'sjis_japanese_ci'; -enable_query_log; +let collation=sjis_japanese_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_tis620.inc' --- a/mysql-test/include/have_tis620.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_tis620.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_tis620.require -disable_query_log; -show collation like 'tis620_thai_ci'; -enable_query_log; +let collation=tis620_thai_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_ucs2.inc' --- a/mysql-test/include/have_ucs2.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_ucs2.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_ucs2.require -disable_query_log; -show collation like 'ucs2_general_ci'; -enable_query_log; +let collation=ucs2_general_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_ujis.inc' --- a/mysql-test/include/have_ujis.inc 2008-07-04 16:41:27 +0000 +++ b/mysql-test/include/have_ujis.inc 2009-12-27 13:54:41 +0000 @@ -1,4 +1,2 @@ --- require r/have_ujis.require -disable_query_log; -show collation like 'ujis_japanese_ci'; -enable_query_log; +let collation=ujis_japanese_ci; +--source include/have_collation.inc === modified file 'mysql-test/include/have_utf8.inc' --- a/mysql-test/include/have_utf8.inc 2007-06-28 17:34:54 +0000 +++ b/mysql-test/include/have_utf8.inc 2009-12-27 13:54:41 +0000 @@ -1,7 +1,2 @@ ---require r/have_utf8.require - ---disable_query_log - -SHOW COLLATION LIKE 'utf8_general_ci'; - ---enable_query_log +let collation=utf8_general_ci; +--source include/have_collation.inc === added file 'mysql-test/r/create-uca.result' --- a/mysql-test/r/create-uca.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/create-uca.result 2009-12-27 13:54:41 +0000 @@ -0,0 +1,31 @@ +drop table if exists t1,t2; +CREATE TABLE t1( +c1 INT DEFAULT 12 COMMENT 'column1', +c2 INT NULL COMMENT 'column2', +c3 INT NOT NULL COMMENT 'column3', +c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', +c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b', +c6 VARCHAR(255)) +COLLATE latin1_bin; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(11) DEFAULT '12' COMMENT 'column1', + `c2` int(11) DEFAULT NULL COMMENT 'column2', + `c3` int(11) NOT NULL COMMENT 'column3', + `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', + `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', + `c6` varchar(255) COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin +CREATE TABLE t2 AS SELECT * FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `c1` int(11) DEFAULT '12' COMMENT 'column1', + `c2` int(11) DEFAULT NULL COMMENT 'column2', + `c3` int(11) NOT NULL COMMENT 'column3', + `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', + `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', + `c6` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; === modified file 'mysql-test/r/create.result' --- a/mysql-test/r/create.result 2009-12-03 11:19:05 +0000 +++ b/mysql-test/r/create.result 2009-12-27 13:54:41 +0000 @@ -1793,52 +1793,6 @@ t1 CREATE TABLE `t1` ( drop table t1; # -- -# -- Bug#21380: DEFAULT definition not always transfered by CREATE -# -- TABLE/SELECT to the new table. -# -- - -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; - -CREATE TABLE t1( -c1 INT DEFAULT 12 COMMENT 'column1', -c2 INT NULL COMMENT 'column2', -c3 INT NOT NULL COMMENT 'column3', -c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', -c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b', -c6 VARCHAR(255)) -COLLATE latin1_bin; - -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `c1` int(11) DEFAULT '12' COMMENT 'column1', - `c2` int(11) DEFAULT NULL COMMENT 'column2', - `c3` int(11) NOT NULL COMMENT 'column3', - `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', - `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', - `c6` varchar(255) COLLATE latin1_bin DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin - -CREATE TABLE t2 AS SELECT * FROM t1; - -SHOW CREATE TABLE t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `c1` int(11) DEFAULT '12' COMMENT 'column1', - `c2` int(11) DEFAULT NULL COMMENT 'column2', - `c3` int(11) NOT NULL COMMENT 'column3', - `c4` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', - `c5` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT 'b', - `c6` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 - -DROP TABLE t2; -DROP TABLE t1; - -# -- End of test case for Bug#21380. - -# -- # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields # -- === removed file 'mysql-test/r/have_big5.require' --- a/mysql-test/r/have_big5.require 2003-12-24 12:59:48 +0000 +++ b/mysql-test/r/have_big5.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -big5_chinese_ci big5 1 Yes Yes 1 === removed file 'mysql-test/r/have_cp1250_ch.require' --- a/mysql-test/r/have_cp1250_ch.require 2005-03-03 10:15:37 +0000 +++ b/mysql-test/r/have_cp1250_ch.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -cp1250_czech_cs cp1250 34 Yes 2 === removed file 'mysql-test/r/have_cp1251.require' --- a/mysql-test/r/have_cp1251.require 2007-06-28 17:34:54 +0000 +++ b/mysql-test/r/have_cp1251.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -cp1251_general_ci cp1251 51 Yes 0 === removed file 'mysql-test/r/have_cp866.require' --- a/mysql-test/r/have_cp866.require 2007-06-28 17:34:54 +0000 +++ b/mysql-test/r/have_cp866.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -cp866_general_ci cp866 36 Yes 0 === removed file 'mysql-test/r/have_cp932.require' --- a/mysql-test/r/have_cp932.require 2005-02-01 10:37:51 +0000 +++ b/mysql-test/r/have_cp932.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -cp932_japanese_ci cp932 95 Yes Yes 1 === removed file 'mysql-test/r/have_eucjpms.require' --- a/mysql-test/r/have_eucjpms.require 2005-02-01 10:37:51 +0000 +++ b/mysql-test/r/have_eucjpms.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -eucjpms_japanese_ci eucjpms 97 Yes Yes 1 === removed file 'mysql-test/r/have_euckr.require' --- a/mysql-test/r/have_euckr.require 2005-12-09 12:37:58 +0000 +++ b/mysql-test/r/have_euckr.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -euckr_korean_ci euckr 19 Yes Yes 1 === removed file 'mysql-test/r/have_gb2312.require' --- a/mysql-test/r/have_gb2312.require 2005-12-09 12:37:58 +0000 +++ b/mysql-test/r/have_gb2312.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -gb2312_chinese_ci gb2312 24 Yes Yes 1 === removed file 'mysql-test/r/have_gbk.require' --- a/mysql-test/r/have_gbk.require 2005-07-22 16:06:02 +0000 +++ b/mysql-test/r/have_gbk.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -gbk_chinese_ci gbk 28 Yes Yes 1 === removed file 'mysql-test/r/have_koi8r.require' --- a/mysql-test/r/have_koi8r.require 2007-06-28 17:34:54 +0000 +++ b/mysql-test/r/have_koi8r.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -koi8r_general_ci koi8r 7 Yes 0 === removed file 'mysql-test/r/have_latin2_ch.require' --- a/mysql-test/r/have_latin2_ch.require 2006-03-20 12:28:25 +0000 +++ b/mysql-test/r/have_latin2_ch.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -latin2_czech_cs latin2 2 Yes 4 === removed file 'mysql-test/r/have_sjis.require' --- a/mysql-test/r/have_sjis.require 2004-03-25 10:29:56 +0000 +++ b/mysql-test/r/have_sjis.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -sjis_japanese_ci sjis 13 Yes Yes 1 === removed file 'mysql-test/r/have_tis620.require' --- a/mysql-test/r/have_tis620.require 2003-12-25 16:11:01 +0000 +++ b/mysql-test/r/have_tis620.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -tis620_thai_ci tis620 18 Yes Yes 4 === removed file 'mysql-test/r/have_ucs2.require' --- a/mysql-test/r/have_ucs2.require 2003-06-02 12:19:06 +0000 +++ b/mysql-test/r/have_ucs2.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -ucs2_general_ci ucs2 35 Yes Yes 1 === removed file 'mysql-test/r/have_ujis.require' --- a/mysql-test/r/have_ujis.require 2003-09-19 10:18:19 +0000 +++ b/mysql-test/r/have_ujis.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -ujis_japanese_ci ujis 12 Yes Yes 1 === removed file 'mysql-test/r/have_utf8.require' --- a/mysql-test/r/have_utf8.require 2007-06-28 17:34:54 +0000 +++ b/mysql-test/r/have_utf8.require 1970-01-01 00:00:00 +0000 @@ -1,2 +0,0 @@ -Collation Charset Id Default Compiled Sortlen -utf8_general_ci utf8 33 Yes Yes 1 === modified file 'mysql-test/r/innodb.result' --- a/mysql-test/r/innodb.result 2009-11-13 21:26:08 +0000 +++ b/mysql-test/r/innodb.result 2009-12-27 13:54:41 +0000 @@ -3160,15 +3160,6 @@ ALTER TABLE t2 MODIFY a INT NOT NULL; ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150) DELETE FROM t1; DROP TABLE t2,t1; -CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY) -ENGINE=InnoDB; -INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4); -DELETE FROM t1; -INSERT INTO t1 VALUES ('DDD'); -SELECT * FROM t1; -a -DDD -DROP TABLE t1; CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=42; INSERT INTO t1 VALUES (0),(347),(0); === added file 'mysql-test/r/innodb_utf8.result' --- a/mysql-test/r/innodb_utf8.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/innodb_utf8.result 2009-12-27 13:54:41 +0000 @@ -0,0 +1,10 @@ +drop table if exists t1; +CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY) +ENGINE=InnoDB; +INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4); +DELETE FROM t1; +INSERT INTO t1 VALUES ('DDD'); +SELECT * FROM t1; +a +DDD +DROP TABLE t1; === modified file 'mysql-test/suite/rpl/t/rpl_ignore_table.test' --- a/mysql-test/suite/rpl/t/rpl_ignore_table.test 2008-11-13 19:19:00 +0000 +++ b/mysql-test/suite/rpl/t/rpl_ignore_table.test 2009-12-27 13:54:41 +0000 @@ -1,4 +1,6 @@ source include/master-slave.inc; +let collation=utf8_unicode_ci; +--source include/have_collation.inc # # BUG#16487 === added file 'mysql-test/t/create-uca.test' --- a/mysql-test/t/create-uca.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/create-uca.test 2009-12-27 13:54:41 +0000 @@ -0,0 +1,26 @@ +# Prerequisites +let collation=utf8_unicode_ci; +--source include/have_collation.inc + +# Initial cleanup +--disable_warnings +drop table if exists t1,t2; +--enable_warnings + +# +# Bug#21380: DEFAULT definition not always transfered by CREATE +# TABLE/SELECT to the new table. +# + +CREATE TABLE t1( + c1 INT DEFAULT 12 COMMENT 'column1', + c2 INT NULL COMMENT 'column2', + c3 INT NOT NULL COMMENT 'column3', + c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', + c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b', + c6 VARCHAR(255)) + COLLATE latin1_bin; +SHOW CREATE TABLE t1; +CREATE TABLE t2 AS SELECT * FROM t1; +SHOW CREATE TABLE t2; +DROP TABLE t1,t2; === modified file 'mysql-test/t/create.test' --- a/mysql-test/t/create.test 2009-12-03 11:19:05 +0000 +++ b/mysql-test/t/create.test 2009-12-27 13:54:41 +0000 @@ -1400,52 +1400,6 @@ drop table t1; --echo --echo # -- ---echo # -- Bug#21380: DEFAULT definition not always transfered by CREATE ---echo # -- TABLE/SELECT to the new table. ---echo # -- ---echo - - ---disable_warnings -DROP TABLE IF EXISTS t1; -DROP TABLE IF EXISTS t2; ---enable_warnings - ---echo - -CREATE TABLE t1( - c1 INT DEFAULT 12 COMMENT 'column1', - c2 INT NULL COMMENT 'column2', - c3 INT NOT NULL COMMENT 'column3', - c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a', - c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b', - c6 VARCHAR(255)) - COLLATE latin1_bin; - ---echo - -SHOW CREATE TABLE t1; - ---echo - -CREATE TABLE t2 AS SELECT * FROM t1; - ---echo - -SHOW CREATE TABLE t2; - ---echo - -DROP TABLE t2; -DROP TABLE t1; - ---echo ---echo # -- End of test case for Bug#21380. - -########################################################################### - ---echo ---echo # -- --echo # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields --echo # -- --echo === modified file 'mysql-test/t/ctype_utf8.test' --- a/mysql-test/t/ctype_utf8.test 2009-01-26 21:19:13 +0000 +++ b/mysql-test/t/ctype_utf8.test 2009-12-27 13:54:41 +0000 @@ -2,6 +2,15 @@ # Tests with the utf8 character set # +let collation=utf8_unicode_ci; +--source include/have_collation.inc + +--disable_warnings +drop table if exists t1,t2,t3,t4; +drop database if exists mysqltest; +--enable_warnings + + --disable_warnings drop table if exists t1,t2; --enable_warnings === modified file 'mysql-test/t/ddl_i18n_koi8r.test' --- a/mysql-test/t/ddl_i18n_koi8r.test 2009-05-15 10:15:56 +0000 +++ b/mysql-test/t/ddl_i18n_koi8r.test 2009-12-27 13:54:41 +0000 @@ -36,6 +36,8 @@ --source include/have_cp866.inc --source include/have_cp1251.inc --source include/have_koi8r.inc +let collation=utf8_unicode_ci; +--source include/have_collation.inc ########################################################################### === modified file 'mysql-test/t/ddl_i18n_utf8.test' --- a/mysql-test/t/ddl_i18n_utf8.test 2009-05-15 10:15:56 +0000 +++ b/mysql-test/t/ddl_i18n_utf8.test 2009-12-27 13:54:41 +0000 @@ -36,6 +36,8 @@ --source include/have_cp866.inc --source include/have_cp1251.inc --source include/have_koi8r.inc +let collation=utf8_unicode_ci; +--source include/have_collation.inc ########################################################################### === modified file 'mysql-test/t/fulltext.test' --- a/mysql-test/t/fulltext.test 2009-09-07 20:50:10 +0000 +++ b/mysql-test/t/fulltext.test 2009-12-27 13:54:41 +0000 @@ -2,6 +2,9 @@ # Test of fulltext index # +let collation=utf8_unicode_ci; +--source include/have_collation.inc + --disable_warnings drop table if exists t1,t2,t3; --enable_warnings === modified file 'mysql-test/t/fulltext2.test' --- a/mysql-test/t/fulltext2.test 2009-10-28 07:52:34 +0000 +++ b/mysql-test/t/fulltext2.test 2009-12-27 13:54:41 +0000 @@ -2,6 +2,9 @@ # test of new fulltext search features # +let collation=utf8_unicode_ci; +--source include/have_collation.inc + # # two-level tree # === modified file 'mysql-test/t/innodb.test' --- a/mysql-test/t/innodb.test 2009-11-13 21:26:08 +0000 +++ b/mysql-test/t/innodb.test 2009-12-27 13:54:41 +0000 @@ -2353,18 +2353,6 @@ DELETE FROM t1; DROP TABLE t2,t1; # -# Bug #26835: table corruption after delete+insert -# - -CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY) -ENGINE=InnoDB; -INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4); -DELETE FROM t1; -INSERT INTO t1 VALUES ('DDD'); -SELECT * FROM t1; -DROP TABLE t1; - -# # Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables) # Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?)) # === added file 'mysql-test/t/innodb_utf8.test' --- a/mysql-test/t/innodb_utf8.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/innodb_utf8.test 2009-12-27 13:54:41 +0000 @@ -0,0 +1,24 @@ +# +# Tests for innodb that requires not default character sets +# + +--source include/have_innodb.inc +let collation=utf8_unicode_ci; +--source include/have_collation.inc + +# Setup +--disable_warnings +drop table if exists t1; +--enable_warnings + +# +# Bug #26835: table corruption after delete+insert +# + +CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY) +ENGINE=InnoDB; +INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4); +DELETE FROM t1; +INSERT INTO t1 VALUES ('DDD'); +SELECT * FROM t1; +DROP TABLE t1; === modified file 'mysql-test/t/query_cache_ps_no_prot.test' --- a/mysql-test/t/query_cache_ps_no_prot.test 2007-05-24 20:13:49 +0000 +++ b/mysql-test/t/query_cache_ps_no_prot.test 2009-12-27 13:54:41 +0000 @@ -11,8 +11,9 @@ # We cannot run on embedded server because we use multiple sessions. --source include/not_embedded.inc - --source include/have_query_cache.inc +let collation=utf8_unicode_ci; +--source include/have_collation.inc # The file with expected results fits only to a run without # ps-protocol/sp-protocol/cursor-protocol/view-protocol. === modified file 'mysql-test/t/query_cache_ps_ps_prot.test' --- a/mysql-test/t/query_cache_ps_ps_prot.test 2007-05-24 20:13:49 +0000 +++ b/mysql-test/t/query_cache_ps_ps_prot.test 2009-12-27 13:54:41 +0000 @@ -11,8 +11,9 @@ # We cannot run on embedded server because we use multiple sessions. --source include/not_embedded.inc - --source include/have_query_cache.inc +let collation=utf8_unicode_ci; +--source include/have_collation.inc # The file with expected results fits only to a run with "--ps-protocol". if (`SELECT $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0 === modified file 'scripts/mysqlbug.sh' --- a/scripts/mysqlbug.sh 2007-10-19 17:06:30 +0000 +++ b/scripts/mysqlbug.sh 2009-12-27 13:54:41 +0000 @@ -21,7 +21,7 @@ echo "Finding system information for a M VERSION="@VERSION@@MYSQL_SERVER_SUFFIX@" COMPILATION_COMMENT="@COMPILATION_COMMENT@" -BUGmysql="mysql(a)lists.mysql.com" +BUGmysql="maria-developers(a)lists.launchpad.net" # This is set by configure COMP_CALL_INFO="CC='@SAVE_CC@' CFLAGS='@SAVE_CFLAGS@' CXX='@SAVE_CXX@' CXXFLAGS='@SAVE_CXXFLAGS@' LDFLAGS='@SAVE_LDFLAGS@' ASFLAGS='@SAVE_ASFLAGS@'" COMP_RUN_INFO="CC='@CC@' CFLAGS='@CFLAGS@' CXX='@CXX@' CXXFLAGS='@CXXFLAGS@' LDFLAGS='@LDFLAGS@' ASFLAGS='@ASFLAGS@'" === modified file 'storage/ndb/plug.in' --- a/storage/ndb/plug.in 2006-08-19 04:19:19 +0000 +++ b/storage/ndb/plug.in 2009-12-27 13:54:41 +0000 @@ -1,5 +1,5 @@ MYSQL_STORAGE_ENGINE(ndbcluster, ndbcluster, [Cluster Storage Engine], - [High Availability Clustered tables], [max]) + [High Availability Clustered tables],) MYSQL_PLUGIN_DIRECTORY(ndbcluster,[storage/ndb]) MYSQL_PLUGIN_STATIC(ndbcluster, [[\$(ndbcluster_libs) \$(ndbcluster_system_libs) \$(NDB_SCI_LIBS)]]) MYSQL_PLUGIN_ACTIONS(ndbcluster,[MYSQL_SETUP_NDBCLUSTER])

2 1

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2783)
by knielsen＠knielsen-hq.org 29 Dec '09

29 Dec '09

#At lp:maria 2783 knielsen(a)knielsen-hq.org 2009-12-29 [merge] Merge PBXT fix from Paul. modified: storage/pbxt/ChangeLog storage/pbxt/src/discover_xt.cc storage/pbxt/src/ha_pbxt.cc storage/pbxt/src/strutil_xt.cc === modified file 'storage/pbxt/ChangeLog' --- a/storage/pbxt/ChangeLog 2009-12-01 09:50:46 +0000 +++ b/storage/pbxt/ChangeLog 2009-12-21 13:13:15 +0000 @@ -1,6 +1,10 @@ PBXT Release Notes ================== +------- 1.0.09g RC3 - 2009-12-16 + +RN292: Fixed a bug that resulted in 2-phase commit not being used between PBXT and the binlog. This bug was a result of a hack which as added to solve a problem in an pre-release version of MySQL 5.1. The hack was removed. + ------- 1.0.09f RC3 - 2009-11-30 RN291: Fixed bug #489088: On shutdown MySQL reports: [Warning] Plugin 'PBXT' will be forced to shutdown. === modified file 'storage/pbxt/src/discover_xt.cc' --- a/storage/pbxt/src/discover_xt.cc 2009-12-16 08:13:18 +0000 +++ b/storage/pbxt/src/discover_xt.cc 2009-12-21 13:13:15 +0000 @@ -355,10 +355,10 @@ static int sort_keys(KEY *a, KEY *b) { if (!(b_flags & HA_NOSAME)) return -1; - if ((a_flags ^ b_flags) & (HA_NULL_PART_KEY | HA_END_SPACE_KEY)) + if ((a_flags ^ b_flags) & HA_NULL_PART_KEY) { /* Sort NOT NULL keys before other keys */ - return (a_flags & (HA_NULL_PART_KEY | HA_END_SPACE_KEY)) ? 1 : -1; + return (a_flags & HA_NULL_PART_KEY) ? 1 : -1; } if (a->name == primary_key_name) return -1; === modified file 'storage/pbxt/src/ha_pbxt.cc' --- a/storage/pbxt/src/ha_pbxt.cc 2009-12-22 11:52:44 +0000 +++ b/storage/pbxt/src/ha_pbxt.cc 2009-12-29 11:34:44 +0000 @@ -1447,7 +1447,7 @@ static int pbxt_commit(handlerton *hton, XTThreadPtr self; if ((self = (XTThreadPtr) *thd_ha_data(thd, hton))) { - XT_PRINT1(self, "pbxt_commit all=%d\n", all); + XT_PRINT2(self, "%s pbxt_commit all=%d\n", all ? "END CONN XACT" : "END STAT", all); if (self->st_xact_data) { /* There are no table locks, commit immediately in all cases @@ -1479,7 +1479,7 @@ static int pbxt_rollback(handlerton *hto XTThreadPtr self; if ((self = (XTThreadPtr) *thd_ha_data(thd, hton))) { - XT_PRINT1(self, "pbxt_rollback all=%d in pbxt_commit\n", all); + XT_PRINT2(self, "%s pbxt_rollback all=%d\n", all ? "CONN END XACT" : "STAT END", all); if (self->st_xact_data) { /* There are no table locks, rollback immediately in all cases @@ -1543,7 +1543,7 @@ static int pbxt_prepare(handlerton *hton * except when this is a statement commit with an explicit * transaction (!all && !self->st_auto_commit). */ - if (all) { + if (all || self->st_auto_commit) { XID xid; XT_PRINT0(self, "xt_xn_prepare in pbxt_prepare\n"); @@ -2625,26 +2625,7 @@ int ha_pbxt::write_row(byte *buf) } #endif - /* GOTCHA: I have a huge problem with the transaction statement. - * It is not ALWAYS committed (I mean ha_commit_trans() is - * not always called - for example in SELECT). - * - * If I call trans_register_ha() but ha_commit_trans() is not called - * then MySQL thinks a transaction is still running (while - * I have committed the auto-transaction in ha_pbxt::external_lock()). - * - * This causes all kinds of problems, like transactions - * are killed when they should not be. - * - * To prevent this, I only inform MySQL that a transaction - * has beens started when an update is performed. I have determined that - * ha_commit_trans() is only guarenteed to be called if an update is done. - */ - if (!pb_open_tab->ot_thread->st_stat_trans) { - trans_register_ha(pb_mysql_thd, FALSE, pbxt_hton); - XT_PRINT0(pb_open_tab->ot_thread, "ha_pbxt::write_row trans_register_ha all=FALSE\n"); - pb_open_tab->ot_thread->st_stat_trans = TRUE; - } + /* {START-STAT-HACK} previously position of start statement hack. */ xt_xlog_check_long_writer(pb_open_tab->ot_thread); @@ -2735,11 +2716,7 @@ int ha_pbxt::update_row(const byte * old XT_DISABLED_TRACE(("UPDATE tx=%d val=%d\n", (int) self->st_xact_data->xd_start_xn_id, (int) XT_GET_DISK_4(&new_data[1]))); //statistic_increment(ha_update_count,&LOCK_status); - if (!self->st_stat_trans) { - trans_register_ha(pb_mysql_thd, FALSE, pbxt_hton); - XT_PRINT0(self, "ha_pbxt::update_row trans_register_ha all=FALSE\n"); - self->st_stat_trans = TRUE; - } + /* {START-STAT-HACK} previously position of start statement hack. */ xt_xlog_check_long_writer(self); @@ -2826,11 +2803,7 @@ int ha_pbxt::delete_row(const byte * buf } #endif - if (!pb_open_tab->ot_thread->st_stat_trans) { - trans_register_ha(pb_mysql_thd, FALSE, pbxt_hton); - XT_PRINT0(pb_open_tab->ot_thread, "ha_pbxt::delete_row trans_register_ha all=FALSE\n"); - pb_open_tab->ot_thread->st_stat_trans = TRUE; - } + /* {START-STAT-HACK} previously position of start statement hack. */ xt_xlog_check_long_writer(pb_open_tab->ot_thread); @@ -3160,15 +3133,12 @@ int ha_pbxt::index_init(uint idx, bool X printf("index_init %s index %d cols req=%d/%d read_bits=%X write_bits=%X index_bits=%X\n", pb_open_tab->ot_table->tab_name->ps_path, (int) idx, pb_open_tab->ot_cols_req, pb_open_tab->ot_cols_req, (int) *table->read_set->bitmap, (int) *table->write_set->bitmap, (int) *ind->mi_col_map.bitmap); #endif + /* {START-STAT-HACK} previously position of start statement hack, + * previous comment to code below: */ /* Start a statement based transaction as soon * as a read is done for a modify type statement! * Previously, this was done too late! */ - if (!thread->st_stat_trans) { - trans_register_ha(pb_mysql_thd, FALSE, pbxt_hton); - XT_PRINT0(thread, "ha_pbxt::update_row trans_register_ha all=FALSE\n"); - thread->st_stat_trans = TRUE; - } } else { pb_open_tab->ot_cols_req = ha_get_max_bit(table->read_set); @@ -3617,15 +3587,12 @@ int ha_pbxt::rnd_init(bool scan) /* The number of columns required: */ if (pb_open_tab->ot_is_modify) { pb_open_tab->ot_cols_req = table->read_set->MX_BIT_SIZE(); + /* {START-STAT-HACK} previously position of start statement hack, + * previous comment to code below: */ /* Start a statement based transaction as soon * as a read is done for a modify type statement! * Previously, this was done too late! */ - if (!thread->st_stat_trans) { - trans_register_ha(pb_mysql_thd, FALSE, pbxt_hton); - XT_PRINT0(thread, "ha_pbxt::update_row trans_register_ha all=FALSE\n"); - thread->st_stat_trans = TRUE; - } } else { pb_open_tab->ot_cols_req = ha_get_max_bit(table->read_set); @@ -4636,7 +4603,7 @@ xtPublic int ha_pbxt::external_lock(THD cont_(b); } - /* See (***) */ + /* See {IS-UPDATE-STAT} */ self->st_is_update = FALSE; /* Auto begin a transaction (if one is not already running): */ @@ -4665,7 +4632,7 @@ xtPublic int ha_pbxt::external_lock(THD } /* - * (**) GOTCHA: trans_register_ha() is not mentioned in the documentation. + * {START-TRANS} GOTCHA: trans_register_ha() is not mentioned in the documentation. * It must be called to inform MySQL that we have a transaction (see start_stmt). * * Here are some tests that confirm whether things are done correctly: @@ -4703,10 +4670,46 @@ xtPublic int ha_pbxt::external_lock(THD */ if (!self->st_auto_commit) { trans_register_ha(thd, TRUE, pbxt_hton); - XT_PRINT0(self, "ha_pbxt::external_lock trans_register_ha all=TRUE\n"); + XT_PRINT0(self, "CONN START XACT - ha_pbxt::external_lock --> trans_register_ha\n"); } } + /* Start a statment transaction: */ + /* {START-STAT-HACK} The problem that ha_commit_trans() is not + * called by MySQL seems to be fixed (tests confirm this). + * Here is the previous comment when this code was execute + * here {START-STAT-HACK} + * + * GOTCHA: I have a huge problem with the transaction statement. + * It is not ALWAYS committed (I mean ha_commit_trans() is + * not always called - for example in SELECT). + * + * If I call trans_register_ha() but ha_commit_trans() is not called + * then MySQL thinks a transaction is still running (while + * I have committed the auto-transaction in ha_pbxt::external_lock()). + * + * This causes all kinds of problems, like transactions + * are killed when they should not be. + * + * To prevent this, I only inform MySQL that a transaction + * has beens started when an update is performed. I have determined that + * ha_commit_trans() is only guarenteed to be called if an update is done. + * -------- + * + * So, this is the correct place to start a statement transaction. + * + * Note: if trans_register_ha() is not called before ha_write_row(), then + * PBXT is not registered correctly as a modification transaction. + * (mark_trx_read_write call in ha_write_row). + * This leads to 2-phase commit not being called as it should when + * binary logging is enabled. + */ + if (!pb_open_tab->ot_thread->st_stat_trans) { + trans_register_ha(pb_mysql_thd, FALSE, pbxt_hton); + XT_PRINT0(pb_open_tab->ot_thread, "STAT START - ha_pbxt::external_lock --> trans_register_ha\n"); + pb_open_tab->ot_thread->st_stat_trans = TRUE; + } + if (lock_type == F_WRLCK || self->st_xact_mode < XT_XACT_REPEATABLE_READ) self->st_visible_time = self->st_database->db_xn_end_time; @@ -4831,7 +4834,7 @@ int ha_pbxt::start_stmt(THD *thd, thr_lo } } - /* (***) This is required at this level! + /* {IS-UPDATE-STAT} This is required at this level! * No matter how often it is called, it is still the start of a * statement. We need to make sure statements that are NOT mistaken * for different type of statement. @@ -4846,7 +4849,7 @@ int ha_pbxt::start_stmt(THD *thd, thr_lo */ self->st_is_update = FALSE; - /* See comment (**) */ + /* See comment {START-TRANS} */ if (!self->st_xact_data) { self->st_xact_mode = thd_tx_isolation(thd) <= ISO_READ_COMMITTED ? XT_XACT_COMMITTED_READ : XT_XACT_REPEATABLE_READ; self->st_ignore_fkeys = (thd_test_options(thd, OPTION_NO_FOREIGN_KEY_CHECKS)) != 0; @@ -4863,10 +4866,17 @@ int ha_pbxt::start_stmt(THD *thd, thr_lo } if (!self->st_auto_commit) { trans_register_ha(thd, TRUE, pbxt_hton); - XT_PRINT0(self, "ha_pbxt::start_stmt trans_register_ha all=TRUE\n"); + XT_PRINT0(self, "START CONN XACT - ha_pbxt::start_stmt --> trans_register_ha\n"); } } + /* Start a statment (see {START-STAT-HACK}): */ + if (!pb_open_tab->ot_thread->st_stat_trans) { + trans_register_ha(pb_mysql_thd, FALSE, pbxt_hton); + XT_PRINT0(pb_open_tab->ot_thread, "START STAT - ha_pbxt::start_stmt --> trans_register_ha\n"); + pb_open_tab->ot_thread->st_stat_trans = TRUE; + } + if (pb_open_tab->ot_for_update || self->st_xact_mode < XT_XACT_REPEATABLE_READ) self->st_visible_time = self->st_database->db_xn_end_time; === modified file 'storage/pbxt/src/strutil_xt.cc' --- a/storage/pbxt/src/strutil_xt.cc 2009-11-24 10:55:06 +0000 +++ b/storage/pbxt/src/strutil_xt.cc 2009-12-21 13:13:15 +0000 @@ -380,7 +380,7 @@ xtPublic void xt_int8_to_byte_size(xtInt /* Version number must also be set in configure.in! */ xtPublic c_char *xt_get_version(void) { - return "1.0.09f RC"; + return "1.0.09g RC"; } /* Copy and URL decode! */

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (knielsen:2782)
by knielsen＠knielsen-hq.org 29 Dec '09

29 Dec '09

#At lp:maria 2782 knielsen(a)knielsen-hq.org 2009-12-29 [merge] Merge Sergey's fix for pbxt.join_nested. modified: mysql-test/suite/pbxt/r/join_nested.result mysql-test/suite/pbxt/t/join_nested.test === modified file 'mysql-test/suite/pbxt/r/join_nested.result' --- a/mysql-test/suite/pbxt/r/join_nested.result 2009-11-24 10:19:08 +0000 +++ b/mysql-test/suite/pbxt/r/join_nested.result 2009-12-27 16:48:27 +0000 @@ -968,7 +968,7 @@ id select_type table type possible_keys Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t8(b); -EXPLAIN EXTENDED +EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1003,22 +1003,23 @@ t0.b=t1.b AND (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 Using where -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) -Warnings: +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 +1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using join buffer +ATTENTION: the above EXPLAIN has several competing QEPs with identical +. costs. To combat the plan change it uses --sorted_result and +. and --replace tricks CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); -EXPLAIN EXTENDED +EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1053,19 +1054,20 @@ t0.b=t1.b AND (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where -1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 Using where -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5`.`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) -Warnings: +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ref idx_a idx_a 5 const 1 +1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 +1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using join buffer +ATTENTION: the above EXPLAIN has several competing QEPs with identical +. costs. To combat the plan change it uses --sorted_result +. and --replace tricks SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 === modified file 'mysql-test/suite/pbxt/t/join_nested.test' --- a/mysql-test/suite/pbxt/t/join_nested.test 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/t/join_nested.test 2009-12-27 16:48:27 +0000 @@ -546,8 +546,9 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3. CREATE INDEX idx_b ON t8(b); +--replace_regex /Using where; // /Using where// --sorted_result -EXPLAIN EXTENDED +EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -582,12 +583,16 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3. (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +--echo ATTENTION: the above EXPLAIN has several competing QEPs with identical +--echo . costs. To combat the plan change it uses --sorted_result and +--echo . and --replace tricks CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); +--replace_regex /Using where; // /Using where// --sorted_result -EXPLAIN EXTENDED +EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -622,6 +627,9 @@ SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3. (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +--echo ATTENTION: the above EXPLAIN has several competing QEPs with identical +--echo . costs. To combat the plan change it uses --sorted_result +--echo . and --replace tricks --sorted_result SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

1 0

[Maria-developers] Rev 2743: DS-MRR backport: fix buildbot valgrind failures: in file:///home/psergey/dev/maria-5.2-dsmrr-r3/
by Sergey Petrunya 27 Dec '09

27 Dec '09

At file:///home/psergey/dev/maria-5.2-dsmrr-r3/ ------------------------------------------------------------ revno: 2743 revision-id: psergey(a)askmonty.org-20091227202422-fs9fgo8x2wk9ry15 parent: igor(a)askmonty.org-20091222151849-rqp41x6x9r14liz6 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.2-dsmrr-r3 timestamp: Sun 2009-12-27 23:24:22 +0300 message: DS-MRR backport: fix buildbot valgrind failures: - Do call update_used_tables() for new conditions obtained when adding outer join's triggered conditions. Correct values of used_tables() are now needed for condition pushdown. - Update test results === modified file 'mysql-test/suite/pbxt/r/join_outer.result' --- a/mysql-test/suite/pbxt/r/join_outer.result 2009-12-16 09:28:51 +0000 +++ b/mysql-test/suite/pbxt/r/join_outer.result 2009-12-27 20:24:22 +0000 @@ -886,7 +886,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 const PRIMARY PRIMARY 4 const 1 Using index 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index -1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where 1 SIMPLE t3 ALL NULL NULL NULL NULL 2 drop table t1,t2; create table t1 (a int, b int); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-12-21 02:26:15 +0000 +++ b/sql/sql_select.cc 2009-12-27 20:24:22 +0000 @@ -6201,6 +6201,7 @@ cond_tab->set_select_cond(new_cond, __LINE__); if (!cond_tab->select_cond) DBUG_RETURN(1); + cond_tab->select_cond->update_used_tables(); cond_tab->select_cond->quick_fix_field(); } } @@ -6482,6 +6483,7 @@ if (!cond_tab->select_cond) DBUG_RETURN(1); cond_tab->select_cond->quick_fix_field(); + cond_tab->select_cond->update_used_tables(); if (cond_tab->select) cond_tab->select->cond= cond_tab->select_cond; } @@ -6537,6 +6539,7 @@ (ulong)cond_tab->select_cond)); if (!cond_tab->select_cond) DBUG_RETURN(1); + cond_tab->select_cond->update_used_tables(); cond_tab->select_cond->quick_fix_field(); if (cond_tab->select) cond_tab->select->cond= cond_tab->select_cond;

1 0

[Maria-developers] Rev 2777: Make pbxt.join_nested test pass in file:///home/psergey/dev/5.1-merge-look/
by Sergey Petrunya 27 Dec '09

27 Dec '09

At file:///home/psergey/dev/5.1-merge-look/ ------------------------------------------------------------ revno: 2777 revision-id: psergey(a)askmonty.org-20091227164827-038uergwe82hyd1m parent: knielsen(a)knielsen-hq.org-20091214083138-axta6hzovkyt65wg committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 5.1-merge-look timestamp: Sun 2009-12-27 19:48:27 +0300 message: Make pbxt.join_nested test pass - The reason the test failed was competition between 3+ QEPs with identical costs. Before, two plans were competing, and that was addressed by using --sorted_result on the EXPLAIN output because they were different only in join order. Now we've got a 3rd plan which differs with "Using where" and that doesn't work anymore. - This patch fixes it by removing 'Using where' from EXPLAIN output. Test coverage is somewhat reduced but probably still ok as PBXT and nested outer join processing have no interaction and we don't expect any bugs here. === modified file 'mysql-test/suite/pbxt/r/join_nested.result' --- a/mysql-test/suite/pbxt/r/join_nested.result 2009-11-24 10:19:08 +0000 +++ b/mysql-test/suite/pbxt/r/join_nested.result 2009-12-27 16:48:27 +0000 @@ -968,7 +968,7 @@ Warnings: Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5` .`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) CREATE INDEX idx_b ON t8(b); -EXPLAIN EXTENDED +EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1003,22 +1003,23 @@ (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 Using where -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5` .`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) -Warnings: +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 3 +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using join buffer +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 +1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using join buffer +ATTENTION: the above EXPLAIN has several competing QEPs with identical +. costs. To combat the plan change it uses --sorted_result and +. and --replace tricks CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); -EXPLAIN EXTENDED +EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -1053,19 +1054,20 @@ (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t0 ref idx_a idx_a 5 const 1 100.00 Using where -1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 100.00 Using where -1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 100.00 Using where -1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 100.00 Using where -1 SIMPLE t6 ALL NULL NULL NULL NULL 3 100.00 Using where -1 SIMPLE t7 ALL NULL NULL NULL NULL 2 100.00 Using where -1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 100.00 Using where -1 SIMPLE t9 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer -Note 1003 select `test`.`t0`.`a` AS `a`,`test`.`t0`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b`,`test`.`t4`.`a` AS `a`,`test`.`t4`.`b` AS `b`,`test`.`t5`.`a` AS `a`,`test`.`t5`.`b` AS `b`,`test`.`t6`.`a` AS `a`,`test`.`t6`.`b` AS `b`,`test`.`t7`.`a` AS `a`,`test`.`t7`.`b` AS `b`,`test`.`t8`.`a` AS `a`,`test`.`t8`.`b` AS `b`,`test`.`t9`.`a` AS `a`,`test`.`t9`.`b` AS `b` from `test`.`t0` join `test`.`t1` left join (`test`.`t2` left join (`test`.`t3` join `test`.`t4`) on(((`test`.`t4`.`b` = `test`.`t2`.`b`) and (`test`.`t3`.`a` = 1))) join `test`.`t5` left join (`test`.`t6` join `test`.`t7` left join `test`.`t8` on(((`test`.`t8`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` < 10)))) on(((`test`.`t7`.`b` = `test`.`t5`.`b`) and (`test`.`t6`.`b` >= 2)))) on((((`test`.`t3`.`b` = 2) or isnull(`test`.`t3`.`c`)) and ((`test`.`t6`.`b` = 2) or isnull(`test`.`t6`.`c`)) and ((`test`.`t5` .`b` = `test`.`t0`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t6`.`c`) or isnull(`test`.`t8`.`c`)) and (`test`.`t1`.`a` <> 2))) join `test`.`t9` where ((`test`.`t9`.`a` = 1) and (`test`.`t1`.`b` = `test`.`t0`.`b`) and (`test`.`t0`.`a` = 1) and ((`test`.`t2`.`a` >= 4) or isnull(`test`.`t2`.`c`)) and ((`test`.`t3`.`a` < 5) or isnull(`test`.`t3`.`c`)) and ((`test`.`t4`.`b` = `test`.`t3`.`b`) or isnull(`test`.`t3`.`c`) or isnull(`test`.`t4`.`c`)) and ((`test`.`t5`.`a` >= 2) or isnull(`test`.`t5`.`c`)) and ((`test`.`t6`.`a` >= 4) or isnull(`test`.`t6`.`c`)) and ((`test`.`t7`.`a` <= 2) or isnull(`test`.`t7`.`c`)) and ((`test`.`t8`.`a` < 1) or isnull(`test`.`t8`.`c`)) and ((`test`.`t9`.`b` = `test`.`t8`.`b`) or isnull(`test`.`t8`.`c`))) -Warnings: +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ref idx_a idx_a 5 const 1 +1 SIMPLE t1 ref idx_b idx_b 5 test.t0.b 1 +1 SIMPLE t2 ALL NULL NULL NULL NULL 3 +1 SIMPLE t3 ALL NULL NULL NULL NULL 2 +1 SIMPLE t4 ref idx_b idx_b 5 test.t2.b 1 +1 SIMPLE t5 ALL idx_b NULL NULL NULL 3 +1 SIMPLE t6 ALL NULL NULL NULL NULL 3 +1 SIMPLE t7 ALL NULL NULL NULL NULL 2 +1 SIMPLE t8 ref idx_b idx_b 5 test.t5.b 1 +1 SIMPLE t9 ALL NULL NULL NULL NULL 3 Using join buffer +ATTENTION: the above EXPLAIN has several competing QEPs with identical +. costs. To combat the plan change it uses --sorted_result +. and --replace tricks SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 === modified file 'mysql-test/suite/pbxt/t/join_nested.test' --- a/mysql-test/suite/pbxt/t/join_nested.test 2009-08-17 15:57:58 +0000 +++ b/mysql-test/suite/pbxt/t/join_nested.test 2009-12-27 16:48:27 +0000 @@ -546,8 +546,9 @@ CREATE INDEX idx_b ON t8(b); +--replace_regex /Using where; // /Using where// --sorted_result -EXPLAIN EXTENDED +EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -582,12 +583,16 @@ (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +--echo ATTENTION: the above EXPLAIN has several competing QEPs with identical +--echo . costs. To combat the plan change it uses --sorted_result and +--echo . and --replace tricks CREATE INDEX idx_b ON t1(b); CREATE INDEX idx_a ON t0(a); +--replace_regex /Using where; // /Using where// --sorted_result -EXPLAIN EXTENDED +EXPLAIN SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b, t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b,t9.a,t9.b FROM t0,t1 @@ -622,6 +627,9 @@ (t8.a < 1 OR t8.c IS NULL) AND (t8.b=t9.b OR t8.c IS NULL) AND (t9.a=1); +--echo ATTENTION: the above EXPLAIN has several competing QEPs with identical +--echo . costs. To combat the plan change it uses --sorted_result +--echo . and --replace tricks --sorted_result SELECT t0.a,t0.b,t1.a,t1.b,t2.a,t2.b,t3.a,t3.b,t4.a,t4.b,

1 1