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] | +-------------------------------------------------------------------------------------------------------------------------+