Hello again,
Regarding the weird behaviour between our two virtuoso builds. We have one virtuoso (Version: 08.03.3316 Build: Mar 5 2020) which is our demo version of our db and I have installed a free version (Version: 07.20.3229 Build: Aug 27 2018) locally for testing, they have the same exactly db loaded and the same query runs in the local version and in the demo version i get no results.
We realized that the different results between the two version of Virtuoso is relevant to IN keyword because we noticed this in a really simple sql table.
The query:
SELECT TOP 1000 projectID, listPublicationCited, patentPublicationKey, patentClassificationCodes, patentTitle, patentPublicationDate, forwardPatentCitationCount FROM VAD.torr.publications_per_patents WHERE projectID IN (222983,304963,241683,241955,223156,223445,600847,603121,242013,200234,304884,212281,223243,305207,304900,201804,278981,248294)
order by forwardPatentCitationCount DESC
And all the results when using IN for filtering with ORDER the ids have the same ProjectID but when using OR keyword the results are fetched correctly. The problem doesnt persist if you remove order By with IN keyword. So we are guessing the problem is combination of IN and ORDER keywords.
Are you able to provide a test case to recreate this problem ?
As it is implied the problem occurs with Virtuoso 8.3 with queries with a combination of IN and ORDER keywords, but when I try such a query with the Virtuoso demo database querying a table using the IN and ORDER keywords I do get results:
SQL> select CustomerID, CompanyName, Country from customers where CustomerID IN ('ALFKI', 'ANATR') order by Country D
ESC;
CustomerID CompanyName Country
VARCHAR NOT NULL VARCHAR VARCHAR
_______________________________________________________________________________
ANATR Ana Trujillo Emparedados y helados Mexico
ALFKI Alfreds Futterkiste Germany
2 Rows. -- 1 msec.
SQL>
Is this the type of query that is failing for you ?
Yes, it’s similar to this case you are describing.
create table "VAD"."torr"."publications_per_patents"
(
"ID" INTEGER IDENTITY,
"projectID" INTEGER,
"listPublicationCited" VARCHAR,
"patentPublicationKey" VARCHAR,
"patentJurisdiction" VARCHAR,
"patentDocumentType" VARCHAR,
"patentClassificationCodes" VARCHAR,
"patentTitle" VARCHAR,
"patentPublicationDate" VARCHAR,
"patentFilingDate" VARCHAR,
"patentEarliestPriorityDate" VARCHAR,
"patentInventors" VARCHAR,
"patentApplicants" VARCHAR,
"patentOwners" VARCHAR,
"simplePatentFamilySize" INTEGER,
"backwardPatentCitationCount" INTEGER,
"forwardPatentCitationCount" INTEGER,
PRIMARY KEY ("ID")
);
And the 10first results of the query is:
Query result:
projectID
INTEGER listPublicationCited
VARCHAR patentPublicationKey
VARCHAR patentClassificationCodes
VARCHAR patentTitle
VARCHAR patentPublicationDate
VARCHAR forwardPatentCitationCount
INTEGER
223243 10.1038/nn.3032 WO 2016 040476 A1 A DROPLET-BASED METHOD AND APPARATUS FOR COMPOSITE SINGLE-CELL NUCLEIC ACID ANALYSIS 2016-03-17 00:00:00 61
223243 10.1126/science.1190897 WO 2012 032103 A1 MUTANT CHANNELRHODOPSIN 2 2012-03-15 00:00:00 54
223243 10.1038/nmeth.1851 WO 2014 117079 A1 G02B21/0032; G01N21/6458; G02B21/0076; G02B21/06; G02B21/365; G02B21/367; G02B26/06; G02B27/0075; G06K9/00134 DEPTH OF FIELD 3D IMAGING SLM MICROSCOPE 2014-07-31 00:00:00 47
223243 10.1167/iovs.10-5741 WO 2012 151324 A1 ANTISENSE COMPOUNDS TARGETING GENES ASSOCIATED WITH USHER SYNDROME 2012-11-08 00:00:00 20
223243 10.1371/journal.pone.0007517 US 9175047 B2 Peptidomimetic macrocycles 2015-11-03 00:00:00 20
223243 10.1002/emmm.201000119;10.1038/gt.2011.3 WO 2015 075154 A2 ARTIFICIAL DNA-BINDING PROTEINS AND USES THEREOF 2015-05-28 00:00:00 17
223243 10.1038/mt.2009.277 US 9585971 B2 Recombinant AAV capsid protein 2017-03-07 00:00:00 14
223243 10.1167/iovs.10-5741 WO 2012 066546 A1 AMINOGLYCOSIDES AND USES THEREOF IN TREATING GENETIC DISORDERS 2012-05-24 00:00:00 14
223243 10.1038/gt.2011.151;10.1038/gt.2011.3;10.1038/mt.2009.277 WO 2014 170480 A1 EFFECTIVE DELIVERY OF LARGE GENES BY DUAL AAV VECTORS 2014-10-23 00:00:00 13
223243 10.1038/gt.2011.151 US 2010 0297084 A1 METHOD FOR TRANSDUCING CELLS WITH PRIMARY CILIA 2010-11-25 00:00:00 13
No. of rows in result: 10
the right results in the older version:
Query result:
projectID
INTEGER listPublicationCited
VARCHAR patentPublicationKey
VARCHAR patentClassificationCodes
VARCHAR patentTitle
VARCHAR patentPublicationDate
VARCHAR forwardPatentCitationCount
INTEGER
242013 10.1038/nn.3032 WO 2016 040476 A1 A DROPLET-BASED METHOD AND APPARATUS FOR COMPOSITE SINGLE-CELL NUCLEIC ACID ANALYSIS 2016-03-17 00:00:00 61
242013 10.1126/science.1190897 WO 2012 032103 A1 MUTANT CHANNELRHODOPSIN 2 2012-03-15 00:00:00 54
242013 10.1038/nmeth.1851 WO 2014 117079 A1 G02B21/0032; G01N21/6458; G02B21/0076; G02B21/06; G02B21/365; G02B21/367; G02B26/06; G02B27/0075; G06K9/00134 DEPTH OF FIELD 3D IMAGING SLM MICROSCOPE 2014-07-31 00:00:00 47
241955 10.1167/iovs.10-5741 WO 2012 151324 A1 ANTISENSE COMPOUNDS TARGETING GENES ASSOCIATED WITH USHER SYNDROME 2012-11-08 00:00:00 20
223243 10.1371/journal.pone.0007517 US 9175047 B2 Peptidomimetic macrocycles 2015-11-03 00:00:00 20
223445 10.1002/emmm.201000119;10.1038/gt.2011.3 WO 2015 075154 A2 ARTIFICIAL DNA-BINDING PROTEINS AND USES THEREOF 2015-05-28 00:00:00 17
223445 10.1038/mt.2009.277 US 9585971 B2 Recombinant AAV capsid protein 2017-03-07 00:00:00 14
241955 10.1167/iovs.10-5741 WO 2012 066546 A1 AMINOGLYCOSIDES AND USES THEREOF IN TREATING GENETIC DISORDERS 2012-05-24 00:00:00 14
223445 10.1038/gt.2011.151;10.1038/gt.2011.3;10.1038/mt.2009.277 WO 2014 170480 A1 EFFECTIVE DELIVERY OF LARGE GENES BY DUAL AAV VECTORS 2014-10-23 00:00:00 13
223445 10.1038/gt.2011.151 US 2010 0297084 A1 METHOD FOR TRANSDUCING CELLS WITH PRIMARY CILIA 2010-11-25 00:00:00 13
No. of rows in result: 10
In your query I note there is a “TOP 1000” restriction in the query which seems to be the source of the problem as I have been able to create a test case where the first value of column in the IN clause is repeatedly returned when a TOP clause is in the query but not when it is removed:
Query with “TOP”:
SQL> select top 200 EmployeeID, ShipName, ShipCountry from orders where EmployeeID IN (6, 9) order by ShipCountry desc;
EmployeeID ShipName ShipCountry
INTEGER VARCHAR VARCHAR
_______________________________________________________________________________
6 LILA-Supermercado Venezuela
6 HILARI?N-Abastos Venezuela
6 HILARI?N-Abastos Venezuela
6 Split Rail Beer & Ale United States
6 Rattlesnake Canyon Grocery United States
6 Lonesome Pine Restaurant United States
6 Save-a-lot Markets United States
.
.
.
Query without “TOP”:
SQL> select EmployeeID, ShipName, ShipCountry from orders where EmployeeID IN (6, 9) order by ShipCountry desc;
EmployeeID ShipName ShipCountry
INTEGER VARCHAR VARCHAR
_______________________________________________________________________________
6 LILA-Supermercado Venezuela
6 HILARI?N-Abastos Venezuela
9 HILARI?N-Abastos Venezuela
6 Split Rail Beer & Ale United States
6 Rattlesnake Canyon Grocery United States
6 Lonesome Pine Restaurant United States
9 Save-a-lot Markets United States
.
.
.
Please confirm if removing the TOP clause enabled the expected values to be returned ?
Oh yes! It is the top keyword, indeed
Actually it the combination of “TOP” and “ORDER BY” clauses in the query that is the source of the problem as if the “ORDER BY” is removed the correct IN clause values are returned:
SQL> select top 1000 EmployeeID, ShipName, ShipCountry from orders where EmployeeID IN (6, 9);
EmployeeID ShipName ShipCountry
INTEGER VARCHAR VARCHAR
_______________________________________________________________________________
6 Toms Spezialit?ten Germany
9 Richter Supermarkt Switzerland
9 Ernst Handel Austria
6 Folk och f? HB Sweden
6 Split Rail Beer & Ale United States
6 Rattlesnake Canyon Grocery United States
This issue has been reported to development to look into and fix.