static void createBuildInTestTables()
{
// create tables
string[] createtables =
{
@"create table test (t1 int, t2 int, t3 int, t4 int);"
,
@"create table a (a1 int, a2 int, a3 int, a4 int);",
@"create table b (b1 int, b2 int, b3 int, b4 int);",
@"create table c (c1 int, c2 int, c3 int, c4 int);",
@"create table d (d1 int, d2 int, d3 int, d4 int);",
// nullable tables
@"create table r (r1 int, r2 int, r3 int, r4 int);",
// distributed tables
@"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;",
@"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;",
@"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;",
@"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;",
@"create table ar (a1 int, a2 int, a3 int, a4 int) replicated;",
@"create table br (b1 int, b2 int, b3 int, b4 int) replicated;",
@"create table arb (a1 int, a2 int, a3 int, a4 int) roundrobin;",
@"create table brb (b1 int, b2 int, b3 int, b4 int) roundrobin;",
// steaming tables
@"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);", // bounded table with ts
@"create stream ainf (a0 datetime, a1 int, a2 int, a3 int, a4 int);", // unbounded table
};
SQLStatement.ExecSQLList(string.Join("", createtables));
// load tables
var appbin_dir = AppContext.BaseDirectory.Substring(0, AppContext.BaseDirectory.LastIndexOf("bin"));
var folder = $@"{appbin_dir}/../data";
var tables = new List <string>()
{
"test", "a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ar", "br", "arb", "brb", "ast"
};
foreach (var v in tables)
{
string filename = $@"'{folder}/{v}.tbl'";
var sql = $"copy {v} from {filename};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
// create index
string[] createindexes =
{
@"create unique index dd1 on d(d1);",
@"create index dd2 on d(d2);",
};
SQLStatement.ExecSQLList(string.Join("", createindexes));
// analyze tables
foreach (var v in tables)
{
var sql = $"analyze {v};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
}
static public void CreateTables()
{
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}\..\..\..\jobench\create";
string filename = $@"{folder}\schema.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
}
static public void CreateIndexes()
{
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}/../../../../tpch/sql_scripts";
string filename = $@"{folder}/TableIndexes.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
}
static public void DropTables()
{
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}/../../../../tpcds/sql_scripts";
string filename = $@"{folder}/DropTables.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
System.GC.Collect();
}
static public void LoadTables(string subfolder)
{
string save_curdir = Directory.GetCurrentDirectory();
string folder = $@"{save_curdir}/../../../../tpch/sql_scripts";
Directory.SetCurrentDirectory(folder);
var sql = File.ReadAllText($@"LoadTables-{subfolder}.sql");
SQLStatement.ExecSQLList(sql);
Directory.SetCurrentDirectory(save_curdir);
}
static public void CreateTables()
{
// hack: drop tpch table customer
SQLStatement.ExecSQL("drop table customer;", out _, out _);
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}\..\..\..\tpch\create";
string filename = $@"{folder}\tpch.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
}
static void createBuildInTestTables()
{
// create tables
string[] createtables =
{
@"create table test (a1 int, a2 int, a3 int, a4 int);"
,
@"create table a (a1 int, a2 int, a3 int, a4 int);",
@"create table b (b1 int, b2 int, b3 int, b4 int);",
@"create table c (c1 int, c2 int, c3 int, c4 int);",
@"create table d (d1 int, d2 int, d3 int, d4 int);",
// nullable tables
@"create table r (r1 int, r2 int, r3 int, r4 int);",
// distributed tables
@"create table ad (a1 int, a2 int, a3 int, a4 int) distributed by a1;",
@"create table bd (b1 int, b2 int, b3 int, b4 int) distributed by b1;",
@"create table cd (c1 int, c2 int, c3 int, c4 int) distributed by c1;",
@"create table dd (d1 int, d2 int, d3 int, d4 int) distributed by d1;",
// steaming tables
@"create table ast (a0 datetime, a1 int, a2 int, a3 int, a4 int);",
};
SQLStatement.ExecSQLList(string.Join("", createtables));
// load tables
var curdir = Directory.GetCurrentDirectory();
var folder = $@"{curdir}\..\..\..\data";
var tables = new List <string>()
{
"a", "b", "c", "d", "r", "ad", "bd", "cd", "dd", "ast"
};
foreach (var v in tables)
{
string filename = $@"'{folder}\{v}.tbl'";
var sql = $"copy {v} from {filename};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
// create index
string[] createindexes =
{
@"create unique index dd1 on d(d1);",
@"create index dd2 on d(d2);",
};
SQLStatement.ExecSQLList(string.Join("", createindexes));
// analyze tables
foreach (var v in tables)
{
var sql = $"analyze {v};";
var result = SQLStatement.ExecSQL(sql, out _, out _);
}
}
static public void CreateTables(bool isdistr = false)
{
DropTables();
string curdir = Directory.GetCurrentDirectory();
string folder = $@"{curdir}/../../../../tpch/sql_scripts";
string postfix = isdistr ? "_d" : "";
string filename = $@"{folder}/tpch{postfix}.sql";
var sql = File.ReadAllText(filename);
SQLStatement.ExecSQLList(sql);
}
public string SQLQueryVerify(string sql_dir_fn, string write_dir_fn, string expect_dir_fn, string[] badQueries)
{
QueryOption option = new QueryOption();
option.optimize_.TurnOnAllOptimizations();
option.optimize_.remove_from_ = false;
option.explain_.show_output_ = true;
option.explain_.show_cost_ = option.optimize_.use_memo_;
// get a list of sql query fine names from the sql directory
string[] sqlFiles = Directory.GetFiles(sql_dir_fn);
// execute the query in each file and and verify the result
foreach (string sqlFn in sqlFiles)
{
string dbg_name = Path.GetFileNameWithoutExtension(sqlFn);
if (badQueries.Contains(dbg_name) == true)
{
continue;
}
// execute query
var sql = File.ReadAllText(sqlFn);
var test_result = SQLStatement.ExecSQLList(sql, option);
// construct file name for result file and write result
string f_name = Path.GetFileNameWithoutExtension(sqlFn);
string write_fn = $@"{write_dir_fn}\{f_name}.txt";
File.WriteAllText(write_fn, test_result);
// construct file name of expected result
string expect_fn = $@"{expect_dir_fn}\{f_name}.txt";
// verify query result against the expected result
if (!resultVerify(write_fn, expect_fn))
{
return(write_fn);
}
}
return(null);
}
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 public void CreateIndexes()
{
SQLStatement.ExecSQLList(string.Join("", createindexes_));
}
