Ticket #80 (new defect)

Opened 2 years ago

Spatial filter combined with UNION may throw exception

Reported by: ggarbis Owned by: pyravlos-team
Priority: minor Milestone:
Component: postgis Version:
Keywords: Cc:

Description

If a triple that retrieves a spatial filter is used in a UNION clause and in a spatial filter, then an SQL exception is thrown. Consider the following example.

Insert data:

PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX ex: <http://example.org/>

INSERT DATA {
 ex:a1 rdf:type ex:A.
 ex:a1 geo:asWKT "POINT(0 0)"^^geo:wktLiteral.

 ex:b1 rdf:type ex:B1.
 ex:b1 geo:asWKT "POINT(0 0)"^^geo:wktLiteral.
 ex:b2 rdf:type ex:B2.
 ex:b2 geo:asWKT "POINT(0 0)"^^geo:wktLiteral.
}

NOT Working SPARQL query, geo:asWKT is inside UNION clauses:

PREFIX geo: <http://www.opengis.net/ont/geosparql#>
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX ex: <http://example.org/>

SELECT ?a 
WHERE {
 ?a rdf:type ex:A.
 ?a geo:asWKT ?sWKT.

 {?b rdf:type ex:B1.
  ?b geo:asWKT ?bWKT.}
 UNION
 {?b rdf:type ex:B2.
  ?b geo:asWKT ?bWKT.}
 FILTER(geof:sfIntersects(?aWKT, ?bWKT))
}

Generated (NOT working) SQL query:

SELECT a0.subj,
 u_s.value
FROM aswkt_32 a0
 INNER JOIN type_7 t1 ON (t1.obj =  '101' 
 AND t1.subj = a0.subj)
 INNER JOIN geo_values l_sWKT ON (l_sWKT.id = a0.obj)
 INNER JOIN geo_values l_tWKT ON (( NULL ))
 CROSS JOIN ( SELECT t3.subj AS t3subj,
  '7'  AS t3pred,
 t3.obj AS t3obj,
  '32'  AS a4pred,
 a4.obj AS a4obj,
  NULL  AS t5pred,
  NULL  AS t5obj,
  NULL  AS a6pred
FROM type_7 t3
 INNER JOIN aswkt_32 a4 ON (a4.obj = l_tWKT.id
 AND a4.subj = t3.subj)
WHERE t3.obj =  '102' 
UNION ALL SELECT t5.subj AS t3subj,
  NULL  AS t3pred,
  NULL  AS t3obj,
  NULL  AS a4pred,
 a6.obj AS a4obj,
  '7'  AS t5pred,
 t5.obj AS t5obj,
  '32'  AS a6pred
FROM type_7 t5
 INNER JOIN aswkt_32 a6 ON (a6.subj = t5.subj)
WHERE t5.obj =  '103'  ) u7
 LEFT JOIN uri_values u_s ON (u_s.id = a0.subj)

Generated Exception:

2015-06-12 15:52:04,747 [http-bio-8989-exec-1] ERROR eu.earthobservatory.org.StrabonEndpoint.QueryBean  - [StrabonEndpoint.QueryBean] Error during querying.
org.openrdf.query.QueryEvaluationException: org.openrdf.sail.rdbms.exceptions.RdbmsQueryEvaluationException: org.postgresql.util.PSQLException: ERROR: invalid reference to FROM-clause entry for table "l_twkt"
  Hint: There is an entry for table "l_twkt", but it cannot be referenced from this part of the query.

Working SPARQL query:, geo:asWKT is now outside UNION clauses:

PREFIX geo: <http://www.opengis.net/ont/geosparql#>                             
PREFIX geof: <http://www.opengis.net/def/function/geosparql/>                   
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>                       
PREFIX ex: <http://example.org/>                                                
                                                                                
SELECT ?a
WHERE {                                                                         
 ?a rdf:type ex:A.                                                              
 ?a geo:asWKT ?aWKT.                                                            
                                                                                
 {?b rdf:type ex:B1.}                                                           
 UNION                                                                          
 {?b rdf:type ex:B2.}                                                           
                                                                                
 ?b geo:asWKT ?bWKT.   
 FILTER(geof:sfIntersects(?aWKT, ?bWKT))                                                         
}                                                                               

# Produced (working) SQL query:

SELECT a0.subj,
 u_b.value
FROM aswkt_32 a0
 INNER JOIN ( SELECT t1.subj AS t1subj,
  '7'  AS t1pred,
 t1.obj AS t1obj,
  NULL  AS t2pred,
  NULL  AS t2obj
FROM type_7 t1
WHERE t1.obj =  '102' 
UNION ALL SELECT t2.subj AS t1subj,
  NULL  AS t1pred,
  NULL  AS t1obj,
  '7'  AS t2pred,
 t2.obj AS t2obj
FROM type_7 t2
WHERE t2.obj =  '103'  ) u3 ON ((u3.t1subj IS NULL  OR u3.t1subj = a0.subj))
 INNER JOIN geo_values l_bWKT ON (l_bWKT.id = a0.obj)
 INNER JOIN geo_values l_aWKT ON ((ST_Intersects(l_aWKT.strdfgeo,l_bWKT.strdfgeo)))
 INNER JOIN aswkt_32 a5 ON (a5.obj = l_aWKT.id)
 INNER JOIN type_7 t6 ON (t6.obj =  '101' 
 AND t6.subj = a5.subj)
 LEFT JOIN uri_values u_b ON (u_b.id = a0.subj)
 LEFT JOIN uri_values u_a ON (u_a.id = a5.subj)
Note: See TracTickets for help on using tickets.