Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.28, 10.11.10
-
None
Description
While the user found a work around, it should behave in the server.
-- Create a table to store GIS geometries
|
CREATE TABLE gis_geometries ( |
id INT AUTO_INCREMENT PRIMARY KEY, |
geom GEOMETRY NOT NULL |
);
|
|
|
-- Insert a variety of geometries, including MULTIPOLYGON with overlap
|
INSERT INTO gis_geometries (geom) VALUES |
-- A simple POINT |
(ST_GeomFromText('POINT(-46.5983 -23.5236)')), |
|
-- A simple LINESTRING |
(ST_GeomFromText('LINESTRING(-46.5983 -23.5236, -46.5920 -23.5220)')), |
|
-- A POLYGON |
(ST_GeomFromText('POLYGON((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043151 -23.5315056, -46.6043151 -23.5172944))')), |
|
|
-- Another overlapping POLYGON |
(ST_GeomFromText('POLYGON((-46.6020000 -23.5230000, -46.5900000 -23.5230000, -46.5900000 -23.5270000, -46.6020000 -23.5270000, -46.6020000 -23.5230000))')), |
|
|
-- A MULTIPOLYGON that includes the one you provided and an additional polygon |
(ST_GeomFromText('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043151 -23.5315056, -46.6043151 -23.5172944)), |
((-46.6000000 -23.5200000, -46.5950000 -23.5200000, -46.5950000 -23.5300000, -46.6000000 -23.5300000, -46.6000000 -23.5200000)))')), |
|
|
-- A POLYGON that does not overlap |
(ST_GeomFromText('POLYGON((-46.5600000 -23.5000000, -46.5500000 -23.5000000, -46.5500000 -23.5100000, -46.5600000 -23.5100000, -46.5600000 -23.5000000))')); |
|
|
-- Add an index
|
CREATE SPATIAL INDEX sp_index ON gis_geometries (geom); |
With a =1 it does a table scan. Without it uses a spatial index as expected.
MariaDB 10.11.11-MariaDB source revision 79cc0f9f78ae89c54a11b9a2be4b2304e3ef1816
|
|
|
MariaDB [test]> explain select * from gis_geometries a where ( ST_Within(a.geom, ST_GeomFromText('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043 |
151 -23.5315056, -46.6043151 -23.5172944)))',4326))=1 ) order by a.id asc limit 1; |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
| 1 | SIMPLE | a | index | NULL | PRIMARY | 4 | NULL | 1 | Using where | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> explain select * from gis_geometries a where ( ST_Within(a.geom, ST_GeomFromText('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043151 -23.5315056, -46.6043151 -23.5172944)))',4326)) ) order by a.id asc limit 1; |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
| 1 | SIMPLE | a | index | sp_index | PRIMARY | 4 | NULL | 1 | Using where | |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ |
Unsure if related, 10.5 (and 10.6) hits a trivial condition removal:
MariaDB 10.5.28-MariaDB source revision ae0cbfe934eacb6744b28fbf9997002ed82c0c49
|
MariaDB [test]> select * from gis_geometries a where ( ST_Within(a.geom, ST_GeomFromText('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043151 -23.5315056, -46.6043151 -23.5172944)))',4326)) ) order by a.id asc limit 1; |
+----+---------------------------+ |
| id | geom |
|
+----+---------------------------+ |
| 1 | 1�*�LG��/L�
|
�7� |
|
+----+---------------------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> explain select * from gis_geometries a where ( ST_Within(a.geom, ST_GeomFromText('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043 |
|
|
151 -23.5315056, -46.6043151 -23.5172944)))',4326)) ) order by a.id asc limit 1; |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
1 row in set (0.001 sec) |
|
|
MariaDB [test]> select id from gis_geometries a where ( ST_Within(a.geom, ST_GeomFromText('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043 |
151 -23.5315056, -46.6043151 -23.5172944)))',4326)) ) order by a.id asc limit 1; |
Empty set (0.000 sec) |
|
|
MariaDB [test]> select * from information_schema.optimizer_trace limit 1\G |
*************************** 1. row ***************************
|
QUERY: select * from gis_geometries a where ( ST_Within(a.geom, ST_GeomFromText('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043 |
151 -23.5315056, -46.6043151 -23.5172944)))',4326))=1 ) order by a.id asc limit 1 |
TRACE: {
|
"steps": [ |
{
|
"join_preparation": { |
"select_id": 1, |
"steps": [ |
{
|
"expanded_query": "select a.`id` AS `id`,a.geom AS geom from gis_geometries a where st_within(a.geom,st_geometryfromtext('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043\n151 -23.5315056, -46.6043151 -23.5172944)))',4326)) = 1 order by a.`id` limit 1" |
}
|
]
|
}
|
},
|
{
|
"join_optimization": { |
"select_id": 1, |
"steps": [ |
{
|
"condition_processing": { |
"condition": "WHERE", |
"original_condition": "st_within(a.geom,st_geometryfromtext('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043\n151 -23.5315056, -46.6043151 -23.5172944)))',4326)) = 1", |
"steps": [ |
{
|
"transformation": "equality_propagation", |
"resulting_condition": "st_within(a.geom,st_geometryfromtext('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043\n151 -23.5315056, -46.6043151 -23.5172944)))',4326)) = 1" |
},
|
{
|
"transformation": "constant_propagation", |
"resulting_condition": "st_within(a.geom,st_geometryfromtext('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043\n151 -23.5315056, -46.6043151 -23.5172944)))',4326)) = 1" |
},
|
{
|
"transformation": "trivial_condition_removal", |
"resulting_condition": "st_within(a.geom,st_geometryfromtext('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043\n151 -23.5315056, -46.6043151 -23.5172944)))',4326)) = 1" |
}
|
]
|
}
|
},
|
{
|
"table_dependencies": [ |
{
|
"table": "a", |
"row_may_be_null": false, |
"map_bit": 0, |
"depends_on_map_bits": [] |
}
|
]
|
},
|
{
|
"ref_optimizer_key_uses": [] |
},
|
{
|
"rows_estimation": [ |
{
|
"table": "a", |
"table_scan": { |
"rows": 6, |
"cost": 1 |
}
|
}
|
]
|
},
|
{
|
"considered_execution_plans": [ |
{
|
"plan_prefix": [], |
"table": "a", |
"best_access_path": { |
"considered_access_paths": [ |
{
|
"access_type": "scan", |
"resulting_rows": 6, |
"cost": 1, |
"chosen": true |
}
|
],
|
"chosen_access_method": { |
"type": "scan", |
"records": 6, |
"cost": 1, |
"uses_join_buffering": false |
}
|
},
|
"rows_for_plan": 6, |
"cost_for_plan": 2.2, |
"estimated_join_cardinality": 6 |
}
|
]
|
},
|
{
|
"best_join_order": ["a"] |
},
|
{
|
"attaching_conditions_to_tables": { |
"original_condition": "st_within(a.geom,<cache>(st_geometryfromtext('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043\n151 -23.5315056, -46.6043151 -23.5172944)))',4326))) = 1", |
"attached_conditions_computation": [], |
"attached_conditions_summary": [ |
{
|
"table": "a", |
"attached": "st_within(a.geom,<cache>(st_geometryfromtext('MULTIPOLYGON(((-46.6043151 -23.5172944, -46.5912849 -23.5172944, -46.5912849 -23.5315056, -46.6043\n151 -23.5315056, -46.6043151 -23.5172944)))',4326))) = 1" |
}
|
]
|
}
|
},
|
{
|
"test_if_skip_sort_order": [ |
{
|
"reconsidering_access_paths_for_index_ordering": { |
"clause": "ORDER BY", |
"fanout": 1, |
"read_time": 1.001, |
"table": "a", |
"rows_estimation": 6, |
"possible_keys": [ |
{
|
"index": "PRIMARY", |
"can_resolve_order": true, |
"updated_limit": 1, |
"index_scan_time": 1, |
"records": 6, |
"chosen": true |
},
|
{
|
"index": "sp_index", |
"can_resolve_order": false, |
"cause": "order can not be resolved by key" |
}
|
]
|
}
|
}
|
]
|
}
|
]
|
}
|
},
|
{
|
"join_execution": { |
"select_id": 1, |
"steps": [] |
}
|
}
|
]
|
}
|
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
|
INSUFFICIENT_PRIVILEGES: 0
|