summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/derived_view.result6
-rw-r--r--mysql-test/r/gis.result23
-rw-r--r--mysql-test/r/view.result33
-rw-r--r--mysql-test/t/gis.test16
-rw-r--r--mysql-test/t/view.test30
-rw-r--r--sql/item.cc10
-rw-r--r--sql/item.h98
-rw-r--r--sql/item_geofunc.cc3
-rw-r--r--sql/table.cc22
-rw-r--r--sql/table.h1
10 files changed, 227 insertions, 15 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result
index ba01db4a66f..5446406924f 100644
--- a/mysql-test/r/derived_view.result
+++ b/mysql-test/r/derived_view.result
@@ -1687,7 +1687,6 @@ SELECT t.b, t.c, t1.a
FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
WHERE t.b AND t.c = t1.a;
b c a
-8 c c
EXPLAIN EXTENDED
SELECT t.b, t.c, t1.a
FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
@@ -1702,7 +1701,6 @@ SELECT t.b, t.c, t1.a
FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
WHERE t.b <> 0 AND t.c = t1.a;
b c a
-8 c c
INSERT INTO t3 VALUES (100), (200);
EXPLAIN EXTENDED
SELECT t.b, t.c, t1.a
@@ -1718,7 +1716,7 @@ SELECT t.b, t.c, t1.a
FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
WHERE t.b AND t.c = t1.a;
b c a
-8 c c
+NULL NULL c
EXPLAIN EXTENDED
SELECT t.b, t.c, t1.a
FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
@@ -1733,7 +1731,7 @@ SELECT t.b, t.c, t1.a
FROM t1, (SELECT t2.b, t2.c FROM t3 RIGHT JOIN t2 ON t2.a = t3.b) AS t
WHERE t.b <> 0 AND t.c = t1.a;
b c a
-8 c c
+NULL NULL c
SET optimizer_switch=@save_optimizer_switch;
DROP TABLE t1,t2,t3;
#
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index ca48177b1b5..30385323502 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -1428,6 +1428,29 @@ WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1;
count(*)
1
DROP DATABASE gis_ogs;
+#
+# BUG #1043845 st_distance() results are incorrect depending on variable order
+#
+select st_distance(geomfromtext('LINESTRING(-95.9673005697771 36.13509598461,
+ -95.9673057475387 36.1344478941074,
+ -95.9673063519371 36.134484524621,
+ -95.9673049102515 36.1343976584193)'),
+geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)')) ;
+st_distance(geomfromtext('LINESTRING(-95.9673005697771 36.13509598461,
+ -95.9673057475387 36.1344478941074,
+ -95.9673063519371 36.134484524621,
+
+0.008148695928146028
+select st_distance(geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)'),
+geomfromtext('LINESTRING(-95.9673005697771 36.13509598461,
+ -95.9673057475387 36.1344478941074,
+ -95.9673063519371 36.134484524621,
+ -95.9673049102515 36.1343976584193) ')) ;
+st_distance(geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)'),
+geomfromtext('LINESTRING(-95.9673005697771 36.13509598461,
+ -95.9673057475387 36.1344478941074,
+ -95.9673063519371 36.
+0.008148695928146028
USE test;
#
# BUG#12414917 - ISCLOSED() CRASHES ON 64-BIT BUILDS
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index b50f6b81206..1b35fe5a56a 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -4687,6 +4687,39 @@ a
1
drop view v2,v1;
drop table t1;
+#
+# MDEV-486 LP BUG#1010116 Incorrect query results in
+# view and derived tables
+#
+SELECT
+`Derived1`.`id`,
+`Derived2`.`Val1`
+FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT
+2 as `id`,
+1 AS `Val1`
+FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`;
+id Val1
+30631 NULL
+create table t1 ( id int );
+insert into t1 values (30631);
+create table t2 ( id int );
+insert into t2 values (30631);
+create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2;
+select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;
+id id val1
+30631 NULL NULL
+drop view v2;
+drop table t1,t2;
+create table t1 ( id int );
+insert into t1 values (30631);
+create table t2 ( id int );
+insert into t2 values (30631);
+create algorithm=MERGE view v2 as select 2 as id, id is null as bbb, id as iddqd, 1 as val1 from t2;
+select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;
+id id bbb iddqd val1
+30631 NULL NULL NULL NULL
+drop view v2;
+drop table t1,t2;
# -----------------------------------------------------------------
# -- End of 5.3 tests.
# -----------------------------------------------------------------
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index f4c9de4e7da..b0ad8329cfb 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -1293,6 +1293,21 @@ WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1;
#WHERE lakes.name = 'Blue Lake';
DROP DATABASE gis_ogs;
+
+--echo #
+--echo # BUG #1043845 st_distance() results are incorrect depending on variable order
+--echo #
+
+select st_distance(geomfromtext('LINESTRING(-95.9673005697771 36.13509598461,
+ -95.9673057475387 36.1344478941074,
+ -95.9673063519371 36.134484524621,
+ -95.9673049102515 36.1343976584193)'),
+ geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)')) ;
+select st_distance(geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)'),
+ geomfromtext('LINESTRING(-95.9673005697771 36.13509598461,
+ -95.9673057475387 36.1344478941074,
+ -95.9673063519371 36.134484524621,
+ -95.9673049102515 36.1343976584193) ')) ;
USE test;
@@ -1344,4 +1359,3 @@ SELECT 1 FROM g1 WHERE a >= ANY
DROP TABLE g1;
--echo End of 5.5 tests
-
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 17026e45724..efb6956ae8f 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -4626,6 +4626,36 @@ select * from t1;
drop view v2,v1;
drop table t1;
+--echo #
+--echo # MDEV-486 LP BUG#1010116 Incorrect query results in
+--echo # view and derived tables
+--echo #
+
+SELECT
+`Derived1`.`id`,
+`Derived2`.`Val1`
+FROM (select 30631 as `id`) AS `Derived1` LEFT OUTER JOIN (SELECT
+2 as `id`,
+1 AS `Val1`
+FROM (select 30631 as `id`) AS `Derived3`) AS `Derived2` ON `Derived1`.`id` = `Derived2`.`id`;
+
+create table t1 ( id int );
+insert into t1 values (30631);
+create table t2 ( id int );
+insert into t2 values (30631);
+create algorithm=MERGE view v2 as select 2 as id, 1 as val1 from t2;
+select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;
+drop view v2;
+drop table t1,t2;
+
+create table t1 ( id int );
+insert into t1 values (30631);
+create table t2 ( id int );
+insert into t2 values (30631);
+create algorithm=MERGE view v2 as select 2 as id, id is null as bbb, id as iddqd, 1 as val1 from t2;
+select t1.*, v2.* from t1 left join v2 on t1.id = v2.id;
+drop view v2;
+drop table t1,t2;
--echo # -----------------------------------------------------------------
--echo # -- End of 5.3 tests.
--echo # -----------------------------------------------------------------
diff --git a/sql/item.cc b/sql/item.cc
index 582865a1b61..4d80a153785 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -7759,6 +7759,13 @@ Item* Item_cache_wrapper::get_tmp_table_item(THD *thd_arg)
}
+bool Item_direct_view_ref::send(Protocol *protocol, String *buffer)
+{
+ if (check_null_ref())
+ return protocol->store_null();
+ return Item_direct_ref::send(protocol, buffer);
+}
+
/**
Prepare referenced field then call usual Item_direct_ref::fix_fields .
@@ -7773,6 +7780,7 @@ Item* Item_cache_wrapper::get_tmp_table_item(THD *thd_arg)
bool Item_direct_view_ref::fix_fields(THD *thd, Item **reference)
{
+ DBUG_ASSERT(1);
/* view fild reference must be defined */
DBUG_ASSERT(*ref);
/* (*ref)->check_cols() will be made in Item_direct_ref::fix_fields */
@@ -9285,7 +9293,7 @@ bool Item_type_holder::join_types(THD *thd, Item *item)
item->max_length, item->decimals));
fld_type= Field::field_type_merge(fld_type, get_real_type(item));
{
- int item_decimals= item->decimals;
+ uint item_decimals= item->decimals;
/* fix variable decimals which always is NOT_FIXED_DEC */
if (Field::result_merge_type(fld_type) == INT_RESULT)
item_decimals= 0;
diff --git a/sql/item.h b/sql/item.h
index 98ec011d4b5..9bcb913d5c5 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -3161,20 +3161,29 @@ class Item_direct_view_ref :public Item_direct_ref
{
Item_equal *item_equal;
TABLE_LIST *view;
+ TABLE *null_ref_table;
+
+ bool check_null_ref()
+ {
+ if (null_ref_table == NULL)
+ {
+ null_ref_table= view->get_real_join_table();
+ }
+ if (null_ref_table->null_row)
+ {
+ null_value= 1;
+ return TRUE;
+ }
+ return FALSE;
+ }
public:
Item_direct_view_ref(Name_resolution_context *context_arg, Item **item,
const char *table_name_arg,
const char *field_name_arg,
TABLE_LIST *view_arg)
:Item_direct_ref(context_arg, item, table_name_arg, field_name_arg),
- item_equal(0), view(view_arg) {}
- /* Constructor need to process subselect with temporary tables (see Item) */
- Item_direct_view_ref(THD *thd, Item_direct_ref *item)
- :Item_direct_ref(thd, item), item_equal(0) {}
- Item_direct_view_ref(TABLE_LIST *view_arg, Item **item,
- const char *field_name_arg)
- :Item_direct_ref(view_arg, item, field_name_arg), item_equal(0)
- {}
+ item_equal(0), view(view_arg),
+ null_ref_table(NULL) {}
bool fix_fields(THD *, Item **);
bool eq(const Item *item, bool binary_cmp) const;
@@ -3205,6 +3214,79 @@ public:
view_arg->view_used_tables|= (*ref)->used_tables();
return 0;
}
+ void save_val(Field *to)
+ {
+ if (check_null_ref())
+ to->set_null();
+ else
+ Item_direct_ref::save_val(to);
+ }
+ double val_real()
+ {
+ if (check_null_ref())
+ return 0;
+ else
+ return Item_direct_ref::val_real();
+ }
+ longlong val_int()
+ {
+ if (check_null_ref())
+ return 0;
+ else
+ return Item_direct_ref::val_int();
+ }
+ String *val_str(String* tmp)
+ {
+ if (check_null_ref())
+ return NULL;
+ else
+ return Item_direct_ref::val_str(tmp);
+ }
+ my_decimal *val_decimal(my_decimal *tmp)
+ {
+ if (check_null_ref())
+ return NULL;
+ else
+ return Item_direct_ref::val_decimal(tmp);
+ }
+ bool val_bool()
+ {
+ if (check_null_ref())
+ return 0;
+ else
+ return Item_direct_ref::val_bool();
+ }
+ bool is_null()
+ {
+ if (check_null_ref())
+ return 1;
+ else
+ return Item_direct_ref::is_null();
+ }
+ bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate)
+ {
+ if (check_null_ref())
+ {
+ bzero((char*) ltime,sizeof(*ltime));
+ return 1;
+ }
+ return Item_direct_ref::get_date(ltime, fuzzydate);
+ }
+ bool send(Protocol *protocol, String *buffer);
+ void save_org_in_field(Field *field)
+ {
+ if (check_null_ref())
+ field->set_null();
+ else
+ Item_direct_ref::save_val(field);
+ }
+ void save_in_result_field(bool no_conversions)
+ {
+ if (check_null_ref())
+ result_field->set_null();
+ else
+ Item_direct_ref::save_in_result_field(no_conversions);
+ }
};
diff --git a/sql/item_geofunc.cc b/sql/item_geofunc.cc
index 3648b10af3f..bc89a6c14b3 100644
--- a/sql/item_geofunc.cc
+++ b/sql/item_geofunc.cc
@@ -1695,7 +1695,8 @@ count_distance:
for (dist_point= collector.get_first(); dist_point; dist_point= dist_point->get_next())
{
/* We only check vertices of object 2 */
- if (dist_point->shape < obj2_si)
+ if (dist_point->type != Gcalc_heap::nt_shape_node ||
+ dist_point->shape < obj2_si)
continue;
/* if we have an edge to check */
diff --git a/sql/table.cc b/sql/table.cc
index d733c5ada62..47b2cae1a04 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4949,6 +4949,28 @@ void TABLE_LIST::set_check_materialized()
}
}
+TABLE *TABLE_LIST::get_real_join_table()
+{
+ TABLE_LIST *tbl= this;
+ while (tbl->table == NULL || tbl->table->reginfo.join_tab == NULL)
+ {
+ if (tbl->view == NULL && tbl->derived == NULL)
+ break;
+ /* we do not support merging of union yet */
+ DBUG_ASSERT(tbl->view == NULL ||
+ tbl->view->select_lex.next_select() == NULL);
+ DBUG_ASSERT(tbl->derived == NULL ||
+ tbl->derived->first_select()->next_select() == NULL);
+
+ if (tbl->table)
+ table= tbl->table;
+ tbl= (tbl->view != NULL ?
+ tbl->view->select_lex.get_table_list() :
+ tbl->derived->first_select()->get_table_list());
+ }
+ return tbl->table;
+}
+
Natural_join_column::Natural_join_column(Field_translator *field_param,
TABLE_LIST *tab)
diff --git a/sql/table.h b/sql/table.h
index 87affe984fc..f5ae3bcebb3 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1929,6 +1929,7 @@ struct TABLE_LIST
TABLE_LIST *find_underlying_table(TABLE *table);
TABLE_LIST *first_leaf_for_name_resolution();
TABLE_LIST *last_leaf_for_name_resolution();
+ TABLE *get_real_join_table();
bool is_leaf_for_name_resolution();
inline TABLE_LIST *top_table()
{ return belong_to_view ? belong_to_view : this; }
View Lorry Controller Status