Error in mapping-generated SQL query

Support Bug
1

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#
PREFIX prov: http://www.w3.org/ns/prov#
PREFIX ex_aiw: http://api.stardog.com/demo_aiw_instance_data/
PREFIX 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

2

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") 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") 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.