summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--libmysqld/lib_sql.cc3
-rw-r--r--mysql-test/mysql-test-run.sh2
-rw-r--r--mysql-test/r/subselect.result233
-rw-r--r--mysql-test/t/subselect.test147
-rw-r--r--sql/item.cc2
-rw-r--r--sql/item.h9
-rw-r--r--sql/item_func.cc4
-rw-r--r--sql/item_row.cc2
-rw-r--r--sql/item_strfunc.h2
-rw-r--r--sql/item_subselect.cc4
-rw-r--r--sql/log_event.cc2
-rw-r--r--sql/sql_base.cc2
-rw-r--r--sql/sql_class.cc2
-rw-r--r--sql/sql_class.h1
-rw-r--r--sql/sql_lex.cc4
-rw-r--r--sql/sql_lex.h3
-rw-r--r--sql/sql_parse.cc1
-rw-r--r--sql/sql_select.cc20
-rw-r--r--sql/sql_table.cc4
19 files changed, 240 insertions, 207 deletions
diff --git a/libmysqld/lib_sql.cc b/libmysqld/lib_sql.cc
index 714d9cc02f7..a1fbaaae631 100644
--- a/libmysqld/lib_sql.cc
+++ b/libmysqld/lib_sql.cc
@@ -63,8 +63,7 @@ my_bool simple_command(MYSQL *mysql,enum enum_server_command command,
}
/* 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
diff --git a/mysql-test/mysql-test-run.sh b/mysql-test/mysql-test-run.sh
index 854a4b10b2f..c451fa72d0d 100644
--- a/mysql-test/mysql-test-run.sh
+++ b/mysql-test/mysql-test-run.sh
@@ -623,7 +623,7 @@ report_stats () {
# 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
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 0cbf341e55c..0d8c96fea85 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -641,168 +641,183 @@ x y
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)
-1
-select 10 IN (SELECT * from t);
-10 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 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 @@ INSERT INTO t1 VALUES (1);
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',
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index bb872df3e5c..1841e9f109a 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -378,79 +378,90 @@ drop table t1, t2, t3;
-- 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 @@ UPDATE t1 SET i=i+1 WHERE i=(SELECT MAX(i));
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` (
diff --git a/sql/item.cc b/sql/item.cc
index d6f0d59e058..ee7af1ae0fd 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -286,7 +286,7 @@ table_map Item_field::used_tables() const
{
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)
diff --git a/sql/item.h b/sql/item.h
index 8c7914cefeb..531817c4844 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -102,6 +102,7 @@ public:
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 @@ class Item_ref :public Item_ident
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 @@ public:
{
(*ref)->save_in_field(result_field, no_conversions);
}
+ Item *real_item() { return *ref; }
};
class Item_in_subselect;
@@ -530,7 +533,7 @@ protected:
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();
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 6494573458c..788961a954d 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -120,10 +120,10 @@ Item_func::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
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;
}
diff --git a/sql/item_row.cc b/sql/item_row.cc
index 23cafe7ec31..cf745e21e45 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -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)
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 3a7810fa306..b2e54474494 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -262,7 +262,7 @@ public:
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 *);
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 21acb31a7d5..fb51b5561e9 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -489,8 +489,8 @@ void Item_in_subselect::single_value_transformer(THD *thd,
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
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 34e3deebf69..0631e21fbd1 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -766,7 +766,7 @@ int Query_log_event::write_data(IO_CACHE* file)
#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),
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index f4a32b482b3..97c92ff2b8f 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -792,7 +792,7 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
DBUG_RETURN(0);
}
table->query_id=thd->query_id;
- thd->lex.tmp_table_used= 1;
+ thd->tmp_table_used= 1;
goto reset;
}
}
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 4d78a03289b..23a8a6fa8f1 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -87,7 +87,7 @@ THD::THD():user_time(0), is_fatal_error(0),
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;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 51067d94990..2d492b19a2b 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -550,6 +550,7 @@ public:
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
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 6bb81de5fcf..29d56905788 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -167,7 +167,6 @@ LEX *lex_start(THD *thd, uchar *buf,uint length)
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 @@ void st_select_lex::init_select()
use_index.empty();
ftfunc_list_alloc.empty();
ftfunc_list= &ftfunc_list_alloc;
- if (linkage != UNION_TYPE)
- linkage= UNSPECIFIED_TYPE;
+ linkage= UNSPECIFIED_TYPE;
}
/*
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 3ce865e7b39..4ef8ffa840e 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -221,9 +221,11 @@ public:
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 @@ typedef struct st_lex
uint slave_thd_opt;
CHARSET_INFO *charset;
char *help_arg;
- bool tmp_table_used;
inline void uncacheable()
{
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index d9bfc901475..08e37a5576b 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3181,6 +3181,7 @@ mysql_init_query(THD *thd)
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);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 00f7ed9bfc9..4543c6bc5d0 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1902,9 +1902,10 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
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 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
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 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
}
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);
}
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 9738e1dd86d..66168459c7a 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -250,7 +250,7 @@ int mysql_rm_table_part2(THD *thd, TABLE_LIST *tables, bool if_exists,
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 @@ int mysql_create_table(THD *thd,const char *db, const char *table_name,
(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)
{
View Lorry Controller Status