Bug: order of results is not guaranteed when query contains distinct and order by (possibly in conjunction with unwind)

[marcosbodio]

Kùzu version

v0.5.0

What operating system are you using?

Mac OSX Sequoia 15.0 on Apple M1 Max chip

What happened?

The following cypher query

match (n:Drug) unwind n.calculated_property_SMILES_imagemol as p return distinct p order by p;

returns results ordered in a different way if executed multiple times.

Examples of two executions of the query:

kuzu> match (n:Drug) unwind n.calculated_property_SMILES_imagemol as p return distinct p order by p;
┌───────────────────────────────────────────────────────────────────┐
│ p                                                                 │
│ STRING                                                            │
├───────────────────────────────────────────────────────────────────┤
│ C1C(Cc2ccccc12)Nc1nc(Nc2cccnc2)nc(n1)N1CCNCC1                     │
│ C1C(Cc2ccccc12)Nc1nc(Nc2ccncc2)cc(n1)N1CCNCC1                     │
│ C1C(Cc2ccccc12)Nc1nc(Nc2ccncc2)nc(n1)N1CCCCC1                     │
│ C1C(Cc2ccccc12)Nc1nc(Nc2ccncc2)nc(n1)N1CCCNCC1                    │
│ C1C(Cc2ccccc12)Nc1nc(Nc2ccncc2)nc(n1)N1CCNCC1                     │
│ C1C(Cc2ccccc12)Nc1nc2ccccc2[nH]1                                  │
│ C1C(Cc2ccccc12)Nc1ncc(cn1)-c1cn(cn1)C(c1ccccc1)(c1ccccc1)c1ccccc1 │
│ C1C(Cc2ccccc12)Nc1nccc2ccccc12                                    │
│ C1C(Cc2ccccc12)Nc1ncnc(n1)N1CCCCC1                                │
│ C1C(Cc2ccccc12)Nc1ncnc2c1nn1ccccc21                               │
│                                ·                                  │
│                                ·                                  │
│                                ·                                  │
│ Oc1ccc2[nH]c3cc(I)c4C(=O)NC(=O)c4c3c2c1                           │
│ Oc1ccc2[nH]c3cc(c4C(=O)NC(=O)c4c3c2c1)-c1c(Br)cccc1Br             │
│ Oc1ccc2[nH]c3cc(c4C(=O)NC(=O)c4c3c2c1)-c1c(Cl)cc(O)cc1Cl          │
│ Oc1ccc2[nH]c3cc(c4C(=O)NC(=O)c4c3c2c1)-c1c(Cl)ccc(O)c1Cl          │
│ Oc1ccc2[nH]c3cc(c4C(=O)NC(=O)c4c3c2c1)-c1c(Cl)cccc1Cl             │
│ Oc1ccc2[nH]c3cc(c4C(=O)NC(=O)c4c3c2c1)-c1c(O)cccc1Cl              │
│ Oc1ccc2[nH]c3cc(c4C(=O)NC(=O)c4c3c2c1)-c1cc(Cl)ccc1Cl             │
│ Oc1ccc2[nH]c3cc(c4C(=O)NC(=O)c4c3c2c1)-c1ccc(Cl)cc1               │
│ Oc1ccc2[nH]c3cc(c4C(=O)NC(=O)c4c3c2c1)-c1ccc(F)cc1                │
│ Oc1ccc2[nH]c3cc(c4C(=O)NC(=O)c4c3c2c1)-c1ccc(cc1)-c1ccccc1        │
└───────────────────────────────────────────────────────────────────┘
(984677 tuples, 20 shown)
(1 column)
Time: 0.36ms (compiling), 959.23ms (executing)
kuzu> match (n:Drug) unwind n.calculated_property_SMILES_imagemol as p return distinct p order by p;
┌─────────────────────────────────────────────────────────────────────────────────┐
│ p                                                                               │
│ STRING                                                                          │
├─────────────────────────────────────────────────────────────────────────────────┤
│ CC(=O)NCC1CCCN(C1)C1CCN(Cc2ccc(Cl)c(Cl)c2)CC1                                   │
│ CC(=O)NCC1CCCN1c1ccc(Nc2cc(ncc2C(=O)NC[C@@H](F)C(C)(C)O)-c2ccc3cc(cnn23)C#N)cn1 │
│ CC(=O)NCC1CCCN1c1ncc(cc1C#N)-c1ccnc(Nc2ccc(CN3CCOCC3)nc2)n1                     │
│ CC(=O)NCC1CCCc2c1c1cc(C)ccc1n2C                                                 │
│ CC(=O)NCC1CCCc2c1c1cc(Cl)ccc1n2C                                                │
│ CC(=O)NCC1CCCc2c1c1cc(OC(F)(F)F)ccc1n2C                                         │
│ CC(=O)NCC1CCCc2c1c1ccccc1n2C                                                    │
│ CC(=O)NCC1CCN(CC1)c1nccc(n1)-c1ccc2ccccc2c1                                     │
│ CC(=O)NCC1CCN(CC1)c1ncnc2cc(sc12)C(N)=O                                         │
│ CC(=O)NCC1CCc2c1c1ccccc1n2C                                                     │
│                                       ·                                         │
│                                       ·                                         │
│                                       ·                                         │
│ Oc1cccc(c1)-c1cncc2ccc(O[C@H]3CCNC3)cc12                                        │
│ Oc1cccc(c1)-c1cncn1Cc1cc2cc(F)ccc2[nH]1                                         │
│ Oc1cccc(c1)-c1csc(NC(=O)C2(CC2)c2ccc3OC(F)(F)Oc3c2)n1                           │
│ Oc1cccc(c1)-c1csc(NC(=O)C2(CC2)c2ccc3OCOc3c2)n1                                 │
│ Oc1cccc(c1)-c1csc(N\N=C2/CC(=O)c3ccccc23)n1                                     │
│ Oc1cccc(c1)-c1csc(N\N=C\c2ccc3ccc4cccc5ccc2c3c45)n1                             │
│ Oc1cccc(c1)-c1csc(c1)-c1cccc(O)c1                                               │
│ Oc1cccc(c1)-c1csc(n1)-c1cccc(O)c1                                               │
│ Oc1cccc(c1)-c1csc(n1)-c1ncccn1                                                  │
│ Oc1cccc(c1)-c1n[nH]c(NCc2c(F)cccc2Cl)n1                                         │
└─────────────────────────────────────────────────────────────────────────────────┘
(984677 tuples, 20 shown)
(1 column)
Time: 0.42ms (compiling), 965.31ms (executing)

Are there known steps to reproduce?

I can provide an export of a simplified version of the data in my node table, if needed to reproduce the bug.

[ray6080]

hi @marcosbodio , this should've been fixed now on our master branch, you can give a try with our next dev build 0.6.1.dev56.