mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-02-16 02:15:33 +01:00
Code Issues Projects Releases Packages Wiki Activity Actions
mariadb/mysql-test/suite/innodb_gis/t/create_spatial_index.test
Monty 727491b72a Added test cases for preceding test 
This includes all test changes from
"Changing all cost calculation to be given in milliseconds"
and forwards.

Some of the things that caused changes in the result files:

- As part of fixing tests, I added 'echo' to some comments to be able to
  easier find out where things where wrong.
- MATERIALIZED has now a higher cost compared to X than before. Because
  of this some MATERIALIZED types have changed to DEPENDEND SUBQUERY.
  - Some test cases that required MATERIALIZED to repeat a bug was
    changed by adding more rows to force MATERIALIZED to happen.
- 'Filtered' in SHOW EXPLAIN has in many case changed from 100.00 to
  something smaller. This is because now filtered also takes into
  account the smallest possible ref access and filters, even if they
  where not used. Another reason for 'Filtered' being smaller is that
  we now also take into account implicit filtering done for subqueries
  using FIRSTMATCH.
  (main.subselect_no_exists_to_in)
  This is caluculated in best_access_path() and stored in records_out.
- Table orders has changed because more accurate costs.
- 'index' and 'ALL' for small tables has changed to use 'range' or
   'ref' because of optimizer_scan_setup_cost.
- index can be changed to 'range' as 'range' optimizer assumes we don't
  have to read the blocks from disk that range optimizer has already read.
  This can be confusing in the case where there is no obvious where clause
  but instead there is a hidden 'key_column > NULL' added by the optimizer.
  (main.subselect_no_exists_to_in)
