Wrong SQL query generated?

90 views
Skip to first unread message

Johan Van Noten

unread,
Sep 1, 2022, 6:11:28 PM9/1/22
to ontop4obda
Environment:
  • Ontop 4.2.1
  • Mappings towards Dremio 20.1.1
  • Dremio accesses CSV, Excel and PostgreSQL data
  • There are only 5 mappings with very simple SQL statements. Every SQL statement individually works fine.

Issue:

The first of the queries below works, the second fails.
It looks as if a wrong SQL query gets generated by Ontop.
Can you analyse this? Do you need more information to do so? I could provide you access to a working environment if that would be required to further analyse the issue.
Or am I overlooking something myself?

Thanks for looking into it!
Johan

  • Working SPARQL query:

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
select distinct ?type where {
  <http://my/ontology/jml#i/PlasmaTreatmentPS/GA-AAAACS-AABBWCIH> a ?type.
}

Results in: (which is ok)
http://my/ontology/dt#ManufacturingElement
http://my/ontology/dt#ProcessStep
http://my/ontology/jml#JoiningPS
http://my/ontology/jml#SubstratePreperationPS
http://my/ontology/jml#PlasmaTreatmentPS

  • Failing SPARQL query:

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX owl: <http://www.w3.org/2002/07/owl#>
select distinct ?type ?derivedType where {
  <http://my/ontology/jml#i/PlasmaTreatmentPS/GA-AAAACS-AABBWCIH> a ?type.
  ?derivedType rdfs:subClassOf ?type .
}

The latter query causes the following issue:

it.unibz.inf.ontop.exception.OntopQueryEvaluationException: java.sql.SQLException: DATA_READ ERROR: Source 'JMLPostgres' returned error 'ERROR: syntax error at or near "AS"
  Position: 2365'

