Re: [Virtuoso-users] Virtuoso support for ERA - Virtuoso backend

2023-11-30 Thread Hugh Williams via Virtuoso-users 
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 

 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 

 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 

 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: 

SELECT DISTINCT ?AA ?BB ?CC ?DD
FROM 
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 

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  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
Community Support: 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 
>  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

[Virtuoso-users] Virtuoso support for ERA - Virtuoso backend

2023-11-30 Thread ATEMEZING Ghislain via Virtuoso-users 
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/notYetAvailable 
http://data.europa.eu/949/demonstrationINF}, 
http://data.europa.eu/949/notYetAvailable, ?unbound),

IF(EXISTS{?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 
http://data.europa.eu/949/notApplicable 
http://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/notYetAvailable 
http://data.europa.eu/949/verificationINF}, 
http://data.europa.eu/949/notYetAvailable, ?unbound),

IF(EXISTS{?5611b111a3e8b6a75f9cf140ebfc88a2400da808384163a68f6ea2b27feae0a2 
http://data.europa.eu/949/notApplicable 
http://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/iss

Re: [Virtuoso-users] Virtuoso support for ERA - Virtuoso backend

2023-11-30 Thread ATEMEZING Ghislain via Virtuoso-users 
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 
Sent: Tuesday, November 28, 2023 1:55 PM
To: ATEMEZING Ghislain 
Cc: AGUADO Marina ; LUCAS RECHE David (DIGIT-EXT) 
; virtuoso-users@lists.sourceforge.net; 
Kingsley Idehen ; Fainti, Anestis 
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
 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
> 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
 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: 

SELECT DISTINCT ?AA ?BB ?CC ?DD
FROM 
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

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  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
Community Support: 
https://community.openlinksw.com