OmniSciDB  95562058bd
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Pages
SerializeToSql.cpp
Go to the documentation of this file.
1 /*
2  * Copyright 2019 OmniSci, Inc.
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 
17 #include "SerializeToSql.h"
18 #include "ExternalExecutor.h"
19 
21 
23  const Catalog_Namespace::Catalog* catalog)
24  : ra_exe_unit_(ra_exe_unit), catalog_(catalog) {}
25 
26 std::string ScalarExprToSql::visitVar(const Analyzer::Var* var) const {
27  auto it = ra_exe_unit_->groupby_exprs.begin();
28  std::advance(it, var->get_varno() - 1);
29  return visit(it->get());
30 }
31 
32 std::string ScalarExprToSql::visitColumnVar(const Analyzer::ColumnVar* col_var) const {
33  return serialize_table_ref(col_var->get_table_id(), catalog_) + "." +
35  col_var->get_table_id(), col_var->get_column_id(), catalog_);
36 }
37 
38 std::string ScalarExprToSql::visitConstant(const Analyzer::Constant* constant) const {
39  if (constant->get_is_null()) {
40  return "NULL";
41  }
42  const auto& constant_ti = constant->get_type_info();
43  const auto result = DatumToString(constant->get_constval(), constant_ti);
44  if (constant_ti.is_string()) {
45  return "'" + result + "'";
46  } else {
47  return result;
48  }
49 }
50 
51 std::string ScalarExprToSql::visitUOper(const Analyzer::UOper* uoper) const {
52  const auto operand = uoper->get_operand();
53  const auto operand_str = visit(operand);
54  const auto optype = uoper->get_optype();
55  switch (optype) {
56  case kNOT: {
57  return "NOT (" + operand_str + ")";
58  }
59  case kUMINUS: {
60  return "-" + operand_str;
61  }
62  case kISNULL: {
63  return operand_str + " IS NULL";
64  }
65  case kCAST: {
66  const auto& operand_ti = operand->get_type_info();
67  const auto& target_ti = uoper->get_type_info();
69  throw std::runtime_error("Type not supported yet for extern execution: " +
70  target_ti.get_type_name());
71  }
72  if ((operand_ti.get_type() == target_ti.get_type()) ||
73  ((operand_ti.is_string() && target_ti.is_string()))) {
74  return operand_str;
75  }
76  return "CAST(" + operand_str + " AS " + target_ti.get_type_name() + ")";
77  }
78  default: {
79  throw std::runtime_error("Unary operator type: " + std::to_string(optype) +
80  " not supported");
81  }
82  }
83 }
84 
85 std::string ScalarExprToSql::visitBinOper(const Analyzer::BinOper* bin_oper) const {
86  return visit(bin_oper->get_left_operand()) + " " +
87  binOpTypeToString(bin_oper->get_optype()) + " " +
88  visit(bin_oper->get_right_operand());
89 }
90 
91 std::string ScalarExprToSql::visitInValues(const Analyzer::InValues* in_values) const {
92  const auto needle = visit(in_values->get_arg());
93  const auto haystack = visitList(in_values->get_value_list());
94  return needle + " IN (" + boost::algorithm::join(haystack, ", ") + ")";
95 }
96 
97 std::string ScalarExprToSql::visitLikeExpr(const Analyzer::LikeExpr* like) const {
98  const auto str = visit(like->get_arg());
99  const auto pattern = visit(like->get_like_expr());
100  const auto result = str + " LIKE " + pattern;
101  if (like->get_escape_expr()) {
102  const auto escape = visit(like->get_escape_expr());
103  return result + " ESCAPE " + escape;
104  }
105  return result;
106 }
107 
108 std::string ScalarExprToSql::visitCaseExpr(const Analyzer::CaseExpr* case_) const {
109  std::string case_str = "CASE ";
110  const auto& expr_pair_list = case_->get_expr_pair_list();
111  for (const auto& expr_pair : expr_pair_list) {
112  const auto when = "WHEN " + visit(expr_pair.first.get());
113  const auto then = " THEN " + visit(expr_pair.second.get());
114  case_str += when + then;
115  }
116  return case_str + " ELSE " + visit(case_->get_else_expr()) + " END";
117 }
118 
119 namespace {
120 
121 std::string agg_type_to_string(const SQLAgg agg_type) {
122  switch (agg_type) {
123  case kAVG:
124  return "AVG";
125  case kMIN:
126  return "MIN";
127  case kMAX:
128  return "MAX";
129  case kSUM:
130  return "SUM";
131  case kCOUNT:
132  return "COUNT";
134  return "APPROX_COUNT_DISTINCT";
135  case kSAMPLE:
136  return "SAMPLE";
137  default:
138  LOG(FATAL) << "Invalid aggregate type: " << agg_type;
139  return "";
140  }
141 }
142 
143 std::string agg_to_string(const Analyzer::AggExpr* agg_expr,
144  const RelAlgExecutionUnit* ra_exe_unit,
145  const Catalog_Namespace::Catalog* catalog) {
146  ScalarExprToSql scalar_expr_to_sql(ra_exe_unit, catalog);
147  const auto agg_type = agg_type_to_string(agg_expr->get_aggtype());
148  const auto arg =
149  agg_expr->get_arg() ? scalar_expr_to_sql.visit(agg_expr->get_arg()) : "*";
150  const auto distinct = agg_expr->get_is_distinct() ? "DISTINCT " : "";
151  return agg_type + "(" + distinct + arg + ")";
152 }
153 
154 } // namespace
155 
157  const Analyzer::FunctionOper* func_oper) const {
158  std::string result = func_oper->getName();
159  if (result == "||") {
160  CHECK_EQ(func_oper->getArity(), size_t(2));
161  return visit(func_oper->getArg(0)) + "||" + visit(func_oper->getArg(1));
162  }
163  if (result == "SUBSTRING") {
164  result = "SUBSTR";
165  }
166  std::vector<std::string> arg_strs;
167  for (size_t i = 0; i < func_oper->getArity(); ++i) {
168  arg_strs.push_back(visit(func_oper->getArg(i)));
169  }
170  return result + "(" + boost::algorithm::join(arg_strs, ",") + ")";
171 }
172 
174  const Analyzer::WindowFunction* window_func) const {
175  std::string result = sql_window_function_to_str(window_func->getKind());
176  {
177  const auto arg_strs = visitList(window_func->getArgs());
178  result += "(" + boost::algorithm::join(arg_strs, ",") + ")";
179  }
180  result += " OVER (";
181  {
182  const auto partition_strs = visitList(window_func->getPartitionKeys());
183  if (!partition_strs.empty()) {
184  result += "PARTITION BY " + boost::algorithm::join(partition_strs, ",");
185  }
186  }
187  {
188  std::vector<std::string> order_strs;
189  const auto& order_keys = window_func->getOrderKeys();
190  const auto& collation = window_func->getCollation();
191  CHECK_EQ(order_keys.size(), collation.size());
192  for (size_t i = 0; i < order_keys.size(); ++i) {
193  std::string order_str = visit(order_keys[i].get());
194  order_str += collation[i].is_desc ? " DESC" : " ASC";
195  // TODO: handle nulls first / last
196  order_strs.push_back(order_str);
197  }
198  if (!order_strs.empty()) {
199  result += " ORDER BY " + boost::algorithm::join(order_strs, ",");
200  }
201  }
202  result += ")";
203  return result;
204 }
205 
206 std::string ScalarExprToSql::visitAggExpr(const Analyzer::AggExpr* agg) const {
207  return agg_to_string(agg, ra_exe_unit_, catalog_);
208 }
209 
210 std::string ScalarExprToSql::binOpTypeToString(const SQLOps op_type) {
211  switch (op_type) {
212  case kEQ:
213  return "=";
214  case kNE:
215  return "<>";
216  case kLT:
217  return "<";
218  case kLE:
219  return "<=";
220  case kGT:
221  return ">";
222  case kGE:
223  return ">=";
224  case kAND:
225  return "AND";
226  case kOR:
227  return "OR";
228  case kMINUS:
229  return "-";
230  case kPLUS:
231  return "+";
232  case kMULTIPLY:
233  return "*";
234  case kDIVIDE:
235  return "/";
236  case kMODULO:
237  return "%";
238  case kARRAY_AT:
239  return "[]";
240  case kOVERLAPS:
241  return "OVERLAPS";
242  default:
243  LOG(FATAL) << "Invalid operator type: " << op_type;
244  return "";
245  }
246 }
247 
248 template <typename List>
249 std::vector<std::string> ScalarExprToSql::visitList(const List& expressions) const {
250  std::vector<std::string> result;
251  for (const auto& expression : expressions) {
252  result.push_back(visit(expression.get()));
253  }
254  return result;
255 }
256 
257 namespace {
258 
259 std::string where_to_string(const RelAlgExecutionUnit* ra_exe_unit,
260  const Catalog_Namespace::Catalog* catalog) {
261  ScalarExprToSql scalar_expr_to_sql(ra_exe_unit, catalog);
262  auto qual_strings = scalar_expr_to_sql.visitList(ra_exe_unit->quals);
263  const auto simple_qual_strings =
264  scalar_expr_to_sql.visitList(ra_exe_unit->simple_quals);
265  qual_strings.insert(
266  qual_strings.end(), simple_qual_strings.begin(), simple_qual_strings.end());
267  return boost::algorithm::join(qual_strings, " AND ");
268 }
269 
270 std::string join_condition_to_string(const RelAlgExecutionUnit* ra_exe_unit,
271  const Catalog_Namespace::Catalog* catalog) {
272  ScalarExprToSql scalar_expr_to_sql(ra_exe_unit, catalog);
273  std::vector<std::string> qual_strings;
274  for (const auto& join_level_quals : ra_exe_unit->join_quals) {
275  const auto level_qual_strings = scalar_expr_to_sql.visitList(join_level_quals.quals);
276  qual_strings.insert(
277  qual_strings.end(), level_qual_strings.begin(), level_qual_strings.end());
278  }
279  return boost::algorithm::join(qual_strings, " AND ");
280 }
281 
282 std::string targets_to_string(const RelAlgExecutionUnit* ra_exe_unit,
283  const Catalog_Namespace::Catalog* catalog) {
284  ScalarExprToSql scalar_expr_to_sql(ra_exe_unit, catalog);
285  std::vector<std::string> target_strings;
286  for (const auto target : ra_exe_unit->target_exprs) {
287  target_strings.push_back(scalar_expr_to_sql.visit(target));
288  }
289  return boost::algorithm::join(target_strings, ", ");
290 }
291 
292 std::string group_by_to_string(const RelAlgExecutionUnit* ra_exe_unit,
293  const Catalog_Namespace::Catalog* catalog) {
294  if (ra_exe_unit->groupby_exprs.size() == 1 || !ra_exe_unit->groupby_exprs.front()) {
295  return "";
296  }
297  ScalarExprToSql scalar_expr_to_sql(ra_exe_unit, catalog);
298  const auto group_by_strings = scalar_expr_to_sql.visitList(ra_exe_unit->groupby_exprs);
299  return boost::algorithm::join(group_by_strings, ", ");
300 }
301 
302 std::string from_to_string(const RelAlgExecutionUnit* ra_exe_unit,
303  const Catalog_Namespace::Catalog* catalog) {
304  std::vector<std::string> from_strings;
305  for (const auto& input_desc : ra_exe_unit->input_descs) {
306  const auto table_ref = serialize_table_ref(input_desc.getTableId(), catalog);
307  from_strings.push_back(table_ref);
308  }
309  return boost::algorithm::join(from_strings, ", ");
310 }
311 
312 std::string maybe(const std::string& prefix, const std::string& clause) {
313  return clause.empty() ? "" : " " + prefix + " " + clause;
314 }
315 
316 } // namespace
317 
318 std::string serialize_table_ref(const int table_id,
319  const Catalog_Namespace::Catalog* catalog) {
320  if (table_id >= 0) {
321  const auto td = catalog->getMetadataForTable(table_id);
322  CHECK(td);
323  return td->tableName;
324  }
325  return "\"#temp" + std::to_string(table_id) + "\"";
326 }
327 
328 std::string serialize_column_ref(const int table_id,
329  const int column_id,
330  const Catalog_Namespace::Catalog* catalog) {
331  if (table_id >= 0) {
332  const auto cd = catalog->getMetadataForColumn(table_id, column_id);
333  CHECK(cd);
334  return cd->columnName;
335  }
336  return "col" + std::to_string(column_id);
337 }
338 
340  const Catalog_Namespace::Catalog* catalog) {
341  const auto targets = targets_to_string(ra_exe_unit, catalog);
342  const auto from = from_to_string(ra_exe_unit, catalog);
343  const auto join_on = join_condition_to_string(ra_exe_unit, catalog);
344  const auto where = where_to_string(ra_exe_unit, catalog);
345  const auto group = group_by_to_string(ra_exe_unit, catalog);
346  return {"SELECT " + targets + " FROM " + from + maybe("ON", join_on) +
347  maybe("WHERE", where) + maybe("GROUP BY", group),
348  from};
349 }
int get_table_id() const
Definition: Analyzer.h:194
bool is_supported_type_for_extern_execution(const SQLTypeInfo &ti)
catalog_(nullptr)
std::vector< Analyzer::Expr * > target_exprs
SQLAgg
Definition: sqldefs.h:71
#define CHECK_EQ(x, y)
Definition: Logger.h:205
SqlWindowFunctionKind getKind() const
Definition: Analyzer.h:1447
std::string visitColumnVar(const Analyzer::ColumnVar *col_var) const override
const Expr * get_else_expr() const
Definition: Analyzer.h:1152
std::string DatumToString(Datum d, const SQLTypeInfo &ti)
Definition: Datum.cpp:239
const Expr * get_escape_expr() const
Definition: Analyzer.h:908
size_t getArity() const
Definition: Analyzer.h:1360
class for a per-database catalog. also includes metadata for the current database and the current use...
Definition: Catalog.h:97
#define LOG(tag)
Definition: Logger.h:188
const Expr * get_right_operand() const
Definition: Analyzer.h:443
SQLOps
Definition: sqldefs.h:29
Definition: sqldefs.h:35
ScalarExprToSql(const RelAlgExecutionUnit *ra_exe_unit, const Catalog_Namespace::Catalog *catalog)
std::string maybe(const std::string &prefix, const std::string &clause)
Definition: sqldefs.h:36
std::string join(T const &container, std::string const &delim)
bool get_is_null() const
Definition: Analyzer.h:334
Definition: sqldefs.h:38
std::vector< InputDescriptor > input_descs
std::string visitWindowFunction(const Analyzer::WindowFunction *window_func) const override
Definition: sqldefs.h:49
Definition: sqldefs.h:30
Expr * get_arg() const
Definition: Analyzer.h:1096
Definition: sqldefs.h:41
std::string visit(const Analyzer::Expr *expr) const
const std::list< std::shared_ptr< Analyzer::Expr > > groupby_exprs
const std::vector< std::shared_ptr< Analyzer::Expr > > & getOrderKeys() const
Definition: Analyzer.h:1455
std::string visitBinOper(const Analyzer::BinOper *bin_oper) const override
const std::vector< OrderEntry > & getCollation() const
Definition: Analyzer.h:1459
const Expr * get_arg() const
Definition: Analyzer.h:905
std::vector< std::string > visitList(const List &expressions) const
std::string visitConstant(const Analyzer::Constant *constant) const override
std::string to_string(char const *&&v)
std::string from_to_string(const RelAlgExecutionUnit *ra_exe_unit, const Catalog_Namespace::Catalog *catalog)
std::string serialize_column_ref(const int table_id, const int column_id, const Catalog_Namespace::Catalog *catalog)
Definition: sqldefs.h:73
std::string visitAggExpr(const Analyzer::AggExpr *agg) const override
SQLOps get_optype() const
Definition: Analyzer.h:439
const std::vector< std::shared_ptr< Analyzer::Expr > > & getArgs() const
Definition: Analyzer.h:1449
const JoinQualsPerNestingLevel join_quals
std::string visitCaseExpr(const Analyzer::CaseExpr *case_) const override
const ColumnDescriptor * getMetadataForColumn(int tableId, const std::string &colName) const
std::string sql_window_function_to_str(const SqlWindowFunctionKind kind)
Definition: sqldefs.h:37
Definition: sqldefs.h:75
int get_varno() const
Definition: Analyzer.h:275
bool get_is_distinct() const
Definition: Analyzer.h:1098
std::string group_by_to_string(const RelAlgExecutionUnit *ra_exe_unit, const Catalog_Namespace::Catalog *catalog)
std::string agg_type_to_string(const SQLAgg agg_type)
const SQLTypeInfo & get_type_info() const
Definition: Analyzer.h:78
std::string visitVar(const Analyzer::Var *) const override
Definition: sqldefs.h:34
std::string visitLikeExpr(const Analyzer::LikeExpr *like) const override
std::string agg_to_string(const Analyzer::AggExpr *agg_expr, const RelAlgExecutionUnit *ra_exe_unit, const Catalog_Namespace::Catalog *catalog)
Definition: sqldefs.h:40
static std::string binOpTypeToString(const SQLOps op_type)
const Expr * get_like_expr() const
Definition: Analyzer.h:907
const Analyzer::Expr * getArg(const size_t i) const
Definition: Analyzer.h:1362
const Expr * get_operand() const
Definition: Analyzer.h:371
Datum get_constval() const
Definition: Analyzer.h:335
Definition: sqldefs.h:32
Definition: sqldefs.h:76
const std::list< std::shared_ptr< Analyzer::Expr > > & get_value_list() const
Definition: Analyzer.h:586
std::string get_type_name() const
Definition: sqltypes.h:362
const RelAlgExecutionUnit * ra_exe_unit_
SQLAgg get_aggtype() const
Definition: Analyzer.h:1095
std::list< std::shared_ptr< Analyzer::Expr > > quals
std::string visitFunctionOper(const Analyzer::FunctionOper *func_oper) const override
#define CHECK(condition)
Definition: Logger.h:197
std::string visitUOper(const Analyzer::UOper *uoper) const override
const Catalog_Namespace::Catalog * catalog_
Definition: sqldefs.h:33
const Expr * get_left_operand() const
Definition: Analyzer.h:442
const TableDescriptor * getMetadataForTable(const std::string &tableName, const bool populateFragmenter=true) const
Returns a pointer to a const TableDescriptor struct matching the provided tableName.
int get_column_id() const
Definition: Analyzer.h:195
std::string getName() const
Definition: Analyzer.h:1358
std::string targets_to_string(const RelAlgExecutionUnit *ra_exe_unit, const Catalog_Namespace::Catalog *catalog)
std::string visitInValues(const Analyzer::InValues *in_values) const override
Definition: sqldefs.h:74
ExecutionUnitSql serialize_to_sql(const RelAlgExecutionUnit *ra_exe_unit, const Catalog_Namespace::Catalog *catalog)
const std::vector< std::shared_ptr< Analyzer::Expr > > & getPartitionKeys() const
Definition: Analyzer.h:1451
std::string join_condition_to_string(const RelAlgExecutionUnit *ra_exe_unit, const Catalog_Namespace::Catalog *catalog)
Definition: sqldefs.h:72
std::string serialize_table_ref(const int table_id, const Catalog_Namespace::Catalog *catalog)
Definition: sqldefs.h:39
const Expr * get_arg() const
Definition: Analyzer.h:584
SQLOps get_optype() const
Definition: Analyzer.h:370
const std::list< std::pair< std::shared_ptr< Analyzer::Expr >, std::shared_ptr< Analyzer::Expr > > > & get_expr_pair_list() const
Definition: Analyzer.h:1149
std::string where_to_string(const RelAlgExecutionUnit *ra_exe_unit, const Catalog_Namespace::Catalog *catalog)
std::list< std::shared_ptr< Analyzer::Expr > > simple_quals