plugin JMLPostgres
sql (SELECT *
FROM ((SELECT *
FROM ((SELECT *
FROM ((SELECT "t0"."v18m48" COLLATE "C", 'http://my/ontology/dt#ManufacturingElement' COLLATE "C" AS "v3"
FROM ((SELECT 1 AS "uselessVariable"
FROM (SELECT "jml_plasma_pretreatment"."execution_ref" COLLATE "C"
FROM "public"."jml_plasma_pretreatment") AS "jml_plasma_pretreatment"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "execution_ref")
UNION ALL
(SELECT 1 AS "uselessVariable"
FROM "public"."jml_plasma_pretreatment_treated_substrates"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "jml_plasma_pretreatment_treated_substrates"."execution_ref" COLLATE "C" AND "jml_plasma_pretreatment_treated_substrates"."substrate" COLLATE "C" IS NOT NULL)) AS "t"
INNER JOIN (VALUES  ('http://my/ontology/dt#ManufacturingElement' COLLATE "C"),
 ('http://my/ontology/dt#ProcessStep' COLLATE "C"),
 ('http://my/ontology/dt#Asset' COLLATE "C"),
 ('http://my/ontology/jml#JoiningPS' COLLATE "C"),
 ('http://my/ontology/dt#Process' COLLATE "C"),
 ('http://my/ontology/dt#Human' COLLATE "C"),
 ('http://my/ontology/jml#JoiningProcessAsset' COLLATE "C"),
 ('http://my/ontology/jml#Sensor' COLLATE "C"),
 ('http://my/ontology/dt#Product' COLLATE "C"),
 ('http://my/ontology/dt#Material' COLLATE "C"),
 ('http://my/ontology/jml#SubstratePreperationPS' COLLATE "C"),
 ('http://my/ontology/jml#TestingPS' COLLATE "C"),
 ('http://my/ontology/jml#ProductAssemblyPS' COLLATE "C"),
 ('http://my/ontology/jml#ProcessJML1' COLLATE "C"),
 ('http://my/ontology/jml#Sample' COLLATE "C"),
 ('http://my/ontology/jml#Substrate' COLLATE "C"),
 ('http://my/ontology/jml#SubstrateMaterial' COLLATE "C"),
 ('http://my/ontology/jml#Solvent' COLLATE "C"),
 ('http://my/ontology/jml#JMLSensorTRH' COLLATE "C"),
 ('http://my/ontology/jml#PlasmaTreatmentPS' COLLATE "C"),
 ('http://my/ontology/jml#SubstrateCleaningPS' COLLATE "C"),
 ('http://my/ontology/jml#AdhesiveApplicationPS' COLLATE "C"),
 ('http://my/ontology/jml#CuringPS' COLLATE "C")) AS "t" ("v18m48") AS "t0" ON TRUE
GROUP BY "t0"."v18m48")
UNION ALL
(SELECT "t6"."v18m48" COLLATE "C", 'http://my/ontology/dt#ProcessStep' COLLATE "C" AS "v3"
FROM ((SELECT 1 AS "uselessVariable"
FROM (SELECT "jml_plasma_pretreatment"."execution_ref" COLLATE "C"
FROM "public"."jml_plasma_pretreatment") AS "jml_plasma_pretreatment0"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "execution_ref")
UNION ALL
(SELECT 1 AS "uselessVariable"
FROM "public"."jml_plasma_pretreatment_treated_substrates"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "jml_plasma_pretreatment_treated_substrates"."execution_ref" COLLATE "C" AND "jml_plasma_pretreatment_treated_substrates"."substrate" COLLATE "C" IS NOT NULL)) AS "t5"
INNER JOIN (VALUES  ('http://my/ontology/dt#ProcessStep' COLLATE "C"),
 ('http://my/ontology/jml#JoiningPS' COLLATE "C"),
 ('http://my/ontology/dt#Process' COLLATE "C"),
 ('http://my/ontology/jml#SubstratePreperationPS' COLLATE "C"),
 ('http://my/ontology/jml#TestingPS' COLLATE "C"),
 ('http://my/ontology/jml#ProductAssemblyPS' COLLATE "C"),
 ('http://my/ontology/jml#ProcessJML1' COLLATE "C"),
 ('http://my/ontology/jml#PlasmaTreatmentPS' COLLATE "C"),
 ('http://my/ontology/jml#SubstrateCleaningPS' COLLATE "C"),
 ('http://my/ontology/jml#AdhesiveApplicationPS' COLLATE "C"),
 ('http://my/ontology/jml#CuringPS' COLLATE "C")) AS "t" ("v18m48") AS "t6" ON TRUE
GROUP BY "t6"."v18m48")) AS "t11")
UNION ALL
(SELECT "t13"."v18m48" COLLATE "C", 'http://my/ontology/jml#JoiningPS' COLLATE "C" AS "v3"
FROM ((SELECT 1 AS "uselessVariable"
FROM (SELECT "jml_plasma_pretreatment"."execution_ref" COLLATE "C"
FROM "public"."jml_plasma_pretreatment") AS "jml_plasma_pretreatment1"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "execution_ref")
UNION ALL
(SELECT 1 AS "uselessVariable"
FROM "public"."jml_plasma_pretreatment_treated_substrates"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "jml_plasma_pretreatment_treated_substrates"."execution_ref" COLLATE "C" AND "jml_plasma_pretreatment_treated_substrates"."substrate" COLLATE "C" IS NOT NULL)) AS "t12"
INNER JOIN (VALUES  ('http://my/ontology/jml#JoiningPS' COLLATE "C"),
 ('http://my/ontology/jml#SubstratePreperationPS' COLLATE "C"),
 ('http://my/ontology/jml#TestingPS' COLLATE "C"),
 ('http://my/ontology/jml#ProductAssemblyPS' COLLATE "C"),
 ('http://my/ontology/jml#PlasmaTreatmentPS' COLLATE "C"),
 ('http://my/ontology/jml#SubstrateCleaningPS' COLLATE "C"),
 ('http://my/ontology/jml#AdhesiveApplicationPS' COLLATE "C"),
 ('http://my/ontology/jml#CuringPS' COLLATE "C")) AS "t" ("v18m48") AS "t13" ON TRUE
GROUP BY "t13"."v18m48")) AS "t18")
UNION ALL
(SELECT "t20"."v18m48" COLLATE "C", 'http://my/ontology/jml#SubstratePreperationPS' COLLATE "C" AS "v3"
FROM ((SELECT 1 AS "uselessVariable"
FROM (SELECT "jml_plasma_pretreatment"."execution_ref" COLLATE "C"
FROM "public"."jml_plasma_pretreatment") AS "jml_plasma_pretreatment2"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "execution_ref")
UNION ALL
(SELECT 1 AS "uselessVariable"
FROM "public"."jml_plasma_pretreatment_treated_substrates"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "jml_plasma_pretreatment_treated_substrates"."execution_ref" COLLATE "C" AND "jml_plasma_pretreatment_treated_substrates"."substrate" COLLATE "C" IS NOT NULL)) AS "t19"
INNER JOIN (VALUES  ('http://my/ontology/jml#SubstratePreperationPS' COLLATE "C"),
 ('http://my/ontology/jml#PlasmaTreatmentPS' COLLATE "C"),
 ('http://my/ontology/jml#SubstrateCleaningPS' COLLATE "C")) AS "t" ("v18m48") AS "t20" ON TRUE
GROUP BY "t20"."v18m48")) AS "t25")
UNION ALL
(SELECT 'http://my/ontology/jml#PlasmaTreatmentPS' COLLATE "C" AS "v18m48", 'http://my/ontology/jml#PlasmaTreatmentPS' COLLATE "C" AS "v3"
FROM ((SELECT 1 AS "uselessVariable"
FROM (SELECT "jml_plasma_pretreatment"."execution_ref" COLLATE "C"
FROM "public"."jml_plasma_pretreatment") AS "jml_plasma_pretreatment3"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "execution_ref")
UNION ALL
(SELECT 1 AS "uselessVariable"
FROM "public"."jml_plasma_pretreatment_treated_substrates"
WHERE 'GA-AAAACS-AABBWCIH' COLLATE "C" = "jml_plasma_pretreatment_treated_substrates"."execution_ref" COLLATE "C" AND "jml_plasma_pretreatment_treated_substrates"."substrate" COLLATE "C" IS NOT NULL)) AS "t26"
HAVING COUNT(*) > 0)
SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:3
SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:3
Fragment 0:0

