gis.test 94.3 KB
Newer Older
1 2
-- source include/have_geometry.inc

unknown's avatar
unknown committed
3

unknown's avatar
unknown committed
4 5 6 7
#
# Spatial objects
#

unknown's avatar
unknown committed
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
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;
SHOW FIELDS FROM gis_line;
SHOW FIELDS FROM gis_polygon;
SHOW FIELDS FROM gis_multi_point;
SHOW FIELDS FROM gis_multi_line;
SHOW FIELDS FROM gis_multi_polygon;
SHOW FIELDS FROM gis_geometrycollection;
SHOW FIELDS FROM gis_geometry;


INSERT INTO gis_point VALUES 
unknown's avatar
unknown committed
28 29 30
(101, PointFromText('POINT(10 10)')),
(102, PointFromText('POINT(20 10)')),
(103, PointFromText('POINT(20 20)')),
unknown's avatar
unknown committed
31
(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
unknown's avatar
unknown committed
32

unknown's avatar
unknown committed
33
INSERT INTO gis_line VALUES
unknown's avatar
unknown committed
34 35
(105, LineFromText('LINESTRING(0 0,0 10,10 0)')),
(106, LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
36
(107, LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10)))));
unknown's avatar
unknown committed
37

unknown's avatar
unknown committed
38
INSERT INTO gis_polygon VALUES
unknown's avatar
unknown committed
39 40
(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))')),
41
(110, PolyFromWKB(AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
unknown's avatar
unknown committed
42

unknown's avatar
unknown committed
43
INSERT INTO gis_multi_point VALUES
unknown's avatar
unknown committed
44 45
(111, MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
(112, MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
46
(113, MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
unknown's avatar
unknown committed
47

unknown's avatar
unknown committed
48
INSERT INTO gis_multi_line VALUES
unknown's avatar
unknown committed
49 50
(114, MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
(115, MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
51
(116, MLineFromWKB(AsWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7))))));
unknown's avatar
unknown committed
52 53


unknown's avatar
unknown committed
54
INSERT INTO gis_multi_polygon VALUES
unknown's avatar
unknown committed
55 56
(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)))')),
57
(119, MPolyFromWKB(AsWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
unknown's avatar
unknown committed
58

unknown's avatar
unknown committed
59
INSERT INTO gis_geometrycollection VALUES
unknown's avatar
unknown committed
60
(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
61
(121, GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))),
62 63
(122, GeomFromText('GeometryCollection()')),
(123, GeomFromText('GeometryCollection EMPTY'));
unknown's avatar
unknown committed
64

unknown's avatar
unknown committed
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
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;
SELECT fid, AsText(g) FROM gis_line;
SELECT fid, AsText(g) FROM gis_polygon;
SELECT fid, AsText(g) FROM gis_multi_point;
SELECT fid, AsText(g) FROM gis_multi_line;
SELECT fid, AsText(g) FROM gis_multi_polygon;
SELECT fid, AsText(g) FROM gis_geometrycollection;
SELECT fid, AsText(g) FROM gis_geometry;

SELECT fid, Dimension(g) FROM gis_geometry;
SELECT fid, GeometryType(g) FROM gis_geometry;
SELECT fid, IsEmpty(g) FROM gis_geometry;
SELECT fid, AsText(Envelope(g)) FROM gis_geometry;
explain extended select Dimension(g), GeometryType(g), IsEmpty(g), AsText(Envelope(g)) from gis_geometry;

SELECT fid, X(g) FROM gis_point;
SELECT fid, Y(g) FROM gis_point;
explain extended select X(g),Y(g) FROM gis_point;

SELECT fid, AsText(StartPoint(g)) FROM gis_line;
SELECT fid, AsText(EndPoint(g)) FROM gis_line;
SELECT fid, GLength(g) FROM gis_line;
SELECT fid, NumPoints(g) FROM gis_line;
SELECT fid, AsText(PointN(g, 2)) FROM gis_line;
SELECT fid, IsClosed(g) FROM gis_line;
explain extended select AsText(StartPoint(g)),AsText(EndPoint(g)),GLength(g),NumPoints(g),AsText(PointN(g, 2)),IsClosed(g) FROM gis_line;

SELECT fid, AsText(Centroid(g)) FROM gis_polygon;
SELECT fid, Area(g) FROM gis_polygon;
SELECT fid, AsText(ExteriorRing(g)) FROM gis_polygon;
SELECT fid, NumInteriorRings(g) FROM gis_polygon;
SELECT fid, AsText(InteriorRingN(g, 1)) FROM gis_polygon;
explain extended select AsText(Centroid(g)),Area(g),AsText(ExteriorRing(g)),NumInteriorRings(g),AsText(InteriorRingN(g, 1)) FROM gis_polygon;

SELECT fid, IsClosed(g) FROM gis_multi_line;

SELECT fid, AsText(Centroid(g)) FROM gis_multi_polygon;
SELECT fid, Area(g) FROM gis_multi_polygon;

SELECT fid, NumGeometries(g) from gis_multi_point;
SELECT fid, NumGeometries(g) from gis_multi_line;
SELECT fid, NumGeometries(g) from gis_multi_polygon;
SELECT fid, NumGeometries(g) from gis_geometrycollection;
explain extended SELECT fid, NumGeometries(g) from gis_multi_point;

SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_line;
SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_polygon;
SELECT fid, AsText(GeometryN(g, 2)) from gis_geometrycollection;
SELECT fid, AsText(GeometryN(g, 1)) from gis_geometrycollection;
explain extended SELECT fid, AsText(GeometryN(g, 2)) from gis_multi_point;
unknown's avatar
unknown committed
124 125 126 127 128

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
unknown's avatar
unknown committed
129
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
130 131 132 133
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
unknown's avatar
unknown committed
134
FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
unknown's avatar
unknown committed
135

unknown's avatar
unknown committed
136
DROP TABLE gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry;
137 138 139 140

#
# Check that ALTER TABLE doesn't loose geometry type
#
unknown's avatar
unknown committed
141 142
CREATE TABLE t1 (
  gp  point,
143 144
  ln  linestring,
  pg  polygon,
unknown's avatar
unknown committed
145
  mp  multipoint,
146 147 148 149 150 151
  mln multilinestring,
  mpg multipolygon,
  gc  geometrycollection,
  gm  geometry
);

unknown's avatar
unknown committed
152 153 154 155
SHOW FIELDS FROM t1;
ALTER TABLE t1 ADD fid INT NOT NULL;
SHOW FIELDS FROM t1;
DROP TABLE t1;
unknown's avatar
unknown committed
156 157

SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
158 159 160 161 162 163
explain extended SELECT AsText(GeometryFromWKB(AsWKB(GeometryFromText('POINT(1 4)'))));
explain extended SELECT AsText(GeometryFromWKB(AsWKB(PointFromText('POINT(1 4)'))));
SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
explain extended SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
#select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6)),issimple(PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),issimple(GeometryFromText('POINT(1 4)')), issimple(AsWKB(GeometryFromText('POINT(1 4)')));
explain extended select issimple(MultiPoint(Point(3, 6), Point(4, 10))), issimple(Point(3, 6));
unknown's avatar
unknown committed
164 165 166

create table t1 (a geometry not null);
insert into t1 values (GeomFromText('Point(1 2)'));
167
-- error 1416
unknown's avatar
unknown committed
168
insert into t1 values ('Garbage');
169
-- error 1416
unknown's avatar
unknown committed
170
insert IGNORE into t1 values ('Garbage');
unknown's avatar
unknown committed
171 172 173
alter table t1 add spatial index(a);

drop table t1;
174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191

#
# Bug #5219: problem with range optimizer
#

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);
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);
drop table t1;
192

193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281
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;
unknown's avatar
unknown committed
282

283
create table t1 select GeomFromWKB(POINT(1,3));
284 285
show create table t1;
drop table t1;
286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353

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)))'));

354 355 356
# Expected results are 115.2970604672862 and 36.23335610879993, but IA64 returns
# slightly different values due to fused multiply-add instructions.
--replace_result 115.29706047613604 115.2970604672862 36.23335611157958 36.23335610879993
357 358 359
select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from
t1 where object_id=85998;

360
# Expected result is 36.34725218253213, but IA64 returns 36.34725217627852
361
# due to fused multiply-add instructions.
362
--replace_result 36.34725217627852 36.34725218253213 -114.86854470090232 -114.86854472054372
363 364 365 366
select object_id, geometrytype(geo), ISSIMPLE(GEO), ASTEXT(centroid(geo)) from
t1 where object_id=85984;

drop table t1;
367

unknown's avatar
unknown committed
368
create table t1 (fl geometry not null);
369 370 371 372 373 374
--error 1416
insert into t1 values (1);
--error 1416
insert into t1 values (1.11);
--error 1416
insert into t1 values ("qwerty");
unknown's avatar
unknown committed
375
--error 1048
376 377 378
insert into t1 values (pointfromtext('point(1,1)'));

drop table t1;
unknown's avatar
unknown committed
379

380 381 382
select (asWKT(geomfromwkb((0x000000000140240000000000004024000000000000))));
select (asWKT(geomfromwkb((0x010100000000000000000024400000000000002440))));

383 384 385 386 387 388
--enable_metadata
create table t1 (g GEOMETRY);
select * from t1;
select asbinary(g) from t1;
--disable_metadata
drop table t1;
389 390 391 392

