feat: add support for nested loop join by crwen · Pull Request #164 · KipData/KiteSQL

let _ = fnck_sql
    .run("CREATE TABLE tab64784(pk INTEGER primary key, col0 INTEGER, col1 FLOAT, col2 VARCHAR, col3 INTEGER, col4 FLOAT, col5 VARCHAR)")
    .await?;
let _ = fnck_sql
    .run("INSERT INTO tab64784 VALUES(0,212,202.62,'nshdy',212,208.79,'wsxfc'),(1,213,203.64,'xwfuo',213,209.26,'lyswz'),(2,214,204.82,'jnued',216,210.48,'qczzf'),(3,215,205.40,'jtijf',217,211.96,'dpugl'),(4,216,206.3,'dpdzk',219,212.43,'xfirg'),(5,218,207.43,'qpwyw',220,213.50,'fmgky'),(6,219,208.3,'uooxb',221,215.30,'xpmdy'),(7,220,209.54,'ndtbb',225,218.8,'ivqyw'),(8,221,210.65,'zjpts',226,219.82,'sezsm'),(9,222,211.57,'slaxq',227,220.91,'bdqyb');")
    .await?;
let (schema, tuples) = fnck_sql.run("explain SELECT pk, col0 FROM tab64784 WHERE col0 IN (SELECT col3 FROM tab64784 WHERE col3 IS NULL OR (col1 < 22.54) OR col4 > 85.74) OR ((col4 IS NULL))").await?;
println!("{}", create_table(&schema, &tuples));
let (schema, tuples) = fnck_sql.run("explain SELECT pk, col0 FROM tab64784 WHERE col0 IN (SELECT col3 FROM tab64784 WHERE col3 IS NULL OR (col1 < 22.54) OR col4 > 85.74)").await?;
println!("{}", create_table(&schema, &tuples));
+-------------------------------------------------------------------------------------------------------------------------+
| PLAN                                                                                                                    |
+=========================================================================================================================+
| Projection [tab64784.pk, tab64784.col0] [Project]                                                                       |
|   LeftSemi Join Where ((tab64784.col0 = (tab64784.col3) as (_temp_table_1_.col3)) || tab64784.col4 is null) [HashJoin]  |
|     Scan tab64784 -> [pk, col0, col3, col4] [SeqScan]                                                                   |
|     Projection [tab64784.col3] [Project]                                                                                |
|       Filter ((tab64784.col3 is null || (tab64784.col1 < 22.54)) || (tab64784.col4 > 85.74)), Is Having: false [Filter] |
|         Scan tab64784 -> [col1, col3, col4] [SeqScan]                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
+-------------------------------------------------------------------------------------------------------------------------+
| PLAN                                                                                                                    |
+=========================================================================================================================+
| Projection [tab64784.pk, tab64784.col0] [Project]                                                                       |
|   LeftSemi Join On tab64784.col0 = (tab64784.col3) as (_temp_table_1_.col3) [HashJoin]                                  |
|     Scan tab64784 -> [pk, col0, col3] [SeqScan]                                                                         |
|     Projection [tab64784.col3] [Project]                                                                                |
|       Filter ((tab64784.col3 is null || (tab64784.col1 < 22.54)) || (tab64784.col4 > 85.74)), Is Having: false [Filter] |
|         Scan tab64784 -> [col1, col3, col4] [SeqScan]                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+