summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/distinct.result37
-rw-r--r--mysql-test/r/gis-rtree.result11
-rw-r--r--mysql-test/r/ps.result15
-rw-r--r--mysql-test/r/subselect.result13
-rw-r--r--mysql-test/r/udf.result11
-rw-r--r--mysql-test/t/distinct.test21
-rw-r--r--mysql-test/t/gis-rtree.test17
-rw-r--r--mysql-test/t/ps.test18
-rw-r--r--mysql-test/t/subselect.test12
-rw-r--r--mysql-test/t/udf.test17
-rw-r--r--sql/item_subselect.cc19
-rw-r--r--sql/item_subselect.h3
-rw-r--r--sql/sql_lex.cc1
-rw-r--r--sql/sql_select.cc44
-rw-r--r--sql/sql_udf.cc6
-rw-r--r--storage/myisam/mi_check.c8
16 files changed, 246 insertions, 7 deletions
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 72c040cb25f..e1a8a3151e9 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -607,3 +607,40 @@ id select_type table type possible_keys key key_len ref rows Extra
SELECT DISTINCT a,a FROM t1 WHERE b < 12 ORDER BY a;
a a
DROP TABLE t1;
+CREATE TABLE t1 (a CHAR(1));
+INSERT INTO t1 VALUES('A'), (0);
+SELECT a FROM t1 WHERE a=0;
+a
+A
+0
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'A'
+SELECT DISTINCT a FROM t1 WHERE a=0;
+a
+A
+0
+Warnings:
+Warning 1292 Truncated incorrect DOUBLE value: 'A'
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');
+EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');
+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 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where
+EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
+WHERE ADDDATE(a,1) = '2002-08-03');
+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 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary
+CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
+INSERT INTO t2 VALUES (0xf6);
+INSERT INTO t2 VALUES ('oe');
+SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt;
+COUNT(*)
+2
+SELECT COUNT(*) FROM
+(SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;
+COUNT(*)
+2
+DROP TABLE t1, t2;
diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result
index 95211ad9133..138360edc49 100644
--- a/mysql-test/r/gis-rtree.result
+++ b/mysql-test/r/gis-rtree.result
@@ -881,3 +881,14 @@ ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
INSERT INTO t1(foo) VALUES ('');
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
DROP TABLE t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT, b POINT NOT NULL, KEY (a), SPATIAL KEY (b));
+INSERT INTO t1 (b) VALUES (GeomFromText('POINT(1 2)'));
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+DROP TABLE t1;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 4988b1139e9..df0269defd9 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -1522,6 +1522,21 @@ Variable_name Value
Slow_queries 1
deallocate prepare no_index;
deallocate prepare sq;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (NULL);
+SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
+a
+1
+2
+PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';
+EXECUTE stmt;
+a
+1
+2
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1,t2;
End of 5.0 tests.
create procedure proc_1() reset query cache;
call proc_1();
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index ee057695cd1..377870696e1 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -3033,6 +3033,19 @@ t3 CREATE TABLE `t3` (
`a` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
+a
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+a
+1
+2
+EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
+DROP TABLE t1;
create table t1 (df decimal(5,1));
insert into t1 values(1.1);
insert into t1 values(2.2);
diff --git a/mysql-test/r/udf.result b/mysql-test/r/udf.result
index abc654c4b74..3e2cceddbe5 100644
--- a/mysql-test/r/udf.result
+++ b/mysql-test/r/udf.result
@@ -232,6 +232,17 @@ DROP FUNCTION sequence;
DROP FUNCTION lookup;
DROP FUNCTION reverse_lookup;
DROP FUNCTION avgcost;
+select * from mysql.func;
+name ret dl type
+CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
+select IS_const(3);
+IS_const(3)
+const
+drop function IS_const;
+select * from mysql.func;
+name ret dl type
+select is_const(3);
+ERROR 42000: FUNCTION test.is_const does not exist
CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
select
is_const(3) as const,
diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
index b2cc42cc0ff..8734b940241 100644
--- a/mysql-test/t/distinct.test
+++ b/mysql-test/t/distinct.test
@@ -504,3 +504,24 @@ DROP TABLE t1;
#DROP TABLE t1;
#DROP TABLE t2;
+#
+# Bug #15881: cast problems
+#
+CREATE TABLE t1 (a CHAR(1)); INSERT INTO t1 VALUES('A'), (0);
+SELECT a FROM t1 WHERE a=0;
+SELECT DISTINCT a FROM t1 WHERE a=0;
+DROP TABLE t1;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');
+EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');
+EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1
+ WHERE ADDDATE(a,1) = '2002-08-03');
+CREATE TABLE t2 (a CHAR(5) CHARACTER SET latin1 COLLATE latin1_general_ci);
+INSERT INTO t2 VALUES (0xf6);
+INSERT INTO t2 VALUES ('oe');
+
+SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t2) dt;
+SELECT COUNT(*) FROM
+ (SELECT DISTINCT a FROM t2 WHERE a='oe' COLLATE latin1_german2_ci) dt;
+
+DROP TABLE t1, t2;
diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test
index e34dd14dbfc..1704fe7dc80 100644
--- a/mysql-test/t/gis-rtree.test
+++ b/mysql-test/t/gis-rtree.test
@@ -254,3 +254,20 @@ INSERT INTO t1() VALUES ();
--error 1416
INSERT INTO t1(foo) VALUES ('');
DROP TABLE t1;
+
+#
+# Bug #23578: Corruption prevents Optimize table from working properly with a
+# spatial index
+#
+
+CREATE TABLE t1 (a INT AUTO_INCREMENT, b POINT NOT NULL, KEY (a), SPATIAL KEY (b));
+
+INSERT INTO t1 (b) VALUES (GeomFromText('POINT(1 2)'));
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+INSERT INTO t1 (b) SELECT b FROM t1;
+
+OPTIMIZE TABLE t1;
+DROP TABLE t1;
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index f38af9644c9..f9d8e11ee96 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -1576,6 +1576,24 @@ execute sq;
deallocate prepare no_index;
deallocate prepare sq;
+#
+# Bug 25027: query with a single-row non-correlated subquery
+# and IS NULL predicate
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (b int);
+INSERT INTO t2 VALUES (NULL);
+
+SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL;
+PREPARE stmt FROM 'SELECT a FROM t1 WHERE (SELECT b FROM t2) IS NULL';
+
+EXECUTE stmt;
+
+DEALLOCATE PREPARE stmt;
+DROP TABLE t1,t2;
+
--echo End of 5.0 tests.
#
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 2f65ee44d3c..eefa2528a17 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -1988,6 +1988,18 @@ SHOW CREATE TABLE t3;
DROP TABLE t1,t2,t3;
+#
+# Bug 24670: subquery witout tables but with a WHERE clause
+#
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (1), (2);
+
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
+SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+EXPLAIN SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
+
+DROP TABLE t1;
# End of 4.1 tests
#
diff --git a/mysql-test/t/udf.test b/mysql-test/t/udf.test
index b0580faea3d..010a532f48b 100644
--- a/mysql-test/t/udf.test
+++ b/mysql-test/t/udf.test
@@ -240,6 +240,23 @@ DROP FUNCTION reverse_lookup;
DROP FUNCTION avgcost;
#
+# Bug #15439: UDF name case handling forces DELETE FROM mysql.func to remove
+# the UDF
+#
+select * from mysql.func;
+--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
+eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_LIB";
+
+select IS_const(3);
+
+drop function IS_const;
+
+select * from mysql.func;
+
+--error 1305
+select is_const(3);
+
+#
# Bug#18761: constant expression as UDF parameters not passed in as constant
#
--replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 0074e33cdea..f3f840bdaa9 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -391,6 +391,7 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
*/
!(select_lex->item_list.head()->type() == FIELD_ITEM ||
select_lex->item_list.head()->type() == REF_ITEM) &&
+ !join->conds && !join->having &&
/*
switch off this optimization for prepare statement,
because we do not rollback this changes
@@ -415,8 +416,6 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
*/
substitution->walk(&Item::remove_dependence_processor, 0,
(byte *) select_lex->outer_select());
- /* SELECT without FROM clause can't have WHERE or HAVING clause */
- DBUG_ASSERT(join->conds == 0 && join->having == 0);
return RES_REDUCE;
}
return RES_OK;
@@ -2320,6 +2319,22 @@ bool subselect_single_select_engine::no_tables()
/*
+ Check statically whether the subquery can return NULL
+
+ SINOPSYS
+ subselect_single_select_engine::may_be_null()
+
+ RETURN
+ FALSE can guarantee that the subquery never return NULL
+ TRUE otherwise
+*/
+bool subselect_single_select_engine::may_be_null()
+{
+ return ((no_tables() && !join->conds && !join->having) ? maybe_null : 1);
+}
+
+
+/*
Report about presence of tables in subquery
SYNOPSIS
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index a5068ff20e0..5a0b2788678 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -363,7 +363,7 @@ public:
enum Item_result type() { return res_type; }
enum_field_types field_type() { return res_field_type; }
virtual void exclude()= 0;
- bool may_be_null() { return maybe_null; };
+ virtual bool may_be_null() { return maybe_null; };
virtual table_map upper_select_const_tables()= 0;
static table_map calc_const_tables(TABLE_LIST *);
virtual void print(String *str)= 0;
@@ -400,6 +400,7 @@ public:
void print (String *str);
bool change_result(Item_subselect *si, select_subselect *result);
bool no_tables();
+ bool may_be_null();
bool is_executed() const { return executed; }
bool no_rows();
};
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 18d30494701..ad098a55811 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1934,7 +1934,6 @@ void st_select_lex_unit::set_limit(SELECT_LEX *sl)
{
ha_rows select_limit_val;
- DBUG_ASSERT(! thd->stmt_arena->is_stmt_prepare());
select_limit_val= (ha_rows)(sl->select_limit ? sl->select_limit->val_uint() :
HA_POS_ERROR);
offset_limit_cnt= (ha_rows)(sl->offset_limit ? sl->offset_limit->val_uint() :
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5da48c69e9e..3c1c2fe4778 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -8593,6 +8593,46 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
return cond; // Point at next and level
}
+/*
+ Check if equality can be used in removing components of GROUP BY/DISTINCT
+
+ SYNOPSIS
+ test_if_equality_guarantees_uniqueness()
+ l the left comparison argument (a field if any)
+ r the right comparison argument (a const of any)
+
+ DESCRIPTION
+ Checks if an equality predicate can be used to take away
+ DISTINCT/GROUP BY because it is known to be true for exactly one
+ distinct value (e.g. <expr> == <const>).
+ Arguments must be of the same type because e.g.
+ <string_field> = <int_const> may match more than 1 distinct value from
+ the column.
+ We must take into consideration and the optimization done for various
+ string constants when compared to dates etc (see Item_int_with_ref) as
+ well as the collation of the arguments.
+
+ RETURN VALUE
+ TRUE can be used
+ FALSE cannot be used
+*/
+static bool
+test_if_equality_guarantees_uniqueness(Item *l, Item *r)
+{
+ return r->const_item() &&
+ /* elements must be of the same result type */
+ (r->result_type() == l->result_type() ||
+ /* or dates compared to longs */
+ (((l->type() == Item::FIELD_ITEM &&
+ ((Item_field *)l)->field->can_be_compared_as_longlong()) ||
+ (l->type() == Item::FUNC_ITEM &&
+ ((Item_func *)l)->result_as_longlong())) &&
+ r->result_type() == INT_RESULT))
+ /* and must have the same collation if compared as strings */
+ && (l->result_type() != STRING_RESULT ||
+ l->collation.collation == r->collation.collation);
+}
+
/*
Return 1 if the item is a const value in all the WHERE clause
*/
@@ -8629,7 +8669,7 @@ const_expression_in_where(COND *cond, Item *comp_item, Item **const_item)
Item *right_item= ((Item_func*) cond)->arguments()[1];
if (left_item->eq(comp_item,1))
{
- if (right_item->const_item())
+ if (test_if_equality_guarantees_uniqueness (left_item, right_item))
{
if (*const_item)
return right_item->eq(*const_item, 1);
@@ -8639,7 +8679,7 @@ const_expression_in_where(COND *cond, Item *comp_item, Item **const_item)
}
else if (right_item->eq(comp_item,1))
{
- if (left_item->const_item())
+ if (test_if_equality_guarantees_uniqueness (right_item, left_item))
{
if (*const_item)
return left_item->eq(*const_item, 1);
diff --git a/sql/sql_udf.cc b/sql/sql_udf.cc
index adda7316e3a..7dec58d9b6e 100644
--- a/sql/sql_udf.cc
+++ b/sql/sql_udf.cc
@@ -481,6 +481,8 @@ int mysql_drop_function(THD *thd,const LEX_STRING *udf_name)
TABLE *table;
TABLE_LIST tables;
udf_func *udf;
+ char *exact_name_str;
+ uint exact_name_len;
DBUG_ENTER("mysql_drop_function");
if (!initialized)
{
@@ -494,6 +496,8 @@ int mysql_drop_function(THD *thd,const LEX_STRING *udf_name)
my_error(ER_FUNCTION_NOT_DEFINED, MYF(0), udf_name->str);
goto err;
}
+ exact_name_str= udf->name.str;
+ exact_name_len= udf->name.length;
del_udf(udf);
/*
Close the handle if this was function that was found during boot or
@@ -508,7 +512,7 @@ int mysql_drop_function(THD *thd,const LEX_STRING *udf_name)
if (!(table = open_ltable(thd,&tables,TL_WRITE)))
goto err;
table->use_all_columns();
- table->field[0]->store(udf_name->str, udf_name->length, system_charset_info);
+ table->field[0]->store(exact_name_str, exact_name_len, &my_charset_bin);
if (!table->file->index_read_idx(table->record[0], 0,
(byte*) table->field[0]->ptr,
table->key_info[0].key_length,
diff --git a/storage/myisam/mi_check.c b/storage/myisam/mi_check.c
index c6b0ad6d13f..161bd9bf980 100644
--- a/storage/myisam/mi_check.c
+++ b/storage/myisam/mi_check.c
@@ -1813,6 +1813,12 @@ int mi_sort_index(MI_CHECK *param, register MI_INFO *info, my_string name)
MI_STATE_INFO old_state;
DBUG_ENTER("mi_sort_index");
+ /* cannot sort index files with R-tree indexes */
+ for (key= 0,keyinfo= &share->keyinfo[0]; key < share->base.keys ;
+ key++,keyinfo++)
+ if (keyinfo->key_alg == HA_KEY_ALG_RTREE)
+ return 0;
+
if (!(param->testflag & T_SILENT))
printf("- Sorting index for MyISAM-table '%s'\n",name);
@@ -1905,6 +1911,8 @@ static int sort_one_index(MI_CHECK *param, MI_INFO *info, MI_KEYDEF *keyinfo,
char llbuff[22];
DBUG_ENTER("sort_one_index");
+ /* cannot walk over R-tree indices */
+ DBUG_ASSERT(keyinfo->key_alg != HA_KEY_ALG_RTREE);
new_page_pos=param->new_file_pos;
param->new_file_pos+=keyinfo->block_length;
View Lorry Controller Status