SELECT 0 from table_name return 1 line instead of expected number_of_lines(table_name) about polars HOT 10 OPEN

In Polars (conceptually speaking) pl.lit(0) is a scalar expression, just like pl.col.x.sum() is. Scalar expressions are returned as a new row by themselves if all columns are scalar, and are broadcasted to the other columns' lengths otherwise.

So in these two examples pl.lit(0) and pl.col.x.sum() are returned as a new row:

>>> df = pl.DataFrame({"x": []}, schema={"x": pl.Int32})
>>> df.select(pl.lit(0))
shape: (1, 1)
┌─────────┐
│ literal │
│ ---     │
│ i32     │
╞═════════╡
│ 0       │
└─────────┘
>>> df.select(s=pl.col.x.sum())
shape: (1, 1)
┌─────┐
│ s   │
│ --- │
│ i32 │
╞═════╡
│ 0   │
└─────┘

In these four examples the scalar values are broadcasted along with the other column, x, which in this case means the result has zero rows when x is empty:

>>> df = pl.DataFrame({"x": [1, 2]}, schema={"x": pl.Int32})
>>> df.select(pl.col.x, pl.lit(0))
shape: (2, 2)
┌─────┬─────────┐
│ x   ┆ literal │
│ --- ┆ ---     │
│ i32 ┆ i32     │
╞═════╪═════════╡
│ 1   ┆ 0       │
│ 2   ┆ 0       │
└─────┴─────────┘
>>> df.select(pl.col.x, s=pl.col.x.sum())
shape: (2, 2)
┌─────┬─────┐
│ x   ┆ s   │
│ --- ┆ --- │
│ i32 ┆ i32 │
╞═════╪═════╡
│ 1   ┆ 3   │
│ 2   ┆ 3   │
└─────┴─────┘

>>> df = pl.DataFrame({"x": []}, schema={"x": pl.Int32})
>>> df.select(pl.col.x, pl.lit(0))
shape: (0, 2)
┌─────┬─────────┐
│ x   ┆ literal │
│ --- ┆ ---     │
│ i32 ┆ i32     │
╞═════╪═════════╡
└─────┴─────────┘
>>> df.select(pl.col.x, s=pl.col.x.sum())
shape: (0, 2)
┌─────┬─────┐
│ x   ┆ s   │
│ --- ┆ --- │
│ i32 ┆ i32 │
╞═════╪═════╡
└─────┴─────┘

This is why Polars has different behavior when you use with_columns †.

As for the original issue, I don't know exactly how to match SQL's behavior here without a lot of work on our end...

† I'm still not 100% sure what we should do in the with_columns case if we end up overwriting every original column in the with_columns expression. You could imagine that there's always a hidden null column for with_columns to broadcast along, which is what the current engine does:

>>> df = pl.DataFrame({"x": []}, schema={"x": pl.Int32})
>>> df.with_columns(x=0)
shape: (0, 1)
┌─────┐
│ x   │
│ --- │
│ i32 │
╞═════╡
└─────┘

This might however be problematic for the new streaming engine, so the jury isn't out on this one yet.

from polars.

Also, is it possible to create expression that means "0 distributed for each row even if nothing else is selected"?

Kind of. You'll have to select a column to broadcast with, then drop that column after the broadcast:

>>> df = pl.DataFrame({"x": [1, 2, 3]})
>>> df.select(pl.first(), pl.lit(0)).drop(pl.first())
shape: (3, 1)
┌─────────┐
│ literal │
│ ---     │
│ i32     │
╞═════════╡
│ 0       │
│ 0       │
│ 0       │
└─────────┘

But I don't believe you can literally do it as a stand-alone expression.

EDIT: you can actually make such an expression by broadcasting by abusing pl.when:

>>> df.select(pl.when(True).then(0).otherwise(pl.first()))
shape: (3, 1)
┌─────────┐
│ literal │
│ ---     │
│ i64     │
╞═════════╡
│ 0       │
│ 0       │
│ 0       │
└─────────┘

Still relies on broadcasting with an existing column but that column never has to show up in your result.

from polars.

The same applies to python API

I expected L.select(pl.lit(0)) to return 2 lines, not 1 as it's returned.
But that's harder to compare with other DBMSes

from polars.

@ritchie46 @orlp can a mod delete/ban the malware link above? Edit: I've reported it as malware abuse, not sure how quickly they act.

@AlexeyDmitriev for the python API, 2 lines is intended I believe. See the discussion in #17107.

from polars.

Actually I saw that issue, but didn't quite get what is expected and what is not

from polars.

So--the below is in reference to with_columns, which apparently has different behavior than select.

pl.lit does not have super obvious intended behavior, but in essence it is supposed to mean "a literal value that fills the height of the columns in the DataFrame." This has the confusion that if no columns are present, a height of 1 is returned, but it is not inconsistent.

In your example, the frame has height 2, so selecting a literal value be also length 2. In the example in the linked issue, an empty frame has height 0, so selecting a literal from there will be length zero. This is consistent with the definition:

import polar as pl

# In an empty frame, literal returns length 1
pl.DataFrame().with_columns(pl.lit(1))
shape: (1, 1)
# ┌─────────┐
# │ literal │
# │ ---     │
# │ i32     │
# ╞═════════╡
# │ 1       │
# └─────────┘

# As long as we have any columns, literal returns that length
# In this case, that length is 0.
pl.DataFrame({"a": []}).with_columns(pl.lit(1))
shape: (0, 2)
# ┌──────┬─────────┐
# │ a    ┆ literal │
# │ ---  ┆ ---     │
# │ null ┆ i32     │
# ╞══════╪═════════╡
# └──────┴─────────┘

Now, with select, we do indeed get a literal of length 1, even if our columns are length 0:

pl.DataFrame({"a": []}).select(pl.lit(1))
shape: (1, 1)
┌─────────┐
│ literal │
│ ---     │
│ i32     │
╞═════════╡
│ 1       │
└─────────┘

I am not sure what to think about that.

from polars.

Thanks for the clarification @orlp, makes perfect sense.

from polars.

@orlp thanks for explanation, I can see now that this behaviour make sense (in particular you allow selecting aggregates and non-aggregates with simpler syntax then in classic sql as in your df.select(pl.col.x, s=pl.col.x.sum()) example)

Personally, I'd find more intuitive if there were to separate functions to select something for each line of the df (accept both, always broadcasted) and select an expression (accepts only scalars, doesn't)

• This way it's easier to understand the shape of what is returned when you read the code (even if you don't know the specific things user passed)

def f(expr):
    ...
    some_df.select(expr) # you know this is always the same shape as some_df, even if user passed literal
    ...

• You can always remove unnecessary column safely, e.g. df.select(x=x, y=y).select('y') is the same as df.select(y=y)

But I guess, that wouldn't work with the semantics of functions like Expr.top_k (because it's neither of my options)
And also, that would be completely changing the API people got used to

Anyway, it would be good if this was explained in some obvious place in the docs, because when I tried to see explanation I failed.

Also, is it possible to create expression that means "0 distributed for each row even if nothing else is selected"?

As for the original issue, I don't know exactly how to match SQL's behavior here without a lot of work on our end...
For me personally I only care for python API and showed sql behaviour only because with sql there's a system to compare to

BTW the fact that pl.lit is scalar also does something strange with joins

from polars.

BTW the fact that pl.lit is scalar also does something strange with joins

@AlexeyDmitriev you mean this? #9603

from polars.

@AlexeyDmitriev you mean this? #9603

I added somewhat simpler example there.

from polars.