[Error Id: 32ee6ced-aa05-4951-aa6f-008d00ac5e09 on 17645e681856:0]

  (org.postgresql.util.PSQLException) ERROR: syntax error at or near "AS"
  Position: 2365
    org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2675
    org.postgresql.core.v3.QueryExecutorImpl.processResults():2365
    org.postgresql.core.v3.QueryExecutorImpl.execute():355
    org.postgresql.jdbc.PgStatement.executeInternal():490
    org.postgresql.jdbc.PgStatement.execute():408
    org.postgresql.jdbc.PgStatement.executeWithFlags():329
    org.postgresql.jdbc.PgStatement.executeCachedSql():315
    org.postgresql.jdbc.PgStatement.executeWithFlags():291
    org.postgresql.jdbc.PgStatement.executeQuery():243
    org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
    org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
    com.dremio.exec.store.jdbc.JdbcRecordReader.setup():225
    com.dremio.exec.store.CoercionReader.setup():118
    com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():311
    com.dremio.sabot.op.scan.ScanOperator.setupReader():302
    com.dremio.sabot.op.scan.ScanOperator.setup():266
    com.dremio.sabot.driver.SmartOp$SmartProducer.setup():569
    com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79
    com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63
    com.dremio.sabot.driver.SmartOp$SmartProducer.accept():539
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.Pipeline.setup():69
    com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():478
    com.dremio.sabot.exec.fragment.FragmentExecutor.run():327
    com.dremio.sabot.exec.fragment.FragmentExecutor.access$1600():97
    com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():820
    com.dremio.sabot.task.AsyncTaskWrapper.run():120
    com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():247
    com.dremio.sabot.task.slicing.SlicingThread.run():171

Benjamin Cogrel

unread,
Sep 8, 2022, 12:29:30 PM9/8/22
to Johan Van Noten, ontop4obda
Hi Johan,

Sorry for the late reply, your email went to my spam folder.

Perhaps the issue is due to the VALUES construct, which we introduced in 4.2.0 (in replacement of some unions) . 
Could please set the option ontop.enableValuesNode to false and let us know if it solves your issue?

Best,
Benjamin


--
Please follow our guidelines on how to report a bug https://ontop-vkg.org/community/contributing/bug-report
---
You received this message because you are subscribed to the Google Groups "ontop4obda" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ontop4obda+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ontop4obda/36e31d3d-5588-44c5-93ba-95d9c73822bfn%40googlegroups.com.

Johan Van Noten

unread,
Sep 9, 2022, 8:09:51 AM9/9/22
to ontop4obda
Benjamin,

Thanks for your reply.
In the meantime, we worked around the issue and our ontology and underlying database has evolved quite a bit.
Retrying the problematic query doesn't fail anymore in this new environment.
It is therefore probably not related to the setting you pointed at.

it is feasible (but quite cumbersome) to bring everything back to the situation of September 2nd in order to come back to a failing state.

If it is ok with you, I 'll follow two paths:
* Check whether the situation reproduces itself on the current infrastructure
* Look for an opportunity to bring up a copy of the September 2nd infrastructure in order to see whether your VALUES suggestion would have made a difference.

Best regards,
Johan
Op donderdag 8 september 2022 om 18:29:30 UTC+2 schreef benjami...@ontopic.ai:

Benjamin Cogrel

unread,
Sep 9, 2022, 2:59:35 PM9/9/22
to Johan Van Noten, ontop4obda
Hi Johan,

Good to hear it wasn't a blocking point.
Thank you for proposing to reproduce the issue, I leave it up to you to decide if it is worthwhile to spend time on it.

Best,
Benjamin

To view this discussion on the web visit https://groups.google.com/d/msgid/ontop4obda/c619e73d-aea3-4231-9d15-40304f95d6c7n%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages