summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/r/subselect.result20
-rw-r--r--mysql-test/r/union.result14
-rw-r--r--mysql-test/t/union.test12
-rw-r--r--sql/ha_isammrg.h1
-rw-r--r--sql/sql_cache.cc7
-rw-r--r--sql/sql_lex.cc6
-rw-r--r--sql/sql_lex.h2
-rw-r--r--sql/sql_parse.cc17
-rw-r--r--sql/sql_union.cc15
-rw-r--r--sql/sql_update.cc13
-rw-r--r--sql/sql_yacc.yy13
11 files changed, 72 insertions, 48 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 32770614221..514fcc41e6d 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -266,7 +266,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
2 SUBQUERY t2 ALL NULL NULL NULL NULL 3
Warnings:
-Note 1003 select high_priority test.t3.a AS `a` from test.t3 where (test.t3.a >= (select min(test.t2.b) from test.t2 limit 1))
+Note 1003 select high_priority test.t3.a AS `a` from test.t3 where (test.t3.a >= (select min(test.t2.b) from test.t2))
select * from t3 where a >= all (select b from t2);
a
7
@@ -327,7 +327,7 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1
Warnings:
Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1
-Note 1003 select high_priority test.t6.patient_uq AS `patient_uq`,test.t6.clinic_uq AS `clinic_uq` from test.t6 where exists(select test.t7.uq AS `uq`,test.t7.name AS `name` from test.t7 where (test.t7.uq = test.t6.clinic_uq) limit 1)
+Note 1003 select high_priority test.t6.patient_uq AS `patient_uq`,test.t6.clinic_uq AS `clinic_uq` from test.t6 where exists(select test.t7.uq AS `uq`,test.t7.name AS `name` from test.t7 where (test.t7.uq = test.t6.clinic_uq))
select * from t1 where a= (select a from t2,t4 where t2.b=t4.b);
ERROR 23000: Column: 'a' in field list is ambiguous
drop table if exists t1,t2,t3;
@@ -728,7 +728,7 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used
3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used
Warnings:
-Note 1003 select high_priority test.t2.id AS `id` from test.t2 where <in_optimizer>(test.t2.id,<exists>(select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(1)) limit 1 union select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(3)) limit 1))
+Note 1003 select high_priority test.t2.id AS `id` from test.t2 where <in_optimizer>(test.t2.id,<exists>(select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(1)) union select 1 AS `Not_used` having (<cache>(test.t2.id) = <null_helper>(3))))
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 3);
id
SELECT * FROM t2 WHERE id IN (SELECT 5 UNION SELECT 2);
@@ -891,7 +891,7 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY t2 ref_or_null a a 5 func 2 Using where; Using index
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where
Warnings:
-Note 1003 select high_priority test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(select 1 AS `Not_used` from test.t2 join test.t3 where ((test.t3.a = test.t2.a) and ((<cache>(test.t1.a) = test.t2.a) or isnull(test.t2.a))) having <is_not_null_test>(test.t2.a) limit 1)) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from test.t1
+Note 1003 select high_priority test.t1.a AS `a`,<in_optimizer>(test.t1.a,<exists>(select 1 AS `Not_used` from test.t2 join test.t3 where ((test.t3.a = test.t2.a) and ((<cache>(test.t1.a) = test.t2.a) or isnull(test.t2.a))) having <is_not_null_test>(test.t2.a))) AS `t1.a in (select t2.a from t2,t3 where t3.a=t2.a)` from test.t1
drop table t1,t2,t3;
create table t1 (a float);
select 10.5 IN (SELECT * from t1 LIMIT 1);
@@ -1162,7 +1162,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
-Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a limit 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a)) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
@@ -1172,7 +1172,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
Warnings:
-Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a limit 1)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select high_priority <in_optimizer>(0,<exists>(select 1 AS `Not_used` from test.t1 a)) AS `0 IN (SELECT 1 FROM t1 a)`
drop table t1;
CREATE TABLE `t1` (
`i` int(11) NOT NULL default '0',
@@ -1304,7 +1304,7 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 func 1 Using where
2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where; Using index
Warnings:
-Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)) limit 1))
+Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a))))
drop table t1, t2, t3;
create table t1 (a int, b int, index a (a,b));
create table t2 (a int, index a (a));
@@ -1343,7 +1343,7 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY t1 ref a a 5 func 1001 Using where; Using index
2 DEPENDENT SUBQUERY t3 index a a 5 NULL 3 Using where; Using index
Warnings:
-Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a)) limit 1))
+Note 1003 select high_priority test.t2.a AS `a` from test.t2 where <in_optimizer>(test.t2.a,<exists>(select 1 AS `Not_used` from test.t1 join test.t3 where ((test.t1.b = test.t3.a) and (<cache>(test.t2.a) = test.t1.a))))
insert into t1 values (3,31);
select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
a
@@ -1483,7 +1483,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t3 ALL NULL NULL NULL NULL 3 Using where
2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
Warnings:
-Note 1003 select high_priority test.t3.a AS `a` from test.t3 where <not>((test.t3.a < (select max(test.t2.b) from test.t2 limit 1)))
+Note 1003 select high_priority test.t3.a AS `a` from test.t3 where <not>((test.t3.a < (select max(test.t2.b) from test.t2)))
insert into t2 values (2,2), (2,1), (3,3), (3,1);
select * from t3 where a > all (select max(b) from t2 group by a);
a
@@ -1666,7 +1666,7 @@ id select_type table type possible_keys key key_len ref rows Extra
2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 7 Using where; Using index
Warnings:
Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1
-Note 1003 select high_priority test.tt.id AS `id`,test.tt.text AS `text` from test.t1 tt where not(exists(select test.t1.id AS `id` from test.t1 where ((test.t1.id < 8) and ((test.t1.id = test.tt.id) or isnull(test.t1.id))) having (test.t1.id is not null) limit 1))
+Note 1003 select high_priority test.tt.id AS `id`,test.tt.text AS `text` from test.t1 tt where not(exists(select test.t1.id AS `id` from test.t1 where ((test.t1.id < 8) and ((test.t1.id = test.tt.id) or isnull(test.t1.id))) having (test.t1.id is not null)))
insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001');
create table t2 (id int not null, text varchar(20) not null default '', primary key (id));
insert into t2 (id, text) values (1, 'text1'), (2, 'text2'), (3, 'text3'), (4, 'text4'), (5, 'text5'), (6, 'text6'), (7, 'text7'), (8, 'text8'), (9, 'text9'), (10, 'text10'), (11, 'text1'), (12, 'text2'), (13, 'text3'), (14, 'text4'), (15, 'text5'), (16, 'text6'), (17, 'text7'), (18, 'text8'), (19, 'text9'), (20, 'text10'),(21, 'text1'), (22, 'text2'), (23, 'text3'), (24, 'text4'), (25, 'text5'), (26, 'text6'), (27, 'text7'), (28, 'text8'), (29, 'text9'), (30, 'text10'), (31, 'text1'), (32, 'text2'), (33, 'text3'), (34, 'text4'), (35, 'text5'), (36, 'text6'), (37, 'text7'), (38, 'text8'), (39, 'text9'), (40, 'text10'), (41, 'text1'), (42, 'text2'), (43, 'text3'), (44, 'text4'), (45, 'text5'), (46, 'text6'), (47, 'text7'), (48, 'text8'), (49, 'text9'), (50, 'text10');
diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result
index 54bba1fd958..bf8b8df7c65 100644
--- a/mysql-test/r/union.result
+++ b/mysql-test/r/union.result
@@ -936,3 +936,17 @@ select 1 union select 2;
1
2
set sql_select_limit=default;
+create table t1 (a int);
+insert into t1 values (100), (1);
+create table t2 (a int);
+insert into t2 values (100);
+select a from t1 union select a from t2 order by a;
+a
+1
+100
+SET SQL_SELECT_LIMIT=1;
+select a from t1 union select a from t2 order by a;
+a
+1
+drop table t1, t2;
+set sql_select_limit=default;
diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test
index effb0139111..21619534f49 100644
--- a/mysql-test/t/union.test
+++ b/mysql-test/t/union.test
@@ -504,3 +504,15 @@ select 1 union select 2;
(select 1) union (select 2) union (select 3) limit 2;
set sql_select_limit=default;
+#
+# ORDER with LIMIT
+#
+create table t1 (a int);
+insert into t1 values (100), (1);
+create table t2 (a int);
+insert into t2 values (100);
+select a from t1 union select a from t2 order by a;
+SET SQL_SELECT_LIMIT=1;
+select a from t1 union select a from t2 order by a;
+drop table t1, t2;
+set sql_select_limit=default;
diff --git a/sql/ha_isammrg.h b/sql/ha_isammrg.h
index 05c1d862eb3..289277a9dac 100644
--- a/sql/ha_isammrg.h
+++ b/sql/ha_isammrg.h
@@ -68,4 +68,5 @@ class ha_isammrg: public handler
int create(const char *name, TABLE *form, HA_CREATE_INFO *create_info);
THR_LOCK_DATA **store_lock(THD *thd, THR_LOCK_DATA **to,
enum thr_lock_type lock_type);
+ uint8 table_cache_type() { return HA_CACHE_TBL_NOCACHE; }
};
diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index f17f4bb2b7f..359150cf716 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -2612,16 +2612,15 @@ TABLE_COUNTER_TYPE Query_cache::is_cacheable(THD *thd, uint32 query_len,
table_alias_charset used here because it depends of
lower_case_table_names variable
*/
- if (tables_used->table->db_type == DB_TYPE_MRG_ISAM ||
- tables_used->table->tmp_table != NO_TMP_TABLE ||
+ if (tables_used->table->tmp_table != NO_TMP_TABLE ||
(*tables_type & HA_CACHE_TBL_NOCACHE) ||
(tables_used->db_length == 5 &&
my_strnncoll(table_alias_charset, (uchar*)tables_used->db, 6,
(uchar*)"mysql",6) == 0))
{
DBUG_PRINT("qcache",
- ("select not cacheable: used MRG_ISAM, temporary, \
-system or other non-cacheable table(s)"));
+ ("select not cacheable: temporary, system or \
+other non-cacheable table(s)"));
DBUG_RETURN(0);
}
if (tables_used->table->db_type == DB_TYPE_MRG_MYISAM)
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 19a6941fe32..e545e35921a 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1023,6 +1023,7 @@ void st_select_lex::init_query()
ref_pointer_array= 0;
select_n_having_items= 0;
prep_where= 0;
+ explicit_limit= 0;
}
void st_select_lex::init_select()
@@ -1616,10 +1617,7 @@ void st_select_lex::print_limit(THD *thd, String *str)
if (!thd)
thd= current_thd;
- if ((select_limit != thd->variables.select_limit &&
- this == &thd->lex->select_lex) ||
- (select_limit != HA_POS_ERROR && this != &thd->lex->select_lex) ||
- offset_limit != 0L)
+ if (explicit_limit)
{
str->append(" limit ", 7);
char buff[20];
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index ea1accabf37..50f13a0391c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -418,6 +418,8 @@ public:
bool braces; /* SELECT ... UNION (SELECT ... ) <- this braces */
/* TRUE when having fix field called in processing of this SELECT */
bool having_fix_field;
+ /* explicit LIMIT clause was used */
+ bool explicit_limit;
/*
SELECT for SELECT command st_select_lex. Used to privent scaning
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index a69c048a918..f6ed19bc1fa 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1874,7 +1874,7 @@ mysql_execute_command(THD *thd)
if (&lex->select_lex != lex->all_selects_list &&
lex->unit.create_total_list(thd, lex, &tables))
DBUG_VOID_RETURN;
-
+
/*
When option readonly is set deny operations which change tables.
Except for the replication thread and the 'super' users.
@@ -1891,6 +1891,13 @@ mysql_execute_command(THD *thd)
switch (lex->sql_command) {
case SQLCOM_SELECT:
{
+ /* assign global limit variable if limit is not given */
+ {
+ SELECT_LEX *param= lex->unit.global_parameters;
+ if (!param->explicit_limit)
+ param->select_limit= thd->variables.select_limit;
+ }
+
select_result *result=lex->result;
if (tables)
{
@@ -3742,9 +3749,7 @@ mysql_init_select(LEX *lex)
{
SELECT_LEX *select_lex= lex->current_select;
select_lex->init_select();
- select_lex->select_limit= (&lex->select_lex == select_lex) ?
- lex->thd->variables.select_limit : /* Primry UNION */
- HA_POS_ERROR; /* subquery */
+ select_lex->select_limit= HA_POS_ERROR;
if (select_lex == &lex->select_lex)
{
lex->exchange= 0;
@@ -3796,9 +3801,7 @@ mysql_new_select(LEX *lex, bool move_down)
fake->select_number= INT_MAX;
fake->make_empty_select();
fake->linkage= GLOBAL_OPTIONS_TYPE;
- fake->select_limit= (&lex->unit == unit) ?
- lex->thd->variables.select_limit : /* Primry UNION */
- HA_POS_ERROR; /* subquery */
+ fake->select_limit= HA_POS_ERROR;
}
}
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 1b3995f30be..42b67a79683 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -377,21 +377,12 @@ int st_select_lex_unit::exec()
if (!thd->is_fatal_error) // Check if EOM
{
ulong options_tmp= thd->options;
- /*
- We have to take into the account a case when:
- SET SQL_SELECT_LIMIT was set.
- In mysql_new_select() function this value was copied to
- the fake_select_lex node of the top-level unit.
- Here below, we just take this value if global LIMIT was not applied
- to the entire UNION.
- */
- ha_rows select_limit= ((global_parameters->select_limit != HA_POS_ERROR) ?
- global_parameters->select_limit : fake_select_lex->select_limit);
thd->lex->current_select= fake_select_lex;
offset_limit_cnt= global_parameters->offset_limit;
- select_limit_cnt= select_limit + global_parameters->offset_limit;
+ select_limit_cnt= global_parameters->select_limit +
+ global_parameters->offset_limit;
- if (select_limit_cnt < select_limit)
+ if (select_limit_cnt < global_parameters->select_limit)
select_limit_cnt= HA_POS_ERROR; // no limit
if (select_limit_cnt == HA_POS_ERROR)
options_tmp&= ~OPTION_FOUND_ROWS;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index d985d2c2e78..d71744d8361 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -544,7 +544,7 @@ int mysql_multi_update(THD *thd,
}
}
- if (!(result=new multi_update(thd, table_list, fields, values,
+ if (!(result=new multi_update(thd, update_list, fields, values,
handle_duplicates)))
DBUG_RETURN(-1);
@@ -578,7 +578,7 @@ multi_update::multi_update(THD *thd_arg, TABLE_LIST *table_list,
int multi_update::prepare(List<Item> &not_used_values,
SELECT_LEX_UNIT *lex_unit)
{
- TABLE_LIST *table_ref, *tables;
+ TABLE_LIST *table_ref;
SQL_LIST update;
table_map tables_to_update= 0;
Item_field *item;
@@ -604,9 +604,8 @@ int multi_update::prepare(List<Item> &not_used_values,
We have to check values after setup_tables to get used_keys right in
reference tables
*/
- tables= thd->lex->select_lex.get_table_list();
- if (setup_fields(thd, 0, tables, *values, 1, 0, 0))
+ if (setup_fields(thd, 0, all_tables, *values, 1, 0, 0))
DBUG_RETURN(1);
/*
@@ -616,7 +615,7 @@ int multi_update::prepare(List<Item> &not_used_values,
*/
update.empty();
- for (table_ref= tables; table_ref; table_ref=table_ref->next)
+ for (table_ref= all_tables; table_ref; table_ref=table_ref->next)
{
TABLE *table=table_ref->table;
if (tables_to_update & table->map)
@@ -685,10 +684,10 @@ int multi_update::prepare(List<Item> &not_used_values,
which will cause an error when reading a row.
(This issue is mostly relevent for MyISAM tables)
*/
- for (table_ref= tables; table_ref; table_ref=table_ref->next)
+ for (table_ref= all_tables; table_ref; table_ref=table_ref->next)
{
TABLE *table=table_ref->table;
- if (!(tables_to_update & table->map) || !table->no_keyread &&
+ if (!(tables_to_update & table->map) &&
find_real_table_in_list(update_tables, table_ref->db,
table_ref->real_name))
table->no_cache= 1; // Disable row cache
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 568a526fd58..2155aa7e80f 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -3566,9 +3566,7 @@ opt_limit_clause_init:
LEX *lex= Lex;
SELECT_LEX *sel= lex->current_select;
sel->offset_limit= 0L;
- sel->select_limit= (&lex->select_lex == sel) ?
- Lex->thd->variables.select_limit : /* primary SELECT */
- HA_POS_ERROR; /* subquery */
+ sel->select_limit= HA_POS_ERROR;
}
| limit_clause {}
;
@@ -3588,18 +3586,21 @@ limit_options:
SELECT_LEX *sel= Select;
sel->select_limit= $1;
sel->offset_limit= 0L;
+ sel->explicit_limit= 1;
}
| ULONG_NUM ',' ULONG_NUM
{
SELECT_LEX *sel= Select;
sel->select_limit= $3;
sel->offset_limit= $1;
+ sel->explicit_limit= 1;
}
| ULONG_NUM OFFSET_SYM ULONG_NUM
{
SELECT_LEX *sel= Select;
sel->select_limit= $1;
sel->offset_limit= $3;
+ sel->explicit_limit= 1;
}
;
@@ -3611,7 +3612,11 @@ delete_limit_clause:
lex->current_select->select_limit= HA_POS_ERROR;
}
| LIMIT ulonglong_num
- { Select->select_limit= (ha_rows) $2; };
+ {
+ SELECT_LEX *sel= Select;
+ sel->select_limit= (ha_rows) $2;
+ sel->explicit_limit= 1;
+ };
ULONG_NUM:
NUM { $$= strtoul($1.str,NULL,10); }