DuckDB returns different results on different OS

[manticore-projects]

What happens?

When running a simple statement on different OS Linux, Windows and MacOS the result can differ.
This frustrates unit tests.

To Reproduce

SELECT ARRAY_DISTINCT(['A', 'A', 'B', NULL, NULL]) AS arr;

On my Linux and GitHub Linux, reliably I will get:

[B, A]

On my Windows VM and GitHub Windows, reliably I will get:

[A, B]

Although I have experimented already with all kind of configurations:

        Properties info = new Properties();
        info.put("old_implicit_casting", "true");
        info.put("default_null_order", "NULLS FIRST");
        info.put("default_order", "ASC");
        info.put("memory_limit", "1GB");
        info.put("threads", "1");

OS:

Any

DuckDB Version:

1.0

DuckDB Client:

Java

Full Name:

Andreas Reichel

Affiliation:

manticore-projects.com

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[hannes]

This is expected, there is some non-deterministic behavior around parallelism and hashing, which happens here.

[manticore-projects]

Thank you for the feedback.
I get your point, but it is really difficult to catch this reliably on the user's side and the impact can be quite big. Imagine a developer on one OS, testing successfully and then deploying to another OS. Both the rocket and the stock market can crash from this.

Especially when using Java/Python/R this will become even more pronounced because we simple minded developers don't think about different OSes but would expect same outcome.

[manticore-projects]

At least with threads=1 option in place I would have hoped for reliable outcomes.

[szarnyasg]

@manticore-projects, I have three main points on this:

1. Why making this 100% portable is unlikely:

   Ensuring that the outcomes of functions with bag semantics (such as list_distinct) are deterministic and always the same with all platforms, compilers, CPU architectures, etc. would require introducing performance bottlenecks in the code (e.g., additional sorting steps, or restricting the use of parallelism or restricting its efficiency by enforcing that it's done in a certain way).

   The documentation on list_distinct currently says:

   Removes all duplicates and NULL values from a list. Does not preserve the original order.

   I think the "does not preserve the original order" is already a hint for non-determinism but it could certainly be extended further to explain the non-determinism. I'll add some clarification in a moment. We can also add a documentation page that lists the cases where non-deterministic behavior is expected.

2. Why adding a workaround for threads=1 is unlikely?

   A key point in DuckDB is performance. I'm not aware of cases where folks use threads=1 to make the results deterministic and we certainly don't want to make any such promise.

3. How can this be worked around?

   You can enforce deterministic ordering by sorting:

   SELECT list_sort(array_distinct(['A', 'A', 'B', NULL, NULL])) AS i
   ORDER BY i;

   Or you can unnest and sort on hash.

   SELECT list(i ORDER BY hash(i)) AS l
   FROM
       (SELECT unnest(array_distinct(['A', 'A', 'B', NULL, NULL])) AS i) sub(i);

[manticore-projects]

Thank you again for your feedback and time. Your explanations are sound and clear and the list_sort works of course. Furthermore I can amend my Unit Tests and compare Arrays by elements, instead by string representation.

However, from a Java Developpers perspective I still would like to recommend a big exclamation mark on the functions which are non-deterministic. "Shuffled" and "Shuffled differently depending on the OS" are still not the same (at least for Java/Python/R guys). Also, I don't know of any other RDBMS having a similar behavior, so good expectation management may help.

All the best and thanks again, we can close this issue.