create table t1 (a TEXT, b GEOMETRY NOT NULL, SPATIAL KEY(b));
alter table t1 disable keys;
--error 1263
unknown's avatar
unknown committed
393
load data infile '../../std_data/bad_gis_data.dat' into table t1;
394 395 396
alter table t1 enable keys;
drop table t1;

397 398 399 400 401 402 403 404
#
# Bug #26038: is null and bad data
#

create table t1 (a int, b blob);
insert into t1 values (1, ''), (2, NULL), (3, '1');
select * from t1;

405
--error ER_ILLEGAL_VALUE_FOR_TYPE
406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423
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;

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;

424
--error ER_ILLEGAL_VALUE_FOR_TYPE
425 426 427 428 429 430 431 432
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;

drop table t1;

433 434 435 436 437 438 439 440
#
# Bug #27164: Crash when mixing InnoDB and MyISAM Geospatial tables
#
CREATE TABLE t1(a POINT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1;
DROP TABLE t1;

unknown's avatar
unknown committed
441 442 443 444 445 446 447 448
#
# Bug #30955 geomfromtext() crasher
#
CREATE TABLE `t1` ( `col9` set('a'), `col89` date);
INSERT INTO `t1` VALUES ('','0000-00-00');
select geomfromtext(col9,col89) as a from t1;
DROP TABLE t1;

449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479
#
# Bug #31158 Spatial, Union, LONGBLOB vs BLOB bug (crops data) 
#

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;

drop table t1;
drop table t2;
drop table t3;

unknown's avatar
unknown committed
480 481 482 483
#
# Bug #30284 spatial key corruption 
#

unknown's avatar
unknown committed
484
create table t1(col1 geometry default null,col15 geometrycollection not
unknown's avatar
unknown committed
485 486 487 488 489 490
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;
drop table t1;

491
--echo End of 4.1 tests
492 493 494 495 496 497 498 499 500 501

#
# Bug #12281 (Geometry: crash in trigger)
#

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;
--error 1048
insert into t1 values (null,null);
drop table t1;
502 503 504 505

#
# Bug #10499 (function creation with GEOMETRY datatype)
#
506 507 508
--disable_warnings
drop procedure if exists fn3;
--enable_warnings
509
create function fn3 () returns point deterministic return GeomFromText("point(1 1)"); 
510 511 512 513
show create function fn3;
select astext(fn3());
drop function fn3;

514 515 516 517
#
# Bug #12267 (primary key over GIS)
#
create table t1(pt POINT);
518 519 520
alter table t1 add primary key pti(pt);
drop table t1;
create table t1(pt GEOMETRY);
521 522 523
--error 1170
alter table t1 add primary key pti(pt);
alter table t1 add primary key pti(pt(20));
unknown's avatar
unknown committed
524 525
drop table t1;

526 527 528 529

create table t1 select GeomFromText('point(1 1)');
desc t1;
drop table t1;
530

531 532 533 534 535 536 537 538
#
# Bug #20691 (DEFAULT over NOT NULL field)
#
create table t1 (g geometry not null);
--error ER_CANT_CREATE_GEOMETRY_OBJECT
insert into t1 values(default);
drop table t1;

539 540 541 542 543 544 545 546 547 548 549 550
#
# Bug #27300: create view with geometry functions lost columns types
#
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;
DESCRIBE v2;

DROP VIEW v1,v2;
DROP TABLE t1;

551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592
#
# Bug#24563: MBROverlaps does not seem to function propertly
# Bug#54888: MBROverlaps missing in 5.1?
#

# Test all MBR* functions and their non-MBR-prefixed aliases,
# using shifted squares to verify the spatial relations.

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;
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;
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;
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;
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;
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;
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;

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;
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;
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;
593
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;
594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616
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;
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;
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;

# Overlaps needs a few more tests, with point and line dimensions

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;
SELECT GROUP_CONCAT(a1.name ORDER BY a1.name) AS overlaps FROM t1 a1 WHERE Overlaps(a1.square, @horiz1) GROUP BY a1.name;
SELECT Overlaps(@horiz1, @vert1) FROM DUAL;
SELECT Overlaps(@horiz1, @horiz2) FROM DUAL;
SELECT Overlaps(@horiz1, @horiz3) FROM DUAL;
SELECT Overlaps(@horiz1, @point1) FROM DUAL;
SELECT Overlaps(@horiz1, @point2) FROM DUAL;

DROP TABLE t1;

617 618 619
#
# Bug#28763: Selecting geometry fields in UNION caused server crash.
#
Alexey Botchkov's avatar
Alexey Botchkov committed
620
create table t1(f1 geometry, f2 linestring, f3 linestring);
621
select f1 from t1 union select f1 from t1;
Alexey Botchkov's avatar
Alexey Botchkov committed
622
insert into t1 (f2,f3) values (GeomFromText('LINESTRING(1 1, 2 2)'),
623 624 625 626 627 628 629
  GeomFromText('LINESTRING(0 0,1 1,2 2)'));
select AsText(f2),AsText(f3) from t1;
select AsText(a) from (select f2 as a from t1 union select f3 from t1) t;
create table t2 as select f2 as a from t1 union select f3 from t1;
desc t2;
select AsText(a) from t2; 
drop table t1, t2;
unknown's avatar
unknown committed
630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649

#
# Bug #29166: MYsql crash when query is run
#

# The test query itself is not logged : too large output.
# The real test is the second query : see if the first hasn't crashed the
# server
--disable_query_log
--disable_result_log
SELECT AsText(GeometryFromText(CONCAT(
  'MULTIPOLYGON(((',
  REPEAT ('-0.00000000001234567890123456789012 -0.123456789012345678,', 1000),
  '-0.00000000001234567890123456789012 -0.123456789012345678',
  ')))'
))) AS a;
--enable_result_log
--enable_query_log
SELECT 1;

650 651
-- source include/gis_keys.inc

unknown's avatar
unknown committed
652 653 654 655 656 657 658 659 660
#
# Bug #31155 gis types in union'd select cause crash
#

create table `t1` (`col002` point)engine=myisam;
insert into t1 values (),(),();
select min(`col002`) from t1 union select `col002` from t1;
drop table t1;

661 662 663 664 665 666 667 668 669 670 671
--echo #
--echo # Bug #47780: crash when comparing GIS items from subquery
--echo #

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)))'));

--echo # must not crash
672
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
673 674 675 676
SELECT 1 FROM t1 WHERE a <> (SELECT GEOMETRYCOLLECTIONFROMWKB(b) FROM t1);

DROP TABLE t1;

677 678 679 680 681 682 683 684 685 686 687 688 689 690 691
--echo #
--echo # Bug #49250 : spatial btree index corruption and crash
--echo # Part one : spatial syntax check
--echo #

--error ER_PARSE_ERROR
CREATE TABLE t1(col1 MULTIPOLYGON NOT NULL,
  SPATIAL INDEX USING BTREE (col1));
CREATE TABLE t2(col1 MULTIPOLYGON NOT NULL);
--error ER_PARSE_ERROR
CREATE SPATIAL INDEX USING BTREE ON t2(col);
--error ER_PARSE_ERROR
ALTER TABLE t2 ADD SPATIAL INDEX USING BTREE (col1);

DROP TABLE t2;
692

693
--echo End of 5.0 tests
694 695


696 697 698 699 700 701 702 703 704
#
# Bug #11335 View redefines column types
#
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;
drop view v1;
drop table t1;

705 706 707 708
#
# Bug#44684: valgrind reports invalid reads in 
# Item_func_spatial_collection::val_str
#
709
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
710
SELECT MultiPoint(12345,'');
711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735
#SELECT MultiPoint(123451,'');
#SELECT MultiPoint(1234512,'');
#SELECT MultiPoint(12345123,'');

--error ER_ILLEGAL_VALUE_FOR_TYPE
#SELECT MultiLineString(12345,'');
#SELECT MultiLineString(123451,'');
#SELECT MultiLineString(1234512,'');
#SELECT MultiLineString(12345123,'');

--error ER_ILLEGAL_VALUE_FOR_TYPE
#SELECT LineString(12345,'');
#SELECT LineString(123451,'');
#SELECT LineString(1234512,'');
#SELECT LineString(12345123,'');

--error ER_ILLEGAL_VALUE_FOR_TYPE
#SELECT Polygon(12345,'');
#SELECT Polygon(123451,'');
#SELECT Polygon(1234512,'');
#SELECT Polygon(12345123,'');

#
# Bug55531 crash with conversions of geometry types / strings
#
736
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
737
SELECT 1 FROM (SELECT GREATEST(1,GEOMETRYCOLLECTION('00000','00000')) b FROM DUAL) AS d WHERE (LINESTRING(d.b));
738

739 740 741 742 743 744 745 746 747 748

--echo #
--echo # BUG#51875: crash when loading data into geometry function polyfromwkb
--echo #
SET @a=0x00000000030000000100000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
SET @a=POLYFROMWKB(@a);
SET @a=0x00000000030000000000000000000000000000000000144000000000000014400000000000001840000000000000184000000000000014400000000000001440;
SET @a=POLYFROMWKB(@a);


749 750 751 752
#
# Bug #57321    crashes and valgrind errors from spatial types
#

753
create table t1(a geometry NOT NULL)engine=myisam;
754 755 756 757 758
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;
drop table t1;

759 760 761 762 763 764 765

