Multiple joins of same table

[rgreminger]

I think I found another edge case. Below are two contrived examples for cases where I first want to extract some data/variable from dt1 and merge it into dt2. Then I want to do something with that variable in dt2 (e.g., define another variable) and merge the result back to the original main table dt1.

using DataFrames
using TidierData
import TidierDB as DB

df1 = DataFrame(id = [string('A' + i ÷ 26, 'A' + i % 26) for i in 0:9], 
                          value0 = [i % 2 == 0 ? "aa" : "bb" for i in 1:10], 
                          value1 = repeat(1:5, 2)) 

df2 = DataFrame(id = df1.id, value2 = rand(10))

db = DB.connect(DB.duckdb())

DB.copy_to(db, df1, "t1");
DB.copy_to(db, df2, "t2");

dt1 = @chain DB.dt(db, "t1") begin
    DB.@group_by(value0)
    DB.@summarize(id = first(id), value1 = sum(value1))
end

dt2 = @chain DB.dt(db, "t2") begin
    DB.@inner_join(DB.t(dt1), id == id)
    DB.@distinct() 
end

df_merged = @chain DB.dt(db, "t1") begin
    DB.@select(id, value0) 
    DB.@inner_join(dt2, id == id)
    @aside DB.@show_query _
    DB.@collect()
end

WITH j1j1cte_1 AS (
SELECT value0, first(id) AS id, SUM(value1) AS value1
        FROM  t1
        GROUP BY value0),
j1cte_1 AS (
SELECT  DISTINCT value2, value0, value1
        FROM t2
        INNER JOIN j1cte_1 ON t2.id = j1cte_1.id),
j1cte_2 AS (
SELECT *
        FROM  j1cte_1),
cte_1 AS (
SELECT  COALESCE(t1.id, j1cte_2.id) AS id, t1.value0, j1cte_2.value2, j1cte_2.value0, j1cte_2.value1
        FROM t1
        INNER JOIN j1cte_2 ON t1.id = j1cte_2.id)  
SELECT *
        FROM cte_1
ERROR: Binder Error: There is a WITH item named "j1cte_1", but it cannot be referenced from this part of the query.
Stacktrace:
....

df1 = DataFrame(id = [string('A' + i ÷ 26, 'A' + i % 26) for i in 0:9], 
                          value0 = [i % 2 == 0 ? "aa" : "bb" for i in 1:10], 
                          value1 = repeat(1:5, 2)) 

df2 = DataFrame(id = df1.id, value2 = rand(10))

db = DB.connect(DB.duckdb())

DB.copy_to(db, df1, "t1");
DB.copy_to(db, df2, "t2");

dt1 = @chain DB.dt(db, "t1") begin
    DB.@distinct() 
end

dt2 = @chain DB.dt(db, "t2") begin
    DB.@inner_join(DB.t(dt1), id)
end

df_merged = @chain DB.dt(db, "t1") begin
    DB.@inner_join(DB.t(dt2), id == id)
    @aside DB.@show_query _
    DB.@collect()
end

WITH j1j1cte_1 AS (
SELECT  DISTINCT id, value0, value1
        FROM t1),
j1j1cte_2 AS (
SELECT *
        FROM  j1cte_1),
j1cte_1 AS (
SELECT COALESCE(t2.id, j1cte_2.id) AS id, t2.value2, j1cte_2.value0, j1cte_2.value1
        FROM  j1cte_0)  
SELECT COALESCE(t1.id, j1cte_1.id) AS id, t1.value0, t1.value1, j1cte_1.value2, j1cte_1.value0, j1cte_1.value1
        FROM t1
        INNER JOIN j1cte_1 ON t1.id = j1cte_1.id
ERROR: Catalog Error: Table with name j1cte_0 does not exist!
Did you mean "sqlite_master"?

LINE 1: ....id) AS id, t2.value2, j1cte_2.value0, j1cte_2.value1 FROM  j1cte_0)  SELECT COALESCE(t1.id, j1cte_1.id) AS id, t1.value0...
                                                                       ^
Stacktrace:
....

[rgreminger]

Note, this is on the branch solving #143 (thanks a lot for already fixing that!)

[drizk1]

I am so curious how you come up with these edge cases, but I do appreicate it.

I tried this and the new one on the main branch as well, and it seems to happen there as well, so I'll figure out a fix

[rgreminger]

As always, really appreciate how fast you're looking into these!
I discover these cases because I decided to use TidierDB for a project that requires quite a bit of data wrangling and combines multiple queries and tables. The tricky part is usually to come up with a simple example that reproduces the behavior I see in the larger queries :)

