https://www.getdaft.io logo
Join Slack
Powered by
I'm asking because this would be pretty powerful b...
# daft-dev
k
I'm asking because this would be pretty powerful but I've come across several additional considerations and complexities that implementing it would entail. Happy to sit down sometime and talk through them
r
That would be super cool. Do you know how PRQL does subqueries? SQL only has four forms (as far as I know..) which might limit the complexity? • scalar-value / row-value subquery • comparison subquery • test subquery (exists, unique) • in subquery I wonder if these problems are easier for daft-sql because there's an additional IR whereas dataframes build logical plans so there's no current ways to deal with name resolution in general. I was curious to see how postgres plans this.
Copy code
postgres=# CREATE SCHEMA example;
postgres=# SET search_path TO example;
SET
postgres=# CREATE TABLE df1 ( key text, value INT );
CREATE TABLE
postgres=# CREATE TABLE df2 ( key text, value INT );
CREATE TABLE
postgres=# INSERT INTO df1 VALUES ('A',1),('A',2),('B',1);
INSERT 0 3
postgres=# INSERT INTO df2 VALUES ('A',1),('A',1),('B',1);
INSERT 0 3
I switched the query to join on keys and compare on value.
Copy code
postgres=# SELECT * FROM df1 WHERE value > (SELECT max(value) FROM df2 WHERE df1.key = df2.key);
 key | value
-----+-------
 A   |     2
(1 row)

postgres=# EXPLAIN SELECT * FROM df1 WHERE value > (SELECT max(value) FROM df2 WHERE df1.key = df2.key);
                                QUERY PLAN
--------------------------------------------------------------------------
 Seq Scan on example.df1  (cost=0.00..32918.88 rows=423 width=36)
   Output: df1.key, df1.value
   Filter: (df1.value > (SubPlan 1))
   SubPlan 1
     ->  Aggregate  (cost=25.89..25.90 rows=1 width=4)
           Output: max(df2.value)
           ->  Seq Scan on example.df2  (cost=0.00..25.88 rows=6 width=4)
                 Output: df2.key, df2.value
                 Filter: (df1.key = df2.key)
I think this is the same as the join problem we were discussing earlier, but I might be being naive here. My approach here would be to associate dataframe ids with columns whenever possible (like sql table-qualified), otherwise leave as unqualified and run a binding pass to resolve names. DataFrame scopes should be more sane than SQL scopes.
k
Yeah I'm only thinking about the four types in SQL at this moment. Join and subquery execution-wise I have a solid plan, this is more related to planning. The dataframe subquery case is slightly different from dataframe joins or SQL subqueries because the input plan lacks the full context to resolve all of the table references. For a join, the left and right plans are also given to the builder, and in SQL, subquery plans can be translated with awareness of the outer plan. However, because we are using Python to define our dataframe DSL, we will have to first build 
subquery
first before building the full query. The issue with this is we don't currently have a mechanism for retrieving the logical plan for 
df1
when building 
subquery
, which means we can't get things like the datatype of 
df1["value"]
during build-time.
A possible solution I have is to either bind the plan or the relevant info such as dtype to the column expression when doing something like 
df1["value"]
. The thing is we also have these anonymous 
col("x")
expressions we can also create, which we cannot resolve without the input plan. So essentially we will need to bind tables to columns in multiple places: 1. when getting a column directly from a dataframe like in 
df["x"]
2. when planning SQL identifiers 
tbl.x
3. in the builder, for anonymous Python DataFrame columns 
col("x")
Another solution would be to create the concept of an unresolved logical plan, which does not contain schema/datatype information, and resolve it when we have the full plan to execute. However this would prevent us from doing certain type checking until a .collect() which would not be great for UX.
r
It could also be interesting for the logical builder to have a reference to the global python variables which you could use to lookup all in-scope names of type DataFrame. Rather than managing scopes ourselves like in SQLPlanner, we could leverage the existing python scope. We could use it to resolve anonymous columns since we’ll have access to the full scope
k
Could you elaborate on resolving anonymous columns? An anonymous column (at least with my definition of it) only makes sense to be resolved to the plan that it's used in, so there would be no need to get other plans from the current scope.
Do you mean like if you do 
col("df.x")
?
r
Like doing col(x) from a subquery in which the current logical plan has no x, but the outer scope does. In SQL you don’t have to qualify if the column name is unambiguous. We definitely don’t have to support that from dataframes though.
k
Hm that’s true too. More to think about…
r
Maybe we can have a call tomorrow and talk about the various cases.
k
yeah good idea
I don’t think I would want to allow for a col(“x”) to refer to an outer column actually. If there are multiple dataframes that have a column “x” it would be ambiguous during subquery planning, even if there is only one dataframe used as the outer table. It should be doable, but the behavior is just is not as clear as it is in sql
7 Views
Open in Slack
PreviousNext
Powered by