--echo #
--echo # Test for bug #59888 "debug assertion when attempt to create spatial index
--echo #                      on char > 31 bytes".
--echo #
create table t1(a char(32) not null) engine=myisam;
--replace_regex /'[^']*test\.#sql-[0-9a-f_]*'/'#sql-temporary'/
766
--error ER_WRONG_ARGUMENTS
767 768 769 770
create spatial index i on t1 (a);
drop table t1;


771
#
772
# Bug#11767480 - SPATIAL INDEXES ON NON-SPATIAL COLUMNS CAUSE CRASHES.
773
#
774 775 776 777 778 779
CREATE TABLE t0 (a BINARY(32) NOT NULL);
--error ER_WRONG_ARGUMENTS
CREATE SPATIAL INDEX i on t0 (a);
INSERT INTO t0 VALUES (1);

--error ER_WRONG_ARGUMENTS
780 781 782 783 784 785 786 787 788 789 790
CREATE TABLE t1(
  col0 BINARY NOT NULL,
  col2 TIMESTAMP,
  SPATIAL INDEX i1 (col0)
) ENGINE=MyISAM;

# Test other ways to add indices
CREATE TABLE t1 (
  col0 BINARY NOT NULL,
  col2 TIMESTAMP
) ENGINE=MyISAM;
791

792
--error ER_WRONG_ARGUMENTS
793
CREATE SPATIAL INDEX idx0 ON t1(col0);
794

795
--error ER_WRONG_ARGUMENTS
796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815
ALTER TABLE t1 ADD SPATIAL INDEX i1 (col0);

CREATE TABLE t2 (
  col0 INTEGER NOT NULL,
  col1 POINT,
  col2 POINT
);

--error ER_WRONG_ARGUMENTS
CREATE SPATIAL INDEX idx0 ON t2 (col1, col2);

--error ER_WRONG_ARGUMENTS
CREATE TABLE t3 (
  col0 INTEGER NOT NULL,
  col1 POINT,
  col2 LINESTRING,
  SPATIAL INDEX i1 (col1, col2)
);

# cleanup
816 817
DROP TABLE t0, t1, t2;

818 819 820 821 822 823

--echo #
--echo # BUG#12414917 - ISCLOSED() CRASHES ON 64-BIT BUILDS
--echo #
SELECT ISCLOSED(CONVERT(CONCAT('     ', 0x2), BINARY(20)));

824 825 826 827
--echo #
--echo # BUG#12537203 - CRASH WHEN SUBSELECTING GLOBAL VARIABLES IN 
--echo # GEOMETRY FUNCTION ARGUMENTS
--echo #
828
--replace_regex /non geometric .* value/non geometric '' value/
829
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
830 831
SELECT GEOMETRYCOLLECTION((SELECT @@OLD));

832 833 834
--echo #
--echo # MDEV-4252 geometry query crashes server
--echo #
835
select astext(0x0100000000030000000100000000000010);
Sergei Golubchik's avatar
Sergei Golubchik committed
836 837
select astext(st_centroid(0x0100000000030000000100000000000010));
select astext(st_exteriorring(0x0100000000030000000100000000000010));
838 839 840
select envelope(0x0100000000030000000100000000000010);
select geometryn(0x0100000000070000000100000001030000000200000000000000ffff0000, 1);
select geometryn(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1);
841

842 843 844 845 846
--echo #
--echo # MDEV-4296 Assertion `n_linear_rings > 0' fails in Gis_polygon::centroid_xy
--echo #

SELECT Centroid( AsBinary( LineString(Point(0,0), Point(0,0), Point(0,0) )));
847

848 849 850 851
--echo #
--echo # MDEV-4295 Server crashes in get_point on a query with Area, AsBinary, MultiPoint
--echo #
SELECT Area(AsBinary(MultiPoint(Point(0,9), Point(0,1), Point(2,2))));
852
--echo End of 5.1 tests
853

Alexey Botchkov's avatar
Alexey Botchkov committed
854 855 856
#bug 850775 ST_AREA does not work on GEOMETRYCOLLECTIONs in maria-5.3-gis
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))'));

857 858
#bug 855336 ST_LENGTH does not work on GEOMETRYCOLLECTIONs
select ST_LENGTH(ST_GEOMCOLLFROMTEXT(' GEOMETRYCOLLECTION(LINESTRING(100 100, 100 30, 20 30), POINT(3 3), LINESTRING(20 20, 30 20))'));
Alexey Botchkov's avatar
Alexey Botchkov committed
859 860


861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128
# Conformance tests
#
# C.3.3 Geometry types and functions 
#

--disable_warnings
DROP DATABASE IF EXISTS gis_ogs;
--enable_warnings

CREATE DATABASE gis_ogs;
USE gis_ogs;

--echo #
--echo # C.3.3.1 Geometry types and functions schema construction 
--echo #

# TODO: WL#2377
#CREATE TABLE spatial_ref_sys ( 
#srid INTEGER NOT NULL PRIMARY KEY, 
#auth_name CHARACTER VARYING, 
#auth_srid INTEGER, 
#srtext CHARACTER VARYING(2048)); 

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); 

--echo #
--echo # C.3.3.2 Geometry types and functions schema data loading 
--echo #

# TODO: WL#2377
#-- Spatial Reference System 
#INSERT INTO spatial_ref_sys VALUES 
#(101, 'POSC', 32214, 'PROJCS["UTM_ZONE_14N", 
#GEOGCS["World Geodetic System 72", 
#DATUM["WGS_72", 
#ELLIPSOID["NWL_10D", 6378135, 298.26]], 
#PRIMEM["Greenwich", 0], 
#UNIT["Meter", 1.0]], 
#PROJECTION["Transverse_Mercator"], 
#PARAMETER["False_Easting", 500000.0], 
#PARAMETER["False_Northing", 0.0], 
#PARAMETER["Central_Meridian", -99.0], 
#PARAMETER["Scale_Factor", 0.9996], 
#PARAMETER["Latitude_of_origin", 0.0], 
#UNIT["Meter", 1.0]]'); 

--echo # 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)); 


--echo # 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)); 

--echo # 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)); 

--echo # 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)); 

--echo # Bridges 

INSERT INTO bridges VALUES(110, 'Cam Bridge', PointFromText( 
'POINT( 44 31 )', 101)); 

--echo # 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)); 

--echo # 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)); 


--echo # 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)); 

--echo # 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)); 

--echo # Map Neatlines 

INSERT INTO map_neatlines VALUES(115, 
PolyFromText( 
'POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) )', 101)); 

--echo #
--echo # C.3.3.3 Geometry types and functions schema test queries 
--echo 

# TODO: WL#2377
#--echo # Conformance Item T1 
#SELECT f_table_name 
#FROM geometry_columns; 
#
#--echo # Conformance Item T2 
#SELECT f_geometry_column 
#FROM geometry_columns 
#WHERE f_table_name = 'streams'; 
#
#--echo # Conformance Item T3 
#SELECT coord_dimension 
#FROM geometry_columns 
#WHERE f_table_name = 'streams'; 
#
#--echo # Conformance Item T4 
#
#SELECT srid 
#FROM geometry_columns 
#WHERE f_table_name = 'streams'; 
#
#--echo # Conformance Item T5 
#
#SELECT srtext 
#FROM SPATIAL_REF_SYS 
#WHERE SRID = 101; 
#


--echo # Conformance Item T6 
# TODO: ST_Dimension() alias
SELECT Dimension(shore) 
FROM lakes 
WHERE name = 'Blue Lake'; 

--echo # Conformance Item T7 
# TODO: ST_GeometryType() alias
SELECT GeometryType(centerlines) 
FROM divided_routes
WHERE name = 'Route 75'; 

--echo # Conformance Item T8 
# TODO: ST_AsText() alias
SELECT AsText(boundary) 
FROM named_places 
WHERE name = 'Goose Island'; 

--echo # Conformance Item T9 
# TODO: ST_AsBinary(), ST_PolyFromWKB() aliases
SELECT AsText(PolyFromWKB(AsBinary(boundary),101)) 
FROM named_places 
WHERE name = 'Goose Island'; 

--echo # Conformance Item T10 
SELECT SRID(boundary) 
FROM named_places 
WHERE name = 'Goose Island'; 

--echo # Conformance Item T11 
SELECT IsEmpty(centerline) 
FROM road_segments 
WHERE name = 'Route 5' 
AND aliases = 'Main Street'; 

1129 1130 1131 1132
--echo # Conformance Item T12 
SELECT IsSimple(shore) 
FROM lakes 
WHERE name = 'Blue Lake'; 
1133

1134 1135 1136 1137
--echo # Conformance Item T13 
SELECT AsText(ST_Boundary(boundary))
FROM named_places 
WHERE name = 'Goose Island'; 
1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167

--echo # Conformance Item T14 
# FIXME: we get anticlockwise, GIS suggests clockwise
SELECT AsText(Envelope(boundary)) 
FROM named_places 
WHERE name = 'Goose Island'; 

--echo # Conformance Item T15 
# TODO: ST_X() alias
SELECT X(position) 
FROM bridges 
WHERE name = 'Cam Bridge'; 

--echo # Conformance Item T16 
# TODO: ST_Y() alias
SELECT Y(position) 
FROM bridges 
WHERE name = 'Cam Bridge'; 

--echo # Conformance Item T17 
# TODO: ST_StartPoint() alias
SELECT AsText(StartPoint(centerline)) 
FROM road_segments 
WHERE fid = 102; 

