Weird behaviour with IN and ORDER keyword between two versions

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.