Below is the list of changes that have just been committed into a local
5.1 repository of hf. When hf does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-05-18 20:00:49+05:00, holyfoot@hfmain.(none) +18 -0
Merge mysql.com:/d2/hf/mrg/mysql-5.0-opt
into mysql.com:/d2/hf/mrg/mysql-5.1-opt
MERGE: 1.1810.2870.51
mysql-test/include/mix1.inc@stripped, 2007-05-18 19:55:01+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.3.24.2
mysql-test/include/mix1.inc@stripped, 2007-05-18 19:55:01+05:00, holyfoot@hfmain.(none) +0 -0
Merge rename: mysql-test/t/innodb_mysql.test -> mysql-test/include/mix1.inc
mysql-test/r/func_gconcat.result@stripped, 2007-05-18 19:55:01+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.52.1.17
mysql-test/r/func_group.result@stripped, 2007-05-18 19:55:02+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.49.1.12
mysql-test/r/innodb_mysql.result@stripped, 2007-05-18 19:55:02+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.3.4.16
mysql-test/r/ps.result@stripped, 2007-05-18 19:56:01+05:00, holyfoot@hfmain.(none) +186 -0
merging
MERGE: 1.56.1.35
mysql-test/r/subselect.result@stripped, 2007-05-18 19:56:34+05:00, holyfoot@hfmain.(none) +30 -30
merging
MERGE: 1.134.1.56
mysql-test/r/type_datetime.result@stripped, 2007-05-18 19:57:04+05:00, holyfoot@hfmain.(none) +0 -0
SCCS merged
MERGE: 1.30.1.14
mysql-test/t/func_gconcat.test@stripped, 2007-05-18 19:55:02+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.44.1.12
mysql-test/t/func_group.test@stripped, 2007-05-18 19:55:02+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.48.1.8
mysql-test/t/ps.test@stripped, 2007-05-18 19:57:43+05:00, holyfoot@hfmain.(none) +90 -0
merging
MERGE: 1.56.1.35
mysql-test/t/subselect.test@stripped, 2007-05-18 19:58:11+05:00, holyfoot@hfmain.(none) +24 -24
merging
MERGE: 1.129.1.23
mysql-test/t/type_datetime.test@stripped, 2007-05-18 19:58:50+05:00, holyfoot@hfmain.(none) +1 -0
merging
MERGE: 1.18.1.12
sql/item.cc@stripped, 2007-05-18 19:55:02+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.113.1.154
sql/item_cmpfunc.cc@stripped, 2007-05-18 19:55:02+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.187.1.65
sql/item_sum.cc@stripped, 2007-05-18 19:55:02+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.170.1.37
sql/opt_sum.cc@stripped, 2007-05-18 20:00:45+05:00, holyfoot@hfmain.(none) +0 -0
SCCS merged
MERGE: 1.44.1.18
sql/sql_select.cc@stripped, 2007-05-18 19:55:03+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.312.1.209
sql/sql_union.cc@stripped, 2007-05-18 19:55:03+05:00, holyfoot@hfmain.(none) +0 -0
Auto merged
MERGE: 1.130.1.12
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: holyfoot
# Host: hfmain.(none)
# Root: /d2/hf/mrg/mysql-5.1-opt/RESYNC
--- 1.267/sql/item.cc 2007-05-18 20:00:55 +05:00
+++ 1.268/sql/item.cc 2007-05-18 20:00:55 +05:00
@@ -3558,7 +3558,8 @@ Item_field::fix_outer_field(THD *thd, Fi
prev_subselect_item->const_item_cache= 0;
set_field(*from_field);
if (!last_checked_context->select_lex->having_fix_field &&
- select->group_list.elements)
+ select->group_list.elements &&
+ (place == SELECT_LIST || place == IN_HAVING))
{
Item_outer_ref *rf;
/*
--- 1.273/sql/item_cmpfunc.cc 2007-05-18 20:00:55 +05:00
+++ 1.274/sql/item_cmpfunc.cc 2007-05-18 20:00:55 +05:00
@@ -690,7 +690,13 @@ Arg_comparator::can_compare_as_dates(Ite
if (cmp_type != CMP_DATE_DFLT)
{
- if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item())
+ /*
+ Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+ for the current thread but it still may change during the execution.
+ */
+ if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item() &&
+ (str_arg->type() != Item::FUNC_ITEM ||
+ ((Item_func*)str_arg)->functype() != Item_func::GUSERVAR_FUNC))
{
THD *thd= current_thd;
ulonglong value;
@@ -718,7 +724,7 @@ int Arg_comparator::set_cmp_func(Item_bo
Item_result type)
{
enum enum_date_cmp_type cmp_type;
- ulonglong const_value;
+ ulonglong const_value= (ulonglong)-1;
a= a1;
b= a2;
@@ -731,8 +737,7 @@ int Arg_comparator::set_cmp_func(Item_bo
a_cache= 0;
b_cache= 0;
- if (cmp_type != CMP_DATE_WITH_DATE &&
- ((*b)->const_item() || (*a)->const_item()))
+ if (const_value != (ulonglong)-1)
{
Item_cache_int *cache= new Item_cache_int();
/* Mark the cache as non-const to prevent re-caching. */
@@ -838,7 +843,12 @@ get_datetime_value(THD *thd, Item ***ite
MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
}
- if (item->const_item() && cache_arg)
+ /*
+ Do not cache GET_USER_VAR() function as its const_item() may return TRUE
+ for the current thread but it still may change during the execution.
+ */
+ if (item->const_item() && cache_arg && (item->type() != Item::FUNC_ITEM ||
+ ((Item_func*)item)->functype() != Item_func::GUSERVAR_FUNC))
{
Item_cache_int *cache= new Item_cache_int();
/* Mark the cache as non-const to prevent re-caching. */
--- 1.218/sql/item_sum.cc 2007-05-18 20:00:55 +05:00
+++ 1.219/sql/item_sum.cc 2007-05-18 20:00:55 +05:00
@@ -3360,6 +3360,10 @@ String* Item_func_group_concat::val_str(
DBUG_ASSERT(fixed == 1);
if (null_value)
return 0;
+ if (!result.length() && tree)
+ /* Tree is used for sorting as in ORDER BY */
+ tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
+ left_root_right);
if (count_cut_values && !warning)
{
/*
@@ -3371,11 +3375,6 @@ String* Item_func_group_concat::val_str(
ER_CUT_VALUE_GROUP_CONCAT,
ER(ER_CUT_VALUE_GROUP_CONCAT));
}
- if (result.length())
- return &result;
- if (tree)
- tree_walk(tree, (tree_walk_action)&dump_leaf_key, (void*)this,
- left_root_right);
return &result;
}
--- 1.61/sql/opt_sum.cc 2007-05-18 20:00:55 +05:00
+++ 1.62/sql/opt_sum.cc 2007-05-18 20:00:55 +05:00
@@ -249,12 +249,68 @@ int opt_sum_query(TABLE_LIST *tables, Li
if (!ref.key_length)
error= table->file->index_first(table->record[0]);
- else
- error= table->file->index_read(table->record[0],key_buff,
- make_prev_keypart_map(ref.key_parts),
- range_fl & NEAR_MIN ?
- HA_READ_AFTER_KEY :
- HA_READ_KEY_OR_NEXT);
+ else
+ {
+ /*
+ Use index to replace MIN/MAX functions with their values
+ according to the following rules:
+
+ 1) Insert the minimum non-null values where the WHERE clause still
+ matches, or
+ 2) a NULL value if there are only NULL values for key_part_k.
+ 3) Fail, producing a row of nulls
+
+ Implementation: Read the smallest value using the search key. If
+ the interval is open, read the next value after the search
+ key. If read fails, and we're looking for a MIN() value for a
+ nullable column, test if there is an exact match for the key.
+ */
+ if (!(range_fl & NEAR_MIN))
+ /*
+ Closed interval: Either The MIN argument is non-nullable, or
+ we have a >= predicate for the MIN argument.
+ */
+ error= table->file->index_read(table->record[0], ref.key_buff,
+ ref.key_length,
+ HA_READ_KEY_OR_NEXT);
+ else
+ {
+ /*
+ Open interval: There are two cases:
+ 1) We have only MIN() and the argument column is nullable, or
+ 2) there is a > predicate on it, nullability is irrelevant.
+ We need to scan the next bigger record first.
+ */
+ error= table->file->index_read(table->record[0], ref.key_buff,
+ ref.key_length, HA_READ_AFTER_KEY);
+ /*
+ If the found record is outside the group formed by the search
+ prefix, or there is no such record at all, check if all
+ records in that group have NULL in the MIN argument
+ column. If that is the case return that NULL.
+
+ Check if case 1 from above holds. If it does, we should read
+ the skipped tuple.
+ */
+ if (ref.key_buff[prefix_len] == 1 &&
+ /*
+ Last keypart (i.e. the argument to MIN) is set to NULL by
+ find_key_for_maxmin only if all other keyparts are bound
+ to constants in a conjunction of equalities. Hence, we
+ can detect this by checking only if the last keypart is
+ NULL.
+ */
+ (error == HA_ERR_KEY_NOT_FOUND ||
+ key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len)))
+ {
+ DBUG_ASSERT(item_field->field->real_maybe_null());
+ error= table->file->index_read(table->record[0], ref.key_buff,
+ ref.key_length,
+ HA_READ_KEY_EXACT);
+ }
+ }
+ }
+ /* Verify that the read tuple indeed matches the search key */
if (!error && reckey_in_range(0, &ref, item_field->field,
conds, range_fl, prefix_len))
error= HA_ERR_KEY_NOT_FOUND;
@@ -784,16 +840,26 @@ static bool find_key_for_maxmin(bool max
if (!max_fl && key_part_used == key_part_to_use && part->null_bit)
{
/*
- SELECT MIN(key_part2) FROM t1 WHERE key_part1=const
- If key_part2 may be NULL, then we want to find the first row
- that is not null
+ The query is on this form:
+
+ SELECT MIN(key_part_k)
+ FROM t1
+ WHERE key_part_1 = const and ... and key_part_k-1 = const
+
+ If key_part_k is nullable, we want to find the first matching row
+ where key_part_k is not null. The key buffer is now {const, ...,
+ NULL}. This will be passed to the handler along with a flag
+ indicating open interval. If a tuple is read that does not match
+ these search criteria, an attempt will be made to read an exact
+ match for the key buffer.
*/
+ /* Set the first byte of key_part_k to 1, that means NULL */
ref->key_buff[ref->key_length]= 1;
ref->key_length+= part->store_length;
ref->key_parts++;
DBUG_ASSERT(ref->key_parts == jdx+1);
*range_fl&= ~NO_MIN_RANGE;
- *range_fl|= NEAR_MIN; // > NULL
+ *range_fl|= NEAR_MIN; // Open interval
}
/*
The following test is false when the key in the key tree is
--- 1.520/sql/sql_select.cc 2007-05-18 20:00:55 +05:00
+++ 1.521/sql/sql_select.cc 2007-05-18 20:00:55 +05:00
@@ -988,6 +988,12 @@ JOIN::optimize()
}
}
+ if (conds &&!outer_join && const_table_map != found_const_table_map &&
+ (select_options & SELECT_DESCRIBE) &&
+ select_lex->master_unit() == &thd->lex->unit) // upper level SELECT
+ {
+ conds=new Item_int((longlong) 0,1); // Always false
+ }
if (make_join_select(this, select, conds))
{
zero_result_cause=
--- 1.149/sql/sql_union.cc 2007-05-18 20:00:55 +05:00
+++ 1.150/sql/sql_union.cc 2007-05-18 20:00:55 +05:00
@@ -147,8 +147,16 @@ st_select_lex_unit::init_prepare_fake_se
fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
(byte **)
&result_table_list.next_local);
- fake_select_lex->context.table_list= fake_select_lex->context.first_name_resolution_table=
+ fake_select_lex->context.table_list=
+ fake_select_lex->context.first_name_resolution_table=
fake_select_lex->get_table_list();
+ if (!fake_select_lex->first_execution)
+ {
+ for (ORDER *order= (ORDER *) global_parameters->order_list.first;
+ order;
+ order= order->next)
+ order->item= &order->item_ptr;
+ }
for (ORDER *order= (ORDER *)global_parameters->order_list.first;
order;
order=order->next)
--- 1.31/mysql-test/r/innodb_mysql.result 2007-05-18 20:00:55 +05:00
+++ 1.32/mysql-test/r/innodb_mysql.result 2007-05-18 20:00:55 +05:00
@@ -145,6 +145,27 @@ SELECT * FROM t1;
c1 cnt
1a 2
DROP TABLE t1;
+CREATE TABLE t1 (
+a1 decimal(10,0) DEFAULT NULL,
+a2 blob,
+a3 time DEFAULT NULL,
+a4 blob,
+a5 char(175) DEFAULT NULL,
+a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+a7 tinyblob,
+INDEX idx (a6,a7(239),a5)
+) ENGINE=InnoDB;
+EXPLAIN SELECT a4 FROM t1 WHERE
+a6=NULL AND
+a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
+t.a6=t.a6 AND t1.a6=NULL AND
+t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+DROP TABLE t1;
End of 4.1 tests
create table t1m (a int) engine = MEMORY;
create table t1i (a int);
--- 1.3.24.1/mysql-test/t/innodb_mysql.test 2007-05-18 20:00:55 +05:00
+++ 1.31/mysql-test/include/mix1.inc 2007-05-18 20:00:55 +05:00
@@ -1,9 +1,37 @@
--- source include/have_innodb.inc
+# include/mix1.inc
+#
+# The variables
+# $engine_type -- storage engine to be tested
+# $other_engine_type -- storage engine <> $engine_type
+# $other_engine_type must point to an all
+# time available storage engine
+# 2006-08 MySQL 5.1 MyISAM and MEMORY only
+# $test_foreign_keys -- 0, skip foreign key tests
+# -- 1, do not skip foreign key tests
+# have to be set before sourcing this script.
+#
+# Note: The comments/expectations refer to InnoDB.
+# They might be not valid for other storage engines.
+#
+# Last update:
+# 2006-08-15 ML refactoring of t/innodb_mysql.test
+# - shift main code of t/innodb_mysql.test to include/mix1.inc
+# - replace hardcoded assignment of storage engine by
+# use of $engine_type and $other_engine_type variables
+# - remove redundant replay testcase of
+# Bug#12882 min/max inconsistent on empty table
+# - corrected analyze table t1; to analyze table t4;
+# Much older versions of this test show that the table
+# where just some indexes have been created must be used.
+#
+
+eval SET SESSION STORAGE_ENGINE = $engine_type;
--disable_warnings
-drop table if exists t1,t2;
+drop table if exists t1,t2,t1m,t1i,t2m,t2i,t4;
--enable_warnings
+
# BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer
# (repeatable only w/innodb).
create table t1 (
@@ -11,8 +39,8 @@ create table t1 (
org_id int(11) default null,
unique key contacts$c_id (c_id),
key contacts$org_id (org_id)
-) engine=innodb;
-insert into t1 values
+);
+insert into t1 values
(2,null),(120,null),(141,null),(218,7), (128,1),
(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
@@ -35,7 +63,7 @@ create table t2 (
unique key t2$slai_id (slai_id),
key t2$owner_id (owner_id),
key t2$sla_id (sla_id)
-) engine=innodb;
+);
insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
@@ -43,17 +71,17 @@ insert into t2(slai_id, owner_tbl, owner
flush tables;
select si.slai_id
from t1 c join t2 si on
- ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
- ( si.owner_tbl = 2 and si.owner_id = c.c_id))
-where
+ ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
+ ( si.owner_tbl = 2 and si.owner_id = c.c_id))
+where
c.c_id = 218 and expiredate is null;
-
+
select * from t1 where org_id is null;
select si.slai_id
from t1 c join t2 si on
- ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
- ( si.owner_tbl = 2 and si.owner_id = c.c_id))
-where
+ ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
+ ( si.owner_tbl = 2 and si.owner_id = c.c_id))
+where
c.c_id = 218 and expiredate is null;
drop table t1, t2;
@@ -62,14 +90,14 @@ drop table t1, t2;
# Bug#17212: results not sorted correctly by ORDER BY when using index
# (repeatable only w/innodb because of index props)
#
-CREATE TABLE t1 (a int, b int, KEY b (b)) Engine=InnoDB;
-CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)) Engine=InnoDB;
-CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
- UNIQUE KEY b (b,c), KEY a (a,b,c)) Engine=InnoDB;
+CREATE TABLE t1 (a int, b int, KEY b (b));
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
+CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
+ UNIQUE KEY b (b,c), KEY a (a,b,c));
INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
-INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
+INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
+INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
INSERT INTO t2 SELECT a + 1, b FROM t2;
@@ -81,13 +109,13 @@ INSERT INTO t3 SELECT a + 4, a + 4, 3 FR
# demonstrate a problem when a must-use-sort table flag
# (sort_by_table=1) is being neglected.
-SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
- t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
+ t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
ORDER BY t1.b LIMIT 2;
# demonstrate the problem described in the bug report
-SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
- t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
+ t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
ORDER BY t1.b LIMIT 5;
DROP TABLE t1, t2, t3;
@@ -105,9 +133,9 @@ CREATE TABLE `t2` (
`id4` INT NOT NULL,
UNIQUE (`id2`,`id4`),
KEY (`id1`)
-) ENGINE=InnoDB;
+);
-INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
+INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
(1,1,1,0),
(1,1,2,1),
(5,1,2,2),
@@ -195,43 +223,22 @@ t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVk
DROP TABLE t1;
--echo End of 4.1 tests
+
+
#
# Bug #12882 min/max inconsistent on empty table
#
--disable_warnings
-create table t1m (a int) engine=myisam;
-create table t1i (a int) engine=innodb;
-create table t2m (a int) engine=myisam;
-create table t2i (a int) engine=innodb;
+eval create table t1m (a int) engine = $other_engine_type;
+create table t1i (a int);
+eval create table t2m (a int) engine = $other_engine_type;
+create table t2i (a int);
--enable_warnings
insert into t2m values (5);
insert into t2i values (5);
-# test with MyISAM
-select min(a) from t1m;
-select min(7) from t1m;
-select min(7) from DUAL;
-explain select min(7) from t2m join t1m;
-select min(7) from t2m join t1m;
-
-select max(a) from t1m;
-select max(7) from t1m;
-select max(7) from DUAL;
-explain select max(7) from t2m join t1m;
-select max(7) from t2m join t1m;
-
-select 1, min(a) from t1m where a=99;
-select 1, min(a) from t1m where 1=99;
-select 1, min(1) from t1m where a=99;
-select 1, min(1) from t1m where 1=99;
-
-select 1, max(a) from t1m where a=99;
-select 1, max(a) from t1m where 1=99;
-select 1, max(1) from t1m where a=99;
-select 1, max(1) from t1m where 1=99;
-
-# test with InnoDB
+# test with $engine_type
select min(a) from t1i;
select min(7) from t1i;
select min(7) from DUAL;
@@ -254,7 +261,7 @@ select 1, max(a) from t1i where 1=99;
select 1, max(1) from t1i where a=99;
select 1, max(1) from t1i where 1=99;
-# mixed MyISAM/InnoDB test
+# mixed $engine_type/$other_engine_type test
explain select count(*), min(7), max(7) from t1m, t1i;
select count(*), min(7), max(7) from t1m, t1i;
@@ -267,13 +274,13 @@ select count(*), min(7), max(7) from t2m
drop table t1m, t1i, t2m, t2i;
#
-# Bug #12672: primary key implcitly included in every innodb index
+# Bug #12882: primary key implcitly included in every innodb index
# (was part of group_min_max.test)
#
-create table t1 (
+eval create table t1 (
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
-);
+) ENGINE = $other_engine_type;
insert into t1 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
@@ -311,14 +318,14 @@ insert into t1 (a1, a2, b, c, d) values
--disable_warnings
create table t4 (
pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
-) engine=innodb;
+);
--enable_warnings
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
create index idx12672_0 on t4 (a1);
create index idx12672_1 on t4 (a1,a2,b,c);
create index idx12672_2 on t4 (a1,a2,b);
-analyze table t1;
+analyze table t4;
select distinct a1 from t4 where pk_col not in (1,2,3,4);
@@ -355,35 +362,35 @@ DROP TABLE t2, t1;
#
-# Bug #6142: a problem with the empty innodb table
+# Bug #6142: a problem with the empty innodb table
# (was part of group_min_max.test)
#
--disable_warnings
create table t1 (
a varchar(30), b varchar(30), primary key(a), key(b)
-) engine=innodb;
+);
--enable_warnings
select distinct a from t1;
drop table t1;
#
-# Bug #9798: group by with rollup
+# Bug #9798: group by with rollup
# (was part of group_min_max.test)
#
--disable_warnings
-create table t1(a int, key(a)) engine=innodb;
+create table t1(a int, key(a));
--enable_warnings
insert into t1 values(1);
select a, count(a) from t1 group by a with rollup;
drop table t1;
#
-# Bug #13293 Wrongly used index results in endless loop.
+# Bug #13293 Wrongly used index results in endless loop.
# (was part of group_min_max.test)
#
-create table t1 (f1 int, f2 char(1), primary key(f1,f2)) engine=innodb;
+create table t1 (f1 int, f2 char(1), primary key(f1,f2));
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
alter table t1 drop primary key, add primary key (f2, f1);
explain select distinct f1 a, f1 b from t1;
@@ -392,35 +399,31 @@ drop table t1;
#
# Test for bug #17164: ORed FALSE blocked conversion of outer join into join
-#
+#
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
- INDEX (name)) ENGINE=InnoDB;
-CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
- FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB;
+ INDEX (name));
+CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11));
+# CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
+# FOREIGN KEY (fkey) REFERENCES t2(id));
+if ($test_foreign_keys)
+{
+ ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
+}
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
EXPLAIN
-SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%' OR FALSE;
DROP TABLE t1,t2;
#
-# Bug#17530: Incorrect key truncation on table creation caused server crash.
-#
-create table t1(f1 varchar(800) binary not null, key(f1)) engine = innodb
- character set utf8 collate utf8_general_ci;
-insert into t1 values('aaa');
-drop table t1;
-
-
-#
# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
#
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
@@ -438,7 +441,6 @@ EXPLAIN SELECT b, SUM(c) FROM t1 GROUP B
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
DROP TABLE t1;
-
#
# Bug#26159: crash for a loose scan of a table that has been emptied
#
@@ -497,6 +499,40 @@ drop table test;
set global query_cache_type=@save_qcache_type;
set global query_cache_size=@save_qcache_size;
+--source include/innodb_rollback_on_timeout.inc
+
+#
+# Bug #27210: INNODB ON DUPLICATE KEY UPDATE
+#
+
+set @save_qcache_size=@@global.query_cache_size;
+set @save_qcache_type=@@global.query_cache_type;
+set global query_cache_size=10*1024*1024;
+set global query_cache_type=1;
+connect (con1,localhost,root,,);
+connection con1;
+drop table if exists `test`;
+CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
+ `test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
+ ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
+disconnect con1;
+connect (con2,localhost,root,,);
+connection con2;
+select * from test;
+INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
+ ON DUPLICATE KEY UPDATE `test2` = '1234';
+select * from test;
+flush tables;
+select * from test;
+disconnect con2;
+connection default;
+drop table test;
+set global query_cache_type=@save_qcache_type;
+set global query_cache_size=@save_qcache_size;
+
+--echo End of 5.0 tests
+
-- source include/have_innodb.inc
#
@@ -598,3 +634,137 @@ EXPLAIN SELECT COUNT(*) FROM t2 WHERE st
DROP TABLE t1,t2;
--echo End of 5.0 tests
+
+#
+# Test of behaviour with CREATE ... SELECT
+#
+
+CREATE TABLE t1 (a int, b int);
+insert into t1 values (1,1),(1,2);
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+CREATE TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t2;
+
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+COMMIT;
+BEGIN;
+INSERT INTO t2 values(101,101);
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error ER_DUP_ENTRY_WITH_KEY_NAME
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t1,t2;
+
+#
+# Bug#17530: Incorrect key truncation on table creation caused server crash.
+#
+create table t1(f1 varchar(800) binary not null, key(f1))
+ character set utf8 collate utf8_general_ci;
+insert into t1 values('aaa');
+drop table t1;
+
+
+#
+# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
+
+INSERT INTO t1 VALUES ( 1 , 1 , 1);
+INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
+INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
+
+EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
+EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
+DROP TABLE t1;
+
+--source include/innodb_rollback_on_timeout.inc
+
+--echo End of 5.0 tests
+
+
+# Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY
+# UPDATE": if the row is updated, it's like a regular UPDATE:
+# LAST_INSERT_ID() is not affected.
+CREATE TABLE `t2` (
+ `k` int(11) NOT NULL auto_increment,
+ `a` int(11) default NULL,
+ `c` int(11) default NULL,
+ PRIMARY KEY (`k`),
+ UNIQUE KEY `idx_1` (`a`)
+);
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+insert into t2 ( a ) values ( 7 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+# test again when last_insert_id() is 0 initially
+select last_insert_id(0);
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+
+# Test of LAST_INSERT_ID() when autogenerated will fail:
+# last_insert_id() should not change
+insert ignore into t2 values (null,6,1),(10,8,1);
+select last_insert_id();
+# First and second autogenerated will fail, last_insert_id() should
+# point to third
+insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
+select last_insert_id();
+select * from t2;
+
+# Test of the workaround which enables people to know the id of the
+# updated row in INSERT ON DUPLICATE KEY UPDATE, by using
+# LAST_INSERT_ID(autoinc_col) in the UPDATE clause.
+
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1, k=last_insert_id(k);
+select last_insert_id();
+select * from t2;
+
+drop table t2;
+
+--echo End of 5.1 tests
--- 1.186/mysql-test/r/subselect.result 2007-05-18 20:00:55 +05:00
+++ 1.187/mysql-test/r/subselect.result 2007-05-18 20:00:55 +05:00
@@ -4041,6 +4041,36 @@ id select_type table type possible_keys
1 PRIMARY t1 ref a a 5 const 1 Using where; Using index
2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
DROP TABLE t1;
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+(3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+SELECT id, st FROM t1
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id st
+3 FL
+1 GA
+7 FL
+SELECT id, st FROM t1
+WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id st
+1 GA
+3 FL
+7 FL
+SELECT id, st FROM t1
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+id st
+2 GA
+4 FL
+SELECT id, st FROM t1
+WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+GROUP BY id;
+id st
+2 GA
+4 FL
+DROP TABLE t1,t2;
End of 5.0 tests.
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
--- 1.154/mysql-test/t/subselect.test 2007-05-18 20:00:55 +05:00
+++ 1.155/mysql-test/t/subselect.test 2007-05-18 20:00:55 +05:00
@@ -2882,6 +2882,30 @@ INSERT INTO t1 VALUES (1,1),(2,1);
EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
DROP TABLE t1;
+#
+# Bug #28377: grouping query with a correlated subquery in WHERE condition
+#
+
+CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id));
+INSERT INTO t1 VALUES
+ (3,'FL'), (2,'GA'), (4,'FL'), (1,'GA'), (5,'NY'), (7,'FL'), (6,'NY');
+CREATE TABLE t2 (id int NOT NULL, INDEX idx(id));
+INSERT INTO t2 VALUES (7), (5), (1), (3);
+
+SELECT id, st FROM t1
+ WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1
+ WHERE st IN ('GA','FL') AND EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+ GROUP BY id;
+
+SELECT id, st FROM t1
+ WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id);
+SELECT id, st FROM t1
+ WHERE st IN ('GA','FL') AND NOT EXISTS(SELECT 1 FROM t2 WHERE t2.id=t1.id)
+ GROUP BY id;
+
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests.
#
--- 1.63/mysql-test/r/func_group.result 2007-05-18 20:00:55 +05:00
+++ 1.64/mysql-test/r/func_group.result 2007-05-18 20:00:55 +05:00
@@ -1321,4 +1321,51 @@ SELECT a,AVG(DISTINCT b) AS average FROM
a average
1 32768.5000
DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+SELECT MIN(a), MIN(b) FROM t1;
+MIN(a) MIN(b)
+NULL 1
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref a a 5 const 2 Using where
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+MIN(b) MIN(c)
+3 2
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+MIN(a) MIN(b)
+2 NULL
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+MIN(a) MIN(b)
+2 NULL
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+MIN(b) min(c)
+NULL 2
+CREATE TABLE t5( a INT, b INT, KEY( a, b) );
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+MIN(a) MIN(b)
+1 1
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+MIN(a) MIN(b)
+1 2
+DROP TABLE t1, t2, t3, t4, t5;
End of 5.0 tests
--- 1.47/mysql-test/r/type_datetime.result 2007-05-18 20:00:55 +05:00
+++ 1.48/mysql-test/r/type_datetime.result 2007-05-18 20:00:55 +05:00
@@ -346,6 +346,36 @@ call test27759();
a b a_then_b b_then_a c_then_a
2007-04-10 2007-04-11 2007-04-10 2007-04-10 2004-04-09 00:00:00
drop procedure test27759;
+create table t1 (f1 date);
+insert into t1 values (curdate());
+select left(f1,10) = curdate() from t1;
+left(f1,10) = curdate()
+1
+drop table t1;
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+Warnings:
+Warning 1292 Incorrect date value: '' for column 'f1' at row 1
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+if(@bug28261 = f1, '', @bug28261:= f1)
+2001-01-01
+2002-02-02
+2001-01-01
+2002-02-02
+drop table t1;
set @org_mode=@@sql_mode;
create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03');
Warnings:
--- 1.57/mysql-test/t/func_group.test 2007-05-18 20:00:55 +05:00
+++ 1.58/mysql-test/t/func_group.test 2007-05-18 20:00:55 +05:00
@@ -834,4 +834,38 @@ SELECT a,AVG(DISTINCT b) AS average FROM
DROP TABLE t1;
+#
+# Bug #27573: MIN() on an indexed column which is always NULL sets _other_
+# results to NULL
+#
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+SELECT MIN(a), MIN(b) FROM t1;
+
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+
+CREATE TABLE t5( a INT, b INT, KEY( a, b) );
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+
+DROP TABLE t1, t2, t3, t4, t5;
+
+###
--echo End of 5.0 tests
--- 1.29/mysql-test/t/type_datetime.test 2007-05-18 20:00:55 +05:00
+++ 1.30/mysql-test/t/type_datetime.test 2007-05-18 20:00:55 +05:00
@@ -227,6 +227,28 @@ call test27759();
drop procedure test27759;
#
+# Bug#28208: Wrong result of a non-const STRING function with a const
+# DATETIME function.
+#
+create table t1 (f1 date);
+insert into t1 values (curdate());
+select left(f1,10) = curdate() from t1;
+drop table t1;
+
+#
+# Bug#28261: Wrong DATETIME comparison result when the GET_USER_VAR function
+# is involved.
+#
+create table t1(f1 date);
+insert into t1 values('01-01-01'),('02-02-02'),('01-01-01'),('02-02-02');
+set @bug28261='';
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+select if(@bug28261 = f1, '', @bug28261:= f1) from t1;
+drop table t1;
+
+
+#
# Test of storing datetime into date fields
#
--- 1.106/mysql-test/r/ps.result 2007-05-18 20:00:55 +05:00
+++ 1.107/mysql-test/r/ps.result 2007-05-18 20:00:55 +05:00
@@ -1190,6 +1190,214 @@ EXECUTE b12651;
DROP VIEW b12651_V1;
DROP TABLE b12651_T1, b12651_T2;
DEALLOCATE PREPARE b12651;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (i INT);
+PREPARE st_19182
+FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1";
+EXECUTE st_19182;
+DESC t2;
+Field Type Null Key Default Extra
+j int(11) YES MUL NULL
+i int(11) YES MUL NULL
+DROP TABLE t2;
+EXECUTE st_19182;
+DESC t2;
+Field Type Null Key Default Extra
+j int(11) YES MUL NULL
+i int(11) YES MUL NULL
+DEALLOCATE PREPARE st_19182;
+DROP TABLE t2, t1;
+drop database if exists mysqltest;
+drop table if exists t1, t2;
+create database mysqltest character set utf8;
+prepare stmt1 from "create table mysqltest.t1 (c char(10))";
+prepare stmt2 from "create table mysqltest.t2 select 'test'";
+execute stmt1;
+execute stmt2;
+show create table mysqltest.t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c` char(10) default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8
+show create table mysqltest.t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `test` varchar(4) character set latin1 NOT NULL default ''
+) ENGINE=MyISAM DEFAULT CHARSET=utf8
+drop table mysqltest.t1;
+drop table mysqltest.t2;
+alter database mysqltest character set latin1;
+execute stmt1;
+execute stmt2;
+show create table mysqltest.t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c` char(10) character set utf8 default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show create table mysqltest.t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `test` varchar(4) NOT NULL default ''
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop database mysqltest;
+deallocate prepare stmt1;
+deallocate prepare stmt2;
+execute stmt;
+show create table t1;
+drop table t1;
+execute stmt;
+show create table t1;
+drop table t1;
+deallocate prepare stmt;
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+PREPARE st1 FROM
+'(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+EXECUTE st1;
+a
+1
+2
+3
+11
+12
+13
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
+End of 4.1 tests.
+create table t1 (a varchar(20));
+insert into t1 values ('foo');
+prepare stmt FROM 'SELECT char_length (a) FROM t1';
+ERROR 42000: FUNCTION test.char_length does not exist
+drop table t1;
+create table t1 (a char(3) not null, b char(3) not null,
+c char(3) not null, primary key (a, b, c));
+create table t2 like t1;
+prepare stmt from
+"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b)
+ where t1.a=1";
+execute stmt;
+a
+execute stmt;
+a
+execute stmt;
+a
+prepare stmt from
+"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from
+(t1 left outer join t2 on t2.a=? and t1.b=t2.b)
+left outer join t2 t3 on t3.a=? where t1.a=?";
+set @a:=1, @b:=1, @c:=1;
+execute stmt using @a, @b, @c;
+a b c a b c
+execute stmt using @a, @b, @c;
+a b c a b c
+execute stmt using @a, @b, @c;
+a b c a b c
+deallocate prepare stmt;
+drop table t1,t2;
+SET @aux= "SELECT COUNT(*)
+ FROM INFORMATION_SCHEMA.COLUMNS A,
+ INFORMATION_SCHEMA.COLUMNS B
+ WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
+ AND A.TABLE_NAME = B.TABLE_NAME
+ AND A.COLUMN_NAME = B.COLUMN_NAME AND
+ A.TABLE_NAME = 'user'";
+prepare my_stmt from @aux;
+execute my_stmt;
+COUNT(*)
+37
+execute my_stmt;
+COUNT(*)
+37
+execute my_stmt;
+COUNT(*)
+37
+deallocate prepare my_stmt;
+drop procedure if exists p1|
+drop table if exists t1|
+create table t1 (id int)|
+insert into t1 values(1)|
+create procedure p1(a int, b int)
+begin
+declare c int;
+select max(id)+1 into c from t1;
+insert into t1 select a+b;
+insert into t1 select a-b;
+insert into t1 select a-c;
+end|
+set @a= 3, @b= 4|
+prepare stmt from "call p1(?, ?)"|
+execute stmt using @a, @b|
+execute stmt using @a, @b|
+select * from t1|
+id
+1
+7
+-1
+1
+7
+-1
+-5
+deallocate prepare stmt|
+drop procedure p1|
+drop table t1|
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+prepare stmt from "select * from t1 limit ?, ?";
+set @offset=0, @limit=1;
+execute stmt using @offset, @limit;
+a
+1
+select * from t1 limit 0, 1;
+a
+1
+set @offset=3, @limit=2;
+execute stmt using @offset, @limit;
+a
+4
+5
+select * from t1 limit 3, 2;
+a
+4
+5
+prepare stmt from "select * from t1 limit ?";
+execute stmt using @limit;
+a
+1
+2
+prepare stmt from "select * from t1 where a in (select a from t1 limit ?)";
+ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
+prepare stmt from "select * from t1 union all select * from t1 limit ?, ?";
+set @offset=9;
+set @limit=2;
+execute stmt using @offset, @limit;
+a
+10
+1
+prepare stmt from "(select * from t1 limit ?, ?) union all
+ (select * from t1 limit ?, ?) order by a limit ?";
+execute stmt using @offset, @limit, @offset, @limit, @limit;
+a
+10
+10
+drop table t1;
+deallocate prepare stmt;
+CREATE TABLE b12651_T1(a int) ENGINE=MYISAM;
+CREATE TABLE b12651_T2(b int) ENGINE=MYISAM;
+CREATE VIEW b12651_V1 as SELECT b FROM b12651_T2;
+PREPARE b12651 FROM 'SELECT 1 FROM b12651_T1 WHERE a IN (SELECT b FROM b12651_V1)';
+EXECUTE b12651;
+1
+DROP VIEW b12651_V1;
+DROP TABLE b12651_T1, b12651_T2;
+DEALLOCATE PREPARE b12651;
create table t1 (id int);
prepare ins_call from "insert into t1 (id) values (1)";
execute ins_call;
--- 1.109/mysql-test/t/ps.test 2007-05-18 20:00:55 +05:00
+++ 1.110/mysql-test/t/ps.test 2007-05-18 20:00:55 +05:00
@@ -1120,6 +1120,114 @@ DROP TABLE t1;
--echo End of 4.1 tests.
+#
+# Bug#19182: CREATE TABLE bar (m INT) SELECT n FROM foo; doesn't work
+# from stored procedure.
+#
+# The cause of a bug was that cached LEX::create_list was modified,
+# and then together with LEX::key_list was reset.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+PREPARE st_19182
+FROM "CREATE TABLE t2 (i INT, j INT, KEY (i), KEY(j)) SELECT i FROM t1";
+
+EXECUTE st_19182;
+DESC t2;
+
+DROP TABLE t2;
+
+# Check that on second execution we don't loose 'j' column and the keys
+# on 'i' and 'j' columns.
+EXECUTE st_19182;
+DESC t2;
+
+DEALLOCATE PREPARE st_19182;
+DROP TABLE t2, t1;
+
+#
+# Bug #22060 "ALTER TABLE x AUTO_INCREMENT=y in SP crashes server"
+#
+# Code which implemented CREATE/ALTER TABLE and CREATE DATABASE
+# statement modified HA_CREATE_INFO structure in LEX, making these
+# statements PS/SP-unsafe (their re-execution might have resulted
+# in incorrect results).
+#
+--disable_warnings
+drop database if exists mysqltest;
+drop table if exists t1, t2;
+--enable_warnings
+# CREATE TABLE and CREATE TABLE ... SELECT
+create database mysqltest character set utf8;
+prepare stmt1 from "create table mysqltest.t1 (c char(10))";
+prepare stmt2 from "create table mysqltest.t2 select 'test'";
+execute stmt1;
+execute stmt2;
+show create table mysqltest.t1;
+show create table mysqltest.t2;
+drop table mysqltest.t1;
+drop table mysqltest.t2;
+alter database mysqltest character set latin1;
+execute stmt1;
+execute stmt2;
+show create table mysqltest.t1;
+show create table mysqltest.t2;
+drop database mysqltest;
+deallocate prepare stmt1;
+deallocate prepare stmt2;
+#
+# CREATE TABLE with DATA DIRECTORY option
+#
+# Protect ourselves from data left in tmp/ by a previos possibly failed
+# test
+--system rm -f $MYSQLTEST_VARDIR/tmp/t1.*
+--disable_warnings
+--disable_query_log
+eval prepare stmt from "create table t1 (c char(10)) data directory='$MYSQLTEST_VARDIR/tmp'";
+--enable_query_log
+execute stmt;
+#
+# DATA DIRECTORY option does not always work: if the operating
+# system does not support symlinks, have_symlinks option is automatically
+# disabled.
+# In this case DATA DIRECTORY is silently ignored when
+# creating a table, and is not output by SHOW CREATE TABLE.
+#
+--disable_result_log
+show create table t1;
+--enable_result_log
+drop table t1;
+execute stmt;
+--disable_result_log
+show create table t1;
+--enable_result_log
+--enable_warnings
+drop table t1;
+deallocate prepare stmt;
+#
+
+#
+# Bug #27937: crash on the second execution for prepared statement
+# from UNION with ORDER BY an expression containing RAND()
+#
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES (2), (3), (1);
+
+PREPARE st1 FROM
+ '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()*0+a';
+
+EXECUTE st1;
+EXECUTE st1;
+
+DEALLOCATE PREPARE st1;
+DROP TABLE t1;
+
+--echo End of 4.1 tests.
############################# 5.0 tests start ################################
#
--- 1.68/mysql-test/r/func_gconcat.result 2007-05-18 20:00:55 +05:00
+++ 1.69/mysql-test/r/func_gconcat.result 2007-05-18 20:00:55 +05:00
@@ -743,4 +743,30 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(b)) F
GROUP_CONCAT(DISTINCT UCASE(b))
ONE.1,TWO.2,ONE.3
DROP TABLE t1;
+CREATE TABLE t1( a VARCHAR( 10 ), b INT );
+INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1),
+( repeat( 'b', 10 ), 2);
+SET group_concat_max_len = 20;
+SELECT GROUP_CONCAT( a ) FROM t1;
+GROUP_CONCAT( a )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning 1260 1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;
+GROUP_CONCAT( DISTINCT a )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning 1260 1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
+GROUP_CONCAT( a ORDER BY b )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning 1260 1 line(s) were cut by GROUP_CONCAT()
+SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
+GROUP_CONCAT( DISTINCT a ORDER BY b )
+aaaaaaaaaa,bbbbbbbbb
+Warnings:
+Warning 1260 1 line(s) were cut by GROUP_CONCAT()
+SET group_concat_max_len = DEFAULT;
+DROP TABLE t1;
End of 5.0 tests
--- 1.57/mysql-test/t/func_gconcat.test 2007-05-18 20:00:55 +05:00
+++ 1.58/mysql-test/t/func_gconcat.test 2007-05-18 20:00:55 +05:00
@@ -507,4 +507,18 @@ SELECT GROUP_CONCAT(DISTINCT UCASE(a)) F
SELECT GROUP_CONCAT(DISTINCT UCASE(b)) FROM t1;
DROP TABLE t1;
+#
+# Bug #28273: GROUP_CONCAT and ORDER BY: No warning when result gets truncated.
+#
+CREATE TABLE t1( a VARCHAR( 10 ), b INT );
+INSERT INTO t1 VALUES ( repeat( 'a', 10 ), 1),
+ ( repeat( 'b', 10 ), 2);
+SET group_concat_max_len = 20;
+SELECT GROUP_CONCAT( a ) FROM t1;
+SELECT GROUP_CONCAT( DISTINCT a ) FROM t1;
+SELECT GROUP_CONCAT( a ORDER BY b ) FROM t1;
+SELECT GROUP_CONCAT( DISTINCT a ORDER BY b ) FROM t1;
+SET group_concat_max_len = DEFAULT;
+DROP TABLE t1;
+
--echo End of 5.0 tests
| Thread |
|---|
| • bk commit into 5.1 tree (holyfoot:1.2520) | holyfoot | 18 May |