--echo # Conformance Item T18 
SELECT AsText(EndPoint(centerline)) 
FROM road_segments 
WHERE fid = 102; 

1168 1169 1170
SELECT IsClosed(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) 
FROM named_places 
WHERE name = 'Goose Island'; 
1171

1172 1173 1174 1175
--echo # Conformance Item T20 
SELECT IsRing(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary))) 
FROM named_places 
WHERE name = 'Goose Island'; 
1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196

--echo # Conformance Item T21 
SELECT GLength(centerline) 
FROM road_segments 
WHERE fid = 106; 

--echo # Conformance Item T22 
SELECT NumPoints(centerline) 
FROM road_segments 
WHERE fid = 102; 

--echo # Conformance Item T23 
SELECT AsText(PointN(centerline, 1)) 
FROM road_segments 
WHERE fid = 102; 

--echo # Conformance Item T24 
SELECT AsText(Centroid(boundary)) 
FROM named_places 
WHERE name = 'Goose Island'; 

1197 1198 1199
SELECT ST_Contains(boundary, PointOnSurface(boundary)) 
FROM named_places 
WHERE name = 'Goose Island'; 
1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246

--echo # Conformance Item T26 
SELECT Area(boundary) 
FROM named_places 
WHERE name = 'Goose Island'; 

--echo # Conformance Item T27 
SELECT AsText(ExteriorRing(shore)) 
FROM lakes 
WHERE name = 'Blue Lake'; 

--echo # Conformance Item T28 
SELECT NumInteriorRings(shore) 
FROM lakes 
WHERE name = 'Blue Lake'; 

--echo # Conformance Item T29 
SELECT AsText(InteriorRingN(shore, 1)) 
FROM lakes 
WHERE name = 'Blue Lake'; 

--echo # Conformance Item T30 
SELECT NumGeometries(centerlines) 
FROM divided_routes 
WHERE name = 'Route 75'; 

--echo # Conformance Item T31 
SELECT AsText(GeometryN(centerlines, 2)) 
FROM divided_routes 
WHERE name = 'Route 75'; 

--echo # Conformance Item T32 
SELECT IsClosed(centerlines) 
FROM divided_routes 
WHERE name = 'Route 75'; 

--echo # Conformance Item T33 
SELECT GLength(centerlines) 
FROM divided_routes 
WHERE name = 'Route 75'; 

--echo # Conformance Item T34 
SELECT AsText(Centroid(shores)) 
FROM ponds 
WHERE fid = 120; 

# TODO: WL#2377
1247 1248 1249 1250
--echo # Conformance Item T35 
SELECT Contains(shores, PointOnSurface(shores)) 
FROM ponds 
WHERE fid = 120; 
1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274

--echo # Conformance Item T36 
SELECT Area(shores) 
FROM ponds 
WHERE fid = 120; 

--echo # 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'; 

--echo # Conformance Item T38 
SELECT ST_Disjoint(centerlines, boundary) 
FROM divided_routes, named_places 
WHERE divided_routes.name = 'Route 75' 
AND named_places.name = 'Ashton'; 

--echo # Conformance Item T39 
SELECT ST_Touches(centerline, shore) 
FROM streams, lakes 
WHERE streams.name = 'Cam Stream' 
AND lakes.name = 'Blue Lake'; 

1275 1276 1277 1278 1279
--echo # Conformance Item T40 
SELECT ST_Within(footprint, boundary) 
FROM named_places, buildings 
WHERE named_places.name = 'Ashton' 
AND buildings.address = '215 Main Street'; 
1280

1281 1282 1283 1284 1285
--echo # 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'; 
1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304

--echo # 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'; 

--echo # 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'; 

--echo # 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'; 

1305 1306 1307 1308 1309
--echo # 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'; 
1310 1311 1312 1313 1314 1315 1316

--echo # Conformance Item T46 
SELECT ST_Distance(position, boundary) 
FROM bridges, named_places 
WHERE bridges.name = 'Cam Bridge' 
AND named_places.name = 'Ashton'; 

1317 1318 1319 1320 1321
--echo # Conformance Item T47 
SELECT AsText(ST_Intersection(centerline, shore)) 
FROM streams, lakes 
WHERE streams.name = 'Cam Stream' 
AND lakes.name = 'Blue Lake'; 
1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345

--echo # 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'; 

#--echo # Conformance Item T49 
SELECT AsText(ST_Union(shore, boundary)) 
FROM lakes, named_places 
WHERE lakes.name = 'Blue Lake' 
AND named_places.name = 'Goose Island'; 

--echo # Conformance Item T50 
SELECT AsText(ST_SymDifference(shore, boundary)) 
FROM lakes, named_places 
WHERE lakes.name = 'Blue Lake' 
AND named_places.name = 'Ashton'; 

--echo # Conformance Item T51 
SELECT count(*) 
FROM buildings, bridges 
WHERE ST_Contains(ST_Buffer(bridges.position, 15.0), buildings.footprint) = 1; 

1346 1347 1348 1349
--echo # Conformance Item T52 
SELECT AsText(ConvexHull(shore)) 
FROM lakes 
WHERE lakes.name = 'Blue Lake'; 
1350 1351

DROP DATABASE gis_ogs;
1352
USE test;
1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367

--echo #
--echo # BUG #1043845 st_distance() results are incorrect depending on variable order
--echo #

select st_distance(geomfromtext('LINESTRING(-95.9673005697771 36.13509598461,
                                            -95.9673057475387 36.1344478941074,
                                            -95.9673063519371 36.134484524621,
                                            -95.9673049102515 36.1343976584193)'),
  geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)')) ;
select st_distance(geomfromtext('point(-95.96269500000000000000 36.14181833333330000000)'),
  geomfromtext('LINESTRING(-95.9673005697771 36.13509598461,
                           -95.9673057475387 36.1344478941074,
                           -95.9673063519371 36.134484524621,
                           -95.9673049102515 36.1343976584193) ')) ;
1368

1369
--echo #
1370
--echo # MDEV-4310 geometry function equals hangs forever.
1371
--echo #
1372 1373 1374 1375
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)"));
1376
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
1377
select equals(`a`,convert(`a` using utf8)) from `t1`;
1378
select equals(`a`,left(`a`,23)) from `t1`;
1379
drop table t1;
1380

1381 1382 1383 1384
--echo #
--echo # MDEV-6883 ST_WITHIN crashes server if (0,0) is matched to POLYGON((0 0))
--echo #
select st_within(GeomFromText('Polygon((0 0))'), Point(0,0));
Sergei Golubchik's avatar
Sergei Golubchik committed
1385

Sergei Golubchik's avatar
Sergei Golubchik committed
1386
--echo End of 5.3 tests
1387

1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399
--echo #
--echo # Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE
--echo #

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;

1400
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
1401 1402 1403 1404 1405 1406
SELECT 1 FROM g1 
FORCE INDEX(i) WHERE a = date_sub(now(), interval 2808.4 year_month)
;

DROP TABLE g1;

1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417
--echo #
--echo # Bug#13013970 MORE CRASHES IN FIELD_BLOB::GET_KEY_IMAGE
--echo #

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) ;

DROP TABLE g1;
1418

1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436
--echo #
--echo # Bug#16451878 GEOMETRY QUERY CRASHES SERVER
--echo #

--echo # should not crash
SELECT ASTEXT(0x0100000000030000000100000000000010);

--echo #should not crash
SELECT ENVELOPE(0x0100000000030000000100000000000010);

--echo #should not crash
SELECT
  GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffff0000, 1);

--echo #should not crash
SELECT
  GEOMETRYN(0x0100000000070000000100000001030000000200000000000000ffffff0f, 1);

1437 1438 1439 1440 1441 1442 1443 1444 1445
--echo #
--echo # MDEV-3819 missing constraints for spatial column types
--echo #

create table t1 (pt point);
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
insert into t1 values(Geomfromtext('POLYGON((1 1, 2 2, 2 1, 1 1))'));
drop table t1;

1446 1447 1448 1449 1450
#
# MDEV-7516 Assertion `!cur_p->event' failed in Gcalc_scan_iterator::arrange_event(int, int)
#
SELECT st_astext(ST_Buffer(ST_PolygonFromText('POLYGON((3 5, 2 4, 2 5, 3 5))'), -100));

1451 1452 1453 1454 1455 1456 1457 1458 1459
#
# MDEV-7779 View definition changes upon creation
#
CREATE VIEW v1 AS SELECT POINT(1,1) AS p;
SHOW CREATE VIEW v1;
SELECT ASTEXT(p) FROM v1;
DROP VIEW v1;

# --echo End of 5.5 tests
1460 1461 1462 1463 1464 1465 1466 1467 1468 1469

--echo #
--echo # Start of 10.0 tests
--echo #

--echo #
--echo # MDEV-12495 Conditional jump depends on uninitialised value for: SELECT NULL UNION geom_expression
--echo #
SELECT AsText(g) FROM (SELECT NULL AS g UNION SELECT Point(1,1)) AS t1;

