Oddity with Spatial Query

[VolkerBarth]

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

[VolkerBarth]

Does the query work correctly when you use the original query as a derived query and then apply the filter on town lateron, i.e. something like:

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%';

[justin_willey]

Interestingly that produces the same wrong answer. I wonder if it is simply being re-written by the optimiser?

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;

works fine.

[VolkerBarth]

Hm, what about putting the town filter in the Join condition, such as

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);

or by using a derived table to filter person before you do the join with postcodelocation?

Yep, all wild guessing, but somehow I remember to have had (non-spatial) queries with similar problems...

[justin_willey]

That's the same again. I also tried:

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;

but no better.

It's strange that making the original query an OUTER join works. As does making the join between DT and PostcodeLocation in the query above a left outer join.

[VolkerBarth]

It's strange that making the original query an OUTER join works. As does making the join between DT and PostcodeLocation in the query above a left outer join.

So that's one of the few cases where the classical "your WHERE clause turns the LEFT JOIN into an INNER JOIN" error does not hold:)

[justin_willey]

Indeed - rather the opposite!

[VolkerBarth]

Adding an index on person.postcode

Hm, does your original schema with table person have a FOREIGN KEY to postcodelocation - and therefore an automatic index? (I'm asking as the table person3 apparently doesn't.) (Sorry, I have not yet checked the attached plans, I had to get to know the meaning of "bodge" first:)...)

So would an index hint on the join condition (aka the FK index) do the trick, too? - I.e. something like

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

If so, that would probably be still a bodge but better than a wrong result set.

[justin_willey]

There is no foreign key (the postcodes in Person may be blank or filled and may or may not exist in PostcodeLocation).

I tried:

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%';

but the same (wrong) behaviour happens.

[VolkerBarth]

Then I guess it would even be similarily wrong if you added a separate NULLable column like "known_postcode" with those codes existing in the other table and declared a FK on that, right?

but the same (wrong) behaviour happens.

So the plan did not use that index?

(Otherwise, your observation "It depends on whether or not the engine uses the index on person.postcode (if it does it gets the right answer)." would not hold...)

---

Besides that, I hope the SQL Anywhere engineers will take over...

[justin_willey · Accepted solution]

Fixed by Engineering Case 805460

SAP SQL Anywhere Version 16.0.0 build 2428

SAP SQL Anywhere Version 17.0 build 2816

The problem only happens when a multi-column index that includes a geometry column is used in the plan.