1 files changed, 322 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb_gis/t/point_big.test b/mysql-test/suite/innodb_gis/t/point_big.test
new file mode 100644
index 00000000000..d9a00474d89
--- /dev/null
+++ b/mysql-test/suite/innodb_gis/t/point_big.test
@@ -0,0 +1,322 @@
+#********************************************************
+# wl6942: Store certain GIS data type in the row rather
+# than as BLOB.
+#
+#********************************************************
+
+--source include/have_geometry.inc
+--source include/have_innodb.inc
+
+--source include/not_embedded.inc
+--source include/big_test.inc
+--source include/not_valgrind.inc
+
+--echo #
+--echo # Test when the POINT is on B-TREE
+--echo #
+CREATE TABLE t1(fid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, p POINT, KEY(p)) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+(101, ST_PointFromText('POINT(10 10)')),
+(102, ST_PointFromText('POINT(20 10)')),
+(103, ST_PointFromText('POINT(20 20)')),
+(104, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(10 20)'))));
+
+SELECT ST_AsText(p) FROM t1;
+SELECT ST_AsText(p) FROM t1 WHERE p = ST_PointFromText('POINT(20 20)');
+
+INSERT INTO t1 VALUES
+(201, ST_PointFromText('POINT(100.32374832 101.23741821)')),
+(202, ST_PointFromText('POINT(102.43287328 100.23489233)')),
+(203, ST_PointFromText('POINT(101.43284962 100.45892392)')),
+(204, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)')))),
+(205, ST_PointFromText('POINT(101.43284962 100.45892392)')),
+(206, ST_PointFromWKB(ST_AsWKB(ST_PointFromText('POINT(103.43718640 105.248206478)'))));
+
+SELECT ST_AsText(p), COUNT(*) FROM t1 GROUP BY p;
+
+TRUNCATE t1;
+
+SELECT * FROM t1;
+
+INSERT INTO t1(p) VALUES(ST_PointFromText('POINT(10 10)')),
+(ST_PointFromText('POINT(10 20)')),
+(ST_PointFromText('POINT(10 40)')),
+(ST_PointFromText('POINT(10 60)')),
+(ST_PointFromText('POINT(20 15)')),
+(ST_PointFromText('POINT(30 10)')),
+(ST_PointFromText('POINT(40 10)')),
+(ST_PointFromText('POINT(50 10)')),
+(ST_PointFromText('POINT(60 5)')),
+(ST_PointFromText('POINT(60 10)')),
+(ST_PointFromText('POINT(60 20)')),
+(ST_PointFromText('POINT(60 100)')),
+(ST_PointFromText('POINT(100 100)')),
+(ST_PointFromText('POINT(80 60)')),
+(ST_PointFromText('POINT(81 50)')),
+(ST_PointFromText('POINT(82 70)')),
+(ST_PointFromText('POINT(83 30)')),
+(ST_PointFromText('POINT(20 100)')),
+(ST_PointFromText('POINT(150 2000)')),
+(ST_PointFromText('POINT(109 230)')),
+(ST_PointFromText('POINT(280 250)')),
+(ST_PointFromText('POINT(176 175)')),
+(ST_PointFromText('POINT(200 10)')),
+(NULL),
+(NULL);
+
+SELECT ST_AsText(p) FROM t1;
+SELECT COUNT(*) FROM t1;
+
+SELECT ST_AsText(p) FROM t1;
+SELECT COUNT(*) FROM t1;
+
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+INSERT INTO t1(p) SELECT p FROM t1;
+
+CHECK TABLE t1;
+
+SELECT COUNT(*) FROM t1;
+SELECT ST_AsText(p), COUNT(*) FROM t1 GROUP BY p;
+SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(280 250)');
+DELETE FROM t1 WHERE p = ST_PointFromText('POINT(280 250)');
+SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(280 250)');
+SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(60 5)');
+DELETE FROM t1 WHERE P = ST_PointFromText('POINT(60 5)');
+SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(60 5)');
+
+SELECT ST_AsText(p), COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(60 10)') OR p = ST_PointFromText('POINT(60 20)') OR p = ST_PointFromText('POINT(60 30)') GROUP BY p;
+UPDATE t1 SET p = ST_PointFromText('POINT(101 102)') WHERE p = ST_PointFromText('POINT(60 10)') OR p = ST_PointFromText('POINT(60 20)') OR p = ST_PointFromText('POINT(60 30)');
+SELECT ST_AsText(p), COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(60 10)') OR p = ST_PointFromText('POINT(60 20)') OR p = ST_PointFromText('POINT(60 30)') GROUP BY p;
+SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(101 102)');
+
+SELECT COUNT(*) FROM t1 WHERE p IS NULL;
+UPDATE t1 SET p = ST_PointFromText('POINT(160 160)') WHERE p IS NULL;
+SELECT COUNT(*) FROM t1 WHERE p IS NULL;
+SELECT COUNT(*) FROM t1 WHERE p = ST_PointFromText('POINT(160 160)');
+
+SELECT ST_AsText(p), COUNT(*) FROM t1 GROUP BY p;
+SELECT COUNT(*) FROM t1;
+
+CHECK TABLE t1;
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # Test when POINT is used in spatial index
+--echo #
+SET @ls1 = ST_GeomFromText('LINESTRING(0 20, 10 0)');
+SET @ls2 = ST_GeomFromText('LINESTRING(0 0, 10 20)');
+SET @ls3 = ST_GeomFromText('LINESTRING(20 -40, 21 -42)');
+SET @ls4 = ST_GeomFromText('LINESTRING(20 -42, 21 -40)');
+SET @poly1 = ST_GeomFromText('POLYGON((2 2, 2 10, 10 10, 10 2, 2 2))');
+SET @poly2 = ST_GeomFromText('POLYGON((0 0, -5 0, -4 -1, -6 -15, -3 -15, 0 0))');
+SET @poly3 = ST_GeomFromText('POLYGON((10.0 10.0, 20.5 20, 20.5 50, 32.0 64.0, 32.3 64.6, 5 60, 10 10))');
+SET @poly4 = ST_GeomFromText('POLYGON((0 10, -10 10, -10 -10, 0 -10, 0 10))');
+SET @p1 = ST_PointFromText('POINT(0 0)');
+SET @mpoly = ST_GeomFromText('MULTIPOLYGON(((3 3, 3 16, 16 16, 16 3, 3 3)), ((10 10, 10 50, 50 50, 50 10, 10 10)))');
+
+CREATE TABLE gis_point (p1 POINT NOT NULL, p2 POINT NOT NULL, SPATIAL KEY k1 (p1), SPATIAL KEY k2 (p2)) ENGINE=InnoDB;
+
+INSERT INTO gis_point VALUES
+(ST_PointFromText('POINT(1 2)'), ST_PointFromText('POINT(-1 -3)')),
+(ST_PointFromText('POINT(2 4)'), ST_PointFromText('POINT(-2 -6)')),
+(ST_PointFromText('POINT(3 6)'), ST_PointFromText('POINT(-3 -9)')),
+(ST_PointFromText('POINT(4 8)'), ST_PointFromText('POINT(-4 -12)')),
+(ST_PointFromText('POINT(5 10)'), ST_PointFromText('POINT(-5 -15)')),
+(ST_PointFromText('POINT(6 12)'), ST_PointFromText('POINT(-6 -18)')),
+(ST_PointFromText('POINT(7 14)'), ST_PointFromText('POINT(-7 -21)')),
+(ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
+(ST_PointFromText('POINT(9 18)'), ST_PointFromText('POINT(-4 2)')),
+(ST_PointFromText('POINT(10 21)'), ST_PointFromText('POINT(-6 3)')),
+(ST_PointFromText('POINT(20.5 41)'), ST_PointFromText('POINT(-8 4)')),
+(ST_PointFromText('POINT(26.25 57)'), ST_PointFromText('POINT(1 2)')),
+(ST_PointFromText('POINT(32.1234 64.2468)'), ST_PointFromText('POINT(-1 -1)'));
+
+-- echo 'The ORDER BY for spatial index will use filesort'
+--replace_column 10 #
+EXPLAIN SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
+SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point ORDER BY p1, p2;
+
+-- echo 'Try to do IDU on the table and verify the result'
+DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
+
+INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
+
+SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
+
+UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 41)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
+
+SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
+
+CHECK TABLE gis_point;
+
+-- echo ============================================================
+-- echo Use a trx to test the IDU on the table and verify the result
+-- echo ============================================================
+
+START TRANSACTION;
+
+DELETE FROM gis_point WHERE ST_Equals(p2, ST_PointFromText('POINT(-8 4)'));
+
+INSERT INTO gis_point VALUES(ST_PointFromText('POINT(20.5 -41)'), ST_PointFromText('POINT(8 -4)'));
+
+SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
+
+UPDATE gis_point SET p1 = ST_PointFromText('POINT(20.5 49)'), p2 = ST_PointFromText('POINT(-8 4)') WHERE ST_Intersection(@ls3, @ls4) = p1;
+
+SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 49)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
+
+ROLLBACK;
+
+SELECT ST_AsText(p1) AS 'Expect (32.1234 64.2468) AND (20.5 41)' FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4);
+
+CHECK TABLE gis_point;
+
+-- echo =======================================================
+-- echo Enlarge the table by inserting the same data and verify
+-- echo =======================================================
+
+INSERT INTO gis_point SELECT * FROM gis_point;
+INSERT INTO gis_point SELECT * FROM gis_point;
+INSERT INTO gis_point SELECT * FROM gis_point;
+INSERT INTO gis_point SELECT * FROM gis_point;
+INSERT INTO gis_point SELECT * FROM gis_point;
+INSERT INTO gis_point SELECT * FROM gis_point;
+INSERT INTO gis_point SELECT * FROM gis_point;
+INSERT INTO gis_point SELECT * FROM gis_point;
+INSERT INTO gis_point SELECT * FROM gis_point;
+INSERT INTO gis_point SELECT * FROM gis_point;
+
+CHECK TABLE gis_point;
+
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
+
+SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1 GROUP BY p1;
+SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1) GROUP BY p1;
+SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2;
+SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1) GROUP BY p2;
+SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) GROUP BY p1, p2;
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly) GROUP BY p1, p2;
+
+-- echo ======================================================================
+-- echo Build another new table with the same schema, will insert data from this table to the orignal one
+-- echo ======================================================================
+
+CREATE TABLE p(p1 POINT NOT NULL, p2 POINT NOT NULL, SPATIAL INDEX k1(p2)) ENGINE=InnoDB;
+
+INSERT INTO p VALUES(ST_PointFromText('POINT(1000 -1000)'), ST_PointFromText('POINT(-201 203.56)'));
+INSERT INTO p VALUES(ST_PointFromText('POINT(20.5 43.9832)'), ST_PointFromText('POINT(-0 0)'));
+INSERT INTO p VALUES(ST_PointFromText('POINT(-4.2 -6.98)'), ST_PointFromText('POINT(-120.5 343.9832)'));
+
+INSERT INTO p SELECT * FROM p WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((0 0, 0 2000, 2000 2000, 2000 -2000, 0 -2000, 0 0))'));
+INSERT INTO p SELECT * FROM p WHERE MBRWithin(p1, ST_GeomFromText('POLYGON((0 0, -10 0, -100 -100, 0 -50, 0 0))'));
+
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM p GROUP BY p1, p2;
+
+INSERT INTO gis_point SELECT * FROM p;
+INSERT INTO gis_point SELECT * FROM p;
+INSERT INTO gis_point SELECT * FROM p;
+INSERT INTO gis_point SELECT * FROM p;
+INSERT INTO gis_point SELECT * FROM p;
+INSERT INTO gis_point SELECT * FROM p;
+INSERT INTO gis_point SELECT * FROM p;
+
+CHECK TABLE gis_point;
+
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
+
+SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
+
+SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE MBRWithin(p2, @poly2) GROUP BY p2;
+
+DROP TABLE p;
+
+-- echo ================================
+-- echo Use a trx to test IUD and verify
+-- echo ================================
+
+# Temporarily disabled the following test case, since the ROLLBACK and
+# some other DML would cost too much of time. We could test them until
+# we have optimized the UNDO, etc.
+
+#START TRANSACTION;
+
+#-- echo 'To remove (-2 -6), (-1 -3)'
+#DELETE FROM gis_point WHERE ST_Contains(ST_GeomFromText('POLYGON((-8 -8, 4 -7, 1 1, 0 -1, -2 -2, -8 -8))') , p2);
+
+#SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2;
+
+#-- echo 'To set (-1 -1) to (-5.12 -10.84)'
+#UPDATE gis_point SET p2 = ST_PointFromText('POINT(-5.12 -10.84)') WHERE ST_Intersection(ST_GeomFromText('LINESTRING(-3 4, -2 0, 0.5 -2.5, 5 0)'), ST_GeomFromText('LINESTRING(0.6 0.6, -10 -10)')) = p2;
+
+#SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE MBRWithin(p2, @poly2) GROUP BY p2;
+
+#-- echo 'To insert several (-3.6 -4.05) (-1 -1)
+#INSERT INTO gis_point VALUES
+#(ST_PointFromText('POINT(10.1 2.6)'), ST_PointFromText('POINT(-1 -1)')),
+#(ST_PointFromText('POINT(100.99 -2.53)'), ST_PointFromText('POINT(-3.6 -4.05)')),
+#(ST_PointFromText('POINT(13.62 3.87)'), ST_PointFromText('POINT(-1 -1)')),
+#(ST_PointFromText('POINT(-36.3 0.34)'), ST_PointFromText('POINT(-1 -1)')),
+#(ST_PointFromText('POINT(1.04 25.6)'), ST_PointFromText('POINT(-3.6 -4.05)'));
+
+#SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE MBRWithin(p2, @poly2) GROUP BY p2;
+
+#SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(ST_GeomFromText('POLYGON((-0.999 -0.999, -6 -1, -6 -7, -0.99999 -0.99999, -0.999 -0.999))'), p2) GROUP BY p2;
+
+#ROLLBACK;
+
+#CHECK TABLE gis_point;
+
+-- echo =============================================
+-- echo Delete those rows selected from another table
+-- echo =============================================
+
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
+
+UPDATE gis_point SET p2 = ST_PointFromText('POINT(2000 2000)') WHERE ST_Contains(ST_GeomFromText('POLYGON((-100 100, -400 100, -400 400, -100 400, -100 100))'), p2) OR ST_Contains(ST_GeomFromText('POLYGON((-0.0001 -0.0002, -0.0001 0.00002, 0.00000005 0.000001, 0.0000025 -0.001, -0.0001 -0.0002))'), p2);
+
+CHECK TABLE gis_point;
+
+-- echo 'To remove all the just updated rows'
+DELETE FROM gis_point WHERE ST_Intersection(ST_GeomFromText('LINESTRING(1800 1900, 2200 2100)'), ST_GeomFromText('LINESTRING(0 0, 2001 2001)')) = p2;
+
+INSERT INTO gis_point VALUES
+(ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)')),
+(ST_PointFromText('POINT(8 16)'), ST_PointFromText('POINT(0 0)'));
+
+-- echo ======================================================================
+-- echo Following results should be almost the same with those at the beginning
+-- echo ======================================================================
+
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2)
+FROM gis_point
+WHERE ST_Contains(ST_GeomFromText('POLYGON((-1000 -1000, -1000 1000, 1000 1000, 1001 -1001, -1000 -1000))'), p1)
+GROUP BY p1, p2;
+
+SELECT ST_AsText(p1), ST_AsText(p2) FROM gis_point GROUP BY p1, p2;
+SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_Intersection(@ls1, @ls2) = p1 GROUP BY p1;
+SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE MBRWithin(p1, @poly1) GROUP BY p1;
+SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@poly2, p2) GROUP BY p2;
+SELECT COUNT(*), ST_AsText(p2) FROM gis_point WHERE ST_Equals(p2, @p1) GROUP BY p2;
+SELECT COUNT(*), ST_AsText(p1) FROM gis_point WHERE ST_TOUCHES(@poly3, p1) AND MBRWithin(p2, @poly4) GROUP BY p1;
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) GROUP BY p1, p2;
+SELECT COUNT(*), ST_AsText(p1), ST_AsText(p2) FROM gis_point WHERE ST_Contains(@mpoly, p1) AND NOT MBRWithin(p1, @mpoly) GROUP BY p1, p2;
+
+CHECK TABLE gis_point;
+
+DROP TABLE gis_point;
+