Incorrect query output from group by query (regression in 0.9.0)

[segasai]

What happens?

Hi,
I have a query where I group by an integer column and I was expecting that I would only get unique values, however recent versions of duckdb (0.9.0+) return duplicates.

Query:

with a as (select label,                                                        
   avg(x) as xx,                                                                
   avg(y) as yy                                                                 
from tab group by label)                                                        
select * from a 

Full reproduction is given below.
The issue does not happen with duckdb 0.8.1

Thanks !

To Reproduce

Here is the code

It requires a data-file ( https://gist.github.com/segasai/36a73d6f3b140e513e1adfc5d05f2c83 )

import duckdb
import numpy as np
import pandas as pd

pdf = pd.read_csv('aa.csv')
conn1 = duckdb.connect(':memory:')
conn1.register('tab', pdf)
R = conn1.execute('''                                                       
with a as (select label,
   avg(x) as xx,
   avg(y) as yy
from tab group by label)                            
select * from a 
                           
''').fetchnumpy()
print(len(R['label']), len(np.unique(R['label'])))

The code should print the length of the result and the number of unique values for the column used as a key. They must be equal but they are not.

For clarity the underlying query is this

with a as (select label,
   avg(x) as xx,
   avg(y) as yy
from tab group by label)                            
select * from a 

OS:

Linux

DuckDB Version:

0.9.1

DuckDB Client:

Python

Full Name:

Sergey Koposov

Affiliation:

University of Edinburgh

Have you tried this on the latest main branch?

I have tested with a main build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have

[szarnyasg]

Thanks, I could reproduce the issue

[lnkuiper]

PR is up #9411

[segasai]

Thank you for the fix!