static void Main(string[] args)
{
Catalog.Init();
string sql = "";
//TestTpcds_LoadData();
if (false)
{
JOBench.CreateTables();
sql = File.ReadAllText("../../../jobench/10a.sql");
goto doit;
}
if (false)
{
Tpch.CreateTables();
Tpch.LoadTables("0001");
//Tpch.CreateIndexes();
Tpch.AnalyzeTables();
sql = File.ReadAllText("../../../tpch/q20.sql");
goto doit;
}
if (true)
{
Tpcds.CreateTables();
//Tpcds.LoadTables("tiny");
//Tpcds.AnalyzeTables();
// 1, 2,3,7,10,
// long time: 4 bad plan
// 6: distinct not supported, causing wrong result
sql = File.ReadAllText("../../../tpcds/q7.sql");
goto doit;
}
doit:
sql = "with cte as (select * from d) select * from cte where d1=1;";
sql = "with cte as (select * from a) select cte1.a1, cte2.a2 from cte cte1, cte cte2 where cte2.a3<3"; // ok
sql = "with cte as (select * from a) select * from cte cte1, cte cte2 where cte1.a2=cte2.a3 and cte1.a1> 0 order by 1;";
sql = "select ab.a1, cd.c1 from (select * from a join b on a1=b1) ab , (select * from c join d on c1=d1) cd where ab.a1=cd.c1";
sql = "select * from (select avg(a2) from a join b on a1=b1) a (a1) join b on a1=b1;";
sql = "with cte as (select * from a join b on a1=b1 join c on a2=c2) select * from cte cte1, cte cte2;"; // ok
sql = "with cte as (select count(*) from a join b on a1=b1) select * from cte cte1;"; // ok
sql = "with cte as (select count(*) from a join b on a1=b1) select * from cte cte1, cte cte2;";
sql = "with cte as (select * from d where d1=1) select * from cte cte1, cte cte2;";
sql = "with cte as (select * from a where a1=1) select * from cte cte1, cte cte2;";
var stopWatch = new Stopwatch();
stopWatch.Start();
Console.WriteLine(sql);
var a = RawParser.ParseSingleSqlStatement(sql);
a.queryOpt_.profile_.enabled_ = true;
a.queryOpt_.optimize_.enable_subquery_unnest_ = true;
a.queryOpt_.optimize_.remove_from_ = false;
a.queryOpt_.optimize_.use_memo_ = true;
a.queryOpt_.optimize_.enable_cte_plan_ = false;
a.queryOpt_.optimize_.use_codegen_ = false;
//a.queryOpt_.optimize_.memo_disable_crossjoin = false;
//a.queryOpt_.optimize_.use_joinorder_solver = true;
// -- Semantic analysis:
// - bind the query
a.queryOpt_.optimize_.ValidateOptions();
a.Bind(null);
// -- generate an initial plan
ExplainOption.show_tablename_ = false;
a.queryOpt_.explain_.show_output_ = false;
a.queryOpt_.explain_.show_cost_ = a.queryOpt_.optimize_.use_memo_;
var rawplan = a.CreatePlan();
Console.WriteLine("***************** raw plan *************");
Console.WriteLine(rawplan.Explain(0));
physic.PhysicNode phyplan = null;
if (a.queryOpt_.optimize_.use_memo_)
{
Console.WriteLine("***************** optimized plan *************");
var optplan = a.SubstitutionOptimize();
Console.WriteLine(optplan.Explain(0, a.queryOpt_.explain_));
a.optimizer_.InitRootPlan(a);
a.optimizer_.OptimizeRootPlan(a, null);
Console.WriteLine(a.optimizer_.PrintMemo());
phyplan = a.optimizer_.CopyOutOptimalPlan();
Console.WriteLine(a.optimizer_.PrintMemo());
Console.WriteLine("***************** Memo plan *************");
Console.WriteLine(phyplan.Explain(0, a.queryOpt_.explain_));
}
else
{
// -- optimize the plan
Console.WriteLine("-- optimized plan --");
var optplan = a.SubstitutionOptimize();
Console.WriteLine(optplan.Explain(0, a.queryOpt_.explain_));
// -- physical plan
Console.WriteLine("-- physical plan --");
phyplan = a.physicPlan_;
Console.WriteLine(phyplan.Explain(0, a.queryOpt_.explain_));
}
Console.WriteLine("-- profiling plan --");
var final = new PhysicCollect(phyplan);
a.physicPlan_ = final;
var context = new ExecContext(a.queryOpt_);
final.ValidateThis();
if (a is SelectStmt select)
{
select.OpenSubQueries(context);
}
var code = final.Open(context);
code += final.Exec(null);
code += final.Close();
if (a.queryOpt_.optimize_.use_codegen_)
{
CodeWriter.WriteLine(code);
Compiler.Run(Compiler.Compile(), a, context);
}
Console.WriteLine(phyplan.Explain(0, a.queryOpt_.explain_));
stopWatch.Stop();
Console.WriteLine("RunTime: " + stopWatch.Elapsed);
Console.ReadKey();
}