bug: Table.select(col=<literal>).order_by("col") references the value, not column name, in SQL

[NickCrews]

What happened?

import ibis

t = ibis.memtable({"a": [1, 2, 3]})
s = t.select("a", i=9, s=ibis.literal("foo"))
o = s.order_by("a", "i", "s")
ibis.to_sql(o)

creates

SELECT
  "t0"."a",
  9 AS "i",
  'foo' AS "s"
FROM "ibis_pandas_memtable_ihuncvko6jcbjbtnxm2cdvekru" AS "t0"
ORDER BY
  "t0"."a" ASC,
  9 ASC,
  'foo' ASC

I would expect

SELECT
  "t0"."a",
  9 AS "i",
  'foo' AS "s"
FROM "ibis_pandas_memtable_ihuncvko6jcbjbtnxm2cdvekru" AS "t0"
ORDER BY
  "t0"."a" ASC,
  "t0"."i" ASC,
  "t0"."s" ASC

What version of ibis are you using?

main

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

No response

Relevant log output

No response

Code of Conduct

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

[gforsyth]

that's a weird one. thanks for reporting @NickCrews !

[cpcloud]

@NickCrews Do you have an example where this deferencing produces an incorrect computation?

As you've currently reported it I don't see a correctness issue here.

[NickCrews]

If you run that SQL in duckdb it errors

[cpcloud]

Ah, right the 9 doesn't mean the same thing as the literal number 9 when used in an ORDER BY.

[kszucs]

The selections should be replaced in the sort keys to properly reference the projected values in the order by clause. While #10475 solves this by implementing an optimization, a more robust solution would be to reference the projected values whenever we can.

[cpcloud]

The selections should be replaced in the sort keys to properly reference the projected values in the order by clause. While #10475 solves this by implementing an optimization, a more robust solution would be to reference the projected values whenever we can.

The problem with that solution is that not all of our backends support referencing projected columns in the ORDER BY, some require repeating the expression.