[Java] Why am I getting double results from db. openQuery(qry, ...) vs db.query(qry., ..)?

[theronic]

Am I using Java’s ICursor impl. correctly when streaming query results from XTDB’s Java interface, or is this a potential bug in openQuery? If I run the following streaming query, I get two rows for every query result when there should be only one:

String qry = String.format("""
            {:find  [(pull ?tx [* {:entry/_tx [* {:entry/account [*]}]}])]
             :in    [start-date end-date [account]]
             :where [[?tx :tx/posting-date ?date]
                     [(>= ?posting-date start-date)]
                     [(< ?posting-date end-date)]
                     [?entry :entry/tx ?tx]
                     [?entry :entry/account account]]}
        """,

var txes = new ArrayList<LedgerTransaction>();

try (ICursor<List<?>> cursor = db.openQuery(qry, startDate, endDate, filters.accounts)) {
    while (cursor.hasNext()) {
        List<?> nextResult = cursor.next();
        Object tx = nextResult.get(0);
        log.debug("adding" + tx.toString());
        txes.add(LedgerTransaction.fromMap((PersistentArrayMap) tx));
    }
    cursor.close();
}

return txes;

However, when I run the same query as an eager, non-streaming query, I get the correct result of one row per result:

return db.query(qry, startDate, endDate, filters.accounts).stream()
        .map(tx -> LedgerTransaction.fromMap((PersistentArrayMap)(tx.get(0))))
        .collect(Collectors.toList()); // correct

[refset]

Hi @theronic this is expected and is a natural (unavoidable) consequence of how query execution happens. See the mention & recommendation here on deduplicating the result tuples: https://docs.xtdb.com/language-reference/1.21.0/datalog-queries/#streaming-queries

[theronic]

I don't understand why this query would naturally return duplicate results. Questions:

1. Does db.query(...) call dedupe for me?
2. Where can I read more about the query execution?
3. Is this because XTDB does not support the :with clause like Datomic does?

[refset]

1. Yep, see the reference to set here:

   xtdb/core/src/xtdb/query.clj

   Line 1990 in 7e9c5bf

   (let [result-coll-fn (if (some (normalize-query query) [:order-by :limit :offset]) vec set)

2. The query.clj namespace is relatively self-contained (if rather impenetrable), but your best bet is probably to dig through some old issues, PRs, and talks for clues. Happy to attempt to talk you through it a bit when we speak next week also 🙂
3. It's not because of that, but that is certainly a related concept which we've consciously decided isn't really required for XT

[theronic]

Hmm so XTDB has the inverse behaviour of Datomic here: by default, Datomic de-duplicates into a Set on the values of :find, but this can be controlled by the :with clause. XT's db.openQuery does not de-duplicate, buuuut, db.query does?

It seems unintuitive that db.openQuery and db.query should return different results.

https://docs.datomic.com/on-prem/query/query.html#with

[refset]

openQuery should be considered as an advanced feature for performing lazy processing in userspace (potentially even across multiple concurrent queries), and laziness necessarily precludes any deduplication. These tests borrowed from DataScript (which implements :with) might be illustrative

xtdb/test/test/xtdb/query_test.clj

Lines 2773 to 2799 in 7e9c5bf

	#_(t/testing "with"
	(t/is (= (d/q '[:find ?heads
	:with ?monster
	:in [[?monster ?heads]] ]
	[["Medusa" 1]
	["Cyclops" 1]
	["Chimera" 1] ])
	[[1] [1] [1]])))
	;; This is solved as XTDB performs the aggregation before turning
	;; it into a set.
	#_(t/testing "Wrong grouping without :with"
	(t/is (= (xt/q db '[:find (sum ?heads)
	:where [(identity [["Cerberus" 3]
	["Medusa" 1]
	["Cyclops" 1]
	["Chimera" 1]]) [[?monster ?heads]]]])
	#{[4]})))
	(t/testing "Multiple aggregates, correct grouping with :with"
	(t/is (= (xt/q db '[:find (sum ?heads) (min ?heads) (max ?heads) (count ?heads) (count-distinct ?heads)
	;; :with ?monster
	:where [(identity [["Cerberus" 3]
	["Medusa" 1]
	["Cyclops" 1]
	["Chimera" 1]]) [[?monster ?heads]]]])
	#{[6 1 3 4 2]})))