summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/group_by.result17
-rw-r--r--mysql-test/r/insert_select.result26
-rw-r--r--mysql-test/r/sp.result19
-rw-r--r--mysql-test/t/disabled.def1
-rw-r--r--mysql-test/t/group_by.test22
-rw-r--r--mysql-test/t/insert_select.test28
-rw-r--r--mysql-test/t/sp.test21
-rw-r--r--sql/item.cc4
-rw-r--r--sql/item.h11
-rw-r--r--sql/sp_head.cc2
-rw-r--r--sql/sql_select.cc4
-rw-r--r--sql/sql_select.h9
-rw-r--r--sql/sql_yacc.yy3
13 files changed, 156 insertions, 11 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index ebe59331357..faa1bc1d661 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -824,6 +824,23 @@ a
2
1
DROP TABLE t1;
+CREATE TABLE t1 (
+f1 int(10) unsigned NOT NULL auto_increment primary key,
+f2 varchar(100) NOT NULL default ''
+);
+CREATE TABLE t2 (
+f1 varchar(10) NOT NULL default '',
+f2 char(3) NOT NULL default '',
+PRIMARY KEY (`f1`),
+KEY `k1` (`f2`,`f1`)
+);
+INSERT INTO t1 values(NULL, '');
+INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
+SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+avg(t2.f1)
+SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+avg(t2.f1)
+DROP TABLE t1, t2;
create table t1 (c1 char(3), c2 char(3));
create table t2 (c3 char(3), c4 char(3));
insert into t1 values ('aaa', 'bb1'), ('aaa', 'bb2');
diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result
index bea8706a5be..2604e4bf648 100644
--- a/mysql-test/r/insert_select.result
+++ b/mysql-test/r/insert_select.result
@@ -699,6 +699,32 @@ Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 1
DROP TABLE t1;
+CREATE TABLE t1 (
+f1 int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
+f2 varchar(100) NOT NULL default ''
+);
+CREATE TABLE t2 (
+f1 varchar(10) NOT NULL default '',
+f2 char(3) NOT NULL default '',
+PRIMARY KEY (`f1`),
+KEY `k1` (`f2`, `f1`)
+);
+INSERT INTO t1 values(NULL, '');
+INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+min(t2.f1)
+INSERT INTO t1 (f2)
+SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+SELECT * FROM t1;
+f1 f2
+1
+DROP TABLE t1, t2;
CREATE TABLE t1 (x int, y int);
CREATE TABLE t2 (z int, y int);
CREATE TABLE t3 (a int, b int);
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index a17c3e4492e..ebdab4a3f89 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -6378,6 +6378,17 @@ Level Code Message
use test;
drop procedure sp_bug29050;
drop table t1;
+SET NAMES latin1;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE бвд INT;
+SELECT бвд;
+END|
+CALL p1();
+бвд
+NULL
+SET NAMES default;
+DROP PROCEDURE p1;
drop procedure if exists proc_25411_a;
drop procedure if exists proc_25411_b;
drop procedure if exists proc_25411_c;
@@ -6420,7 +6431,7 @@ select 1;
select 3;
select 4;
-end utf8 utf8_general_ci latin1_swedish_ci
+end latin1 latin1_swedish_ci latin1_swedish_ci
call proc_25411_a();
1
1
@@ -6440,7 +6451,7 @@ proc_25411_b CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_25411_b`(
)
begin
select p1, p2;
-end utf8 utf8_general_ci latin1_swedish_ci
+end latin1 latin1_swedish_ci latin1_swedish_ci
select name, param_list, body from mysql.proc where name like "%25411%";
name param_list body
proc_25411_a begin
@@ -6478,7 +6489,7 @@ select 1 ,2 ,3;
select 1,2 ,3 ;
select 1 ,2 ,3 ;
select 1 ,2 ,3 ;
-end utf8 utf8_general_ci latin1_swedish_ci
+end latin1 latin1_swedish_ci latin1_swedish_ci
call proc_25411_c();
1 2 3
1 2 3
@@ -6499,7 +6510,7 @@ select 1 /* testing */;
show create procedure proc_26302;
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
proc_26302 CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_26302`()
-select 1 /* testing */ utf8 utf8_general_ci latin1_swedish_ci
+select 1 /* testing */ latin1 latin1_swedish_ci latin1_swedish_ci
select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES
where ROUTINE_NAME = "proc_26302";
ROUTINE_NAME ROUTINE_DEFINITION
diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def
index fff651e54c9..368a659d939 100644
--- a/mysql-test/t/disabled.def
+++ b/mysql-test/t/disabled.def
@@ -25,4 +25,3 @@ ctype_big5 : BUG#26711 2007-06-21 Lars Test has never worked on Do
mysql_upgrade : Bug#28560 test links to /usr/local/mysql/lib libraries, causes non-determinism and failures on ABI breakage
federated_transactions : Bug#29523 Transactions do not work
-ddl_i18n_koi8r : Bug #30120 SP with local variables with non-ASCII names crashes server
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 3db8972bc6b..5db110e8d36 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -633,6 +633,28 @@ SELECT a FROM t1 ORDER BY "a" DESC;
SELECT a FROM t1 ORDER BY `a` DESC;
DROP TABLE t1;
+#
+# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself
+# returns empty
+#
+CREATE TABLE t1 (
+ f1 int(10) unsigned NOT NULL auto_increment primary key,
+ f2 varchar(100) NOT NULL default ''
+);
+CREATE TABLE t2 (
+ f1 varchar(10) NOT NULL default '',
+ f2 char(3) NOT NULL default '',
+ PRIMARY KEY (`f1`),
+ KEY `k1` (`f2`,`f1`)
+);
+
+INSERT INTO t1 values(NULL, '');
+INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
+SELECT SQL_BUFFER_RESULT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+SELECT avg(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+DROP TABLE t1, t2;
+
+
# End of 4.1 tests
#
diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test
index 10bc58303ca..ace51ef61c7 100644
--- a/mysql-test/t/insert_select.test
+++ b/mysql-test/t/insert_select.test
@@ -239,6 +239,34 @@ show status like 'Handler_read%';
DROP TABLE t1;
+#
+# Bug #29717 INSERT INTO SELECT inserts values even if SELECT statement itself returns empty
+#
+
+CREATE TABLE t1 (
+ f1 int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
+ f2 varchar(100) NOT NULL default ''
+);
+CREATE TABLE t2 (
+ f1 varchar(10) NOT NULL default '',
+ f2 char(3) NOT NULL default '',
+ PRIMARY KEY (`f1`),
+ KEY `k1` (`f2`, `f1`)
+);
+
+INSERT INTO t1 values(NULL, '');
+INSERT INTO `t2` VALUES ('486878','WDT'),('486910','WDT');
+SELECT COUNT(*) FROM t1;
+
+SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+
+INSERT INTO t1 (f2)
+ SELECT min(t2.f1) FROM t1, t2 where t2.f2 = 'SIR' GROUP BY t1.f1;
+
+SELECT COUNT(*) FROM t1;
+SELECT * FROM t1;
+DROP TABLE t1, t2;
+
# End of 4.1 tests
#
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index f834a57c5b1..46ef6bc6ddd 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -7365,6 +7365,27 @@ drop procedure sp_bug29050;
drop table t1;
#
+# Bug #30120 SP with local variables with non-ASCII names crashes server.
+#
+
+SET NAMES latin1;
+
+DELIMITER |;
+
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE бвд INT;
+ SELECT бвд;
+END|
+
+DELIMITER ;|
+
+CALL p1();
+
+SET NAMES default;
+DROP PROCEDURE p1;
+
+#
# Bug#25411 (trigger code truncated)
#
diff --git a/sql/item.cc b/sql/item.cc
index d6785461a7d..593915ef172 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1082,9 +1082,9 @@ bool Item_sp_variable::is_null()
Item_splocal::Item_splocal(const LEX_STRING &sp_var_name,
uint sp_var_idx,
enum_field_types sp_var_type,
- uint pos_in_q)
+ uint pos_in_q, uint len_in_q)
:Item_sp_variable(sp_var_name.str, sp_var_name.length),
- m_var_idx(sp_var_idx), pos_in_query(pos_in_q)
+ m_var_idx(sp_var_idx), pos_in_query(pos_in_q), len_in_query(len_in_q)
{
maybe_null= TRUE;
diff --git a/sql/item.h b/sql/item.h
index 5f4e0117d02..ab39505bf5f 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1054,9 +1054,18 @@ public:
SP variable in query text.
*/
uint pos_in_query;
+ /*
+ Byte length of SP variable name in the statement (see pos_in_query).
+ The value of this field may differ from the name_length value because
+ name_length contains byte length of UTF8-encoded item name, but
+ the query string (see sp_instr_stmt::m_query) is currently stored with
+ a charset from the SET NAMES statement.
+ */
+ uint len_in_query;
Item_splocal(const LEX_STRING &sp_var_name, uint sp_var_idx,
- enum_field_types sp_var_type, uint pos_in_q= 0);
+ enum_field_types sp_var_type,
+ uint pos_in_q= 0, uint len_in_q= 0);
bool is_splocal() { return 1; } /* Needed for error checking */
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index a5ad5043985..7b2073b8de3 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -927,7 +927,7 @@ subst_spvars(THD *thd, sp_instr *instr, LEX_STRING *query_str)
/* append the text between sp ref occurences */
res|= qbuf.append(cur + prev_pos, (*splocal)->pos_in_query - prev_pos);
- prev_pos= (*splocal)->pos_in_query + (*splocal)->m_name.length;
+ prev_pos= (*splocal)->pos_in_query + (*splocal)->len_in_query;
/* append the spvar substitute */
res|= qbuf.append(STRING_WITH_LEN(" NAME_CONST('"));
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 6a381c1f367..192709cea55 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1134,6 +1134,7 @@ JOIN::optimize()
order=0; // The output has only one row
simple_order=1;
select_distinct= 0; // No need in distinct for 1 row
+ group_optimized_away= 1;
}
calc_group_buffer(this, group_list);
@@ -11742,7 +11743,8 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),
if (!join->first_record || end_of_records ||
(idx=test_if_group_changed(join->group_fields)) >= 0)
{
- if (join->first_record || (end_of_records && !join->group))
+ if (join->first_record ||
+ (end_of_records && !join->group && !join->group_optimized_away))
{
if (join->procedure)
join->procedure->end_group();
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 98f2a7829dd..64173a91162 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -321,6 +321,14 @@ public:
ROLLUP rollup; // Used with rollup
bool select_distinct; // Set if SELECT DISTINCT
+ /*
+ If we have the GROUP BY statement in the query,
+ but the group_list was emptied by optimizer, this
+ flag is TRUE.
+ It happens when fields in the GROUP BY are from
+ constant table
+ */
+ bool group_optimized_away;
/*
simple_xxxxx is set if ORDER/GROUP BY doesn't include any references
@@ -429,6 +437,7 @@ public:
zero_result_cause= 0;
optimized= 0;
cond_equal= 0;
+ group_optimized_away= 0;
all_fields= fields_arg;
fields_list= fields_arg;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 468c3305c57..855c8dede5f 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -9505,7 +9505,8 @@ simple_ident:
Item_splocal *splocal;
splocal= new Item_splocal($1, spv->offset, spv->type,
lip->get_tok_start_prev() -
- lex->sphead->m_tmp_query);
+ lex->sphead->m_tmp_query,
+ lip->get_tok_end() - lip->get_tok_start_prev());
#ifndef DBUG_OFF
if (splocal)
splocal->m_sp= lex->sphead;
View Lorry Controller Status