summaryrefslogtreecommitdiff
path: root/mysql-test/t
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/t')
-rw-r--r--mysql-test/t/derived_view.test4
-rw-r--r--mysql-test/t/gis-precise.test312
-rw-r--r--mysql-test/t/gis-rt-precise.test64
-rw-r--r--mysql-test/t/gis-rtree.test12
-rw-r--r--mysql-test/t/gis.test562
-rw-r--r--mysql-test/t/ps.test28
-rwxr-xr-xmysql-test/t/range_vs_index_merge.test36
-rw-r--r--mysql-test/t/subselect.test10
-rw-r--r--mysql-test/t/subselect3.test25
-rw-r--r--mysql-test/t/subselect4.test5
-rw-r--r--mysql-test/t/subselect_mat_cost_bugs.test4
-rw-r--r--mysql-test/t/subselect_sj.test30
-rw-r--r--mysql-test/t/subselect_sj_jcl6.test42
13 files changed, 1093 insertions, 41 deletions
diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test
index c1a9435ef6c..e35aca9b718 100644
--- a/mysql-test/t/derived_view.test
+++ b/mysql-test/t/derived_view.test
@@ -394,7 +394,7 @@ DROP TABLE t1;
--echo # LP bug #803851: materialized view + IN->EXISTS
--echo #
-SET SESSION optimizer_switch='semijoin=off,derived_with_keys=on';
+SET SESSION optimizer_switch='semijoin=off,derived_with_keys=on,materialization=off';
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (2,2), (3,3), (1,1);
@@ -895,7 +895,7 @@ INSERT INTO t3 VALUES
(14,4,'a'), (15,7,'b'), (16,4,'c'), (17,1,'d'), (18,9,'e'),
(19,4,'f'), (20,8,'g');
-SET SESSION optimizer_switch='derived_merge=on,subquery_cache=off';
+SET SESSION optimizer_switch='derived_merge=on,subquery_cache=off,materialization=off';
--echo # The following two EXPLAINs must return the same execution plan
EXPLAIN
diff --git a/mysql-test/t/gis-precise.test b/mysql-test/t/gis-precise.test
new file mode 100644
index 00000000000..1de8726a7bc
--- /dev/null
+++ b/mysql-test/t/gis-precise.test
@@ -0,0 +1,312 @@
+-- source include/have_geometry.inc
+
+
+#
+# Spatial objects
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+select 1, ST_Intersects(GeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0))'), GeomFromText('POLYGON((10 10,30 10,30 30,10 30,10 10))'));
+select 0, ST_Intersects(GeomFromText('POLYGON((0 0,20 10,10 30, 0 0))'), GeomFromText('POLYGON((10 40, 40 50, 20 70, 10 40))'));
+select 1, ST_Intersects(GeomFromText('POLYGON((0 0,20 10,10 30, 0 0))'), GeomFromText('POINT(10 10)'));
+select 1, ST_Intersects(GeomFromText('POLYGON((0 0,20 10,10 30, 0 0))'), GeomFromText('POLYGON((10 10,30 20,20 40, 10 10))'));
+select 0, ST_Within(GeomFromText('POLYGON((0 0,20 10,10 30, 0 0))'), GeomFromText('POLYGON((10 10,30 20,20 40, 10 10))'));
+select 1, ST_Within(GeomFromText('POLYGON((1 1,20 10,10 30, 1 1))'), GeomFromText('POLYGON((0 0,30 5,10 40, 0 0))'));
+
+
+create table t1 (g point);
+insert into t1 values
+(GeomFromText('POINT(2 2)')), (GeomFromText('POINT(2 4)')), (GeomFromText('POINT(2 6)')), (GeomFromText('POINT(2 8)')),
+(GeomFromText('POINT(4 2)')), (GeomFromText('POINT(4 4)')), (GeomFromText('POINT(4 6)')), (GeomFromText('POINT(4 8)')),
+(GeomFromText('POINT(6 2)')), (GeomFromText('POINT(6 4)')), (GeomFromText('POINT(6 6)')), (GeomFromText('POINT(6 8)')),
+(GeomFromText('POINT(8 2)')), (GeomFromText('POINT(8 4)')), (GeomFromText('POINT(8 6)')), (GeomFromText('POINT(8 8)'));
+
+select astext(g) from t1 where ST_Within(g, GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1))'));
+select 'Contains';
+select astext(g) from t1 where ST_Contains(GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1))'), g);
+select 'Intersects';
+select astext(g) from t1 where ST_Intersects(GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1))'), g);
+select 'Contains';
+select astext(g) from t1 where ST_Contains(GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1))'), g);
+select 'Contains2';
+select astext(g) from t1 where ST_Contains(GeomFromText('POLYGON((5 1, 7 1, 7 7, 5 7, 3 3, 5 3, 5 1), (5.01 3.01, 6 5, 9 5, 8 3, 5.01 3.01))'), g);
+
+DROP TABLE t1;
+
+select 0, ST_Within(GeomFromText('LINESTRING(15 15, 50 50, 60 60)'), GeomFromText('POLYGON((10 10,30 20,20 40, 10 10))'));
+select 1, ST_Within(GeomFromText('LINESTRING(15 15, 16 16)'), GeomFromText('POLYGON((10 10,30 20,20 40, 10 10))'));
+
+
+select 1, ST_Intersects(GeomFromText('LINESTRING(15 15, 50 50)'), GeomFromText('LINESTRING(50 15, 15 50)'));
+select 0, ST_Intersects(GeomFromText('LINESTRING(15 15, 50 50)'), GeomFromText('LINESTRING(16 16, 51 51)'));
+
+select 1, ST_Intersects(GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), GeomFromText('POLYGON((50 5, 55 10, 0 45, 50 5))'));
+
+select astext(ST_Union(geometryfromtext('point(1 1)'), geometryfromtext('polygon((0 0, 2 0, 1 2, 0 0))')));
+select astext(ST_Intersection(geometryfromtext('point(1 1)'), geometryfromtext('polygon((0 0, 2 0, 1 2, 0 0))')));
+
+select ST_Intersects(GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), GeomFromText('POLYGON((50 5, 55 10, 0 45, 50 5))'));
+select ST_contains(GeomFromText('MULTIPOLYGON(((0 0, 0 5, 5 5, 5 0, 0 0)), ((6 6, 6 11, 11 11, 11 6, 6 6)))'), GeomFromText('POINT(5 10)'));
+select ST_Disjoint(GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), GeomFromText('POLYGON((10 10, 10 15, 15 15, 15 10, 10 10))'));
+select ST_Disjoint(GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), GeomFromText('POLYGON((10 10, 10 4, 4 4, 4 10, 10 10))'));
+select ST_Overlaps(GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), GeomFromText('POLYGON((10 10, 10 4, 4 4, 4 10, 10 10))'));
+select ST_Overlaps(GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'), GeomFromText('POLYGON((1 1, 1 4, 4 4, 4 1, 1 1))'));
+
+# Distance tests
+select ST_DISTANCE(geomfromtext('polygon((0 0, 1 2, 2 1, 0 0))'), geomfromtext('polygon((2 2, 3 4, 4 3, 2 2))'));
+select ST_DISTANCE(geomfromtext('polygon((0 0, 1 2, 2 1, 0 0))'), geomfromtext('linestring(0 1, 1 0)'));
+select ST_DISTANCE(geomfromtext('polygon((0 0, 3 6, 6 3, 0 0))'), geomfromtext('polygon((2 2, 3 4, 4 3, 2 2))'));
+select ST_DISTANCE(geomfromtext('polygon((0 0, 3 6, 6 3, 0 0),(2 2, 3 4, 4 3, 2 2))'), geomfromtext('point(3 3)'));
+select ST_DISTANCE(geomfromtext('linestring(0 0, 3 6, 6 3, 0 0)'), geomfromtext('polygon((2 2, 3 4, 4 3, 2 2))'));
+
+
+# Operations tests
+select astext(ST_Intersection(GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), GeomFromText('POLYGON((50 5, 55 10, 0 45, 50 5))')));
+select astext(ST_Intersection(GeomFromText('LINESTRING(0 0, 50 45, 40 50, 0 0)'), GeomFromText('LINESTRING(50 5, 55 10, 0 45, 50 5)')));
+select astext(ST_Intersection(GeomFromText('LINESTRING(0 0, 50 45, 40 50)'), GeomFromText('LINESTRING(50 5, 55 10, 0 45)')));
+select astext(ST_Intersection(GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), GeomFromText('POINT(20 20)')));
+select astext(ST_Intersection(GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), GeomFromText('LINESTRING(-10 -10, 200 200)')));
+select astext(ST_Intersection(GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), GeomFromText('LINESTRING(-10 -10, 200 200, 199 201, -11 -9)')));
+select astext(ST_UNION(GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), GeomFromText('LINESTRING(-10 -10, 200 200, 199 201, -11 -9)')));
+
+select astext(ST_intersection(geomfromtext('polygon((0 0, 1 0, 0 1, 0 0))'), geomfromtext('polygon((0 0, 1 1, 0 2, 0 0))')));
+
+select astext(ST_symdifference(geomfromtext('polygon((0 0, 1 0, 0 1, 0 0))'), geomfromtext('polygon((0 0, 1 1, 0 2, 0 0))')));
+select astext(ST_UNION(GeomFromText('POLYGON((0 0, 50 45, 40 50, 0 0))'), GeomFromText('LINESTRING(-10 -10, 200 200, 199 201, -11 -9)')));
+
+# Buffer() tests
+select astext(ST_buffer(geometryfromtext('point(1 1)'), 1));
+create table t1(geom geometrycollection);
+insert into t1 values (geomfromtext('POLYGON((0 0, 10 10, 0 8, 0 0))'));
+insert into t1 values (geomfromtext('POLYGON((1 1, 10 10, 0 8, 1 1))'));
+select astext(geom), area(geom),area(ST_buffer(geom,2)) from t1;
+select astext(ST_buffer(geom,2)) from t1;
+
+set @geom=geomfromtext('LINESTRING(2 1, 4 2, 2 3, 2 5)');
+set @buff=ST_buffer(@geom,1);
+select astext(@buff);
+
+# cleanup
+DROP TABLE t1;
+
+#Touches tests
+select st_touches(geomfromtext('point(0 0)'), geomfromtext('point(1 1)'));
+select st_touches(geomfromtext('point(1 1)'), geomfromtext('point(1 1)'));
+select st_touches(geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), geomfromtext('point(1 1)'));
+select st_touches(geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), geomfromtext('point(1 0)'));
+select st_touches(geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), geomfromtext('point(1 2)'));
+select st_touches(geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), geomfromtext('polygon((1 1.2, 1 0, 2 0, 1 1.2))'));
+select st_touches(geomfromtext('polygon((0 0, 2 2, 0 4, 0 0))'), geomfromtext('polygon((1 1, 1 0, 2 0, 1 1))'));
+
+#Equals test
+SELECT ST_Equals(PolyFromText('POLYGON((67 13, 67 18, 67 18, 59 18, 59 13, 67 13) )'),PolyFromText('POLYGON((67 13, 67 18, 59 19, 59 13, 59 13, 67 13) )')) as result;
+SELECT ST_Equals(PolyFromText('POLYGON((67 13, 67 18, 67 18, 59 18, 59 13, 67 13) )'),PolyFromText('POLYGON((67 13, 67 18, 59 18, 59 13, 59 13, 67 13) )')) as result;
+SELECT ST_Equals(PointFromText('POINT (12 13)'),PointFromText('POINT (12 13)')) as result;
+
+# bug #801243 Assertion `(0)' failed in Gis_geometry_collection::init_from_opresult on ST_UNION
+
+SELECT astext(ST_UNION (
+ PolyFromText('POLYGON(( 2 2 ,3 2,2 7,2 2),( 0 0,8 2,1 9,0 0))'),
+ ExteriorRing( Envelope( MultiLineStringFromText('MULTILINESTRING((3 4,5 3),(3 0,0 5))')))));
+
+#bug 801189 ST_BUFFER asserts if radius = 0
+SELECT astext(ST_BUFFER(LineStringFromText('LINESTRING(0 0,1 1)'),0));
+
+#bug 801199 Infinite recursion in Gcalc_function::count_internal with ST_BUFFER over MULTIPOINT
+SELECT Round(ST_Area(ST_BUFFER(MultipointFromText('MULTIPOINT(7 7,3 7,7 2,7 4 ,7 7)'), 3)), 5);
+
+#bug 801212 Assertion with ST_INTERSECTION on NULL values
+SELECT ST_INTERSECTION(NULL, NULL);
+
+#bug 804305 Crash in wkb_get_double with ST_INTERSECTION
+SELECT ASTEXT(ST_INTERSECTION(
+ MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((2 2,2 8,8 8,8 2,2 2),(4 4,4 6,6 6,6 4,4 4)),
+ ((0 5,3 5,3 0,0 0,0 1,2 1,2 2,0 2,0 5), (1 3,2 3,2 4,1 4,1 3)),
+ ((2 2,5 2,4 4,2 8,2 2)))'),
+ MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((3 5,2 4,2 5,3 5)),
+ ((2 2,9 2,0 2,2 6,2 2)),
+ ((2 2,2 8,8 8,8 2,2 2), (4 4,4 6,6 6,6 4,4 4)),
+ ((9 9,6 8,7 0,9 9)))')));
+
+#bug 804324 Assertion 0 in Gcalc_scan_iterator::pop_suitable_intersection
+
+SELECT ASTEXT(ST_UNION(
+ MULTILINESTRINGFROMTEXT('MULTILINESTRING((6 2,4 0,3 5,3 6,4 3,6 4,3 9,0 7,3 7,8 4,2 9,5 0),
+ (8 2,1 3,9 0,4 4))'),
+ MULTILINESTRINGFROMTEXT('MULTILINESTRING((2 5,6 7,9 7,5 2,1 6,3 6))')));
+
+SELECT ST_NUMGEOMETRIES((ST_UNION(ST_UNION(
+ MULTILINESTRINGFROMTEXT('MULTILINESTRING((2 0,4 2,0 2,1 5,0 3,7 0,8 5,5 8),
+ (6 2,4 0,3 5,3 6,4 3,6 4,3 9,0 7,3 7,8 4,2 9,5 0),
+ (7 8,3 1,0 9,6 0,4 8),
+ (9 3,0 4,5 9,6 4),
+ (8 2,1 3,9 0,4 4))'),
+ MULTILINESTRINGFROMTEXT('MULTILINESTRING((6 0,9 3,2 5,3 6,3 2),
+ (2 5,6 7,9 7,5 2,1 6,3 6))')),
+ MULTIPOLYGONFROMTEXT('MULTIPOLYGON(((7 7,3 7,3 1,7 8,7 7)),
+ ((3 5,2 4,2 5,3 5)),
+ ((7 7,8 7,3 7,7 7,7 7)),
+ ((0 5,3 5,3 4,1 4,1 3,3 3,3 0,0 0,0 5), (1 1,2 1,2 2,1 2,1 1)))'))));
+
+#bug #805860 Second assertion Assertion `n > 0 && n < SINUSES_CALCULATED*2+1' in get_n_sinco
+
+SELECT Round(ST_AREA(ST_BUFFER( ST_UNION(
+ POLYGONFROMTEXT('POLYGON((7 7, 7 7, 7 4, 7 7, 7 7))'),
+ POLYGONFROMTEXT('POLYGON((7 7, 4 7, 2 9, 7 6, 7 7))')), 1)), 6);
+
+
+#bug #804259 Second assertion in Gis_geometry_collection::init_from_opresult
+
+SELECT AsText(ST_UNION(MultiPolygonFromText('
+ MULTIPOLYGON(((2 2, 2 8, 8 8, 8 2, 2 2), (4 4, 4 6, 6 6, 6 4, 4 4)),
+ ((0 0, 8 3, 7 4, 0 0)),
+ ((2 2, 2 8, 8 8, 8 2, 2 2), (4 4, 4 6, 6 6, 6 4, 4 4)))'),
+MultiPolygonFromText(' MULTIPOLYGON(((0 0, 1 9, 4 6, 0 0)),
+ ((0 5, 3 5, 3 4, 1 4, 1 3, 3 3, 3 0, 0 0, 0 5), (1 1, 2 1, 2 2, 1 2, 1 1)),
+ ((7 7, 4 7, 6 3, 7 2, 7 7)),
+ ((0 5, 3 5, 3 4, 1 4, 1 3, 3 3, 3 0, 0 0, 0 5), (1 1, 2 1, 2 2, 1 2, 1 1))) ')));
+
+
+#bug 801217 Assertion `t1->result_range' in Gcalc_operation_reducer::end_couple
+
+SELECT AsText(ST_SYMDIFFERENCE(
+ MultiLineStringFromText('MULTILINESTRING((7 7, 1 7, 8 5, 7 8, 7 7),
+ (6 3, 3 4, 1 1, 9 9, 9 0, 8 4, 9 9))'),
+ Envelope(GeometryFromText('MULTIPOINT(7 9, 0 0, 3 7, 1 6, 0 0)'))));
+
+#bug 804266 Memory corruption/valgrind warning/crash in move_hole() with ST_UNION
+
+SELECT AsText(ST_UNION(
+ MultiPolygonFromText('MULTIPOLYGON(((9 9, 7 9, 1 1, 9 9)),
+ ((2 2, 1 2, 3 3, 2 2, 2 2)),
+ ((0 0, 7 5, 9 6, 0 0)),
+ ((7 7, 5 7, 1 5, 7 1, 7 7)))'),
+ MultiPolygonFromText('MULTIPOLYGON(((2 2, 2 2, 1 5, 2 7, 2 2)),
+ ((0 5, 3 5, 3 0, 0 0, 0 5), (1 1, 2 1, 2 4, 1 4, 1 1)))')));
+
+#bug 802376 ST_INTERSECTION returns wrong result on two overlapping linestrings in maria-5.3-gis
+
+SELECT AsText( ST_INTERSECTION(
+ LinestringFromText('LINESTRING( 3 5, 2 5, 2 4, 3 4, 3 5 ) ') ,
+ LinestringFromText('LINESTRING( 3 5, 2 4, 2 5, 3 5 ) ')
+));
+
+#bug 801560 ST_UNION of adjacent polygons includes extra line in maria-5.3-gis
+
+SELECT AsText( ST_UNION(
+ PolygonFromText(' POLYGON( ( 2 2 , 3 2 , 7 5 , 2 0 , 2 2 ) ) ') ,
+ PolygonFromText(' POLYGON( ( 2 2 , 3 2 , 3 3 , 2 5 , 2 2 ) ) ') ) );
+
+#bug 801466 ST_INTERSECTION() returns invalid value on empty intersection in maria-5.3-gis
+
+SELECT AsText(ST_INTERSECTION(LinestringFromText('LINESTRING(1 1, 2 2)'), GeometryFromText('LINESTRING(3 3, 4 4)')));
+
+#bug 839341 100% CPU usage with ST_UNION in maria-5.3-gis
+SELECT AsText(ST_UNION(GEOMETRYFROMTEXT('POINT(8 1)') ,MULTILINESTRINGFROMTEXT('MULTILINESTRING((3 5, 2 5, 2 4, 3 4, 3 5))')));
+
+#bug 839318 Crash in Gcalc_scan_iterator::point::get_shape with ST_DISTANCE and MULTILINESTRING in maria-5.3-gis
+SELECT ST_DISTANCE(POINTFROMTEXT('POINT(7 1)'),MULTILINESTRINGFROMTEXT('MULTILINESTRING(
+ (4 7,9 7,6 1,3 4,1 1), (3 5, 2 5, 2 4, 3 4, 3 5))'));
+
+#bug 839327 Crash in Gcalc_operation_reducer::end_couple with ST_UNION and MULTIPOLYGONs in 5.3-gis
+SELECT AsText(ST_UNION(POLYGONFROMTEXT('POLYGON((12 9, 3 6, 3 0, 12 9))'), POLYGONFROMTEXT('POLYGON((2 2, 7 2, 4 2, 2 0, 2 2))')));
+
+#bug 841622 Assertion `t->rp->type == Gcalc_function::shape_line' failed in Gcalc_operation_reducer::end_line in maria-5.3-gis
+
+SELECT ST_NUMPOINTS(ST_EXTERIORRING(ST_BUFFER(ST_UNION(
+ MULTILINESTRINGFROMTEXT('MULTILINESTRING((3 4, 2 5, 7 6, 1 8),(0 0 ,1 6 ,0 1, 8 9, 2 4, 6 1, 3 5, 4 8), (9 3, 5 4, 1 8, 4 2, 5 8, 3 0))' ) ,
+ MULTILINESTRINGFROMTEXT('MULTILINESTRING((3 4, 3 1, 2 7, 4 2, 6 2, 1 5))')
+ ), 16)));
+
+#bug 841625 Assertion `m_poly_borders->next' failed in Gcalc_operation_reducer::count_slice in maria-5.3-gis
+
+SELECT ST_NUMGEOMETRIES(ST_DIFFERENCE (
+ ST_UNION (
+ MULTILINESTRINGFROMTEXT( ' MULTILINESTRING( ( 2 4 , 5 0 , 2 9 , 6 2 , 0 2 ) , ( 4 3 , 5 6 , 9 4 , 0 7 , 7 2 , 2 0 , 8 2 ) , ( 5 0 , 1 5 , 3 7 , 7 7 ) , ( 2 3 , 9 5 , 2 0 , 8 1 ) , ( 0 9 , 9 3 , 2 8 , 8 1 , 9 4 ) ) ' ),
+ ST_UNION (
+ MULTIPOLYGONFROMTEXT( ' MULTIPOLYGON( ( ( 2 2 , 7 2 , 6 2 , 2 6 , 2 2 ) ) , ( (3 5, 2 5, 2 4, 3 4, 3 5) ) ) ' ) ,
+ ENVELOPE(
+ MULTIPOLYGONFROMTEXT( ' MULTIPOLYGON( ( (3 5, 2 5, 2 4, 3 4, 3 5) ) ) ' )
+ )
+ )
+ ),
+ MULTILINESTRINGFROMTEXT( ' MULTILINESTRING( ( 2 9 , 1 3 , 7 3 , 8 5 ) , ( 5 0 , 8 1 , 2 0 , 7 4 , 1 0 ) , ( 9 2 , 5 2 , 6 5 , 8 8 , 0 2 ) , ( 0 8 , 3 9 , 4 0 , 1 0 ) , ( 0 0 , 7 6 , 8 3 , 0 0 ) ) ' )
+));
+
+
+#bug 841745 ssertion `!sp0->is_bottom()' failed in Gcalc_scan_iterator::find_intersections in maria-5.3-gis
+SELECT ASTEXT(ST_DIFFERENCE (
+ POLYGONFROMTEXT( ' POLYGON( ( 2 2 , 2 8 , 8 8 , 8 2 , 2 2 ) , ( 4 4 , 4 6 , 6 6 , 6 4 , 4 4 ) ) ' ) ,
+ ST_UNION (
+ MULTILINESTRINGFROMTEXT( ' MULTILINESTRING( (3 5, 2 5, 2 4, 3 4, 3 5) ) ' ) ,
+ ST_SYMDIFFERENCE (
+ MULTILINESTRINGFROMTEXT( ' MULTILINESTRING( ( 3 8 , 0 8 , 6 6 , 6 1 , 0 5 , 6 7 , 3 7 ) , ( 5 8 , 7 7 , 9 0 , 8 7 ) , ( 1 5 , 1 8 , 2 3 , 3 9 ) , ( 1 3 , 9 7 , 5 5 , 0 8 , 6 9 ) , ( 3 6 , 6 9 , 8 7 , 0 2 , 4 6 , 9 5 ) ) ' ) ,
+ ST_UNION (
+ MULTILINESTRINGFROMTEXT( ' MULTILINESTRING( ( 9 4 , 2 0 , 2 2 , 7 2 , 6 2 ) , ( 5 2 , 8 2 , 4 8 , 3 4 ) , ( 1 0 , 1 4 , 2 7 , 7 0 , 1 5 ) , ( 2 8 , 4 4 , 5 0 , 7 0 , 4 0 ) ) ' ) ,
+ GEOMETRYFROMTEXT( ' MULTILINESTRING( ( 3 7 , 7 3 , 5 8 , 4 8 ) , ( 3 2 , 5 0 , 9 3 , 4 4 ) , ( 6 0 , 4 2 , 7 8 , 1 3 ) ) ' )
+ )
+ )
+ )
+));
+
+#bug 841773 Assertion `t0->rp->type == t1->rp->type' failed in Gcalc_operation_reducer::end_couple in maria-5.3-gis
+SELECT ST_NUMGEOMETRIES(ST_UNION (
+ MULTILINESTRINGFROMTEXT( ' MULTILINESTRING( ( 0 8 , 1 9 , 5 7 , 2 8 , 5 8 , 6 7 ) , ( 4 5 , 8 4 , 0 3 , 5 1 ) , ( 6 8 , 2 7 , 1 6 , 9 9 , 7 2 ) , ( 9 5 , 2 8 , 1 2 , 9 6 , 2 0 ) ) ' ) ,
+ MULTIPOLYGONFROMTEXT( ' MULTIPOLYGON( ( ( 7 7 , 2 7, 6 8, 7 1 , 7 7 ) ) ) ' )
+));
+
+#bug 841662 Third assertion `n > 0 && n < SINUSES_CALCULATED*2+1' in get_n_sincos
+SELECT ST_BUFFER (
+ LINESTRINGFROMTEXT( ' LINESTRING( 5 4 , 3 8 , 2 6 , 5 5 , 7 9 ) ' ) ,
+ ST_DISTANCE (
+ MULTIPOLYGONFROMTEXT( ' MULTIPOLYGON( ( (3 5, 2 4, 2 5, 3 5) ) , ( (3 5, 2 5, 2 4, 3 4, 3 5) ) , ( ( 0 0 , 8 3 , 9 5 , 0 0 ) ) ) ' ) ,
+ ST_DIFFERENCE (
+ MULTIPOLYGONFROMTEXT( ' MULTIPOLYGON( ( (3 5, 2 5, 2 4, 3 4, 3 5) ) ) ' ) ,
+ MULTIPOLYGONFROMTEXT( ' MULTIPOLYGON( ( ( 2 2 , 2 8 , 8 8 , 8 2 , 2 2 ) , ( 4 4 , 4 6 , 6 6 , 6 4 , 4 4 ) ) , ( ( 0 0 , 3 8 , 9 4 , 0 0 ) ) ) ' )
+ )
+ )
+ ) ;
+SELECT ST_DISTANCE ( ST_DIFFERENCE ( MULTIPOLYGONFROMTEXT( ' MULTIPOLYGON( ( (3 5, 2 5, 2 4, 3 4, 3 5) ) ) ' ) , MULTIPOLYGONFROMTEXT( ' MULTIPOLYGON( ( ( 2 2 , 2 8 , 8 8 , 8 2 , 2 2 ) , ( 4 4 , 4 6 , 6 6 , 6 4 , 4 4 ) ) , ( ( 0 0 , 3 8 , 9 4 , 0 0 ) ) ) ' ) ), MULTIPOLYGONFROMTEXT( ' MULTIPOLYGON( ( (3 5, 2 4, 2 5, 3 5) ) , ( (3 5, 2 5, 2 4, 3 4, 3 5) ) , ( ( 0 0 , 8 3 , 9 5 , 0 0 ) ) ) ' ) ) ;
+
+#bug 848939 Wrong result with ST_INTERSECTION between linestrings and a polygon in 5.3-gis
+SELECT ASTEXT(ST_INTERSECTION( GEOMETRYFROMTEXT('GEOMETRYCOLLECTION(LINESTRING(7 7,5.33333333333333 7),LINESTRING(5.33333333333333 7,0 7,5 8,5.33333333333333 7),LINESTRING(5.33333333333333 7,7 2,7 7),POLYGON((0 5,3 5,3 2,1 2,1 1,3 1,3 0,0 0,0 3,2 3,2 4,0 4,0 5)))'), geomETRYFROMTEXT(' MULTILINESTRING( ( 5 1 , 3 7 , 6 1 , 7 0 ) , ( 1 6 , 8 5 , 7 5 , 5 6 ) )') ));
+
+#bug 855485 ST_CROSSES returns different result than PostGIS for overlapping polygons
+
+SELECT ST_CROSSES( GEOMETRYFROMTEXT(' POLYGON( (3 5, 2 4, 2 5, 3 5) ) ') , POLYGONFROMTEXT(' POLYGON((2 4,3 4,3 5,2 5,2 4)) '));
+
+#bug 855487 ST_WITHIN returns wrong result for partially overlapping polygons
+
+SELECT ST_WITHIN( POLYGONFROMTEXT(' POLYGON( (0 5, 3 5, 3 4, 2 0 , 1 0, 2 4 , 0 4, 0 5) ) ') , POLYGONFROMTEXT(' POLYGON( (0 5, 3 5, 3 4, 1 4 , 1 3 , 3 3 , 3 0 , 0 0 , 0 5), ( 1 1 , 2 1 , 2 2 , 1 2 , 1 1 ) ) ') );
+
+#bug 855492 ST_WITHIN returns TRUE on point on the edge of a polygon
+
+SELECT ST_WITHIN( POINTFROMTEXT(' POINT(1 2 ) ') , MULTIPOLYGONFROMTEXT(' MULTIPOLYGON( ( (0 5, 3 5, 3 0, 0 0, 0 5), ( 1 1 , 2 1 , 2 4, 1 4, 1 1 ) ) ) '));
+
+#bug 855497 ST_ENVELOPE of GEOMETRYCOLLECTION EMPTY returns NULL and not GEOMETRYCOLLECTION EMPTY
+
+select ST_ASTEXT(envelope(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION EMPTY')));
+
+#bug 855503 ST_EQUALS reports TRUE between a POLYGON and a MULTILINESTRING
+
+SELECT ST_EQUALS( GEOMETRYFROMTEXT(' MULTILINESTRING( (3 5, 2 5, 2 4, 3 4, 3 5) ) ') , GEOMETRYFROMTEXT(' POLYGON( (3 5, 2 5, 2 4, 3 4, 3 5) ) ') );
+
+#bug 855505 ST_TOUCHES reports TRUE for intersecting polygon and linestring
+
+SELECT ST_TOUCHES( GEOMETRYFROMTEXT(' LINESTRING( 1 1 , 1 4 , 5 0 , 8 3 ) ') , POLYGONFROMTEXT(' POLYGON( ( 2 2 , 2 8 , 8 8 , 8 2 , 2 2 ) , ( 4 4 , 4 6 , 6 6 , 6 4 , 4 4 ) ) ') );
+
+#bug 857051 ST_EQUALS returns TRUE on two nonidentical MULTIPOINTs
+
+SELECT ST_EQUALS( MULTIPOINTFROMTEXT(' MULTIPOINT( 5 1 , 6 9 , 1 4 , 4 0 ) ') , MULTIPOINTFROMTEXT(' MULTIPOINT( 5 8 , 5 2 , 1 8 , 3 0 , 3 0 , 7 8 ) ') );
+SELECT ST_EQUALS( MULTIPOINTFROMTEXT(' MULTIPOINT( 5 1 , 6 9 , 1 4 , 4 0 ) ') , MULTIPOINTFROMTEXT('MULTIPOINT( 4 0 , 6 9 , 5 1, 1 4 )') );
+
+#bug 857050 ST_WITHIN returns wrong result with MULTIPOINT and POLYGON
+SELECT ST_WITHIN( MULTIPOINTFROMTEXT(' MULTIPOINT( 2 9 , 2 9 , 4 9 , 9 1 ) ') , POLYGONFROMTEXT(' POLYGON( ( 2 2 , 2 8 , 8 8 , 8 2 , 2 2 ) , ( 4 4 , 4 6 , 6 6 , 6 4 , 4 4 ) ) '));
+
+#bug 857087 Wrong result with ST_INTERSECTS and LINESTRINGs
+
+SELECT ST_INTERSECTS( GeomFromText('MULTILINESTRING( ( 4030 3045 , 3149 2461 , 3004 3831 , 3775 2976 ) )') , GeomFromText('LINESTRING(3058.41 3187.91,3081.52 3153.19,3042.99 3127.57,3019.89 3162.29,3039.07 3175.05,3039.07 3175.05,3058.41 3187.91,3081.52 3153.19,3042.99 3127.57,3019.89 3162.29)') );
+
diff --git a/mysql-test/t/gis-rt-precise.test b/mysql-test/t/gis-rt-precise.test
new file mode 100644
index 00000000000..4cae10a9076
--- /dev/null
+++ b/mysql-test/t/gis-rt-precise.test
@@ -0,0 +1,64 @@
+-- source include/have_geometry.inc
+
+#
+# test of rtree (using with spatial data)
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (
+ fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ g GEOMETRY NOT NULL,
+ SPATIAL KEY(g)
+) ENGINE=MyISAM;
+
+SHOW CREATE TABLE t1;
+
+--disable_query_log
+let $1=150;
+let $2=150;
+while ($1)
+{
+ eval INSERT INTO t1 (g) VALUES (GeomFromText('LineString($1 $1, $2 $2)'));
+ dec $1;
+ inc $2;
+}
+--enable_query_log
+
+SELECT count(*) FROM t1;
+EXPLAIN SELECT fid, AsText(g) FROM t1 WHERE ST_Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 140))'));
+SELECT fid, AsText(g) FROM t1 WHERE ST_Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (
+ fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+ g GEOMETRY NOT NULL
+) ENGINE=MyISAM;
+
+--disable_query_log
+let $1=10;
+while ($1)
+{
+ let $2=10;
+ while ($2)
+ {
+ eval INSERT INTO t1 (g) VALUES (LineString(Point($1 * 10 - 9, $2 * 10 - 9), Point($1 * 10, $2 * 10)));
+ dec $2;
+ }
+ dec $1;
+}
+--enable_query_log
+
+ALTER TABLE t1 ADD SPATIAL KEY(g);
+SHOW CREATE TABLE t1;
+SELECT count(*) FROM t1;
+EXPLAIN SELECT fid, AsText(g) FROM t1 WHERE ST_Within(g,
+ GeomFromText('Polygon((40 40,60 40,60 60,40 40))'));
+SELECT fid, AsText(g) FROM t1 WHERE ST_Within(g,
+ GeomFromText('Polygon((40 40,60 40,60 60,40 40))'));
+
+DROP TABLE t1;
+
+--echo End of 5.5 tests.
diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test
index b99d35ecd48..e7e6fa59df0 100644
--- a/mysql-test/t/gis-rtree.test
+++ b/mysql-test/t/gis-rtree.test
@@ -17,16 +17,12 @@ SHOW CREATE TABLE t1;
let $1=150;
let $2=150;
---disable_query_log
-begin;
while ($1)
{
eval INSERT INTO t1 (g) VALUES (GeomFromText('LineString($1 $1, $2 $2)'));
dec $1;
inc $2;
}
-commit;
---enable_query_log
SELECT count(*) FROM t1;
EXPLAIN SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
@@ -39,8 +35,6 @@ CREATE TABLE t2 (
g GEOMETRY NOT NULL
) ENGINE=MyISAM;
---disable_query_log
-begin;
let $1=10;
while ($1)
{
@@ -52,8 +46,6 @@ while ($1)
}
dec $1;
}
-commit;
---enable_query_log
ALTER TABLE t2 ADD SPATIAL KEY(g);
SHOW CREATE TABLE t2;
@@ -63,8 +55,6 @@ EXPLAIN SELECT fid, AsText(g) FROM t2 WHERE Within(g,
SELECT fid, AsText(g) FROM t2 WHERE Within(g,
GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))'));
---disable_query_log
-begin;
let $1=10;
while ($1)
{
@@ -77,8 +67,6 @@ while ($1)
}
dec $1;
}
-commit;
---enable_query_log
DROP TABLE t2;
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 104afddeee5..202e4f88cd8 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -62,7 +62,9 @@ INSERT INTO gis_multi_polygon VALUES
INSERT INTO gis_geometrycollection VALUES
(120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
-(121, GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))));
+(121, GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))),
+(122, GeomFromText('GeometryCollection()')),
+(123, GeomFromText('GeometryCollection EMPTY'));
INSERT into gis_geometry SELECT * FROM gis_point;
INSERT into gis_geometry SELECT * FROM gis_line;
@@ -539,18 +541,6 @@ insert into t1 values(default);
drop table t1;
#
-# 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;
-
-#
# Bug#24563: MBROverlaps does not seem to function propertly
# Bug#54888: MBROverlaps missing in 5.1?
#
@@ -773,14 +763,7 @@ drop table t1;
#
# Bug #50574 5.5.x allows spatial indexes on non-spatial
# columns, causing crashes!
-# Bug#11767480 SPATIAL INDEXES ON NON-SPATIAL COLUMNS
-# CAUSE CRASHES.
#
-CREATE TABLE t0 (a BINARY(32) NOT NULL);
---error ER_SPATIAL_MUST_HAVE_GEOM_COL
-CREATE SPATIAL INDEX i on t0 (a);
-INSERT INTO t0 VALUES (1);
-
--error ER_SPATIAL_MUST_HAVE_GEOM_COL
CREATE TABLE t1(
col0 BINARY NOT NULL,
@@ -818,7 +801,542 @@ CREATE TABLE t3 (
);
# cleanup
-DROP TABLE t0, t1, t2;
+DROP TABLE t1;
+DROP TABLE t2;
+
+#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))'));
+
+#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))'));
+
+
+# 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
+# TODO: ST_SRID() alias
+SELECT SRID(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+
+--echo # Conformance Item T11
+# TODO: ST_IsEmpty() alias
+SELECT IsEmpty(centerline)
+FROM road_segments
+WHERE name = 'Route 5'
+AND aliases = 'Main Street';
+
+# FIXME: get wrong result:0, expected 1.
+#--echo # Conformance Item T12
+# TODO: ST_IsSimple() alias
+#SELECT IsSimple(shore)
+#FROM lakes
+#WHERE name = 'Blue Lake';
+
+# TODO: WL#2377
+#--echo # Conformance Item T13
+#SELECT AsText(Boundary((boundary),101)
+#FROM named_places
+#WHERE name = 'Goose Island';
+
+--echo # Conformance Item T14
+# TODO: ST_Envelope( ) alias
+# 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
+# TODO: ST_EndPoint
+SELECT AsText(EndPoint(centerline))
+FROM road_segments
+WHERE fid = 102;
+
+# TODO: WL#2377
+#--echo # Conformance Item T19
+# TODO: ST_LineFromWKB() alias
+#SELECT IsClosed(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary)))
+#FROM named_places
+#WHERE name = 'Goose Island';
+
+# TODO: WL#2377
+#--echo # Conformance Item T20
+#SELECT IsRing(LineFromWKB(AsBinary(Boundary(boundary)),SRID(boundary)))
+#FROM named_places
+#WHERE name = 'Goose Island';
+
+--echo # Conformance Item T21
+# TODO: ST_Length() alias
+SELECT GLength(centerline)
+FROM road_segments
+WHERE fid = 106;
+
+--echo # Conformance Item T22
+# TODO: ST_NumPoints() alias
+SELECT NumPoints(centerline)
+FROM road_segments
+WHERE fid = 102;
+
+--echo # Conformance Item T23
+# TODO: ST_PointN() alias
+SELECT AsText(PointN(centerline, 1))
+FROM road_segments
+WHERE fid = 102;
+
+--echo # Conformance Item T24
+# TODO: ST_Centroid() alias
+SELECT AsText(Centroid(boundary))
+FROM named_places
+WHERE name = 'Goose Island';
+
+# TODO: WL#2377
+#--echo # Conformance Item T25
+#SELECT Contains(boundary, PointOnSurface(boundary))
+#FROM named_places
+#WHERE name = 'Goose Island';
+
+--echo # Conformance Item T26
+# TODO: ST_Area() alias
+SELECT Area(boundary)
+FROM named_places
+WHERE name = 'Goose Island';
+
+--echo # Conformance Item T27
+# TODO: ST_ExteriorRing() alias
+SELECT AsText(ExteriorRing(shore))
+FROM lakes
+WHERE name = 'Blue Lake';
+
+--echo # Conformance Item T28
+# TODO: ST_NumInteriorRings() alias
+SELECT NumInteriorRings(shore)
+FROM lakes
+WHERE name = 'Blue Lake';
+
+--echo # Conformance Item T29
+# TODO: ST_InteriorRingN() alias
+SELECT AsText(InteriorRingN(shore, 1))
+FROM lakes
+WHERE name = 'Blue Lake';
+
+--echo # Conformance Item T30
+# TODO: ST_NumGeometries() alias
+SELECT NumGeometries(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+
+--echo # Conformance Item T31
+# TODO: ST_GeometryN() alias
+SELECT AsText(GeometryN(centerlines, 2))
+FROM divided_routes
+WHERE name = 'Route 75';
+
+--echo # Conformance Item T32
+# TODO: ST_IsClosed() alias
+SELECT IsClosed(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+
+--echo # Conformance Item T33
+# TODO: ST_Length() alias
+SELECT GLength(centerlines)
+FROM divided_routes
+WHERE name = 'Route 75';
+
+--echo # Conformance Item T34
+# TODO: ST_Centroid() alias
+SELECT AsText(Centroid(shores))
+FROM ponds
+WHERE fid = 120;
+
+# TODO: WL#2377
+#--echo # Conformance Item T35
+#SELECT Contains(shores, PointOnSurface(shores))
+#FROM ponds
+#WHERE fid = 120;
+
+--echo # Conformance Item T36
+# TODO: ST_Area() alias
+SELECT Area(shores)
+FROM ponds
+WHERE fid = 120;
+
+--echo # Conformance Item T37
+# TODO: ST_PolyFromText() alias
+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';
+
+# FIXME: wrong result: get 0, expected 1
+#--echo # Conformance Item T40
+#SELECT ST_Within(boundary, footprint)
+#FROM named_places, buildings
+#WHERE named_places.name = 'Ashton'
+#AND buildings.address = '215 Main Street';
+
+# FIXME: wrong result: get 0, expected 1
+#--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';
+
+--echo # Conformance Item T42
+# FIXME: TODO: ST_Crosses() alias
+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';
+
+# TODO: WL#2377
+#--echo # Conformance Item T45
+#SELECT Relate(forests.boundary, named_places.boundary, 'TTTTTTTTT')
+#FROM forests, named_places
+#WHERE forests.name = 'Green Forest'
+#AND named_places.name = 'Ashton';
+
+--echo # Conformance Item T46
+SELECT ST_Distance(position, boundary)
+FROM bridges, named_places
+WHERE bridges.name = 'Cam Bridge'
+AND named_places.name = 'Ashton';
+
+# FIXME: wrong result: NULL, expected 12
+#--echo # Conformance Item T47
+#SELECT AsText(ST_Intersection(centerline, shore))
+#FROM streams, lakes
+#WHERE streams.name = 'Cam Stream'
+#AND lakes.name = 'Blue Lake';
+
+--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;
+
+# TODO: WL#2377
+#--echo # Conformance Item T52
+#SELECT AsText(ConvexHull(shore))
+#FROM lakes
+#WHERE lakes.name = 'Blue Lake';
+
+DROP DATABASE gis_ogs;
+USE test;
--echo #
--echo # Bug#11908153: CRASH AND/OR VALGRIND ERRORS IN FIELD_BLOB::GET_KEY_IMAGE
@@ -838,5 +1356,5 @@ FORCE INDEX(i) WHERE a = date_sub(now(), interval 2808.4 year_month)
DROP TABLE g1;
-
--echo End of 5.5 tests
+
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index 18329a1aa75..e660c017854 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -3379,3 +3379,31 @@ execute stmt using @a;
set @a='2010-08-08';
execute stmt using @a;
execute stmt using @a;
+
+--echo #
+--echo # Bug #892725: look-up is changed for a full scan when executing PS
+--echo #
+
+create table t1 (a int primary key, b int);
+insert into t1 values
+ (7,70), (3,40), (4,40), (8,70), (1,70), (9,50), (2,70);
+
+prepare st from 'select * from t1 where a=8';
+
+flush status;
+execute st;
+show status like '%Handler_read%';
+flush status;
+execute st;
+show status like '%Handler_read%';
+flush status;
+select * from t1 use index() where a=3;
+show status like '%Handler_read%';
+flush status;
+execute st;
+show status like '%Handler_read%';
+
+deallocate prepare st;
+
+drop table t1;
+
diff --git a/mysql-test/t/range_vs_index_merge.test b/mysql-test/t/range_vs_index_merge.test
index 2ffa2d8eb49..de7e6a0838c 100755
--- a/mysql-test/t/range_vs_index_merge.test
+++ b/mysql-test/t/range_vs_index_merge.test
@@ -1030,9 +1030,8 @@ SELECT * FROM t1,t2,t3
DROP TABLE t1,t2,t3;
#
-# LP bug #823301: index merge union with prossible index scan
+# LP bug #823301: index merge sort union with possible index scan
#
-#
CREATE TABLE t1 (
a int, b int, c int, d int,
@@ -1058,5 +1057,38 @@ SET SESSION optimizer_switch=DEFAULT;
DROP TABLE t1;
+#
+# LP bug #800184: possible index merge sort union
+#
+
+CREATE TABLE t1 (a int NOT NULL PRIMARY KEY, b int, c int, INDEX idx(c,b));
+INSERT INTO t1 VALUES (19,1,NULL), (20,5,7);
+
+EXPLAIN
+SELECT * FROM t1
+ WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND
+ (t1.c=0 OR t1.a=500);
+SELECT * FROM t1
+ WHERE t1.a>300 AND t1.c!=0 AND t1.b>=350 AND t1.b<=400 AND
+ (t1.c=0 OR t1.a=500);
+
+DROP TABLE t1;
+
+#
+# LP bug #891953: always true OR
+#
+
+CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b));
+INSERT INTO t1 VALUES (167,9999), (168,10000);
+
+EXPLAIN
+SELECT * FROM t1
+ WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2;
+SELECT * FROM t1
+ WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2;
+
+DROP TABLE t1;
+
#the following command must be the last one in the file
set session optimizer_switch='index_merge_sort_intersection=default';
+
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 6369e4edbeb..3d7050ebc25 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5063,4 +5063,14 @@ SET SESSION sql_mode=@old_sql_mode;
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
+--echo #
+--echo # LP BUG#833777 Performance regression with deeply nested subqueries
+--echo #
+
+create table t1 (a int not null, b char(10) not null);
+insert into t1 values (1, 'a');
+set @@optimizer_switch='in_to_exists=on,semijoin=off,materialization=off,subquery_cache=off';
+select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1 where a in (select a from t1)))))))))))))))))))))))))))));
+drop table t1;
+
set optimizer_switch=@subselect_tmp;
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test
index 13c4b532c74..3800b019462 100644
--- a/mysql-test/t/subselect3.test
+++ b/mysql-test/t/subselect3.test
@@ -872,12 +872,17 @@ create table t12 select * from t1 where b = 67 AND (c IS NULL OR c > NOW()) orde
create table t21 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 asc;
create table t22 select * from t1 where b = 67 AND (c IS NULL OR c > '2005-12-08') order by 3 desc;
+set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch='materialization=off';
+
update t22 set c = '2005-12-08 15:58:27' where a = 255;
explain select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
select t21.* from t21,t22 where t21.a = t22.a and
t22.a in (select t12.a from t11, t12 where t11.a in(255,256) and t11.a = t12.a and t11.c is null) and t22.c is null order by t21.a;
+set @@optimizer_switch=@save_optimizer_switch;
+
drop table t1, t11, t12, t21, t22;
#
@@ -888,7 +893,7 @@ drop table t1, t11, t12, t21, t22;
create table t1(a int);
insert into t1 values (0),(1);
-set @@optimizer_switch='firstmatch=off';
+set @@optimizer_switch='firstmatch=off,materialization=off';
explain
select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
select (select max(Y.a) from t1 Y where a in (select a from t1 Z) and a < X.a) as subq from t1 X;
@@ -914,6 +919,9 @@ select * from t1 where 2 in (select a from t0);
set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch='materialization=off';
+
#
# FirstMatch referring to a derived table
#
@@ -943,6 +951,8 @@ and t4.pk=t1.c);
drop table t1, t3, t4;
+set @@optimizer_switch=@save_optimizer_switch;
+
#
# Test if we handle duplicate elimination temptable overflowing to disk
#
@@ -997,6 +1007,9 @@ explain select * from t1 where a in (select a from t1);
drop table t1;
set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch=@save_optimizer_switch;
+set @@optimizer_switch='materialization=off';
+
#
# SJ-Materialization-scan for non-first table
#
@@ -1040,6 +1053,8 @@ explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
drop table t1,t2;
+set @@optimizer_switch=@save_optimizer_switch;
+
#
# Multi-column sj-materialization with lookups
#
@@ -1048,7 +1063,7 @@ insert into t1 select a,a from t0;
create table t2 (a int, b int);
insert into t2 select A.a + 10*B.a, A.a + 10*B.a from t0 A, t0 B;
-set @@optimizer_switch='firstmatch=off';
+set @@optimizer_switch='firstmatch=off,materialization=off';
explain select * from t1 where (a,b) in (select a,b from t2);
# A smallish test if find_best() still works for semi-join optimization:
@@ -1060,6 +1075,7 @@ set @@optimizer_switch=@save_optimizer_switch;
drop table t0, t1, t2;
+set @@optimizer_switch='materialization=off';
#
# Primitive SJ-Materialization tests for DECIMAL and DATE
@@ -1094,6 +1110,8 @@ explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y,
drop table t0,t1,t2;
+set @@optimizer_switch=@save_optimizer_switch;
+
--echo
--echo BUG#37842: Assertion in DsMrr_impl::dsmrr_init, at handler.cc:4307
--echo
@@ -1150,12 +1168,15 @@ create table t3 (
insert into t3 values (1,1, 'foo'), (2,2,'bar');
--echo The following must be converted to a semi-join:
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='materialization=off';
explain extended SELECT a.idIndividual FROM t1 a
WHERE a.idIndividual IN
( SELECT c.idObj FROM t3 cona
INNER JOIN t2 c ON c.idContact=cona.idContact
WHERE cona.postalStripped='T2H3B2'
);
+set @@optimizer_switch=@save_optimizer_switch;
drop table t1,t2,t3;
--echo #
diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test
index 41ca1762fef..a5a4e0a3cfe 100644
--- a/mysql-test/t/subselect4.test
+++ b/mysql-test/t/subselect4.test
@@ -126,6 +126,9 @@ INSERT INTO t4 VALUES (1), (2), (3);
CREATE TABLE t5 ( a INT );
INSERT INTO t5 VALUES (NULL), (2);
+SET @old_optimizer_switch = @@session.optimizer_switch;
+SET SESSION optimizer_switch = 'materialization=off,in_to_exists=on,semijoin=off';
+
--replace_column 1 x 3 x 4 x 5 x 6 x 7 x 8 x 9 x 10 x
EXPLAIN
SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 );
@@ -167,6 +170,8 @@ SELECT * FROM t1 WHERE b NOT IN ( SELECT c FROM t2 WHERE c = 1 );
SELECT * FROM t1 WHERE NULL NOT IN ( SELECT c FROM t2 WHERE c = 1 AND c <> 1 );
+SET SESSION optimizer_switch = @old_optimizer_switch;
+
DROP TABLE t1, t2, t3, t4, t5;
--echo #
diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test
index 2e5dd71f9af..4ce19012e0c 100644
--- a/mysql-test/t/subselect_mat_cost_bugs.test
+++ b/mysql-test/t/subselect_mat_cost_bugs.test
@@ -323,7 +323,7 @@ create index key1 on t2 (kp1, kp2);
create index key2 on t2 (kp1);
create index key3 on t2 (kp2);
-set session optimizer_switch='default';
+SET @@optimizer_switch='materialization=off,semijoin=off,in_to_exists=on';
analyze table t2;
@@ -331,6 +331,8 @@ explain
select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7;
select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7;
+set @@optimizer_switch='default';
+
drop table t1, t2;
--echo #
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index 740289ffcc8..5ae968742aa 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -1880,5 +1880,35 @@ set optimizer_switch= @tmp_otimizer_switch;
drop table t1,t2;
+--echo #
+--echo # Bug #887496: semijoin with IN equality for the second part of an index
+--echo #
+
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (9), (0), (8), (5);
+
+CREATE TABLE t2 (a int, b varchar(1), INDEX idx (b,a));
+INSERT INTO t2 VALUES (5,'r'), (5,'z');
+
+CREATE TABLE t3 (a int, b varchar(1), INDEX idx (b,a));
+INSERT INTO t3 VALUES (5,'r'), (5,'z');
+
+set @tmp_otimizer_switch= @@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=on,firstmatch=on';
+
+SET SESSION optimizer_switch='loosescan=off';
+EXPLAIN
+SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b);
+SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b);
+
+SET SESSION optimizer_switch='loosescan=on';
+EXPLAIN
+SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b);
+SELECT * FROM t1 WHERE a IN (SELECT t2.a FROM t2,t3 WHERE t2.b = t3.b);
+
+set optimizer_switch= @tmp_otimizer_switch;
+
+DROP TABLE t1,t2,t3;
+
# The following command must be the last one the file
set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/t/subselect_sj_jcl6.test b/mysql-test/t/subselect_sj_jcl6.test
index e475adbf9a8..fc539ec1a01 100644
--- a/mysql-test/t/subselect_sj_jcl6.test
+++ b/mysql-test/t/subselect_sj_jcl6.test
@@ -43,6 +43,48 @@ SELECT * FROM t0 WHERE t0.a IN
set optimizer_switch=@save_optimizer_switch;
drop table t0, t1, t2;
+--echo #
+--echo # Bug #891995: IN subquery with join_cache_level >= 3
+--echo #
+
+CREATE TABLE t1 (a varchar(1));
+INSERT INTO t1 VALUES ('w'),('q');
+
+CREATE TABLE t2 (a int NOT NULL, b time, PRIMARY KEY (a));
+INSERT INTO t2 VALUES
+ (2,'18:56:33'), (5,'19:11:10'), (3,'18:56:33'), (7,'19:11:10');
+
+CREATE TABLE t3 (
+ a int NOT NULL, b int, c int, d varchar(1), PRIMARY KEY (a), KEY (d,c)
+);
+INSERT INTO t3 VALUES
+ (25,158,10,'f'), (26,5,2,'v'), (27,163,103,'f'), (28,2,3,'q'), (29,8,6,'y');
+
+set @save_optimizer_switch=@@optimizer_switch;
+SET SESSION optimizer_switch='semijoin=on';
+
+SET SESSION optimizer_switch='join_cache_hashed=on';
+SET SESSION join_cache_level=3;
+EXPLAIN
+SELECT * FROM t1, t2
+ WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
+SELECT * FROM t1, t2
+ WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
+
+
+SET SESSION optimizer_switch='mrr=on';
+SET SESSION join_cache_level=6;
+EXPLAIN
+SELECT * FROM t1, t2
+ WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
+SELECT * FROM t1, t2
+ WHERE t2.a IN (SELECT b FROM t3 WHERE t3.d <= t1.a);
+
+set optimizer_switch=@save_optimizer_switch;
+set join_cache_level=default;
+
+DROP TABLE t1,t2,t3;
+
--echo # End
set join_cache_level=default;
View Lorry Controller Status