# Error in mapping-generated SQL query

**URL:** <https://community.stardog.com/t/error-in-mapping-generated-sql-query/3103>\
**Category:** Bug\
**Created:** [June 16, 2021, 1:29pm UTC](https://community.stardog.com/t/error-in-mapping-generated-sql-query/3103 "2021-06-16T13:29:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\
**Post date:** [June 16, 2021, 1:29pm UTC](https://community.stardog.com/t/error-in-mapping-generated-sql-query/3103/1 "2021-06-16T13:29:45Z")

</div>

Hello,

I'm encountering an error due to a SQL query that is generated when I attempt certain SPARQL queries. It seems that the error occurs when referencing two different tables with the same column names. A simplified example occurs with the following query:

> PREFIX time: [http://www.w3.org/2006/time#](http://www.w3.org/2006/time#)  
> PREFIX prov: [http://www.w3.org/ns/prov#](http://www.w3.org/ns/prov#)  
> PREFIX ex\_aiw: [http://api.stardog.com/demo\_aiw\_instance\_data/](http://api.stardog.com/demo_aiw_instance_data/)  
> PREFIX famo: [http://ontology.eil.utoronto.ca/FAMO/famo/](http://ontology.eil.utoronto.ca/FAMO/famo/)
> 
> SELECT \*  
> FROM virtual://tw\_mock\_wms  
> FROM virtual://tw\_mock\_aiw
> 
> WHERE {  
> ?desc famo:indicatesHasUID ?asset\_id.
> 
> ```
> ?rec2 famo:specifies ?desc2;
> prov:invalidatedAtTime ?dt.
> ?desc2 famo:indicatesHasUID ?asset_id.  
> 
> FILTER(?dt<= NOW()) 
> 
> ```
> 
> }

Where there are instances of triples that satisfy the pattern: ?desc famo:indicatesHasUID ?asset\_id. in multiple mappings. When I attempt the query above, I get the following error from Stardog:

> com.complexible.stardog.plan.eval.operator.OperatorException: Unable to execute virtual graph query. SQL string: SELECT \*
> 
> FROM (SELECT \*
> 
> FROM "public"."aiw\_ghost\_wms\_asset\_record\_tbl"
> 
> INNER JOIN "public"."aiw\_capital\_phase\_tbl" ON "aiw\_ghost\_wms\_asset\_record\_tbl"."phase\_uuid" = "aiw\_capital\_phase\_tbl"."phase\_uuid") AS "t"
> 
> INNER JOIN (SELECT \*
> 
> FROM "public"."aiw\_ghost\_wms\_asset\_record\_tbl" AS "aiw\_ghost\_wms\_asset\_record\_tbl0"
> 
> INNER JOIN "public"."aiw\_capital\_phase\_tbl" AS "aiw\_capital\_phase\_tbl0" ON "aiw\_ghost\_wms\_asset\_record\_tbl0"."phase\_uuid" = "aiw\_capital\_phase\_tbl0"."phase\_uuid") AS "t0" ON "t"."wms\_asset\_record\_id" = "t0"."wms\_asset\_record\_id" AND "t"."phase\_uuid" = "t0"."phase\_uuid" AND "t0"."start\_date" IS NOT NULL
> 
> INNER JOIN (SELECT \*
> 
> FROM "public"."aiw\_ghost\_wms\_asset\_record\_tbl" AS "aiw\_ghost\_wms\_asset\_record\_tbl1"
> 
> INNER JOIN "public"."aiw\_capital\_phase\_tbl" AS "aiw\_capital\_phase\_tbl1" ON "aiw\_ghost\_wms\_asset\_record\_tbl1"."phase\_uuid" = "aiw\_capital\_phase\_tbl1"."phase\_uuid") AS "t1" ON "t"."wms\_asset\_record\_id" = "t1"."wms\_asset\_record\_id" AND "t"."phase\_uuid" = "t1"."phase\_uuid" AND "t0"."wms\_asset\_record\_id" = "t1"."wms\_asset\_record\_id" AND "t0"."phase\_uuid" = "t1"."phase\_uuid"
> 
> WHERE "t"."wms\_asset\_record\_id" = 'WMS-Asset-1' OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-2' OR "t"."wms\_asset\_record\_id" = 'WMS-Asset-3') OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-4' OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-5' OR "t"."wms\_asset\_record\_id" = 'WMS-Asset-6')) OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-7' OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-8' OR "t"."wms\_asset\_record\_id" = 'WMS-Asset-9') OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-10' OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-11' OR "t"."wms\_asset\_record\_id" = 'WMS-Asset-12')))

And when I attempt the problematic SQL query defined above directly against the postgres database, I get the following error:

> AIW\_Data-TW=\> SELECT \*
> 
> AIW\_Data-TW-\> FROM (SELECT \*
> 
> AIW\_Data-TW(\> FROM "public"."aiw\_ghost\_wms\_asset\_record\_tbl"
> 
> AIW\_Data-TW(\> INNER JOIN "public"."aiw\_capital\_phase\_tbl" ON "aiw\_ghost\_wms\_asset\_record\_tbl"."phase\_uuid" = "aiw\_capital\_phase\_tbl"."phase\_uuid") AS "t"
> 
> AIW\_Data-TW-\> INNER JOIN (SELECT \*
> 
> AIW\_Data-TW(\> FROM "public"."aiw\_ghost\_wms\_asset\_record\_tbl" AS "aiw\_ghost\_wms\_asset\_record\_tbl0"
> 
> AIW\_Data-TW(\> INNER JOIN "public"."aiw\_capital\_phase\_tbl" AS "aiw\_capital\_phase\_tbl0" ON "aiw\_ghost\_wms\_asset\_record\_tbl0"."phase\_uuid" = "aiw\_capital\_phase\_tbl0"."phase\_uuid") AS "t0" ON "t"."wms\_asset\_record\_id" = "t0"."wms\_asset\_record\_id" AND "t"."phase\_uuid" = "t0"."phase\_uuid" AND "t0"."start\_date" IS NOT NULL
> 
> AIW\_Data-TW-\> INNER JOIN (SELECT \*
> 
> AIW\_Data-TW(\> FROM "public"."aiw\_ghost\_wms\_asset\_record\_tbl" AS "aiw\_ghost\_wms\_asset\_record\_tbl1"
> 
> AIW\_Data-TW(\> INNER JOIN "public"."aiw\_capital\_phase\_tbl" AS "aiw\_capital\_phase\_tbl1" ON "aiw\_ghost\_wms\_asset\_record\_tbl1"."phase\_uuid" = "aiw\_capital\_phase\_tbl1"."phase\_uuid") AS "t1" ON "t"."wms\_asset\_record\_id" = "t1"."wms\_asset\_record\_id" AND "t"."phase\_uuid" = "t1"."phase\_uuid" AND "t0"."wms\_asset\_record\_id" = "t1"."wms\_asset\_record\_id" AND "t0"."phase\_uuid" = "t1"."phase\_uuid"
> 
> AIW\_Data-TW-\> WHERE "t"."wms\_asset\_record\_id" = 'WMS-Asset-1' OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-2' OR "t"."wms\_asset\_record\_id" = 'WMS-Asset-3') OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-4' OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-5' OR "t"."wms\_asset\_record\_id" = 'WMS-Asset-6')) OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-7' OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-8' OR "t"."wms\_asset\_record\_id" = 'WMS-Asset-9') OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-10' OR ("t"."wms\_asset\_record\_id" = 'WMS-Asset-11' OR "t"."wms\_asset\_record\_id" = 'WMS-Asset-12')));
> 
> ERROR: column reference "phase\_uuid" is ambiguous
> 
> LINE 7: ...asset\_record\_id" = "t0"."wms\_asset\_record\_id" AND "t"."phase...

It seems as though the mapping engine is generating a query with an ambiguous column reference that is problematic for the database, is that right? Any solutions for this beyond renaming the columns? Is it possible/necessary to disambiguate between column names in the template specification (i.e. for {phase\_uuid}, identifying which table column it is referring to)?

I am running Stardog 7.6.4 and the virtual graphs are defined with mappings to a postgresql database. Please let me know if you require any additional information.

Thanks!  
Megan

---

<div class="post-metadata">

**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\
**Post date:** [June 16, 2021, 1:41pm UTC](https://community.stardog.com/t/error-in-mapping-generated-sql-query/3103/2 "2021-06-16T13:41:34Z")

</div>

Update: to confirm my suspicion I've re-tried the query with an updated mapping that defines two new columns to enable disambiguation in the template specification. In other words, what was originally:

> MAPPING  
> FROM SQL {  
> SELECT \*  
> FROM "aiw\_ghost\_wms\_asset\_record\_tbl", "aiw\_capital\_phase\_tbl"  
> WHERE "aiw\_ghost\_wms\_asset\_record\_tbl"."phase\_uuid"="aiw\_capital\_phase\_tbl"."phase\_uuid"  
> }  
> TO {  
> ?rec a famo:WMSDataRecord;  
> a famo:AssetDataRecord;  
> famo:specifies ?desc;  
> prov:wasInvalidatedBy ?phase;  
> prov:invalidatedAtTime ?dt.  
> ?desc famo:indicatesHasUID ?wms\_id.  
> }  
> WHERE {  
> BIND(template("_:{wms\_asset\_record\_id}_{phase\_uuid}_wmsassetrec") AS ?rec)  
> BIND(template("_:{wms\_asset\_record\_id}\_{phase\_uuid}\_wmsassetdesc") AS ?desc)  
> BIND(template("[http://api.stardog.com/demo\_aiw\_instance\_data/{phase\_uuid}\_phase](http://api.stardog.com/demo_aiw_instance_data/%7Bphase_uuid%7D_phase)") AS ?phase)  
> BIND(xsd:datetime(?start\_date) AS ?dt)  
> BIND(xsd:string(?wms\_asset\_record\_id) AS ?wms\_id)  
> }

I modified to:

> MAPPING  
> FROM SQL {  
> SELECT \*, "aiw\_ghost\_wms\_asset\_record\_tbl"."phase\_uuid" AS WMS\_PHASE\_UUID, "aiw\_capital\_phase\_tbl"."phase\_uuid" AS AIW\_PHASE\_UUID  
> FROM "aiw\_ghost\_wms\_asset\_record\_tbl", "aiw\_capital\_phase\_tbl"  
> WHERE "aiw\_ghost\_wms\_asset\_record\_tbl"."phase\_uuid"="aiw\_capital\_phase\_tbl"."phase\_uuid"  
> }  
> TO {  
> ?rec a famo:WMSDataRecord;  
> a famo:AssetDataRecord;  
> famo:specifies ?desc;  
> prov:wasInvalidatedBy ?phase;  
> prov:invalidatedAtTime ?dt.  
> ?desc famo:indicatesHasUID ?wms\_id.  
> }  
> WHERE {  
> BIND(template("_:{wms\_asset\_record\_id}_{WMS\_PHASE\_UUID}_wmsassetrec") AS ?rec)  
> BIND(template("_:{wms\_asset\_record\_id}\_{WMS\_PHASE\_UUID}\_wmsassetdesc") AS ?desc)  
> BIND(template("[http://api.stardog.com/demo\_aiw\_instance\_data/{WMS\_PHASE\_UUID}\_phase](http://api.stardog.com/demo_aiw_instance_data/%7BWMS_PHASE_UUID%7D_phase)") AS ?phase)  
> BIND(xsd:datetime(?start\_date) AS ?dt)  
> BIND(xsd:string(?wms\_asset\_record\_id) AS ?wms\_id)  
> }

And the error (with the same query) no longer arises.