[GitHub] [arrow-datafusion] alamb commented on issue #3819: Left join not working?

[GitBox <gi...@apache.org>]

alamb commented on issue #3819:
URL: https://github.com/apache/arrow-datafusion/issues/3819#issuecomment-1287764290

   Hi @luobei42  -- the `bug` label is added when the ticket is created. 
   
   I am not familiar with datafusion python, and I was not able to reproduce your report  with datafusion-cli from master (maybe it was fixed previously 🤔 )
   
   
   ```sql
   (arrow_dev) alamb@MacBook-Pro-8:~/Software/arrow-datafusion2$ datafusion-cli
   DataFusion CLI v13.0.0
   ❯ 
   create table author
   as
   select column1 as id, column2 as name from
   (VALUES
     (0, 'Aldous0 Abfalterer0')
    ,(1, 'Bob0 Abramovich0')
    ,(2, 'Cyril0 Jobstreibitzer0')
    ,(3, 'Dmitry0 Laurent0')
   )as sq;
   
   0 rows in set. Query took 0.002 seconds.
   ❯ 
   create table book
   AS
   select column1 as id, column2 as author_id, column3 as name
   from
   (VALUES
    (0,1,'always a true house')
    ,(1,2,'always one true banana')
    ,(2,2,'never the false tree')
    ,(3,3,'always the true table')
    ,(4,3,'never a false house')
    ,(5,3,'always one green window')
   ) as sq;
   
   0 rows in set. Query took 0.001 seconds.
   ❯ SELECT a.id, a.name, count(distinct b.id)
   from author a
   left join book b on a.id=b.author_id
   where a.id=0
   group by a.id, a.name;
   +----+---------------------+----------------------+
   | id | name                | COUNT(DISTINCT b.id) |
   +----+---------------------+----------------------+
   | 0  | Aldous0 Abfalterer0 | 0                    |
   +----+---------------------+----------------------+
   1 row in set. Query took 0.004 seconds.
   ❯ 
   select count(*) from author;
   
   +-----------------+
   | COUNT(UInt8(1)) |
   +-----------------+
   | 4               |
   +-----------------+
   1 row in set. Query took 0.000 seconds.
   ❯ select count(*) from book;
   
   +-----------------+
   | COUNT(UInt8(1)) |
   +-----------------+
   | 6               |
   +-----------------+
   1 row in set. Query took 0.000 seconds.
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org