static void TestTpcds_LoadData()
{
var files = Directory.GetFiles(@"../../../tpcds", "*.sql");
string[] norun = { "q1", "q10" };
Tpcds.CreateTables();
Tpcds.LoadTables("tiny");
Tpcds.AnalyzeTables();
// make sure all queries can generate phase one opt plan
QueryOption option = new QueryOption();
option.optimize_.enable_subquery_unnest_ = true;
option.optimize_.remove_from_ = false;
option.optimize_.use_memo_ = false;
foreach (var v in files)
{
char[] splits = { '.', '/', '\\' };
var tokens = v.Split(splits, StringSplitOptions.RemoveEmptyEntries);
if (norun.Contains(tokens[1]))
{
continue;
}
var sql = File.ReadAllText(v);
var result = SQLStatement.ExecSQL(sql, out string phyplan, out _, option);
}
}
static void Main(string[] args)
{
Catalog.Init();
string sql = "";
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 (false)
{
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 = "select a2,b2,c2,d2 from ad, bd, cd, dd where a2=b2 and c2 = b2 and c2=d2 order by a2";
sql = "select count(*) from ast group by tumble(a0, interval '10' second)";
sql = "select round(a1, 10), count(*) from a group by round(a1, 10)";
sql = "select count(*) from a group by round(a1, 10)";
sql = "select count(*) from ast group by hop(a0, interval '5' second, interval '10' second)";
sql = "select round(a1, 10) from a group by a1;";
sql = "select abs(-a1*2), count(*) from a group by round(a1, 10);";
sql = "select abs(-a1*2), count(*) from a group by a1;";
sql = "select tumble_start(a0, interval '10' second), tumble_end(a0, interval '10' second), count(*) from ast group by tumble(a0, interval '10' second)";
var datetime = new DateTime();
datetime = DateTime.Now;
var stopWatch = new Stopwatch();
stopWatch.Start();
// query options might be conflicting or incomplete
Console.WriteLine(sql);
var a = RawParser.ParseSingleSqlStatement(sql);
ExplainOption.show_tablename_ = false;
a.queryOpt_.profile_.enabled_ = true;
a.queryOpt_.optimize_.enable_subquery_unnest_ = false;
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_.memo_use_joinorder_solver_ = false;
a.queryOpt_.explain_.show_output_ = true;
a.queryOpt_.explain_.show_id_ = false;
a.queryOpt_.explain_.show_cost_ = a.queryOpt_.optimize_.use_memo_;
// -- Semantic analysis:
// - bind the query
a.queryOpt_.optimize_.ValidateOptions();
a.Bind(null);
// -- generate an initial plan
var rawplan = a.CreatePlan();
Console.WriteLine("***************** raw plan *************");
Console.WriteLine(rawplan.Explain());
// -- optimize the plan
PhysicNode phyplan = null;
if (a.queryOpt_.optimize_.use_memo_)
{
Console.WriteLine("***************** optimized plan *************");
var optplan = a.SubstitutionOptimize();
Console.WriteLine(optplan.Explain(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(a.queryOpt_.explain_));
}
else
{
// -- optimize the plan
Console.WriteLine("-- optimized plan --");
var optplan = a.SubstitutionOptimize();
Console.WriteLine(optplan.Explain(a.queryOpt_.explain_));
// -- physical plan
Console.WriteLine("-- physical plan --");
phyplan = a.physicPlan_;
Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));
}
// -- output profile and query result
Console.WriteLine("-- profiling plan --");
var final = new PhysicCollect(phyplan);
a.physicPlan_ = final;
ExecContext context = a.CreateExecContext();
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(a.queryOpt_.explain_));
stopWatch.Stop();
Console.WriteLine("RunTime: " + stopWatch.Elapsed);
Console.ReadKey();
}
static void Main(string[] args)
{
Catalog.Init();
string sql = "";
if (args.Length != 0)
{
sql = args[0];
}
#pragma warning disable CS0162 // Unreachable code detected
// The warnings are annoying, so using a pragma to suppress them.
if (false)
{
JOBench.CreateTables();
var stats_fn = "../../../../jobench/statistics/jobench_stats";
Catalog.sysstat_.read_serialized_stats(stats_fn);
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 (false)
{
//84
sql = File.ReadAllText("../../../../tpcds/q33.sql");
Tpcds.CreateTables();
Tpcds.LoadTables("tiny");
Tpcds.AnalyzeTables();
// long time: 4 bad plan
// 6: distinct not supported, causing wrong result
// q23, q33, q56, q60: in-subquery plan bug
// 10,11,13, 31, 38, 41, 48, 54, 66, 72, 74: too slow
goto doit;
}
#pragma warning restore CS0162 // Unreachable code detected
doit:
bool convMode = false;
// Application Arguments in Project properties seem to be
// effective only after rebuilding.
// This is a last ditch effort to be able to debug arbitrary
// statements without rebuilding the solution.
string inputFile = "";
if (sql.Length == 2 && sql == "-i")
{
convMode = true;
}
else if (sql.Length == 2 && sql.StartsWith("-f"))
{
inputFile = args[1];
}
else if (sql.Length == 0)
{
sql = "select * from a tablesample row (2);";
}
do
{
if (convMode == true || (sql.Length == 1 && sql.Equals("-")))
{
System.Console.Write("QSQL> ");
sql = System.Console.ReadLine();
System.Console.WriteLine(sql);
}
var datetime = new DateTime();
datetime = DateTime.Now;
var stopWatch = new Stopwatch();
stopWatch.Start();
if (inputFile.Length != 0)
{
// read the file and execute all statements in it.
RunSQLFromFile(inputFile);
goto done;
}
// query options might be conflicting or incomplete
Console.WriteLine(sql);
var a = RawParser.ParseSingleSqlStatement(sql);
ExplainOption.show_tablename_ = true;
a.queryOpt_.profile_.enabled_ = true;
a.queryOpt_.optimize_.enable_subquery_unnest_ = true;
a.queryOpt_.optimize_.remove_from_ = true;
a.queryOpt_.optimize_.use_memo_ = true;
a.queryOpt_.optimize_.enable_cte_plan_ = true;
a.queryOpt_.optimize_.use_codegen_ = false;
a.queryOpt_.optimize_.memo_disable_crossjoin_ = false;
a.queryOpt_.optimize_.memo_use_joinorder_solver_ = false;
a.queryOpt_.explain_.show_output_ = true;
a.queryOpt_.explain_.show_id_ = true;
a.queryOpt_.explain_.show_estCost_ = a.queryOpt_.optimize_.use_memo_;
a.queryOpt_.explain_.mode_ = ExplainMode.full;
// -- Semantic analysis:
// - bind the query
a.queryOpt_.optimize_.ValidateOptions();
if (!(a is SelectStmt))
{
SQLStatement.ExecSQLList(sql);
goto done;
}
a.Bind(null);
// -- generate an initial plan
var rawplan = a.CreatePlan();
Console.WriteLine("***************** raw plan *************");
Console.WriteLine(rawplan.Explain());
// -- optimize the plan
PhysicNode phyplan = null;
if (a.queryOpt_.optimize_.use_memo_)
{
Console.WriteLine("***************** optimized plan *************");
var optplan = a.SubstitutionOptimize();
Console.WriteLine(optplan.Explain(a.queryOpt_.explain_));
a.optimizer_ = new Optimizer(a);
a.optimizer_.ExploreRootPlan(a);
phyplan = a.optimizer_.CopyOutOptimalPlan();
Console.WriteLine(a.optimizer_.PrintMemo());
Console.WriteLine("***************** Memo plan *************");
Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));
}
else
{
// -- optimize the plan
Console.WriteLine("-- optimized plan --");
var optplan = a.SubstitutionOptimize();
Console.WriteLine(optplan.Explain(a.queryOpt_.explain_));
// -- physical plan
Console.WriteLine("-- physical plan --");
phyplan = a.physicPlan_;
Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));
}
// -- output profile and query result
Console.WriteLine("-- profiling plan --");
var final = new PhysicCollect(phyplan);
a.physicPlan_ = final;
ExecContext context = a.CreateExecContext();
final.ValidateThis();
if (a is SelectStmt select)
{
select.OpenSubQueries(context);
}
final.Open(context);
final.Exec(null);
final.Close();
if (a.queryOpt_.optimize_.use_codegen_)
{
CodeWriter.WriteLine(context.code_);
Compiler.Run(Compiler.Compile(), a, context);
}
Console.WriteLine(phyplan.Explain(a.queryOpt_.explain_));
done:
stopWatch.Stop();
Console.WriteLine("RunTime: " + stopWatch.Elapsed);
} while (convMode == true);
Console.ReadKey();
}
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();
}
