summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/sp-group.result156
-rw-r--r--mysql-test/t/sp-group.test187
-rw-r--r--sql/item.cc7
-rw-r--r--sql/item.h2
-rw-r--r--sql/sql_lex.cc2
-rw-r--r--sql/sql_lex.h2
-rw-r--r--sql/sql_select.cc2
-rw-r--r--sql/sql_select.h4
-rw-r--r--sql/sql_union.cc3
-rw-r--r--sql/table.cc2
10 files changed, 357 insertions, 10 deletions
diff --git a/mysql-test/r/sp-group.result b/mysql-test/r/sp-group.result
new file mode 100644
index 00000000000..9744bbee6b6
--- /dev/null
+++ b/mysql-test/r/sp-group.result
@@ -0,0 +1,156 @@
+drop table if exists t1;
+Warnings:
+Note 1051 Unknown table 't1'
+drop view if exists view_t1;
+Warnings:
+Note 1051 Unknown table 'test.view_t1'
+SET sql_mode=ONLY_FULL_GROUP_BY;
+CREATE TABLE t1 (
+pk INT,
+f0 INT, f1 INT, f2 INT, f3 INT, f4 INT,
+f5 INT, f6 INT, f7 INT, f8 INT, f9 INT,
+PRIMARY KEY (pk)
+);
+CREATE VIEW view_t1 AS SELECT * FROM t1;
+CREATE PROCEDURE s1()
+SELECT * FROM (
+INFORMATION_SCHEMA.`INNODB_BUFFER_PAGE_LRU` AS table1
+LEFT JOIN test.view_t1 AS table2
+ON ( table2.`f6` = table1.FREE_PAGE_CLOCK)
+)
+ORDER BY table1.NUMBER_RECORDS
+LIMIT 0
+;
+CALL s1;
+POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE COMPRESSED IO_FIX IS_OLD FREE_PAGE_CLOCK pk f0 f1 f2 f3 f4 f5 f6 f7 f8 f9
+CALL s1;
+POOL_ID LRU_POSITION SPACE PAGE_NUMBER PAGE_TYPE FLUSH_TYPE FIX_COUNT IS_HASHED NEWEST_MODIFICATION OLDEST_MODIFICATION ACCESS_TIME TABLE_NAME INDEX_NAME NUMBER_RECORDS DATA_SIZE COMPRESSED_SIZE COMPRESSED IO_FIX IS_OLD FREE_PAGE_CLOCK pk f0 f1 f2 f3 f4 f5 f6 f7 f8 f9
+drop table t1;
+drop view view_t1;
+drop procedure s1;
+CREATE TABLE A (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+CREATE VIEW view_A AS SELECT * FROM A;
+CREATE TABLE C (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_date_key DATE,
+col_date_nokey DATE,
+col_time_key TIME,
+col_time_nokey TIME,
+col_datetime_key DATETIME,
+col_datetime_nokey DATETIME,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+CREATE VIEW view_C AS SELECT * FROM C;
+CREATE TABLE AA (
+pk INTEGER AUTO_INCREMENT,
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_date_key DATE,
+col_date_nokey DATE,
+col_time_key TIME,
+col_time_nokey TIME,
+col_datetime_key DATETIME,
+col_datetime_nokey DATETIME,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key, col_int_key)
+) ENGINE=MyISAM;
+CREATE VIEW view_AA AS SELECT * FROM AA;
+CREATE TABLE BB (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER,
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key, col_int_key)
+) ENGINE=MyISAM;
+CREATE VIEW view_BB AS SELECT * FROM BB;
+CREATE TABLE DD (
+pk INTEGER AUTO_INCREMENT,
+col_int_key INTEGER,
+col_date_key DATE,
+col_time_key TIME,
+col_datetime_key DATETIME,
+col_varchar_key VARCHAR(1),
+PRIMARY KEY (pk),
+KEY (col_varchar_key, col_int_key)
+) ENGINE=MyISAM;
+CREATE VIEW view_DD AS SELECT * FROM DD;
+CREATE TRIGGER k BEFORE INSERT ON `DD` FOR EACH ROW INSERT INTO `view_BB` SELECT * FROM `view_A` LIMIT 0 ;
+CREATE TRIGGER r BEFORE INSERT ON `A` FOR EACH ROW INSERT INTO `view_AA` SELECT * FROM `view_C` LIMIT 0 ;
+ALTER TABLE `DD` DROP PRIMARY KEY;
+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
+INSERT INTO `view_A` ( `pk` ) VALUES (NULL);
+INSERT INTO `DD` ( `pk` ) VALUES (NULL);
+INSERT INTO `A` ( `pk` ) VALUES (NULL);
+INSERT INTO `view_DD` ( `pk` ) VALUES (NULL);
+drop trigger r;
+drop trigger k;
+drop view view_A,view_AA,view_C,view_BB,view_DD;
+drop table A,C,AA,BB,DD;
+CREATE TABLE A (
+i INT,
+i1 INT,
+i2 INT,
+d1 DATE,
+d2 DATE,
+col_time_nokey1 TIME,
+col_time_nokey2 TIME,
+col_datetime_nokey1 DATETIME,
+col_datetime_nokey2 DATETIME,
+col_varchar_nokey1 VARCHAR(1),
+col_varchar_nokey2 VARCHAR(1)
+) ENGINE=MyISAM;
+CREATE VIEW view_A AS SELECT * FROM A;
+CREATE TABLE B (
+col_varchar_nokey VARCHAR(1)
+) ENGINE=MyISAM;
+CREATE TABLE AA (
+i INT,
+i1 INT,
+i2 INT,
+d1 DATE,
+d2 DATE,
+col_time_nokey1 TIME,
+col_time_nokey2 TIME,
+col_datetime_nokey1 DATETIME,
+col_datetime_nokey2 DATETIME,
+col_varchar_nokey1 VARCHAR(1),
+col_varchar_nokey2 VARCHAR(1)
+) ENGINE=MyISAM;
+CREATE VIEW view_AA AS SELECT * FROM AA;
+CREATE TABLE DD (
+i INT,
+i1 INT,
+i2 INT,
+d1 DATE,
+d2 DATE,
+col_time_nokey1 TIME,
+col_time_nokey2 TIME,
+col_datetime_nokey1 DATETIME,
+col_datetime_nokey2 DATETIME,
+col_varchar_nokey1 VARCHAR(1),
+col_varchar_nokey2 VARCHAR(1)
+) ENGINE=MyISAM;
+CREATE VIEW view_DD AS SELECT * FROM DD;
+CREATE TRIGGER tr1 BEFORE INSERT ON `AA` FOR EACH ROW INSERT INTO `view_A` SELECT * FROM `view_AA` LIMIT 0 ;
+CREATE TRIGGER tr2 BEFORE INSERT ON `B` FOR EACH ROW INSERT INTO `D` SELECT * FROM `A` LIMIT 0 ;
+INSERT INTO `view_AA` ( `i` ) VALUES (1);
+INSERT INTO `AA` ( `i` ) VALUES (2);
+DELETE FROM `B`;
+INSERT INTO `view_DD` ( `i` ) VALUES (1);
+INSERT INTO `view_AA` ( `i` ) VALUES (3);
+drop trigger tr1;
+drop trigger tr2;
+drop view view_A, view_AA,view_DD;
+drop table A,B,AA,DD;
diff --git a/mysql-test/t/sp-group.test b/mysql-test/t/sp-group.test
new file mode 100644
index 00000000000..2083ac97595
--- /dev/null
+++ b/mysql-test/t/sp-group.test
@@ -0,0 +1,187 @@
+--source include/have_innodb.inc
+
+drop table if exists t1;
+drop view if exists view_t1;
+
+#
+# Test case for MDEV 7601, MDEV-7594 and MDEV-7555
+# Server crashes in functions related to stored procedures
+# Server crashes in different ways while executing concurrent
+# flow involving views and non-empty sql_mode with ONLY_FULL_GROUP_BY
+#
+
+SET sql_mode=ONLY_FULL_GROUP_BY;
+
+CREATE TABLE t1 (
+ pk INT,
+ f0 INT, f1 INT, f2 INT, f3 INT, f4 INT,
+ f5 INT, f6 INT, f7 INT, f8 INT, f9 INT,
+ PRIMARY KEY (pk)
+);
+
+CREATE VIEW view_t1 AS SELECT * FROM t1;
+CREATE PROCEDURE s1()
+ SELECT * FROM (
+ INFORMATION_SCHEMA.`INNODB_BUFFER_PAGE_LRU` AS table1
+ LEFT JOIN test.view_t1 AS table2
+ ON ( table2.`f6` = table1.FREE_PAGE_CLOCK)
+ )
+ ORDER BY table1.NUMBER_RECORDS
+ LIMIT 0
+;
+CALL s1;
+CALL s1;
+
+drop table t1;
+drop view view_t1;
+drop procedure s1;
+
+#
+# MDEV-7590
+# Server crashes in st_select_lex_unit::cleanup on executing a trigger
+#
+
+CREATE TABLE A (
+ pk INTEGER AUTO_INCREMENT,
+ col_int_key INTEGER,
+ col_varchar_key VARCHAR(1),
+ PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+CREATE VIEW view_A AS SELECT * FROM A;
+CREATE TABLE C (
+ pk INTEGER AUTO_INCREMENT,
+ col_int_nokey INTEGER,
+ col_int_key INTEGER,
+ col_date_key DATE,
+ col_date_nokey DATE,
+ col_time_key TIME,
+ col_time_nokey TIME,
+ col_datetime_key DATETIME,
+ col_datetime_nokey DATETIME,
+ col_varchar_key VARCHAR(1),
+ col_varchar_nokey VARCHAR(1),
+ PRIMARY KEY (pk)
+) ENGINE=MyISAM;
+CREATE VIEW view_C AS SELECT * FROM C;
+CREATE TABLE AA (
+ pk INTEGER AUTO_INCREMENT,
+ col_int_nokey INTEGER,
+ col_int_key INTEGER,
+ col_date_key DATE,
+ col_date_nokey DATE,
+ col_time_key TIME,
+ col_time_nokey TIME,
+ col_datetime_key DATETIME,
+ col_datetime_nokey DATETIME,
+ col_varchar_key VARCHAR(1),
+ col_varchar_nokey VARCHAR(1),
+ PRIMARY KEY (pk),
+ KEY (col_varchar_key, col_int_key)
+) ENGINE=MyISAM;
+CREATE VIEW view_AA AS SELECT * FROM AA;
+CREATE TABLE BB (
+ pk INTEGER AUTO_INCREMENT,
+ col_int_key INTEGER,
+ col_varchar_key VARCHAR(1),
+ col_varchar_nokey VARCHAR(1),
+ PRIMARY KEY (pk),
+ KEY (col_varchar_key, col_int_key)
+) ENGINE=MyISAM;
+CREATE VIEW view_BB AS SELECT * FROM BB;
+CREATE TABLE DD (
+ pk INTEGER AUTO_INCREMENT,
+ col_int_key INTEGER,
+ col_date_key DATE,
+ col_time_key TIME,
+ col_datetime_key DATETIME,
+ col_varchar_key VARCHAR(1),
+ PRIMARY KEY (pk),
+ KEY (col_varchar_key, col_int_key)
+) ENGINE=MyISAM;
+CREATE VIEW view_DD AS SELECT * FROM DD;
+CREATE TRIGGER k BEFORE INSERT ON `DD` FOR EACH ROW INSERT INTO `view_BB` SELECT * FROM `view_A` LIMIT 0 ;
+CREATE TRIGGER r BEFORE INSERT ON `A` FOR EACH ROW INSERT INTO `view_AA` SELECT * FROM `view_C` LIMIT 0 ;
+--error ER_WRONG_AUTO_KEY
+ALTER TABLE `DD` DROP PRIMARY KEY;
+INSERT INTO `view_A` ( `pk` ) VALUES (NULL);
+--error 0,ER_WRONG_VALUE_COUNT_ON_ROW
+INSERT INTO `DD` ( `pk` ) VALUES (NULL);
+INSERT INTO `A` ( `pk` ) VALUES (NULL);
+--error 0,ER_WRONG_VALUE_COUNT_ON_ROW
+INSERT INTO `view_DD` ( `pk` ) VALUES (NULL);
+
+drop trigger r;
+drop trigger k;
+drop view view_A,view_AA,view_C,view_BB,view_DD;
+drop table A,C,AA,BB,DD;
+
+#
+# MDEV-7581
+# Server crashes in st_select_lex_unit::cleanup after a sequence of statements
+#
+
+CREATE TABLE A (
+ i INT,
+ i1 INT,
+ i2 INT,
+ d1 DATE,
+ d2 DATE,
+ col_time_nokey1 TIME,
+ col_time_nokey2 TIME,
+ col_datetime_nokey1 DATETIME,
+ col_datetime_nokey2 DATETIME,
+ col_varchar_nokey1 VARCHAR(1),
+ col_varchar_nokey2 VARCHAR(1)
+) ENGINE=MyISAM;
+
+CREATE VIEW view_A AS SELECT * FROM A;
+
+CREATE TABLE B (
+ col_varchar_nokey VARCHAR(1)
+) ENGINE=MyISAM;
+
+CREATE TABLE AA (
+ i INT,
+ i1 INT,
+ i2 INT,
+ d1 DATE,
+ d2 DATE,
+ col_time_nokey1 TIME,
+ col_time_nokey2 TIME,
+ col_datetime_nokey1 DATETIME,
+ col_datetime_nokey2 DATETIME,
+ col_varchar_nokey1 VARCHAR(1),
+ col_varchar_nokey2 VARCHAR(1)
+) ENGINE=MyISAM;
+
+CREATE VIEW view_AA AS SELECT * FROM AA;
+
+CREATE TABLE DD (
+ i INT,
+ i1 INT,
+ i2 INT,
+ d1 DATE,
+ d2 DATE,
+ col_time_nokey1 TIME,
+ col_time_nokey2 TIME,
+ col_datetime_nokey1 DATETIME,
+ col_datetime_nokey2 DATETIME,
+ col_varchar_nokey1 VARCHAR(1),
+ col_varchar_nokey2 VARCHAR(1)
+) ENGINE=MyISAM;
+
+CREATE VIEW view_DD AS SELECT * FROM DD;
+
+CREATE TRIGGER tr1 BEFORE INSERT ON `AA` FOR EACH ROW INSERT INTO `view_A` SELECT * FROM `view_AA` LIMIT 0 ;
+CREATE TRIGGER tr2 BEFORE INSERT ON `B` FOR EACH ROW INSERT INTO `D` SELECT * FROM `A` LIMIT 0 ;
+
+INSERT INTO `view_AA` ( `i` ) VALUES (1);
+INSERT INTO `AA` ( `i` ) VALUES (2);
+DELETE FROM `B`;
+INSERT INTO `view_DD` ( `i` ) VALUES (1);
+INSERT INTO `view_AA` ( `i` ) VALUES (3);
+
+drop trigger tr1;
+drop trigger tr2;
+drop view view_A, view_AA,view_DD;
+drop table A,B,AA,DD;
diff --git a/sql/item.cc b/sql/item.cc
index dbcf1b4cbe1..2f49ae46596 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4883,7 +4883,7 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
non aggregated fields of the outer select.
*/
marker= select->cur_pos_in_select_list;
- select->non_agg_fields.push_back(this);
+ select->join->non_agg_fields.push_back(this);
}
if (*from_field != view_ref_found)
{
@@ -5299,9 +5299,10 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
fixed= 1;
if (thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
!outer_fixed && !thd->lex->in_sum_func &&
- thd->lex->current_select->cur_pos_in_select_list != UNDEF_POS)
+ thd->lex->current_select->cur_pos_in_select_list != UNDEF_POS &&
+ thd->lex->current_select->join)
{
- thd->lex->current_select->non_agg_fields.push_back(this);
+ thd->lex->current_select->join->non_agg_fields.push_back(this);
marker= thd->lex->current_select->cur_pos_in_select_list;
}
mark_non_agg_field:
diff --git a/sql/item.h b/sql/item.h
index 4aaa67d9a1d..a38a25dfc75 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -631,7 +631,7 @@ public:
*/
uint name_length; /* Length of name */
uint decimals;
- int8 marker;
+ int marker;
bool maybe_null; /* If item may be null */
bool in_rollup; /* If used in GROUP BY list
of a query with ROLLUP */
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 1d62acdbe4b..9ccafa75ca7 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1917,7 +1917,6 @@ void st_select_lex::init_select()
with_sum_func= 0;
is_correlated= 0;
cur_pos_in_select_list= UNDEF_POS;
- non_agg_fields.empty();
cond_value= having_value= Item::COND_UNDEF;
inner_refs_list.empty();
insert_tables= 0;
@@ -1925,6 +1924,7 @@ void st_select_lex::init_select()
m_non_agg_field_used= false;
m_agg_func_used= false;
name_visibility_map= 0;
+ join= 0;
}
/*
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index a0f8e456800..aa59d76245b 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -877,8 +877,6 @@ public:
bool no_wrap_view_item;
/* exclude this select from check of unique_table() */
bool exclude_from_table_unique_test;
- /* List of fields that aren't under an aggregate function */
- List<Item_field> non_agg_fields;
/* index in the select list of the expression currently being fixed */
int cur_pos_in_select_list;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index fb7cafc595f..5ed7a67da61 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -20705,7 +20705,7 @@ setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
Item_field *field;
int cur_pos_in_select_list= 0;
List_iterator<Item> li(fields);
- List_iterator<Item_field> naf_it(thd->lex->current_select->non_agg_fields);
+ List_iterator<Item_field> naf_it(thd->lex->current_select->join->non_agg_fields);
field= naf_it++;
while (field && (item=li++))
diff --git a/sql/sql_select.h b/sql/sql_select.h
index de5baeee151..4f807ff5b93 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -922,6 +922,9 @@ public:
Item *pre_sort_idx_pushed_cond;
void clean_pre_sort_join_tab();
+ /* List of fields that aren't under an aggregate function */
+ List<Item_field> non_agg_fields;
+
/*
For "Using temporary+Using filesort" queries, JOIN::join_tab can point to
either:
@@ -1301,6 +1304,7 @@ public:
all_fields= fields_arg;
if (&fields_list != &fields_arg) /* Avoid valgrind-warning */
fields_list= fields_arg;
+ non_agg_fields.empty();
bzero((char*) &keyuse,sizeof(keyuse));
tmp_table_param.init();
tmp_table_param.end_write_records= HA_POS_ERROR;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 6e2e6e06ff7..87d3e86b2c7 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1021,7 +1021,6 @@ bool st_select_lex::cleanup()
{
error= (bool) ((uint) error | (uint) lex_unit->cleanup());
}
- non_agg_fields.empty();
inner_refs_list.empty();
exclude_from_table_unique_test= FALSE;
DBUG_RETURN(error);
@@ -1032,6 +1031,7 @@ void st_select_lex::cleanup_all_joins(bool full)
{
SELECT_LEX_UNIT *unit;
SELECT_LEX *sl;
+ DBUG_ENTER("st_select_lex::cleanup_all_joins");
if (join)
join->cleanup(full);
@@ -1039,6 +1039,7 @@ void st_select_lex::cleanup_all_joins(bool full)
for (unit= first_inner_unit(); unit; unit= unit->next_unit())
for (sl= unit->first_select(); sl; sl= sl->next_select())
sl->cleanup_all_joins(full);
+ DBUG_VOID_RETURN;
}
diff --git a/sql/table.cc b/sql/table.cc
index e4dee77171d..f521056aaee 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -5194,7 +5194,7 @@ Item *Field_iterator_table::create_item(THD *thd)
if (item && thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
!thd->lex->in_sum_func && select->cur_pos_in_select_list != UNDEF_POS)
{
- select->non_agg_fields.push_back(item);
+ select->join->non_agg_fields.push_back(item);
item->marker= select->cur_pos_in_select_list;
select->set_non_agg_field_used(true);
}
View Lorry Controller Status