Sparql select DISTINCT not working as expected (gives duplicates) in Virtuoso

MakeItWork · September 20, 2025, 8:39pm

OpenLink Virtuoso
Version: 07.20.3241
Build: May 21 2025 (bae7c13af8)

The following select returns four records, two of them are identical
(dataset provided)

SELECT distinct * from http://example/graph/Example_1 WHERE {
?r_type rdfs:subPropertyOf* http://example/property/positive .
?r rdf:type ?r_type .
?r http://example/property/reference ?ref .
?ref http://example/property/partOf ?doc .
?s ?r ?o .
?o rdf:type ?o_type .
optional{ ?doc http://example/property/startTime ?startTime . }
filter (?s = http://example/resource/P_1) }

Strangely enough any of the following lines removed will prevent the doubles:

?o rdf:type ?o_type .
optional{ ?doc http://example/property/startTime ?startTime . }

I presume the error occurs due to the SELECT cost and could be resolved by settings change.
Any advise will be appreciated.

Dataset TTL

@prefix rdf: http://www.w3.org/1999/02/22-rdf-syntax-ns# .
@prefix ns1: http://example/property/ .
ns1:partOf rdf:type rdf:Property .
ns1:startTime rdf:type rdf:Property .
@prefix ns2: http://example/resource/ .
@prefix ns3: http://example/ontology/ .
ns2:P_1 rdf:type ns3:Person .
@prefix rdfs: http://www.w3.org/2000/01/rdf-schema# .
ns3:Document rdf:type rdfs:Class .
ns3:Organization rdf:type rdfs:Class .
ns3:Person rdf:type rdfs:Class .
ns3:Reference rdf:type rdfs:Class .
ns1:r_1 rdf:type ns1:related .
ns1:r_2 rdf:type ns1:positive .
ns1:r_3 rdf:type ns1:related .
ns1:reference rdf:type rdf:Property .
ns1:relation rdf:type rdf:Property .
ns2:Ref_1 rdf:type ns3:Reference .
ns2:Ref_2 rdf:type ns3:Reference .
ns2:Ref_3 rdf:type ns3:Reference .
ns3:Location rdf:type rdfs:Class .
ns2:O_1 rdf:type ns3:Organization .
ns2:O_2 rdf:type ns3:Organization .
ns2:D_1 rdf:type ns3:Document .
ns2:D_2 rdf:type ns3:Document .
ns2:D_3 rdf:type ns3:Document .
ns2:L_1 rdf:type ns3:Location .
ns1:positive rdfs:subPropertyOf ns1:relation .
ns1:related rdfs:subPropertyOf ns1:positive .
ns2:Ref_1 ns1:partOf ns2:D_1 .
ns2:Ref_2 ns1:partOf ns2:D_2 .
ns2:Ref_3 ns1:partOf ns2:D_3 .
ns2:D_2 ns1:startTime ns2:T_1 .
ns2:D_3 ns1:startTime ns2:T_2 .
ns2:P_1 ns1:r_1 ns2:O_1 ;
ns1:r_2 ns2:O_2 ;
ns1:r_3 ns2:L_1 .
ns1:r_1 ns1:reference ns2:Ref_1 .
ns1:r_2 ns1:reference ns2:Ref_2 .
ns1:r_3 ns1:reference ns2:Ref_3 .

Dataset NQ

http://example/property/r_1 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/property/related http://example/graph/Example_1 .
http://example/property/r_1 http://example/property/reference http://example/resource/Ref_1 http://example/graph/Example_1 .

http://example/property/r_2 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/property/positive http://example/graph/Example_1 .
http://example/property/r_2 http://example/property/reference http://example/resource/Ref_2 http://example/graph/Example_1 .

http://example/property/r_3 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/property/related http://example/graph/Example_1 .
http://example/property/r_3 http://example/property/reference http://example/resource/Ref_3 http://example/graph/Example_1 .

http://example/resource/D_1 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Document http://example/graph/Example_1 .

http://example/resource/D_2 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Document http://example/graph/Example_1 .
http://example/resource/D_2 http://example/property/startTime http://example/resource/T_1 http://example/graph/Example_1 .

http://example/resource/D_3 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Document http://example/graph/Example_1 .
http://example/resource/D_3 http://example/property/startTime http://example/resource/T_2 http://example/graph/Example_1 .

http://example/resource/O_1 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Organization http://example/graph/Example_1 .
http://example/resource/O_2 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Organization http://example/graph/Example_1 .
http://example/resource/L_1 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Location http://example/graph/Example_1 .

http://example/resource/P_1 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Person http://example/graph/Example_1 .
http://example/resource/P_1 http://example/property/r_1 http://example/resource/O_1 http://example/graph/Example_1 .
http://example/resource/P_1 http://example/property/r_2 http://example/resource/O_2 http://example/graph/Example_1 .
http://example/resource/P_1 http://example/property/r_3 http://example/resource/L_1 http://example/graph/Example_1 .

http://example/resource/Ref_1 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Reference http://example/graph/Example_1 .
http://example/resource/Ref_1 http://example/property/partOf http://example/resource/D_1 http://example/graph/Example_1 .

http://example/resource/Ref_2 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Reference http://example/graph/Example_1 .
http://example/resource/Ref_2 http://example/property/partOf http://example/resource/D_2 http://example/graph/Example_1 .

http://example/resource/Ref_3 http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://example/ontology/Reference http://example/graph/Example_1 .
http://example/resource/Ref_3 http://example/property/partOf http://example/resource/D_3 http://example/graph/Example_1 .

http://example/ontology/Location http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://www.w3.org/2000/01/rdf-schema#Class http://example/graph/Example_1 .
http://example/ontology/Organization http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://www.w3.org/2000/01/rdf-schema#Class http://example/graph/Example_1 .
http://example/ontology/Person http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://www.w3.org/2000/01/rdf-schema#Class http://example/graph/Example_1 .
http://example/ontology/Document http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://www.w3.org/2000/01/rdf-schema#Class http://example/graph/Example_1 .
http://example/ontology/Reference http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://www.w3.org/2000/01/rdf-schema#Class http://example/graph/Example_1 .

http://example/property/startTime http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://www.w3.org/1999/02/22-rdf-syntax-ns#Property http://example/graph/Example_1 .
http://example/property/reference http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://www.w3.org/1999/02/22-rdf-syntax-ns#Property http://example/graph/Example_1 .
http://example/property/partOf http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://www.w3.org/1999/02/22-rdf-syntax-ns#Property http://example/graph/Example_1 .

http://example/property/relation http://www.w3.org/1999/02/22-rdf-syntax-ns#type http://www.w3.org/1999/02/22-rdf-syntax-ns#Property http://example/graph/Example_1 .
http://example/property/positive http://www.w3.org/2000/01/rdf-schema#subPropertyOf http://example/property/relation http://example/graph/Example_1 .
http://example/property/related http://www.w3.org/2000/01/rdf-schema#subPropertyOf http://example/property/positive http://example/graph/Example_1 .

hwilliams · September 22, 2025, 12:49pm

@MakeItWork I am not able to recreate this issue having load the NQUAD dataset into local Virtuoso open source database running binaries:

existing binary Version 07.20.3240-pthreads for Linux as of Jan 30 2025 (23abbfbe9e)
latest binary Version 07.20.3241-pthreads for Linux as of Sep 22 2025 (e0e4ec0272)

In both case the following is returned:

SQL> SPARQL  SELECT distinct * from <http://example/graph/Example_1> WHERE {   ?r_type rdfs:subPropertyOf*  <http://example/property/positive> .  ?r rdf:type ?r_type .  ?r <http://example/property/reference> ?ref .  ?ref <http://example/property/partOf>  ?doc .  ?s ?r ?o .  ?o rdf:type ?o_type .  optional{ ?doc <http://example/property/startTime> ?startTime . }  filter (?s = <http://example/resource/P_1>) };
r                                                                                 r_type                                                                            ref                                                                               doc                                                                               s                            o                                                                                 o_type                                                                            startTime
LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      VARCHAR                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR
_______________________________________________________________________________

http://example/property/r_3                                                       http://example/property/related                                                   http://example/resource/Ref_3                                                     http://example/resource/D_3                                                       http://example/resource/P_1  http://example/resource/L_1                                                       http://example/ontology/Location                                                  http://example/resource/T_2
http://example/property/r_2                                                       http://example/property/positive                                                  http://example/resource/Ref_2                                                     http://example/resource/D_2                                                       http://example/resource/P_1  http://example/resource/O_1                                                       http://example/ontology/Organization                                              http://example/resource/T_1
http://example/property/r_1                                                       http://example/property/related                                                   http://example/resource/Ref_1                                                     http://example/resource/D_1                                                       http://example/resource/P_1  http://example/resource/O_1                                                       http://example/ontology/Organization                                              NULL

3 Rows. -- 6 msec.
SQL

Both from isql and the SPARQL endpoint (~/sparql).

MakeItWork · September 24, 2025, 7:30am

Hello Williams,
@hwilliams Steps to recreate the error:

1 Virtuoso Version: 07.20.3241 install
2 Package “Faceted Browser” 1.16.123/ 2025-05-21 install
3 Virtuoso reboot
4 The dump load (with an 20 extra lines for graph “Example_2” in additional to the original “Example_1” graph data.

Combined dataset for the both graphs:

Sparql

SELECT distinct * from <http://example/graph/Example_1> WHERE { 
?r_type rdfs:subPropertyOf*  <http://example/property/positive>. 
?r rdf:type ?r_type . 
?r <http://example/property/reference> ?ref .                      
?ref <http://example/property/partOf>  ?doc . 
?s ?r ?o . 
?o rdf:type ?o_type . 
optional{ ?doc <http://example/property/startTime> ?startTime .  }
filter (?s = <http://example/resource/P_1>)}

Dataset

<http://example/ontology/Document>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://www.w3.org/2000/01/rdf-schema#Class>	<http://example/graph/Example_1> .
<http://example/ontology/Location>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://www.w3.org/2000/01/rdf-schema#Class>	<http://example/graph/Example_1> .
<http://example/ontology/Organization>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://www.w3.org/2000/01/rdf-schema#Class>	<http://example/graph/Example_1> .
<http://example/ontology/Person>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://www.w3.org/2000/01/rdf-schema#Class>	<http://example/graph/Example_1> .
<http://example/ontology/Reference>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://www.w3.org/2000/01/rdf-schema#Class>	<http://example/graph/Example_1> .
<http://example/property/partOf>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#Property>	<http://example/graph/Example_1> .
<http://example/property/positive>	<http://www.w3.org/2000/01/rdf-schema#subPropertyOf>	<http://example/property/relation>	<http://example/graph/Example_1> .
<http://example/property/r_1>	<http://example/property/reference>	<http://example/resource/Ref_1>	<http://example/graph/Example_1> .
<http://example/property/r_1>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/property/related>	<http://example/graph/Example_1> .
<http://example/property/r_2>	<http://example/property/reference>	<http://example/resource/Ref_2>	<http://example/graph/Example_1> .
<http://example/property/r_2>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/property/positive>	<http://example/graph/Example_1> .
<http://example/property/r_3>	<http://example/property/reference>	<http://example/resource/Ref_3>	<http://example/graph/Example_1> .
<http://example/property/r_3>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/property/related>	<http://example/graph/Example_1> .
<http://example/property/reference>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#Property>	<http://example/graph/Example_1> .
<http://example/property/related>	<http://www.w3.org/2000/01/rdf-schema#subPropertyOf>	<http://example/property/positive>	<http://example/graph/Example_1> .
<http://example/property/relation>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#Property>	<http://example/graph/Example_1> .
<http://example/property/startTime>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#Property>	<http://example/graph/Example_1> .
<http://example/resource/D_1>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Document>	<http://example/graph/Example_1> .
<http://example/resource/D_2>	<http://example/property/startTime>	<http://example/resource/T_1>	<http://example/graph/Example_1> .
<http://example/resource/D_2>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Document>	<http://example/graph/Example_1> .
<http://example/resource/D_3>	<http://example/property/startTime>	<http://example/resource/T_2>	<http://example/graph/Example_1> .
<http://example/resource/D_3>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Document>	<http://example/graph/Example_1> .
<http://example/resource/L_1>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Location>	<http://example/graph/Example_1> .
<http://example/resource/O_1>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Organization>	<http://example/graph/Example_1> .
<http://example/resource/O_2>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Organization>	<http://example/graph/Example_1> .
<http://example/resource/P_1>	<http://example/property/r_1>	<http://example/resource/O_1>	<http://example/graph/Example_1> .
<http://example/resource/P_1>	<http://example/property/r_2>	<http://example/resource/O_2>	<http://example/graph/Example_1> .
<http://example/resource/P_1>	<http://example/property/r_3>	<http://example/resource/L_1>	<http://example/graph/Example_1> .
<http://example/resource/P_1>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Person>	<http://example/graph/Example_1> .
<http://example/resource/Ref_1>	<http://example/property/partOf>	<http://example/resource/D_1>	<http://example/graph/Example_1> .
<http://example/resource/Ref_1>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Reference>	<http://example/graph/Example_1> .
<http://example/resource/Ref_2>	<http://example/property/partOf>	<http://example/resource/D_2>	<http://example/graph/Example_1> .
<http://example/resource/Ref_2>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Reference>	<http://example/graph/Example_1> .
<http://example/resource/Ref_3>	<http://example/property/partOf>	<http://example/resource/D_3>	<http://example/graph/Example_1> .
<http://example/resource/Ref_3>	<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>	<http://example/ontology/Reference>	<http://example/graph/Example_1> .
<http://example/resource/0001> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0002> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0003> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0004> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0005> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0006> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0007> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0008> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0009> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0010> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0011> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0012> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0013> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0014> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0015> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0016> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0017> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0018> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0019> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .
<http://example/resource/0020> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://example/ontology/Document> <http://example/graph/Example_2> .

Sometimes just 20 extra lines is not enough, the following script can be used to add more.

To recreate the error the full dump needs to be loaded before the sparql select used.
(virtuoso.db / virtuoso-temp.db need to be deleted and big enough dump loaded first.)

Extra lines script

for i in seq -w 1 1500
do
echo <http://example/resource/$i\> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type\> <http://example/ontology/Document\> <http://example/graph/Example_1\> . >> 1.txt
done

Best regards

hwilliams · September 24, 2025, 12:00pm

I loaded this new NQUAD dataset provided and now the query returns 4 rows, but none are duplicate:

SQL> SPARQL SELECT distinct * from <http://example/graph/Example_1> WHERE {  ?r_type rdfs:subPropertyOf*  <http://example/property/positive>.  ?r rdf:type ?r_type .  ?r <http://example/property/reference> ?ref .                       ?ref <http://example/property/partOf>  ?doc .  ?s ?r ?o .  ?o rdf:type ?o_type .  optional{ ?doc <http://example/property/startTime> ?startTime .  } filter (?s = <http://example/resource/P_1>)};
r                                                                                 r_type                                                                            ref                                                                               doc                                                                               s                            o                                                                                 o_type                                                                            startTime
LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      VARCHAR                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR
_______________________________________________________________________________

http://example/property/r_2                                                       http://example/property/positive                                                  http://example/resource/Ref_2                                                     http://example/resource/D_2                                                       http://example/resource/P_1  http://example/resource/O_2                                                       http://example/ontology/Organization                                              http://example/resource/T_1
http://example/property/r_3                                                       http://example/property/related                                                   http://example/resource/Ref_3                                                     http://example/resource/D_3                                                       http://example/resource/P_1  http://example/resource/L_1                                                       http://example/ontology/Location                                                  http://example/resource/T_2
http://example/property/r_2                                                       http://example/property/positive                                                  http://example/resource/Ref_2                                                     http://example/resource/D_2                                                       http://example/resource/P_1  http://example/resource/O_1                                                       http://example/ontology/Organization                                              http://example/resource/T_1
http://example/property/r_1                                                       http://example/property/related                                                   http://example/resource/Ref_1                                                     http://example/resource/D_1                                                       http://example/resource/P_1  http://example/resource/O_1                                                       http://example/ontology/Organization                                              NULL

4 Rows. -- 6 msec.
SQL>

What is that Extra Lines script suppose to do and how do you run it ? As I get errors when running it:

ubuntu@ip-172-30-0-77:~$ for i in seq -w 1 1500
> do
> echo <http://example/resource/$i\> <http://www.w3.org/1999/02/22-rdf-syntax-ns#type\> <http://example/ontology/Document\> <http://example/graph/Example_1\> . >> 1.txt
> done
-bash: http://example/resource/seq>: No such file or directory
-bash: http://example/resource/-w>: No such file or directory
-bash: http://example/resource/1>: No such file or directory
-bash: http://example/resource/1500>: No such file or directory
ubuntu@ip-172-30-0-77:~$

Can you not provided a dump the graph in your Virtuoso database with this dump_one_graph() procedure or if the database is not too large the complete database in its existing state.

You should also check the database for any possible corruption by performing a database integrity check against the database with the command:

backup '/dev/null';

and check the primary key on the RDF_QUAD table is not broken by running the command:

select count (s), count (p ), count (o ), count (g ) from rdf_quad   table option (index rdf_quad, check);

The counts of the s, p, o, g columns should all be the same

Check the RDF Index counts with the queries:

select count (*) from rdf_quad a table option (index rdf_quad) where not exists (select 1 from rdf_quad b table option (loop, index rdf_quad_pogs) where a.g = b.g and a.p = b.p and a.o = b.o and a.s = b.s); 
select count (*) from rdf_quad a table option (index rdf_quad_pogs) where not exists (select 1 from rdf_quad b table option (loop, index primary key) where a.g = b.g and a.p = b.p and a.o = b.o and a.s = b.s); 
select count (*) from rdf_quad a table option (index rdf_quad_pogs) where not exists (select 1 from rdf_quad b table option (loop, index rdf_quad_op) where a.g = b.g and a.p = b.p and a.o = b.o and a.s = b.s); 
select count (*) from rdf_quad a table option (index rdf_quad_pogs) where not exists (select 1 from rdf_quad b table option (loop, index rdf_quad_sp) where a.g = b.g and a.p = b.p and a.o = b.o and a.s = b.s); 
select count (*) from rdf_quad a table option (index rdf_quad_pogs) where not exists (select 1 from rdf_quad b table option (loop, index rdf_quad_gs) where a.g = b.g and a.p = b.p and a.o = b.o and a.s = b.s);

If any counts are not 0 it indicates a bad index.

MakeItWork · September 24, 2025, 3:55pm

1
I used dump_one_graph() procedure and reused the dump saved. Still have the same doubles.

Dump of graph TTL

# Dump of graph <http://example/graph/Example_1>, as of 2025-09-24 10:19:00.812939
@base <> .
@prefix rdf:	<http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix ns1:	<http://example/property/> .
ns1:partOf	rdf:type	rdf:Property .
ns1:reference	rdf:type	rdf:Property .
ns1:relation	rdf:type	rdf:Property .
ns1:startTime	rdf:type	rdf:Property .
@prefix ns2:	<http://example/ontology/> .
@prefix rdfs:	<http://www.w3.org/2000/01/rdf-schema#> .
ns2:Document	rdf:type	rdfs:Class .
ns2:Location	rdf:type	rdfs:Class .
ns2:Organization	rdf:type	rdfs:Class .
ns2:Person	rdf:type	rdfs:Class .
ns2:Reference	rdf:type	rdfs:Class .
<http://example/resource/0001>	rdf:type	ns2:Document .
<http://example/resource/0002>	rdf:type	ns2:Document .
<http://example/resource/0003>	rdf:type	ns2:Document .
<http://example/resource/0004>	rdf:type	ns2:Document .
<http://example/resource/0005>	rdf:type	ns2:Document .
<http://example/resource/0006>	rdf:type	ns2:Document .
<http://example/resource/0007>	rdf:type	ns2:Document .
<http://example/resource/0008>	rdf:type	ns2:Document .
<http://example/resource/0009>	rdf:type	ns2:Document .
<http://example/resource/0010>	rdf:type	ns2:Document .
<http://example/resource/0011>	rdf:type	ns2:Document .
<http://example/resource/0012>	rdf:type	ns2:Document .
<http://example/resource/0013>	rdf:type	ns2:Document .
<http://example/resource/0014>	rdf:type	ns2:Document .
<http://example/resource/0015>	rdf:type	ns2:Document .
<http://example/resource/0016>	rdf:type	ns2:Document .
<http://example/resource/0017>	rdf:type	ns2:Document .
<http://example/resource/0018>	rdf:type	ns2:Document .
<http://example/resource/0019>	rdf:type	ns2:Document .
<http://example/resource/0020>	rdf:type	ns2:Document .
@prefix ns4:	<http://example/resource/> .
ns4:D_1	rdf:type	ns2:Document .
ns4:D_2	rdf:type	ns2:Document .
ns4:D_3	rdf:type	ns2:Document .
ns4:L_1	rdf:type	ns2:Location .
ns4:O_1	rdf:type	ns2:Organization .
ns4:O_2	rdf:type	ns2:Organization .
ns4:P_1	rdf:type	ns2:Person .
ns4:Ref_1	rdf:type	ns2:Reference .
ns4:Ref_2	rdf:type	ns2:Reference .
ns4:Ref_3	rdf:type	ns2:Reference .
ns1:r_2	rdf:type	ns1:positive .
ns1:r_3	rdf:type	ns1:related .
ns1:r_1	rdf:type	ns1:related .
ns1:related	rdfs:subPropertyOf	ns1:positive .
ns1:positive	rdfs:subPropertyOf	ns1:relation .
ns4:P_1	ns1:r_3	ns4:L_1 .
ns4:Ref_1	ns1:partOf	ns4:D_1 .
ns4:Ref_2	ns1:partOf	ns4:D_2 .
ns4:Ref_3	ns1:partOf	ns4:D_3 .
ns4:P_1	ns1:r_1	ns4:O_1 ;
	ns1:r_2	ns4:O_2 .
ns1:r_1	ns1:reference	ns4:Ref_1 .
ns1:r_2	ns1:reference	ns4:Ref_2 .
ns1:r_3	ns1:reference	ns4:Ref_3 .
ns4:D_2	ns1:startTime	ns4:T_1 .
ns4:D_3	ns1:startTime	ns4:T_2 .

select count (s), count (p ), count (o ), count (g ) from rdf_quad table option (index rdf_quad, check);
return: 9806 9806 9806 9806

“RDF Index counts with the queries”
All return 0;

I loaded this new NQUAD dataset provided and now the query returns 4 rows, but none are duplicate:

This result looks wrong too:

“r_2” is a property “relation positive” that used only once to connect P_1 and O_2

ns4:P_1	ns1:r_1	ns4:O_1 ;
	ns1:r_2	ns4:O_2 .

Thus the line #3 should not exist

>http://example/property/r_2                                                       http://example/property/positive                                                  http://example/resource/Ref_2                                                     http://example/resource/D_2                                                       http://example/resource/P_1  http://example/resource/O_1

MakeItWork · September 24, 2025, 4:30pm

It makes “1.txt” with a simple data to add to the graph and make it larger.
(it’s already in the TTL provided).

Without the extra lines I have no errors.

So, the issue probably could be resolved by adjusting the cache/memory parameters.

hwilliams · September 24, 2025, 8:26pm

@MakeItWork Loading the dump_one_graph() TTL dataset you provided the query returns 3 rows none of which are duplicates:

SQL> SPARQL SELECT distinct * from <http://example/graph/Example_1> WHERE {  ?r_type rdfs:subPropertyOf*  <http://example/property/positive>.  ?r rdf:type ?r_type .  ?r <http://example/property/reference> ?ref .                       ?ref <http://example/property/partOf>  ?doc .  ?s ?r ?o .  ?o rdf:type ?o_type .  optional{ ?doc <http://example/property/startTime> ?startTime .  } filter (?s = <http://example/resource/P_1>)};
r                                                                                 r_type                                                                            ref                                                                               doc                                                                               s                            o                                                                                 o_type                                                                            startTime
LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      VARCHAR                      LONG VARCHAR                                                                      LONG VARCHAR                                                                      LONG VARCHAR
_______________________________________________________________________________

http://example/property/r_2                                                       http://example/property/positive                                                  http://example/resource/Ref_2                                                     http://example/resource/D_2                                                       http://example/resource/P_1  http://example/resource/O_2                                                       http://example/ontology/Organization                                              http://example/resource/T_1
http://example/property/r_3                                                       http://example/property/related                                                   http://example/resource/Ref_3                                                     http://example/resource/D_3                                                       http://example/resource/P_1  http://example/resource/L_1                                                       http://example/ontology/Location                                                  http://example/resource/T_2
http://example/property/r_1                                                       http://example/property/related                                                   http://example/resource/Ref_1                                                     http://example/resource/D_1                                                       http://example/resource/P_1  http://example/resource/O_1                                                       http://example/ontology/Organization                                              NULL

3 Rows. -- 7 msec.
SQL>

What issue probably could be resolved by adjusting the cache/memory parameters ?