- Scan on primary clustered key does not report 'Using Index' anymore
  (It's a table scan, not an index scan).
- For derived tables, the number of rows is now 100 instead of 2,
  which can be seen in EXPLAIN.
- More tests have "Using index for group by" as the cost of this
  optimization is now more correct (lower).
- A primary key could be preferred for a normal key, even if it would
  access more rows, as it's faster to do 1 lokoup and 3 'index_next' on a
  clustered primary key than one lookup trough a secondary.
  (main.stat_tables_innodb)

Notes:

- There was a 4.7% more calls to best_extension_by_limited_search() in
  the main.greedy_optimizer test.  However examining the test results
  it looked that the plans where slightly better (eq_ref where more
  chained together) so I assume this is ok.
- I have verified a few test cases where there was notable/unexpected
  changes in the plan and in all cases the new optimizer plans where
  faster.  (main.greedy_optimizer and some others)
2023-02-03 00:00:35 +03:00

1184 lines
43 KiB
Text
Raw Permalink Blame History

# *********************************************************
# Test Multiple Spatial Indexes on compression table
# Test spatial index with table having primary key column
# Test Spatial index with Create Index different clauses
# Test Spatial index with spatial relationship functions
# Test Spatial index with MBR spatial relationship functions
# Test Spatial index columns with DML & SELECT queries
# Test Spatial index with procedures
# Test Delete & Update & check status of the table
# Test spatial index with table have no primary key column
# Test spatial index with table have no auto_increment
# Test spatial index with check constraint
# **********************************************************
--source include/have_innodb.inc
--source include/have_innodb_16k.inc
--source include/have_geometry.inc
--disable_query_log
SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
SET GLOBAL innodb_read_only_compressed=OFF;
--enable_query_log
# Check spatial index functionality on compress table with Primary key
CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL,
c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL)
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
# Check spatial index functionality with Create Index clause options
CREATE SPATIAL INDEX idx1 on tab(c2 ASC);
CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968';
CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=8 ;
CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=4
COMMENT 'Spatial index on Geometry type column';
# Check index type
SHOW INDEXES FROM tab;
# Populate some spatial data
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
ANALYZE TABLE tab;
# Check the spatial relationship between 2 GIS shapes
--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
# Show plan if we cannot use index order
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1+0;
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
--echo # Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
--echo # Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
--echo # Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
--echo # Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
--echo # Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
--echo # Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRTouches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
--echo # Test with Procedure
delimiter |;
CREATE PROCEDURE proc_wl6968()
BEGIN
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
END |
delimiter ;|
CALL proc_wl6968();
--echo # Test the Delete & Update
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
CHECK TABLE tab;
SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
CHECK TABLE tab;
SET @g1 = ST_GeomFromText('POLYGON((100 200,1010 1010,1020 1020,500 300,300 200,100 300,100 200))');
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
CHECK TABLE tab;
SET @g1 = ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))');
SET @g2 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
UPDATE tab SET C4 = @g2 WHERE ST_Crosses(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g2) ORDER BY c1;
CHECK TABLE tab;
# Cleanup
DROP TABLE tab;
DROP PROCEDURE proc_wl6968;
# End of Testcase compress table with Primary key
# Check spatial index functionality on compress table No Primary key
CREATE TABLE tab(c1 int ,c2 POINT NOT NULL,
c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL)
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
# Check spatial index functionality with Create Index clause options
CREATE SPATIAL INDEX idx1 on tab(c2 ASC);
CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968';
CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=2 ;
CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=8
COMMENT 'Spatial index on Geometry type column';
# Check index type
SHOW INDEXES FROM tab;
# Populate some spatial data
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(3,ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(4,ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(5,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(6,ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(7,ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(8,ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(9,ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
INSERT INTO tab(c1,c2,c3,c4,c5)
VALUES(10,ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
ANALYZE TABLE tab;
# Check the spatial relationship between 2 GIS shapes
--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
--echo # Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
--echo # Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
--echo # Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
--echo # Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
--echo # Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
--echo # Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRTouches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
--echo # Test with Procedure
delimiter |;
CREATE PROCEDURE proc_wl6968()
BEGIN
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
END |
delimiter ;|
CALL proc_wl6968();
--echo # Test the Delete & Update
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
CHECK TABLE tab;
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
SET @g2 = ST_GeomFromText( 'POLYGON((1 1,2 2,3 3,10 3,5 1,1 1))');
UPDATE tab SET C4 = @g2 WHERE MBROverlaps(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
CHECK TABLE tab;
# Cleanup
DROP TABLE tab;
DROP PROCEDURE proc_wl6968;
# End of Testcase compress table No Primary key
# Check spatial index functionality on compress table with auto_increment
CREATE TABLE tab(c1 int AUTO_INCREMENT PRIMARY KEY,c2 POINT NOT NULL,
c3 LINESTRING NOT NULL,c4 POLYGON NOT NULL,c5 GEOMETRY NOT NULL)
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
# Check spatial index functionality with Create Index clause options
CREATE SPATIAL INDEX idx1 on tab(c2 ASC);
CREATE SPATIAL INDEX idx2 on tab(c3 DESC) COMMENT 'wl6968';
CREATE SPATIAL INDEX idx3 on tab(c4 ASC) KEY_BLOCK_SIZE=16 ;
CREATE SPATIAL INDEX idx4 on tab(c5 DESC) KEY_BLOCK_SIZE=16
COMMENT 'Spatial index on Geometry type column';
# Check index type
SHOW INDEXES FROM tab;
# Populate some spatial data
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(4 4)'),ST_GeomFromText('LINESTRING(130 130,140 140,150 150)'),
ST_GeomFromText('POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))'),
ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(50 50)'),ST_GeomFromText('LINESTRING(200 200,300 300,400 400)'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'),
ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(400 400,500 500,600 700)'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'),
ST_GeomFromText('POLYGON((1010 1010,1020 1020,1030 1030,1040 1030,1020 1010,1010 1010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(3 3)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'),
ST_GeomFromText('POLYGON((2010 2010,2020 2020,2030 2030,2040 2030,2020 2010,2010 2010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(60 70)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(0 0)'),ST_GeomFromText('LINESTRING(40 40,50 50,60 70)'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'),
ST_GeomFromText('POLYGON((3010 3010,3020 3020,3030 3030,3040 3030,3020 3010,3010 3010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(120 120)'),ST_GeomFromText('LINESTRING(100 100,110 110,120 120)'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'),
ST_GeomFromText('POLYGON((4010 4010,4020 4020,4030 4030,4040 4030,4020 4010,4010 4010))'));
INSERT INTO tab(c2,c3,c4,c5)
VALUES(ST_GeomFromText('POINT(160 160)'),ST_GeomFromText('LINESTRING(140 140,150 150,160 160)'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'),
ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))'));
ANALYZE TABLE tab;
# Check the spatial relationship between 2 GIS shapes
--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 300 300,400 400)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
--echo # Test the MBRWithin
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
--echo # Test the ST_Crosses
SET @g1 = ST_GeomFromText('POLYGON((100 200,200 300,400 500,500 300,300 200,100 300,100 200))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 10 10,30 30,40 40)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_CRosses(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Crosses(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Crosses(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Crosses(tab.c4, @g1);
--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
--echo # Test the Overelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING(7 1,30 30,1010 3010,1010 2010,3010 3010,4010 4010,5010 5010 )');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
--echo # Test the ST_Touches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 100 100,200 200,300 300)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE ST_Touches(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE ST_Touches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE ST_Touches(tab.c4, @g1);
--echo # Test the MBRContains
SET @g1 = ST_GeomFromText( 'POLYGON((7 1,6 2,6 3,10 3,10 1,7 1))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRContains(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRContains(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRContains(tab.c4, @g1);
SET @g1 = ST_GeomFromText( 'POLYGON((30 30,40 40,50 50,30 50,30 40,30 30)) ');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRWithin(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRWithin(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRWithin(tab.c4, @g1);
--echo # Test the MBRDisjoint
SET @g1 = ST_GeomFromText('POLYGON((4 -2,5 -4,6 -5,7 -4,7 2,4 -2))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRDisjoint(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRDisjoint(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRDisjoint(tab.c4, @g1);
--echo # Test the MBREquals
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBREquals(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
--echo # Test the MBRintersects
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
SET @g1 = ST_GeomFromText('LINESTRING( 30 30,40 40,50 50)');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRintersects(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRintersects(tab.c4, @g1);
--echo # Test the MBROverelaps
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 2,4 5,5 5,7 1,0 0 ))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBROverlaps(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBROverlaps(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBROverlaps(tab.c4, @g1);
--echo # Test the MBRTouches
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
EXPLAIN SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRTouches(tab.c4, @g1) ORDER BY c1;
EXPLAIN UPDATE tab SET C2 = ST_GeomFromText('POINT(0 0)')
WHERE MBRTouches(tab.c4, @g1);
EXPLAIN DELETE FROM tab WHERE MBRTouches(tab.c4, @g1);
--echo # Test the Delete & Update
SET @g1 = ST_GeomFromText('POLYGON((5010 5010,5020 5020,5030 5030,5040 5030,5020 5010,5010 5010))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
DELETE FROM tab WHERE MBREquals(tab.c4, @g1);
SELECT c1,ST_Astext(c4) FROM tab WHERE MBREquals(tab.c4, @g1) ORDER BY c1;
CHECK TABLE tab;
SET @g1 = ST_GeomFromText( 'POLYGON((0 0,0 30,30 40,40 50,50 30,0 0))');
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
DELETE FROM tab WHERE MBRIntersects(tab.c4, @g1);
--disable_query_log
SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
--enable_query_log
SELECT c1,ST_Astext(c4) FROM tab WHERE MBRIntersects(tab.c4, @g1) ORDER BY c1;
CHECK TABLE tab;
# Cleanup
DROP TABLE tab;
# End of Testcase compress table with Auto_increment
--echo # Test check constraint on spatial column
--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(c1 > 0) ) ENGINE=InnoDB;
CREATE TABLE tab(c1 POINT NOT NULL,CONSTRAINT tab_const check(CAST(c1 AS BINARY) > 0) ) ENGINE=InnoDB;
CREATE SPATIAL INDEX idx1 ON tab(c1) ;
SHOW CREATE TABLE tab;
SHOW INDEX FROM tab;
set @g1 = ST_GeomFromText('POINT(-1 -2)');
SELECT ST_AsText(c1) FROM tab;
DROP table tab;
# repro case for bug#20451454 - FAILING ASSERTION: LOW_MATCH
# < DTUPLE_GET_N_FIELDS_CMP(TUPLE)
create table `t1`(`a` geometry not null,`b` linestring not null,
primary key (`b`(192),`a`(141)),spatial key (`b`)) engine=innodb;
insert into `t1` values(
point(1,1),
linestring(point(1,1),point(1,1))
);
--error ER_BAD_NULL_ERROR
insert into `t1` values
(
polygon(
linestring(point(1,1),point(1,1)),
linestring(point(1,1),point(11,1))
),
linestring(point(1,1),point(1,1))
);
select 1 from t1 where st_intersects(
geometrycollection(point(1,-1)),b
);
drop table t1;
# Reproduce case for updating statistic after droping stats info table.
CREATE TABLE t1(c1 POINT NOT NULL);
DROP TABLE mysql.innodb_table_stats;
CALL mtr.add_suppression("InnoDB: Table `mysql`.`innodb_table_stats` not found.");
CALL mtr.add_suppression("InnoDB: Fetch of persistent statistics requested for table `test`.`t1` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.");
CREATE SPATIAL INDEX idx2 ON t1(c1);
DROP TABLE t1;
CREATE TABLE mysql.innodb_table_stats (
database_name varchar(64) COLLATE utf8_bin NOT NULL,
table_name varchar(199) COLLATE utf8_bin NOT NULL,
last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows bigint(20) unsigned NOT NULL,
clustered_index_size bigint(20) unsigned NOT NULL,
sum_of_other_index_sizes bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
Reference in a new issue View git blame Copy permalink