Below is the list of changes that have just been committed into a local
4.1 repository of monty. When monty 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://www.mysql.com/doc/I/n/Installing_source_tree.html
ChangeSet
1.1447 03/02/10 17:59:16 monty@stripped +19 -0
Move tmp_table_used to THD
Optimize depending sub querys
Remove valgrind warnings
sql/sql_table.cc
1.147 03/02/10 17:59:13 monty@stripped +2 -2
Move tmp_table_used to THD
sql/sql_select.cc
1.203 03/02/10 17:59:13 monty@stripped +12 -8
optimize depending sub querys
sql/sql_parse.cc
1.268 03/02/10 17:59:13 monty@stripped +1 -0
Move tmp_table_used to THD
sql/sql_lex.h
1.118 03/02/10 17:59:13 monty@stripped +2 -1
Move tmp_table_used to THD
sql/sql_lex.cc
1.70 03/02/10 17:59:13 monty@stripped +1 -3
Move tmp_table_used to THD
sql/sql_class.h
1.145 03/02/10 17:59:13 monty@stripped +1 -0
Move tmp_table_used to THD
sql/sql_class.cc
1.104 03/02/10 17:59:13 monty@stripped +1 -1
Move tmp_table_used to THD
sql/sql_base.cc
1.141 03/02/10 17:59:13 monty@stripped +1 -1
Move tmp_table_used to THD
sql/log_event.cc
1.101 03/02/10 17:59:13 monty@stripped +1 -1
Move tmp_table_used to THD
sql/item_subselect.cc
1.56 03/02/10 17:59:13 monty@stripped +2 -2
Better debugging names
sql/item_strfunc.h
1.45 03/02/10 17:59:13 monty@stripped +1 -1
Fixed memory overrun
sql/item_row.cc
1.14 03/02/10 17:59:13 monty@stripped +1 -1
Remove warning
sql/item_func.cc
1.108 03/02/10 17:59:13 monty@stripped +2 -2
Remove warning for uninitalized data
sql/item.h
1.56 03/02/10 17:59:13 monty@stripped +6 -3
optimize depending sub querys
sql/item.cc
1.57 03/02/10 17:59:13 monty@stripped +1 -1
Indentation fix
mysql-test/t/subselect.test
1.56 03/02/10 17:59:13 monty@stripped +79 -68
Don't use table t1
mysql-test/r/subselect.result
1.62 03/02/10 17:59:13 monty@stripped +123 -108
Don't use table t1
mysql-test/mysql-test-run.sh
1.160 03/02/10 17:59:13 monty@stripped +1 -1
Remove wrong `
libmysqld/lib_sql.cc
1.46 03/02/10 17:59:13 monty@stripped +1 -2
Better way to reset errors
# 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: monty
# Host: mashka.mysql.fi
# Root: /home/my/mysql-4.1
--- 1.159/mysql-test/mysql-test-run.sh Tue Feb 4 09:54:18 2003
+++ 1.160/mysql-test/mysql-test-run.sh Mon Feb 10 17:59:13 2003
@@ -623,7 +623,7 @@
# Find errors
for i in "^Warning:" "^Error:" "^==.* at 0x"
do
- if `$GREP "$i" $MY_LOG_DIR/warnings.tmp >> $MY_LOG_DIR/warnings`
+ if $GREP "$i" $MY_LOG_DIR/warnings.tmp >> $MY_LOG_DIR/warnings
then
found_error=1
fi
--- 1.56/sql/item.cc Tue Feb 4 09:54:20 2003
+++ 1.57/sql/item.cc Mon Feb 10 17:59:13 2003
@@ -286,7 +286,7 @@
{
if (field->table->const_table)
return 0; // const item
- return (depended_from? RAND_TABLE_BIT : field->table->map);
+ return (depended_from ? RAND_TABLE_BIT : field->table->map);
}
Item *Item_field::get_tmp_table_item(THD *thd)
--- 1.55/sql/item.h Tue Feb 4 21:52:10 2003
+++ 1.56/sql/item.h Mon Feb 10 17:59:13 2003
@@ -102,6 +102,7 @@
virtual void save_in_result_field(bool no_conversions) {}
virtual void no_rows_in_result() {}
virtual Item *copy_or_same(THD *thd) { return this; }
+ virtual Item *real_item() { return this; }
virtual Item *get_tmp_table_item(THD *thd) { return copy_or_same(thd); }
virtual bool binary() const
@@ -468,9 +469,10 @@
public:
Field *result_field; /* Save result here */
Item **ref;
- Item_ref(char *db_par,char *table_name_par,char *field_name_par)
+ Item_ref(const char *db_par, const char *table_name_par,
+ const char *field_name_par)
:Item_ident(db_par,table_name_par,field_name_par),ref(0) {}
- Item_ref(Item **item, char *table_name_par,char *field_name_par)
+ Item_ref(Item **item, const char *table_name_par, const char *field_name_par)
:Item_ident(NullS,table_name_par,field_name_par),ref(item) {}
// Constructor need to process subselect with temporary tables (see Item)
Item_ref(THD *thd, Item_ref &item)
@@ -521,6 +523,7 @@
{
(*ref)->save_in_field(result_field, no_conversions);
}
+ Item *real_item() { return *ref; }
};
class Item_in_subselect;
@@ -530,7 +533,7 @@
Item_in_subselect* owner;
public:
Item_ref_null_helper(Item_in_subselect* master, Item **item,
- char *table_name_par, char *field_name_par):
+ const char *table_name_par, const char *field_name_par):
Item_ref(item, table_name_par, field_name_par), owner(master) {}
double val();
longlong val_int();
--- 1.107/sql/item_func.cc Fri Feb 7 15:47:07 2003
+++ 1.108/sql/item_func.cc Mon Feb 10 17:59:13 2003
@@ -120,10 +120,10 @@
used_tables_cache|=(*arg)->used_tables();
const_item_cache&= (*arg)->const_item();
}
- if (result_type() == STRING_RESULT)
- set_charset((*args)->charset());
}
fix_length_and_dec();
+ if (result_type() == STRING_RESULT)
+ set_charset((*args)->charset());
fixed= 1;
return 0;
}
--- 1.44/sql/item_strfunc.h Fri Feb 7 15:47:07 2003
+++ 1.45/sql/item_strfunc.h Mon Feb 10 17:59:13 2003
@@ -262,7 +262,7 @@
class Item_func_old_password :public Item_str_func
{
- char tmp_value[16]; /* old password length */
+ char tmp_value[17]; /* old password length +1 */
public:
Item_func_old_password(Item *a) :Item_str_func(a) {}
String *val_str(String *);
--- 1.100/sql/log_event.cc Sun Feb 2 17:39:24 2003
+++ 1.101/sql/log_event.cc Mon Feb 10 17:59:13 2003
@@ -766,7 +766,7 @@
#ifndef MYSQL_CLIENT
Query_log_event::Query_log_event(THD* thd_arg, const char* query_arg,
ulong query_length, bool using_trans)
- :Log_event(thd_arg, !thd_arg->lex.tmp_table_used ?
+ :Log_event(thd_arg, !thd_arg->tmp_table_used ?
0 : LOG_EVENT_THREAD_SPECIFIC_F, using_trans),
data_buf(0), query(query_arg),
db(thd_arg->db), q_len((uint32) query_length),
--- 1.140/sql/sql_base.cc Tue Feb 4 09:54:22 2003
+++ 1.141/sql/sql_base.cc Mon Feb 10 17:59:13 2003
@@ -792,7 +792,7 @@
DBUG_RETURN(0);
}
table->query_id=thd->query_id;
- thd->lex.tmp_table_used= 1;
+ thd->tmp_table_used= 1;
goto reset;
}
}
--- 1.103/sql/sql_class.cc Tue Feb 4 09:54:22 2003
+++ 1.104/sql/sql_class.cc Mon Feb 10 17:59:13 2003
@@ -87,7 +87,7 @@
locked=killed=count_cuted_fields=some_tables_deleted=no_errors=password=
query_start_used=prepare_command=0;
db_length=query_length=col_access=0;
- query_error=0;
+ query_error= tmp_table_used= 0;
next_insert_id=last_insert_id=0;
open_tables= temporary_tables= handler_tables= derived_tables= 0;
current_tablenr=0;
--- 1.144/sql/sql_class.h Tue Feb 4 21:52:10 2003
+++ 1.145/sql/sql_class.h Mon Feb 10 17:59:13 2003
@@ -550,6 +550,7 @@
bool query_error, bootstrap, cleanup_done;
bool volatile killed;
bool prepare_command;
+ bool tmp_table_used;
/*
If we do a purge of binary logs, log index info of the threads
--- 1.69/sql/sql_lex.cc Tue Jan 28 14:48:10 2003
+++ 1.70/sql/sql_lex.cc Mon Feb 10 17:59:13 2003
@@ -167,7 +167,6 @@
lex->slave_thd_opt=0;
lex->sql_command=SQLCOM_END;
lex->safe_to_cache_query= 1;
- lex->tmp_table_used= 0;
bzero(&lex->mi,sizeof(lex->mi));
return lex;
}
@@ -1051,8 +1050,7 @@
use_index.empty();
ftfunc_list_alloc.empty();
ftfunc_list= &ftfunc_list_alloc;
- if (linkage != UNION_TYPE)
- linkage= UNSPECIFIED_TYPE;
+ linkage= UNSPECIFIED_TYPE;
}
/*
--- 1.117/sql/sql_lex.h Sun Feb 2 23:29:59 2003
+++ 1.118/sql/sql_lex.h Mon Feb 10 17:59:13 2003
@@ -221,9 +221,11 @@
static void *operator new(size_t size)
{
+ // TODO: Change to alloc() and explicitely clear elements in constructors
return (void*) sql_calloc((uint) size);
}
static void operator delete(void *ptr,size_t size) {}
+ st_select_lex_node() {}
virtual ~st_select_lex_node() {}
inline st_select_lex_node* get_master() { return master; }
virtual void init_query();
@@ -475,7 +477,6 @@
uint slave_thd_opt;
CHARSET_INFO *charset;
char *help_arg;
- bool tmp_table_used;
inline void uncacheable()
{
--- 1.267/sql/sql_parse.cc Fri Feb 7 15:55:58 2003
+++ 1.268/sql/sql_parse.cc Mon Feb 10 17:59:13 2003
@@ -3181,6 +3181,7 @@
thd->sent_row_count= thd->examined_row_count= 0;
thd->is_fatal_error= thd->rand_used= 0;
thd->server_status &= ~SERVER_MORE_RESULTS_EXISTS;
+ thd->tmp_table_used= 0;
if (opt_bin_log)
reset_dynamic(&thd->user_var_events);
--- 1.202/sql/sql_select.cc Fri Feb 7 15:55:59 2003
+++ 1.203/sql/sql_select.cc Mon Feb 10 17:59:13 2003
@@ -1902,9 +1902,10 @@
case Item_func::OPTIMIZE_NONE:
break;
case Item_func::OPTIMIZE_KEY:
- if (cond_func->key_item()->type() == Item::FIELD_ITEM)
+ if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM)
add_key_field(key_fields,*and_level,
- ((Item_field*) (cond_func->key_item()))->field,
+ ((Item_field*) (cond_func->key_item()->real_item()))
+ ->field,
0,(Item*) 0,usable_tables);
break;
case Item_func::OPTIMIZE_OP:
@@ -1912,18 +1913,20 @@
bool equal_func=(cond_func->functype() == Item_func::EQ_FUNC ||
cond_func->functype() == Item_func::EQUAL_FUNC);
- if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM)
+ if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM)
{
add_key_field(key_fields,*and_level,
- ((Item_field*) (cond_func->arguments()[0]))->field,
+ ((Item_field*) (cond_func->arguments()[0])->real_item())
+ ->field,
equal_func,
(cond_func->arguments()[1]),usable_tables);
}
- if (cond_func->arguments()[1]->type() == Item::FIELD_ITEM &&
+ if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
cond_func->functype() != Item_func::LIKE_FUNC)
{
add_key_field(key_fields,*and_level,
- ((Item_field*) (cond_func->arguments()[1]))->field,
+ ((Item_field*) (cond_func->arguments()[1])->real_item())
+ ->field,
equal_func,
(cond_func->arguments()[0]),usable_tables);
}
@@ -1931,10 +1934,11 @@
}
case Item_func::OPTIMIZE_NULL:
/* column_name IS [NOT] NULL */
- if (cond_func->arguments()[0]->type() == Item::FIELD_ITEM)
+ if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM)
{
add_key_field(key_fields,*and_level,
- ((Item_field*) (cond_func->arguments()[0]))->field,
+ ((Item_field*) (cond_func->arguments()[0])->real_item())
+ ->field,
cond_func->functype() == Item_func::ISNULL_FUNC,
new Item_null, usable_tables);
}
--- 1.146/sql/sql_table.cc Tue Feb 4 09:54:23 2003
+++ 1.147/sql/sql_table.cc Mon Feb 10 17:59:13 2003
@@ -250,7 +250,7 @@
wrong_tables.append(String(table->real_name,default_charset_info));
}
}
- thd->lex.tmp_table_used= tmp_table_deleted;
+ thd->tmp_table_used= tmp_table_deleted;
if (some_tables_deleted || tmp_table_deleted)
{
query_cache_invalidate3(thd, tables, 0);
@@ -885,7 +885,7 @@
(void) rm_temporary_table(create_info->db_type, path);
goto end;
}
- thd->lex.tmp_table_used= 1;
+ thd->tmp_table_used= 1;
}
if (!tmp_table && !no_log)
{
--- 1.61/mysql-test/r/subselect.result Thu Jan 30 22:15:42 2003
+++ 1.62/mysql-test/r/subselect.result Mon Feb 10 17:59:13 2003
@@ -641,168 +641,183 @@
drop table t1, t2, t3;
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
No tables used
-CREATE TABLE t (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
-INSERT INTO t VALUES (1),(2);
-SELECT * FROM t WHERE id IN (SELECT 1);
+CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t2 VALUES (1),(2);
+SELECT * FROM t2 WHERE id IN (SELECT 1);
id
1
-EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1);
+EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t ref id id 5 const 1 Using where; Using index
+1 PRIMARY t2 ref id id 5 const 1 Using where; Using index
Warnings:
Note 1247 Select 2 was reduced during optimisation
-SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
+SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id
1
-SELECT * FROM t WHERE id IN (SELECT 1+(select 1));
+SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id
2
-EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1+(select 1));
+EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t ref id id 5 const 1 Using where; Using index
+1 PRIMARY t2 ref id id 5 const 1 Using where; Using index
3 SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
Note 1247 Select 3 was reduced during optimisation
Note 1247 Select 2 was reduced during optimisation
-EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
+EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t index NULL id 5 NULL 2 Using where; Using index
+1 PRIMARY t2 index NULL id 5 NULL 2 Using where; Using index
2 DEPENDENT SUBSELECT NULL NULL NULL NULL NULL NULL NULL No tables used
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
-SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 3);
+SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
id
-SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2);
+SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
id
2
-INSERT INTO t VALUES ((SELECT * FROM t));
-You can't specify target table 't' for update in FROM clause
-SELECT * FROM t;
+INSERT INTO t2 VALUES ((SELECT * FROM t2));
+You can't specify target table 't2' for update in FROM clause
+SELECT * FROM t2;
id
1
2
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 values (1),(1);
-UPDATE t SET id=(SELECT * FROM t1);
+UPDATE t2 SET id=(SELECT * FROM t1);
Subselect returns more than 1 record
-drop table t, t1;
-create table t (a int);
-insert into t values (1),(2),(3);
-select 1 IN (SELECT * from t);
-1 IN (SELECT * from t)
+drop table t2, t1;
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+select 1 IN (SELECT * from t1);
+1 IN (SELECT * from t1)
1
-select 10 IN (SELECT * from t);
-10 IN (SELECT * from t)
+select 10 IN (SELECT * from t1);
+10 IN (SELECT * from t1)
0
-select NULL IN (SELECT * from t);
-NULL IN (SELECT * from t)
+select NULL IN (SELECT * from t1);
+NULL IN (SELECT * from t1)
NULL
-update t set a=NULL where a=2;
-select 1 IN (SELECT * from t);
-1 IN (SELECT * from t)
+update t1 set a=NULL where a=2;
+select 1 IN (SELECT * from t1);
+1 IN (SELECT * from t1)
1
-select 3 IN (SELECT * from t);
-3 IN (SELECT * from t)
+select 3 IN (SELECT * from t1);
+3 IN (SELECT * from t1)
1
-select 10 IN (SELECT * from t);
-10 IN (SELECT * from t)
+select 10 IN (SELECT * from t1);
+10 IN (SELECT * from t1)
NULL
-select 1 > ALL (SELECT * from t);
-1 > ALL (SELECT * from t)
+select 1 > ALL (SELECT * from t1);
+1 > ALL (SELECT * from t1)
0
-select 10 > ALL (SELECT * from t);
-10 > ALL (SELECT * from t)
+select 10 > ALL (SELECT * from t1);
+10 > ALL (SELECT * from t1)
NULL
-select 1 > ANY (SELECT * from t);
-1 > ANY (SELECT * from t)
+select 1 > ANY (SELECT * from t1);
+1 > ANY (SELECT * from t1)
NULL
-select 10 > ANY (SELECT * from t);
-10 > ANY (SELECT * from t)
+select 10 > ANY (SELECT * from t1);
+10 > ANY (SELECT * from t1)
1
-drop table t;
-create table t (a varchar(20));
-insert into t values ('A'),('BC'),('DEF');
-select 'A' IN (SELECT * from t);
-'A' IN (SELECT * from t)
+drop table t1;
+create table t1 (a varchar(20));
+insert into t1 values ('A'),('BC'),('DEF');
+select 'A' IN (SELECT * from t1);
+'A' IN (SELECT * from t1)
1
-select 'XYZS' IN (SELECT * from t);
-'XYZS' IN (SELECT * from t)
+select 'XYZS' IN (SELECT * from t1);
+'XYZS' IN (SELECT * from t1)
0
-select NULL IN (SELECT * from t);
-NULL IN (SELECT * from t)
+select NULL IN (SELECT * from t1);
+NULL IN (SELECT * from t1)
NULL
-update t set a=NULL where a='BC';
-select 'A' IN (SELECT * from t);
-'A' IN (SELECT * from t)
+update t1 set a=NULL where a='BC';
+select 'A' IN (SELECT * from t1);
+'A' IN (SELECT * from t1)
1
-select 'DEF' IN (SELECT * from t);
-'DEF' IN (SELECT * from t)
+select 'DEF' IN (SELECT * from t1);
+'DEF' IN (SELECT * from t1)
1
-select 'XYZS' IN (SELECT * from t);
-'XYZS' IN (SELECT * from t)
+select 'XYZS' IN (SELECT * from t1);
+'XYZS' IN (SELECT * from t1)
NULL
-select 'A' > ALL (SELECT * from t);
-'A' > ALL (SELECT * from t)
+select 'A' > ALL (SELECT * from t1);
+'A' > ALL (SELECT * from t1)
0
-select 'XYZS' > ALL (SELECT * from t);
-'XYZS' > ALL (SELECT * from t)
+select 'XYZS' > ALL (SELECT * from t1);
+'XYZS' > ALL (SELECT * from t1)
NULL
-select 'A' > ANY (SELECT * from t);
-'A' > ANY (SELECT * from t)
+select 'A' > ANY (SELECT * from t1);
+'A' > ANY (SELECT * from t1)
NULL
-select 'XYZS' > ANY (SELECT * from t);
-'XYZS' > ANY (SELECT * from t)
+select 'XYZS' > ANY (SELECT * from t1);
+'XYZS' > ANY (SELECT * from t1)
1
-drop table t;
-create table t (a float);
-insert into t values (1.5),(2.5),(3.5);
-select 1.5 IN (SELECT * from t);
-1.5 IN (SELECT * from t)
+drop table t1;
+create table t1 (a float);
+insert into t1 values (1.5),(2.5),(3.5);
+select 1.5 IN (SELECT * from t1);
+1.5 IN (SELECT * from t1)
1
-select 10.5 IN (SELECT * from t);
-10.5 IN (SELECT * from t)
+select 10.5 IN (SELECT * from t1);
+10.5 IN (SELECT * from t1)
0
-select NULL IN (SELECT * from t);
-NULL IN (SELECT * from t)
+select NULL IN (SELECT * from t1);
+NULL IN (SELECT * from t1)
NULL
-update t set a=NULL where a=2.5;
-select 1.5 IN (SELECT * from t);
-1.5 IN (SELECT * from t)
+update t1 set a=NULL where a=2.5;
+select 1.5 IN (SELECT * from t1);
+1.5 IN (SELECT * from t1)
1
-select 3.5 IN (SELECT * from t);
-3.5 IN (SELECT * from t)
+select 3.5 IN (SELECT * from t1);
+3.5 IN (SELECT * from t1)
1
-select 10.5 IN (SELECT * from t);
-10.5 IN (SELECT * from t)
+select 10.5 IN (SELECT * from t1);
+10.5 IN (SELECT * from t1)
NULL
-select 1.5 > ALL (SELECT * from t);
-1.5 > ALL (SELECT * from t)
+select 1.5 > ALL (SELECT * from t1);
+1.5 > ALL (SELECT * from t1)
0
-select 10.5 > ALL (SELECT * from t);
-10.5 > ALL (SELECT * from t)
+select 10.5 > ALL (SELECT * from t1);
+10.5 > ALL (SELECT * from t1)
NULL
-select 1.5 > ANY (SELECT * from t);
-1.5 > ANY (SELECT * from t)
+select 1.5 > ANY (SELECT * from t1);
+1.5 > ANY (SELECT * from t1)
NULL
-select 10.5 > ANY (SELECT * from t);
-10.5 > ANY (SELECT * from t)
+select 10.5 > ANY (SELECT * from t1);
+10.5 > ANY (SELECT * from t1)
1
-explain select (select a+1) from t;
+explain select (select a+1) from t1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t ALL NULL NULL NULL NULL 3
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3
Warnings:
Note 1247 Select 2 was reduced during optimisation
-select (select a+1) from t;
+select (select a+1) from t1;
(select a+1)
2.5
NULL
4.5
-drop table t;
-create table t (a float);
-select 10.5 IN (SELECT * from t LIMIT 1);
+drop table t1;
+CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a));
+CREATE TABLE t2 (a int(11) default '0', INDEX (a));
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t2 VALUES (1),(2),(3);
+SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
+a t1.a in (select t2.a from t2)
+1 1
+2 1
+3 1
+4 0
+explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 index NULL PRIMARY 4 NULL 4 Using index
+2 DEPENDENT SUBSELECT t2 index NULL a 5 NULL 3 Using where; Using index
+drop table t1,t2;
+create table t1 (a float);
+select 10.5 IN (SELECT * from t1 LIMIT 1);
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
-select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5);
+select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
-drop table t;
+drop table t1;
create table t1 (a int, b int, c varchar(10));
create table t2 (a int);
insert into t1 values (1,2,'a'),(2,3,'b'),(3,4,'c');
@@ -899,20 +914,20 @@
UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
Invalid use of group function
drop table t1;
-CREATE TABLE t (a int(1));
-EXPLAIN SELECT (SELECT RAND() FROM t) FROM t;
+CREATE TABLE t1 (a int(1));
+EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t system NULL NULL NULL NULL 0 const row not found
-2 UNCACHEABLE SUBSELECT t system NULL NULL NULL NULL 0 const row not found
-EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t) FROM t;
+1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
+2 UNCACHEABLE SUBSELECT t1 system NULL NULL NULL NULL 0 const row not found
+EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t system NULL NULL NULL NULL 0 const row not found
-2 UNCACHEABLE SUBSELECT t system NULL NULL NULL NULL 0 const row not found
-EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t) FROM t;
+1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
+2 UNCACHEABLE SUBSELECT t1 system NULL NULL NULL NULL 0 const row not found
+EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY t system NULL NULL NULL NULL 0 const row not found
-2 UNCACHEABLE SUBSELECT t system NULL NULL NULL NULL 0 const row not found
-drop table t;
+1 PRIMARY t1 system NULL NULL NULL NULL 0 const row not found
+2 UNCACHEABLE SUBSELECT t1 system NULL NULL NULL NULL 0 const row not found
+drop table t1;
CREATE TABLE `t1` (
`mot` varchar(30) character set latin1 NOT NULL default '',
`topic` mediumint(8) unsigned NOT NULL default '0',
--- 1.55/mysql-test/t/subselect.test Fri Feb 7 15:47:07 2003
+++ 1.56/mysql-test/t/subselect.test Mon Feb 10 17:59:13 2003
@@ -378,79 +378,90 @@
-- error 1096
SELECT * FROM (SELECT 1) b WHERE 1 IN (SELECT *);
-CREATE TABLE t (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
-INSERT INTO t VALUES (1),(2);
-SELECT * FROM t WHERE id IN (SELECT 1);
-EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1);
-SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
-SELECT * FROM t WHERE id IN (SELECT 1+(select 1));
-EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1+(select 1));
-EXPLAIN SELECT * FROM t WHERE id IN (SELECT 1 UNION SELECT 3);
-SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 3);
-SELECT * FROM t WHERE id IN (SELECT 5 UNION SELECT 2);
+CREATE TABLE t2 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
+INSERT INTO t2 VALUES (1),(2);
+SELECT * FROM t2 WHERE id IN (SELECT 1);
+EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1);
+SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
+SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
+EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
+EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1 UNION SELECT 3);
+SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
+SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
-- error 1093
-INSERT INTO t VALUES ((SELECT * FROM t));
-SELECT * FROM t;
+INSERT INTO t2 VALUES ((SELECT * FROM t2));
+SELECT * FROM t2;
CREATE TABLE t1 (id int(11) default NULL, KEY id (id)) TYPE=MyISAM CHARSET=latin1;
INSERT INTO t1 values (1),(1);
-- error 1240
-UPDATE t SET id=(SELECT * FROM t1);
-drop table t, t1;
-
+UPDATE t2 SET id=(SELECT * FROM t1);
+drop table t2, t1;
#NULL test
-create table t (a int);
-insert into t values (1),(2),(3);
-select 1 IN (SELECT * from t);
-select 10 IN (SELECT * from t);
-select NULL IN (SELECT * from t);
-update t set a=NULL where a=2;
-select 1 IN (SELECT * from t);
-select 3 IN (SELECT * from t);
-select 10 IN (SELECT * from t);
-select 1 > ALL (SELECT * from t);
-select 10 > ALL (SELECT * from t);
-select 1 > ANY (SELECT * from t);
-select 10 > ANY (SELECT * from t);
-drop table t;
-create table t (a varchar(20));
-insert into t values ('A'),('BC'),('DEF');
-select 'A' IN (SELECT * from t);
-select 'XYZS' IN (SELECT * from t);
-select NULL IN (SELECT * from t);
-update t set a=NULL where a='BC';
-select 'A' IN (SELECT * from t);
-select 'DEF' IN (SELECT * from t);
-select 'XYZS' IN (SELECT * from t);
-select 'A' > ALL (SELECT * from t);
-select 'XYZS' > ALL (SELECT * from t);
-select 'A' > ANY (SELECT * from t);
-select 'XYZS' > ANY (SELECT * from t);
-drop table t;
-create table t (a float);
-insert into t values (1.5),(2.5),(3.5);
-select 1.5 IN (SELECT * from t);
-select 10.5 IN (SELECT * from t);
-select NULL IN (SELECT * from t);
-update t set a=NULL where a=2.5;
-select 1.5 IN (SELECT * from t);
-select 3.5 IN (SELECT * from t);
-select 10.5 IN (SELECT * from t);
-select 1.5 > ALL (SELECT * from t);
-select 10.5 > ALL (SELECT * from t);
-select 1.5 > ANY (SELECT * from t);
-select 10.5 > ANY (SELECT * from t);
-explain select (select a+1) from t;
-select (select a+1) from t;
-drop table t;
+create table t1 (a int);
+insert into t1 values (1),(2),(3);
+select 1 IN (SELECT * from t1);
+select 10 IN (SELECT * from t1);
+select NULL IN (SELECT * from t1);
+update t1 set a=NULL where a=2;
+select 1 IN (SELECT * from t1);
+select 3 IN (SELECT * from t1);
+select 10 IN (SELECT * from t1);
+select 1 > ALL (SELECT * from t1);
+select 10 > ALL (SELECT * from t1);
+select 1 > ANY (SELECT * from t1);
+select 10 > ANY (SELECT * from t1);
+drop table t1;
+create table t1 (a varchar(20));
+insert into t1 values ('A'),('BC'),('DEF');
+select 'A' IN (SELECT * from t1);
+select 'XYZS' IN (SELECT * from t1);
+select NULL IN (SELECT * from t1);
+update t1 set a=NULL where a='BC';
+select 'A' IN (SELECT * from t1);
+select 'DEF' IN (SELECT * from t1);
+select 'XYZS' IN (SELECT * from t1);
+select 'A' > ALL (SELECT * from t1);
+select 'XYZS' > ALL (SELECT * from t1);
+select 'A' > ANY (SELECT * from t1);
+select 'XYZS' > ANY (SELECT * from t1);
+drop table t1;
+create table t1 (a float);
+insert into t1 values (1.5),(2.5),(3.5);
+select 1.5 IN (SELECT * from t1);
+select 10.5 IN (SELECT * from t1);
+select NULL IN (SELECT * from t1);
+update t1 set a=NULL where a=2.5;
+select 1.5 IN (SELECT * from t1);
+select 3.5 IN (SELECT * from t1);
+select 10.5 IN (SELECT * from t1);
+select 1.5 > ALL (SELECT * from t1);
+select 10.5 > ALL (SELECT * from t1);
+select 1.5 > ANY (SELECT * from t1);
+select 10.5 > ANY (SELECT * from t1);
+explain select (select a+1) from t1;
+select (select a+1) from t1;
+drop table t1;
+
+#
+# Null with keys
+#
+
+CREATE TABLE t1 (a int(11) NOT NULL default '0', PRIMARY KEY (a));
+CREATE TABLE t2 (a int(11) default '0', INDEX (a));
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t2 VALUES (1),(2),(3);
+SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
+explain SELECT t1.a, t1.a in (select t2.a from t2) FROM t1;
+drop table t1,t2;
#LIMIT is not supported now
-create table t (a float);
+create table t1 (a float);
-- error 1235
-select 10.5 IN (SELECT * from t LIMIT 1);
+select 10.5 IN (SELECT * from t1 LIMIT 1);
-- error 1235
-select 10.5 IN (SELECT * from t LIMIT 1 UNION SELECT 1.5);
-drop table t;
+select 10.5 IN (SELECT * from t1 LIMIT 1 UNION SELECT 1.5);
+drop table t1;
create table t1 (a int, b int, c varchar(10));
create table t2 (a int);
@@ -517,11 +528,11 @@
drop table t1;
#test of uncacheable subqueries
-CREATE TABLE t (a int(1));
-EXPLAIN SELECT (SELECT RAND() FROM t) FROM t;
-EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t) FROM t;
-EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t) FROM t;
-drop table t;
+CREATE TABLE t1 (a int(1));
+EXPLAIN SELECT (SELECT RAND() FROM t1) FROM t1;
+EXPLAIN SELECT (SELECT ENCRYPT('test') FROM t1) FROM t1;
+EXPLAIN SELECT (SELECT BENCHMARK(1,1) FROM t1) FROM t1;
+drop table t1;
CREATE TABLE `t1` (
--- 1.13/sql/item_row.cc Thu Jan 30 22:15:42 2003
+++ 1.14/sql/item_row.cc Mon Feb 10 17:59:13 2003
@@ -26,7 +26,7 @@
*/
Item_row::Item_row(List<Item> &arg):
- Item(), used_tables_cache(0), array_holder(1), const_item_cache(1)
+ Item(), used_tables_cache(0), array_holder(1), const_item_cache(1), with_null(0)
{
//TODO: think placing 2-3 component items in item (as it done for function)
--- 1.55/sql/item_subselect.cc Sun Feb 2 23:29:59 2003
+++ 1.56/sql/item_subselect.cc Mon Feb 10 17:59:13 2003
@@ -489,8 +489,8 @@
sl->order_list.elements + sl->group_list.elements);
item= (*func)(expr, new Item_ref_null_helper(this,
sl->ref_pointer_array,
- (char *)"<no matter>",
- (char*)"<result>"));
+ (char *)"<ref>",
+ this->full_name()));
sl->having= and_items(sl->having, item);
}
else
--- 1.45/libmysqld/lib_sql.cc Tue Feb 4 21:52:10 2003
+++ 1.46/libmysqld/lib_sql.cc Mon Feb 10 17:59:13 2003
@@ -63,8 +63,7 @@
}
/* Clear result variables */
- thd->net.last_error[0]=0;
- thd->net.last_errno=0;
+ thd->clear_error();
mysql->affected_rows= ~(my_ulonglong) 0;
thd->store_globals(); // Fix if more than one connect
| Thread |
|---|
| • bk commit into 4.1 tree (1.1447) | monty | 10 Feb |