1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484
--echo #
--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery
--echo #

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;
SELECT LENGTH(CONCAT(t2,'-',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub;
SELECT LENGTH(CONCAT(t2,'--',t2)) c2 FROM (SELECT ST_BUFFER(POINT(x,y), 0) t2 FROM t1) sub;
DROP TABLE t1;
SET optimizer_switch=@save_optimizer_switch;


1485 1486 1487
--echo #
--echo # End 10.0 tests
--echo #
1488 1489 1490 1491 1492 1493

SHOW CREATE TABLE information_schema.geometry_columns;
SHOW CREATE TABLE information_schema.spatial_ref_sys;

create table t1(g GEOMETRY, pt POINT);
create table t2(g LINESTRING, pl POLYGON);
1494
select * from information_schema.geometry_columns where f_table_schema='test';
1495 1496
drop table t1, t2;

1497 1498
--echo 10.1 tests
create table t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101, pt POINT(8,2), pg GEOMETRY REF_SYSTEM_ID=102);
1499
SELECT SRID from information_schema.geometry_columns WHERE f_table_schema='test' and G_TABLE_NAME='t1';
1500 1501
drop table t1;

Alexander Barkov's avatar
Alexander Barkov committed
1502 1503 1504 1505
-- echo # Expect an int(1) column to be created
CREATE TABLE t1 AS SELECT CONTAINS(NULL, NULL);
SHOW CREATE TABLE t1;
DROP TABLE t1;
1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524


--echo #
--echo # MDEV-7334 valgrind warning "unitialized bytes" in 10.1.
--echo #
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';
drop table t1;

1525 1526 1527 1528 1529 1530
--echo #
--echo # MDEV-7510 GIS: IsRing returns false for a primitive triangle.
--echo #
select ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,0 0)'));
select ST_IsRing(ST_LineFromText('LINESTRING(0 0,0 10,10 10,-10 -10, 0 -10, 0 0)'));

1531 1532 1533 1534 1535
--echo #
--echo # MDEV-7514 GIS: PointOnSurface returns NULL instead of the point.
--echo #
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))')));

1536 1537 1538 1539 1540 1541 1542 1543 1544
--echo #
--echo # MDEV-7529 GIS: ST_Relate returns unexpected results for POINT relations
--echo #
select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'T*F**FFF*') AS equals;
select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'T*****FF*') AS contains;
select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'T*F**F***') AS within;
select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(1 1)'),'FF*FF****') as disjoint;
select ST_Relate(ST_PointFromText('POINT(0 0)'),ST_PointFromText('POINT(0 0)'),'FF*FF****') as disjoint;

1545 1546 1547 1548 1549 1550 1551

--echo #
--echo # MDEV-7528 GIS: Functions return NULL instead of specified -1 for NULL arguments.
--echo #

select ST_IsRing(NULL);

1552 1553 1554 1555 1556 1557 1558
--echo #
--echo # MDEV-8675 Different results of GIS functions on NULL vs NOT NULL columns
--echo #
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;
DROP TABLE t1,t2;
1559 1560 1561 1562 1563

--echo #
--echo # Start of 10.2 tests
--echo #

1564 1565 1566 1567 1568 1569 1570 1571
#
# Item_func_spatial_collection::print()
#
create view v1 as select AsWKT(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))));
show create view v1;
select * from v1;
drop view v1;

1572
--echo #
Alexander Barkov's avatar
Alexander Barkov committed
1573
--echo # MDEV-10134 Add full support for DEFAULT
1574 1575 1576
--echo #

CREATE TABLE t1 (a POINT, x DOUBLE DEFAULT x(a), y DOUBLE DEFAULT y(a));
Alexander Barkov's avatar
Alexander Barkov committed
1577
SHOW CREATE TABLE t1;
1578 1579 1580 1581 1582
INSERT INTO t1 (a) VALUES (Point(1,2));
SELECT x,y FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (g GEOMETRY, area DOUBLE DEFAULT ST_AREA(g));
Alexander Barkov's avatar
Alexander Barkov committed
1583
SHOW CREATE TABLE t1;
1584 1585 1586 1587 1588
INSERT INTO t1 (g) VALUES (GeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0))'));
SELECT area FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (g GEOMETRY, length DOUBLE DEFAULT ST_LENGTH(g));
Alexander Barkov's avatar
Alexander Barkov committed
1589
SHOW CREATE TABLE t1;
1590 1591 1592 1593 1594
INSERT INTO t1 (g) VALUES (GeomFromText('LINESTRING(0 0,20 0,20 20,0 20,0 0)'));
SELECT length FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (g POINT, distance DOUBLE DEFAULT ST_DISTANCE(g, POINT(0,0)));
Alexander Barkov's avatar
Alexander Barkov committed
1595
SHOW CREATE TABLE t1;
1596 1597 1598 1599
INSERT INTO t1 (g) VALUES (Point(1,0));
SELECT distance FROM t1;
DROP TABLE t1;

1600
CREATE TABLE t1 (a TEXT, g GEOMETRY DEFAULT GeomFromText(a));
Alexander Barkov's avatar
Alexander Barkov committed
1601
SHOW CREATE TABLE t1;
1602 1603 1604 1605 1606
INSERT INTO t1 (a) VALUES ('point(1 1)');
SELECT AsText(g) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (x INT, y INT, g GEOMETRY DEFAULT POINT(x,y));
Alexander Barkov's avatar
Alexander Barkov committed
1607
SHOW CREATE TABLE t1;
1608 1609 1610 1611 1612
INSERT INTO t1 (x,y) VALUES (10,20);
SELECT AsText(g) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT PointN(a,2));
Alexander Barkov's avatar
Alexander Barkov committed
1613
SHOW CREATE TABLE t1;
1614 1615 1616 1617 1618
INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,2 2,3 3)'));
SELECT AsText(b) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT StartPoint(a));
Alexander Barkov's avatar
Alexander Barkov committed
1619
SHOW CREATE TABLE t1;
1620 1621 1622 1623 1624
INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,2 2,3 3)'));
SELECT AsText(b) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c GEOMETRY DEFAULT GeometryCollection(a,b));
Alexander Barkov's avatar
Alexander Barkov committed
1625
SHOW CREATE TABLE t1;
1626 1627 1628 1629 1630
INSERT INTO t1 (a,b) VALUES (Point(1,1), Point(2,2));
SELECT AsText(c) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT GeomFromWKB(AsBinary(a),20));
Alexander Barkov's avatar
Alexander Barkov committed
1631
SHOW CREATE TABLE t1;
1632 1633 1634 1635 1636
INSERT INTO t1 (a) VALUES (GeomFromText('POINT(1 1)', 10));
SELECT AsText(a), SRID(a), AsText(b), SRID(b) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT BOUNDARY(a));
Alexander Barkov's avatar
Alexander Barkov committed
1637
SHOW CREATE TABLE t1;
1638 1639 1640 1641 1642
INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))'));
SELECT AsText(b) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT BUFFER(a,10));
Alexander Barkov's avatar
Alexander Barkov committed
1643
SHOW CREATE TABLE t1;
1644 1645 1646 1647 1648
INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))'));
SELECT GeometryType(b) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT CENTROID(a));
Alexander Barkov's avatar
Alexander Barkov committed
1649
SHOW CREATE TABLE t1;
1650 1651 1652 1653 1654
INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))'));
SELECT AsText(b) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT ENVELOPE(a));
Alexander Barkov's avatar
Alexander Barkov committed
1655
SHOW CREATE TABLE t1;
1656 1657 1658 1659 1660
INSERT INTO t1 (a) VALUES (GeomFromText('LineString(1 1,4 4)'));
SELECT AsText(b) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT PointOnSurface(a));
Alexander Barkov's avatar
Alexander Barkov committed
1661
SHOW CREATE TABLE t1;
1662 1663 1664 1665 1666
INSERT INTO t1 (a) VALUES (GeomFromText('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))'));
SELECT GeometryType(b) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY DEFAULT Point(1,1), c GEOMETRY DEFAULT ST_UNION(a,b));
Alexander Barkov's avatar
Alexander Barkov committed
1667
SHOW CREATE TABLE t1;
1668 1669 1670 1671 1672
INSERT INTO t1 (a) VALUES (Point(0,0));
SELECT AsText(c) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b VARCHAR(20) DEFAULT GeometryType(a));
Alexander Barkov's avatar
Alexander Barkov committed
1673
SHOW CREATE TABLE t1;
1674 1675 1676 1677 1678
INSERT INTO t1 (a) VALUES (Point(0, 0));
SELECT b FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsSimple(a));
Alexander Barkov's avatar
Alexander Barkov committed
1679
SHOW CREATE TABLE t1;
1680 1681 1682 1683 1684
INSERT INTO t1 (a) VALUES (Point(0, 0));
SELECT b FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsEmpty(a));
Alexander Barkov's avatar
Alexander Barkov committed
1685
SHOW CREATE TABLE t1;
1686 1687 1688 1689 1690
INSERT INTO t1 (a) VALUES (Point(0, 0));
SELECT b FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsRing(a));
Alexander Barkov's avatar
Alexander Barkov committed
1691
SHOW CREATE TABLE t1;
1692 1693 1694 1695 1696
INSERT INTO t1 (a) VALUES (GeomFromText('LineString(0 0,0 1,1 1,1 0,0 0)'));
SELECT b FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT IsClosed(a));
Alexander Barkov's avatar
Alexander Barkov committed
1697
SHOW CREATE TABLE t1;
1698 1699 1700 1701 1702
INSERT INTO t1 (a) VALUES (GeomFromText('LineString(0 0,0 1,1 1,1 0,0 0)'));
SELECT b FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT Dimension(a));
Alexander Barkov's avatar
Alexander Barkov committed
1703
SHOW CREATE TABLE t1;
1704 1705 1706 1707 1708
INSERT INTO t1 (a) VALUES (Buffer(Point(1,1),1));
SELECT b FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumGeometries(a));
Alexander Barkov's avatar
Alexander Barkov committed
1709
SHOW CREATE TABLE t1;
1710 1711 1712 1713 1714
INSERT INTO t1 (a) VALUES (ST_UNION(Point(1,1),Point(0,0)));
SELECT b FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumInteriorRings(a));
Alexander Barkov's avatar
Alexander Barkov committed
1715
SHOW CREATE TABLE t1;
1716 1717 1718 1719 1720
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;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT NumPoints(a));
Alexander Barkov's avatar
Alexander Barkov committed
1721
SHOW CREATE TABLE t1;
1722 1723 1724 1725 1726
INSERT INTO t1 (a) VALUES (LineString(Point(1,1),Point(0,0)));
SELECT b FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b INT DEFAULT SRID(a));
Alexander Barkov's avatar
Alexander Barkov committed
1727
SHOW CREATE TABLE t1;
1728 1729 1730 1731 1732
INSERT INTO t1 (a) VALUES (GeomFromText('Point(1 1)', 100));
SELECT b FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT MBRDisjoint(a,b));
Alexander Barkov's avatar
Alexander Barkov committed
1733
SHOW CREATE TABLE t1;
1734 1735 1736 1737 1738
INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1));
SELECT c FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT ST_Disjoint(a,b));
Alexander Barkov's avatar
Alexander Barkov committed
1739
SHOW CREATE TABLE t1;
1740 1741 1742 1743 1744
INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1));
SELECT c FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a GEOMETRY, b GEOMETRY, c INT DEFAULT ST_Relate(a,b,'T*F**FFF*'));
Alexander Barkov's avatar
Alexander Barkov committed
1745
SHOW CREATE TABLE t1;
1746 1747 1748 1749
INSERT INTO t1 (a,b) VALUES (Point(1,1),Point(1,1));
SELECT c FROM t1;
DROP TABLE t1;

