# SPARQL Query Bug **URL:** \ **Category:** Bug\ **Created:** [June 7, 2021, 6:48pm UTC](https://community.stardog.com/t/sparql-query-bug/3083 "2021-06-07T18:48:55Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\ **Post date:** [June 7, 2021, 6:48pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/1 "2021-06-07T18:48:55Z")
Hello, I'm encountering some strange behaviour while testing SPARQL queries (on Stardog 7.6.4). The same query will sometimes run successfully, and other times will return an error. For example, the following query runs successfully: > SELECT \* > FROM virtual://tw\_mockdb2 > FROM virtual://aiw > > WHERE{ > ?d1 famo:indicatesType famo:Valve; > famo:indicatesHasIdentifier ?wms\_id; > famo:indicatesInstallDate ?i\_date; > famo:indicatesIsResponsibleForRole [ > famo:hasEntityNumber ?r\_enum]. > ?d2 famo:indicatesHasEntityNumber ?r\_enum; > famo:indicatesLocatedInSpace ?s. > > ?a famo:hasDocumentation ?rec. > ?rec famo:specifies ?desc. > } However, with the addition of one other statement: > ?desc famo:indicatesHasIdentifier ?a\_id. An error is returned: > com.complexible.stardog.plan.eval.operator.OperatorException: Unable to execute virtual graph query. SQL string: SELECT "aiw\_real\_asset\_record\_tbl"."asset\_id", "aiw\_real\_asset\_record\_tbl"."project\_phase\_uuid", "aiw\_real\_asset\_record\_tbl"."new\_asset", "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id", "aiw\_real\_asset\_record\_tbl"."aiw\_asset\_id", "aiw\_real\_asset\_record\_tbl"."asset\_manufacturer\_verified\_n\_new", "aiw\_real\_asset\_record\_tbl"."asset\_model\_verified\_n\_new", "aiw\_real\_asset\_record\_tbl"."mfr\_serial\_on\_wms\_record", "aiw\_real\_asset\_record\_tbl"."mfr\_serial\_verified\_n\_new", "aiw\_real\_asset\_record\_tbl"."asset\_classification\_on\_wms\_record", "aiw\_real\_asset\_record\_tbl"."asset\_classification\_verified\_n\_new", "aiw\_real\_asset\_record\_tbl"."existing\_asset\_moved", "aiw\_real\_asset\_record\_tbl"."existing\_asset\_decommissioned", "aiw\_real\_asset\_record\_tbl"."existing\_asset\_removed", "aiw\_real\_asset\_record\_tbl"."occupied\_role\_id\_on\_wms\_record", "aiw\_real\_asset\_record\_tbl"."occupied\_role\_id\_verified", "aiw\_real\_asset\_record\_tbl"."occupied\_role\_id\_final", "aiw\_capital\_phase\_tbl"."phase\_uuid", "aiw\_capital\_phase\_tbl"."aiw\_project\_uuid", "aiw\_capital\_phase\_tbl"."project\_phase\_number", "aiw\_capital\_phase\_tbl"."scope\_description", "aiw\_capital\_phase\_tbl"."consultant\_company\_name", "aiw\_capital\_phase\_tbl"."contractor\_company\_name", "aiw\_capital\_phase\_tbl"."start\_date", "aiw\_capital\_phase\_tbl"."commissioning\_date", "aiw\_capital\_phase\_tbl"."status", CASE WHEN "aiw\_real\_asset\_record\_tbl"."existing\_asset\_moved" THEN 'MoveAsset' WHEN "aiw\_real\_asset\_record\_tbl"."existing\_asset\_decommissioned" THEN 'DecommissionAsset' WHEN "aiw\_real\_asset\_record\_tbl"."existing\_asset\_removed" THEN 'RemoveAsset' ELSE NULL END AS "activity", CASE WHEN "aiw\_real\_asset\_record\_tbl"."existing\_asset\_decommissioned" AND "aiw\_real\_asset\_record\_tbl"."existing\_asset\_removed" OR "aiw\_real\_asset\_record\_tbl"."existing\_asset\_decommissioned" AND NOT "aiw\_real\_asset\_record\_tbl"."existing\_asset\_removed" THEN 'decommissioned\_and\_discarded' WHEN "aiw\_real\_asset\_record\_tbl"."existing\_asset\_moved" THEN 'operational' ELSE NULL END AS "asset\_status" > FROM "public"."aiw\_real\_asset\_record\_tbl" > INNER JOIN "public"."aiw\_capital\_phase\_tbl" ON "aiw\_real\_asset\_record\_tbl"."project\_phase\_uuid" = "aiw\_capital\_phase\_tbl"."phase\_uuid" AND "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" IS NOT NULL AND "aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" IS NOT NULL AND NOT "aiw\_real\_asset\_record\_tbl"."new\_asset" > WHERE "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '32' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-2') OR "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '154' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-9') OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '35' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-10') OR "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '12' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-12')) OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '987' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-13') OR "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '94' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-15') OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '1034' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-17') OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '2' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-2') OR "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '8' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-9')))) OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '9' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-10') OR "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '5' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-12') OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '11' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-13') OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '12' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-14') OR "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = '13' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 'R-15'))) OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = 'WMS-Asset-6' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 5) OR "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = 'WMS-Asset-5' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 4) OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = 'WMS-Asset-4' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 3) OR ("aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = 'WMS-Asset-2' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 2) OR "aiw\_real\_asset\_record\_tbl"."wms\_asset\_record\_id" = 'WMS-Asset-1' AND ("aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" = 'Project-UUID-1' AND "aiw\_real\_asset\_record\_tbl"."asset\_id" = 1))))) If I attempt to re-run the original query (with the additional statement removed), it no longer runs successfully, but instead returns this error: > com.complexible.stardog.plan.eval.operator.OperatorException: Unable to execute virtual graph query. SQL string: SELECT "aiw\_real\_role\_record\_tbl"."aiw\_role\_id", "aiw\_real\_role\_record\_tbl"."etms\_role\_record\_id", "aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" > FROM "public"."aiw\_real\_role\_record\_tbl" > INNER JOIN "public"."aiw\_capital\_phase\_tbl" ON "aiw\_real\_role\_record\_tbl"."project\_phase\_uuid" = "aiw\_capital\_phase\_tbl"."phase\_uuid" AND "aiw\_real\_role\_record\_tbl"."etms\_role\_record\_id" IS NOT NULL AND "aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" IS NOT NULL AND NOT "aiw\_real\_role\_record\_tbl"."new\_role" It seems like there might be an issue due to the mapping specification for the virtual graph but it's not clear to me what the problem might be. In either case, it doesn't make sense that the same query works and then returns an error once some other query has been run. Thanks and best regards, Megan ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 7, 2021, 7:32pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/2 "2021-06-07T19:32:38Z")
> [@mkatsumi](#): > > SELECT "aiw\_real\_role\_record\_tbl"."aiw\_role\_id", "aiw\_real\_role\_record\_tbl"."etms\_role\_record\_id", "aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" > FROM "public"."aiw\_real\_role\_record\_tbl" > INNER JOIN "public"."aiw\_capital\_phase\_tbl" ON "aiw\_real\_role\_record\_tbl"."project\_phase\_uuid" = "aiw\_capital\_phase\_tbl"."phase\_uuid" AND "aiw\_real\_role\_record\_tbl"."etms\_role\_record\_id" IS NOT NULL AND "aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" IS NOT NULL AND NOT "aiw\_real\_role\_record\_tbl"."new\_role" Hi Meagan, I've never seem the issue you describe where a query runs then doesn't, though maybe it is possible if cardinality estimates are updated and the query plan changes. Regardless, we shouldn't be getting your errors ever. There may be more details in the stardog.log file about the type of error. If that's available it would help. I see `AND NOT "aiw_real_role_record_tbl"."new_role"` which is treating `"new_role"` as though it were a boolean type. Not all DB engines support booleans, so maybe that is the problem. What is the backing database in this case? Thanks, -Paul ---
**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\ **Post date:** [June 7, 2021, 8:57pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/3 "2021-06-07T20:57:29Z")
Hi Paul, Thanks for the quick reply. It's a postgresql database, and I've tried the query with the boolean condition directly and it seemed to work OK. Can you tell me - what's the best way to get the log file for just this most recent issue? I'll get that to you as soon as possible. ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 7, 2021, 9:26pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/4 "2021-06-07T21:26:33Z")
The log file is named `stardog.log`, it's on the server in the home directory pointed to by the $STARDOG\_HOME environment variable, or if that is not set, the current directory where `stardog-admin server start` was run. ---
**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\ **Post date:** [June 7, 2021, 11:53pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/5 "2021-06-07T23:53:56Z")
Thanks Paul! What I meant was if there was a way to extract the relevant part of the log file (since it gets so large, I end up deleting it and then reproducing the error to capture only the relevant part). In any case, please find the log file attached. Does that help any? [stardog.log](https://community.stardog.com/uploads/short-url/2owgApSDQ8anD5KKrEcHMXG2tMe.log) (101.8 KB) ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 8, 2021, 2:50pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/6 "2021-06-08T14:50:24Z")
Ah, I see. I usually open the whole log and copy/paste everything from the timeframe of the error. The pertinent error is coming from PG: org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "R-2" ...while running the query: ```auto SELECT "aiw_real_role_record_tbl"."aiw_role_id", "aiw_real_role_record_tbl"."etms_role_record_id", "aiw_capital_phase_tbl"."aiw_project_uuid" FROM "public"."aiw_real_role_record_tbl" INNER JOIN "public"."aiw_capital_phase_tbl" ON "aiw_real_role_record_tbl"."project_phase_uuid" = "aiw_capital_phase_tbl"."phase_uuid" AND "aiw_real_role_record_tbl"."etms_role_record_id" IS NOT NULL AND "aiw_capital_phase_tbl"."aiw_project_uuid" IS NOT NULL AND NOT "aiw_real_role_record_tbl"."new_role" ``` Is it possible that between `"aiw_real_role_record_tbl"."project_phase_uuid"` and `"aiw_capital_phase_tbl"."phase_uuid"` that one of those is a text type and the other an integer? Could you share your DDL and mappings? Thanks, -Paul ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 8, 2021, 3:29pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/7 "2021-06-08T15:29:33Z")
Follow-up question: Is this just a normal postgres table, or is it possibly using foreign data wrappers to access a csv file or similar? ---
**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\ **Post date:** [June 8, 2021, 3:55pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/8 "2021-06-08T15:55:20Z")
Hi Paul, I don't have complete access to the database implementation, but here are the definitions of the two tables in question: > ``` > Table "public.aiw_real_role_record_tbl" > Column | Type | Collation | Nullable | Default > > ``` > > --------------------------------+---------+-----------+----------+--------- > aiw\_role\_id | text | | not null | > project\_phase\_uuid | text | | | > new\_role | boolean | | | > wms\_role\_record\_id | integer | | | > entity\_number\_on\_wms\_record | text | | | > etms\_role\_record\_id | integer | | | > entity\_number\_on\_etms\_record | text | | | > entity\_number\_verified | text | | | > entity\_number\_final | text | | | > existing\_role\_to\_be\_eliminated | boolean | | | > parent\_role\_id\_on\_wms\_record | text | | | > parent\_role\_id\_verified | text | | | > Indexes: > "aiw\_real\_role\_record\_tbl\_pk" PRIMARY KEY, btree (aiw\_role\_id) > "fki\_aiw\_full\_role\_record\_tbl\_aiw\_capital\_phase\_tbl" btree (project\_phase\_uuid) > Foreign-key constraints: > "aiw\_full\_role\_record\_tbl\_aiw\_capital\_phase\_tbl" FOREIGN KEY (project\_phase\_uuid) REFERENCES aiw\_capital\_phase\_tbl(phase\_uuid) > Referenced by: > TABLE "aiw\_real\_asset\_record\_tbl" CONSTRAINT "aiw\_full\_asset\_record\_tbl\_aiw\_full\_role\_record\_tbl1" FOREIGN KEY (occupied\_role\_id\_on\_wms\_record) REFERENCES aiw\_real\_role\_record\_tbl(aiw\_role\_id) > TABLE "aiw\_real\_asset\_record\_tbl" CONSTRAINT "aiw\_full\_asset\_record\_tbl\_aiw\_full\_role\_record\_tbl2" FOREIGN KEY (occupied\_role\_id\_final) REFERENCES aiw\_real\_role\_record\_tbl(aiw\_role\_id) > TABLE "aiw\_real\_asset\_record\_tbl" CONSTRAINT "aiw\_full\_asset\_record\_tbl\_aiw\_full\_role\_record\_tbl3" FOREIGN KEY (occupied\_role\_id\_verified) REFERENCES aiw\_real\_role\_record\_tbl(aiw\_role\_id) and ``` Table "public.aiw_capital_phase_tbl" Column | Type | Collation | Nullable | Defaul ``` ## t -------------------------+-----------------------+-----------+----------+------- > phase\_uuid | text | | not null | > aiw\_project\_uuid | text | | | > project\_phase\_number | integer | | | > scope\_description | character varying(50) | | | > consultant\_company\_name | text | | | > contractor\_company\_name | text | | | > start\_date | date | | | > commissioning\_date | date | | | > status | integer | | | > Indexes: > "aiw\_capital\_phase\_tbl\_pk" PRIMARY KEY, btree (phase\_uuid) > Foreign-key constraints: > "capital\_phase\_tbl\_aiw" FOREIGN KEY (aiw\_project\_uuid) REFERENCES aiw\_instan > ce\_tbl(aiw\_project\_uuid) > Referenced by: > TABLE "aiw\_real\_asset\_record\_tbl" CONSTRAINT "aiw\_full\_asset\_record\_tbl\_aiw\_ > capital\_phase\_tbl" FOREIGN KEY (project\_phase\_uuid) REFERENCES aiw\_capital\_phase > _tbl(phase\_uuid) > TABLE "aiw\_real\_role\_record\_tbl" CONSTRAINT "aiw\_full\_role\_record\_tbl\_aiw\_ca > pital\_phase\_tbl" FOREIGN KEY (project\_phase\_uuid) REFERENCES aiw\_capital\_phase\_t > bl(phase\_uuid) > TABLE "aiw\_ghost\_wms\_asset\_record\_tbl" CONSTRAINT "aiw\_ghost\_wms\_asset\_recor > d\_tbl\_aiw\_instance\_tbl" FOREIGN KEY (phase\_uuid) REFERENCES aiw\_capital\_phase\_tb > l(phase\_uuid) > TABLE "aiw\_ghost\_wms\_role\_record\_tbl" CONSTRAINT "aiw\_ghost\_wms\_role\_record_ > tbl\_aiw\_instance\_tbl" FOREIGN KEY (phase\_uuid) REFERENCES aiw\_capital\_phase\_tbl( > phase\_uuid) It looks like both the phase\_uuid and project\_phase\_uuid are text types. I've attached the current mapping files for your reference. Note that "mapping1" is the mapping that is relevant for the tables you referenced in the previous reply, but "mapping2" is also used for part of the SPARQL query. [mapping1.ttl](https://community.stardog.com/uploads/short-url/qOzGEscUcEL0vKUiDYz5JqPdmzl.ttl) (19.2 KB) [mapping2.ttl](https://community.stardog.com/uploads/short-url/4kxWos7MJJNnCycjJyXv2voc7zV.ttl) (4.5 KB) To your follow-up question: all of the databases are just normal postgres tables, not wrappers. ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 8, 2021, 8:05pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/9 "2021-06-08T20:05:31Z")
Hi Megan, This is kind of weird. Stardog is submitting a fairly straightforward query to Postgres: ```auto SELECT "aiw_real_role_record_tbl"."aiw_role_id", "aiw_real_role_record_tbl"."etms_role_record_id", "aiw_capital_phase_tbl"."aiw_project_uuid" FROM "public"."aiw_real_role_record_tbl" INNER JOIN "public"."aiw_capital_phase_tbl" ON "aiw_real_role_record_tbl"."project_phase_uuid" = "aiw_capital_phase_tbl"."phase_uuid" AND "aiw_real_role_record_tbl"."etms_role_record_id" IS NOT NULL AND "aiw_capital_phase_tbl"."aiw_project_uuid" IS NOT NULL AND NOT "aiw_real_role_record_tbl"."new_role" ``` The error we're getting seems to be indicating that Postgres is finding text data in a column where there should integer. The only field that is integer in this context is `etms_role_record_id`. Could you run that query through a SQL client and confirm that you get the same error? (It looks like when we run it the error occurs on the 2531th row, if I'm interpreting the error correctly.) If you get the same error, you could try not retrieving that column: ```auto SELECT "aiw_real_role_record_tbl"."aiw_role_id", "aiw_capital_phase_tbl"."aiw_project_uuid" FROM "public"."aiw_real_role_record_tbl" INNER JOIN "public"."aiw_capital_phase_tbl" ON "aiw_real_role_record_tbl"."project_phase_uuid" = "aiw_capital_phase_tbl"."phase_uuid" AND "aiw_capital_phase_tbl"."aiw_project_uuid" IS NOT NULL AND NOT "aiw_real_role_record_tbl"."new_role" ``` If the error goes away, it seems like you have text in an integer column. Maybe there was a CSV import done with validation disabled? (I didn't know Postgres would allow that, but I'm not a regular user.) If you don't get the error in either case, I'm still thinking it's text-in-integer, but only leading to an error when the data is retrieved from the JDBC driver. -Paul ---
**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\ **Post date:** [June 8, 2021, 10:51pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/10 "2021-06-08T22:51:13Z")
Hi Paul, I just tried the first query directly against the Postgres database and it ran successfully. I also tried the following, as the wms\_role\_record\_id field is also defined as an integer value: > SELECT > "aiw\_real\_role\_record\_tbl"."aiw\_role\_id", > "aiw\_real\_role\_record\_tbl"."wms\_role\_record\_id", > "aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" > FROM "public"."aiw\_real\_role\_record\_tbl" > INNER JOIN "public"."aiw\_capital\_phase\_tbl" > ON "aiw\_real\_role\_record\_tbl"."project\_phase\_uuid" = "aiw\_capital\_phase\_tbl"."phase\_uuid" > AND "aiw\_real\_role\_record\_tbl"."wms\_role\_record\_id" IS NOT NULL > AND "aiw\_capital\_phase\_tbl"."aiw\_project\_uuid" IS NOT NULL > AND NOT "aiw\_real\_role\_record\_tbl"."new\_role" > ...but that ran successfully as well. Also, regarding the location of the error: the database contains a small mock dataset (most tables with under 20 rows), and the query returns a result with 7 rows. ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 9, 2021, 12:38pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/11 "2021-06-09T12:38:09Z")
If the whole dataset is small I can try it here if you want to back it up. > ---
**Author:** ![rjohnson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/rjohnson/32/1006_2.png) [@rjohnson](https://community.stardog.com/u/rjohnson)\ **Post date:** [June 10, 2021, 1:33pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/12 "2021-06-10T13:33:05Z")
[Database backup.zip](https://community.stardog.com/uploads/short-url/sAIGirJSciRquZ6mplHJqqkflMO.zip) (2.8 KB) Hi Paul, I have attached a backup of our postgres database. I am sending this on Megan's behalf. ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 10, 2021, 11:20pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/13 "2021-06-10T23:20:15Z")
Hi Meagan, I was able to set up the database using the backup that Rachel provided. I can see that this query isn't going to return any results unless I incorporate `mapping2.ttl` because it references `famo:indicatesIsResponsibleForRole`, which is only in the 2nd mapping. That mapping requires a `WMS_Entity_Base` table. If you could export that table for me as well I'll give it another shot. I guess it doesn't matter which is which, but between `mapping1.ttl` and `mapping2.ttl`, one is `aiw` and the other is `tw_mockdb2`, correct? Thanks, -Paul ---
**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\ **Post date:** [June 11, 2021, 12:23pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/14 "2021-06-11T12:23:27Z")
Hi Paul, Sorry about that! You're right, the query uses both mappings (and requires a second, also small database), and I suspect this combination may have something to do with the error. We'll get the second backup to you asap! Megan ---
**Author:** ![rjohnson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/rjohnson/32/1006_2.png) [@rjohnson](https://community.stardog.com/u/rjohnson)\ **Post date:** [June 11, 2021, 12:41pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/15 "2021-06-11T12:41:41Z")
Hello, Here is the second mockdb2 database backup. [mockdb2 database backup.zip](https://community.stardog.com/uploads/short-url/uv3AyMadlVmx6C6CaJJUOjgZ1UW.zip) (2.9 KB) ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 11, 2021, 1:50pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/16 "2021-06-11T13:50:05Z")
That did the trick - I was able to reproduce your exact error. I will dig into finding the root cause today. Thanks, -Paul ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 14, 2021, 5:51pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/17 "2021-06-14T17:51:16Z")
Hi Megan, I've isolated a couple problems with Stardog that occurred with this query. I'm going to explain the summary and then the details and two potential workarounds. There were two issues. The first was that Stardog generated an invalid SQL query that included an expression that compared an integer to a text value (the snippet from the large `WHERE` clause is: `...AND "aiw_real_asset_record_tbl"."asset_id" = 'R-2') OR...`). Postgres threw an `ERROR: invalid input syntax for type integer: "R-2"` exception (at position 2531 of the SQL query). The second issue is that once this error occurred, the JDBC connection (that we pool for reuse over multiple queries) went into an unresponsive state so that reverting to simpler (and valid) SPARQL queries would now fail with the original error message despite the new queries not having an `R-2` literal in them at all. Once this occurred, the only way to reset that connection is to restart Stardog or re-add the virtual graph (or the data source, if you created them separately). FWIW, I was only able to reproduce this type of connection corruption with Postgres - for all our other supported backends the connection is reusable after a SQL error. I've created a new Jira ticket for this: PLAT-2531 The initial problem arose because we have two templates in the mapping that have the same "shape". (`template("http://api.stardog.com/demo_aiw_instance_data/{aiw_project_uuid}_{asset_id}"` and `template("http://api.stardog.com/demo_aiw_instance_data/{aiw_project_uuid}_{aiw_role_id}"`) where the referenced fields have different types (`asset_id` and `aiw_role_id`) and your query allowed for the possibility that the same IRI could be found in the two mappings (new asset mapping and new role). This is not your error. We should have casted the integer `asset_id` from an integer to a text field before using it in the expression. There are two ways you can work around this while we're working on putting the fix into the product. The fixes are semantically different so it depends on what you really want. The first fix assumes that the mappings are written the way you really want - that you want to allow for the possibility that the same IRI can be both a `famo:PhysicalAsset` and a `famo:Role`. With your sample data they can never match because the textual `aiw_role_id`s are always prefixed with an `R-` while the `asset_id`s are integer, but the data could change and Stardog has no way to know there's not a `aiw_role_id` containing, say, an '8', which would create an IRI that could also be an Asset. If this is desired (IRIs that are boths Roles and Assets) you can achieve this by adding an expression to the SELECT clause (`SELECT *, CAST("asset_id" AS VARCHAR(256)) AS "asset_id_txt"...`) and using the `asset_id_txt` field in place of the `asset_id` field in the `TEMPLATE` functions. I've attached updated mappings. The second fix assumes that you never want an IRI to be both an Asset and a Role. I think this is what you really intend. I see in your mappings where you ensured against such things by creating templates that have a type suffix: `template("_:{aiw_project_uuid}_{asset_id_txt}_mfg")` and `template("_:{aiw_project_uuid}_{asset_id_txt}_model")`. Stardog can see that those two templates can never match (technically, with carefully designed data they could, but Stardog assumes no). You could add such suffixes to the Asset and Role templates and that would prevent Stardog from trying to join them and it therefore wouldn't attempt to equate the incompatible types. It has the added benefit that Stardog can rule out more matches at query planning time, leading to simpler and more efficient SQL queries. We need a blog post on this, it's a best practice. Thanks for your patience with helping me reproduce your error. -Paul [mapping1\_cast.ttl](https://community.stardog.com/uploads/short-url/9IheGrmM5tOAfap8bdbFlq9jytg.ttl) (19.4 KB) ---
**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\ **Post date:** [June 15, 2021, 11:44am UTC](https://community.stardog.com/t/sparql-query-bug/3083/18 "2021-06-15T11:44:01Z")
Hi Paul, Thank you for looking into this! Regarding the solutions, you're right that the second approach is likely better suited to my case. I made the changes that you suggested to the asset and role identifier templates, however I think I am still running into the same sort of issue with another query (I'm assuming this would be due to another possible match with the templates). Are you able to confirm whether this is the case? The query is as follows, with the same databases and mappings: ``` PREFIX famo: PREFIX ex_aiw: SELECT ?a_wmsuid ?i_date ?aid_2 ?aid_3 ?y FROM FROM WHERE { ?a famo:isResponsibleForRole ?x2; famo:hasDocumentation ?rec_a. ?rec_a famo:specifies ?desc_a. ?desc_a famo:indicatesHasUID ?a_wmsuid. } ``` Unfortunately, I don't understand enough of how the mapping process works in Stardog to understand / recognize other cases where this scenario might come up again. Any advice/pointers on this? In the immediate term we may simply try changing all of our datatypes to text - of course this won't be a feasible solution in the long run. Thanks again! Megan ---
**Author:** ![PaulJackson](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/pauljackson/32/243_2.png) [@PaulJackson](https://community.stardog.com/u/PaulJackson)\ **Post date:** [June 15, 2021, 4:53pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/19 "2021-06-15T16:53:06Z")
> [@mkatsumi](#): > > ```auto > PREFIX famo: > PREFIX ex_aiw: > > SELECT ?a_wmsuid ?i_date ?aid_2 ?aid_3 ?y > FROM > FROM > > WHERE > { > ?a famo:isResponsibleForRole ?x2; > famo:hasDocumentation ?rec_a. > ?rec_a famo:specifies ?desc_a. > ?desc_a famo:indicatesHasUID ?a_wmsuid. } > > ``` I am unable to reproduce the error but it may have to do with the changes to the mappings you are making. Are you re-adding the VG after every error? In the query you provided it is attempting to find all `?a` that have both a `famo:isResponsibleForRole` and a `famo:hasDocumentation` property. When I scan through the mappings I see a few subject templates that can match those: ```auto famo:isResponsibleForRole "http://api.stardog.com/demo_aiw_instance_data/{aiw_project_uuid}_{asset_id}" "http://api.stardog.com/demo_aiw_instance_data/{aiw_project_uuid}_{asset_id}_asset2" "http://api.stardog.com/demo_aiw_instance_data/{aiw_project_uuid}_{asset_id}_asset3" famo:hasDocumentation "http://api.stardog.com/demo_aiw_instance_data/{aiw_project_uuid}_{asset_id}_asset1" "http://api.stardog.com/demo_aiw_instance_data/{aiw_project_uuid}_{aiw_role_id}_role1 ``` None of these templates are able to match one another so I am getting no results from this query. That is, the plan I get for: ```auto SELECT * FROM FROM WHERE { ?a famo:isResponsibleForRole ?x2; famo:hasDocumentation ?rec_a. } ``` is: ```auto From From Empty [#1] ``` ...indicating that Stardog was able to rule out all possible joins by looking at the templates. If you run `?a famo:isResponsibleForRole ?x2` and `famo:hasDocumentation ?rec_a` separately you'll see there is no common `?a` between the two queries. If your mappings are different, this error happens when two compatible templates (templates that are identical except for the names of the variables in the templates), say: ```auto "http://api.stardog.com/demo_aiw_instance_data/{aiw_project_uuid}_{asset_id}" ``` and ```auto "http://api.stardog.com/demo_aiw_instance_data/{aiw_project_uuid}_{aiw_role_id}" ``` are attempted to join (serve as the subject for two different patterns, `?a famo:isResponsibleForRole ?x2` and `?a famo:hasDocumentation ?rec_a`). The solution is to ensure the two fields have the same type using the cast of the first solution, or modify one or both of the templates so that they are not compatible. -Paul ---
**Author:** ![mkatsumi](https://avatars.discourse-cdn.com/v4/letter/m/e480ec/32.png) [@mkatsumi](https://community.stardog.com/u/mkatsumi)\ **Post date:** [June 15, 2021, 5:31pm UTC](https://community.stardog.com/t/sparql-query-bug/3083/20 "2021-06-15T17:31:07Z")
Hi Paul, Apologies, I'd forgotten that I had made some changes to the mapping file after sharing it with you. The updated mapping files are attached. [mapping1.ttl](https://community.stardog.com/uploads/short-url/rWjFjeTntqsWpcNOOayzi0bK9Am.ttl) (19.3 KB) [mapping2.ttl](https://community.stardog.com/uploads/short-url/AvKwQJD8Ng2DxwSp706mC2BMwee.ttl) (4.5 KB) Are you able to reproduce the error with these? We have just revised the databases so that all of the columns are now typed as text, and I've restarted the server, so I expect that the issue of types should be resolved, but I will try re-adding the VGs to be sure... [Next page](https://community.stardog.com/t/sparql-query-bug/3083.md?page=2)