diff options
Diffstat (limited to 'mysql-test/main/gis.result')
| -rw-r--r-- | mysql-test/main/gis.result | 4981 |
1 files changed, 4981 insertions, 0 deletions
diff --git a/mysql-test/main/gis.result b/mysql-test/main/gis.result new file mode 100644 index 00000000000..5a01a402c7d --- /dev/null +++ b/mysql-test/main/gis.result @@ -0,0 +1,4981 @@ +CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT); +CREATE TABLE gis_line (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING); +CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON); +CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT); +CREATE TABLE gis_multi_line (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING); +CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON); +CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION); +CREATE TABLE gis_geometry (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY); +SHOW FIELDS FROM gis_point; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL +g point YES NULL +SHOW FIELDS FROM gis_line; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL +g linestring YES NULL +SHOW FIELDS FROM gis_polygon; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL +g polygon YES NULL +SHOW FIELDS FROM gis_multi_point; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL +g multipoint YES NULL +SHOW FIELDS FROM gis_multi_line; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL +g multilinestring YES NULL +SHOW FIELDS FROM gis_multi_polygon; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL +g multipolygon YES NULL +SHOW FIELDS FROM gis_geometrycollection; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL +g geometrycollection YES NULL +SHOW FIELDS FROM gis_geometry; +Field Type Null Key Default Extra +fid int(11) NO PRI NULL +g geometry YES NULL +INSERT INTO gis_point VALUES +(101, PointFromText('POINT(10 10)')), +(102, PointFromText('POINT(20 10)')), +(103, PointFromText('POINT(20 20)')), +(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); +INSERT INTO gis_line VALUES +(105, LineFromText('LINESTRING(0 0,0 10,10 0)')), +(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')), +(107, LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10))))); +INSERT INTO gis_polygon VALUES +(108, PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')), +(109, PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')), +(110, PolyFromWKB(AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))))); +INSERT INTO gis_multi_point VALUES +(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')), +(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')), +(113, MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10))))); +INSERT INTO gis_multi_line VALUES +(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')), +(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')), +(116, MLineFromWKB(AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))))); +INSERT INTO gis_multi_polygon VALUES +(117, MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), +(118, MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')), +(119, MPolyFromWKB(AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))))); +INSERT INTO gis_geometrycollection VALUES +(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')), +(121, GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))), +(122, GeomFromText('GeometryCollection()')), +(123, GeomFromText('GeometryCollection EMPTY')); +INSERT into gis_geometry SELECT * FROM gis_point; +INSERT into gis_geometry SELECT * FROM gis_line; +INSERT into gis_geometry SELECT * FROM gis_polygon; +INSERT into gis_geometry SELECT * FROM gis_multi_point; +INSERT into gis_geometry SELECT * FROM gis_multi_line; +INSERT into gis_geometry SELECT * FROM gis_multi_polygon; +INSERT into gis_geometry SELECT * FROM gis_geometrycollection; +SELECT fid, AsText(g) FROM gis_point; +fid AsText(g) +101 POINT(10 10) +102 POINT(20 10) +103 POINT(20 20) +104 POINT(10 20) +SELECT fid, AsText(g) FROM gis_line; +fid AsText(g) +105 LINESTRING(0 0,0 10,10 0) +106 LINESTRING(10 10,20 10,20 20,10 20,10 10) +107 LINESTRING(10 10,40 10) +SELECT fid, AsText(g) FROM gis_polygon; +fid AsText(g) +108 POLYGON((10 10,20 10,20 20,10 20,10 10)) +109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10)) +110 POLYGON((0 0,30 0,30 30,0 0)) +SELECT fid, AsText(g) FROM gis_multi_point; +fid AsText(g) +111 MULTIPOINT(0 0,10 10,10 20,20 20) +112 MULTIPOINT(1 1,11 11,11 21,21 21) +113 MULTIPOINT(3 6,4 10) +SELECT fid, AsText(g) FROM gis_multi_line; +fid AsText(g) +114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48)) +115 MULTILINESTRING((10 48,10 21,10 0)) +116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7)) +SELECT fid, AsText(g) FROM gis_multi_polygon; +fid AsText(g) +117 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) +118 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) +119 MULTIPOLYGON(((0 3,3 3,3 0,0 3))) +SELECT fid, AsText(g) FROM gis_geometrycollection; +fid AsText(g) +120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10)) +121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9)) +122 GEOMETRYCOLLECTION EMPTY +123 GEOMETRYCOLLECTION EMPTY +SELECT fid, AsText(g) FROM gis_geometry; +fid AsText(g) +101 POINT(10 10) +102 POINT(20 10) +103 POINT(20 20) +104 POINT(10 20) +105 LINESTRING(0 0,0 10,10 0) +106 LINESTRING(10 10,20 10,20 20,10 20,10 10) +107 LINESTRING(10 10,40 10) +108 POLYGON((10 10,20 10,20 20,10 20,10 10)) +109 POLYGON((0 0,50 0,50 50,0 50,0 0),(10 10,20 10,20 20,10 20,10 10)) +110 POLYGON((0 0,30 0,30 30,0 0)) +111 MULTIPOINT(0 0,10 10,10 20,20 20) +112 MULTIPOINT(1 1,11 11,11 21,21 21) +113 MULTIPOINT(3 6,4 10) +114 MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48)) +115 MULTILINESTRING((10 48,10 21,10 0)) +116 MULTILINESTRING((1 2,3 5),(2 5,5 8,21 7)) +117 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) +118 MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18))) +119 MULTIPOLYGON(((0 3,3 3,3 0,0 3))) +120 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 0,10 10)) +121 GEOMETRYCOLLECTION(POINT(44 6),LINESTRING(3 6,7 9)) +122 GEOMETRYCOLLECTION EMPTY +123 GEOMETRYCOLLECTION EMPTY +SELECT fid, Dimension(g) FROM gis_geometry; +fid Dimension(g) +101 0 +102 0 +103 0 +104 0 +105 1 +106 1 +107 1 +108 2 +109 2 +110 2 +111 0 +112 0 +113 0 +114 1 +115 1 +116 1 +117 2 +118 2 +119 2 +120 1 +121 1 +122 0 +123 0 +SELECT fid, GeometryType(g) FROM gis_geometry; +fid GeometryType(g) +101 POINT +102 POINT +103 POINT +104 POINT +105 LINESTRING +106 LINESTRING +107 LINESTRING +108 POLYGON +109 POLYGON +110 POLYGON +111 MULTIPOINT +112 MULTIPOINT +113 MULTIPOINT +114 MULTILINESTRING +115 MULTILINESTRING +116 MULTILINESTRING +117 MULTIPOLYGON +118 MULTIPOLYGON +119 MULTIPOLYGON +120 GEOMETRYCOLLECTION +121 GEOMETRYCOLLECTION +122 GEOMETRYCOLLECTION +123 GEOMETRYCOLLECTION +SELECT fid, IsEmpty(g) FROM gis_geometry; +fid IsEmpty(g) +101 0 +102 0 +103 0 +104 0 +105 0 +106 0 +107 0 +108 0 +109 0 +110 0 +111 0 +112 0 +113 0 +114 0 +115 0 +116 0 +117 0 +118 0 +119 0 +120 0 +121 0 +122 0 +123 0 +SELECT fid, AsText(Envelope(g)) FROM gis_geometry; +fid AsText(Envelope(g)) +101 POLYGON((10 10,10 10,10 10,10 10,10 10)) +102 POLYGON((20 10,20 10,20 10,20 10,20 10)) +103 POLYGON((20 20,20 20,20 20,20 20,20 20)) +104 POLYGON((10 20,10 20,10 20,10 20,10 20)) +105 POLYGON((0 0,10 0,10 10,0 10,0 0)) +106 POLYGON((10 10,20 10,20 20,10 20,10 10)) +107 POLYGON((10 10,40 10,40 10,10 10,10 10)) +108 POLYGON((10 10,20 10,20 20,10 20,10 10)) +109 POLYGON((0 0,50 0,50 50,0 50,0 0)) +110 POLYGON((0 0,30 0,30 30,0 30,0 0)) +111 POLYGON((0 0,20 0,20 20,0 20,0 0)) +112 POLYGON((1 1,21 1,21 21,1 21,1 1)) +113 POLYGON((3 6,4 6,4 10,3 10,3 6)) +114 POLYGON((10 0,16 0,16 48,10 48,10 0)) +115 POLYGON((10 0,10 0,10 48,10 48,10 0)) +116 POLYGON((1 2,21 2,21 8,1 8,1 2)) +117 POLYGON((28 0,84 0,84 42,28 42,28 0)) +118 POLYGON((28 0,84 0,84 42,28 42,28 0)) +119 POLYGON((0 0,3 0,3 3,0 3,0 0)) +120 POLYGON((0 0,10 0,10 10,0 10,0 0)) +121 POLYGON((3 6,44 6,44 9,3 9,3 6)) +122 GEOMETRYCOLLECTION EMPTY +123 GEOMETRYCOLLECTION EMPTY +explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE gis_geometry ALL NULL NULL NULL NULL 23 100.00 +Warnings: +Note 1003 select st_dimension(`test`.`gis_geometry`.`g`) AS `Dimension(g)`,st_geometrytype(`test`.`gis_geometry`.`g`) AS `GeometryType(g)`,st_isempty(`test`.`gis_geometry`.`g`) AS `IsEmpty(g)`,st_astext(st_envelope(`test`.`gis_geometry`.`g`)) AS `AsText(Envelope(g))` from `test`.`gis_geometry` +SELECT fid, X(g) FROM gis_point; +fid X(g) +101 10 +102 20 +103 20 +104 10 +SELECT fid, Y(g) FROM gis_point; +fid Y(g) +101 10 +102 10 +103 20 +104 20 +explain extended select X(g),Y(g) FROM gis_point; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE gis_point ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 select st_x(`test`.`gis_point`.`g`) AS `X(g)`,st_y(`test`.`gis_point`.`g`) AS `Y(g)` from `test`.`gis_point` +SELECT fid, AsText(StartPoint(g)) FROM gis_line; +fid AsText(StartPoint(g)) +105 POINT(0 0) +106 POINT(10 10) +107 POINT(10 10) +SELECT fid, AsText(EndPoint(g)) FROM gis_line; +fid AsText(EndPoint(g)) +105 POINT(10 0) +106 POINT(10 10) +107 POINT(40 10) +SELECT fid, GLength(g) FROM gis_line; +fid GLength(g) +105 24.14213562373095 +106 40 +107 30 +SELECT fid, NumPoints(g) FROM gis_line; +fid NumPoints(g) +105 3 +106 5 +107 2 +SELECT fid, AsText(PointN(g, 2)) FROM gis_line; +fid AsText(PointN(g, 2)) +105 POINT(0 10) +106 POINT(20 10) +107 POINT(40 10) +SELECT fid, IsClosed(g) FROM gis_line; +fid IsClosed(g) +105 0 +106 1 +107 0 +explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE gis_line ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select st_astext(st_startpoint(`test`.`gis_line`.`g`)) AS `AsText(StartPoint(g))`,st_astext(st_endpoint(`test`.`gis_line`.`g`)) AS `AsText(EndPoint(g))`,st_length(`test`.`gis_line`.`g`) AS `GLength(g)`,st_numpoints(`test`.`gis_line`.`g`) AS `NumPoints(g)`,st_astext(st_pointn(`test`.`gis_line`.`g`,2)) AS `AsText(PointN(g, 2))`,st_isclosed(`test`.`gis_line`.`g`) AS `IsClosed(g)` from `test`.`gis_line` +SELECT fid, AsText(Centroid(g)) FROM gis_polygon; +fid AsText(Centroid(g)) +108 POINT(15 15) +109 POINT(25.416666666666668 25.416666666666668) +110 POINT(20 10) +SELECT fid, Area(g) FROM gis_polygon; +fid Area(g) +108 100 +109 2400 +110 450 +SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon; +fid AsText(ExteriorRing(g)) +108 LINESTRING(10 10,20 10,20 20,10 20,10 10) +109 LINESTRING(0 0,50 0,50 50,0 50,0 0) +110 LINESTRING(0 0,30 0,30 30,0 0) +SELECT fid, NumInteriorRings(g) FROM gis_polygon; +fid NumInteriorRings(g) +108 0 +109 1 +110 0 +SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon; +fid AsText(InteriorRingN(g, 1)) +108 NULL +109 LINESTRING(10 10,20 10,20 20,10 20,10 10) +110 NULL +explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE gis_polygon ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select st_astext(st_centroid(`test`.`gis_polygon`.`g`)) AS `AsText(Centroid(g))`,st_area(`test`.`gis_polygon`.`g`) AS `Area(g)`,st_astext(st_exteriorring(`test`.`gis_polygon`.`g`)) AS `AsText(ExteriorRing(g))`,st_numinteriorrings(`test`.`gis_polygon`.`g`) AS `NumInteriorRings(g)`,st_astext(st_interiorringn(`test`.`gis_polygon`.`g`,1)) AS `AsText(InteriorRingN(g, 1))` from `test`.`gis_polygon` +SELECT fid, IsClosed(g) FROM gis_multi_line; +fid IsClosed(g) +114 0 +115 0 +116 0 +SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon; +fid AsText(Centroid(g)) +117 POINT(57.98031067576927 17.854754130800433) +118 POINT(57.98031067576927 17.854754130800433) +119 POINT(2 2) +SELECT fid, Area(g) FROM gis_multi_polygon; +fid Area(g) +117 1684.5 +118 1684.5 +119 4.5 +SELECT fid, NumGeometries(g) from gis_multi_point; +fid NumGeometries(g) +111 4 +112 4 +113 2 +SELECT fid, NumGeometries(g) from gis_multi_line; +fid NumGeometries(g) +114 2 +115 1 +116 2 +SELECT fid, NumGeometries(g) from gis_multi_polygon; +fid NumGeometries(g) +117 2 +118 2 +119 1 +SELECT fid, NumGeometries(g) from gis_geometrycollection; +fid NumGeometries(g) +120 2 +121 2 +122 0 +123 0 +explain extended SELECT fid, NumGeometries(g) from gis_multi_point; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_numgeometries(`test`.`gis_multi_point`.`g`) AS `NumGeometries(g)` from `test`.`gis_multi_point` +SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point; +fid AsText(GeometryN(g, 2)) +111 POINT(10 10) +112 POINT(11 11) +113 POINT(4 10) +SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line; +fid AsText(GeometryN(g, 2)) +114 LINESTRING(16 0,16 23,16 48) +115 NULL +116 LINESTRING(2 5,5 8,21 7) +SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon; +fid AsText(GeometryN(g, 2)) +117 POLYGON((59 18,67 18,67 13,59 13,59 18)) +118 POLYGON((59 18,67 18,67 13,59 13,59 18)) +119 NULL +SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection; +fid AsText(GeometryN(g, 2)) +120 LINESTRING(0 0,10 10) +121 LINESTRING(3 6,7 9) +122 NULL +123 NULL +SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection; +fid AsText(GeometryN(g, 1)) +120 POINT(0 0) +121 POINT(44 6) +122 NULL +123 NULL +explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE gis_multi_point ALL NULL NULL NULL NULL 3 100.00 +Warnings: +Note 1003 select `test`.`gis_multi_point`.`fid` AS `fid`,st_astext(st_geometryn(`test`.`gis_multi_point`.`g`,2)) AS `AsText(GeometryN(g, 2))` from `test`.`gis_multi_point` +SELECT g1.fid as first, g2.fid as second, +Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, +Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, +Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r +FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; +first second w c o e d t i r +120 120 1 1 0 1 0 0 1 0 +120 121 0 0 1 0 0 0 1 0 +120 122 NULL NULL NULL NULL NULL NULL NULL NULL +120 123 NULL NULL NULL NULL NULL NULL NULL NULL +121 120 0 0 1 0 0 0 1 0 +121 121 1 1 0 1 0 0 1 0 +121 122 NULL NULL NULL NULL NULL NULL NULL NULL +121 123 NULL NULL NULL NULL NULL NULL NULL NULL +122 120 NULL NULL NULL NULL NULL NULL NULL NULL +122 121 NULL NULL NULL NULL NULL NULL NULL NULL +122 122 NULL NULL NULL NULL NULL NULL NULL NULL +122 123 NULL NULL NULL NULL NULL NULL NULL NULL +123 120 NULL NULL NULL NULL NULL NULL NULL NULL +123 121 NULL NULL NULL NULL NULL NULL NULL NULL +123 122 NULL NULL NULL NULL NULL NULL NULL NULL +123 123 NULL NULL NULL NULL NULL NULL NULL NULL +explain extended SELECT g1.fid as first, g2.fid as second, +Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o, +Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t, +Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r +FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE g1 ALL NULL NULL NULL NULL 4 100.00 Using temporary; Using filesort +1 SIMPLE g2 ALL NULL NULL NULL NULL 4 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`g1`.`fid` AS `first`,`test`.`g2`.`fid` AS `second`,st_within(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `w`,st_contains(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `c`,mbroverlaps(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `o`,st_equals(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `e`,mbrdisjoint(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `d`,st_touches(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `t`,mbrintersects(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `i`,st_crosses(`test`.`g1`.`g`,`test`.`g2`.`g`) AS `r` from `test`.`gis_geometrycollection` `g1` join `test`.`gis_geometrycollection` `g2` order by `test`.`g1`.`fid`,`test`.`g2`.`fid` +DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; +CREATE TABLE t1 ( +gp point, +ln linestring, +pg polygon, +mp multipoint, +mln multilinestring, +mpg multipolygon, +gc geometrycollection, +gm geometry +); +SHOW FIELDS FROM t1; +Field Type Null Key Default Extra +gp point YES NULL +ln linestring YES NULL +pg polygon YES NULL +mp multipoint YES NULL +mln multilinestring YES NULL +mpg multipolygon YES NULL +gc geometrycollection YES NULL +gm geometry YES NULL +ALTER TABLE t1 ADD fid INT NOT NULL; +SHOW FIELDS FROM t1; +Field Type Null Key Default Extra +gp point YES NULL +ln linestring YES NULL +pg polygon YES NULL +mp multipoint YES NULL +mln multilinestring YES NULL +mpg multipolygon YES NULL +gc geometrycollection YES NULL +gm geometry YES NULL +fid int(11) NO NULL +DROP TABLE t1; +SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))); +AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))) +POINT(1 4) +explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)')))); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select st_astext(st_geometryfromwkb(st_aswkb(st_geometryfromtext('POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))))` +explain extended SELECT AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)')))); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select st_astext(st_geometryfromwkb(st_aswkb(st_geometryfromtext('POINT(1 4)')))) AS `AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))))` +SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); +SRID(GeomFromText('LineString(1 1,2 2)',101)) +101 +explain extended SELECT SRID(GeomFromText('LineString(1 1,2 2)',101)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select srid(st_geometryfromtext('LineString(1 1,2 2)',101)) AS `SRID(GeomFromText('LineString(1 1,2 2)',101))` +explain extended select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select st_issimple(geometrycollection(point(3,6),point(4,10))) AS `issimple(MultiPoint(Point(3, 6), Point(4, 10)))`,st_issimple(point(3,6)) AS `issimple(Point(3, 6))` +create table t1 (a geometry not null); +insert into t1 values (GeomFromText('Point(1 2)')); +insert into t1 values ('Garbage'); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +insert IGNORE into t1 values ('Garbage'); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +alter table t1 add spatial index(a); +drop table t1; +create table t1(a geometry not null, spatial index(a)); +insert into t1 values +(GeomFromText('POINT(1 1)')), (GeomFromText('POINT(3 3)')), +(GeomFromText('POINT(4 4)')), (GeomFromText('POINT(6 6)')); +select AsText(a) from t1 where +MBRContains(GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a) +or +MBRContains(GeomFromText('Polygon((2 2, 2 5, 5 5, 5 2, 2 2))'), a); +AsText(a) +POINT(1 1) +POINT(3 3) +POINT(4 4) +select AsText(a) from t1 where +MBRContains(GeomFromText('Polygon((0 0, 0 2, 2 2, 2 0, 0 0))'), a) +and +MBRContains(GeomFromText('Polygon((0 0, 0 7, 7 7, 7 0, 0 0))'), a); +AsText(a) +POINT(1 1) +drop table t1; +CREATE TABLE t1 (Coordinates POINT NOT NULL, SPATIAL INDEX(Coordinates)); +INSERT INTO t1 VALUES(GeomFromText('POINT(383293632 1754448)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(564952612 157516260)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(903994614 180726515)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(98128178 141127631)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(862547902 799334546)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(341989013 850270906)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(803302376 93039099)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(857439153 817431356)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(319757546 343162742)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(826341972 717484432)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(305066789 201736238)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(626068992 616241497)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(55789424 755830108)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(802874458 312435220)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(153795660 551723671)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(242207428 537089292)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(553478119 807160039)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(694605552 457472733)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(987886554 792733729)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(598600363 850434457)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(592068275 940589376)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(700705362 395370650)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(33628474 558144514)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(212802006 353386020)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(901307256 39143977)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(70870451 206374045)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(240880214 696939443)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(822615542 296669638)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(452769551 625489999)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(609104858 606565210)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(177213669 851312285)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(143654501 730691787)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(658472325 838260052)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(188164520 646358878)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(630993781 786764883)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(496793334 223062055)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(727354258 197498696)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(618432704 760982731)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(755643210 831234710)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(114368751 656950466)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(870378686 185239202)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(863324511 111258900)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(882178645 685940052)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(407928538 334948195)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(311430051 17033395)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(941513405 488643719)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(868345680 85167906)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(219335507 526818004)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(923427958 407500026)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(173176882 554421738)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(194264908 669970217)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(777483793 921619165)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(867468912 395916497)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(682601897 623112122)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(227151206 796970647)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(280062588 97529892)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(982209849 143387099)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(208788792 864388493)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(829327151 616717329)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(199336688 140757201)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(633750724 140850093)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(629400920 502096404)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(226017998 848736426)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(28914408 149445955)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(256236452 202091290)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(703867693 450501360)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(872061506 481351486)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(372120524 739530418)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(877267982 54722420)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(362642540 104419188)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(851693067 642705127)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(201949080 833902916)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(786092225 410737872)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(698291409 615419376)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(27455201 897628096)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(756176576 661205925)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(38478189 385577496)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(163302328 264496186)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(234313922 192216735)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(413942141 490550373)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(394308025 117809834)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(941051732 266369530)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(599161319 313172256)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(5899948 476429301)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(367894677 368542487)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(580848489 219587743)')); +INSERT INTO t1 VALUES(GeomFromText('POINT(11247614 782797569)')); +drop table t1; +create table t1 select GeomFromWKB(POINT(1,3)); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `GeomFromWKB(POINT(1,3))` geometry DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t1; +CREATE TABLE `t1` (`object_id` bigint(20) unsigned NOT NULL default '0', `geo` +geometry NOT NULL default '') ENGINE=MyISAM ; +insert into t1 values ('85984',GeomFromText('MULTIPOLYGON(((-115.006363 +36.305435,-114.992394 36.305202,-114.991219 36.305975,-114.991163 +36.306845,-114.989432 36.309452,-114.978275 36.312642,-114.977363 +36.311978,-114.975327 36.312344,-114.96502 36.31597,-114.963364 +36.313629,-114.961723 36.313721,-114.956398 36.316057,-114.951882 +36.320979,-114.947073 36.323475,-114.945207 36.326451,-114.945207 +36.326451,-114.944132 36.326061,-114.94003 36.326588,-114.924017 +36.334484,-114.923281 36.334146,-114.92564 36.331504,-114.94072 +36.319282,-114.945348 36.314812,-114.948091 36.314762,-114.951755 +36.316211,-114.952446 36.313883,-114.952644 36.309488,-114.944725 +36.313083,-114.93706 36.32043,-114.932478 36.323497,-114.924556 +36.327708,-114.922608 36.329715,-114.92009 36.328695,-114.912105 +36.323566,-114.901647 36.317952,-114.897436 36.313968,-114.895344 +36.309573,-114.891699 36.304398,-114.890569 36.303551,-114.886356 +36.302702,-114.885141 36.301351,-114.885709 36.297391,-114.892499 +36.290893,-114.902142 36.288974,-114.904941 36.288838,-114.905308 +36.289845,-114.906325 36.290395,-114.909916 36.289549,-114.914527 +36.287535,-114.918797 36.284423,-114.922982 36.279731,-114.924113 +36.277282,-114.924057 36.275817,-114.927733 36.27053,-114.929354 +36.269029,-114.929354 36.269029,-114.950856 36.268715,-114.950768 +36.264324,-114.960206 36.264293,-114.960301 36.268943,-115.006662 +36.268929,-115.008583 36.265619,-115.00665 36.264247,-115.006659 +36.246873,-115.006659 36.246873,-115.006838 36.247697,-115.010764 +36.247774,-115.015609 36.25113,-115.015765 36.254505,-115.029517 +36.254619,-115.038573 36.249317,-115.038573 36.249317,-115.023403 +36.25841,-115.023873 36.258994,-115.031845 36.259829,-115.03183 +36.261053,-115.025561 36.261095,-115.036417 36.274632,-115.033729 +36.276041,-115.032217 36.274851,-115.029845 36.273959,-115.029934 +36.274966,-115.025763 36.274896,-115.025406 36.281044,-115.028731 +36.284471,-115.036497 36.290377,-115.042071 36.291039,-115.026759 +36.298478,-115.008995 36.301966,-115.006363 36.305435),(-115.079835 +36.244369,-115.079735 36.260186,-115.076435 36.262369,-115.069758 +36.265,-115.070235 36.268757,-115.064542 36.268655,-115.061843 +36.269857,-115.062676 36.270693,-115.06305 36.272344,-115.059051 +36.281023,-115.05918 36.283008,-115.060591 36.285246,-115.061913 +36.290022,-115.062499 36.306353,-115.062499 36.306353,-115.060918 +36.30642,-115.06112 36.289779,-115.05713 36.2825,-115.057314 +36.279446,-115.060779 36.274659,-115.061366 36.27209,-115.057858 +36.26557,-115.055805 36.262883,-115.054688 36.262874,-115.047335 +36.25037,-115.044234 36.24637,-115.052434 36.24047,-115.061734 +36.23507,-115.061934 36.22677,-115.061934 36.22677,-115.061491 +36.225267,-115.062024 36.218194,-115.060134 36.218278,-115.060133 +36.210771,-115.057833 36.210771,-115.057433 36.196271,-115.062233 +36.196271,-115.062233 36.190371,-115.062233 36.190371,-115.065533 +36.190371,-115.071333 36.188571,-115.098331 36.188275,-115.098331 +36.188275,-115.098435 36.237569,-115.097535 36.240369,-115.097535 +36.240369,-115.093235 36.240369,-115.089135 36.240469,-115.083135 +36.240569,-115.083135 36.240569,-115.079835 +36.244369)))')),('85998',GeomFromText('MULTIPOLYGON(((-115.333107 +36.264587,-115.333168 36.280638,-115.333168 36.280638,-115.32226 +36.280643,-115.322538 36.274311,-115.327222 36.274258,-115.32733 +36.263026,-115.330675 36.262984,-115.332132 36.264673,-115.333107 +36.264587),(-115.247239 36.247066,-115.247438 36.218267,-115.247438 +36.218267,-115.278525 36.219263,-115.278525 36.219263,-115.301545 +36.219559,-115.332748 36.219197,-115.332757 36.220041,-115.332757 +36.220041,-115.332895 36.233514,-115.349023 36.233479,-115.351489 +36.234475,-115.353681 36.237021,-115.357106 36.239789,-115.36519 +36.243331,-115.368156 36.243487,-115.367389 36.244902,-115.364553 +36.246014,-115.359219 36.24616,-115.356186 36.248025,-115.353347 +36.248004,-115.350813 36.249507,-115.339673 36.25387,-115.333069 +36.255018,-115.333069 36.255018,-115.333042 36.247767,-115.279039 +36.248666,-115.263639 36.247466,-115.263839 36.252766,-115.261439 +36.252666,-115.261439 36.247366,-115.247239 36.247066)))')); +select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from +t1 where object_id=85998; +object_id geometrytype(geo) ISSIMPLE(GEO) ASTEXT(centroid(geo)) +85998 MULTIPOLYGON 1 POINT(115.2970604672862 -36.23335610879993) +select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from +t1 where object_id=85984; +object_id geometrytype(geo) ISSIMPLE(GEO) ASTEXT(centroid(geo)) +85984 MULTIPOLYGON 1 POINT(-114.86854472054372 36.34725218253213) +drop table t1; +create table t1 (fl geometry not null); +insert into t1 values (1); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +insert into t1 values (1.11); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +insert into t1 values ("qwerty"); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +insert into t1 values (pointfromtext('point(1,1)')); +ERROR 23000: Column 'fl' cannot be null +drop table t1; +select (asWKT(geomfromwkb((0x000000000140240000000000004024000000000000)))); +(asWKT(geomfromwkb((0x000000000140240000000000004024000000000000)))) +POINT(10 10) +select (asWKT(geomfromwkb((0x010100000000000000000024400000000000002440)))); +(asWKT(geomfromwkb((0x010100000000000000000024400000000000002440)))) +POINT(10 10) +create table t1 (g GEOMETRY); +select * from t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def test t1 t1 g g 255 4294967295 0 Y 144 0 63 +g +select asbinary(g) from t1; +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def asbinary(g) 251 4294967295 0 Y 128 0 63 +asbinary(g) +drop table t1; +create table t1 (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b)); +alter table t1 disable keys; +load data infile '../../std_data/bad_gis_data.dat' into table t1; +ERROR 22004: Column set to default value; NULL supplied to NOT NULL column 'b' at row 1 +alter table t1 enable keys; +drop table t1; +create table t1 (a int, b blob); +insert into t1 values (1, ''), (2, NULL), (3, '1'); +select * from t1; +a b +1 +2 NULL +3 1 +select +geometryfromtext(b) IS NULL, geometryfromwkb(b) IS NULL, astext(b) IS NULL, +aswkb(b) IS NULL, geometrytype(b) IS NULL, centroid(b) IS NULL, +envelope(b) IS NULL, startpoint(b) IS NULL, endpoint(b) IS NULL, +exteriorring(b) IS NULL, pointn(b, 1) IS NULL, geometryn(b, 1) IS NULL, +interiorringn(b, 1) IS NULL, multipoint(b) IS NULL, isempty(b) IS NULL, +issimple(b) IS NULL, isclosed(b) IS NULL, dimension(b) IS NULL, +numgeometries(b) IS NULL, numinteriorrings(b) IS NULL, numpoints(b) IS NULL, +area(b) IS NULL, glength(b) IS NULL, srid(b) IS NULL, x(b) IS NULL, +y(b) IS NULL +from t1; +ERROR 22007: Illegal non geometric '`test`.`t1`.`b`' value found during parsing +select +within(b, b) IS NULL, contains(b, b) IS NULL, overlaps(b, b) IS NULL, +equals(b, b) IS NULL, disjoint(b, b) IS NULL, touches(b, b) IS NULL, +intersects(b, b) IS NULL, crosses(b, b) IS NULL +from t1; +within(b, b) IS NULL contains(b, b) IS NULL overlaps(b, b) IS NULL equals(b, b) IS NULL disjoint(b, b) IS NULL touches(b, b) IS NULL intersects(b, b) IS NULL crosses(b, b) IS NULL +1 1 1 1 1 1 1 1 +1 1 1 1 1 1 1 1 +1 1 1 1 1 1 1 1 +select +point(b, b) IS NULL, linestring(b) IS NULL, polygon(b) IS NULL, multipoint(b) IS NULL, +multilinestring(b) IS NULL, multipolygon(b) IS NULL, +geometrycollection(b) IS NULL +from t1; +ERROR 22007: Illegal non geometric '`test`.`t1`.`b`' value found during parsing +drop table t1; +CREATE TABLE t1(a POINT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +a +NULL +DROP TABLE t1; +CREATE TABLE `t1` ( `col9` set('a'), `col89` date); +INSERT INTO `t1` VALUES ('','0000-00-00'); +select geomfromtext(col9,col89) as a from t1; +a +NULL +DROP TABLE t1; +CREATE TABLE t1 ( +geomdata polygon NOT NULL, +SPATIAL KEY index_geom (geomdata) +) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; +CREATE TABLE t2 ( +geomdata polygon NOT NULL, +SPATIAL KEY index_geom (geomdata) +) ENGINE=MyISAM DEFAULT CHARSET=latin2 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED; +CREATE TABLE t3 +select +aswkb(ws.geomdata) AS geomdatawkb +from +t1 ws +union +select +aswkb(ws.geomdata) AS geomdatawkb +from +t2 ws; +describe t3; +Field Type Null Key Default Extra +geomdatawkb longblob YES NULL +drop table t1; +drop table t2; +drop table t3; +create table t1(col1 geometry default null,col15 geometrycollection not +null,spatial index(col15),index(col1(15)))engine=myisam; +insert into t1 set col15 = GeomFromText('POINT(6 5)'); +insert into t1 set col15 = GeomFromText('POINT(6 5)'); +check table t1 extended; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +End of 4.1 tests +create table t1 (s1 geometry not null,s2 char(100)); +create trigger t1_bu before update on t1 for each row set new.s1 = null; +insert into t1 values (null,null); +ERROR 23000: Column 's1' cannot be null +drop table t1; +drop procedure if exists fn3; +create function fn3 () returns point deterministic return GeomFromText("point(1 1)"); +show create function fn3; +Function sql_mode Create Function character_set_client collation_connection Database Collation +fn3 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `fn3`() RETURNS point + DETERMINISTIC +return GeomFromText("point(1 1)") latin1 latin1_swedish_ci latin1_swedish_ci +select astext(fn3()); +astext(fn3()) +POINT(1 1) +drop function fn3; +create table t1(pt POINT); +alter table t1 add primary key pti(pt); +Warnings: +Warning 1280 Name 'pti' ignored for PRIMARY key. +drop table t1; +create table t1(pt GEOMETRY); +alter table t1 add primary key pti(pt); +ERROR 42000: BLOB/TEXT column 'pt' used in key specification without a key length +alter table t1 add primary key pti(pt(20)); +Warnings: +Warning 1280 Name 'pti' ignored for PRIMARY key. +drop table t1; +create table t1 select GeomFromText('point(1 1)'); +desc t1; +Field Type Null Key Default Extra +GeomFromText('point(1 1)') geometry YES NULL +drop table t1; +create table t1 (g geometry not null); +insert into t1 values(default); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +drop table t1; +CREATE TABLE t1 (a GEOMETRY); +CREATE VIEW v1 AS SELECT GeomFromwkb(ASBINARY(a)) FROM t1; +CREATE VIEW v2 AS SELECT a FROM t1; +DESCRIBE v1; +Field Type Null Key Default Extra +GeomFromwkb(ASBINARY(a)) geometry YES NULL +DESCRIBE v2; +Field Type Null Key Default Extra +a geometry YES NULL +DROP VIEW v1,v2; +DROP TABLE t1; +create table t1 (name VARCHAR(100), square GEOMETRY); +INSERT INTO t1 VALUES("center", GeomFromText('POLYGON (( 0 0, 0 2, 2 2, 2 0, 0 0))')); +INSERT INTO t1 VALUES("small", GeomFromText('POLYGON (( 0 0, 0 1, 1 1, 1 0, 0 0))')); +INSERT INTO t1 VALUES("big", GeomFromText('POLYGON (( 0 0, 0 3, 3 3, 3 0, 0 0))')); +INSERT INTO t1 VALUES("up", GeomFromText('POLYGON (( 0 1, 0 3, 2 3, 2 1, 0 1))')); +INSERT INTO t1 VALUES("up2", GeomFromText('POLYGON (( 0 2, 0 4, 2 4, 2 2, 0 2))')); +INSERT INTO t1 VALUES("up3", GeomFromText('POLYGON (( 0 3, 0 5, 2 5, 2 3, 0 3))')); +INSERT INTO t1 VALUES("down", GeomFromText('POLYGON (( 0 -1, 0 1, 2 1, 2 -1, 0 -1))')); +INSERT INTO t1 VALUES("down2", GeomFromText('POLYGON (( 0 -2, 0 0, 2 0, 2 -2, 0 -2))')); +INSERT INTO t1 VALUES("down3", GeomFromText('POLYGON (( 0 -3, 0 -1, 2 -1, 2 -3, 0 -3))')); +INSERT INTO t1 VALUES("right", GeomFromText('POLYGON (( 1 0, 1 2, 3 2, 3 0, 1 0))')); +INSERT INTO t1 VALUES("right2", GeomFromText('POLYGON (( 2 0, 2 2, 4 2, 4 0, 2 0))')); +INSERT INTO t1 VALUES("right3", GeomFromText('POLYGON (( 3 0, 3 2, 5 2, 5 0, 3 0))')); +INSERT INTO t1 VALUES("left", GeomFromText('POLYGON (( -1 0, -1 2, 1 2, 1 0, -1 0))')); +INSERT INTO t1 VALUES("left2", GeomFromText('POLYGON (( -2 0, -2 2, 0 2, 0 0, -2 0))')); +INSERT INTO t1 VALUES("left3", GeomFromText('POLYGON (( -3 0, -3 2, -1 2, -1 0, -3 0))')); +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrcontains FROM t1 a1 JOIN t1 a2 ON MBRContains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +mbrcontains +center,small +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrdisjoint FROM t1 a1 JOIN t1 a2 ON MBRDisjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +mbrdisjoint +down3,left3,right3,up3 +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrequal FROM t1 a1 JOIN t1 a2 ON MBREqual( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +mbrequal +center +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrintersect FROM t1 a1 JOIN t1 a2 ON MBRIntersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +mbrintersect +big,center,down,down2,left,left2,right,right2,small,up,up2 +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbroverlaps FROM t1 a1 JOIN t1 a2 ON MBROverlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +mbroverlaps +down,left,right,up +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrtouches FROM t1 a1 JOIN t1 a2 ON MBRTouches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +mbrtouches +down2,left2,right2,up2 +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS mbrwithin FROM t1 a1 JOIN t1 a2 ON MBRWithin( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +mbrwithin +big,center +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS contains FROM t1 a1 JOIN t1 a2 ON Contains( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +contains +center,small +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS disjoint FROM t1 a1 JOIN t1 a2 ON Disjoint( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +disjoint +down3,left3,right3,up3 +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS equals FROM t1 a1 JOIN t1 a2 ON Equals( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +equals +center +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS intersects FROM t1 a1 JOIN t1 a2 ON Intersects( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +intersects +big,center,down,down2,left,left2,right,right2,small,up,up2 +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS overlaps FROM t1 a1 JOIN t1 a2 ON Overlaps( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +overlaps +down,left,right,up +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS touches FROM t1 a1 JOIN t1 a2 ON Touches( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +touches +down2,left2,right2,up2 +SELECT GROUP_CONCAT(a2.name ORDER BY a2.name) AS within FROM t1 a1 JOIN t1 a2 ON Within( a1.square, a2.square) WHERE a1.name = "center" GROUP BY a1.name; +within +big,center +SET @vert1 = GeomFromText('POLYGON ((0 -2, 0 2, 0 -2))'); +SET @horiz1 = GeomFromText('POLYGON ((-2 0, 2 0, -2 0))'); +SET @horiz2 = GeomFromText('POLYGON ((-1 0, 3 0, -1 0))'); +SET @horiz3 = GeomFromText('POLYGON ((2 0, 3 0, 2 0))'); +SET @point1 = GeomFromText('POLYGON ((0 0))'); +SET @point2 = GeomFromText('POLYGON ((-2 0))'); +SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @vert1) GROUP BY a1.name; +overlaps +SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name; +overlaps +SELECT Overlaps(@horiz1, @vert1) FROM DUAL; +Overlaps(@horiz1, @vert1) +0 +SELECT Overlaps(@horiz1, @horiz2) FROM DUAL; +Overlaps(@horiz1, @horiz2) +1 +SELECT Overlaps(@horiz1, @horiz3) FROM DUAL; +Overlaps(@horiz1, @horiz3) +0 +SELECT Overlaps(@horiz1, @point1) FROM DUAL; +Overlaps(@horiz1, @point1) +0 +SELECT Overlaps(@horiz1, @point2) FROM DUAL; +Overlaps(@horiz1, @point2) +0 +DROP TABLE t1; +create table t1(f1 geometry, f2 linestring, f3 linestring); +select f1 from t1 union select f1 from t1; +f1 +insert into t1 (f2,f3) values (GeomFromText('LINESTRING(1 1, 2 2)'), +GeomFromText('LINESTRING(0 0,1 1,2 2)')); +select AsText(f2),AsText(f3) from t1; +AsText(f2) AsText(f3) +LINESTRING(1 1,2 2) LINESTRING(0 0,1 1,2 2) +select AsText(a) from (select f2 as a from t1 union select f3 from t1) t; +AsText(a) +LINESTRING(1 1,2 2) +LINESTRING(0 0,1 1,2 2) +create table t2 as select f2 as a from t1 union select f3 from t1; +desc t2; +Field Type Null Key Default Extra +a linestring YES NULL +select AsText(a) from t2; +AsText(a) +LINESTRING(1 1,2 2) +LINESTRING(0 0,1 1,2 2) +drop table t1, t2; +SELECT 1; +1 +1 +CREATE TABLE t1 (p POINT); +CREATE TABLE t2 (p POINT, INDEX(p)); +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +1 +EXPLAIN +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system p NULL NULL NULL 1 +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +1 +INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)')); +INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)')); +EXPLAIN +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +2 +EXPLAIN +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref p p 28 const # Using where +SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +2 +EXPLAIN +SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); +COUNT(*) +2 +DROP TABLE t1, t2; +End of 5.0 tests +# +# Test for bug #58650 "Failing assertion: primary_key_no == -1 || +# primary_key_no == 0". +# +drop table if exists t1; +# The minimal test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a)); +drop table t1; +# The original test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12))); +create unique index a on t1(a); +drop table t1; +create table `t1` (`col002` point)engine=myisam; +insert into t1 values (),(),(); +select min(`col002`) from t1 union select `col002` from t1; +min(`col002`) +NULL +drop table t1; +# +# Bug #47780: crash when comparing GIS items from subquery +# +CREATE TABLE t1(a INT, b MULTIPOLYGON); +INSERT INTO t1 VALUES +(0, +GEOMFROMTEXT( +'multipolygon(((1 2,3 4,5 6,7 8,9 8),(7 6,5 4,3 2,1 2,3 4)))')); +# must not crash +SELECT 1 FROM t1 WHERE a <> (SELECT GEOMETRYCOLLECTIONFROMWKB(b) FROM t1); +ERROR HY000: Illegal parameter data types int and geometry for operation '<>' +DROP TABLE t1; +# +# Bug #49250 : spatial btree index corruption and crash +# Part one : spatial syntax check +# +CREATE TABLE t1(col1 MULTIPOLYGON NOT NULL, +SPATIAL INDEX USING BTREE (col1)); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE (col1))' at line 2 +CREATE TABLE t2(col1 MULTIPOLYGON NOT NULL); +CREATE SPATIAL INDEX USING BTREE ON t2(col); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE ON t2(col)' at line 1 +ALTER TABLE t2 ADD SPATIAL INDEX USING BTREE (col1); +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USING BTREE (col1)' at line 1 +DROP TABLE t2; +End of 5.0 tests +create table t1 (f1 tinyint(1), f2 char(1), f3 varchar(1), f4 geometry, f5 datetime); +create view v1 as select * from t1; +desc v1; +Field Type Null Key Default Extra +f1 tinyint(1) YES NULL +f2 char(1) YES NULL +f3 varchar(1) YES NULL +f4 geometry YES NULL +f5 datetime YES NULL +drop view v1; +drop table t1; +SELECT MultiPoint(12345,''); +ERROR HY000: Illegal parameter data type int for operation 'geometrycollection' +SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b)); +ERROR HY000: Illegal parameter data type varchar for operation 'geometrycollection' +# +# BUG#51875: crash when loading data into geometry function polyfromwkb +# +SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; +SET @a=POLYFROMWKB(@a); +SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440; +SET @a=POLYFROMWKB(@a); +create table t1(a geometry NOT NULL)engine=myisam; +insert into t1 values (geomfromtext("point(0 1)")); +insert into t1 values (geomfromtext("point(1 0)")); +select * from (select polygon(t1.a) as p from t1 order by t1.a) d; +p +NULL +NULL +drop table t1; +# +# Test for bug #59888 "debug assertion when attempt to create spatial index +# on char > 31 bytes". +# +create table t1(a char(32) not null) engine=myisam; +create spatial index i on t1 (a); +ERROR HY000: Incorrect arguments to SPATIAL INDEX +drop table t1; +CREATE TABLE t0 (a BINARY(32) NOT NULL); +CREATE SPATIAL INDEX i on t0 (a); +ERROR HY000: Incorrect arguments to SPATIAL INDEX +INSERT INTO t0 VALUES (1); +CREATE TABLE t1( +col0 BINARY NOT NULL, +col2 TIMESTAMP, +SPATIAL INDEX i1 (col0) +) ENGINE=MyISAM; +ERROR HY000: Incorrect arguments to SPATIAL INDEX +CREATE TABLE t1 ( +col0 BINARY NOT NULL, +col2 TIMESTAMP +) ENGINE=MyISAM; +CREATE SPATIAL INDEX idx0 ON t1(col0); +ERROR HY000: Incorrect arguments to SPATIAL INDEX +ALTER TABLE t1 ADD SPATIAL INDEX i1 (col0); +ERROR HY000: Incorrect arguments to SPATIAL INDEX +CREATE TABLE t2 ( +col0 INTEGER NOT NULL, +col1 POINT, +col2 POINT +); +CREATE SPATIAL INDEX idx0 ON t2 (col1, col2); +ERROR HY000: Incorrect arguments to SPATIAL INDEX +CREATE TABLE t3 ( +col0 INTEGER NOT NULL, +col1 POINT, +col2 LINESTRING, +SPATIAL INDEX i1 (col1, col2) +); +ERROR HY000: Incorrect arguments to SPATIAL INDEX +DROP TABLE t0, t1, t2; +# +# BUG#12414917 - ISCLOSED() CRASHES ON 64-BIT BUILDS +# +SELECT ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20))); +ISCLOSED(CONVERT(CONCAT(' ', 0x2), BINARY(20))) +-1 +# +# BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN +# GEOMETRY FUNCTION ARGUMENTS +# +SELECT GEOMETRYCOLLECTION((SELECT @@OLD)); +ERROR HY000: Illegal parameter data type bigint for operation 'geometrycollection' +# +# MDEV-4252 geometry query crashes server +# +select astext(0x0100000000030000000100000000000010); +astext(0x0100000000030000000100000000000010) +NULL +select astext(st_centroid(0x0100000000030000000100000000000010)); +astext(st_centroid(0x0100000000030000000100000000000010)) +NULL +select astext(st_exteriorring(0x0100000000030000000100000000000010)); +astext(st_exteriorring(0x0100000000030000000100000000000010)) +NULL +select envelope(0x0100000000030000000100000000000010); +envelope(0x0100000000030000000100000000000010) +NULL +select geometryn(0x0100000000070000000100000001030000000200000000000000ffff0000, 1); +geometryn(0x0100000000070000000100000001030000000200000000000000ffff0000, 1) +NULL +select geometryn(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1); +geometryn(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1) +NULL +# +# MDEV-4296 Assertion `n_linear_rings > 0' fails in Gis_polygon::centroid_xy +# +SELECT Centroid( AsBinary( LineString(Point(0,0), Point(0,0), Point(0,0) ))); +Centroid( AsBinary( LineString(Point(0,0), Point(0,0), Point(0,0) ))) +NULL +# +# MDEV-4295 Server crashes in get_point on a query with Area, AsBinary, MultiPoint +# +SELECT Area(AsBinary(MultiPoint(Point(0,9), Point(0,1), Point(2,2)))); +Area(AsBinary(MultiPoint(Point(0,9), Point(0,1), Point(2,2)))) +NULL +End of 5.1 tests +select ST_AREA(ST_GEOMCOLLFROMTEXT(' GEOMETRYCOLLECTION(LINESTRING(100 100, 31 10, 77 80), POLYGON((0 0,4 7,1 1,0 0)), POINT(20 20))')); +ST_AREA(ST_GEOMCOLLFROMTEXT(' GEOMETRYCOLLECTION(LINESTRING(100 100, 31 10, 77 80), POLYGON((0 0,4 7,1 1,0 0)), POINT(20 20))')) +1.5 +select ST_LENGTH(ST_GEOMCOLLFROMTEXT(' GEOMETRYCOLLECTION(LINESTRING(100 100, 100 30, 20 30), POINT(3 3), LINESTRING(20 20, 30 20))')); +ST_LENGTH(ST_GEOMCOLLFROMTEXT(' GEOMETRYCOLLECTION(LINESTRING(100 100, 100 30, 20 30), POINT(3 3), LINESTRING(20 20, 30 20))')) +160 +DROP DATABASE IF EXISTS gis_ogs; +CREATE DATABASE gis_ogs; +USE gis_ogs; +# +# C.3.3.1 Geometry types and functions schema construction +# +CREATE TABLE lakes ( +fid INTEGER NOT NULL PRIMARY KEY, +name CHARACTER VARYING(64), +shore POLYGON); +CREATE TABLE road_segments ( +fid INTEGER NOT NULL PRIMARY KEY, +name CHARACTER VARYING(64), +aliases CHARACTER VARYING(64), +num_lanes INTEGER, +centerline LINESTRING); +CREATE TABLE divided_routes ( +fid INTEGER NOT NULL PRIMARY KEY, +name CHARACTER VARYING(64), +num_lanes INTEGER, +centerlines MULTILINESTRING); +CREATE TABLE forests ( +fid INTEGER NOT NULL PRIMARY KEY, +name CHARACTER VARYING(64), +boundary MULTIPOLYGON); +CREATE TABLE bridges ( +fid INTEGER NOT NULL PRIMARY KEY, +name CHARACTER VARYING(64), +position POINT); +CREATE TABLE streams ( +fid INTEGER NOT NULL PRIMARY KEY, +name CHARACTER VARYING(64), +centerline LINESTRING); +CREATE TABLE buildings ( +fid INTEGER NOT NULL PRIMARY KEY, +address CHARACTER VARYING(64), +position POINT, +footprint POLYGON); +CREATE TABLE ponds ( +fid INTEGER NOT NULL PRIMARY KEY, +name CHARACTER VARYING(64), +type CHARACTER VARYING(64), +shores MULTIPOLYGON); +CREATE TABLE named_places ( +fid INTEGER NOT NULL PRIMARY KEY, +name CHARACTER VARYING(64), +boundary POLYGON); +CREATE TABLE map_neatlines ( +fid INTEGER NOT NULL PRIMARY KEY, +neatline POLYGON); +# +# C.3.3.2 Geometry types and functions schema data loading +# +# Lakes +INSERT INTO lakes VALUES ( +101, 'BLUE LAKE', +PolyFromText( +'POLYGON( +(52 18,66 23,73 9,48 6,52 18), +(59 18,67 18,67 13,59 13,59 18) +)', +101)); +# Road Segments +INSERT INTO road_segments VALUES(102, 'Route 5', NULL, 2, +LineFromText( +'LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,101)); +INSERT INTO road_segments VALUES(103, 'Route 5', 'Main Street', 4, +LineFromText( +'LINESTRING( 44 31, 56 34, 70 38 )' ,101)); +INSERT INTO road_segments VALUES(104, 'Route 5', NULL, 2, +LineFromText( +'LINESTRING( 70 38, 72 48 )' ,101)); +INSERT INTO road_segments VALUES(105, 'Main Street', NULL, 4, +LineFromText( +'LINESTRING( 70 38, 84 42 )' ,101)); +INSERT INTO road_segments VALUES(106, 'Dirt Road by Green Forest', NULL, +1, +LineFromText( +'LINESTRING( 28 26, 28 0 )',101)); +# DividedRoutes +INSERT INTO divided_routes VALUES(119, 'Route 75', 4, +MLineFromText( +'MULTILINESTRING((10 48,10 21,10 0), +(16 0,16 23,16 48))', 101)); +# Forests +INSERT INTO forests VALUES(109, 'Green Forest', +MPolyFromText( +'MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26), +(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))', +101)); +# Bridges +INSERT INTO bridges VALUES(110, 'Cam Bridge', PointFromText( +'POINT( 44 31 )', 101)); +# Streams +INSERT INTO streams VALUES(111, 'Cam Stream', +LineFromText( +'LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 )', 101)); +INSERT INTO streams VALUES(112, NULL, +LineFromText( +'LINESTRING( 76 0, 78 4, 73 9 )', 101)); +# Buildings +INSERT INTO buildings VALUES(113, '123 Main Street', +PointFromText( +'POINT( 52 30 )', 101), +PolyFromText( +'POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) )', 101)); +INSERT INTO buildings VALUES(114, '215 Main Street', +PointFromText( +'POINT( 64 33 )', 101), +PolyFromText( +'POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) )', 101)); +# Ponds +INSERT INTO ponds VALUES(120, NULL, 'Stock Pond', +MPolyFromText( +'MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ), +( ( 26 44, 26 40, 28 42, 26 44) ) )', 101)); +# Named Places +INSERT INTO named_places VALUES(117, 'Ashton', +PolyFromText( +'POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) )', 101)); +INSERT INTO named_places VALUES(118, 'Goose Island', +PolyFromText( +'POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )', 101)); +# Map Neatlines +INSERT INTO map_neatlines VALUES(115, +PolyFromText( +'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 101)); +# +# C.3.3.3 Geometry types and functions schema test queries + +# Conformance Item T6 +SELECT Dimension(shore) +FROM lakes +WHERE name = 'Blue Lake'; +Dimension(shore) +2 +# Conformance Item T7 +SELECT GeometryType(centerlines) +FROM divided_routes +WHERE name = 'Route 75'; +GeometryType(centerlines) +MULTILINESTRING +# Conformance Item T8 +SELECT AsText(boundary) +FROM named_places +WHERE name = 'Goose Island'; +AsText(boundary) +POLYGON((67 13,67 18,59 18,59 13,67 13)) +# Conformance Item T9 +SELECT AsText(PolyFromWKB(AsBinary(boundary),101)) +FROM named_places +WHERE name = 'Goose Island'; +AsText(PolyFromWKB(AsBinary(boundary),101)) +POLYGON((67 13,67 18,59 18,59 13,67 13)) +# Conformance Item T10 +SELECT SRID(boundary) +FROM named_places +WHERE name = 'Goose Island'; +SRID(boundary) +101 +# Conformance Item T11 +SELECT IsEmpty(centerline) +FROM road_segments +WHERE name = 'Route 5' +AND aliases = 'Main Street'; +IsEmpty(centerline) +0 +# Conformance Item T12 +SELECT IsSimple(shore) +FROM lakes +WHERE name = 'Blue Lake'; +IsSimple(shore) +1 +# Conformance Item T13 +SELECT AsText(ST_Boundary(boundary)) +FROM named_places +WHERE name = 'Goose Island'; +AsText(ST_Boundary(boundary)) +LINESTRING(67 13,67 18,59 18,59 13,67 13) +# Conformance Item T14 +SELECT AsText(Envelope(boundary)) +FROM named_places +WHERE name = 'Goose Island'; +AsText(Envelope(boundary)) +POLYGON((59 13,67 13,67 18,59 18,59 13)) +# Conformance Item T15 +SELECT X(position) +FROM bridges +WHERE name = 'Cam Bridge'; +X(position) +44 +# Conformance Item T16 +SELECT Y(position) +FROM bridges +WHERE name = 'Cam Bridge'; +Y(position) +31 +# Conformance Item T17 +SELECT AsText(StartPoint(centerline)) +FROM road_segments +WHERE fid = 102; +AsText(StartPoint(centerline)) +POINT(0 18) +# Conformance Item T18 +SELECT AsText(EndPoint(centerline)) +FROM road_segments +WHERE fid = 102; +AsText(EndPoint(centerline)) +POINT(44 31) +SELECT IsClosed(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) +FROM named_places +WHERE name = 'Goose Island'; +IsClosed(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) +1 +# Conformance Item T20 +SELECT IsRing(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) +FROM named_places +WHERE name = 'Goose Island'; +IsRing(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) +1 +# Conformance Item T21 +SELECT GLength(centerline) +FROM road_segments +WHERE fid = 106; +GLength(centerline) +26 +# Conformance Item T22 +SELECT NumPoints(centerline) +FROM road_segments +WHERE fid = 102; +NumPoints(centerline) +5 +# Conformance Item T23 +SELECT AsText(PointN(centerline, 1)) +FROM road_segments +WHERE fid = 102; +AsText(PointN(centerline, 1)) +POINT(0 18) +# Conformance Item T24 +SELECT AsText(Centroid(boundary)) +FROM named_places +WHERE name = 'Goose Island'; +AsText(Centroid(boundary)) +POINT(63 15.5) +SELECT ST_Contains(boundary, PointOnSurface(boundary)) +FROM named_places +WHERE name = 'Goose Island'; +ST_Contains(boundary, PointOnSurface(boundary)) +1 +# Conformance Item T26 +SELECT Area(boundary) +FROM named_places +WHERE name = 'Goose Island'; +Area(boundary) +40 +# Conformance Item T27 +SELECT AsText(ExteriorRing(shore)) +FROM lakes +WHERE name = 'Blue Lake'; +AsText(ExteriorRing(shore)) +LINESTRING(52 18,66 23,73 9,48 6,52 18) +# Conformance Item T28 +SELECT NumInteriorRings(shore) +FROM lakes +WHERE name = 'Blue Lake'; +NumInteriorRings(shore) +1 +# Conformance Item T29 +SELECT AsText(InteriorRingN(shore, 1)) +FROM lakes +WHERE name = 'Blue Lake'; +AsText(InteriorRingN(shore, 1)) +LINESTRING(59 18,67 18,67 13,59 13,59 18) +# Conformance Item T30 +SELECT NumGeometries(centerlines) +FROM divided_routes +WHERE name = 'Route 75'; +NumGeometries(centerlines) +2 +# Conformance Item T31 +SELECT AsText(GeometryN(centerlines, 2)) +FROM divided_routes +WHERE name = 'Route 75'; +AsText(GeometryN(centerlines, 2)) +LINESTRING(16 0,16 23,16 48) +# Conformance Item T32 +SELECT IsClosed(centerlines) +FROM divided_routes +WHERE name = 'Route 75'; +IsClosed(centerlines) +0 +# Conformance Item T33 +SELECT GLength(centerlines) +FROM divided_routes +WHERE name = 'Route 75'; +GLength(centerlines) +96 +# Conformance Item T34 +SELECT AsText(Centroid(shores)) +FROM ponds +WHERE fid = 120; +AsText(Centroid(shores)) +POINT(25 42) +# Conformance Item T35 +SELECT Contains(shores, PointOnSurface(shores)) +FROM ponds +WHERE fid = 120; +Contains(shores, PointOnSurface(shores)) +1 +# Conformance Item T36 +SELECT Area(shores) +FROM ponds +WHERE fid = 120; +Area(shores) +8 +# Conformance Item T37 +SELECT ST_Equals(boundary, +PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1)) +FROM named_places +WHERE name = 'Goose Island'; +ST_Equals(boundary, +PolyFromText('POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) )',1)) +1 +# Conformance Item T38 +SELECT ST_Disjoint(centerlines, boundary) +FROM divided_routes, named_places +WHERE divided_routes.name = 'Route 75' +AND named_places.name = 'Ashton'; +ST_Disjoint(centerlines, boundary) +1 +# Conformance Item T39 +SELECT ST_Touches(centerline, shore) +FROM streams, lakes +WHERE streams.name = 'Cam Stream' +AND lakes.name = 'Blue Lake'; +ST_Touches(centerline, shore) +1 +# Conformance Item T40 +SELECT ST_Within(footprint, boundary) +FROM named_places, buildings +WHERE named_places.name = 'Ashton' +AND buildings.address = '215 Main Street'; +ST_Within(footprint, boundary) +1 +# Conformance Item T41 +SELECT ST_Overlaps(forests.boundary, named_places.boundary) +FROM forests, named_places +WHERE forests.name = 'Green Forest' +AND named_places.name = 'Ashton'; +ST_Overlaps(forests.boundary, named_places.boundary) +1 +# Conformance Item T42 +SELECT Crosses(road_segments.centerline, divided_routes.centerlines) +FROM road_segments, divided_routes +WHERE road_segments.fid = 102 +AND divided_routes.name = 'Route 75'; +Crosses(road_segments.centerline, divided_routes.centerlines) +1 +# Conformance Item T43 +SELECT ST_Intersects(road_segments.centerline, divided_routes.centerlines) +FROM road_segments, divided_routes +WHERE road_segments.fid = 102 +AND divided_routes.name = 'Route 75'; +ST_Intersects(road_segments.centerline, divided_routes.centerlines) +1 +# Conformance Item T44 +SELECT ST_Contains(forests.boundary, named_places.boundary) +FROM forests, named_places +WHERE forests.name = 'Green Forest' +AND named_places.name = 'Ashton'; +ST_Contains(forests.boundary, named_places.boundary) +0 +# Conformance Item T45 +SELECT ST_Relate(forests.boundary, named_places.boundary, 'TTTTTTTTT') +FROM forests, named_places +WHERE forests.name = 'Green Forest' +AND named_places.name = 'Ashton'; +ST_Relate(forests.boundary, named_places.boundary, 'TTTTTTTTT') +1 +# Conformance Item T46 +SELECT ST_Distance(position, boundary) +FROM bridges, named_places +WHERE bridges.name = 'Cam Bridge' +AND named_places.name = 'Ashton'; +ST_Distance(position, boundary) +12 +# Conformance Item T47 +SELECT AsText(ST_Intersection(centerline, shore)) +FROM streams, lakes +WHERE streams.name = 'Cam Stream' +AND lakes.name = 'Blue Lake'; +AsText(ST_Intersection(centerline, shore)) +POINT(52 18) +# Conformance Item T48 +SELECT AsText(ST_Difference(named_places.boundary, forests.boundary)) +FROM named_places, forests +WHERE named_places.name = 'Ashton' +AND forests.name = 'Green Forest'; +AsText(ST_Difference(named_places.boundary, forests.boundary)) +POLYGON((56 34,62 48,84 48,84 42,56 34)) +SELECT AsText(ST_Union(shore, boundary)) +FROM lakes, named_places +WHERE lakes.name = 'Blue Lake' +AND named_places.name = 'Goose Island'; +AsText(ST_Union(shore, boundary)) +POLYGON((48 6,52 18,66 23,73 9,48 6)) +# Conformance Item T50 +SELECT AsText(ST_SymDifference(shore, boundary)) +FROM lakes, named_places +WHERE lakes.name = 'Blue Lake' +AND named_places.name = 'Ashton'; +AsText(ST_SymDifference(shore, boundary)) +MULTIPOLYGON(((48 6,52 18,66 23,73 9,48 6),(59 13,59 18,67 18,67 13,59 13)),((56 30,56 34,62 48,84 48,84 30,56 30))) +# Conformance Item T51 +SELECT count(*) +FROM buildings, bridges +WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1; +count(*) +1 +# Conformance Item T52 +SELECT AsText(ConvexHull(shore)) +FROM lakes +WHERE lakes.name = 'Blue Lake'; +AsText(ConvexHull(shore)) +POLYGON((48 6,52 18,66 23,73 9,48 6)) +DROP DATABASE gis_ogs; +USE test; +# +# 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 +# +# MDEV-4310 geometry function equals hangs forever. +# +create table t1(a geometry not null)engine=myisam; +insert into t1 values(geomfromtext("POINT(0 0)")); +insert into t1 values(geomfromtext("POINT(0 9.2233720368548e18)")); +insert into t1 values(geomfromtext("POINT(0 9.2233720368548e18)")); +select equals(`a`,convert(`a` using utf8)) from `t1`; +ERROR HY000: Illegal parameter data type longblob for operation 'st_equals' +select equals(`a`,left(`a`,23)) from `t1`; +equals(`a`,left(`a`,23)) +NULL +NULL +NULL +drop table t1; +# +# MDEV-6883 ST_WITHIN crashes server if (0,0) is matched to POLYGON((0 0)) +# +select st_within(GeomFromText('Polygon((0 0))'), Point(0,0)); +st_within(GeomFromText('Polygon((0 0))'), Point(0,0)) +1 +End of 5.3 tests +# +# Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE +# +CREATE TABLE g1 +(a geometry NOT NULL, UNIQUE KEY i (a(151))) engine=myisam; +INSERT INTO g1 VALUES (geomfromtext('point(1 1)')); +INSERT INTO g1 VALUES (geomfromtext('point(1 2)')); +FLUSH TABLES; +SELECT 1 FROM g1 +FORCE INDEX(i) WHERE a = date_sub(now(), interval 2808.4 year_month) +; +ERROR HY000: Illegal parameter data types geometry and datetime for operation '=' +DROP TABLE g1; +# +# Bug#13013970 MORE CRASHES IN FIELD_BLOB::GET_KEY_IMAGE +# +CREATE TABLE g1(a TEXT NOT NULL, KEY(a(255))); +INSERT INTO g1 VALUES ('a'),('a'); +SELECT 1 FROM g1 WHERE a >= ANY +(SELECT 1 FROM g1 WHERE a = geomfromtext('') OR a) ; +1 +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'a' +Warning 1292 Truncated incorrect DOUBLE value: 'a' +DROP TABLE g1; +# +# Bug#16451878 GEOMETRY QUERY CRASHES SERVER +# +# should not crash +SELECT ASTEXT(0x0100000000030000000100000000000010); +ASTEXT(0x0100000000030000000100000000000010) +NULL +#should not crash +SELECT ENVELOPE(0x0100000000030000000100000000000010); +ENVELOPE(0x0100000000030000000100000000000010) +NULL +#should not crash +SELECT +GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffff0000, 1); +GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffff0000, 1) +NULL +#should not crash +SELECT +GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1); +GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1) +NULL +# +# MDEV-3819 missing constraints for spatial column types +# +create table t1 (pt point); +insert into t1 values(Geomfromtext('POLYGON((1 1, 2 2, 2 1, 1 1))')); +ERROR 22007: Incorrect POINT value: 'POLYGON((1 1,2 2,2 1,1 1))' for column `test`.`t1`.`pt` at row 1 +drop table t1; +SELECT st_astext(ST_Buffer(ST_PolygonFromText('POLYGON((3 5, 2 4, 2 5, 3 5))'), -100)); +st_astext(ST_Buffer(ST_PolygonFromText('POLYGON((3 5, 2 4, 2 5, 3 5))'), -100)) +GEOMETRYCOLLECTION EMPTY +CREATE VIEW v1 AS SELECT POINT(1,1) AS p; +SHOW CREATE VIEW v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select point(1,1) AS `p` latin1 latin1_swedish_ci +SELECT ASTEXT(p) FROM v1; +ASTEXT(p) +POINT(1 1) +DROP VIEW v1; +# +# Start of 10.0 tests +# +# +# MDEV-12495 Conditional jump depends on uninitialised value for: SELECT NULL UNION geom_expression +# +SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1; +AsText(g) +NULL +POINT(1 1) +# +# MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery +# +SET @save_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='derived_merge=on'; +CREATE TABLE t1 (x INT, y INT); +INSERT INTO t1 VALUES(0,0); +SELECT LENGTH(t2) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +c2 +25 +SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +c2 +51 +SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub; +c2 +52 +DROP TABLE t1; +SET optimizer_switch=@save_optimizer_switch; +# +# MDEV-16995: ER_CANT_CREATE_GEOMETRY_OBJECT encountered for a query with +# optimizer_use_condition_selectivity>=3 +# +CREATE TABLE t1 (a POINT); +INSERT INTO t1 VALUES (POINT(1,1)),(POINT(1,2)),(POINT(1,3)); +set @save_use_stat_tables= @@use_stat_tables; +set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; +set @@use_stat_tables= PREFERABLY; +set @@optimizer_use_condition_selectivity=3; +SELECT COUNT(*) FROM t1 WHERE a IN ('test','test1'); +COUNT(*) +0 +set @@use_stat_tables= @save_use_stat_tables; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +drop table t1; +# +# End 10.0 tests +# +SHOW CREATE TABLE information_schema.geometry_columns; +Table Create Table +GEOMETRY_COLUMNS CREATE TEMPORARY TABLE `GEOMETRY_COLUMNS` ( + `F_TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', + `F_TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', + `F_TABLE_NAME` varchar(64) NOT NULL DEFAULT '', + `F_GEOMETRY_COLUMN` varchar(64) NOT NULL DEFAULT '', + `G_TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '', + `G_TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', + `G_TABLE_NAME` varchar(64) NOT NULL DEFAULT '', + `G_GEOMETRY_COLUMN` varchar(64) NOT NULL DEFAULT '', + `STORAGE_TYPE` tinyint(2) NOT NULL DEFAULT 0, + `GEOMETRY_TYPE` int(7) NOT NULL DEFAULT 0, + `COORD_DIMENSION` tinyint(2) NOT NULL DEFAULT 0, + `MAX_PPR` tinyint(2) NOT NULL DEFAULT 0, + `SRID` smallint(5) NOT NULL DEFAULT 0 +) ENGINE=MEMORY DEFAULT CHARSET=utf8 +SHOW CREATE TABLE information_schema.spatial_ref_sys; +Table Create Table +SPATIAL_REF_SYS CREATE TEMPORARY TABLE `SPATIAL_REF_SYS` ( + `SRID` smallint(5) NOT NULL DEFAULT 0, + `AUTH_NAME` varchar(512) NOT NULL DEFAULT '', + `AUTH_SRID` int(5) NOT NULL DEFAULT 0, + `SRTEXT` varchar(2048) NOT NULL DEFAULT '' +) ENGINE=MEMORY DEFAULT CHARSET=utf8 +create table t1(g GEOMETRY, pt POINT); +create table t2(g LINESTRING, pl POLYGON); +select * from information_schema.geometry_columns where f_table_schema='test'; +F_TABLE_CATALOG F_TABLE_SCHEMA F_TABLE_NAME F_GEOMETRY_COLUMN G_TABLE_CATALOG G_TABLE_SCHEMA G_TABLE_NAME G_GEOMETRY_COLUMN STORAGE_TYPE GEOMETRY_TYPE COORD_DIMENSION MAX_PPR SRID +def test t1 def test t1 g 1 0 2 0 0 +def test t1 def test t1 pt 1 1 2 0 0 +def test t2 def test t2 g 1 2 2 0 0 +def test t2 def test t2 pl 1 3 2 0 0 +drop table t1, t2; +10.1 tests +create table t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101, pt POINT(8,2), pg GEOMETRY REF_SYSTEM_ID=102); +SELECT SRID from information_schema.geometry_columns WHERE f_table_schema='test' and G_TABLE_NAME='t1'; +SRID +101 +0 +102 +drop table t1; +# Expect an int(1) column to be created +CREATE TABLE t1 AS SELECT CONTAINS(NULL, NULL); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `CONTAINS(NULL, NULL)` int(1) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MDEV-7334 valgrind warning "unitialized bytes" in 10.1. +# +CREATE TABLE t1 ( +gp point, +ln linestring, +pg polygon, +mp multipoint, +mln multilinestring, +mpg multipolygon, +gc geometrycollection, +gm geometry +); +ALTER TABLE t1 ADD fid INT NOT NULL; +select SRID from information_schema.geometry_columns where F_TABLE_NAME='t1'; +SRID +0 +0 +0 +0 +0 +0 +0 +0 +drop table t1; +# +# MDEV-7510 GIS: IsRing returns false for a primitive triangle. +# +select ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,0 0)')); +ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,0 0)')) +1 +select ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,-10 -10, 0 -10, 0 0)')); +ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,-10 -10, 0 -10, 0 0)')) +0 +# +# MDEV-7514 GIS: PointOnSurface returns NULL instead of the point. +# +SELECT ST_GEOMETRYTYPE(ST_PointOnSurface(ST_PolyFromText('POLYGON((-70.916 42.1002,-70.9468 42.0946,-70.9754 42.0875,-70.9749 42.0879,-70.9759 42.0897,-70.916 42.1002))'))); +ST_GEOMETRYTYPE(ST_PointOnSurface(ST_PolyFromText('POLYGON((-70.916 42.1002,-70.9468 42.0946,-70.9754 42.0875,-70.9749 42.0879,-70.9759 42.0897,-70.916 42.1002))'))) +NULL +# +# MDEV-7529 GIS: ST_Relate returns unexpected results for POINT relations +# +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'T*F**FFF*') AS equals; +equals +1 +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'T*****FF*') AS contains; +contains +1 +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'T*F**F***') AS within; +within +1 +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(1 1)'),'FF*FF****') as disjoint; +disjoint +1 +select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'FF*FF****') as disjoint; +disjoint +0 +# +# MDEV-7528 GIS: Functions return NULL instead of specified -1 for NULL arguments. +# +select ST_IsRing(NULL); +ST_IsRing(NULL) +-1 +# +# MDEV-8675 Different results of GIS functions on NULL vs NOT NULL columns +# +CREATE TABLE t1 (g1 GEOMETRY NOT NULL,g2 GEOMETRY NULL); +CREATE TABLE t2 AS SELECT WITHIN(g1,g1) as w1,WITHIN(g2,g2) AS w2 FROM t1; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `w1` int(1) DEFAULT NULL, + `w2` int(1) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1,t2; +CREATE TABLE t1 ( +id INT UNSIGNED NOT NULL AUTO_INCREMENT, +point_data POINT NOT NULL, +PRIMARY KEY (id), +KEY idx_point_data(point_data) +) ENGINE=MyISAM; +INSERT t1 (point_data) VALUES +(GeomFromText('Point(37.0248492 23.8512726)')), +(GeomFromText('Point(38.0248492 23.8512726)')); +SELECT id FROM t1 +WHERE ST_Contains(point_data, GeomFromText('Point(38.0248492 23.8512726)')); +id +2 +DROP TABLE t1; +# +# Start of 10.2 tests +# +create view v1 as select AsWKT(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))); +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select st_astext(geometrycollection(point(44,6),geometrycollection(point(3,6),point(7,9)))) AS `Name_exp_1` latin1 latin1_swedish_ci +select * from v1; +Name_exp_1 +GEOMETRYCOLLECTION(POINT(44 6),GEOMETRYCOLLECTION(POINT(3 6),POINT(7 9))) +drop view v1; +# +# MDEV-10134 Add full support for DEFAULT +# +CREATE TABLE t1 (a POINT, x DOUBLE DEFAULT x(a), y DOUBLE DEFAULT y(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` point DEFAULT NULL, + `x` double DEFAULT st_x(`a`), + `y` double DEFAULT st_y(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (Point(1,2)); +SELECT x,y FROM t1; +x y +1 2 +DROP TABLE t1; +CREATE TABLE t1 (g GEOMETRY, area DOUBLE DEFAULT ST_AREA(g)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` geometry DEFAULT NULL, + `area` double DEFAULT st_area(`g`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (g) VALUES (GeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0))')); +SELECT area FROM t1; +area +400 +DROP TABLE t1; +CREATE TABLE t1 (g GEOMETRY, length DOUBLE DEFAULT ST_LENGTH(g)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` geometry DEFAULT NULL, + `length` double DEFAULT st_length(`g`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (g) VALUES (GeomFromText('LINESTRING(0 0,20 0,20 20,0 20,0 0)')); +SELECT length FROM t1; +length +80 +DROP TABLE t1; +CREATE TABLE t1 (g POINT, distance DOUBLE DEFAULT ST_DISTANCE(g, POINT(0,0))); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` point DEFAULT NULL, + `distance` double DEFAULT st_distance(`g`,point(0,0)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (g) VALUES (Point(1,0)); +SELECT distance FROM t1; +distance +1 +DROP TABLE t1; +CREATE TABLE t1 (a TEXT, g GEOMETRY DEFAULT GeomFromText(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` text DEFAULT NULL, + `g` geometry DEFAULT st_geometryfromtext(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES ('point(1 1)'); +SELECT AsText(g) FROM t1; +AsText(g) +POINT(1 1) +DROP TABLE t1; +CREATE TABLE t1 (x INT, y INT, g GEOMETRY DEFAULT POINT(x,y)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) DEFAULT NULL, + `y` int(11) DEFAULT NULL, + `g` geometry DEFAULT point(`x`,`y`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (x,y) VALUES (10,20); +SELECT AsText(g) FROM t1; +AsText(g) +POINT(10 20) +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT PointN(a,2)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT st_pointn(`a`,2) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,2 2,3 3)')); +SELECT AsText(b) FROM t1; +AsText(b) +POINT(2 2) +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT StartPoint(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT st_startpoint(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,2 2,3 3)')); +SELECT AsText(b) FROM t1; +AsText(b) +POINT(1 1) +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c GEOMETRY DEFAULT GeometryCollection(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT NULL, + `c` geometry DEFAULT geometrycollection(`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a,b) VALUES (Point(1,1), Point(2,2)); +SELECT AsText(c) FROM t1; +AsText(c) +GEOMETRYCOLLECTION(POINT(1 1),POINT(2 2)) +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT GeomFromWKB(AsBinary(a),20)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT st_geometryfromwkb(st_aswkb(`a`),20) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('POINT(1 1)', 10)); +SELECT AsText(a), SRID(a), AsText(b), SRID(b) FROM t1; +AsText(a) SRID(a) AsText(b) SRID(b) +POINT(1 1) 10 POINT(1 1) 20 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT BOUNDARY(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT st_boundary(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); +SELECT AsText(b) FROM t1; +AsText(b) +LINESTRING(10 10,10 20,20 20,20 10,10 10) +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT BUFFER(a,10)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT st_buffer(`a`,10) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); +SELECT GeometryType(b) FROM t1; +GeometryType(b) +POLYGON +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT CENTROID(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT st_centroid(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); +SELECT AsText(b) FROM t1; +AsText(b) +POINT(15 15) +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT ENVELOPE(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT st_envelope(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,4 4)')); +SELECT AsText(b) FROM t1; +AsText(b) +POLYGON((1 1,4 1,4 4,1 4,1 1)) +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT PointOnSurface(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT st_pointonsurface(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))')); +SELECT GeometryType(b) FROM t1; +GeometryType(b) +POINT +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT Point(1,1), c GEOMETRY DEFAULT ST_UNION(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT point(1,1), + `c` geometry DEFAULT st_union(`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (Point(0,0)); +SELECT AsText(c) FROM t1; +AsText(c) +MULTIPOINT(0 0,1 1) +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b VARCHAR(20) DEFAULT GeometryType(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` varchar(20) DEFAULT st_geometrytype(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (Point(0, 0)); +SELECT b FROM t1; +b +POINT +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsSimple(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT st_issimple(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (Point(0, 0)); +SELECT b FROM t1; +b +1 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsEmpty(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT st_isempty(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (Point(0, 0)); +SELECT b FROM t1; +b +0 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsRing(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT st_isring(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('LineString(0 0,0 1,1 1,1 0,0 0)')); +SELECT b FROM t1; +b +1 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsClosed(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT st_isclosed(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('LineString(0 0,0 1,1 1,1 0,0 0)')); +SELECT b FROM t1; +b +1 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT Dimension(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT st_dimension(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (Buffer(Point(1,1),1)); +SELECT b FROM t1; +b +2 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumGeometries(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT st_numgeometries(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (ST_UNION(Point(1,1),Point(0,0))); +SELECT b FROM t1; +b +2 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumInteriorRings(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT st_numinteriorrings(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0),(1 1,1 2,2 2,2 1,1 1))')); +SELECT b FROM t1; +b +1 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumPoints(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT st_numpoints(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (LineString(Point(1,1),Point(0,0))); +SELECT b FROM t1; +b +2 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT SRID(a)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` int(11) DEFAULT srid(`a`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a) VALUES (GeomFromText('Point(1 1)', 100)); +SELECT b FROM t1; +b +100 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT MBRDisjoint(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT NULL, + `c` int(11) DEFAULT mbrdisjoint(`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1)); +SELECT c FROM t1; +c +0 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT ST_Disjoint(a,b)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT NULL, + `c` int(11) DEFAULT st_disjoint(`a`,`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1)); +SELECT c FROM t1; +c +0 +DROP TABLE t1; +CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT ST_Relate(a,b,'T*F**FFF*')); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` geometry DEFAULT NULL, + `b` geometry DEFAULT NULL, + `c` int(11) DEFAULT st_relate(`a`,`b`,'T*F**FFF*') +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1)); +SELECT c FROM t1; +c +1 +DROP TABLE t1; +create table t1 (p point default "qwer"); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +create table t1 (p point default 0); +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +create table t1 (p point not null default st_geometryfromtext('point 0)')); +ERROR 42000: Invalid default value for 'p' +create table t1 (p point not null default st_geometryfromtext('point(0 0)')); +insert into t1 values(default); +select st_astext(p) from t1; +st_astext(p) +POINT(0 0) +drop table t1; +# +# MDEV-21056 Memory leak after query with DEFAULT on a geometry field +# +CREATE TABLE t1 (f POINT DEFAULT ST_GEOMFROMTEXT('Point(0 0)')); +SELECT ST_GEOMFROMTEXT('Point(1 1)') IN ( DEFAULT( `f` ), ST_GEOMFROMTEXT('Point(2 2)') ) AS x FROM t1; +x +DROP TABLE t1; +# +# End of 10.2 tests +# +# +# MDEV-11478 Result data type aggregation for pluggable data types +# +CREATE PROCEDURE p2(query TEXT) +BEGIN +DECLARE errcount INT DEFAULT 0; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SET errcount = errcount+1; +#SHOW WARNINGS; +GET DIAGNOSTICS CONDITION 1 @p= MESSAGE_TEXT; +SELECT @p AS `ERROR: `; +END; +SELECT query AS ``; +EXECUTE IMMEDIATE query; +IF errcount = 0 +THEN +SHOW CREATE TABLE t2; +DROP TABLE t2; +END IF; +END; +$$ +CREATE PROCEDURE p1(query TEXT) +BEGIN +SELECT query AS `-------------------------------------`; +EXECUTE IMMEDIATE query; +CALL p2('CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1'); +CALL p2('CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1'); +CALL p2('CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1'); +CALL p2('CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1'); +CALL p2('CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1'); +DROP TABLE t1; +END; +$$ +------------------------------------- +CREATE TABLE t1 (a CHAR(10), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a VARCHAR(10), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TINYTEXT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TEXT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a MEDIUMTEXT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a LONGTEXT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `a` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TINYINT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a SMALLINT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a MEDIUMINT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a INT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a BIGINT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a FLOAT, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a DOUBLE, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a DECIMAL(10,2), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a BIT(8), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a TIME, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a DATE, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a DATETIME, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a TIMESTAMP, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a YEAR, b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'case' + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'coalesce' + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'if' + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'ifnull' + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'UNION' +# This creates BLOB with hybrid functions, but fails on error with UNION (MDEV-11458) +------------------------------------- +CREATE TABLE t1 (a ENUM(0x61), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types enum and geometry for operation 'UNION' +------------------------------------- +CREATE TABLE t1 (a SET(0x61), b Point) + +CREATE TABLE t2 AS SELECT CASE WHEN TRUE THEN a ELSE b END FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `CASE WHEN TRUE THEN a ELSE b END` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT COALESCE(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `COALESCE(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IF(TRUE,a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IF(TRUE,a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT IFNULL(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `IFNULL(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + +CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT b FROM t1 +ERROR: +Illegal parameter data types set and geometry for operation 'UNION' +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))` 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` point DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +# +# MDEV-11692 Comparison data type aggregation for pluggable data types +# +CREATE PROCEDURE p2(query TEXT) +BEGIN +DECLARE errcount INT DEFAULT 0; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SET errcount = errcount+1; +GET DIAGNOSTICS CONDITION 1 @p= MESSAGE_TEXT; +SELECT @p AS `ERROR: `; +END; +SELECT query AS ``; +EXECUTE IMMEDIATE query; +END; +$$ +CREATE PROCEDURE p1(query TEXT) +BEGIN +SELECT query AS `-------------------------------------`; +EXECUTE IMMEDIATE query; +CALL p2('SELECT a=b FROM t1'); +CALL p2('SELECT b=a FROM t1'); +CALL p2('SELECT a BETWEEN b AND c FROM t1'); +CALL p2('SELECT a IN (b,c) FROM t1'); +CALL p2('SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1'); +CALL p2('SELECT a=POINT(1,1) FROM t1'); +CALL p2('SELECT POINT(1,1)=a FROM t1'); +CALL p2('SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1'); +CALL p2('SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1'); +CALL p2('SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1'); +DROP TABLE t1; +END; +$$ +CALL p1('CREATE TABLE t1 (a CHAR(10), b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a CHAR(10), b Point, c Point) + +SELECT a=b FROM t1 +a=b + +SELECT b=a FROM t1 +b=a + +SELECT a BETWEEN b AND c FROM t1 +a BETWEEN b AND c + +SELECT a IN (b,c) FROM t1 +a IN (b,c) + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +CASE a WHEN b THEN "a" WHEN c THEN "b" END + +SELECT a=POINT(1,1) FROM t1 +a=POINT(1,1) + +SELECT POINT(1,1)=a FROM t1 +POINT(1,1)=a + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +a BETWEEN POINT(1,1) AND POINT(1,2) + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +a IN (POINT(1,1),POINT(1,2)) + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END +CALL p1('CREATE TABLE t1 (a VARCHAR(10), b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a VARCHAR(10), b Point, c Point) + +SELECT a=b FROM t1 +a=b + +SELECT b=a FROM t1 +b=a + +SELECT a BETWEEN b AND c FROM t1 +a BETWEEN b AND c + +SELECT a IN (b,c) FROM t1 +a IN (b,c) + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +CASE a WHEN b THEN "a" WHEN c THEN "b" END + +SELECT a=POINT(1,1) FROM t1 +a=POINT(1,1) + +SELECT POINT(1,1)=a FROM t1 +POINT(1,1)=a + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +a BETWEEN POINT(1,1) AND POINT(1,2) + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +a IN (POINT(1,1),POINT(1,2)) + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END +CALL p1('CREATE TABLE t1 (a TINYTEXT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a TINYTEXT, b Point, c Point) + +SELECT a=b FROM t1 +a=b + +SELECT b=a FROM t1 +b=a + +SELECT a BETWEEN b AND c FROM t1 +a BETWEEN b AND c + +SELECT a IN (b,c) FROM t1 +a IN (b,c) + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +CASE a WHEN b THEN "a" WHEN c THEN "b" END + +SELECT a=POINT(1,1) FROM t1 +a=POINT(1,1) + +SELECT POINT(1,1)=a FROM t1 +POINT(1,1)=a + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +a BETWEEN POINT(1,1) AND POINT(1,2) + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +a IN (POINT(1,1),POINT(1,2)) + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END +CALL p1('CREATE TABLE t1 (a TEXT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a TEXT, b Point, c Point) + +SELECT a=b FROM t1 +a=b + +SELECT b=a FROM t1 +b=a + +SELECT a BETWEEN b AND c FROM t1 +a BETWEEN b AND c + +SELECT a IN (b,c) FROM t1 +a IN (b,c) + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +CASE a WHEN b THEN "a" WHEN c THEN "b" END + +SELECT a=POINT(1,1) FROM t1 +a=POINT(1,1) + +SELECT POINT(1,1)=a FROM t1 +POINT(1,1)=a + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +a BETWEEN POINT(1,1) AND POINT(1,2) + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +a IN (POINT(1,1),POINT(1,2)) + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END +CALL p1('CREATE TABLE t1 (a MEDIUMTEXT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a MEDIUMTEXT, b Point, c Point) + +SELECT a=b FROM t1 +a=b + +SELECT b=a FROM t1 +b=a + +SELECT a BETWEEN b AND c FROM t1 +a BETWEEN b AND c + +SELECT a IN (b,c) FROM t1 +a IN (b,c) + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +CASE a WHEN b THEN "a" WHEN c THEN "b" END + +SELECT a=POINT(1,1) FROM t1 +a=POINT(1,1) + +SELECT POINT(1,1)=a FROM t1 +POINT(1,1)=a + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +a BETWEEN POINT(1,1) AND POINT(1,2) + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +a IN (POINT(1,1),POINT(1,2)) + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END +CALL p1('CREATE TABLE t1 (a LONGTEXT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a LONGTEXT, b Point, c Point) + +SELECT a=b FROM t1 +a=b + +SELECT b=a FROM t1 +b=a + +SELECT a BETWEEN b AND c FROM t1 +a BETWEEN b AND c + +SELECT a IN (b,c) FROM t1 +a IN (b,c) + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +CASE a WHEN b THEN "a" WHEN c THEN "b" END + +SELECT a=POINT(1,1) FROM t1 +a=POINT(1,1) + +SELECT POINT(1,1)=a FROM t1 +POINT(1,1)=a + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +a BETWEEN POINT(1,1) AND POINT(1,2) + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +a IN (POINT(1,1),POINT(1,2)) + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END +CALL p1('CREATE TABLE t1 (a ENUM("a","b"), b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a ENUM("a","b"), b Point, c Point) + +SELECT a=b FROM t1 +a=b + +SELECT b=a FROM t1 +b=a + +SELECT a BETWEEN b AND c FROM t1 +a BETWEEN b AND c + +SELECT a IN (b,c) FROM t1 +a IN (b,c) + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +CASE a WHEN b THEN "a" WHEN c THEN "b" END + +SELECT a=POINT(1,1) FROM t1 +a=POINT(1,1) + +SELECT POINT(1,1)=a FROM t1 +POINT(1,1)=a + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +a BETWEEN POINT(1,1) AND POINT(1,2) + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +a IN (POINT(1,1),POINT(1,2)) + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END +CALL p1('CREATE TABLE t1 (a TINYINT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a TINYINT, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and tinyint for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and tinyint for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a SMALLINT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a SMALLINT, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and smallint for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and smallint for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a MEDIUMINT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a MEDIUMINT, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and mediumint for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and mediumint for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a INT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a INT, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and int for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and int for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a BIGINT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a BIGINT, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and bigint for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and bigint for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a FLOAT, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a FLOAT, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and float for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and float for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a DOUBLE, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a DOUBLE, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and double for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and double for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a DECIMAL(10,2), b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a DECIMAL(10,2), b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and decimal for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and decimal for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a BIT(8), b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a BIT(8), b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and bit for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and bit for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a TIME, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a TIME, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and time for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and time for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a DATE, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a DATE, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and date for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and date for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a DATETIME, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a DATETIME, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and datetime for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and datetime for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a TIMESTAMP, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a TIMESTAMP, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and timestamp for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and timestamp for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a YEAR, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a YEAR, b Point, c Point) + +SELECT a=b FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation '=' + +SELECT b=a FROM t1 +ERROR: +Illegal parameter data types geometry and year for operation '=' + +SELECT a BETWEEN b AND c FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'between' + +SELECT a IN (b,c) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'in' + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'case..when' + +SELECT a=POINT(1,1) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation '=' + +SELECT POINT(1,1)=a FROM t1 +ERROR: +Illegal parameter data types geometry and year for operation '=' + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'between' + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'in' + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'case..when' +CALL p1('CREATE TABLE t1 (a Point, b Point, c Point)'); +------------------------------------- +CREATE TABLE t1 (a Point, b Point, c Point) + +SELECT a=b FROM t1 +a=b + +SELECT b=a FROM t1 +b=a + +SELECT a BETWEEN b AND c FROM t1 +a BETWEEN b AND c + +SELECT a IN (b,c) FROM t1 +a IN (b,c) + +SELECT CASE a WHEN b THEN "a" WHEN c THEN "b" END FROM t1 +CASE a WHEN b THEN "a" WHEN c THEN "b" END + +SELECT a=POINT(1,1) FROM t1 +a=POINT(1,1) + +SELECT POINT(1,1)=a FROM t1 +POINT(1,1)=a + +SELECT a BETWEEN POINT(1,1) AND POINT(1,2) FROM t1 +a BETWEEN POINT(1,1) AND POINT(1,2) + +SELECT a IN (POINT(1,1),POINT(1,2)) FROM t1 +a IN (POINT(1,1),POINT(1,2)) + +SELECT CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END FROM t1 +CASE a WHEN POINT(1,1) THEN "a" WHEN POINT(1,2) THEN "b" END +DROP PROCEDURE p1; +DROP PROCEDURE p2; +# +# MDEV-12506 Split Item_func_min_max::fix_length_and_dec() into methods in Type_handler +# +CREATE PROCEDURE p2(query TEXT) +BEGIN +DECLARE errcount INT DEFAULT 0; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SET errcount = errcount+1; +#SHOW WARNINGS; +GET DIAGNOSTICS CONDITION 1 @p= MESSAGE_TEXT; +SELECT @p AS `ERROR: `; +END; +SELECT query AS ``; +EXECUTE IMMEDIATE query; +IF errcount = 0 +THEN +SHOW CREATE TABLE t2; +DROP TABLE t2; +END IF; +END; +$$ +CREATE PROCEDURE p1(query TEXT) +BEGIN +SELECT query AS `-------------------------------------`; +EXECUTE IMMEDIATE query; +CALL p2('CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1'); +DROP TABLE t1; +END; +$$ +------------------------------------- +CREATE TABLE t1 (a CHAR(10), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a VARCHAR(10), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TINYTEXT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TEXT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a MEDIUMTEXT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a LONGTEXT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a TINYINT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types tinyint and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a SMALLINT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types smallint and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a MEDIUMINT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types mediumint and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a INT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types int and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a BIGINT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types bigint and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a FLOAT, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types float and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a DOUBLE, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types double and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a DECIMAL(10,2), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types decimal and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a BIT(8), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types bit and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a TIME, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types time and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a DATE, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types date and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a DATETIME, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types datetime and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a TIMESTAMP, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types timestamp and geometry for operation 'least' +------------------------------------- +CREATE TABLE t1 (a YEAR, b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +ERROR: +Illegal parameter data types year and geometry for operation 'least' +# This LEAST(ENUM,GEOMETRY) creates BLOB, but fails on error with UNION (see MDEV-12503) +------------------------------------- +CREATE TABLE t1 (a ENUM(0x61), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +------------------------------------- +CREATE TABLE t1 (a SET(0x61), b Point) + +CREATE TABLE t2 AS SELECT LEAST(a,b) FROM t1 +Table Create Table +t2 CREATE TABLE `t2` ( + `LEAST(a,b)` longblob DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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))` point DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +# +# MDEV-12001 Split Item_func_round::fix_length_and_dec to virtual methods in Type_handler +# +CREATE TABLE t1 (a GEOMETRY); +SELECT ROUND(a) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'round' +DROP TABLE t1; +SELECT ROUND(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'round' +# +# MDEV-12199 Split Item_func_{abs|neg|int_val}::fix_length_and_dec() into methods in Type_handler +# +CREATE TABLE t1 (a GEOMETRY); +SELECT -a FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation '-' +SELECT ABS(a) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'abs' +SELECT CEILING(a) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'ceiling' +SELECT FLOOR(a) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'floor' +SELECT -COALESCE(a) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation '-' +SELECT ABS(COALESCE(a)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'abs' +SELECT CEILING(COALESCE(a)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'ceiling' +SELECT FLOOR(COALESCE(a)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'floor' +DROP TABLE t1; +SELECT -POINT(1,1); +ERROR HY000: Illegal parameter data type geometry for operation '-' +SELECT ABS(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'abs' +SELECT CEILING(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'ceiling' +SELECT FLOOR(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'floor' +# +# MDEV-12239 Add Type_handler::Item_sum_{sum|avg|variance}_fix_length_and_dec() +# +CREATE TABLE t1 (a GEOMETRY); +SELECT SUM(POINT(1,1)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'sum' +SELECT SUM(a) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'sum' +SELECT SUM(COALESCE(a)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'sum' +SELECT AVG(POINT(1,1)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'avg' +SELECT AVG(a) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'avg' +SELECT AVG(COALESCE(a)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'avg' +SELECT VARIANCE(POINT(1,1)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'variance(' +SELECT VARIANCE(a) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'variance(' +SELECT VARIANCE(COALESCE(a)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'variance(' +SELECT STDDEV(POINT(1,1)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'std(' +SELECT STDDEV(a) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'std(' +SELECT STDDEV(COALESCE(a)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'std(' +DROP TABLE t1; +# +# MDEV-12303 Add Type_handler::Item_xxx_fix_length_and_dec() for CAST classes +# +CREATE TABLE t1 (a GEOMETRY); +SELECT CAST(POINT(1,1) AS SIGNED) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_signed' +SELECT CAST(POINT(1,1) AS UNSIGNED) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_unsigned' +SELECT CAST(POINT(1,1) AS FLOAT) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'float_typecast' +SELECT CAST(POINT(1,1) AS DOUBLE) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'double_typecast' +SELECT CAST(POINT(1,1) AS DECIMAL(10,1)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'decimal_typecast' +SELECT CAST(POINT(1,1) AS CHAR) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_char' +SELECT CAST(POINT(1,1) AS TIME) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_time' +SELECT CAST(POINT(1,1) AS DATE) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_date' +SELECT CAST(POINT(1,1) AS DATETIME) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_datetime' +SELECT CAST(a AS SIGNED) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_signed' +SELECT CAST(a AS UNSIGNED) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_unsigned' +SELECT CAST(a AS FLOAT) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'float_typecast' +SELECT CAST(a AS DOUBLE) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'double_typecast' +SELECT CAST(a AS DECIMAL(10,1)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'decimal_typecast' +SELECT CAST(a AS CHAR) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_char' +SELECT CAST(a AS TIME) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_time' +SELECT CAST(a AS DATE) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_date' +SELECT CAST(a AS DATETIME) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_datetime' +SELECT CAST(COALESCE(a) AS SIGNED) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_signed' +SELECT CAST(COALESCE(a) AS UNSIGNED) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_unsigned' +SELECT CAST(COALESCE(a) AS FLOAT) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'float_typecast' +SELECT CAST(COALESCE(a) AS DOUBLE) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'double_typecast' +SELECT CAST(COALESCE(a) AS DECIMAL(10,1)) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'decimal_typecast' +SELECT CAST(COALESCE(a) AS CHAR) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_char' +SELECT CAST(COALESCE(a) AS TIME) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_time' +SELECT CAST(COALESCE(a) AS DATE) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_date' +SELECT CAST(COALESCE(a) AS DATETIME) FROM t1; +ERROR HY000: Illegal parameter data type geometry for operation 'cast_as_datetime' +SELECT LENGTH(CAST(POINT(1,1) AS BINARY)) FROM t1; +LENGTH(CAST(POINT(1,1) AS BINARY)) +SELECT LENGTH(CAST(a AS BINARY)) FROM t1; +LENGTH(CAST(a AS BINARY)) +SELECT LENGTH(CAST(COALESCE(a) AS BINARY)) FROM t1; +LENGTH(CAST(COALESCE(a) AS BINARY)) +DROP TABLE t1; +# +# MDEV-12238 Add Type_handler::Item_func_{plus|minus|mul|div|mod}_fix_length_and_dec() +# +CREATE TABLE t1 (a GEOMETRY); +SELECT POINT(1,1) + 1; +ERROR HY000: Illegal parameter data types geometry and int for operation '+' +SELECT POINT(1,1) - 1; +ERROR HY000: Illegal parameter data types geometry and int for operation '-' +SELECT POINT(1,1) * 1; +ERROR HY000: Illegal parameter data types geometry and int for operation '*' +SELECT POINT(1,1) / 1; +ERROR HY000: Illegal parameter data types geometry and int for operation '/' +SELECT POINT(1,1) MOD 1; +ERROR HY000: Illegal parameter data types geometry and int for operation 'MOD' +SELECT 1 + POINT(1,1); +ERROR HY000: Illegal parameter data types int and geometry for operation '+' +SELECT 1 - POINT(1,1); +ERROR HY000: Illegal parameter data types int and geometry for operation '-' +SELECT 1 * POINT(1,1); +ERROR HY000: Illegal parameter data types int and geometry for operation '*' +SELECT 1 / POINT(1,1); +ERROR HY000: Illegal parameter data types int and geometry for operation '/' +SELECT 1 MOD POINT(1,1); +ERROR HY000: Illegal parameter data types int and geometry for operation 'MOD' +SELECT a + 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation '+' +SELECT a - 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation '-' +SELECT a * 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation '*' +SELECT a / 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation '/' +SELECT a MOD 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation 'MOD' +SELECT 1 + a FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation '+' +SELECT 1 - a FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation '-' +SELECT 1 * a FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation '*' +SELECT 1 / a FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation '/' +SELECT 1 MOD a FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation 'MOD' +SELECT COALESCE(a) + 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation '+' +SELECT COALESCE(a) - 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation '-' +SELECT COALESCE(a) * 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation '*' +SELECT COALESCE(a) / 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation '/' +SELECT COALESCE(a) MOD 1 FROM t1; +ERROR HY000: Illegal parameter data types geometry and int for operation 'MOD' +SELECT 1 + COALESCE(a) FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation '+' +SELECT 1 - COALESCE(a) FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation '-' +SELECT 1 * COALESCE(a) FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation '*' +SELECT 1 / COALESCE(a) FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation '/' +SELECT 1 MOD COALESCE(a) FROM t1; +ERROR HY000: Illegal parameter data types int and geometry for operation 'MOD' +DROP TABLE t1; +# +# MDEV-12514 Split Item_temporal_func::fix_length_and_dec() +# +SELECT DATE_ADD(POINT(1,1), INTERVAL 10 DAY); +ERROR HY000: Illegal parameter data types geometry and interval for operation 'date_add_interval' +SELECT DATE_SUB(POINT(1,1), INTERVAL 10 DAY); +ERROR HY000: Illegal parameter data types geometry and interval for operation 'date_add_interval' +SELECT POINT(1,1) + INTERVAL 10 DAY; +ERROR HY000: Illegal parameter data types geometry and interval for operation 'date_add_interval' +SELECT POINT(1,1) - INTERVAL 10 DAY; +ERROR HY000: Illegal parameter data types geometry and interval for operation 'date_add_interval' +SELECT INTERVAL 10 DAY + POINT(1,1); +ERROR HY000: Illegal parameter data types geometry and interval for operation 'date_add_interval' +SELECT INTERVAL 10 DAY + POINT(1,1); +ERROR HY000: Illegal parameter data types geometry and interval for operation 'date_add_interval' +SELECT ADDTIME(POINT(1,1), '10:10:10'); +ERROR HY000: Illegal parameter data types geometry and varchar for operation 'addtime' +SELECT ADDTIME('10:10:10', POINT(1,1)); +ERROR HY000: Illegal parameter data types varchar and geometry for operation 'addtime' +SELECT ADDTIME(POINT(1,1), TIME'10:10:10'); +ERROR HY000: Illegal parameter data types geometry and time for operation 'addtime' +SELECT ADDTIME(TIME'10:10:10', POINT(1,1)); +ERROR HY000: Illegal parameter data types time and geometry for operation 'addtime' +SELECT ADDTIME(POINT(1,1), TIMESTAMP'2001-01-01 10:10:10'); +ERROR HY000: Illegal parameter data types geometry and datetime for operation 'addtime' +SELECT ADDTIME(TIMESTAMP'2001-01-01 10:10:10', POINT(1,1)); +ERROR HY000: Illegal parameter data types datetime and geometry for operation 'addtime' +SELECT STR_TO_DATE(POINT(1,1),'%M %d,%Y'); +ERROR HY000: Illegal parameter data types geometry and varchar for operation 'str_to_date' +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; +# +# MDEV-12798 Item_param does not preserve exact field type in EXECUTE IMMEDIATE 'CREATE TABLE AS SELECT ?' USING POINT(1,1) +# +EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ?' USING POINT(1,1); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `?` point NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +PREPARE stmt FROM 'CREATE OR REPLACE TABLE t1 AS SELECT ?'; +EXECUTE stmt USING POINT(1,1); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `?` point NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions +# +SELECT +ST_ISSIMPLE(POINT(1,1)), +ST_ISRING(POINT(1,1)), +ST_ISCLOSED(POINT(1,1)), +ST_DIMENSION(POINT(1,1)), +ST_NUMGEOMETRIES(POINT(1,1)), +ST_NUMINTERIORRINGS(POINT(1,1)), +ST_NUMPOINTS(POINT(1,1)), +ST_SRID(POINT(1,1)); +Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr +def ST_ISSIMPLE(POINT(1,1)) 3 2 1 Y 32896 0 63 +def ST_ISRING(POINT(1,1)) 3 2 2 Y 32896 0 63 +def ST_ISCLOSED(POINT(1,1)) 3 2 2 Y 32896 0 63 +def ST_DIMENSION(POINT(1,1)) 3 10 1 Y 32896 0 63 +def ST_NUMGEOMETRIES(POINT(1,1)) 3 10 0 Y 32896 0 63 +def ST_NUMINTERIORRINGS(POINT(1,1)) 3 10 0 Y 32896 0 63 +def ST_NUMPOINTS(POINT(1,1)) 3 10 0 Y 32896 0 63 +def ST_SRID(POINT(1,1)) 3 10 1 Y 32896 0 63 +ST_ISSIMPLE(POINT(1,1)) ST_ISRING(POINT(1,1)) ST_ISCLOSED(POINT(1,1)) ST_DIMENSION(POINT(1,1)) ST_NUMGEOMETRIES(POINT(1,1)) ST_NUMINTERIORRINGS(POINT(1,1)) ST_NUMPOINTS(POINT(1,1)) ST_SRID(POINT(1,1)) +1 -1 -1 0 NULL NULL NULL 0 +# +# MDEV-12803 Improve function parameter data type control +# +# +# Item_geometry_func_args_geometry +# +SELECT ST_CENTROID(1); +ERROR HY000: Illegal parameter data type int for operation 'st_centroid' +SELECT ST_ENVELOPE(1); +ERROR HY000: Illegal parameter data type int for operation 'st_envelope' +SELECT ST_BOUNDARY(1); +ERROR HY000: Illegal parameter data type int for operation 'st_boundary' +SELECT ST_STARTPOINT(1); +ERROR HY000: Illegal parameter data type int for operation 'st_startpoint' +SELECT ST_POINTONSURFACE(1); +ERROR HY000: Illegal parameter data type int for operation 'st_pointonsurface' +SELECT ST_POINTN(1,1); +ERROR HY000: Illegal parameter data type int for operation 'st_pointn' +SELECT ST_POINTN(LineString(Point(1,1)),Point(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'st_pointn' +SELECT ST_BUFFER(1, 1); +ERROR HY000: Illegal parameter data type int for operation 'st_buffer' +SELECT ST_BUFFER(Point(1,1), Point(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'st_buffer' +PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ST_ENVELOPE(?) AS g'; +EXECUTE stmt USING 1; +ERROR HY000: Illegal parameter data type int for operation 'st_envelope' +EXECUTE stmt USING POINT(1,1); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` polygon DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT ST_ASTEXT(g) FROM t1; +ST_ASTEXT(g) +POLYGON((1 1,1 1,1 1,1 1,1 1)) +DROP TABLE t1; +DEALLOCATE PREPARE stmt; +PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ST_BUFFER(?,?) AS g'; +EXECUTE stmt USING 1,1; +ERROR HY000: Illegal parameter data type int for operation 'st_buffer' +EXECUTE stmt USING POINT(1,1),POINT(1,1); +ERROR HY000: Illegal parameter data type geometry for operation 'st_buffer' +EXECUTE stmt USING POINT(1,1),0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` geometry DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT ST_GEOMETRYTYPE(g) FROM t1; +ST_GEOMETRYTYPE(g) +POINT +DROP TABLE t1; +DEALLOCATE PREPARE stmt; +# +# Functions created GEOMETRY from non-GEOMETRY +# +SELECT ST_GEOMETRYFROMTEXT(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_geometryfromtext' +SELECT ST_GEOMETRYFROMTEXT(Point(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'st_geometryfromtext' +SELECT ST_GEOMETRYFROMTEXT(Point(1,1), 1); +ERROR HY000: Illegal parameter data type geometry for operation 'st_geometryfromtext' +SELECT ST_GEOMETRYFROMTEXT('test', Point(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'st_geometryfromtext' +SELECT ST_GEOMETRYFROMWKB(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_geometryfromwkb' +SELECT ST_GEOMETRYFROMWKB(1); +ERROR HY000: Illegal parameter data type int for operation 'st_geometryfromwkb' +SELECT ST_GEOMETRYFROMWKB(1, 1); +ERROR HY000: Illegal parameter data type int for operation 'st_geometryfromwkb' +SELECT ST_GEOMETRYFROMWKB(Point(1,1), Point(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'st_geometryfromwkb' +SELECT ST_GEOMFROMGEOJSON(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_geomfromgeojson' +SELECT ST_GEOMFROMGEOJSON(1); +ERROR HY000: Illegal parameter data type int for operation 'st_geomfromgeojson' +SELECT ST_GEOMFROMGEOJSON(1,1); +ERROR HY000: Illegal parameter data type int for operation 'st_geomfromgeojson' +SELECT ST_GEOMFROMGEOJSON(Point(1,1), Point(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'st_geomfromgeojson' +SELECT POINT(ROW(1,1),1); +ERROR HY000: Illegal parameter data type row for operation 'point' +SELECT POINT(POINT(1,1),1); +ERROR HY000: Illegal parameter data type geometry for operation 'point' +SELECT POINT(1,ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'point' +SELECT POINT(1,POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'point' +PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ST_GEOMFROMTEXT(?,?) AS g'; +EXECUTE stmt USING 1,1; +ERROR HY000: Illegal parameter data type int for operation 'st_geometryfromtext' +EXECUTE stmt USING POINT(1,1),POINT(1,1); +ERROR HY000: Illegal parameter data type geometry for operation 'st_geometryfromtext' +EXECUTE stmt USING 'POINT(1 1)',1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `g` geometry DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT ST_ASTEXT(g), SRID(g) FROM t1; +ST_ASTEXT(g) SRID(g) +POINT(1 1) 1 +DROP TABLE t1; +DEALLOCATE PREPARE stmt; +# Item_func_spatial_collection +SELECT GEOMETRYCOLLECTION(ROW(1,1), POINT(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'geometrycollection' +SELECT GEOMETRYCOLLECTION(POINT(1,1), ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'geometrycollection' +SELECT GEOMETRYCOLLECTION(1, POINT(1,1)); +ERROR HY000: Illegal parameter data type int for operation 'geometrycollection' +SELECT GEOMETRYCOLLECTION(POINT(1,1), 1); +ERROR HY000: Illegal parameter data type int for operation 'geometrycollection' +# Item_func_spatial_operation +SELECT ST_UNION(ROW(1,1), POINT(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_union' +SELECT ST_UNION(POINT(1,1), ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_union' +SELECT ST_UNION(1, POINT(1,1)); +ERROR HY000: Illegal parameter data type int for operation 'st_union' +SELECT ST_UNION(POINT(1,1), 1); +ERROR HY000: Illegal parameter data type int for operation 'st_union' +# Item_binary_func_args_geometry +SELECT ST_ASWKB(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_aswkb' +SELECT ST_ASWKB(1); +ERROR HY000: Illegal parameter data type int for operation 'st_aswkb' +SELECT ST_ASWKB('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_aswkb' +# Item_real_func_args_geometry +SELECT ST_X(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_x' +SELECT ST_X(1); +ERROR HY000: Illegal parameter data type int for operation 'st_x' +SELECT ST_X('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_x' +SELECT ST_Y(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_y' +SELECT ST_Y(1); +ERROR HY000: Illegal parameter data type int for operation 'st_y' +SELECT ST_Y('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_y' +SELECT ST_AREA(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_area' +SELECT ST_AREA(1); +ERROR HY000: Illegal parameter data type int for operation 'st_area' +SELECT ST_AREA('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_area' +SELECT ST_LENGTH(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_length' +SELECT ST_LENGTH(1); +ERROR HY000: Illegal parameter data type int for operation 'st_length' +SELECT ST_LENGTH('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_length' +# Item_real_func_geometry_args_geometry_geometry +SELECT ST_DISTANCE(ROW(1,1), 1); +ERROR HY000: Illegal parameter data type row for operation 'st_distance' +SELECT ST_DISTANCE(1, 1); +ERROR HY000: Illegal parameter data type int for operation 'st_distance' +SELECT ST_DISTANCE('test', 1); +ERROR HY000: Illegal parameter data type varchar for operation 'st_distance' +SELECT ST_DISTANCE(POINT(1,1), ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_distance' +SELECT ST_DISTANCE(POINT(1,1), 1); +ERROR HY000: Illegal parameter data type int for operation 'st_distance' +SELECT ST_DISTANCE(POINT(1,1), 'test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_distance' +# Item_int_func_args_geometry +SELECT ST_ISSIMPLE(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_issimple' +SELECT ST_ISSIMPLE(1); +ERROR HY000: Illegal parameter data type int for operation 'st_issimple' +SELECT ST_ISSIMPLE('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_issimple' +SELECT ST_ISRING(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_isring' +SELECT ST_ISRING(1); +ERROR HY000: Illegal parameter data type int for operation 'st_isring' +SELECT ST_ISRING('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_isring' +SELECT ST_ISCLOSED(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_isclosed' +SELECT ST_ISCLOSED(1); +ERROR HY000: Illegal parameter data type int for operation 'st_isclosed' +SELECT ST_ISCLOSED('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_isclosed' +SELECT ST_DIMENSION(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_dimension' +SELECT ST_DIMENSION(1); +ERROR HY000: Illegal parameter data type int for operation 'st_dimension' +SELECT ST_DIMENSION('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_dimension' +SELECT ST_NUMGEOMETRIES(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_numgeometries' +SELECT ST_NUMGEOMETRIES(1); +ERROR HY000: Illegal parameter data type int for operation 'st_numgeometries' +SELECT ST_NUMGEOMETRIES('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_numgeometries' +SELECT ST_NUMINTERIORRINGS(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_numinteriorrings' +SELECT ST_NUMINTERIORRINGS(1); +ERROR HY000: Illegal parameter data type int for operation 'st_numinteriorrings' +SELECT ST_NUMINTERIORRINGS('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_numinteriorrings' +SELECT ST_NUMPOINTS(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_numpoints' +SELECT ST_NUMPOINTS(1); +ERROR HY000: Illegal parameter data type int for operation 'st_numpoints' +SELECT ST_NUMPOINTS('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_numpoints' +SELECT ST_SRID(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'srid' +SELECT ST_SRID(1); +ERROR HY000: Illegal parameter data type int for operation 'srid' +SELECT ST_SRID('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'srid' +# Item_bool_func_args_geometry +SELECT ST_ISEMPTY(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_isempty' +SELECT ST_ISEMPTY(1); +ERROR HY000: Illegal parameter data type int for operation 'st_isempty' +SELECT ST_ISEMPTY('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_isempty' +# Item_bool_func_geometry_args_geometry_geometry +SELECT ST_RELATE(ROW(1,1), POINT(1,1), 'T*F**FFF*'); +ERROR HY000: Illegal parameter data type row for operation 'st_relate' +SELECT ST_RELATE(POINT(1,1), ROW(1,1), 'T*F**FFF*'); +ERROR HY000: Illegal parameter data type row for operation 'st_relate' +SELECT ST_RELATE(1, POINT(1,1), 'T*F**FFF*'); +ERROR HY000: Illegal parameter data type int for operation 'st_relate' +SELECT ST_RELATE(POINT(1,1), 1, 'T*F**FFF*'); +ERROR HY000: Illegal parameter data type int for operation 'st_relate' +SELECT ST_RELATE(Point(1,1),Point(1,1),'T*F**FFF*') AS equals; +equals +1 +SELECT ST_RELATE(Point(1,1),Point(1,1),NULL); +ST_RELATE(Point(1,1),Point(1,1),NULL) +NULL +SELECT ST_RELATE(Point(1,1),Point(1,1),ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_relate' +SELECT ST_RELATE(Point(1,1),Point(1,1),1); +ERROR HY000: Illegal parameter data type int for operation 'st_relate' +SELECT ST_RELATE(Point(1,1),Point(1,1),1.0); +ERROR HY000: Illegal parameter data type decimal for operation 'st_relate' +SELECT ST_RELATE(Point(1,1),Point(1,1),1e0); +ERROR HY000: Illegal parameter data type double for operation 'st_relate' +SELECT ST_RELATE(Point(1,1),Point(1,1),TIME'10:10:10'); +ERROR HY000: Illegal parameter data type time for operation 'st_relate' +SELECT ST_RELATE(Point(1,1),Point(1,1),DATE'2010-01-01'); +ERROR HY000: Illegal parameter data type date for operation 'st_relate' +SELECT ST_RELATE(Point(1,1),Point(1,1),TIMESTAMP'2010-01-01 10:10:10'); +ERROR HY000: Illegal parameter data type datetime for operation 'st_relate' +SELECT ST_RELATE(Point(1,1),Point(1,1),Point(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'st_relate' +# Item_str_ascii_func_args_geometry +SELECT ST_ASTEXT(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_astext' +SELECT ST_ASTEXT(1); +ERROR HY000: Illegal parameter data type int for operation 'st_astext' +SELECT ST_ASTEXT('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_astext' +SELECT ST_GEOMETRYTYPE(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_geometrytype' +SELECT ST_GEOMETRYTYPE(1); +ERROR HY000: Illegal parameter data type int for operation 'st_geometrytype' +SELECT ST_GEOMETRYTYPE('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_geometrytype' +SELECT ST_ASGEOJSON(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_asgeojson' +SELECT ST_ASGEOJSON(1); +ERROR HY000: Illegal parameter data type int for operation 'st_asgeojson' +SELECT ST_ASGEOJSON('test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_asgeojson' +SELECT ST_ASGEOJSON(POINT(1,1), POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'st_asgeojson' +SELECT ST_ASGEOJSON(POINT(1,1), 1, POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'st_asgeojson' +# Item_func_spatial_rel +SELECT ST_TOUCHES(ROW(1,1), POINT(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_touches' +SELECT ST_TOUCHES(POINT(1,1), ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_touches' +SELECT ST_TOUCHES(1, POINT(1,1)); +ERROR HY000: Illegal parameter data type int for operation 'st_touches' +SELECT ST_TOUCHES(POINT(1,1), 1); +ERROR HY000: Illegal parameter data type int for operation 'st_touches' +SELECT ST_TOUCHES('test', POINT(1,1)); +ERROR HY000: Illegal parameter data type varchar for operation 'st_touches' +SELECT ST_TOUCHES(POINT(1,1), 'test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_touches' +SELECT MBRTOUCHES(ROW(1,1), POINT(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_touches' +SELECT MBRTOUCHES(POINT(1,1), ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'st_touches' +SELECT MBRTOUCHES(1, POINT(1,1)); +ERROR HY000: Illegal parameter data type int for operation 'st_touches' +SELECT MBRTOUCHES(POINT(1,1), 1); +ERROR HY000: Illegal parameter data type int for operation 'st_touches' +SELECT MBRTOUCHES('test', POINT(1,1)); +ERROR HY000: Illegal parameter data type varchar for operation 'st_touches' +SELECT MBRTOUCHES(POINT(1,1), 'test'); +ERROR HY000: Illegal parameter data type varchar for operation 'st_touches' +# +# MDEV-13964 Parameter data type control for Item_real_func +# +SELECT EXP(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'exp' +SELECT LN(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'ln' +SELECT LOG2(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'log2' +SELECT LOG10(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'log10' +SELECT SQRT(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'sqrt' +SELECT ACOS(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'acos' +SELECT ASIN(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'asin' +SELECT COS(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'cos' +SELECT SIN(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'sin' +SELECT TAN(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'tan' +SELECT COT(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'cot' +SELECT LOG(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'log' +SELECT LOG(POINT(1,1),POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'log' +SELECT LOG(1, POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'log' +SELECT ATAN(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'atan' +SELECT ATAN(POINT(1,1),POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'atan' +SELECT ATAN(1, POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'atan' +SELECT POW(POINT(1,1),POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'pow' +SELECT RAND(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'rand' +SELECT RADIANS(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'radians' +SELECT DEGREES(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'degrees' +SELECT EXP(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'exp' +SELECT LN(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'ln' +SELECT LOG2(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'log2' +SELECT LOG10(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'log10' +SELECT SQRT(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'sqrt' +SELECT ACOS(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'acos' +SELECT ASIN(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'asin' +SELECT COS(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'cos' +SELECT SIN(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'sin' +SELECT TAN(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'tan' +SELECT COT(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'cot' +SELECT LOG(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'log' +SELECT LOG(ROW(1,1),ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'log' +SELECT LOG(1, ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'log' +SELECT ATAN(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'atan' +SELECT ATAN(ROW(1,1),ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'atan' +SELECT ATAN(1, ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'atan' +SELECT POW(ROW(1,1),ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'pow' +SELECT RAND(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'rand' +SELECT RADIANS(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'radians' +SELECT DEGREES(ROW(1,1)); +ERROR HY000: Illegal parameter data type row for operation 'degrees' +# +# MDEV-13965 Parameter data type control for Item_longlong_func +# +SELECT POINT(1,1) | 1; +ERROR HY000: Illegal parameter data type geometry for operation '|' +SELECT 1 | POINT(1,1); +ERROR HY000: Illegal parameter data type geometry for operation '|' +SELECT POINT(1,1) & 1; +ERROR HY000: Illegal parameter data type geometry for operation '&' +SELECT 1 & POINT(1,1); +ERROR HY000: Illegal parameter data type geometry for operation '&' +SELECT POINT(1,1) << 1; +ERROR HY000: Illegal parameter data type geometry for operation '<<' +SELECT 1 << POINT(1,1); +ERROR HY000: Illegal parameter data type geometry for operation '<<' +SELECT POINT(1,1) >> 1; +ERROR HY000: Illegal parameter data type geometry for operation '>>' +SELECT 1 >> POINT(1,1); +ERROR HY000: Illegal parameter data type geometry for operation '>>' +SELECT ~POINT(1,1); +ERROR HY000: Illegal parameter data type geometry for operation '~' +SELECT TO_SECONDS(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'to_seconds' +SELECT TIMESTAMPDIFF(SECOND,POINT(1,1), 1); +ERROR HY000: Illegal parameter data type geometry for operation 'timestampdiff' +SELECT TIMESTAMPDIFF(SECOND,1, POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'timestampdiff' +SELECT INET_ATON(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'inet_aton' +SELECT LAST_INSERT_ID(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'last_insert_id' +# +# MDEV-13966 Parameter data type control for Item_temporal_func +# +SELECT FROM_DAYS(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'from_days' +SELECT MAKEDATE(POINT(1,1),1); +ERROR HY000: Illegal parameter data type geometry for operation 'makedate' +SELECT MAKEDATE(1, POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'makedate' +SELECT LAST_DAY(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'last_day' +SELECT SEC_TO_TIME(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'sec_to_time' +SELECT TIMEDIFF(POINT(1,1),1); +ERROR HY000: Illegal parameter data type geometry for operation 'timediff' +SELECT TIMEDIFF(1, POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'timediff' +SELECT MAKETIME(POINT(1,1),1,1); +ERROR HY000: Illegal parameter data type geometry for operation 'maketime' +SELECT MAKETIME(1, POINT(1,1), 1); +ERROR HY000: Illegal parameter data type geometry for operation 'maketime' +SELECT MAKETIME(1, 1, POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'maketime' +SELECT FROM_UNIXTIME(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'from_unixtime' +SELECT CONVERT_TZ(POINT(1,1),1,1); +ERROR HY000: Illegal parameter data type geometry for operation 'convert_tz' +SELECT CONVERT_TZ(1, POINT(1,1), 1); +ERROR HY000: Illegal parameter data type geometry for operation 'convert_tz' +SELECT CONVERT_TZ(1, 1, POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'convert_tz' +# +# MDEV-13967 Parameter data type control for Item_long_func +# +SELECT STRCMP(POINT(1,1),POINT(1,1)); +STRCMP(POINT(1,1),POINT(1,1)) +0 +SELECT CHAR_LENGTH(POINT(1,1)); +CHAR_LENGTH(POINT(1,1)) +25 +SELECT OCTET_LENGTH(POINT(1,1)); +OCTET_LENGTH(POINT(1,1)) +25 +SELECT UNCOMPRESSED_LENGTH(POINT(1,1)); +UNCOMPRESSED_LENGTH(POINT(1,1)) +0 +SELECT COERCIBILITY(POINT(1,1)); +COERCIBILITY(POINT(1,1)) +4 +SELECT ASCII(POINT(1,1)); +ASCII(POINT(1,1)) +0 +SELECT CRC32(POINT(1,1)); +CRC32(POINT(1,1)) +1349318989 +SELECT ORD(POINT(1,1)); +ORD(POINT(1,1)) +0 +SELECT SIGN(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'sign' +SELECT LOCATE('a','a',POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'locate' +SELECT LOCATE(POINT(1,1),POINT(1,1)); +LOCATE(POINT(1,1),POINT(1,1)) +1 +SELECT BIT_COUNT(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'bit_count' +SELECT BENCHMARK(POINT(1,1),''); +ERROR HY000: Illegal parameter data type geometry for operation 'benchmark' +SELECT SLEEP(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'sleep' +SELECT GET_LOCK('x', POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'get_lock' +SELECT PERIOD_ADD(POINT(1,1),1); +ERROR HY000: Illegal parameter data type geometry for operation 'period_add' +SELECT PERIOD_ADD(1,POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'period_add' +SELECT PERIOD_DIFF(POINT(1,1),1); +ERROR HY000: Illegal parameter data type geometry for operation 'period_diff' +SELECT PERIOD_DIFF(1,POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'period_diff' +SELECT TO_DAYS(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'to_days' +SELECT DAYOFMONTH(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'dayofmonth' +SELECT DAYOFYEAR(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'dayofyear' +SELECT QUARTER(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'quarter' +SELECT YEAR(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'year' +SELECT YEARWEEK(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'yearweek' +SELECT WEEK(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'week' +SELECT WEEK(POINT(1,1),1); +ERROR HY000: Illegal parameter data type geometry for operation 'week' +SELECT WEEK(1,POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'week' +SELECT HOUR(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'hour' +SELECT MINUTE(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'minute' +SELECT SECOND(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'second' +SELECT MICROSECOND(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'microsecond' +SELECT JSON_DEPTH(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'json_depth' +SELECT JSON_LENGTH(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'json_length' +SELECT JSON_LENGTH('json', POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'json_length' +SELECT JSON_LENGTH(POINT(1,1), POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'json_length' +SELECT REGEXP_INSTR(POINT(1,1),''); +REGEXP_INSTR(POINT(1,1),'') +1 +SELECT REGEXP_INSTR('',POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'regexp_instr' +SELECT FIND_IN_SET(POINT(1,1),''); +FIND_IN_SET(POINT(1,1),'') +0 +SELECT FIND_IN_SET('',POINT(1,1)); +FIND_IN_SET('',POINT(1,1)) +0 +SELECT RELEASE_LOCK(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'release_lock' +SELECT IS_FREE_LOCK(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'is_free_lock' +SELECT IS_USED_LOCK(POINT(1,1)); +ERROR HY000: Illegal parameter data type geometry for operation 'is_used_lock' +# +# End of 10.3 tests +# |