1750
--echo #
1751
--echo # Start of 10.3 tests
1752
--echo #
1753 1754 1755

--echo #
--echo # MDEV-11478 Result data type aggregation for pluggable data types
1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831
--echo #

DELIMITER $$;
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;
$$
DELIMITER ;$$

--disable_query_log
CALL p1('CREATE TABLE t1 (a CHAR(10), b Point)');
CALL p1('CREATE TABLE t1 (a VARCHAR(10), b Point)');
CALL p1('CREATE TABLE t1 (a TINYTEXT, b Point)');
CALL p1('CREATE TABLE t1 (a TEXT, b Point)');
CALL p1('CREATE TABLE t1 (a MEDIUMTEXT, b Point)');
CALL p1('CREATE TABLE t1 (a LONGTEXT, b Point)');

CALL p1('CREATE TABLE t1 (a TINYINT, b Point)');
CALL p1('CREATE TABLE t1 (a SMALLINT, b Point)');
CALL p1('CREATE TABLE t1 (a MEDIUMINT, b Point)');
CALL p1('CREATE TABLE t1 (a INT, b Point)');
CALL p1('CREATE TABLE t1 (a BIGINT, b Point)');
CALL p1('CREATE TABLE t1 (a FLOAT, b Point)');
CALL p1('CREATE TABLE t1 (a DOUBLE, b Point)');
CALL p1('CREATE TABLE t1 (a DECIMAL(10,2), b Point)');
CALL p1('CREATE TABLE t1 (a BIT(8), b Point)');

CALL p1('CREATE TABLE t1 (a TIME, b Point)');
CALL p1('CREATE TABLE t1 (a DATE, b Point)');
CALL p1('CREATE TABLE t1 (a DATETIME, b Point)');
CALL p1('CREATE TABLE t1 (a TIMESTAMP, b Point)');
CALL p1('CREATE TABLE t1 (a YEAR, b Point)');

--echo # This creates BLOB with hybrid functions, but fails on error with UNION (MDEV-11458)
CALL p1('CREATE TABLE t1 (a ENUM(0x61), b Point)');
CALL p1('CREATE TABLE t1 (a SET(0x61), b Point)');
--enable_query_log

CREATE TABLE t1 AS SELECT COALESCE(NULL, Point(1,1));
SHOW CREATE TABLE t1;
DROP TABLE t1;
CREATE TABLE t1 AS SELECT NULL UNION SELECT Point(1,1);
SHOW CREATE TABLE t1;
DROP TABLE t1;

DROP PROCEDURE p1;
DROP PROCEDURE p2;


1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898
--echo #
--echo # MDEV-11692 Comparison data type aggregation for pluggable data types
--echo #

DELIMITER $$;
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;
$$
DELIMITER ;$$

CALL p1('CREATE TABLE t1 (a CHAR(10), b Point, c Point)');
CALL p1('CREATE TABLE t1 (a VARCHAR(10), b Point, c Point)');
CALL p1('CREATE TABLE t1 (a TINYTEXT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a TEXT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a MEDIUMTEXT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a LONGTEXT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a ENUM("a","b"), b Point, c Point)');

CALL p1('CREATE TABLE t1 (a TINYINT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a SMALLINT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a MEDIUMINT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a INT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a BIGINT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a FLOAT, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a DOUBLE, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a DECIMAL(10,2), b Point, c Point)');
CALL p1('CREATE TABLE t1 (a BIT(8), b Point, c Point)');

CALL p1('CREATE TABLE t1 (a TIME, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a DATE, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a DATETIME, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a TIMESTAMP, b Point, c Point)');
CALL p1('CREATE TABLE t1 (a YEAR, b Point, c Point)');

CALL p1('CREATE TABLE t1 (a Point, b Point, c Point)');


DROP PROCEDURE p1;
DROP PROCEDURE p2;

1899

1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969
--echo #
--echo # MDEV-12506 Split Item_func_min_max::fix_length_and_dec() into methods in Type_handler
--echo #

DELIMITER $$;
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;
$$
DELIMITER ;$$

--disable_query_log
CALL p1('CREATE TABLE t1 (a CHAR(10), b Point)');
CALL p1('CREATE TABLE t1 (a VARCHAR(10), b Point)');
CALL p1('CREATE TABLE t1 (a TINYTEXT, b Point)');
CALL p1('CREATE TABLE t1 (a TEXT, b Point)');
CALL p1('CREATE TABLE t1 (a MEDIUMTEXT, b Point)');
CALL p1('CREATE TABLE t1 (a LONGTEXT, b Point)');

CALL p1('CREATE TABLE t1 (a TINYINT, b Point)');
CALL p1('CREATE TABLE t1 (a SMALLINT, b Point)');
CALL p1('CREATE TABLE t1 (a MEDIUMINT, b Point)');
CALL p1('CREATE TABLE t1 (a INT, b Point)');
CALL p1('CREATE TABLE t1 (a BIGINT, b Point)');
CALL p1('CREATE TABLE t1 (a FLOAT, b Point)');
CALL p1('CREATE TABLE t1 (a DOUBLE, b Point)');
CALL p1('CREATE TABLE t1 (a DECIMAL(10,2), b Point)');
CALL p1('CREATE TABLE t1 (a BIT(8), b Point)');

CALL p1('CREATE TABLE t1 (a TIME, b Point)');
CALL p1('CREATE TABLE t1 (a DATE, b Point)');
CALL p1('CREATE TABLE t1 (a DATETIME, b Point)');
CALL p1('CREATE TABLE t1 (a TIMESTAMP, b Point)');
CALL p1('CREATE TABLE t1 (a YEAR, b Point)');

--echo # This LEAST(ENUM,GEOMETRY) creates BLOB, but fails on error with UNION (see MDEV-12503)
CALL p1('CREATE TABLE t1 (a ENUM(0x61), b Point)');
CALL p1('CREATE TABLE t1 (a SET(0x61), b Point)');
--enable_query_log

CREATE TABLE t1 AS SELECT LEAST(NULL, Point(1,1));
SHOW CREATE TABLE t1;
DROP TABLE t1;

DROP PROCEDURE p1;
DROP PROCEDURE p2;

1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980
--echo #
--echo # MDEV-12001 Split Item_func_round::fix_length_and_dec to virtual methods in Type_handler
--echo #

CREATE TABLE t1 (a GEOMETRY);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ROUND(a) FROM t1;
DROP TABLE t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ROUND(POINT(1,1));

1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015

--echo #
--echo # MDEV-12199 Split Item_func_{abs|neg|int_val}::fix_length_and_dec() into methods in Type_handler
--echo #

