Group By failed with date_trunc

[rcourivaud]

I don't really understand but this query runs well with pg8000 driver 1.30.3 but fails with the new version

db.query(
Invoice.energy,
func.date_trunc("month", Invoice.date).label("month"),
func.sum(Invoice.consumption).label("consumption"),
)

query = query.group_by(Invoice.energy, func.date_trunc("month", Invoice.date))

[tlocke]

Hi @rcourivaud thanks for your bug report. I think it would be easier to fix the bug if you could provide a Minimal Reproducible Example.

[rcourivaud]

Finally, after few tests, the error is also on the older versions. But it works fine with other postgres drivers.

For the example :

def local_connector():
    POSTGRES_USER = os.environ.get("POSTGRES_USER")
    POSTGRES_PASSWORD = os.environ.get("POSTGRES_PASSWORD")
    POSTGRES_DB = os.environ.get("POSTGRES_DB")
    POSTGRES_HOST = os.environ.get("POSTGRES_HOST")
    POSTGRES_PORT = os.environ.get("POSTGRES_PORT", "5432")

    SQLALCHEMY_DATABASE_URL = f"postgresql+pg8000://{POSTGRES_USER}:{POSTGRES_PASSWORD}@{POSTGRES_HOST}:{POSTGRES_PORT}/{POSTGRES_DB}"
    print(SQLALCHEMY_DATABASE_URL)

    engine = create_engine(SQLALCHEMY_DATABASE_URL)
    return engine
    

engine = local_connector()
SessionLocal = sessionmaker(autocommit=False, autoflush=True, bind=engine)

    query = (
        db.query(
            Invoice.energy,
            func.date_trunc("month", Invoice.date).label("month"),
            func.sum(Invoice.consumption).label("consumption"),
        )
    )
    query = query.group_by(Invoice.energy, func.date_trunc("month", Invoice.date))


    query.all()
     result_dict = defaultdict(list)    

You can test with this file
invoices - Feuille 1.csv

[tlocke]

Hi @rcourivaud, that's closer to a Minimal Reproducible Example, but not quite there yet. For instance a MinRep should be all in one file. Also, I can't see any code to set up the table? The idea is to produce a script that anyone can run that reproduces the problem.