Error when trying to parse multiple rows via Relational API `values`

[JoeRimsky]

What happens?

When trying to utilize the values() function for converting a native Python object into a relation, I am unable to create a relation with multiple rows and columns.

To Reproduce

import duckdb

conn = duckdb.connect()

# 1) Base `VALUES` query using straight SQL (correct/expected)
conn.execute("FROM (VALUES (1, 'a'), (2, 'b'))").df()

# 2) 1 row, multiple columns relation (correct/expected)
conn.values([1, 'a']).df()

# 3) 2 rows, multiple columns relation (multiple different attempts - same output below)
conn.values([1, 'a'], [2, 'b']).df()
conn.values([((1), ('a')), ((2), ('b'))]).df()
conn.values(([1, 'a']), ([2, 'b'])).df()
conn.values([[1, 'a'], [2, 'b']]).df()

# 4) 2 rows, multiple columns relation (works, but typed as lists)
conn.values(([1], ['a']), ([2], ['b'])).df()

# 5) Attempted to remove the lists 🤷🏼
conn.values((1, 'a'), (2, 'b'))

Outputs:

# 1)
   col0 col1
0      1      a
1      2      b

# 2)
   col0 col1
0      1      a

# 3)
     col0    col1
0  [1, a]  [2, b]

# 4)
    [1]  ['a']
0  [1]    [a]
1   [2]   [b]

# 5)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
duckdb.duckdb.BinderException: Binder Error: Referenced column "a" not found in FROM clause!

OS:

OSX 15.6

DuckDB Package Version:

1.4.0

Python Version:

3.11

Full Name:

Joe Rimsky

Affiliation:

Gecko Robotics

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[JoeRimsky]

If this is in fact user error, please let me know 😅

[evertlammerts]

This seems like a bug:

>>> duckdb.values((1, 3), (2, 4)).df()
   1  3
0  1  3
1  2  4
>>> duckdb.values((1, 3, 'a', 5), (2, 4, 'b', 6)).df()
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
_duckdb.BinderException: Binder Error: Referenced column "a" not found in FROM clause!

values() is a bit of a neglected function I'm afraid.

[Tishj]

Er, I think what values does is turn the inputs into expressions, and a str to expression implicitly becomes a column reference.

You could work around this by explicitly turning the str into a Constant expression
duckdb.ConstantExpression('a')