bug: first returns different values across backends

[Hoxbro]

What happened?

I would expect first (or last) of a table column to return a singular value and not a pd.Series of the same value.

import os
import sqlite3

import ibis
import pandas as pd


def create_sqlite(df, name):
    filename = "tmp.sqlite"
    os.remove(filename)
    con = sqlite3.Connection(filename)
    df.to_sql(name, con, index=False)
    return ibis.sqlite.connect(filename).table(name)


df = pd.DataFrame(
    {"string": ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]},
)
sqlite_table = create_sqlite(df, "table")
pandas_table = ibis.pandas.connect({"df": df}).table("df")

pandas_table["string"], sqlite_table["string"]
pandas_table["string"].first().execute()
sqlite_table["string"].first().execute()

What version of ibis are you using?

3.2.0

What backend(s) are you using, if any?

Pandas and Sqlite

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

Hi @Hoxbro, thanks for the issue!

The first and last APIs are based on the SQL window functions FIRST_VALUE and LAST_VALUE, which produce columns.

It sounds like you want first to handle both cases (single value in an aggregate setting, and window behavior in a window setting).

We have #4149 for that, so I'm going to close this out!

Thanks again for reporting!

[cpcloud]

@jcrist Pointed out to me that this is a slightly different issue: the backend behaviors don't match here. I think we can address this issue without addressing #4149, so I'll reopen this one.

[mesejo]

Hey, I want to take this for a spin. Could you clarify which of the outputs is the correct one? I think it should be the pandas one, but not sure. Thanks!

[cpcloud]

@mesejo Great!

The SQLite behavior is the correct one, because first currently maps to SQL's first_value window function.