maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #10680
Please review a patch for MDEV-12665 Hybrid functions do not preserve geometry type
Hello Alexey,
Please review a joint patch fixing these two problems:
MDEV-12665 Hybrid functions do not preserve geometry type
MDEV-12560 Wrong data type for SELECT NULL UNION SELECT Point(1,1)
Thanks!
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index fdc0e1e..1a4a4bb 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -2792,19 +2792,18 @@ t2 CREATE TABLE `t2` (
CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1
ERROR:
Illegal parameter data types set and geometry for operation 'UNION'
-# This does not preserve geometry type (MDEV-12560)
CREATE TABLE t1 AS SELECT COALESCE(NULL, Point(1,1));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `COALESCE(NULL, Point(1,1))` geometry DEFAULT NULL
+ `COALESCE(NULL, Point(1,1))` point DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
CREATE TABLE t1 AS SELECT NULL UNION SELECT Point(1,1);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `NULL` geometry DEFAULT NULL
+ `NULL` point DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
@@ -3889,12 +3888,11 @@ Table Create Table
t2 CREATE TABLE `t2` (
`LEAST(a,b)` longblob DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
-# This does not preserve geometry type (MDEV-9405)
CREATE TABLE t1 AS SELECT LEAST(NULL, Point(1,1));
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
- `LEAST(NULL, Point(1,1))` geometry DEFAULT NULL
+ `LEAST(NULL, Point(1,1))` point DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
DROP TABLE t1;
DROP PROCEDURE p1;
@@ -4122,5 +4120,185 @@ ERROR HY000: Illegal parameter data types geometry and varchar for operation 'st
SELECT STR_TO_DATE('2001-01-01', POINT(1,1));
ERROR HY000: Illegal parameter data types varchar and geometry for operation 'str_to_date'
#
+# MDEV-12665 Hybrid functions do not preserve geometry type
+#
+CREATE TABLE t1 AS SELECT
+Point(0,0) AS p0,
+COALESCE(Point(0,0)) AS p1,
+CASE WHEN 0 THEN Point(0,0) ELSE Point(1,1) END AS p2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `p0` point DEFAULT NULL,
+ `p1` point DEFAULT NULL,
+ `p2` point DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT LEAST(Point(0,0),Point(0,0)) AS p1;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `p1` point DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (
+c_geometry GEOMETRY,
+c_point POINT,
+c_linestring LINESTRING,
+c_polygon POLYGON,
+c_multipoint MULTIPOINT,
+c_multilinestring MULTILINESTRING,
+c_multipolygon MULTIPOLYGON,
+c_geometrycollection GEOMETRYCOLLECTION
+);
+CREATE TABLE t2 AS SELECT
+COALESCE(NULL, c_geometry),
+COALESCE(NULL, c_point),
+COALESCE(NULL, c_linestring),
+COALESCE(NULL, c_polygon),
+COALESCE(NULL, c_multipoint),
+COALESCE(NULL, c_multilinestring),
+COALESCE(NULL, c_multipolygon),
+COALESCE(NULL, c_geometrycollection)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `COALESCE(NULL, c_geometry)` geometry DEFAULT NULL,
+ `COALESCE(NULL, c_point)` point DEFAULT NULL,
+ `COALESCE(NULL, c_linestring)` linestring DEFAULT NULL,
+ `COALESCE(NULL, c_polygon)` polygon DEFAULT NULL,
+ `COALESCE(NULL, c_multipoint)` multipoint DEFAULT NULL,
+ `COALESCE(NULL, c_multilinestring)` multilinestring DEFAULT NULL,
+ `COALESCE(NULL, c_multipolygon)` multipolygon DEFAULT NULL,
+ `COALESCE(NULL, c_geometrycollection)` geometrycollection DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+COALESCE(c_geometry, NULL),
+COALESCE(c_point, NULL),
+COALESCE(c_linestring, NULL),
+COALESCE(c_polygon, NULL),
+COALESCE(c_multipoint, NULL),
+COALESCE(c_multilinestring, NULL),
+COALESCE(c_multipolygon, NULL),
+COALESCE(c_geometrycollection, NULL)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `COALESCE(c_geometry, NULL)` geometry DEFAULT NULL,
+ `COALESCE(c_point, NULL)` point DEFAULT NULL,
+ `COALESCE(c_linestring, NULL)` linestring DEFAULT NULL,
+ `COALESCE(c_polygon, NULL)` polygon DEFAULT NULL,
+ `COALESCE(c_multipoint, NULL)` multipoint DEFAULT NULL,
+ `COALESCE(c_multilinestring, NULL)` multilinestring DEFAULT NULL,
+ `COALESCE(c_multipolygon, NULL)` multipolygon DEFAULT NULL,
+ `COALESCE(c_geometrycollection, NULL)` geometrycollection DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+CREATE TABLE t2 AS SELECT
+COALESCE(c_geometry, c_geometry),
+COALESCE(c_point, c_point),
+COALESCE(c_linestring, c_linestring),
+COALESCE(c_polygon, c_polygon),
+COALESCE(c_multipoint, c_multipoint),
+COALESCE(c_multilinestring, c_multilinestring),
+COALESCE(c_multipolygon, c_multipolygon),
+COALESCE(c_geometrycollection, c_geometrycollection)
+FROM t1;
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `COALESCE(c_geometry, c_geometry)` geometry DEFAULT NULL,
+ `COALESCE(c_point, c_point)` point DEFAULT NULL,
+ `COALESCE(c_linestring, c_linestring)` linestring DEFAULT NULL,
+ `COALESCE(c_polygon, c_polygon)` polygon DEFAULT NULL,
+ `COALESCE(c_multipoint, c_multipoint)` multipoint DEFAULT NULL,
+ `COALESCE(c_multilinestring, c_multilinestring)` multilinestring DEFAULT NULL,
+ `COALESCE(c_multipolygon, c_multipolygon)` multipolygon DEFAULT NULL,
+ `COALESCE(c_geometrycollection, c_geometrycollection)` geometrycollection DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# MDEV-12560 Wrong data type for SELECT NULL UNION SELECT Point(1,1)
+#
+CREATE TABLE t1 AS SELECT NULL AS c1 UNION SELECT POINT(1,1);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` point DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE PROCEDURE p1(name TEXT)
+BEGIN
+EXECUTE IMMEDIATE CONCAT('CREATE TABLE t1 (a ', name, ')');
+CREATE TABLE t2 AS
+SELECT a AS a1, a AS a2, NULL AS a3 FROM t1 UNION
+SELECT a AS a1, NULL AS a2, a AS a3 FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+END;
+$$
+CALL p1('geometry');
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a1` geometry DEFAULT NULL,
+ `a2` geometry DEFAULT NULL,
+ `a3` geometry DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CALL p1('point');
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a1` point DEFAULT NULL,
+ `a2` point DEFAULT NULL,
+ `a3` point DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CALL p1('linestring');
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a1` linestring DEFAULT NULL,
+ `a2` linestring DEFAULT NULL,
+ `a3` linestring DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CALL p1('polygon');
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a1` polygon DEFAULT NULL,
+ `a2` polygon DEFAULT NULL,
+ `a3` polygon DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CALL p1('multipoint');
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a1` multipoint DEFAULT NULL,
+ `a2` multipoint DEFAULT NULL,
+ `a3` multipoint DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CALL p1('multilinestring');
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a1` multilinestring DEFAULT NULL,
+ `a2` multilinestring DEFAULT NULL,
+ `a3` multilinestring DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CALL p1('multipolygon');
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a1` multipolygon DEFAULT NULL,
+ `a2` multipolygon DEFAULT NULL,
+ `a3` multipolygon DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+CALL p1('geometrycollection');
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a1` geometrycollection DEFAULT NULL,
+ `a2` geometrycollection DEFAULT NULL,
+ `a3` geometrycollection DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP PROCEDURE p1;
+#
# End of 10.3 tests
#
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 9b3dc69..3479733 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -1789,7 +1789,6 @@ CALL p1('CREATE TABLE t1 (a ENUM(0x61), b Point)');
CALL p1('CREATE TABLE t1 (a SET(0x61), b Point)');
--enable_query_log
---echo # This does not preserve geometry type (MDEV-12560)
CREATE TABLE t1 AS SELECT COALESCE(NULL, Point(1,1));
SHOW CREATE TABLE t1;
DROP TABLE t1;
@@ -1932,7 +1931,6 @@ CALL p1('CREATE TABLE t1 (a ENUM(0x61), b Point)');
CALL p1('CREATE TABLE t1 (a SET(0x61), b Point)');
--enable_query_log
---echo # This does not preserve geometry type (MDEV-9405)
CREATE TABLE t1 AS SELECT LEAST(NULL, Point(1,1));
SHOW CREATE TABLE t1;
DROP TABLE t1;
@@ -2197,5 +2195,104 @@ SELECT STR_TO_DATE(POINT(1,1),'%M %d,%Y');
SELECT STR_TO_DATE('2001-01-01', POINT(1,1));
--echo #
+--echo # MDEV-12665 Hybrid functions do not preserve geometry type
+--echo #
+
+CREATE TABLE t1 AS SELECT
+ Point(0,0) AS p0,
+ COALESCE(Point(0,0)) AS p1,
+ CASE WHEN 0 THEN Point(0,0) ELSE Point(1,1) END AS p2;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 AS SELECT LEAST(Point(0,0),Point(0,0)) AS p1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+ c_geometry GEOMETRY,
+ c_point POINT,
+ c_linestring LINESTRING,
+ c_polygon POLYGON,
+ c_multipoint MULTIPOINT,
+ c_multilinestring MULTILINESTRING,
+ c_multipolygon MULTIPOLYGON,
+ c_geometrycollection GEOMETRYCOLLECTION
+);
+
+CREATE TABLE t2 AS SELECT
+ COALESCE(NULL, c_geometry),
+ COALESCE(NULL, c_point),
+ COALESCE(NULL, c_linestring),
+ COALESCE(NULL, c_polygon),
+ COALESCE(NULL, c_multipoint),
+ COALESCE(NULL, c_multilinestring),
+ COALESCE(NULL, c_multipolygon),
+ COALESCE(NULL, c_geometrycollection)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE TABLE t2 AS SELECT
+ COALESCE(c_geometry, NULL),
+ COALESCE(c_point, NULL),
+ COALESCE(c_linestring, NULL),
+ COALESCE(c_polygon, NULL),
+ COALESCE(c_multipoint, NULL),
+ COALESCE(c_multilinestring, NULL),
+ COALESCE(c_multipolygon, NULL),
+ COALESCE(c_geometrycollection, NULL)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+CREATE TABLE t2 AS SELECT
+ COALESCE(c_geometry, c_geometry),
+ COALESCE(c_point, c_point),
+ COALESCE(c_linestring, c_linestring),
+ COALESCE(c_polygon, c_polygon),
+ COALESCE(c_multipoint, c_multipoint),
+ COALESCE(c_multilinestring, c_multilinestring),
+ COALESCE(c_multipolygon, c_multipolygon),
+ COALESCE(c_geometrycollection, c_geometrycollection)
+FROM t1;
+SHOW CREATE TABLE t2;
+DROP TABLE t2;
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-12560 Wrong data type for SELECT NULL UNION SELECT Point(1,1)
+--echo #
+
+CREATE TABLE t1 AS SELECT NULL AS c1 UNION SELECT POINT(1,1);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+DELIMITER $$;
+CREATE PROCEDURE p1(name TEXT)
+BEGIN
+ EXECUTE IMMEDIATE CONCAT('CREATE TABLE t1 (a ', name, ')');
+ CREATE TABLE t2 AS
+ SELECT a AS a1, a AS a2, NULL AS a3 FROM t1 UNION
+ SELECT a AS a1, NULL AS a2, a AS a3 FROM t1;
+ SHOW CREATE TABLE t2;
+ DROP TABLE t2;
+ DROP TABLE t1;
+END;
+$$
+DELIMITER ;$$
+CALL p1('geometry');
+CALL p1('point');
+CALL p1('linestring');
+CALL p1('polygon');
+CALL p1('multipoint');
+CALL p1('multilinestring');
+CALL p1('multipolygon');
+CALL p1('geometrycollection');
+DROP PROCEDURE p1;
+
+--echo #
--echo # End of 10.3 tests
--echo #
diff --git a/sql/item.cc b/sql/item.cc
index c145e69..3bb232f 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -10029,18 +10029,14 @@ void Item_cache_row::set_null()
Item_type_holder::Item_type_holder(THD *thd, Item *item)
:Item(thd, item),
Type_handler_hybrid_field_type(item->real_type_handler()),
- enum_set_typelib(0),
- geometry_type(Field::GEOM_GEOMETRY)
+ Type_geometry_attributes(item),
+ enum_set_typelib(0)
{
DBUG_ASSERT(item->fixed);
maybe_null= item->maybe_null;
get_full_info(item);
DBUG_ASSERT(!decimals || Item_type_holder::result_type() != INT_RESULT);
prev_decimal_int_part= item->decimal_int_part();
-#ifdef HAVE_SPATIAL
- if (item->field_type() == MYSQL_TYPE_GEOMETRY)
- geometry_type= item->get_geometry_type();
-#endif /* HAVE_SPATIAL */
}
@@ -10093,9 +10089,7 @@ bool Item_type_holder::join_types(THD *thd, Item *item)
else
decimals= MY_MAX(decimals, item->decimals);
- if (Item_type_holder::field_type() == FIELD_TYPE_GEOMETRY)
- geometry_type=
- Field_geom::geometry_type_merge(geometry_type, item->get_geometry_type());
+ Type_geometry_attributes::join(item);
if (Item_type_holder::result_type() == DECIMAL_RESULT)
{
diff --git a/sql/item.h b/sql/item.h
index 0e00457..cf61e63 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1805,6 +1805,55 @@ inline Item* get_item_copy (THD *thd, MEM_ROOT *mem_root, T* item)
}
+class Type_geometry_attributes
+{
+ uint m_geometry_type;
+ static const uint m_geometry_type_unknown= Field::GEOM_GEOMETRYCOLLECTION + 1;
+ void copy(const Item *item)
+ {
+ // Ignore implicit NULLs
+ m_geometry_type= item->type_handler() == &type_handler_geometry ?
+ item->uint_geometry_type() :
+ m_geometry_type_unknown;
+ }
+public:
+ Type_geometry_attributes()
+ :m_geometry_type(m_geometry_type_unknown)
+ { }
+ Type_geometry_attributes(const Item *item)
+ :m_geometry_type(m_geometry_type_unknown)
+ {
+ copy(item);
+ }
+ void join(const Item *item)
+ {
+ // Ignore implicit NULLs
+ if (m_geometry_type == m_geometry_type_unknown)
+ copy(item);
+ else if (item->type_handler() == &type_handler_geometry)
+ {
+ m_geometry_type=
+ Field_geom::geometry_type_merge((Field_geom::geometry_type)
+ m_geometry_type,
+ (Field_geom::geometry_type)
+ item->uint_geometry_type());
+ }
+ }
+ Field::geometry_type get_geometry_type() const
+ {
+ return m_geometry_type == m_geometry_type_unknown ?
+ Field::GEOM_GEOMETRY :
+ (Field::geometry_type) m_geometry_type;
+ }
+ void set_geometry_type(uint type)
+ {
+ DBUG_ASSERT(type <= m_geometry_type_unknown);
+ m_geometry_type= type;
+ }
+};
+
+
+
/**
Compare two Items for List<Item>::add_unique()
*/
@@ -5774,12 +5823,11 @@ class Item_cache_row: public Item_cache
single SP/PS execution.
*/
class Item_type_holder: public Item,
- public Type_handler_hybrid_field_type
+ public Type_handler_hybrid_field_type,
+ public Type_geometry_attributes
{
protected:
TYPELIB *enum_set_typelib;
- Field::geometry_type geometry_type;
-
void get_full_info(Item *item);
/* It is used to count decimal precision in join_types */
@@ -5828,7 +5876,10 @@ class Item_type_holder: public Item,
make_and_init_table_field(&name, Record_addr(maybe_null),
*this, table);
}
- Field::geometry_type get_geometry_type() const { return geometry_type; };
+ Field::geometry_type get_geometry_type() const
+ {
+ return Type_geometry_attributes::get_geometry_type();
+ }
Item* get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; }
};
diff --git a/sql/item_func.h b/sql/item_func.h
index 33f671c..4a90551 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -381,7 +381,8 @@ class Item_real_func :public Item_func
Functions whose returned field type is determined at fix_fields() time.
*/
class Item_hybrid_func: public Item_func,
- public Type_handler_hybrid_field_type
+ public Type_handler_hybrid_field_type,
+ public Type_geometry_attributes
{
protected:
bool fix_attributes(Item **item, uint nitems);
@@ -402,6 +403,8 @@ class Item_hybrid_func: public Item_func,
{ return Type_handler_hybrid_field_type::result_type(); }
enum Item_result cmp_type () const
{ return Type_handler_hybrid_field_type::cmp_type(); }
+ Field::geometry_type get_geometry_type() const
+ { return Type_geometry_attributes::get_geometry_type(); };
};
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index 23cfe20..52893f6 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -2115,6 +2115,26 @@ bool Type_handler_timestamp_common::
return false;
}
+#ifdef HAVE_SPATIAL
+bool Type_handler_geometry::
+ Item_hybrid_func_fix_attributes(THD *thd, Item_hybrid_func *func,
+ Item **items, uint nitems) const
+{
+ DBUG_ASSERT(nitems > 0);
+ Type_geometry_attributes gattr(items[0]);
+ for (uint i= 1; i < nitems; i++)
+ gattr.join(items[i]);
+ func->set_geometry_type(gattr.get_geometry_type());
+ func->collation.set(&my_charset_bin);
+ func->unsigned_flag= false;
+ func->decimals= 0;
+ func->max_length= (uint32) UINT_MAX32;
+ func->maybe_null= true;
+ return false;
+}
+#endif
+
+
/*************************************************************************/
bool Type_handler::
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 67a302e..b28b76d 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -2032,6 +2032,8 @@ class Type_handler_geometry: public Type_handler_string_result
bool Item_func_int_val_fix_length_and_dec(Item_func_int_val *) const;
bool Item_func_abs_fix_length_and_dec(Item_func_abs *) const;
bool Item_func_neg_fix_length_and_dec(Item_func_neg *) const;
+ bool Item_hybrid_func_fix_attributes(THD *thd, Item_hybrid_func *func,
+ Item **items, uint nitems) const;
bool Item_sum_sum_fix_length_and_dec(Item_sum_sum *) const;
bool Item_sum_avg_fix_length_and_dec(Item_sum_avg *) const;
bool Item_sum_variance_fix_length_and_dec(Item_sum_variance *) const;
