summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/explain.result3
-rw-r--r--mysql-test/r/join.result13
-rw-r--r--mysql-test/t/explain.test7
-rw-r--r--mysql-test/t/join.test9
-rw-r--r--sql/sql_select.cc100
5 files changed, 87 insertions, 45 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index 2892c2ea587..e85aca4c6f3 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -1,5 +1,8 @@
drop table if exists t1;
create table t1 (id int not null, str char(10), unique(str));
+explain select * from t1;
+table type possible_keys key key_len ref rows Extra
+t1 system NULL NULL NULL NULL 0 const row not found
insert into t1 values (1, null),(2, null),(3, "foo"),(4, "bar");
select * from t1 where str is null;
id str
diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 96113dcdc8b..ff608825b9c 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -14,8 +14,7 @@ insert into t1 values (101);
insert into t1 values (105);
insert into t1 values (106);
insert into t1 values (107);
-insert into t2 values (107);
-insert into t2 values (75);
+insert into t2 values (107),(75),(1000);
select t1.id, t2.id from t1, t2 where t2.id = t1.id;
id id
107 107
@@ -28,6 +27,16 @@ select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t2.id;
id count(t2.id)
75 1
107 1
+select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
+id id
+NULL 75
+explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
+table type possible_keys key key_len ref rows Extra
+t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition
+t2 ALL NULL NULL NULL NULL 3 Using where
+explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0;
+Comment
+Impossible WHERE noticed after reading const tables
drop table t1,t2;
CREATE TABLE t1 (
id int(11) NOT NULL auto_increment,
diff --git a/mysql-test/t/explain.test b/mysql-test/t/explain.test
index 3e33d58215e..1cf8c5e93e1 100644
--- a/mysql-test/t/explain.test
+++ b/mysql-test/t/explain.test
@@ -5,6 +5,7 @@
drop table if exists t1;
--enable_warnings
create table t1 (id int not null, str char(10), unique(str));
+explain select * from t1;
insert into t1 values (1, null),(2, null),(3, "foo"),(4, "bar");
select * from t1 where str is null;
select * from t1 where str="foo";
@@ -14,8 +15,10 @@ explain select * from t1 ignore key (str) where str="foo";
explain select * from t1 use key (str,str) where str="foo";
#The following should give errors
-!$1072 explain select * from t1 use key (str,str,foo) where str="foo";
-!$1072 explain select * from t1 ignore key (str,str,foo) where str="foo";
+--error 1072
+explain select * from t1 use key (str,str,foo) where str="foo";
+--error 1072
+explain select * from t1 ignore key (str,str,foo) where str="foo";
drop table t1;
explain select 1;
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 653b4271fe8..2a3b8488ca0 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -21,13 +21,18 @@ insert into t1 values (105);
insert into t1 values (106);
insert into t1 values (107);
-insert into t2 values (107);
-insert into t2 values (75);
+insert into t2 values (107),(75),(1000);
select t1.id, t2.id from t1, t2 where t2.id = t1.id;
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t1.id;
select t1.id, count(t2.id) from t1,t2 where t2.id = t1.id group by t2.id;
+#
+# Test problems with impossible ON or WHERE
+#
+select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
+explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0 where t2.id=75 and t1.id is null;
+explain select t1.id, t2.id from t1, t2 where t2.id = t1.id and t1.id <0 and t1.id > 0;
drop table t1,t2;
#
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5912f3021dc..8ee1fbca6a5 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1,4 +1,4 @@
-/* Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
+/* Copyright (C) 2000-2003 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
@@ -1256,7 +1256,6 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
table_map found_const_table_map,all_table_map;
TABLE **table_vector;
JOIN_TAB *stat,*stat_end,*s,**stat_ref;
- SQL_SELECT *select;
KEYUSE *keyuse,*start_keyuse;
table_map outer_join=0;
JOIN_TAB *stat_vector[MAX_TABLES+1];
@@ -1268,7 +1267,6 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
table_vector=(TABLE**) join->thd->alloc(sizeof(TABLE*)*(table_count*2));
if (!stat || !stat_ref || !table_vector)
DBUG_RETURN(1); // Eom /* purecov: inspected */
- select=0;
join->best_ref=stat_vector;
@@ -1452,7 +1450,7 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
{ // Found everything for ref.
int tmp;
ref_changed = 1;
- s->type=JT_CONST;
+ s->type= JT_CONST;
join->const_table_map|=table->map;
set_position(join,const_count++,s,start_keyuse);
if (create_ref_for_key(join, s, start_keyuse,
@@ -1503,23 +1501,44 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
if (s->const_keys)
{
ha_rows records;
- if (!select)
- select=make_select(s->table, found_const_table_map,
- found_const_table_map,
- and_conds(conds,s->on_expr),&error);
- records=get_quick_record_count(select,s->table, s->const_keys,
- join->row_limit);
+ SQL_SELECT *select;
+ select= make_select(s->table, found_const_table_map,
+ found_const_table_map,
+ s->on_expr ? s->on_expr : conds,
+ &error);
+ records= get_quick_record_count(select,s->table, s->const_keys,
+ join->row_limit);
s->quick=select->quick;
s->needed_reg=select->needed_reg;
select->quick=0;
+ if (records == 0 && s->table->reginfo.impossible_range)
+ {
+ /*
+ Impossible WHERE or ON expression
+ In case of ON, we mark that the we match one empty NULL row.
+ In case of WHERE, don't set found_const_table_map to get the
+ caller to abort with a zero row result.
+ */
+ join->const_table_map|= s->table->map;
+ set_position(join,const_count++,s,(KEYUSE*) 0);
+ s->type= JT_CONST;
+ if (s->on_expr)
+ {
+ /* Generate empty row */
+ s->info= "Impossible ON condition";
+ found_const_table_map|= s->table->map;
+ s->type= JT_CONST;
+ mark_as_null_row(s->table); // All fields are NULL
+ }
+ }
if (records != HA_POS_ERROR)
{
s->found_records=records;
s->read_time= (ha_rows) (s->quick ? s->quick->read_time : 0.0);
}
+ delete select;
}
}
- delete select;
/* Find best combination and return it */
join->join_tab=stat;
@@ -2615,7 +2634,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
keyparts != keyinfo->key_parts)
j->type=JT_REF; /* Must read with repeat */
else if (ref_key == j->ref.key_copy)
- { /* Should never be reached */
+ {
/*
This happen if we are using a constant expression in the ON part
of an LEFT JOIN.
@@ -7729,37 +7748,40 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
if (tab->info)
item_list.push_back(new Item_string(tab->info,strlen(tab->info),
default_charset_info));
- else if (tab->select)
+ else
{
- if (tab->use_quick == 2)
+ if (tab->select)
{
- sprintf(buff_ptr,"; Range checked for each record (index map: %u)",
- tab->keys);
- buff_ptr=strend(buff_ptr);
+ if (tab->use_quick == 2)
+ {
+ sprintf(buff_ptr,"; Range checked for each record (index map: %u)",
+ tab->keys);
+ buff_ptr=strend(buff_ptr);
+ }
+ else
+ buff_ptr=strmov(buff_ptr,"; Using where");
}
- else
- buff_ptr=strmov(buff_ptr,"; Using where");
- }
- if (key_read)
- buff_ptr= strmov(buff_ptr,"; Using index");
- if (table->reginfo.not_exists_optimize)
- buff_ptr= strmov(buff_ptr,"; Not exists");
- if (need_tmp_table)
- {
- need_tmp_table=0;
- buff_ptr= strmov(buff_ptr,"; Using temporary");
- }
- if (need_order)
- {
- need_order=0;
- buff_ptr= strmov(buff_ptr,"; Using filesort");
+ if (key_read)
+ buff_ptr= strmov(buff_ptr,"; Using index");
+ if (table->reginfo.not_exists_optimize)
+ buff_ptr= strmov(buff_ptr,"; Not exists");
+ if (need_tmp_table)
+ {
+ need_tmp_table=0;
+ buff_ptr= strmov(buff_ptr,"; Using temporary");
+ }
+ if (need_order)
+ {
+ need_order=0;
+ buff_ptr= strmov(buff_ptr,"; Using filesort");
+ }
+ if (distinct & test_all_bits(used_tables,thd->used_tables))
+ buff_ptr= strmov(buff_ptr,"; Distinct");
+ if (buff_ptr == buff)
+ buff_ptr+= 2; // Skip inital "; "
+ item_list.push_back(new Item_string(buff+2,(uint) (buff_ptr - buff)-2,
+ default_charset_info));
}
- if (distinct & test_all_bits(used_tables,thd->used_tables))
- buff_ptr= strmov(buff_ptr,"; Distinct");
- if (buff_ptr == buff)
- buff_ptr+= 2; // Skip inital "; "
- item_list.push_back(new Item_string(buff+2,(uint) (buff_ptr - buff)-2,
- default_charset_info));
// For next iteration
used_tables|=table->map;
if (result->send_data(item_list))
View Lorry Controller Status