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:

• The personid you can see here is not one that satisfies the spatial part of the query. It isn't in the correct result above.
• I get repeats of the personid, which is the primary key of the person table.
• Each personid is repeated 4001 times. 4001 is the number of postcodes in the PostcodeLocation table that satisfy the spatial query, however only 8 of those postcodes are in the Person table - see the original result set.
• All the records in the result set do satisfy the non-spatial part of the WHERE clause but NOT the join condition.
• If I change the join type to LEFT OUTER JOIN, then I do get the correct result (the two records in the first result set where the town is Dundee)

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 16.0.0.2213

---

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.

http://sqlanywhere-forum.sap.com/upfiles/WithoutExtraCondition(CorrectResultSet).saplan.jpg
http://sqlanywhere-forum.sap.com/upfiles/WithExtraCondition(WrongResultSet).saplan.jpg

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

select * from 
(select p.personid, town
   from person 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) DT
where DT.town like 'D%';

select * from 
(select p.personid, town
   from person 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) DT;

select p.personid, town
   from person p inner join postcodelocation l
      on p.town like 'D%' and p.postcode = l.postcode 
  where l.IQXPos.ST_Within(
     (select Geometry from "Boundary-line-historic-counties_region" where "name"='Angus')) = 1);

select personid, town from 
(select p.personid, town, postcode
   from person p where p.town like 'D%') DT inner join postcodelocation l
      on DT.postcode = l.postcode
  where l.IQXPos.ST_Within(
     (select Geometry from "Boundary-line-historic-counties_region" where "name"='Angus')) = 1;

...from person p with (index <Your choice here>) inner join postcodelocation l...

select p.personid, p.town from person p with (index (person_postcode)) 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
   and p.town like 'D%';