[drizk1]

Ok makes sense. Thank you for choosing TidierDB. I actually shouted out you in a talk I gave on TidierDB recently for all these case you've found.

As I work on these two issues (and others you will likely find), in the interim, you may find some luck working around the errors by creating views of some of the queries you are joining together

This issue is around
j1j1cte_2 AS ( SELECT * FROM j1cte_1
j1cte_1 should be j1j1cte_1 which should be fixable pretty easily

[drizk1]

Brief update:
I fixed an issue with @distinct in the same branch as mentioned above.

So now, for dt2, if you create a view instead, the query will run. value0 gets dupliacted in this query just by nature of the joins i think.

I am still trying to figure out how to get that j1cte_ to correctly match.

julia> @chain DB.dt(db, "t2") begin
           DB.@inner_join(DB.t(dt1), id)
           DB.@create_view(test)
       end
(Count = Int64[],)

julia> df_merged = @chain DB.dt(db, "t1") begin
           DB.@inner_join(DB.dt(db, "test"), id == id)
           @aside DB.@show_query _
           DB.@collect()
       end

SELECT COALESCE(t1.id, test.id) AS id, t1.value0, t1.value1, test.value2, test.value0, test.value1
        FROM t1
        INNER JOIN test ON t1.id = test.id
10×6 DataFrame
 Row │ id      value0  value1  value2     value0_1  value1_1 
     │ String  String  Int64   Float64    String    Int64    
─────┼───────────────────────────────────────────────────────
   1 │ AA      bb           1  0.518642   bb               1
   2 │ AB      aa           2  0.254371   aa               2
   3 │ AC      bb           3  0.178181   bb               3
   4 │ AD      aa           4  0.0390392  aa               4
   5 │ AE      bb           5  0.566446   bb               5
   6 │ AF      aa           1  0.244013   aa               1
   7 │ AG      bb           2  0.878291   bb               2
   8 │ AH      aa           3  0.151751   aa               3
   9 │ AI      bb           4  0.870855   bb               4
  10 │ AJ      aa           5  0.449933   aa               5

[drizk1]

This one should work now as well on branch w ongoing fixes. This branch still works on this query , which I use as my gold standard for testing queries with multiple joins

as a side note, you no longer have to prefrace queries with DB.t() inside of a join

dt1 = @chain DB.dt(db, "t1") begin
    DB.@distinct() 
end

dt2 = @chain DB.dt(db, "t2") begin
    DB.@inner_join(dt1, id)
end

df_merged = @chain DB.dt(db, "t1") begin
           DB.@inner_join(dt2, id == id)
           @aside DB.@show_query _
           DB.@collect()
       end
WITH j1j1cte_1 AS (
SELECT  DISTINCT id, value0, value1
        FROM t1),
j1j1cte_2 AS (
SELECT *
        FROM  j1j1cte_1),
j1cte_1 AS (
SELECT COALESCE(t2.id, j1j1cte_2.id) AS id, t2.value2, j1j1cte_2.value0, j1j1cte_2.value1
        FROM  t2
        INNER JOIN j1j1cte_2 ON t2.id = j1j1cte_2.id)  
SELECT COALESCE(t1.id, j1cte_1.id) AS id, t1.value0, t1.value1, j1cte_1.value2, j1cte_1.value0, j1cte_1.value1
        FROM t1
        INNER JOIN j1cte_1 ON t1.id = j1cte_1.id
10×6 DataFrame
 Row │ id      value0  value1  value2     value0_1  value1_1 
     │ String  String  Int64   Float64    String    Int64    
─────┼───────────────────────────────────────────────────────
   1 │ AA      bb           1  0.338878   bb               1
   2 │ AB      aa           2  0.703834   aa               2
   3 │ AC      bb           3  0.453351   bb               3
   4 │ AD      aa           4  0.337659   aa               4
   5 │ AE      bb           5  0.137974   bb               5
   6 │ AF      aa           1  0.172792   aa               1
   7 │ AG      bb           2  0.988683   bb               2
   8 │ AH      aa           3  0.52477    aa               3
   9 │ AI      bb           4  0.754892   bb               4
  10 │ AJ      aa           5  0.0696808  aa               5

Leveraging Views

While the above does work, I'm still a bit biased towards using views (assuming youre allowed to on your database) since it can keep the queries a bit cleaner to trouble shoot - the table or view name just has to be string

@chain DB.dt(db, "t1") begin
    DB.@distinct() 
    DB.@create_view dt1 true # true arg here will replace existing view w that name
end

@chain DB.dt(db, "t2") begin
    DB.@inner_join("dt1", id)
    DB.@create_view dt2 true
end

@chain DB.dt(db, "t1") begin
        DB.@inner_join("dt2", id == id)
        @aside DB.@show_query _
        DB.@collect()
    end
@chain DB.dt(db, "t1") begin
               DB.@inner_join("dt2", id == id)
               @aside DB.@show_query _
               DB.@collect()
           end
SELECT COALESCE(t1.id, dt2.id) AS id, t1.value0, t1.value1, dt2.value2, dt2.value0, dt2.value1
        FROM t1
        INNER JOIN dt2 ON t1.id = dt2.id
10×6 DataFrame
 Row │ id      value0  value1  value2     value0_1  value1_1 
     │ String  String  Int64   Float64    String    Int64    
─────┼───────────────────────────────────────────────────────
   1 │ AA      bb           1  0.338878   bb               1
   2 │ AB      aa           2  0.703834   aa               2
   3 │ AC      bb           3  0.453351   bb               3
   4 │ AD      aa           4  0.337659   aa               4
   5 │ AE      bb           5  0.137974   bb               5
   6 │ AF      aa           1  0.172792   aa               1
   7 │ AG      bb           2  0.988683   bb               2
   8 │ AH      aa           3  0.52477    aa               3
   9 │ AI      bb           4  0.754892   bb               4
  10 │ AJ      aa           5  0.0696808  aa               5

comparison with TidierData

Wanted to make sure its the same - but I had to change names because. Rename in TidierDB is still a little buggy, but I will address that later.

dtd1 = @chain df1 begin
           @rename value00 = value0
           @distinct() 
       end
10×3 DataFrame
 Row │ id      value00  value1 
     │ String  String   Int64  
─────┼─────────────────────────
   1 │ AA      bb            1
   2 │ AB      aa            2
   3 │ AC      bb            3
   4 │ AD      aa            4
   5 │ AE      bb            5
   6 │ AF      aa            1
   7 │ AG      bb            2
   8 │ AH      aa            3
   9 │ AI      bb            4
  10 │ AJ      aa            5

dtd2 = @chain df2 begin
           @inner_join(dtd1, id)
           @rename value11 = value1 #!value0
       end
10×4 DataFrame
 Row │ id      value2     value00  value11 
     │ String  Float64    String   Int64   
─────┼─────────────────────────────────────
   1 │ AA      0.338878   bb             1
   2 │ AB      0.703834   aa             2
   3 │ AC      0.453351   bb             3
   4 │ AD      0.337659   aa             4
   5 │ AE      0.137974   bb             5
   6 │ AF      0.172792   aa             1
   7 │ AG      0.988683   bb             2
   8 │ AH      0.52477    aa             3
   9 │ AI      0.754892   bb             4
  10 │ AJ      0.0696808  aa             5

df_mergedd2 = @chain df1 begin    
           @inner_join(dtd2, id = id)
       end
10×6 DataFrame
 Row │ id      value0  value1  value2     value00  value11 
     │ String  String  Int64   Float64    String   Int64   
─────┼─────────────────────────────────────────────────────
   1 │ AA      bb           1  0.338878   bb             1
   2 │ AB      aa           2  0.703834   aa             2
   3 │ AC      bb           3  0.453351   bb             3
   4 │ AD      aa           4  0.337659   aa             4
   5 │ AE      bb           5  0.137974   bb             5
   6 │ AF      aa           1  0.172792   aa             1
   7 │ AG      bb           2  0.988683   bb             2
   8 │ AH      aa           3  0.52477    aa             3
   9 │ AI      bb           4  0.754892   bb             4
  10 │ AJ      aa           5  0.0696808  aa             5

[rgreminger]

Amazing, thank you! This seems to now work on my end. And thank you for the tips with using views and not having to use DB.t anymore (I probably should reread the docs to learn about all the updates you made!). It's funny, I was actually experimenting with views and creating new tables to avoid this bug, which is how I came across #149 .

[drizk1]

Some of the docs have actually not quite caught up and still use older syntax, so I will have to update those as well lol, but hopefully these few issues are resolved. I try to keep the news.md updated but sometimes forget as an easy place to see new changes

I will do a formal update once I add these edgecases as tests over the coming week

[drizk1]

going to release and close this set of issues, lmk if you run into any others / reopen if needed