I'm going to be a little cautious about calling this a bug after my previous spatial embarrassment, but I think something rather odd is happening here. It's a bit complicated but I'll try to explain in stages.
I am trying to run queries that identify which of various person records fall inside certain UK administrative areas. The link is based on the persons' postcodes. UK postcodes identify groups of usually 20-50 properties and the Ordnance Survey [OS] provide a OSGB grid reference for the centroid of each postcode area. The OS also provide polygons with the outlines of the administrative areas - in this case counties.
I have the person records in a table called Person, there are some 23k records in the table. The table includes various information including postcode; about half the records have a postcode. I have another table, PostcodeLocation, with the centroid of each postcode as an ST_Point. The table contains records for all 1.4m postcode in the UK. Most of the postcodes in the Person table have a matching value in PostcodeLocation but some will be out-of-date or incorrect.
A third table, Boundary-line-historic-counties_region, contains the (multi)polygons that represent the county boundaries.
When I run a query like:
select p.personid, town from person p join postcodelocation l on p.postcode = l.postcode where l.IQXPos.ST_Within((select Geometry from "Boundary-line-historic-counties_region" where "name"='Angus')) =1
I get the expected result set:
personid,town 'TI04201631032011000A','Arbroath' 'TI0SJJAN231020060005', 'TI161223240520110F97','Arbroath' 'TI215211160720026362','DUNDEE' 'TI322616240820110001','Arbroath' 'TI531716310320110001','Arbroath' 'TI32536','DUNDEE' 'TI35398','BY FORFAR'
ie 8 Person records have postcodes that are in Angus. However, as soon as I add any additional restraints to the WHERE clause eg:
select p.personid, town from person p join postcodelocation l on p.postcode = l.postcode where l.IQXPos.ST_Within((select Geometry from "Boundary-line-historic-counties_region" where "name"='Angus')) =1 and p.town like 'D%';
rather than getting a sub-set of my previous results, I get a completely different result:
personid,town 'TI1238192502201117E7','Deeside' 'TI1238192502201117E7','Deeside' 'TI1238192502201117E7','Deeside' 'TI1238192502201117E7','Deeside' 'TI1238192502201117E7','Deeside' 'TI1238192502201117E7','Deeside' 'TI1238192502201117E7','Deeside' .......
This continues for hundreds of thousands of rows. Things to note:
I've tried reproducing the problem with a much smaller Person table but I haven't yet been able to recreate the issue, so it seems that something about the Person table is involved in creating the problem. This is hard to isolate as it has many foreign keys and is referenced by many other tables.
Am I missing something very obvious here?
A full validation on the database showed no issues. An unload & reload did not make any difference. Version 188.8.131.523
Update: I thought I would take a look at the execution plans. Interestingly the working query plan was returned in a few seconds but it took over 20 minutes for the problem query to return a plan to the plan viewer. The main difference is that the problem one uses Spatial Index Probes which the other doesn't. I'd upload the plans if I could work out how to :) - I've seen them attached to other questions!
Further Update: Graphical Plans for both good and bad queries attached using the Volker-Dmitri bodge.
To retrieve plans, right click on the hyper links above and choose "Save link as.." or equivalent depending on browser, then remove jpg extension from resulting downloaded file.
And another update: I've been able to work out what determines whether I get the right result set or the wrong one. It depends on whether or not the engine uses the index on person.postcode (if it does it gets the right answer).
I created a new table:
CREATE TABLE "pears"."Person3" ( "personid" CHAR(20) NOT NULL, "town" CHAR(30) NULL, "postcode" CHAR(30) NULL, PRIMARY KEY ( "personid" ASC ) ) IN "system";and populated it with a select statement from the original Person table. When I ran the query
select p.personid from person3 p inner join postcodelocation l on p.postcode = l.postcode where l.IQXPos.ST_Within((select Geometry from "Boundary-line-historic-counties_region" where "name"='Angus')) =1;I got the wrong result set. Adding an index on person.postcode and running the query again gave the correct results. To check, I dropped the index, re-ran the query and we went back to the wrong results.
Reported to SAP as incident ID:002075129400004746052016
This is a WAG...
That whole business of using a "correlated parenthesized ( subquery ) as a function parameter in a WHERE predicate" may be OK because it appears in a few examples in the Help, but...
...those examples use the "comma join" operator in the outer SELECT, not an INNER JOIN with ON clause.
In other words, it's a kind of undocumented LATERAL join using the WHERE clause.
In practice, parenthesized ( subqueries ) are very dangerous things.
In theory they are allowed as function parameters but not as procedure CALL parameters (go figure!).
Personally, I limit parenthesized ( subquery ) usage to a few well-proven uses; e.g., SELECT lists (rare) and IF EXISTS statements (frequent).
Perhaps you can move the ST_Within predicate down into a derived table based on postcodelocation... or into a completely separate query to load a temporary table.