Design for Joining - Githubissues

This task is automatically imported from the old Task Issue Board and it was originally created by James Dunkerley. Original issue is here.

Core functionality:

Join

can be on multiple fields (SELECT A.*, B,* from A INNER JOIN B on A.a=B.a and A.b=B.b... ==> [Join_Condition Column Column Equal|GreaterThan|...])
- Warn on incompatible type mismatch (String == Integer)
- If equality on floats should raise a warning
Allow for range based joins (SELECT A.*, B,* from A INNER JOIN B on A.a>=B.a and A.a<=B.b... ==> Greater/Less/GT/LT)
Modes: Inner, Left, Right, Full, LeftExclusive, RightExclusive

Cartesian Join : All rows from B on each row of A (warning if B row_count high)

Naming conflict rules

Auto rename either to Left_<existing> or Right_<existing>
- Optional parameter to control this
If renamed follow normal rules

Union

Accept one or more inputs
User specifies order of the appending (pass Vector of Tables)
Map columns based on name or position
- Manual column mapping => Pass a set of names for each column.
Auto type coercion
- Numeric coercion as is obvious
- To Text from most types
- From text is a maybe with the default parsing (i.e. ISO for dates, a plain number)
- warning if not possible

Additional nice to have:

Integrated ability to rename fields (less convinced as think will make API too complicated)
Control over text comparison - case sensitivity

Row Join

Row position join: A Row 1 joined with B Row 1 etc...
Nulls when you run out
Not sure how easy in SQL (would need an ordering on table and then to add Row_Number for join)

Blockers:

#184032869 resolved #183854144 resolved #184072796 resolved #183901439 resolved #184215302 blocked

Comments:

https://learn.microsoft.com/en-us/powerquery-m/table-join https://help.alteryx.com/20221/designer/join-tool https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.join.html (James Dunkerley - Oct 25, 2022)

https://help.alteryx.com/20221/designer/union-tool https://learn.microsoft.com/en-us/powerquery-m/table-combine https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.concat.html (Radosław Waśko - Oct 25, 2022)

Radosław Waśko reports a new STANDUP for yesterday (2022-10-27):

Progress: Created a first draft of the Joins design. It should be finished by 2022-11-04.

Next Day: Next day I will be working on the same task. Address comments on the draft, maybe try implementing some PoC. Look into the ArrayProxy benchmarks, if time suffices start work on Text.match refactor. (Enso Bot - Oct 28, 2022)

Radosław Waśko reports a new STANDUP for yesterday (2022-10-28):

Progress: Fixed the ArrayProxy benchmarks and improved a few ArrayProxy edge cases. Addressed PR comments on Join design, added some details on error handling and discussed a few changes we want to make; adding first details on Union/Concat. It should be finished by 2022-11-04.

Next Day: Next day I will be working on the same task. Continue the Union design, update error handling approach for Joins, create a PoC/prototype for joins to see how the design behaves in practice. Start work on Text.match refactor. (Enso Bot - Oct 28, 2022)

Radosław Waśko reports a new STANDUP for today (2022-10-31):

Progress: Created a basic design for Join/Union/Zip. Added support for While predicate for Table.take/drop thanks to the Row type. It should be finished by 2022-11-04.

Next Day: Next day I will be working on the same task. Get the designs reviewed, create a join PoC prototype and compare two approaches to join conditions. Get the While PR merged. Work on Text.match refactor (Enso Bot - Oct 31, 2022)

Radosław Waśko reports a new STANDUP for today (2022-11-02):

Progress: Addressed comments on Join design, added new methods, described and updated error handling. First work on join prototype. Some movement forward on Text.match refactor. It should be finished by 2022-11-04.

Next Day: Next day I will be working on the same task. Get the next iteration reviewed, try adding some screenshots based on the prototype. Continue work on Text.match if time suffices. (Enso Bot - Nov 2, 2022)

Radosław Waśko reports a new STANDUP for today (2022-11-03):

Progress: Clarified updated design for Text.match refactor, updated the APIs. Need to add some tests before this can be finished. Fruitful discussion with James about aspects of Joins design, updated the design after the discussion. Initial implementation of Join prototype. It should be finished by 2022-11-04.

Next Day: Next day I will be working on the same task. Finish prototype. Try taking some screenshots. Possibly prepare design for further discussion. Add tests for Text.match, possibly making it ready for review. (Enso Bot - Nov 3, 2022)

Radosław Waśko reports a new STANDUP for yesterday (2022-11-04):

Progress: Working on Joins prototype, initial implementation is done, but it still needs some fixes (parts of it work, parts don't). Addressed next iteration of design comments. Reviewed the pretty huge expressions PR. It should be finished by 2022-11-04.

Next Day: Next day I will be working on the same task. Finish prototype, try adding some screenshots to the design, prepare for further discussion. If time suffices, work on Text.match. (Enso Bot - Nov 5, 2022)

Radosław Waśko reports a new 🔴 DELAY for yesterday (2022-11-04):

Summary: There is 10 days delay in implementation of the Design for Joining (#182653480) task. It will cause 0 days delay for the delivery of this weekly plan.

Delay Cause: The initial design sketch is done but we still need to discuss it further which will happen after my timeoff. (Enso Bot - Nov 5, 2022)

Radosław Waśko reports a new 🔴 DELAY for yesterday (2022-11-14):

Summary: There is 4 days delay in implementation of the Design for Joining (#182653480) task. It will cause 0 days delay for the delivery of this weekly plan.

Delay Cause: I'm not sure what is the correct end date to give a design ticket since it's open while implementation is open. I plan to do finishing touches tomorrow, then we scheduled discussion for Wednesday and there will be at least 1 more day needed (possibly more) to address the feedback. (Enso Bot - Nov 14, 2022)

Radosław Waśko reports a new STANDUP for yesterday (2022-11-14):

Progress: Catching up after time off. Small PR fixing a broken pure-Enso benchmark and trying to make stuff better by allowing to run all aggregated benchmarks. Got the Join prototype into a working state in the CLI - will try GUI tomorrow. It should be finished by 2022-11-18.

Next Day: Next day I will be working on the same task. Try adding some screenshots from the prototype to the design, add a note on mixing backends as discussed with James. Initial task split for the implementation. If time allows, move forawrd with Text.match (Enso Bot - Nov 14, 2022)

Radosław Waśko reports a new STANDUP for today (2022-11-15):

Progress: Prepared and discussed an initial task split for the Joins "epic". Added some details to the designs. Added screenshots from the prototype. Progress on Text.match - updating tests and fixing bugs, should be finished soon too. It should be finished by 2022-11-18.

Next Day: Next day I will be working on the same task. Discussion on Joins Design. Prepare Text.match PR for review. (Enso Bot - Nov 15, 2022)

Radosław Waśko reports a new STANDUP for yesterday (2022-11-16):

Progress: The Text.match refactor has been implemented, tested and is ready for review. Discussion on Joins design. It should be finished by 2022-11-18.

Next Day: Next day I will be working on the same task. Update the Joins Design based on notes from the discussions. (Enso Bot - Nov 17, 2022)

Radosław Waśko reports a new STANDUP for today (2022-11-17):

Progress: Updated the Joins design based on the discussions. Updated Figma designs accordingly. Waiting with updatin the prototype until the new changes are more or less accepted. It should be finished by 2022-11-18.

Next Day: Next day I will be working on the #183390699 task. Start work on custom Postgres tables method: Add some tests creating a separate database and checking if tables from it appear there. Test writing a custom query. (Enso Bot - Nov 17, 2022)

enso-org / enso

Design for Joining #4982