Dear Hugh, Thank you for your message. 1. We'll investigate/test your work around solution you provided for our first issue. BTW, have you used our endpoint to collect data? We experienced sudden unusual traffic. Could you please confirm if someone in Dev at Openlink is using the endpoint? 2. I guess we need to live with until there is update from the dev team.
Thank you. Best, Ghislain ________________________________ Ghislain ATEMEZING Operational Data Unit Project Officer Tel: +33 327096 500 Mobile: +33 0609242967 European Union Agency for Railways From: Hugh Williams <hwilli...@openlinksw.com> Sent: Tuesday, November 28, 2023 1:55 PM To: ATEMEZING Ghislain <ghislain.atemez...@era.europa.eu> Cc: AGUADO Marina <marina.agu...@era.europa.eu>; LUCAS RECHE David (DIGIT-EXT) <david.lucas-re...@ext.ec.europa.eu>; virtuoso-users@lists.sourceforge.net; Kingsley Idehen <kide...@openlinksw.com>; Fainti, Anestis <afai...@deloitte.gr> Subject: Re: Virtuoso support for ERA - Virtuoso backend Hi Ghislain, Yes, I do remember from the publication office and Mondeca days, I am fine thanks and trust you are too ... The issues you report were logged as a support case#693<https://shop.openlinksw.com/support_system/customers/display_case.vsp?caseNumber=693> by Anestis from Deloitte (cc'ed in on this mail) who support the Virtuoso services hosted for EC Digit a few weeks ago. You can be given access to by Anestis the case owner, if you are registered on our support system, where such issues should always be logged so they can be managed and tracked centrally, rather than email threads. >From the support >case#693<https://shop.openlinksw.com/support_system/customers/display_case.vsp?caseNumber=693> > the following updates were provided last week: For Issue #1 - Wrong results for BIND depending on OPTIONAL/BIND order: I created Virtuoso SPARQL to SQL<https://vos.openlinksw.com/owiki/wiki/VOS/VirtTipsAndTricksAnalyzingSPARQLQuery> plans for the two queries to see how they differ when translated to SQL for execution for analysis by development who report, it is very hard to say what exactly happens to SPARQL to SQL translation especially when nesting subqueries in coalsece/case so on. And would suggest to modifying the queries to the following which returns the correct results when the optionals are specified with the respective bind calls: PREFIX eud: <http://data.europa.eu/949/> SELECT DISTINCT ?AA ?BB ?CC ?DD FROM <http://data.europa.eu/949/graph/rinf> WHERE { ?AA a eud:OperationalPoint. ?AA eud:uopid ?EE. FILTER(lcase(str(?EE)) = lcase("DEYBBMO")) ?AA eud:track ?BB. OPTIONAL { ?BB eud:demonstrationINF ?CC_direct } OPTIONAL { ?BB ?CC_tmp eud:demonstrationINF } BIND(COALESCE(?CC_direct,?CC_tmp,eud:notData) AS ?CC ) OPTIONAL { ?BB eud:verificationINF ?DD_direct. } OPTIONAL { ?BB ?DD_tmp eud:verificationINF } BIND(COALESCE(?DD_direct,?DD_tmp,eud:notData) AS ?DD) } LIMIT 10 TEST server Live Link<https://test.virtuoso.ecdp.tech.ec.europa.eu/sparql?default-graph-uri=&qtxt=PREFIX+eud%3A+%3Chttp%3A%2F%2Fdata.europa.eu%2F949%2F%3E%0D%0A%0D%0ASELECT+DISTINCT+%3FAA+%3FBB+%3FCC+%3FDD+%0D%0AFROM+%3Chttp%3A%2F%2Fdata.europa.eu%2F949%2Fgraph%2Frinf%3E%0D%0AWHERE+%7B%0D%0A++%3FAA+a+eud%3AOperationalPoint.%0D%0A++%3FAA+eud%3Auopid+%3FEE.%0D%0A++FILTER%28lcase%28str%28%3FEE%29%29+%3D+lcase%28%22DEYBBMO%22%29%29%0D%0A%0D%0A++%3FAA+eud%3Atrack+%3FBB.%0D%0A%0D%0A++OPTIONAL+%7B+%3FBB+eud%3AdemonstrationINF+%3FCC_direct++%7D%0D%0A++OPTIONAL+%7B+%3FBB+%3FCC_tmp+eud%3AdemonstrationINF+%7D%0D%0A++BIND%28COALESCE%28%3FCC_direct%2C%3FCC_tmp%2Ceud%3AnotData%29+AS+%3FCC+%29%0D%0A%0D%0A++OPTIONAL+%7B+%3FBB+eud%3AverificationINF+%3FDD_direct.+%7D%0D%0A++OPTIONAL+%7B+%3FBB+%3FDD_tmp+eud%3AverificationINF+%7D%0D%0A++BIND%28COALESCE%28%3FDD_direct%2C%3FDD_tmp%2Ceud%3AnotData%29+AS+%3FDD%29%0D%0A%7D%0D%0A%0D%0ALIMIT+10+&should-sponge=&format=text%2Fhtml&timeout=30000> where we assume ?BB ?CC_tmp eud:demonstrationINF and ?BB ?DD_tmp eud:verificationINF could be either notApplicable/notYetAvailable or not exists , the BIND can be moved around and would works. Note the 'OPTIONAL { ?BB ?DD/CC_tmp eud:verificationINF }' simplification of 'COALESCE(IF EXISTS...))' in the revised query. Let us know if this works as expected ... Development are going to look into this issue with the optionals with both original queries should return the same results, and so we will be dumping some of the <http://data.europa.eu/949/graph/rinf> graph data to recreate locally. For issue #2 - Error in COALESCE division by zero: I checked with development and they indicate the Virtuoso COALESCE "Divide by Zero" issue is a known problem from sometime ago and cannot readily be fixed in the Virtuoso SQL engine where the function is implemented. Generally the trapping and supressing of errors in such SQL functions cannot readily be passed back to the SPARQL layer without significantly affecting performance. Best Regards Hugh Williams Professional Services OpenLink Software Home Page: http://www.openlinksw.com<http://www.openlinksw.com/> Community Support: https://community.openlinksw.com<https://community.openlinksw.com/> Company Blog: https://medium.com/openlink-software-blog Virtuoso Blog: https://medium.com/virtuoso-blog Data Access Drivers Blog: https://medium.com/openlink-odbc-jdbc-ado-net-data-access-drivers LinkedIn -- http://www.linkedin.com/company/openlink-software/ Twitter -- http://twitter.com/OpenLink Facebook -- http://www.facebook.com/OpenLinkSoftware Universal Data Access, Integration, and Management Technology Providers On 28 Nov 2023, at 07:55, ATEMEZING Ghislain <ghislain.atemez...@era.europa.eu<mailto:ghislain.atemez...@era.europa.eu>> wrote: Dear Hugh, I hope you are doing great. We had the chance to exchange last year during the benchmark of Virtuoso for Publications Office. I joined ERA in September, and we are using Virtuoso as backend for a railway infrastructure in Europe [1]. We have some problematic issues/queries for the application consuming RDF data stored in Virtuoo 1- Wrong results for BIND depending on OPTIONAL/BIND order We have the following query, where 2 different parameters are requested and we want to also check if they are NYA or NA, for both. # SPARQL query generated by ERA Search App SELECT DISTINCT ?e2e8d0ff44169432a9b6a49d415768b6aeb230e6e308eacd14c58203edec7c1e ?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 ?7c9eedbe297dee33d5692bb017576d5f90128c5a57245968861342c5d938bc15 ?068c016b70a3e345e79f2c917aa68e82e0a103ed4a2d3a377fc7e6d65298e659 FROM http://data.europa.eu/949/graph/rinf WHERE { ?e2e8d0ff44169432a9b6a49d415768b6aeb230e6e308eacd14c58203edec7c1e a http://data.europa.eu/949/OperationalPoint. ?e2e8d0ff44169432a9b6a49d415768b6aeb230e6e308eacd14c58203edec7c1e http://data.europa.eu/949/uopid ?bed82d131276294cde9b33e06fc5540daf5ce0a0ca03c6428b296bff9ff8e95b. FILTER(lcase(str(?bed82d131276294cde9b33e06fc5540daf5ce0a0ca03c6428b296bff9ff8e95b)) = lcase("DEYBBMO")) ?e2e8d0ff44169432a9b6a49d415768b6aeb230e6e308eacd14c58203edec7c1e http://data.europa.eu/949/track ?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2. # Query part to get results for demonstrationINF OPTIONAL { ?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 http://data.europa.eu/949/demonstrationINF ?7c9eedbe297dee33d5692bb017576d5f90128c5a57245968861342c5d938bc15_direct. } BIND( COALESCE(?7c9eedbe297dee33d5692bb017576d5f90128c5a57245968861342c5d938bc15_direct, IF(EXISTS{?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 http://data.europa.eu/949/notYetAvailablehttp://data.europa.eu/949/demonstrationINF},http://data.europa.eu/949/notYetAvailable, ?unbound), IF(EXISTS{?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 http://data.europa.eu/949/notApplicablehttp://data.europa.eu/949/demonstrationINF},http://data.europa.eu/949/notApplicable, ?unbound), http://data.europa.eu/949/notData ) AS ?7c9eedbe297dee33d5692bb017576d5f90128c5a57245968861342c5d938bc15). # Query part to get results for verificationINF OPTIONAL { ?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 http://data.europa.eu/949/verificationINF ?068c016b70a3e345e79f2c917aa68e82e0a103ed4a2d3a377fc7e6d65298e659_direct. } BIND( COALESCE(?068c016b70a3e345e79f2c917aa68e82e0a103ed4a2d3a377fc7e6d65298e659_direct, IF(EXISTS{?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 http://data.europa.eu/949/notYetAvailablehttp://data.europa.eu/949/verificationINF},http://data.europa.eu/949/notYetAvailable, ?unbound), IF(EXISTS{?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 http://data.europa.eu/949/notApplicablehttp://data.europa.eu/949/verificationINF},http://data.europa.eu/949/notApplicable, ?unbound), http://data.europa.eu/949/notData ) AS ?068c016b70a3e345e79f2c917aa68e82e0a103ed4a2d3a377fc7e6d65298e659). } LIMIT 10 For one of the parameters we get the expected results, for the other we are not getting anything (notData is just the last option of the COALESCE). If we execute the query with only one of the parameters, the results are the expected, but not with both at the same time. We can only get the expected results reordering the statements in the previous query, like this: # SPARQL query generated by ERA Search App SELECT DISTINCT ?e2e8d0ff44169432a9b6a49d415768b6aeb230e6e308eacd14c58203edec7c1e ?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 ?7c9eedbe297dee33d5692bb017576d5f90128c5a57245968861342c5d938bc15 ?068c016b70a3e345e79f2c917aa68e82e0a103ed4a2d3a377fc7e6d65298e659 FROM http://data.europa.eu/949/graph/rinf WHERE { ?e2e8d0ff44169432a9b6a49d415768b6aeb230e6e308eacd14c58203edec7c1e a http://data.europa.eu/949/OperationalPoint. ?e2e8d0ff44169432a9b6a49d415768b6aeb230e6e308eacd14c58203edec7c1e http://data.europa.eu/949/uopid ?bed82d131276294cde9b33e06fc5540daf5ce0a0ca03c6428b296bff9ff8e95b. FILTER(lcase(str(?bed82d131276294cde9b33e06fc5540daf5ce0a0ca03c6428b296bff9ff8e95b)) = lcase("DEYBBMO")) OPTIONALs can be on top of BINDs or the other way around, both cases work, but not in between. 2- Error in COALESCE division by zero The following query does not give the expected results. SELECT ?test WHERE { BIND (COALESCE(1/0,3) AS ?test) } Virtuoso 22012 Error SR084: Division by 0. * Related: https://github.com/openlink/virtuoso-opensource/issues/309 * https://github.com/openlink/virtuoso-opensource/issues/140 Do you know if you can have a look on the above issues? Thank you in advance, Best regards, Ghislain [1] https://data-interop.era.europa.eu/endpoint Ghislain ATEMEZING Operational Data Unit Project Officer [cid:image001.png@01DA221C.E4C86D20] [cid:image002.png@01DA221C.E4C86D20] European Union Agency for Railways 120 rue Marc Lefrancq BP 20392 FR-59307 Valenciennes Cedex Visit our website: www.era.europa.eu<http://www.era.europa.eu/> E-mail: ghislain.atemez...@era.europa.eu<mailto:ghislain.atemez...@era.europa.eu> Mobile: +33 0609242967 Tel: +33 327096 500 [cid:image003.png@01DA221C.E4C86D20] This message is intended for the use of the addressee only and may contain information that is privileged and/or confidential information. If you are not the intended recipient, you are informed that any dissemination or other use of this message is strictly prohibited. If you have received this message in error, please inform the European Union Agency for Railways immediately by returning it and then delete the material. The European Union Agency for Railways endeavours to keep its network free of viruses; however you are strongly advised to check this e-mail and any attachments for viruses. The European Union Agency for Railways accepts no responsibility with regard to any computer virus transferred by way of this e-mail. ________________________________ Please consider the environment before deciding to print this email
_______________________________________________ Virtuoso-users mailing list Virtuoso-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/virtuoso-users
