summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/cast.result6
-rw-r--r--mysql-test/r/join_outer.result25
-rw-r--r--mysql-test/r/outfile.resultbin1382 -> 2135 bytes
-rw-r--r--mysql-test/r/subselect.result8
-rw-r--r--mysql-test/t/cast.test6
-rw-r--r--mysql-test/t/join_outer.test20
-rw-r--r--mysql-test/t/outfile.test35
-rw-r--r--mysql-test/t/subselect.test8
-rw-r--r--sql/item_func.cc6
-rw-r--r--sql/item_subselect.cc15
-rw-r--r--sql/sql_parse.cc3
-rw-r--r--sql/sql_select.cc41
-rw-r--r--sql/sql_select.h1
13 files changed, 160 insertions, 14 deletions
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 647c210b7d0..c538d8d55e1 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -351,6 +351,12 @@ DROP TABLE t1;
select isnull(date(NULL)), isnull(cast(NULL as DATE));
isnull(date(NULL)) isnull(cast(NULL as DATE))
1 1
+SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
+CAST(cast('01-01-01' as date) AS UNSIGNED)
+20010101
+SELECT CAST(cast('01-01-01' as date) AS SIGNED);
+CAST(cast('01-01-01' as date) AS SIGNED)
+20010101
End of 4.1 tests
select cast('1.2' as decimal(3,2));
cast('1.2' as decimal(3,2))
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index e667d11195d..62dfb36bb52 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1214,3 +1214,28 @@ SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
f1 f2 f3
bla blah sheep
DROP TABLE t1,t2;
+CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
+CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
+INSERT INTO t1 VALUES
+(1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
+INSERT INTO t2 VALUES
+(3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
+EXPLAIN
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5
+1 SIMPLE t2 ref idx idx 4 test.t1.id 2 Using where; Not exists
+flush status;
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+id a
+1 aaaaaaa
+4 ddddddd
+show status like 'Handler_read%';
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 5
+Handler_read_next 0
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 6
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/outfile.result b/mysql-test/r/outfile.result
index 023c4ea205f..8503df545d2 100644
--- a/mysql-test/r/outfile.result
+++ b/mysql-test/r/outfile.result
Binary files differ
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 0143b0c7fd3..6f01217c1a0 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4034,6 +4034,14 @@ SUM( (SELECT AVG( (SELECT COUNT(*) FROM t1 t HAVING XXA < 12) ) FROM t2) )
FROM t1;
ERROR HY000: Invalid use of group function
DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int, KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1);
+EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b);
+id select_type table type possible_keys key key_len ref rows Extra
+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;
+End of 5.0 tests.
End of 5.0 tests.
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (2,22),(1,11),(2,22);
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index bc50d484a5f..7217d00a1bb 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -182,6 +182,12 @@ DROP TABLE t1;
select isnull(date(NULL)), isnull(cast(NULL as DATE));
+#
+# Bug#23656: Wrong result of CAST from DATE to int
+#
+SELECT CAST(cast('01-01-01' as date) AS UNSIGNED);
+SELECT CAST(cast('01-01-01' as date) AS SIGNED);
+
--echo End of 4.1 tests
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index 10856142701..0a29b4cb325 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -831,3 +831,23 @@ SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla';
SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla';
DROP TABLE t1,t2;
+
+#
+# Bug 28188: 'not exists' optimization for outer joins
+#
+
+CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8));
+CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id));
+INSERT INTO t1 VALUES
+ (1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc');
+INSERT INTO t2 VALUES
+ (3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40);
+
+EXPLAIN
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+
+flush status;
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+show status like 'Handler_read%';
+
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/outfile.test b/mysql-test/t/outfile.test
index 7c90fd32909..f285407efd4 100644
--- a/mysql-test/t/outfile.test
+++ b/mysql-test/t/outfile.test
@@ -96,3 +96,38 @@ create table t1(a int);
eval select * into outfile "$MYSQL_TEST_DIR/outfile-test1" from t1;
drop table t1;
+#
+# Bug#28181 Access denied to 'information_schema when
+# select into out file (regression)
+#
+create database mysqltest;
+create user user_1@localhost;
+grant all on mysqltest.* to user_1@localhost;
+connect (con28181_1,localhost,user_1,,mysqltest);
+
+--error 1044
+eval select schema_name
+into outfile "../tmp/outfile-test.4"
+fields terminated by ',' optionally enclosed by '"'
+ lines terminated by '\n'
+from information_schema.schemata
+where schema_name like 'mysqltest';
+
+connection default;
+grant file on *.* to user_1@localhost;
+
+connect (con28181_2,localhost,user_1,,mysqltest);
+eval select schema_name
+into outfile "../tmp/outfile-test.4"
+fields terminated by ',' optionally enclosed by '"'
+ lines terminated by '\n'
+from information_schema.schemata
+where schema_name like 'mysqltest';
+
+connection default;
+--exec rm $MYSQLTEST_VARDIR/tmp/outfile-test.4
+use test;
+revoke all privileges on *.* from user_1@localhost;
+drop user user_1@localhost;
+drop database mysqltest;
+
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 0648c3ebda0..aaeb998d0c7 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -2874,6 +2874,14 @@ FROM t1;
DROP TABLE t1,t2;
+#
+# Bug #27807: Server crash when executing subquery with EXPLAIN
+#
+CREATE TABLE t1 (a int, b int, KEY (a));
+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;
+
--echo End of 5.0 tests.
#
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 830b033a067..313d96c4c81 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -945,7 +945,8 @@ longlong Item_func_signed::val_int()
longlong value;
int error;
- if (args[0]->cast_to_int_type() != STRING_RESULT)
+ if (args[0]->cast_to_int_type() != STRING_RESULT ||
+ args[0]->result_as_longlong())
{
value= args[0]->val_int();
null_value= args[0]->null_value;
@@ -984,7 +985,8 @@ longlong Item_func_unsigned::val_int()
my_decimal2int(E_DEC_FATAL_ERROR, dec, 1, &value);
return value;
}
- else if (args[0]->cast_to_int_type() != STRING_RESULT)
+ else if (args[0]->cast_to_int_type() != STRING_RESULT ||
+ args[0]->result_as_longlong())
{
value= args[0]->val_int();
null_value= args[0]->null_value;
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 812d3c222c0..7d635ba444e 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1817,6 +1817,21 @@ int subselect_single_select_engine::exec()
thd->lex->current_select= save_select;
DBUG_RETURN(join->error ? join->error : 1);
}
+ if (!select_lex->uncacheable && thd->lex->describe &&
+ !(join->select_options & SELECT_DESCRIBE) &&
+ join->need_tmp && item->const_item())
+ {
+ /*
+ Force join->join_tmp creation, because this subquery will be replaced
+ by a simple select from the materialization temp table by optimize()
+ called by EXPLAIN and we need to preserve the initial query structure
+ so we can display it.
+ */
+ select_lex->uncacheable|= UNCACHEABLE_EXPLAIN;
+ select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
+ if (join->init_save_join_tab())
+ DBUG_RETURN(1);
+ }
if (item->engine_changed)
{
DBUG_RETURN(1);
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index af80ea91397..ae9a7bdde3d 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4570,7 +4570,8 @@ check_access(THD *thd, ulong want_access, const char *db, ulong *save_priv,
if (schema_db)
{
- if (want_access & ~(SELECT_ACL | EXTRA_ACL))
+ if (!(sctx->master_access & FILE_ACL) && (want_access & FILE_ACL) ||
+ (want_access & ~(SELECT_ACL | EXTRA_ACL | FILE_ACL)))
{
if (!no_errors)
{
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index dff810e57b0..7d7de250dc9 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1436,14 +1436,13 @@ JOIN::optimize()
}
}
- if (select_lex->uncacheable && !is_top_level_join())
- {
- /* If this join belongs to an uncacheable subquery */
- if (!(tmp_join= (JOIN*)thd->alloc(sizeof(JOIN))))
- DBUG_RETURN(-1);
- error= 0; // Ensure that tmp_join.error= 0
- restore_tmp();
- }
+ /*
+ If this join belongs to an uncacheable subquery save
+ the original join
+ */
+ if (select_lex->uncacheable && !is_top_level_join() &&
+ init_save_join_tab())
+ DBUG_RETURN(-1);
}
error= 0;
@@ -1505,6 +1504,27 @@ JOIN::reinit()
DBUG_RETURN(0);
}
+/**
+ @brief Save the original join layout
+
+ @details Saves the original join layout so it can be reused in
+ re-execution and for EXPLAIN.
+
+ @return Operation status
+ @retval 0 success.
+ @retval 1 error occurred.
+*/
+
+bool
+JOIN::init_save_join_tab()
+{
+ if (!(tmp_join= (JOIN*)thd->alloc(sizeof(JOIN))))
+ return 1;
+ error= 0; // Ensure that tmp_join.error= 0
+ restore_tmp();
+ return 0;
+}
+
bool
JOIN::save_join_tab()
@@ -10768,7 +10788,6 @@ static enum_nested_loop_state
evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
int error, my_bool *report_error)
{
- bool not_exists_optimize= join_tab->table->reginfo.not_exists_optimize;
bool not_used_in_distinct=join_tab->not_used_in_distinct;
ha_rows found_records=join->found_records;
COND *select_cond= join_tab->select_cond;
@@ -10805,6 +10824,8 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
first_unmatched->found= 1;
for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
{
+ if (tab->table->reginfo.not_exists_optimize)
+ return NESTED_LOOP_NO_MORE_ROWS;
/* Check all predicates that has just been activated. */
/*
Actually all predicates non-guarded by first_unmatched->found
@@ -10850,8 +10871,6 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
if (found)
{
enum enum_nested_loop_state rc;
- if (not_exists_optimize)
- return NESTED_LOOP_NO_MORE_ROWS;
/* A match from join_tab is found for the current partial join. */
rc= (*join_tab->next_select)(join, join_tab+1, 0);
if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)
diff --git a/sql/sql_select.h b/sql/sql_select.h
index ca37c7bd274..644224d1bed 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -473,6 +473,7 @@ public:
void cleanup(bool full);
void clear();
bool save_join_tab();
+ bool init_save_join_tab();
bool send_row_on_empty_set()
{
return (do_send_rows && tmp_table_param.sum_func_count != 0 &&
View Lorry Controller Status