CREATE TABLE t1 (a GEOMETRY);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT -a FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ABS(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CEILING(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT FLOOR(a) FROM t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT -COALESCE(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ABS(COALESCE(a)) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CEILING(COALESCE(a)) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT FLOOR(COALESCE(a)) FROM t1;
DROP TABLE t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT -POINT(1,1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ABS(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CEILING(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT FLOOR(POINT(1,1));

2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 2049 2050 2051 2052

--echo #
--echo # MDEV-12239 Add Type_handler::Item_sum_{sum|avg|variance}_fix_length_and_dec()
--echo #

CREATE TABLE t1 (a GEOMETRY);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT SUM(POINT(1,1)) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT SUM(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT SUM(COALESCE(a)) FROM t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT AVG(POINT(1,1)) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT AVG(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT AVG(COALESCE(a)) FROM t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT VARIANCE(POINT(1,1)) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT VARIANCE(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT VARIANCE(COALESCE(a)) FROM t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT STDDEV(POINT(1,1)) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT STDDEV(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT STDDEV(COALESCE(a)) FROM t1;

DROP TABLE t1;

2053 2054 2055 2056 2057 2058 2059 2060 2061 2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115
--echo #
--echo # MDEV-12303 Add Type_handler::Item_xxx_fix_length_and_dec() for CAST classes
--echo #

CREATE TABLE t1 (a GEOMETRY);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(POINT(1,1) AS SIGNED) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(POINT(1,1) AS UNSIGNED) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(POINT(1,1) AS DOUBLE) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(POINT(1,1) AS DECIMAL(10,1)) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(POINT(1,1) AS CHAR) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(POINT(1,1) AS TIME) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(POINT(1,1) AS DATE) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(POINT(1,1) AS DATETIME) FROM t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(a AS SIGNED) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(a AS UNSIGNED) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(a AS DOUBLE) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(a AS DECIMAL(10,1)) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(a AS CHAR) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(a AS TIME) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(a AS DATE) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(a AS DATETIME) FROM t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(COALESCE(a) AS SIGNED) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(COALESCE(a) AS UNSIGNED) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(COALESCE(a) AS DOUBLE) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(COALESCE(a) AS DECIMAL(10,1)) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(COALESCE(a) AS CHAR) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(COALESCE(a) AS TIME) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(COALESCE(a) AS DATE) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT CAST(COALESCE(a) AS DATETIME) FROM t1;

# CAST from GEOMETRY to BINARY is OK
SELECT LENGTH(CAST(POINT(1,1) AS BINARY)) FROM t1;
SELECT LENGTH(CAST(a AS BINARY)) FROM t1;
SELECT LENGTH(CAST(COALESCE(a) AS BINARY)) FROM t1;

DROP TABLE t1;
2116

2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 2144 2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190
--echo #
--echo # MDEV-12238 Add Type_handler::Item_func_{plus|minus|mul|div|mod}_fix_length_and_dec()
--echo #

CREATE TABLE t1 (a GEOMETRY);

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT POINT(1,1) + 1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT POINT(1,1) - 1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT POINT(1,1) * 1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT POINT(1,1) / 1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT POINT(1,1) MOD 1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 + POINT(1,1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 - POINT(1,1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 * POINT(1,1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 / POINT(1,1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 MOD POINT(1,1);

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT a + 1 FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT a - 1 FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT a * 1 FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT a / 1 FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT a MOD 1 FROM t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 + a FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 - a FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 * a FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 / a FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 MOD a FROM t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT COALESCE(a) + 1 FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT COALESCE(a) - 1 FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT COALESCE(a) * 1 FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT COALESCE(a) / 1 FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT COALESCE(a) MOD 1 FROM t1;

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 + COALESCE(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 - COALESCE(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 * COALESCE(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 / COALESCE(a) FROM t1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT 1 MOD COALESCE(a) FROM t1;

DROP TABLE t1;

2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217 2218 2219 2220 2221 2222 2223 2224
--echo #
--echo # MDEV-12514 Split Item_temporal_func::fix_length_and_dec()
--echo #

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT DATE_ADD(POINT(1,1), INTERVAL 10 DAY);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT DATE_SUB(POINT(1,1), INTERVAL 10 DAY);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT POINT(1,1) + INTERVAL 10 DAY;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT POINT(1,1) - INTERVAL 10 DAY;
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT INTERVAL 10 DAY + POINT(1,1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT INTERVAL 10 DAY + POINT(1,1);

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT ADDTIME(POINT(1,1), '10:10:10');
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT ADDTIME('10:10:10', POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT ADDTIME(POINT(1,1), TIME'10:10:10');
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT ADDTIME(TIME'10:10:10', POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT ADDTIME(POINT(1,1), TIMESTAMP'2001-01-01 10:10:10');
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT ADDTIME(TIMESTAMP'2001-01-01 10:10:10', POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT STR_TO_DATE(POINT(1,1),'%M %d,%Y');
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
SELECT STR_TO_DATE('2001-01-01', POINT(1,1));
2225

2226 2227 2228 2229 2230 2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 2252 2253 2254 2255 2256 2257 2258 2259 2260 2261 2262 2263 2264 2265 2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 2280 2281 2282 2283 2284 2285 2286 2287 2288 2289 2290 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 2322 2323 2324
--echo #
--echo # MDEV-12665 Hybrid functions do not preserve geometry type
--echo #

CREATE TABLE t1 AS SELECT
  Point(0,0) AS p0,
  COALESCE(Point(0,0)) AS p1,
  CASE WHEN 0 THEN Point(0,0) ELSE Point(1,1) END AS p2;
SHOW CREATE TABLE t1;
DROP TABLE t1;

CREATE TABLE t1 AS SELECT LEAST(Point(0,0),Point(0,0)) AS p1;
SHOW CREATE TABLE t1;
DROP TABLE t1;

CREATE TABLE t1 (
  c_geometry GEOMETRY,
  c_point POINT,
  c_linestring LINESTRING,
  c_polygon POLYGON,
  c_multipoint  MULTIPOINT,
  c_multilinestring MULTILINESTRING,
  c_multipolygon MULTIPOLYGON,
  c_geometrycollection GEOMETRYCOLLECTION
);

CREATE TABLE t2 AS SELECT
  COALESCE(NULL, c_geometry),
  COALESCE(NULL, c_point),
  COALESCE(NULL, c_linestring),
  COALESCE(NULL, c_polygon),
  COALESCE(NULL, c_multipoint),
  COALESCE(NULL, c_multilinestring),
  COALESCE(NULL, c_multipolygon),
  COALESCE(NULL, c_geometrycollection)
FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t2;

CREATE TABLE t2 AS SELECT
  COALESCE(c_geometry, NULL),
  COALESCE(c_point, NULL),
  COALESCE(c_linestring, NULL),
  COALESCE(c_polygon, NULL),
  COALESCE(c_multipoint, NULL),
  COALESCE(c_multilinestring, NULL),
  COALESCE(c_multipolygon, NULL),
  COALESCE(c_geometrycollection, NULL)
FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t2;

CREATE TABLE t2 AS SELECT
  COALESCE(c_geometry, c_geometry),
  COALESCE(c_point, c_point),
  COALESCE(c_linestring, c_linestring),
  COALESCE(c_polygon, c_polygon),
  COALESCE(c_multipoint, c_multipoint),
  COALESCE(c_multilinestring, c_multilinestring),
  COALESCE(c_multipolygon, c_multipolygon),
  COALESCE(c_geometrycollection, c_geometrycollection)
FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t2;

DROP TABLE t1;


--echo #
--echo # MDEV-12560 Wrong data type for SELECT NULL UNION SELECT Point(1,1)
--echo #

CREATE TABLE t1 AS SELECT NULL AS c1 UNION SELECT POINT(1,1);
SHOW CREATE TABLE t1;
DROP TABLE t1;

DELIMITER $$;
CREATE PROCEDURE p1(name TEXT)
BEGIN
  EXECUTE IMMEDIATE CONCAT('CREATE TABLE t1 (a ', name, ')');
  CREATE TABLE t2 AS
    SELECT a AS a1, a    AS a2, NULL AS a3 FROM t1 UNION
    SELECT a AS a1, NULL AS a2, a    AS a3 FROM t1;
  SHOW CREATE TABLE t2;
  DROP TABLE t2;
  DROP TABLE t1;
END;
$$
DELIMITER ;$$
CALL p1('geometry');
CALL p1('point');
CALL p1('linestring');
CALL p1('polygon');
CALL p1('multipoint');
CALL p1('multilinestring');
CALL p1('multipolygon');
CALL p1('geometrycollection');
DROP PROCEDURE p1;

2325 2326 2327 2328 2329 2330 2331 2332 2333 2334 2335 2336
--echo #
--echo # MDEV-12798 Item_param does not preserve exact field type in EXECUTE IMMEDIATE 'CREATE TABLE AS SELECT ?' USING POINT(1,1)
--echo #
EXECUTE IMMEDIATE 'CREATE TABLE t1 AS SELECT ?' USING POINT(1,1);
SHOW CREATE TABLE t1;
DROP TABLE t1;

PREPARE stmt FROM 'CREATE OR REPLACE TABLE t1 AS SELECT ?';
EXECUTE stmt USING POINT(1,1);
SHOW CREATE TABLE t1;
DROP TABLE t1;

2337 2338 2339 2340 2341 2342 2343 2344 2345 2346 2347 2348 2349 2350 2351 2352 2353 2354 2355
--echo #
--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
--echo #

--enable_metadata
--disable_ps_protocol
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));
--enable_ps_protocol
--disable_metadata


2356 2357 2358 2359 2360 2361 2362 2363 2364 2365 2366 2367 2368 2369 2370 2371 2372 2373 2374 2375 2376 2377 2378 2379 2380 2381 2382 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 2408 2409 2410 2411 2412 2413 2414 2415 2416 2417 2418 2419 2420 2421 2422 2423 2424 2425 2426 2427 2428 2429 2430 2431 2432 2433 2434 2435 2436 2437 2438 2439 2440 2441 2442 2443 2444 2445 2446 2447 2448 2449 2450 2451 2452 2453 2454 2455 2456 2457 2458 2459 2460 2461 2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 2478 2479 2480 2481 2482 2483 2484 2485 2486 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 2502 2503 2504 2505 2506 2507 2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 2530 2531 2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 2548 2549 2550 2551 2552 2553 2554 2555 2556 2557 2558 2559 2560 2561 2562 2563 2564 2565 2566 2567 2568 2569 2570 2571 2572 2573 2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 2596 2597 2598 2599 2600 2601 2602 2603 2604 2605 2606 2607 2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 2629 2630 2631 2632 2633 2634 2635 2636 2637 2638 2639 2640 2641 2642 2643 2644 2645 2646 2647 2648 2649 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 2665 2666 2667 2668 2669 2670 2671 2672 2673 2674 2675 2676 2677 2678 2679 2680 2681 2682 2683 2684 2685 2686 2687 2688 2689 2690 2691
--echo #
--echo # MDEV-12803 Improve function parameter data type control
--echo #

--echo #
--echo # Item_geometry_func_args_geometry
--echo #

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_CENTROID(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ENVELOPE(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_BOUNDARY(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_STARTPOINT(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_POINTONSURFACE(1);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_POINTN(1,1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_POINTN(LineString(Point(1,1)),Point(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_BUFFER(1, 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_BUFFER(Point(1,1), Point(1,1));


PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ST_ENVELOPE(?) AS g';
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
EXECUTE stmt USING 1;
EXECUTE stmt USING POINT(1,1);
SHOW CREATE TABLE t1;
SELECT ST_ASTEXT(g) FROM t1;
DROP TABLE t1;
DEALLOCATE PREPARE stmt;

PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ST_BUFFER(?,?) AS g';
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
EXECUTE stmt USING 1,1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
EXECUTE stmt USING POINT(1,1),POINT(1,1);
EXECUTE stmt USING POINT(1,1),0;
SHOW CREATE TABLE t1;
SELECT ST_GEOMETRYTYPE(g) FROM t1;
DROP TABLE t1;
DEALLOCATE PREPARE stmt;

--echo #
--echo # Functions created GEOMETRY from non-GEOMETRY
--echo #

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYFROMTEXT(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYFROMTEXT(Point(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYFROMTEXT(Point(1,1), 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYFROMTEXT('test', Point(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYFROMWKB(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYFROMWKB(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYFROMWKB(1, 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYFROMWKB(Point(1,1), Point(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMFROMGEOJSON(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMFROMGEOJSON(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMFROMGEOJSON(1,1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMFROMGEOJSON(Point(1,1), Point(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POINT(ROW(1,1),1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POINT(POINT(1,1),1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POINT(1,ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POINT(1,POINT(1,1));


PREPARE stmt FROM 'CREATE TABLE t1 AS SELECT ST_GEOMFROMTEXT(?,?) AS g';
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
EXECUTE stmt USING 1,1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
EXECUTE stmt USING POINT(1,1),POINT(1,1);
EXECUTE stmt USING 'POINT(1 1)',1;
SHOW CREATE TABLE t1;
SELECT ST_ASTEXT(g), SRID(g) FROM t1;
DROP TABLE t1;
DEALLOCATE PREPARE stmt;



--echo # Item_func_spatial_collection

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT GEOMETRYCOLLECTION(ROW(1,1), POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT GEOMETRYCOLLECTION(POINT(1,1), ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT GEOMETRYCOLLECTION(1, POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT GEOMETRYCOLLECTION(POINT(1,1), 1);

--echo # Item_func_spatial_operation

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_UNION(ROW(1,1), POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_UNION(POINT(1,1), ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_UNION(1, POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_UNION(POINT(1,1), 1);

--echo # Item_binary_func_args_geometry

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASWKB(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASWKB(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASWKB('test');


--echo # Item_real_func_args_geometry

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_X(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_X(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_X('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_Y(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_Y(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_Y('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_AREA(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_AREA(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_AREA('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_LENGTH(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_LENGTH(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_LENGTH('test');


--echo # Item_real_func_geometry_args_geometry_geometry

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_DISTANCE(ROW(1,1), 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_DISTANCE(1, 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_DISTANCE('test', 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_DISTANCE(POINT(1,1), ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_DISTANCE(POINT(1,1), 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_DISTANCE(POINT(1,1), 'test');


--echo # Item_int_func_args_geometry

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISSIMPLE(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISSIMPLE(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISSIMPLE('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISRING(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISRING(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISRING('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISCLOSED(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISCLOSED(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISCLOSED('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_DIMENSION(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_DIMENSION(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_DIMENSION('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_NUMGEOMETRIES(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_NUMGEOMETRIES(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_NUMGEOMETRIES('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_NUMINTERIORRINGS(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_NUMINTERIORRINGS(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_NUMINTERIORRINGS('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_NUMPOINTS(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_NUMPOINTS(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_NUMPOINTS('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_SRID(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_SRID(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_SRID('test');


--echo # Item_bool_func_args_geometry

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISEMPTY(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISEMPTY(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ISEMPTY('test');

--echo # Item_bool_func_geometry_args_geometry_geometry

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(ROW(1,1), POINT(1,1), 'T*F**FFF*');
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(POINT(1,1), ROW(1,1), 'T*F**FFF*');
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(1, POINT(1,1), 'T*F**FFF*');
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(POINT(1,1), 1, 'T*F**FFF*');
SELECT ST_RELATE(Point(1,1),Point(1,1),'T*F**FFF*') AS equals;
SELECT ST_RELATE(Point(1,1),Point(1,1),NULL);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(Point(1,1),Point(1,1),ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(Point(1,1),Point(1,1),1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(Point(1,1),Point(1,1),1.0);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(Point(1,1),Point(1,1),1e0);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(Point(1,1),Point(1,1),TIME'10:10:10');
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(Point(1,1),Point(1,1),DATE'2010-01-01');
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(Point(1,1),Point(1,1),TIMESTAMP'2010-01-01 10:10:10');
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_RELATE(Point(1,1),Point(1,1),Point(1,1));


--echo # Item_str_ascii_func_args_geometry

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASTEXT(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASTEXT(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASTEXT('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYTYPE(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYTYPE(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_GEOMETRYTYPE('test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASGEOJSON(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASGEOJSON(1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASGEOJSON('test');
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASGEOJSON(POINT(1,1), POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_ASGEOJSON(POINT(1,1), 1, POINT(1,1));

--echo # Item_func_spatial_rel

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_TOUCHES(ROW(1,1), POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_TOUCHES(POINT(1,1), ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_TOUCHES(1, POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_TOUCHES(POINT(1,1), 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_TOUCHES('test', POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ST_TOUCHES(POINT(1,1), 'test');

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT MBRTOUCHES(ROW(1,1), POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT MBRTOUCHES(POINT(1,1), ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT MBRTOUCHES(1, POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT MBRTOUCHES(POINT(1,1), 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT MBRTOUCHES('test', POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT MBRTOUCHES(POINT(1,1), 'test');

2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 2705 2706 2707 2708 2709 2710 2711 2712 2713 2714 2715 2716 2717 2718 2719 2720 2721 2722 2723 2724 2725 2726 2727 2728 2729 2730 2731 2732 2733 2734 2735 2736 2737 2738 2739 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 2755 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 2770 2771 2772 2773 2774 2775 2776 2777 2778 2779 2780 2781 2782 2783 2784 2785 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796

--echo #
--echo # MDEV-13964 Parameter data type control for Item_real_func
--echo #

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT EXP(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LN(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG2(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG10(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT SQRT(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ACOS(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ASIN(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT COS(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT SIN(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT TAN(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT COT(POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG(POINT(1,1),POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG(1, POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ATAN(POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ATAN(POINT(1,1),POINT(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ATAN(1, POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POW(POINT(1,1),POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT RAND(POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT RADIANS(POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT DEGREES(POINT(1,1));


--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT EXP(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LN(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG2(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG10(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT SQRT(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ACOS(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ASIN(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT COS(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT SIN(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT TAN(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT COT(ROW(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG(ROW(1,1),ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LOG(1, ROW(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ATAN(ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ATAN(ROW(1,1),ROW(1,1));
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ATAN(1, ROW(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POW(ROW(1,1),ROW(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT RAND(ROW(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT RADIANS(ROW(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT DEGREES(ROW(1,1));


2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 2819 2820 2821 2822 2823 2824 2825 2826 2827 2828 2829 2830 2831 2832 2833 2834 2835 2836 2837 2838
--echo #
--echo # MDEV-13965 Parameter data type control for Item_longlong_func
--echo #

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POINT(1,1) | 1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT 1 | POINT(1,1);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POINT(1,1) & 1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT 1 & POINT(1,1);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POINT(1,1) << 1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT 1 << POINT(1,1);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT POINT(1,1) >> 1;
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT 1 >> POINT(1,1);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT ~POINT(1,1);

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT TO_SECONDS(POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT TIMESTAMPDIFF(SECOND,POINT(1,1), 1);
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT TIMESTAMPDIFF(SECOND,1, POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT INET_ATON(POINT(1,1));

--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
SELECT LAST_INSERT_ID(POINT(1,1));


2839
--echo #
2840
--echo # End of 10.3 tests
2841
--echo #