summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/func_gconcat.result31
-rw-r--r--mysql-test/r/null_key.result60
-rw-r--r--mysql-test/t/func_gconcat.test39
-rw-r--r--mysql-test/t/null_key.test45
-rw-r--r--sql/sql_class.h3
-rw-r--r--sql/sql_select.cc12
6 files changed, 180 insertions, 10 deletions
diff --git a/mysql-test/r/func_gconcat.result b/mysql-test/r/func_gconcat.result
index 09b6de796c4..184bd07b066 100644
--- a/mysql-test/r/func_gconcat.result
+++ b/mysql-test/r/func_gconcat.result
@@ -524,3 +524,34 @@ group_concat(a)
ABW
ABW
drop table t1;
+CREATE TABLE t1 (
+aID smallint(5) unsigned NOT NULL auto_increment,
+sometitle varchar(255) NOT NULL default '',
+bID smallint(5) unsigned NOT NULL,
+PRIMARY KEY (aID),
+UNIQUE KEY sometitle (sometitle)
+);
+INSERT INTO t1 SET sometitle = 'title1', bID = 1;
+INSERT INTO t1 SET sometitle = 'title2', bID = 1;
+CREATE TABLE t2 (
+bID smallint(5) unsigned NOT NULL auto_increment,
+somename varchar(255) NOT NULL default '',
+PRIMARY KEY (bID),
+UNIQUE KEY somename (somename)
+);
+INSERT INTO t2 SET somename = 'test';
+SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
+FROM t1 JOIN t2 ON t1.bID = t2.bID;
+COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
+2 test
+INSERT INTO t2 SET somename = 'test2';
+SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
+FROM t1 JOIN t2 ON t1.bID = t2.bID;
+COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
+2 test
+DELETE FROM t2 WHERE somename = 'test2';
+SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
+FROM t1 JOIN t2 ON t1.bID = t2.bID;
+COUNT(*) GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
+2 test
+DROP TABLE t1,t2;
diff --git a/mysql-test/r/null_key.result b/mysql-test/r/null_key.result
index b9824cc4b81..a74a01de8fb 100644
--- a/mysql-test/r/null_key.result
+++ b/mysql-test/r/null_key.result
@@ -369,3 +369,63 @@ select * from t1;
id id2
1 1
drop table t1;
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int, b int, INDEX idx(a));
+CREATE TABLE t3 (b int, INDEX idx(b));
+CREATE TABLE t4 (b int, INDEX idx(b));
+INSERT INTO t1 VALUES (1), (2), (3), (4);
+INSERT INTO t2 VALUES (1, 1), (3, 1);
+INSERT INTO t3 VALUES
+(NULL), (NULL), (NULL), (NULL), (NULL),
+(NULL), (NULL), (NULL), (NULL), (NULL);
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t3 VALUES (2), (3);
+ANALYZE table t1, t2, t3;
+Table Op Msg_type Msg_text
+test.t1 analyze status OK
+test.t2 analyze status OK
+test.t3 analyze status OK
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+15972
+EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+LEFT JOIN t3 ON t2.b=t3.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 4
+1 SIMPLE t2 ref idx idx 5 test.t1.a 1
+1 SIMPLE t3 ref idx idx 5 test.t2.b 1 Using index
+FLUSH STATUS ;
+SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+LEFT JOIN t3 ON t2.b=t3.b;
+a a b b
+1 1 1 NULL
+2 NULL NULL NULL
+3 3 1 NULL
+4 NULL NULL NULL
+SHOW STATUS LIKE "handler_read%";
+Variable_name Value
+Handler_read_first 0
+Handler_read_key 6
+Handler_read_next 2
+Handler_read_prev 0
+Handler_read_rnd 0
+Handler_read_rnd_next 5
+SELECT FOUND_ROWS();
+FOUND_ROWS()
+4
+DROP TABLE t1,t2,t3,t4;
diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test
index 185a2fe1e78..9793d0d0a2c 100644
--- a/mysql-test/t/func_gconcat.test
+++ b/mysql-test/t/func_gconcat.test
@@ -310,12 +310,37 @@ select a, group_concat(b order by b) from t1 group by a with rollup;
select a, group_concat(distinct b order by b) from t1 group by a with rollup;
drop table t1;
-# End of 4.1 tests
-
#
-# Bug #6475
+# Bug #12095: GROUP_CONCAT for one row table
#
-create table t1 (a char(3), b char(20), primary key (a, b));
-insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English');
-select group_concat(a) from t1 group by b;
-drop table t1;
+
+CREATE TABLE t1 (
+ aID smallint(5) unsigned NOT NULL auto_increment,
+ sometitle varchar(255) NOT NULL default '',
+ bID smallint(5) unsigned NOT NULL,
+ PRIMARY KEY (aID),
+ UNIQUE KEY sometitle (sometitle)
+);
+INSERT INTO t1 SET sometitle = 'title1', bID = 1;
+INSERT INTO t1 SET sometitle = 'title2', bID = 1;
+
+CREATE TABLE t2 (
+ bID smallint(5) unsigned NOT NULL auto_increment,
+ somename varchar(255) NOT NULL default '',
+ PRIMARY KEY (bID),
+ UNIQUE KEY somename (somename)
+);
+INSERT INTO t2 SET somename = 'test';
+
+SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
+ FROM t1 JOIN t2 ON t1.bID = t2.bID;
+INSERT INTO t2 SET somename = 'test2';
+SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
+ FROM t1 JOIN t2 ON t1.bID = t2.bID;
+DELETE FROM t2 WHERE somename = 'test2';
+SELECT COUNT(*), GROUP_CONCAT(DISTINCT t2.somename SEPARATOR ' |')
+ FROM t1 JOIN t2 ON t1.bID = t2.bID;
+
+DROP TABLE t1,t2;
+
+# End of 4.1 tests
diff --git a/mysql-test/t/null_key.test b/mysql-test/t/null_key.test
index 318e1fa6b9b..ff8a4ff924c 100644
--- a/mysql-test/t/null_key.test
+++ b/mysql-test/t/null_key.test
@@ -194,4 +194,49 @@ delete from t1 where id <=> NULL;
select * from t1;
drop table t1;
+#
+# Test for bug #12144: optimizations for key access with null keys
+# used for outer joins
+#
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (a int, b int, INDEX idx(a));
+CREATE TABLE t3 (b int, INDEX idx(b));
+CREATE TABLE t4 (b int, INDEX idx(b));
+INSERT INTO t1 VALUES (1), (2), (3), (4);
+INSERT INTO t2 VALUES (1, 1), (3, 1);
+INSERT INTO t3 VALUES
+ (NULL), (NULL), (NULL), (NULL), (NULL),
+ (NULL), (NULL), (NULL), (NULL), (NULL);
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t4 SELECT * FROM t3;
+INSERT INTO t3 SELECT * FROM t4;
+INSERT INTO t3 VALUES (2), (3);
+
+ANALYZE table t1, t2, t3;
+
+SELECT COUNT(*) FROM t3;
+
+EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+ LEFT JOIN t3 ON t2.b=t3.b;
+FLUSH STATUS ;
+SELECT SQL_CALC_FOUND_ROWS * FROM t1 LEFT JOIN t2 ON t1.a=t2.a
+ LEFT JOIN t3 ON t2.b=t3.b;
+SHOW STATUS LIKE "handler_read%";
+SELECT FOUND_ROWS();
+
+DROP TABLE t1,t2,t3,t4;
# End of 4.1 tests
diff --git a/sql/sql_class.h b/sql/sql_class.h
index d6847f5fb35..90112bd650b 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1705,13 +1705,14 @@ public:
bool using_indirect_summary_function;
/* If >0 convert all blob fields to varchar(convert_blob_length) */
uint convert_blob_length;
+ bool need_const; /* <=> const items are saved in tmp table */
CHARSET_INFO *table_charset;
bool schema_table;
TMP_TABLE_PARAM()
:copy_field(0), group_parts(0),
group_length(0), group_null_parts(0), convert_blob_length(0),
- schema_table(0)
+ need_const(0), schema_table(0)
{}
~TMP_TABLE_PARAM()
{
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 8aced3c1da6..74ac9f21a81 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -2759,7 +2759,9 @@ add_key_field(KEY_FIELD **key_fields,uint and_level, Item_func *cond,
*/
(*key_fields)->null_rejecting= (cond->functype() == Item_func::EQ_FUNC) &&
((*value)->type() == Item::FIELD_ITEM) &&
- ((Item_field*)*value)->field->maybe_null();
+
+ (((Item_field*)*value)->field->maybe_null() ||
+ ((Item_field *)*value)->field->table->maybe_null);
(*key_fields)++;
}
@@ -8256,7 +8258,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
param->using_indirect_summary_function=1;
continue;
}
- if (item->const_item() && (int) hidden_field_count <= 0)
+ if (item->const_item() && (int) hidden_field_count <= 0 &&
+ !param->need_const)
continue; // We don't have to store this
}
if (type == Item::SUM_FUNC_ITEM && !group && !save_sum_fields)
@@ -9857,6 +9860,11 @@ join_read_always_key(JOIN_TAB *tab)
int error;
TABLE *table= tab->table;
+ for (uint i= 0 ; i < tab->ref.key_parts ; i++)
+ {
+ if ((tab->ref.null_rejecting & 1 << i) && tab->ref.items[i]->is_null())
+ return -1;
+ }
if (!table->file->inited)
table->file->ha_index_init(tab->ref.key);
if (cp_buffer_from_ref(tab->join->thd, &tab->ref))
View Lorry Controller Status