OmniSciDB  c1a53651b2
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
HeavyDBParser.java
Go to the documentation of this file.
1 /*
2  * Copyright 2022 HEAVY.AI, 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 package com.mapd.calcite.parser;
18 
19 import static org.apache.calcite.sql.parser.SqlParserPos.ZERO;
20 
21 import com.google.common.collect.ImmutableList;
25 import com.mapd.parser.extension.ddl.ExtendedSqlParser;
28 
29 import org.apache.calcite.avatica.util.Casing;
30 import org.apache.calcite.config.CalciteConnectionConfig;
31 import org.apache.calcite.config.CalciteConnectionConfigImpl;
32 import org.apache.calcite.config.CalciteConnectionProperty;
33 import org.apache.calcite.plan.Context;
34 import org.apache.calcite.plan.RelOptTable;
35 import org.apache.calcite.plan.RelOptUtil;
36 import org.apache.calcite.plan.hep.HepPlanner;
37 import org.apache.calcite.plan.hep.HepProgramBuilder;
40 import org.apache.calcite.rel.RelNode;
41 import org.apache.calcite.rel.RelRoot;
42 import org.apache.calcite.rel.RelShuttleImpl;
43 import org.apache.calcite.rel.core.TableModify;
44 import org.apache.calcite.rel.core.TableModify.Operation;
45 import org.apache.calcite.rel.logical.LogicalProject;
46 import org.apache.calcite.rel.logical.LogicalTableModify;
47 import org.apache.calcite.rel.rules.CoreRules;
49 import org.apache.calcite.rel.type.RelDataType;
50 import org.apache.calcite.rel.type.RelDataTypeFactory;
51 import org.apache.calcite.rel.type.RelDataTypeSystem;
52 import org.apache.calcite.rex.*;
53 import org.apache.calcite.runtime.CalciteException;
54 import org.apache.calcite.schema.SchemaPlus;
55 import org.apache.calcite.schema.Statistic;
56 import org.apache.calcite.schema.Table;
57 import org.apache.calcite.sql.*;
58 import org.apache.calcite.sql.advise.SqlAdvisorValidator;
59 import org.apache.calcite.sql.dialect.CalciteSqlDialect;
60 import org.apache.calcite.sql.fun.SqlCase;
61 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
62 import org.apache.calcite.sql.parser.SqlParseException;
63 import org.apache.calcite.sql.parser.SqlParser;
64 import org.apache.calcite.sql.parser.SqlParserPos;
65 import org.apache.calcite.sql.type.OperandTypes;
66 import org.apache.calcite.sql.type.ReturnTypes;
67 import org.apache.calcite.sql.type.SqlTypeName;
68 import org.apache.calcite.sql.type.SqlTypeUtil;
69 import org.apache.calcite.sql.util.SqlBasicVisitor;
70 import org.apache.calcite.sql.util.SqlShuttle;
71 import org.apache.calcite.sql.util.SqlVisitor;
72 import org.apache.calcite.sql.validate.SqlConformanceEnum;
73 import org.apache.calcite.sql.validate.SqlValidator;
76 import org.apache.calcite.tools.*;
77 import org.apache.calcite.util.Pair;
78 import org.apache.calcite.util.Util;
79 import org.slf4j.Logger;
80 import org.slf4j.LoggerFactory;
81 
82 import java.io.IOException;
83 import java.lang.reflect.Field;
84 import java.util.*;
85 import java.util.concurrent.ConcurrentHashMap;
86 import java.util.function.BiPredicate;
87 import java.util.function.Supplier;
88 import java.util.stream.Stream;
89 
90 import ai.heavy.thrift.server.TColumnType;
91 import ai.heavy.thrift.server.TDatumType;
92 import ai.heavy.thrift.server.TEncodingType;
93 import ai.heavy.thrift.server.TTableDetails;
94 
95 public final class HeavyDBParser {
96  public static final ThreadLocal<HeavyDBParser> CURRENT_PARSER = new ThreadLocal<>();
97  private static final EnumSet<SqlKind> SCALAR =
98  EnumSet.of(SqlKind.SCALAR_QUERY, SqlKind.SELECT);
99  private static final EnumSet<SqlKind> EXISTS = EnumSet.of(SqlKind.EXISTS);
100  private static final EnumSet<SqlKind> DELETE = EnumSet.of(SqlKind.DELETE);
101  private static final EnumSet<SqlKind> UPDATE = EnumSet.of(SqlKind.UPDATE);
102  private static final EnumSet<SqlKind> IN = EnumSet.of(SqlKind.IN);
103  private static final EnumSet<SqlKind> ARRAY_VALUE =
104  EnumSet.of(SqlKind.ARRAY_VALUE_CONSTRUCTOR);
105  private static final EnumSet<SqlKind> OTHER_FUNCTION =
106  EnumSet.of(SqlKind.OTHER_FUNCTION);
107 
108  final static Logger HEAVYDBLOGGER = LoggerFactory.getLogger(HeavyDBParser.class);
109 
110  private final Supplier<HeavyDBSqlOperatorTable> dbSqlOperatorTable;
111  private final String dataDir;
112 
113  private int callCount = 0;
114  private final int dbPort;
117 
118  private static Map<String, Boolean> SubqueryCorrMemo = new ConcurrentHashMap<>();
119 
120  public HeavyDBParser(String dataDir,
121  final Supplier<HeavyDBSqlOperatorTable> dbSqlOperatorTable,
122  int dbPort,
124  this.dataDir = dataDir;
125  this.dbSqlOperatorTable = dbSqlOperatorTable;
126  this.dbPort = dbPort;
127  this.sock_transport_properties = skT;
128  }
129 
130  public void clearMemo() {
131  SubqueryCorrMemo.clear();
132  }
133 
134  private static final Context DB_CONNECTION_CONTEXT = new Context() {
135  HeavyDBTypeSystem myTypeSystem = new HeavyDBTypeSystem();
136  CalciteConnectionConfig config = new CalciteConnectionConfigImpl(new Properties()) {
137  {
138  properties.put(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
139  String.valueOf(false));
140  properties.put(CalciteConnectionProperty.CONFORMANCE.camelName(),
141  String.valueOf(SqlConformanceEnum.LENIENT));
142  }
143 
144  @SuppressWarnings("unchecked")
145  public <T extends Object> T typeSystem(
146  java.lang.Class<T> typeSystemClass, T defaultTypeSystem) {
147  return (T) myTypeSystem;
148  };
149 
150  public boolean caseSensitive() {
151  return false;
152  };
153 
154  public org.apache.calcite.sql.validate.SqlConformance conformance() {
155  return SqlConformanceEnum.LENIENT;
156  };
157  };
158 
159  @Override
160  public <C> C unwrap(Class<C> aClass) {
161  if (aClass.isInstance(config)) {
162  return aClass.cast(config);
163  }
164  return null;
165  }
166  };
167 
169  return getPlanner(true, false, false);
170  }
171 
172  private boolean isCorrelated(SqlNode expression) {
173  String queryString = expression.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
174  Boolean isCorrelatedSubquery = SubqueryCorrMemo.get(queryString);
175  if (null != isCorrelatedSubquery) {
176  return isCorrelatedSubquery;
177  }
178 
179  try {
183  parser.setUser(dbUser);
184  parser.processSql(expression, options);
185  } catch (Exception e) {
186  // if we are not able to parse, then assume correlated
187  SubqueryCorrMemo.put(queryString, true);
188  return true;
189  }
190  SubqueryCorrMemo.put(queryString, false);
191  return false;
192  }
193 
194  private boolean isHashJoinableType(TColumnType type) {
195  switch (type.getCol_type().type) {
196  case TINYINT:
197  case SMALLINT:
198  case INT:
199  case BIGINT: {
200  return true;
201  }
202  case STR: {
203  return type.col_type.encoding == TEncodingType.DICT;
204  }
205  default: {
206  return false;
207  }
208  }
209  }
210 
211  private boolean isColumnHashJoinable(
212  List<String> joinColumnIdentifier, MetaConnect mc) {
213  try {
214  TTableDetails tableDetails = mc.get_table_details(joinColumnIdentifier.get(0));
215  return null
216  != tableDetails.row_desc.stream()
217  .filter(c
218  -> c.col_name.toLowerCase(Locale.ROOT)
219  .equals(joinColumnIdentifier.get(1)
220  .toLowerCase(
221  Locale.ROOT))
222  && isHashJoinableType(c))
223  .findFirst()
224  .orElse(null);
225  } catch (Exception e) {
226  return false;
227  }
228  }
229 
230  private HeavyDBPlanner getPlanner(final boolean allowSubQueryExpansion,
231  final boolean isWatchdogEnabled,
232  final boolean isDistributedMode) {
233  HeavyDBUser user = new HeavyDBUser(dbUser.getUser(),
234  dbUser.getSession(),
235  dbUser.getDB(),
236  -1,
237  ImmutableList.of());
238  final MetaConnect mc =
240  BiPredicate<SqlNode, SqlNode> expandPredicate = new BiPredicate<SqlNode, SqlNode>() {
241  @Override
242  public boolean test(SqlNode root, SqlNode expression) {
243  if (!allowSubQueryExpansion) {
244  return false;
245  }
246 
247  if (expression.isA(EXISTS) || expression.isA(IN)) {
248  // try to expand subquery by EXISTS and IN clauses by default
249  // note that current Calcite decorrelator fails to flat
250  // NOT-IN clause in some cases, so we do not decorrelate it for now
251 
252  if (expression.isA(IN)) {
253  // If we enable watchdog, we suffer from large projection exception in many
254  // cases since decorrelation needs de-duplication step which adds project -
255  // aggregate logic. And the added project is the source of the exception when
256  // its underlying table is large. Thus, we enable IN-clause decorrelation
257  // under watchdog iff we explicitly have correlated join in IN-clause
258  if (expression instanceof SqlCall) {
259  SqlCall outerSelectCall = (SqlCall) expression;
260  if (outerSelectCall.getOperandList().size() == 2) {
261  // if IN clause is correlated, its second operand of corresponding
262  // expression is SELECT clause which indicates a correlated subquery.
263  // Here, an expression "f.val IN (SELECT ...)" has two operands.
264  // Since we have interest in its subquery, so try to check whether
265  // the second operand, i.e., call.getOperandList().get(1)
266  // is a type of SqlSelect and also is correlated.
267  if (outerSelectCall.getOperandList().get(1) instanceof SqlSelect) {
268  // the below checking logic is to allow IN-clause decorrelation
269  // if it has hash joinable IN expression without correlated join
270  // i.e., SELECT ... WHERE a.intVal IN (SELECT b.intVal FROM b) ...;
271  SqlSelect innerSelectCall =
272  (SqlSelect) outerSelectCall.getOperandList().get(1);
273  if (innerSelectCall.hasWhere()) {
274  // IN-clause may have correlated join within subquery's WHERE clause
275  // i.e., f.val IN (SELECT r.val FROM R r WHERE f.val2 = r.val2)
276  // then we have to deccorrelate the IN-clause
277  JoinOperatorChecker joinOperatorChecker = new JoinOperatorChecker();
278  if (joinOperatorChecker.containsExpression(
279  innerSelectCall.getWhere())) {
280  return true;
281  }
282  }
283  if (isDistributedMode) {
284  // we temporarily disable IN-clause decorrelation in dist mode
285  // todo (yoonmin) : relax this in dist mode when available
286  return false;
287  }
288  boolean hasHashJoinableExpression = false;
289  if (isWatchdogEnabled) {
290  // when watchdog is enabled, we try to selectively allow decorrelation
291  // iff IN-expression is between two columns that both are hash
292  // joinable
293  Map<String, String> tableAliasMap = new HashMap<>();
294  if (root instanceof SqlSelect) {
295  tableAliasFinder(((SqlSelect) root).getFrom(), tableAliasMap);
296  }
297  tableAliasFinder(innerSelectCall.getFrom(), tableAliasMap);
298  if (outerSelectCall.getOperandList().get(0) instanceof SqlIdentifier
299  && innerSelectCall.getSelectList().get(0)
300  instanceof SqlIdentifier) {
301  SqlIdentifier outerColIdentifier =
302  (SqlIdentifier) outerSelectCall.getOperandList().get(0);
303  SqlIdentifier innerColIdentifier =
304  (SqlIdentifier) innerSelectCall.getSelectList().get(0);
305  if (tableAliasMap.containsKey(outerColIdentifier.names.get(0))
306  && tableAliasMap.containsKey(
307  innerColIdentifier.names.get(0))) {
308  String outerTableName =
309  tableAliasMap.get(outerColIdentifier.names.get(0));
310  String innerTableName =
311  tableAliasMap.get(innerColIdentifier.names.get(0));
312  if (isColumnHashJoinable(ImmutableList.of(outerTableName,
313  outerColIdentifier.names.get(1)),
314  mc)
316  ImmutableList.of(innerTableName,
317  innerColIdentifier.names.get(1)),
318  mc)) {
319  hasHashJoinableExpression = true;
320  }
321  }
322  }
323  if (!hasHashJoinableExpression) {
324  return false;
325  }
326  }
327  }
328  }
329  }
330  if (root instanceof SqlSelect) {
331  SqlSelect selectCall = (SqlSelect) root;
332  if (new ExpressionListedInSelectClauseChecker().containsExpression(
333  selectCall, expression)) {
334  // occasionally, Calcite cannot properly decorrelate IN-clause listed in
335  // SELECT clause e.g., SELECT x, CASE WHEN x in (SELECT x FROM R) ... FROM
336  // ... in that case we disable input query's decorrelation
337  return false;
338  }
339  if (null != selectCall.getWhere()) {
340  if (new ExpressionListedAsChildOROperatorChecker().containsExpression(
341  selectCall.getWhere(), expression)) {
342  // Decorrelation logic of the current Calcite cannot cover IN-clause
343  // well if it is listed as a child operand of OR-op
344  return false;
345  }
346  }
347  if (null != selectCall.getHaving()) {
348  if (new ExpressionListedAsChildOROperatorChecker().containsExpression(
349  selectCall.getHaving(), expression)) {
350  // Decorrelation logic of the current Calcite cannot cover IN-clause
351  // well if it is listed as a child operand of OR-op
352  return false;
353  }
354  }
355  }
356  }
357 
358  // otherwise, let's decorrelate the expression
359  return true;
360  }
361 
362  // special handling of sub-queries
363  if (expression.isA(SCALAR) && isCorrelated(expression)) {
364  // only expand if it is correlated.
365  SqlSelect select = null;
366  if (expression instanceof SqlCall) {
367  SqlCall call = (SqlCall) expression;
368  if (call.getOperator().equals(SqlStdOperatorTable.SCALAR_QUERY)) {
369  expression = call.getOperandList().get(0);
370  }
371  }
372 
373  if (expression instanceof SqlSelect) {
374  select = (SqlSelect) expression;
375  }
376 
377  if (null != select) {
378  if (null != select.getFetch() || null != select.getOffset()
379  || (null != select.getOrderList()
380  && select.getOrderList().size() != 0)) {
381  throw new CalciteException(
382  "Correlated sub-queries with ordering not supported.", null);
383  }
384  }
385  return true;
386  }
387 
388  // per default we do not want to expand
389  return false;
390  }
391  };
392 
393  final HeavyDBSchema defaultSchema = new HeavyDBSchema(
395  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
396  final SchemaPlus defaultSchemaPlus = rootSchema.add(dbUser.getDB(), defaultSchema);
397  for (String db : mc.getDatabases()) {
398  if (!db.equalsIgnoreCase(dbUser.getDB())) {
399  rootSchema.add(db,
400  new HeavyDBSchema(
402  }
403  }
404 
405  final FrameworkConfig config =
406  Frameworks.newConfigBuilder()
407  .defaultSchema(defaultSchemaPlus)
408  .operatorTable(dbSqlOperatorTable.get())
409  .parserConfig(SqlParser.configBuilder()
410  .setConformance(SqlConformanceEnum.LENIENT)
411  .setUnquotedCasing(Casing.UNCHANGED)
412  .setCaseSensitive(false)
413  // allow identifiers of up to 512 chars
414  .setIdentifierMaxLength(512)
415  .setParserFactory(ExtendedSqlParser.FACTORY)
416  .build())
417  .sqlToRelConverterConfig(
418  SqlToRelConverter
419  .configBuilder()
420  // enable sub-query expansion (de-correlation)
421  .withExpandPredicate(expandPredicate)
422  // allow as many as possible IN operator values
423  .withInSubQueryThreshold(Integer.MAX_VALUE)
424  .withHintStrategyTable(
426  .build())
427 
428  .typeSystem(createTypeSystem())
429  .context(DB_CONNECTION_CONTEXT)
430  .build();
431  HeavyDBPlanner planner = new HeavyDBPlanner(config);
432  planner.setRestrictions(dbUser.getRestrictions());
433  return planner;
434  }
435 
436  public void setUser(HeavyDBUser dbUser) {
437  this.dbUser = dbUser;
438  }
439 
440  public Pair<String, SqlIdentifierCapturer> process(
441  String sql, final HeavyDBParserOptions parserOptions)
442  throws SqlParseException, ValidationException, RelConversionException {
443  final HeavyDBPlanner planner = getPlanner(
444  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
445  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
446  String res = processSql(sqlNode, parserOptions);
447  SqlIdentifierCapturer capture = captureIdentifiers(sqlNode);
448  return new Pair<String, SqlIdentifierCapturer>(res, capture);
449  }
450 
452  String query, final HeavyDBParserOptions parserOptions) throws IOException {
453  HeavyDBSchema schema = new HeavyDBSchema(
455  HeavyDBPlanner planner = getPlanner(
456  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
457 
458  planner.setFilterPushDownInfo(parserOptions.getFilterPushDownInfo());
459  RelRoot optRel = planner.buildRATreeAndPerformQueryOptimization(query, schema);
460  optRel = replaceIsTrue(planner.getTypeFactory(), optRel);
461  return HeavyDBSerializer.toString(optRel.project());
462  }
463 
464  public String processSql(String sql, final HeavyDBParserOptions parserOptions)
465  throws SqlParseException, ValidationException, RelConversionException {
466  callCount++;
467 
468  final HeavyDBPlanner planner = getPlanner(
469  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
470  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
471 
472  return processSql(sqlNode, parserOptions);
473  }
474 
475  public String processSql(
476  final SqlNode sqlNode, final HeavyDBParserOptions parserOptions)
477  throws SqlParseException, ValidationException, RelConversionException {
478  callCount++;
479 
480  if (sqlNode instanceof JsonSerializableDdl) {
481  return ((JsonSerializableDdl) sqlNode).toJsonString();
482  }
483 
484  if (sqlNode instanceof SqlDdl) {
485  return sqlNode.toString();
486  }
487 
488  final HeavyDBPlanner planner = getPlanner(
489  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
490  planner.advanceToValidate();
491 
492  final RelRoot sqlRel = convertSqlToRelNode(sqlNode, planner, parserOptions);
493  RelNode project = sqlRel.project();
494 
495  if (parserOptions.isExplain()) {
496  return RelOptUtil.toString(sqlRel.project());
497  }
498 
499  String res = HeavyDBSerializer.toString(project);
500 
501  return res;
502  }
503 
504  public HeavyDBPlanner.CompletionResult getCompletionHints(
505  String sql, int cursor, List<String> visible_tables) {
506  return getPlanner().getCompletionHints(sql, cursor, visible_tables);
507  }
508 
509  public HashSet<ImmutableList<String>> resolveSelectIdentifiers(
510  SqlIdentifierCapturer capturer) {
511  HashSet<ImmutableList<String>> resolved = new HashSet<ImmutableList<String>>();
512 
513  for (ImmutableList<String> names : capturer.selects) {
514  HeavyDBSchema schema = new HeavyDBSchema(
515  dataDir, this, dbPort, dbUser, sock_transport_properties, names.get(1));
516  HeavyDBTable table = (HeavyDBTable) schema.getTable(names.get(0));
517  if (null == table) {
518  throw new RuntimeException("table/view not found: " + names.get(0));
519  }
520 
521  if (table instanceof HeavyDBView) {
522  HeavyDBView view = (HeavyDBView) table;
523  resolved.addAll(resolveSelectIdentifiers(view.getAccessedObjects()));
524  } else {
525  resolved.add(names);
526  }
527  }
528 
529  return resolved;
530  }
531 
532  private String getTableName(SqlNode node) {
533  if (node.isA(EnumSet.of(SqlKind.AS))) {
534  node = ((SqlCall) node).getOperandList().get(1);
535  }
536  if (node instanceof SqlIdentifier) {
537  SqlIdentifier id = (SqlIdentifier) node;
538  return id.names.get(id.names.size() - 1);
539  }
540  return null;
541  }
542 
543  private SqlSelect rewriteSimpleUpdateAsSelect(final SqlUpdate update) {
544  SqlNode where = update.getCondition();
545 
546  if (update.getSourceExpressionList().size() != 1) {
547  return null;
548  }
549 
550  if (!(update.getSourceExpressionList().get(0) instanceof SqlSelect)) {
551  return null;
552  }
553 
554  final SqlSelect inner = (SqlSelect) update.getSourceExpressionList().get(0);
555 
556  if (null != inner.getGroup() || null != inner.getFetch() || null != inner.getOffset()
557  || (null != inner.getOrderList() && inner.getOrderList().size() != 0)
558  || (null != inner.getGroup() && inner.getGroup().size() != 0)
559  || null == getTableName(inner.getFrom())) {
560  return null;
561  }
562 
563  if (!isCorrelated(inner)) {
564  return null;
565  }
566 
567  final String updateTableName = getTableName(update.getTargetTable());
568 
569  if (null != where) {
570  where = where.accept(new SqlShuttle() {
571  @Override
572  public SqlNode visit(SqlIdentifier id) {
573  if (id.isSimple()) {
574  id = new SqlIdentifier(Arrays.asList(updateTableName, id.getSimple()),
575  id.getParserPosition());
576  }
577 
578  return id;
579  }
580  });
581  }
582 
583  SqlJoin join = new SqlJoin(ZERO,
584  update.getTargetTable(),
585  SqlLiteral.createBoolean(false, ZERO),
586  SqlLiteral.createSymbol(JoinType.LEFT, ZERO),
587  inner.getFrom(),
588  SqlLiteral.createSymbol(JoinConditionType.ON, ZERO),
589  inner.getWhere());
590 
591  SqlNode select0 = inner.getSelectList().get(0);
592 
593  boolean wrapInSingleValue = true;
594  if (select0 instanceof SqlCall) {
595  SqlCall selectExprCall = (SqlCall) select0;
596  if (Util.isSingleValue(selectExprCall)) {
597  wrapInSingleValue = false;
598  }
599  }
600 
601  if (wrapInSingleValue) {
602  if (select0.isA(EnumSet.of(SqlKind.AS))) {
603  select0 = ((SqlCall) select0).getOperandList().get(0);
604  }
605  select0 = new SqlBasicCall(
606  SqlStdOperatorTable.SINGLE_VALUE, new SqlNode[] {select0}, ZERO);
607  }
608 
609  SqlNodeList selectList = new SqlNodeList(ZERO);
610  selectList.add(select0);
611  selectList.add(new SqlBasicCall(SqlStdOperatorTable.AS,
612  new SqlNode[] {new SqlBasicCall(
613  new SqlUnresolvedFunction(
614  new SqlIdentifier("OFFSET_IN_FRAGMENT", ZERO),
615  null,
616  null,
617  null,
618  null,
619  SqlFunctionCategory.USER_DEFINED_FUNCTION),
620  new SqlNode[0],
621  SqlParserPos.ZERO),
622  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
623  ZERO));
624 
625  SqlNodeList groupBy = new SqlNodeList(ZERO);
626  groupBy.add(new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO));
627 
628  SqlSelect select = new SqlSelect(ZERO,
629  null,
630  selectList,
631  join,
632  where,
633  groupBy,
634  null,
635  null,
636  null,
637  null,
638  null,
639  null);
640  return select;
641  }
642 
643  private LogicalTableModify getDummyUpdate(SqlUpdate update)
644  throws SqlParseException, ValidationException, RelConversionException {
645  SqlIdentifier targetTable = (SqlIdentifier) update.getTargetTable();
646  String targetTableName = targetTable.toString();
647  HeavyDBPlanner planner = getPlanner();
648  String dummySql = "DELETE FROM " + targetTableName;
649  SqlNode dummyNode = planner.parse(dummySql);
650  dummyNode = planner.validate(dummyNode);
651  RelRoot dummyRoot = planner.rel(dummyNode);
652  LogicalTableModify dummyModify = (LogicalTableModify) dummyRoot.rel;
653  return dummyModify;
654  }
655 
656  private RelRoot rewriteUpdateAsSelect(
657  SqlUpdate update, HeavyDBParserOptions parserOptions)
658  throws SqlParseException, ValidationException, RelConversionException {
659  int correlatedQueriesCount[] = new int[1];
660  SqlBasicVisitor<Void> correlatedQueriesCounter = new SqlBasicVisitor<Void>() {
661  @Override
662  public Void visit(SqlCall call) {
663  if (call.isA(SCALAR)
664  && ((call instanceof SqlBasicCall && call.operandCount() == 1
665  && !call.operand(0).isA(SCALAR))
666  || !(call instanceof SqlBasicCall))) {
667  if (isCorrelated(call)) {
668  correlatedQueriesCount[0]++;
669  }
670  }
671  return super.visit(call);
672  }
673  };
674 
675  update.accept(correlatedQueriesCounter);
676  if (correlatedQueriesCount[0] > 1) {
677  throw new CalciteException(
678  "table modifications with multiple correlated sub-queries not supported.",
679  null);
680  }
681 
682  boolean allowSubqueryDecorrelation = true;
683  SqlNode updateCondition = update.getCondition();
684  if (null != updateCondition) {
685  boolean hasInClause =
686  new FindSqlOperator().containsSqlOperator(updateCondition, SqlKind.IN);
687  if (hasInClause) {
688  SqlNode updateTargetTable = update.getTargetTable();
689  if (null != updateTargetTable && updateTargetTable instanceof SqlIdentifier) {
690  SqlIdentifier targetTable = (SqlIdentifier) updateTargetTable;
691  if (targetTable.names.size() == 2) {
692  final MetaConnect mc = new MetaConnect(dbPort,
693  dataDir,
694  dbUser,
695  this,
696  sock_transport_properties,
697  targetTable.names.get(0));
698  TTableDetails updateTargetTableDetails =
699  mc.get_table_details(targetTable.names.get(1));
700  if (null != updateTargetTableDetails
701  && updateTargetTableDetails.is_temporary) {
702  allowSubqueryDecorrelation = false;
703  }
704  }
705  }
706  }
707  }
708 
709  SqlNodeList sourceExpression = new SqlNodeList(SqlParserPos.ZERO);
710  LogicalTableModify dummyModify = getDummyUpdate(update);
711  RelOptTable targetTable = dummyModify.getTable();
712  RelDataType targetTableType = targetTable.getRowType();
713 
714  SqlSelect select = rewriteSimpleUpdateAsSelect(update);
715  boolean applyRexCast = null == select;
716 
717  if (null == select) {
718  for (int i = 0; i < update.getSourceExpressionList().size(); i++) {
719  SqlNode targetColumn = update.getTargetColumnList().get(i);
720  SqlNode expression = update.getSourceExpressionList().get(i);
721 
722  if (!(targetColumn instanceof SqlIdentifier)) {
723  throw new RuntimeException("Unknown identifier type!");
724  }
725  SqlIdentifier id = (SqlIdentifier) targetColumn;
726  RelDataType fieldType =
727  targetTableType.getField(id.names.get(id.names.size() - 1), false, false)
728  .getType();
729 
730  if (expression.isA(ARRAY_VALUE) && null != fieldType.getComponentType()) {
731  // apply a cast to all array value elements
732 
733  SqlDataTypeSpec elementType = new SqlDataTypeSpec(
734  new SqlBasicTypeNameSpec(fieldType.getComponentType().getSqlTypeName(),
735  fieldType.getPrecision(),
736  fieldType.getScale(),
737  null == fieldType.getCharset() ? null
738  : fieldType.getCharset().name(),
739  SqlParserPos.ZERO),
740  SqlParserPos.ZERO);
741  SqlCall array_expression = (SqlCall) expression;
742  ArrayList<SqlNode> values = new ArrayList<>();
743 
744  for (SqlNode value : array_expression.getOperandList()) {
745  if (value.isA(EnumSet.of(SqlKind.LITERAL))) {
746  SqlNode casted_value = new SqlBasicCall(SqlStdOperatorTable.CAST,
747  new SqlNode[] {value, elementType},
748  value.getParserPosition());
749  values.add(casted_value);
750  } else {
751  values.add(value);
752  }
753  }
754 
755  expression = new SqlBasicCall(HeavyDBSqlOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
756  values.toArray(new SqlNode[0]),
757  expression.getParserPosition());
758  }
759  sourceExpression.add(expression);
760  }
761 
762  sourceExpression.add(new SqlBasicCall(SqlStdOperatorTable.AS,
763  new SqlNode[] {
764  new SqlBasicCall(new SqlUnresolvedFunction(
765  new SqlIdentifier("OFFSET_IN_FRAGMENT",
766  SqlParserPos.ZERO),
767  null,
768  null,
769  null,
770  null,
771  SqlFunctionCategory.USER_DEFINED_FUNCTION),
772  new SqlNode[0],
773  SqlParserPos.ZERO),
774  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
775  ZERO));
776 
777  select = new SqlSelect(SqlParserPos.ZERO,
778  null,
779  sourceExpression,
780  update.getTargetTable(),
781  update.getCondition(),
782  null,
783  null,
784  null,
785  null,
786  null,
787  null,
788  null);
789  }
790 
791  HeavyDBPlanner planner = getPlanner(allowSubqueryDecorrelation,
792  parserOptions.isWatchdogEnabled(),
793  parserOptions.isDistributedMode());
794  SqlNode node = null;
795  try {
796  node = planner.parse(select.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
797  node = planner.validate(node);
798  } catch (Exception e) {
799  HEAVYDBLOGGER.error("Error processing UPDATE rewrite, rewritten stmt was: "
800  + select.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
801  throw e;
802  }
803 
804  RelRoot root = planner.rel(node);
805  LogicalProject project = (LogicalProject) root.project();
806 
807  ArrayList<String> fields = new ArrayList<String>();
808  ArrayList<RexNode> nodes = new ArrayList<RexNode>();
809  final RexBuilder builder = new RexBuilder(planner.getTypeFactory());
810 
811  for (SqlNode n : update.getTargetColumnList()) {
812  if (n instanceof SqlIdentifier) {
813  SqlIdentifier id = (SqlIdentifier) n;
814  fields.add(id.names.get(id.names.size() - 1));
815  } else {
816  throw new RuntimeException("Unknown identifier type!");
817  }
818  }
819 
820  // The magical number here when processing the projection
821  // is skipping the OFFSET_IN_FRAGMENT() expression used by
822  // update and delete
823  int idx = 0;
824  for (RexNode exp : project.getProjects()) {
825  if (applyRexCast && idx + 1 < project.getProjects().size()) {
826  RelDataType expectedFieldType =
827  targetTableType.getField(fields.get(idx), false, false).getType();
828  boolean is_array_kind = exp.isA(ARRAY_VALUE);
829  boolean is_func_kind = exp.isA(OTHER_FUNCTION);
830  // runtime functions have expression kind == OTHER_FUNCTION, even if they
831  // return an array
832  if (!exp.getType().equals(expectedFieldType)
833  && !(is_array_kind || is_func_kind)) {
834  exp = builder.makeCast(expectedFieldType, exp);
835  }
836  }
837 
838  nodes.add(exp);
839  idx++;
840  }
841 
842  ArrayList<RexNode> inputs = new ArrayList<RexNode>();
843  int n = 0;
844  for (int i = 0; i < fields.size(); i++) {
845  inputs.add(
846  new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
847  n++;
848  }
849 
850  fields.add("EXPR$DELETE_OFFSET_IN_FRAGMENT");
851  inputs.add(new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
852 
853  project = project.copy(
854  project.getTraitSet(), project.getInput(), nodes, project.getRowType());
855 
856  LogicalTableModify modify = LogicalTableModify.create(targetTable,
857  dummyModify.getCatalogReader(),
858  project,
859  Operation.UPDATE,
860  fields,
861  inputs,
862  true);
863  return RelRoot.of(modify, SqlKind.UPDATE);
864  }
865 
866  RelRoot queryToRelNode(final String sql, final HeavyDBParserOptions parserOptions)
867  throws SqlParseException, ValidationException, RelConversionException {
868  final HeavyDBPlanner planner = getPlanner(
869  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
870  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
871  return convertSqlToRelNode(sqlNode, planner, parserOptions);
872  }
873 
874  RelRoot convertSqlToRelNode(final SqlNode sqlNode,
876  final HeavyDBParserOptions parserOptions)
877  throws SqlParseException, ValidationException, RelConversionException {
878  SqlNode node = sqlNode;
879  HeavyDBPlanner planner = HeavyDBPlanner;
880  boolean allowCorrelatedSubQueryExpansion = true;
881  boolean patchUpdateToDelete = false;
882  if (node.isA(DELETE)) {
883  SqlDelete sqlDelete = (SqlDelete) node;
884  node = new SqlUpdate(node.getParserPosition(),
885  sqlDelete.getTargetTable(),
886  SqlNodeList.EMPTY,
887  SqlNodeList.EMPTY,
888  sqlDelete.getCondition(),
889  sqlDelete.getSourceSelect(),
890  sqlDelete.getAlias());
891 
892  patchUpdateToDelete = true;
893  }
894  if (node.isA(UPDATE)) {
895  SqlUpdate update = (SqlUpdate) node;
896  update = (SqlUpdate) planner.validate(update);
897  RelRoot root = rewriteUpdateAsSelect(update, parserOptions);
898 
899  if (patchUpdateToDelete) {
900  LogicalTableModify modify = (LogicalTableModify) root.rel;
901 
902  try {
903  Field f = TableModify.class.getDeclaredField("operation");
904  f.setAccessible(true);
905  f.set(modify, Operation.DELETE);
906  } catch (Throwable e) {
907  throw new RuntimeException(e);
908  }
909 
910  root = RelRoot.of(modify, SqlKind.DELETE);
911  }
912 
913  return root;
914  }
915  if (parserOptions.isLegacySyntax()) {
916  // close original planner
917  planner.close();
918  // create a new one
919  planner = getPlanner(allowCorrelatedSubQueryExpansion,
920  parserOptions.isWatchdogEnabled(),
921  parserOptions.isDistributedMode());
922  node = parseSql(
923  node.toSqlString(CalciteSqlDialect.DEFAULT).toString(), false, planner);
924  }
925 
926  SqlNode validateR = planner.validate(node);
927  planner.setFilterPushDownInfo(parserOptions.getFilterPushDownInfo());
928  // check to see if a view is involved in the query
929  boolean foundView = false;
930  SqlIdentifierCapturer capturer = captureIdentifiers(sqlNode);
931  for (ImmutableList<String> names : capturer.selects) {
932  HeavyDBSchema schema = new HeavyDBSchema(
933  dataDir, this, dbPort, dbUser, sock_transport_properties, names.get(1));
934  HeavyDBTable table = (HeavyDBTable) schema.getTable(names.get(0));
935  if (null == table) {
936  throw new RuntimeException("table/view not found: " + names.get(0));
937  }
938  if (table instanceof HeavyDBView) {
939  foundView = true;
940  }
941  }
942  RelRoot relRootNode = planner.getRelRoot(validateR);
943  relRootNode = replaceIsTrue(planner.getTypeFactory(), relRootNode);
944  RelNode rootNode = planner.optimizeRATree(
945  relRootNode.project(), parserOptions.isViewOptimizeEnabled(), foundView);
946  planner.close();
947  return new RelRoot(rootNode,
948  relRootNode.validatedRowType,
949  relRootNode.kind,
950  relRootNode.fields,
951  relRootNode.collation,
952  Collections.emptyList());
953  }
954 
955  private RelRoot replaceIsTrue(final RelDataTypeFactory typeFactory, RelRoot root) {
956  final RexShuttle callShuttle = new RexShuttle() {
957  RexBuilder builder = new RexBuilder(typeFactory);
958 
959  public RexNode visitCall(RexCall call) {
960  call = (RexCall) super.visitCall(call);
961  if (call.getKind() == SqlKind.IS_TRUE) {
962  return builder.makeCall(SqlStdOperatorTable.AND,
963  builder.makeCall(
964  SqlStdOperatorTable.IS_NOT_NULL, call.getOperands().get(0)),
965  call.getOperands().get(0));
966  } else if (call.getKind() == SqlKind.IS_NOT_TRUE) {
967  return builder.makeCall(SqlStdOperatorTable.OR,
968  builder.makeCall(
969  SqlStdOperatorTable.IS_NULL, call.getOperands().get(0)),
970  builder.makeCall(SqlStdOperatorTable.NOT, call.getOperands().get(0)));
971  } else if (call.getKind() == SqlKind.IS_FALSE) {
972  return builder.makeCall(SqlStdOperatorTable.AND,
973  builder.makeCall(
974  SqlStdOperatorTable.IS_NOT_NULL, call.getOperands().get(0)),
975  builder.makeCall(SqlStdOperatorTable.NOT, call.getOperands().get(0)));
976  } else if (call.getKind() == SqlKind.IS_NOT_FALSE) {
977  return builder.makeCall(SqlStdOperatorTable.OR,
978  builder.makeCall(
979  SqlStdOperatorTable.IS_NULL, call.getOperands().get(0)),
980  call.getOperands().get(0));
981  }
982 
983  return call;
984  }
985  };
986 
987  RelNode node = root.rel.accept(new RelShuttleImpl() {
988  @Override
989  protected RelNode visitChild(RelNode parent, int i, RelNode child) {
990  RelNode node = super.visitChild(parent, i, child);
991  return node.accept(callShuttle);
992  }
993  });
994 
995  return new RelRoot(node,
996  root.validatedRowType,
997  root.kind,
998  root.fields,
999  root.collation,
1000  Collections.emptyList());
1001  }
1002 
1003  private SqlNode parseSql(String sql, final boolean legacy_syntax, Planner planner)
1004  throws SqlParseException {
1005  SqlNode parseR = null;
1006  try {
1007  parseR = planner.parse(sql);
1008  HEAVYDBLOGGER.debug(" node is \n" + parseR.toString());
1009  } catch (SqlParseException ex) {
1010  HEAVYDBLOGGER.error("failed to parse SQL '" + sql + "' \n" + ex.toString());
1011  throw ex;
1012  }
1013 
1014  if (!legacy_syntax) {
1015  return parseR;
1016  }
1017 
1018  RelDataTypeFactory typeFactory = planner.getTypeFactory();
1019  SqlSelect select_node = null;
1020  if (parseR instanceof SqlSelect) {
1021  select_node = (SqlSelect) parseR;
1022  desugar(select_node, typeFactory);
1023  } else if (parseR instanceof SqlOrderBy) {
1024  SqlOrderBy order_by_node = (SqlOrderBy) parseR;
1025  if (order_by_node.query instanceof SqlSelect) {
1026  select_node = (SqlSelect) order_by_node.query;
1027  SqlOrderBy new_order_by_node = desugar(select_node, order_by_node, typeFactory);
1028  if (new_order_by_node != null) {
1029  return new_order_by_node;
1030  }
1031  } else if (order_by_node.query instanceof SqlWith) {
1032  SqlWith old_with_node = (SqlWith) order_by_node.query;
1033  if (old_with_node.body instanceof SqlSelect) {
1034  select_node = (SqlSelect) old_with_node.body;
1035  desugar(select_node, typeFactory);
1036  }
1037  }
1038  } else if (parseR instanceof SqlWith) {
1039  SqlWith old_with_node = (SqlWith) parseR;
1040  if (old_with_node.body instanceof SqlSelect) {
1041  select_node = (SqlSelect) old_with_node.body;
1042  desugar(select_node, typeFactory);
1043  }
1044  }
1045  return parseR;
1046  }
1047 
1048  private void desugar(SqlSelect select_node, RelDataTypeFactory typeFactory) {
1049  desugar(select_node, null, typeFactory);
1050  }
1051 
1052  private SqlNode expandCase(SqlCase old_case_node, RelDataTypeFactory typeFactory) {
1053  SqlNodeList newWhenList =
1054  new SqlNodeList(old_case_node.getWhenOperands().getParserPosition());
1055  SqlNodeList newThenList =
1056  new SqlNodeList(old_case_node.getThenOperands().getParserPosition());
1057  java.util.Map<String, SqlNode> id_to_expr = new java.util.HashMap<String, SqlNode>();
1058  for (SqlNode node : old_case_node.getWhenOperands()) {
1059  SqlNode newCall = expand(node, id_to_expr, typeFactory);
1060  if (null != newCall) {
1061  newWhenList.add(newCall);
1062  } else {
1063  newWhenList.add(node);
1064  }
1065  }
1066  for (SqlNode node : old_case_node.getThenOperands()) {
1067  SqlNode newCall = expand(node, id_to_expr, typeFactory);
1068  if (null != newCall) {
1069  newThenList.add(newCall);
1070  } else {
1071  newThenList.add(node);
1072  }
1073  }
1074  SqlNode new_else_operand = old_case_node.getElseOperand();
1075  if (null != new_else_operand) {
1076  SqlNode candidate_else_operand =
1077  expand(old_case_node.getElseOperand(), id_to_expr, typeFactory);
1078  if (null != candidate_else_operand) {
1079  new_else_operand = candidate_else_operand;
1080  }
1081  }
1082  SqlNode new_value_operand = old_case_node.getValueOperand();
1083  if (null != new_value_operand) {
1084  SqlNode candidate_value_operand =
1085  expand(old_case_node.getValueOperand(), id_to_expr, typeFactory);
1086  if (null != candidate_value_operand) {
1087  new_value_operand = candidate_value_operand;
1088  }
1089  }
1090  SqlNode newCaseNode = SqlCase.createSwitched(old_case_node.getParserPosition(),
1091  new_value_operand,
1092  newWhenList,
1093  newThenList,
1094  new_else_operand);
1095  return newCaseNode;
1096  }
1097 
1098  private SqlOrderBy desugar(SqlSelect select_node,
1099  SqlOrderBy order_by_node,
1100  RelDataTypeFactory typeFactory) {
1101  HEAVYDBLOGGER.debug("desugar: before: " + select_node.toString());
1102  desugarExpression(select_node.getFrom(), typeFactory);
1103  desugarExpression(select_node.getWhere(), typeFactory);
1104  SqlNodeList select_list = select_node.getSelectList();
1105  SqlNodeList new_select_list = new SqlNodeList(select_list.getParserPosition());
1106  java.util.Map<String, SqlNode> id_to_expr = new java.util.HashMap<String, SqlNode>();
1107  for (SqlNode proj : select_list) {
1108  if (!(proj instanceof SqlBasicCall)) {
1109  if (proj instanceof SqlCase) {
1110  new_select_list.add(expandCase((SqlCase) proj, typeFactory));
1111  } else {
1112  new_select_list.add(proj);
1113  }
1114  } else {
1115  assert proj instanceof SqlBasicCall;
1116  SqlBasicCall proj_call = (SqlBasicCall) proj;
1117  if (proj_call.operands.length > 0) {
1118  for (int i = 0; i < proj_call.operands.length; i++) {
1119  if (proj_call.operand(i) instanceof SqlCase) {
1120  SqlNode new_op = expandCase(proj_call.operand(i), typeFactory);
1121  proj_call.setOperand(i, new_op);
1122  }
1123  }
1124  }
1125  new_select_list.add(expand(proj_call, id_to_expr, typeFactory));
1126  }
1127  }
1128  select_node.setSelectList(new_select_list);
1129  SqlNodeList group_by_list = select_node.getGroup();
1130  if (group_by_list != null) {
1131  select_node.setGroupBy(expand(group_by_list, id_to_expr, typeFactory));
1132  }
1133  SqlNode having = select_node.getHaving();
1134  if (having != null) {
1135  expand(having, id_to_expr, typeFactory);
1136  }
1137  SqlOrderBy new_order_by_node = null;
1138  if (order_by_node != null && order_by_node.orderList != null
1139  && order_by_node.orderList.size() > 0) {
1140  SqlNodeList new_order_by_list =
1141  expand(order_by_node.orderList, id_to_expr, typeFactory);
1142  new_order_by_node = new SqlOrderBy(order_by_node.getParserPosition(),
1143  select_node,
1144  new_order_by_list,
1145  order_by_node.offset,
1146  order_by_node.fetch);
1147  }
1148 
1149  HEAVYDBLOGGER.debug("desugar: after: " + select_node.toString());
1150  return new_order_by_node;
1151  }
1152 
1153  private void desugarExpression(SqlNode node, RelDataTypeFactory typeFactory) {
1154  if (node instanceof SqlSelect) {
1155  desugar((SqlSelect) node, typeFactory);
1156  return;
1157  }
1158  if (!(node instanceof SqlBasicCall)) {
1159  return;
1160  }
1161  SqlBasicCall basic_call = (SqlBasicCall) node;
1162  for (SqlNode operator : basic_call.getOperands()) {
1163  if (operator instanceof SqlOrderBy) {
1164  desugarExpression(((SqlOrderBy) operator).query, typeFactory);
1165  } else {
1166  desugarExpression(operator, typeFactory);
1167  }
1168  }
1169  }
1170 
1171  private SqlNode expand(final SqlNode node,
1172  final java.util.Map<String, SqlNode> id_to_expr,
1173  RelDataTypeFactory typeFactory) {
1174  HEAVYDBLOGGER.debug("expand: " + node.toString());
1175  if (node instanceof SqlBasicCall) {
1176  SqlBasicCall node_call = (SqlBasicCall) node;
1177  SqlNode[] operands = node_call.getOperands();
1178  for (int i = 0; i < operands.length; ++i) {
1179  node_call.setOperand(i, expand(operands[i], id_to_expr, typeFactory));
1180  }
1181  SqlNode expanded_string_function = expandStringFunctions(node_call, typeFactory);
1182  if (expanded_string_function != null) {
1183  return expanded_string_function;
1184  }
1185  SqlNode expanded_variance = expandVariance(node_call, typeFactory);
1186  if (expanded_variance != null) {
1187  return expanded_variance;
1188  }
1189  SqlNode expanded_covariance = expandCovariance(node_call, typeFactory);
1190  if (expanded_covariance != null) {
1191  return expanded_covariance;
1192  }
1193  SqlNode expanded_correlation = expandCorrelation(node_call, typeFactory);
1194  if (expanded_correlation != null) {
1195  return expanded_correlation;
1196  }
1197  }
1198  if (node instanceof SqlSelect) {
1199  SqlSelect select_node = (SqlSelect) node;
1200  desugar(select_node, typeFactory);
1201  }
1202  return node;
1203  }
1204 
1205  private SqlNodeList expand(final SqlNodeList group_by_list,
1206  final java.util.Map<String, SqlNode> id_to_expr,
1207  RelDataTypeFactory typeFactory) {
1208  SqlNodeList new_group_by_list = new SqlNodeList(new SqlParserPos(-1, -1));
1209  for (SqlNode group_by : group_by_list) {
1210  if (!(group_by instanceof SqlIdentifier)) {
1211  new_group_by_list.add(expand(group_by, id_to_expr, typeFactory));
1212  continue;
1213  }
1214  SqlIdentifier group_by_id = ((SqlIdentifier) group_by);
1215  if (id_to_expr.containsKey(group_by_id.toString())) {
1216  new_group_by_list.add(id_to_expr.get(group_by_id.toString()));
1217  } else {
1218  new_group_by_list.add(group_by);
1219  }
1220  }
1221  return new_group_by_list;
1222  }
1223 
1224  private SqlNode expandStringFunctions(
1225  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1226  //
1227  // Expand string functions
1228  //
1229 
1230  final int operandCount = proj_call.operandCount();
1231 
1232  if (proj_call.getOperator().isName("MID", false)
1233  || proj_call.getOperator().isName("SUBSTR", false)) {
1234  // Replace MID/SUBSTR with SUBSTRING
1235  //
1236  // Note: SUBSTRING doesn't offer much flexibility for the numeric arg's type
1237  // "only constant, column, or other string operator arguments are allowed"
1238  final SqlParserPos pos = proj_call.getParserPosition();
1239  if (operandCount == 2) {
1240  final SqlNode primary_operand = proj_call.operand(0);
1241  final SqlNode from_operand = proj_call.operand(1);
1242  return SqlStdOperatorTable.SUBSTRING.createCall(
1243  pos, primary_operand, from_operand);
1244 
1245  } else if (operandCount == 3) {
1246  final SqlNode primary_operand = proj_call.operand(0);
1247  final SqlNode from_operand = proj_call.operand(1);
1248  final SqlNode for_operand = proj_call.operand(2);
1249  return SqlStdOperatorTable.SUBSTRING.createCall(
1250  pos, primary_operand, from_operand, for_operand);
1251  }
1252  return null;
1253 
1254  } else if (proj_call.getOperator().isName("CONTAINS", false)) {
1255  // Replace CONTAINS with LIKE
1256  // as noted by TABLEAU's own published documention
1257  final SqlParserPos pos = proj_call.getParserPosition();
1258  if (operandCount == 2) {
1259  final SqlNode primary = proj_call.operand(0);
1260  final SqlNode pattern = proj_call.operand(1);
1261 
1262  if (pattern instanceof SqlLiteral) {
1263  // LIKE only supports Literal patterns ... at the moment
1264  SqlLiteral literalPattern = (SqlLiteral) pattern;
1265  String sPattern = literalPattern.getValueAs(String.class);
1266  SqlLiteral withWildcards =
1267  SqlLiteral.createCharString("%" + sPattern + "%", pos);
1268  return SqlStdOperatorTable.LIKE.createCall(pos, primary, withWildcards);
1269  }
1270  }
1271  return null;
1272 
1273  } else if (proj_call.getOperator().isName("ENDSWITH", false)) {
1274  // Replace ENDSWITH with LIKE
1275  final SqlParserPos pos = proj_call.getParserPosition();
1276  if (operandCount == 2) {
1277  final SqlNode primary = proj_call.operand(0);
1278  final SqlNode pattern = proj_call.operand(1);
1279 
1280  if (pattern instanceof SqlLiteral) {
1281  // LIKE only supports Literal patterns ... at the moment
1282  SqlLiteral literalPattern = (SqlLiteral) pattern;
1283  String sPattern = literalPattern.getValueAs(String.class);
1284  SqlLiteral withWildcards = SqlLiteral.createCharString("%" + sPattern, pos);
1285  return SqlStdOperatorTable.LIKE.createCall(pos, primary, withWildcards);
1286  }
1287  }
1288  return null;
1289  } else if (proj_call.getOperator().isName("LCASE", false)) {
1290  // Expand LCASE with LOWER
1291  final SqlParserPos pos = proj_call.getParserPosition();
1292  if (operandCount == 1) {
1293  final SqlNode primary = proj_call.operand(0);
1294  return SqlStdOperatorTable.LOWER.createCall(pos, primary);
1295  }
1296  return null;
1297 
1298  } else if (proj_call.getOperator().isName("LEFT", false)) {
1299  // Replace LEFT with SUBSTRING
1300  final SqlParserPos pos = proj_call.getParserPosition();
1301 
1302  if (operandCount == 2) {
1303  final SqlNode primary = proj_call.operand(0);
1304  SqlNode start = SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO);
1305  final SqlNode count = proj_call.operand(1);
1306  return SqlStdOperatorTable.SUBSTRING.createCall(pos, primary, start, count);
1307  }
1308  return null;
1309 
1310  } else if (proj_call.getOperator().isName("LEN", false)) {
1311  // Replace LEN with CHARACTER_LENGTH
1312  final SqlParserPos pos = proj_call.getParserPosition();
1313  if (operandCount == 1) {
1314  final SqlNode primary = proj_call.operand(0);
1315  return SqlStdOperatorTable.CHARACTER_LENGTH.createCall(pos, primary);
1316  }
1317  return null;
1318 
1319  } else if (proj_call.getOperator().isName("MAX", false)
1320  || proj_call.getOperator().isName("MIN", false)) {
1321  // Replace MAX(a,b), MIN(a,b) with CASE
1322  final SqlParserPos pos = proj_call.getParserPosition();
1323 
1324  if (operandCount == 2) {
1325  final SqlNode arg1 = proj_call.operand(0);
1326  final SqlNode arg2 = proj_call.operand(1);
1327 
1328  SqlNodeList whenList = new SqlNodeList(pos);
1329  SqlNodeList thenList = new SqlNodeList(pos);
1330  SqlNodeList elseClause = new SqlNodeList(pos);
1331 
1332  if (proj_call.getOperator().isName("MAX", false)) {
1333  whenList.add(
1334  SqlStdOperatorTable.GREATER_THAN_OR_EQUAL.createCall(pos, arg1, arg2));
1335  } else {
1336  whenList.add(
1337  SqlStdOperatorTable.LESS_THAN_OR_EQUAL.createCall(pos, arg1, arg2));
1338  }
1339  thenList.add(arg1);
1340  elseClause.add(arg2);
1341 
1342  SqlNode caseIdentifier = null;
1343  return SqlCase.createSwitched(
1344  pos, caseIdentifier, whenList, thenList, elseClause);
1345  }
1346  return null;
1347 
1348  } else if (proj_call.getOperator().isName("RIGHT", false)) {
1349  // Replace RIGHT with SUBSTRING
1350  final SqlParserPos pos = proj_call.getParserPosition();
1351 
1352  if (operandCount == 2) {
1353  final SqlNode primary = proj_call.operand(0);
1354  final SqlNode count = proj_call.operand(1);
1355  if (count instanceof SqlNumericLiteral) {
1356  SqlNumericLiteral numericCount = (SqlNumericLiteral) count;
1357  if (numericCount.intValue(true) > 0) {
1358  // common case
1359  final SqlNode negativeCount =
1360  SqlNumericLiteral.createNegative(numericCount, pos);
1361  return SqlStdOperatorTable.SUBSTRING.createCall(pos, primary, negativeCount);
1362  }
1363  // allow zero (or negative) to return an empty string
1364  // matches behavior of LEFT
1365  SqlNode zero = SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO);
1366  return SqlStdOperatorTable.SUBSTRING.createCall(pos, primary, zero, zero);
1367  }
1368  // if not a simple literal ... attempt to evaluate
1369  // expected to fail ... with a useful error message
1370  return SqlStdOperatorTable.SUBSTRING.createCall(pos, primary, count);
1371  }
1372  return null;
1373 
1374  } else if (proj_call.getOperator().isName("SPACE", false)) {
1375  // Replace SPACE with REPEAT
1376  final SqlParserPos pos = proj_call.getParserPosition();
1377  if (operandCount == 1) {
1378  final SqlNode count = proj_call.operand(0);
1379  SqlFunction fn_repeat = new SqlFunction("REPEAT",
1380  SqlKind.OTHER_FUNCTION,
1381  ReturnTypes.ARG0_NULLABLE,
1382  null,
1383  OperandTypes.CHARACTER,
1384  SqlFunctionCategory.STRING);
1385  SqlLiteral space = SqlLiteral.createCharString(" ", pos);
1386  return fn_repeat.createCall(pos, space, count);
1387  }
1388  return null;
1389 
1390  } else if (proj_call.getOperator().isName("SPLIT", false)) {
1391  // Replace SPLIT with SPLIT_PART
1392  final SqlParserPos pos = proj_call.getParserPosition();
1393  if (operandCount == 3) {
1394  final SqlNode primary = proj_call.operand(0);
1395  final SqlNode delimeter = proj_call.operand(1);
1396  final SqlNode count = proj_call.operand(2);
1397  SqlFunction fn_split = new SqlFunction("SPLIT_PART",
1398  SqlKind.OTHER_FUNCTION,
1399  ReturnTypes.ARG0_NULLABLE,
1400  null,
1401  OperandTypes.CHARACTER,
1402  SqlFunctionCategory.STRING);
1403 
1404  return fn_split.createCall(pos, primary, delimeter, count);
1405  }
1406  return null;
1407 
1408  } else if (proj_call.getOperator().isName("STARTSWITH", false)) {
1409  // Replace STARTSWITH with LIKE
1410  final SqlParserPos pos = proj_call.getParserPosition();
1411  if (operandCount == 2) {
1412  final SqlNode primary = proj_call.operand(0);
1413  final SqlNode pattern = proj_call.operand(1);
1414 
1415  if (pattern instanceof SqlLiteral) {
1416  // LIKE only supports Literal patterns ... at the moment
1417  SqlLiteral literalPattern = (SqlLiteral) pattern;
1418  String sPattern = literalPattern.getValueAs(String.class);
1419  SqlLiteral withWildcards = SqlLiteral.createCharString(sPattern + "%", pos);
1420  return SqlStdOperatorTable.LIKE.createCall(pos, primary, withWildcards);
1421  }
1422  }
1423  return null;
1424 
1425  } else if (proj_call.getOperator().isName("UCASE", false)) {
1426  // Replace UCASE with UPPER
1427  final SqlParserPos pos = proj_call.getParserPosition();
1428  if (operandCount == 1) {
1429  final SqlNode primary = proj_call.operand(0);
1430  return SqlStdOperatorTable.UPPER.createCall(pos, primary);
1431  }
1432  return null;
1433  }
1434 
1435  return null;
1436  }
1437 
1438  private SqlNode expandVariance(
1439  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1440  // Expand variance aggregates that are not supported natively
1441  if (proj_call.operandCount() != 1) {
1442  return null;
1443  }
1444  boolean biased;
1445  boolean sqrt;
1446  boolean flt;
1447  if (proj_call.getOperator().isName("STDDEV_POP", false)) {
1448  biased = true;
1449  sqrt = true;
1450  flt = false;
1451  } else if (proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_POP_FLOAT")) {
1452  biased = true;
1453  sqrt = true;
1454  flt = true;
1455  } else if (proj_call.getOperator().isName("STDDEV_SAMP", false)
1456  || proj_call.getOperator().getName().equalsIgnoreCase("STDDEV")) {
1457  biased = false;
1458  sqrt = true;
1459  flt = false;
1460  } else if (proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_SAMP_FLOAT")
1461  || proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_FLOAT")) {
1462  biased = false;
1463  sqrt = true;
1464  flt = true;
1465  } else if (proj_call.getOperator().isName("VAR_POP", false)) {
1466  biased = true;
1467  sqrt = false;
1468  flt = false;
1469  } else if (proj_call.getOperator().getName().equalsIgnoreCase("VAR_POP_FLOAT")) {
1470  biased = true;
1471  sqrt = false;
1472  flt = true;
1473  } else if (proj_call.getOperator().isName("VAR_SAMP", false)
1474  || proj_call.getOperator().getName().equalsIgnoreCase("VARIANCE")) {
1475  biased = false;
1476  sqrt = false;
1477  flt = false;
1478  } else if (proj_call.getOperator().getName().equalsIgnoreCase("VAR_SAMP_FLOAT")
1479  || proj_call.getOperator().getName().equalsIgnoreCase("VARIANCE_FLOAT")) {
1480  biased = false;
1481  sqrt = false;
1482  flt = true;
1483  } else {
1484  return null;
1485  }
1486  final SqlNode operand = proj_call.operand(0);
1487  final SqlParserPos pos = proj_call.getParserPosition();
1488  SqlNode expanded_proj_call =
1489  expandVariance(pos, operand, biased, sqrt, flt, typeFactory);
1490  HEAVYDBLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1491  HEAVYDBLOGGER.debug("to : " + expanded_proj_call.toString());
1492  return expanded_proj_call;
1493  }
1494 
1495  private SqlNode expandVariance(final SqlParserPos pos,
1496  final SqlNode operand,
1497  boolean biased,
1498  boolean sqrt,
1499  boolean flt,
1500  RelDataTypeFactory typeFactory) {
1501  // stddev_pop(x) ==>
1502  // power(
1503  // (sum(x * x) - sum(x) * sum(x) / (case count(x) when 0 then NULL else count(x)
1504  // end)) / (case count(x) when 0 then NULL else count(x) end), .5)
1505  //
1506  // stddev_samp(x) ==>
1507  // power(
1508  // (sum(x * x) - sum(x) * sum(x) / (case count(x) when 0 then NULL else count(x)
1509  // )) / ((case count(x) when 1 then NULL else count(x) - 1 end)), .5)
1510  //
1511  // var_pop(x) ==>
1512  // (sum(x * x) - sum(x) * sum(x) / ((case count(x) when 0 then NULL else
1513  // count(x)
1514  // end))) / ((case count(x) when 0 then NULL else count(x) end))
1515  //
1516  // var_samp(x) ==>
1517  // (sum(x * x) - sum(x) * sum(x) / ((case count(x) when 0 then NULL else
1518  // count(x)
1519  // end))) / ((case count(x) when 1 then NULL else count(x) - 1 end))
1520  //
1521  final SqlNode arg = SqlStdOperatorTable.CAST.createCall(pos,
1522  operand,
1523  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1524  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1525  final SqlNode argSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, arg, arg);
1526  final SqlNode sumArgSquared = SqlStdOperatorTable.SUM.createCall(pos, argSquared);
1527  final SqlNode sum = SqlStdOperatorTable.SUM.createCall(pos, arg);
1528  final SqlNode sumSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, sum, sum);
1529  final SqlNode count = SqlStdOperatorTable.COUNT.createCall(pos, arg);
1530  final SqlLiteral nul = SqlLiteral.createNull(pos);
1531  final SqlNumericLiteral zero = SqlLiteral.createExactNumeric("0", pos);
1532  final SqlNode countEqZero = SqlStdOperatorTable.EQUALS.createCall(pos, count, zero);
1533  SqlNodeList whenList = new SqlNodeList(pos);
1534  SqlNodeList thenList = new SqlNodeList(pos);
1535  whenList.add(countEqZero);
1536  thenList.add(nul);
1537  final SqlNode int_denominator = SqlStdOperatorTable.CASE.createCall(
1538  null, pos, null, whenList, thenList, count);
1539  final SqlNode denominator = SqlStdOperatorTable.CAST.createCall(pos,
1540  int_denominator,
1541  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1542  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1543  final SqlNode avgSumSquared =
1544  SqlStdOperatorTable.DIVIDE.createCall(pos, sumSquared, denominator);
1545  final SqlNode diff =
1546  SqlStdOperatorTable.MINUS.createCall(pos, sumArgSquared, avgSumSquared);
1547  final SqlNode denominator1;
1548  if (biased) {
1549  denominator1 = denominator;
1550  } else {
1551  final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
1552  final SqlNode countEqOne = SqlStdOperatorTable.EQUALS.createCall(pos, count, one);
1553  final SqlNode countMinusOne = SqlStdOperatorTable.MINUS.createCall(pos, count, one);
1554  SqlNodeList whenList1 = new SqlNodeList(pos);
1555  SqlNodeList thenList1 = new SqlNodeList(pos);
1556  whenList1.add(countEqOne);
1557  thenList1.add(nul);
1558  final SqlNode int_denominator1 = SqlStdOperatorTable.CASE.createCall(
1559  null, pos, null, whenList1, thenList1, countMinusOne);
1560  denominator1 = SqlStdOperatorTable.CAST.createCall(pos,
1561  int_denominator1,
1562  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1563  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1564  }
1565  final SqlNode div = SqlStdOperatorTable.DIVIDE.createCall(pos, diff, denominator1);
1566  SqlNode result = div;
1567  if (sqrt) {
1568  final SqlNumericLiteral half = SqlLiteral.createExactNumeric("0.5", pos);
1569  result = SqlStdOperatorTable.POWER.createCall(pos, div, half);
1570  }
1571  return SqlStdOperatorTable.CAST.createCall(pos,
1572  result,
1573  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1574  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1575  }
1576 
1577  private SqlNode expandCovariance(
1578  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1579  // Expand covariance aggregates
1580  if (proj_call.operandCount() != 2) {
1581  return null;
1582  }
1583  boolean pop;
1584  boolean flt;
1585  if (proj_call.getOperator().isName("COVAR_POP", false)) {
1586  pop = true;
1587  flt = false;
1588  } else if (proj_call.getOperator().isName("COVAR_SAMP", false)) {
1589  pop = false;
1590  flt = false;
1591  } else if (proj_call.getOperator().getName().equalsIgnoreCase("COVAR_POP_FLOAT")) {
1592  pop = true;
1593  flt = true;
1594  } else if (proj_call.getOperator().getName().equalsIgnoreCase("COVAR_SAMP_FLOAT")) {
1595  pop = false;
1596  flt = true;
1597  } else {
1598  return null;
1599  }
1600  final SqlNode operand0 = proj_call.operand(0);
1601  final SqlNode operand1 = proj_call.operand(1);
1602  final SqlParserPos pos = proj_call.getParserPosition();
1603  SqlNode expanded_proj_call =
1604  expandCovariance(pos, operand0, operand1, pop, flt, typeFactory);
1605  HEAVYDBLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1606  HEAVYDBLOGGER.debug("to : " + expanded_proj_call.toString());
1607  return expanded_proj_call;
1608  }
1609 
1610  private SqlNode expandCovariance(SqlParserPos pos,
1611  final SqlNode operand0,
1612  final SqlNode operand1,
1613  boolean pop,
1614  boolean flt,
1615  RelDataTypeFactory typeFactory) {
1616  // covar_pop(x, y) ==> avg(x * y) - avg(x) * avg(y)
1617  // covar_samp(x, y) ==> (sum(x * y) - sum(x) * avg(y))
1618  // ((case count(x) when 1 then NULL else count(x) - 1 end))
1619  final SqlNode arg0 = SqlStdOperatorTable.CAST.createCall(operand0.getParserPosition(),
1620  operand0,
1621  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1622  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1623  final SqlNode arg1 = SqlStdOperatorTable.CAST.createCall(operand1.getParserPosition(),
1624  operand1,
1625  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1626  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1627  final SqlNode mulArg = SqlStdOperatorTable.MULTIPLY.createCall(pos, arg0, arg1);
1628  final SqlNode avgArg1 = SqlStdOperatorTable.AVG.createCall(pos, arg1);
1629  if (pop) {
1630  final SqlNode avgMulArg = SqlStdOperatorTable.AVG.createCall(pos, mulArg);
1631  final SqlNode avgArg0 = SqlStdOperatorTable.AVG.createCall(pos, arg0);
1632  final SqlNode mulAvgAvg =
1633  SqlStdOperatorTable.MULTIPLY.createCall(pos, avgArg0, avgArg1);
1634  final SqlNode covarPop =
1635  SqlStdOperatorTable.MINUS.createCall(pos, avgMulArg, mulAvgAvg);
1636  return SqlStdOperatorTable.CAST.createCall(pos,
1637  covarPop,
1638  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1639  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1640  }
1641  final SqlNode sumMulArg = SqlStdOperatorTable.SUM.createCall(pos, mulArg);
1642  final SqlNode sumArg0 = SqlStdOperatorTable.SUM.createCall(pos, arg0);
1643  final SqlNode mulSumAvg =
1644  SqlStdOperatorTable.MULTIPLY.createCall(pos, sumArg0, avgArg1);
1645  final SqlNode sub = SqlStdOperatorTable.MINUS.createCall(pos, sumMulArg, mulSumAvg);
1646  final SqlNode count = SqlStdOperatorTable.COUNT.createCall(pos, operand0);
1647  final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
1648  final SqlNode countEqOne = SqlStdOperatorTable.EQUALS.createCall(pos, count, one);
1649  final SqlNode countMinusOne = SqlStdOperatorTable.MINUS.createCall(pos, count, one);
1650  final SqlLiteral nul = SqlLiteral.createNull(pos);
1651  SqlNodeList whenList1 = new SqlNodeList(pos);
1652  SqlNodeList thenList1 = new SqlNodeList(pos);
1653  whenList1.add(countEqOne);
1654  thenList1.add(nul);
1655  final SqlNode int_denominator = SqlStdOperatorTable.CASE.createCall(
1656  null, pos, null, whenList1, thenList1, countMinusOne);
1657  final SqlNode denominator = SqlStdOperatorTable.CAST.createCall(pos,
1658  int_denominator,
1659  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1660  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1661  final SqlNode covarSamp =
1662  SqlStdOperatorTable.DIVIDE.createCall(pos, sub, denominator);
1663  return SqlStdOperatorTable.CAST.createCall(pos,
1664  covarSamp,
1665  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1666  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1667  }
1668 
1669  private SqlNode expandCorrelation(
1670  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1671  // Expand correlation coefficient
1672  if (proj_call.operandCount() != 2) {
1673  return null;
1674  }
1675  boolean flt;
1676  if (proj_call.getOperator().isName("CORR", false)
1677  || proj_call.getOperator().getName().equalsIgnoreCase("CORRELATION")) {
1678  // expand correlation coefficient
1679  flt = false;
1680  } else if (proj_call.getOperator().getName().equalsIgnoreCase("CORR_FLOAT")
1681  || proj_call.getOperator().getName().equalsIgnoreCase("CORRELATION_FLOAT")) {
1682  // expand correlation coefficient
1683  flt = true;
1684  } else {
1685  return null;
1686  }
1687  // corr(x, y) ==> (avg(x * y) - avg(x) * avg(y)) / (stddev_pop(x) *
1688  // stddev_pop(y))
1689  // ==> covar_pop(x, y) / (stddev_pop(x) * stddev_pop(y))
1690  final SqlNode operand0 = proj_call.operand(0);
1691  final SqlNode operand1 = proj_call.operand(1);
1692  final SqlParserPos pos = proj_call.getParserPosition();
1693  SqlNode covariance =
1694  expandCovariance(pos, operand0, operand1, true, flt, typeFactory);
1695  SqlNode stddev0 = expandVariance(pos, operand0, true, true, flt, typeFactory);
1696  SqlNode stddev1 = expandVariance(pos, operand1, true, true, flt, typeFactory);
1697  final SqlNode mulStddev =
1698  SqlStdOperatorTable.MULTIPLY.createCall(pos, stddev0, stddev1);
1699  final SqlNumericLiteral zero = SqlLiteral.createExactNumeric("0.0", pos);
1700  final SqlNode mulStddevEqZero =
1701  SqlStdOperatorTable.EQUALS.createCall(pos, mulStddev, zero);
1702  final SqlLiteral nul = SqlLiteral.createNull(pos);
1703  SqlNodeList whenList1 = new SqlNodeList(pos);
1704  SqlNodeList thenList1 = new SqlNodeList(pos);
1705  whenList1.add(mulStddevEqZero);
1706  thenList1.add(nul);
1707  final SqlNode denominator = SqlStdOperatorTable.CASE.createCall(
1708  null, pos, null, whenList1, thenList1, mulStddev);
1709  final SqlNode expanded_proj_call =
1710  SqlStdOperatorTable.DIVIDE.createCall(pos, covariance, denominator);
1711  HEAVYDBLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1712  HEAVYDBLOGGER.debug("to : " + expanded_proj_call.toString());
1713  return expanded_proj_call;
1714  }
1715 
1716  public SqlIdentifierCapturer captureIdentifiers(String sql, boolean legacy_syntax)
1717  throws SqlParseException {
1718  try {
1719  Planner planner = getPlanner();
1720  SqlNode node = parseSql(sql, legacy_syntax, planner);
1721  return captureIdentifiers(node);
1722  } catch (Exception | Error e) {
1723  HEAVYDBLOGGER.error("Error parsing sql: " + sql, e);
1724  return new SqlIdentifierCapturer();
1725  }
1726  }
1727 
1728  public SqlIdentifierCapturer captureIdentifiers(SqlNode node) throws SqlParseException {
1729  try {
1731  capturer.scan(node);
1732  capturer.selects = addDbContextIfMissing(capturer.selects);
1733  capturer.updates = addDbContextIfMissing(capturer.updates);
1734  capturer.deletes = addDbContextIfMissing(capturer.deletes);
1735  capturer.inserts = addDbContextIfMissing(capturer.inserts);
1736  return capturer;
1737  } catch (Exception | Error e) {
1738  HEAVYDBLOGGER.error("Error parsing sql: " + node, e);
1739  return new SqlIdentifierCapturer();
1740  }
1741  }
1742 
1743  private Set<ImmutableList<String>> addDbContextIfMissing(
1744  Set<ImmutableList<String>> names) {
1745  Set<ImmutableList<String>> result = new HashSet<>();
1746  for (ImmutableList<String> name : names) {
1747  if (name.size() == 1) {
1748  result.add(new ImmutableList.Builder<String>()
1749  .addAll(name)
1750  .add(dbUser.getDB())
1751  .build());
1752  } else {
1753  result.add(name);
1754  }
1755  }
1756  return result;
1757  }
1758 
1759  public int getCallCount() {
1760  return callCount;
1761  }
1762 
1763  public void updateMetaData(String schema, String table) {
1764  HEAVYDBLOGGER.debug("schema :" + schema + " table :" + table);
1765  HeavyDBSchema db = new HeavyDBSchema(
1766  dataDir, this, dbPort, null, sock_transport_properties, schema);
1767  db.updateMetaData(schema, table);
1768  }
1769 
1770  protected RelDataTypeSystem createTypeSystem() {
1771  final HeavyDBTypeSystem typeSystem = new HeavyDBTypeSystem();
1772  return typeSystem;
1773  }
1774 
1776  extends SqlBasicVisitor<Void> {
1777  @Override
1778  public Void visit(SqlCall call) {
1779  if (call instanceof SqlSelect) {
1780  SqlSelect selectNode = (SqlSelect) call;
1781  String targetString = targetExpression.toString();
1782  for (SqlNode listedNode : selectNode.getSelectList()) {
1783  if (listedNode.toString().contains(targetString)) {
1784  throw Util.FoundOne.NULL;
1785  }
1786  }
1787  }
1788  return super.visit(call);
1789  }
1790 
1791  boolean containsExpression(SqlNode node, SqlNode targetExpression) {
1792  try {
1793  this.targetExpression = targetExpression;
1794  node.accept(this);
1795  return false;
1796  } catch (Util.FoundOne e) {
1797  return true;
1798  }
1799  }
1800 
1802  }
1803 
1805  extends SqlBasicVisitor<Void> {
1806  @Override
1807  public Void visit(SqlCall call) {
1808  if (call instanceof SqlBasicCall) {
1809  SqlBasicCall basicCall = (SqlBasicCall) call;
1810  if (basicCall.getKind() == SqlKind.OR) {
1811  String targetString = targetExpression.toString();
1812  for (SqlNode listedOperand : basicCall.operands) {
1813  if (listedOperand.toString().contains(targetString)) {
1814  throw Util.FoundOne.NULL;
1815  }
1816  }
1817  }
1818  }
1819  return super.visit(call);
1820  }
1821 
1822  boolean containsExpression(SqlNode node, SqlNode targetExpression) {
1823  try {
1824  this.targetExpression = targetExpression;
1825  node.accept(this);
1826  return false;
1827  } catch (Util.FoundOne e) {
1828  return true;
1829  }
1830  }
1831 
1833  }
1834 
1835  private static class JoinOperatorChecker extends SqlBasicVisitor<Void> {
1836  Set<SqlBasicCall> targetCalls = new HashSet<>();
1837 
1838  public boolean isEqualityJoinOperator(SqlBasicCall basicCall) {
1839  if (null != basicCall) {
1840  if (basicCall.operands.length == 2
1841  && (basicCall.getKind() == SqlKind.EQUALS
1842  || basicCall.getKind() == SqlKind.NOT_EQUALS)
1843  && basicCall.operand(0) instanceof SqlIdentifier
1844  && basicCall.operand(1) instanceof SqlIdentifier) {
1845  return true;
1846  }
1847  }
1848  return false;
1849  }
1850 
1851  @Override
1852  public Void visit(SqlCall call) {
1853  if (call instanceof SqlBasicCall) {
1854  targetCalls.add((SqlBasicCall) call);
1855  }
1856  for (SqlNode node : call.getOperandList()) {
1857  if (null != node && !targetCalls.contains(node)) {
1858  node.accept(this);
1859  }
1860  }
1861  return super.visit(call);
1862  }
1863 
1864  boolean containsExpression(SqlNode node) {
1865  try {
1866  if (null != node) {
1867  node.accept(this);
1868  for (SqlBasicCall basicCall : targetCalls) {
1869  if (isEqualityJoinOperator(basicCall)) {
1870  throw Util.FoundOne.NULL;
1871  }
1872  }
1873  }
1874  return false;
1875  } catch (Util.FoundOne e) {
1876  return true;
1877  }
1878  }
1879  }
1880 
1881  // this visitor checks whether a parse tree contains at least one
1882  // specific SQL operator we have an interest in
1883  // (do not count the accurate # operators we found)
1884  private static class FindSqlOperator extends SqlBasicVisitor<Void> {
1885  @Override
1886  public Void visit(SqlCall call) {
1887  if (call instanceof SqlBasicCall) {
1888  SqlBasicCall basicCall = (SqlBasicCall) call;
1889  if (basicCall.getKind().equals(targetKind)) {
1890  throw Util.FoundOne.NULL;
1891  }
1892  }
1893  return super.visit(call);
1894  }
1895 
1896  boolean containsSqlOperator(SqlNode node, SqlKind operatorKind) {
1897  try {
1898  targetKind = operatorKind;
1899  node.accept(this);
1900  return false;
1901  } catch (Util.FoundOne e) {
1902  return true;
1903  }
1904  }
1905 
1906  private SqlKind targetKind;
1907  }
1908 
1909  public void tableAliasFinder(SqlNode sqlNode, Map<String, String> tableAliasMap) {
1910  final SqlVisitor<Void> aliasCollector = new SqlBasicVisitor<Void>() {
1911  @Override
1912  public Void visit(SqlCall call) {
1913  if (call instanceof SqlBasicCall) {
1914  SqlBasicCall basicCall = (SqlBasicCall) call;
1915  if (basicCall.getKind() == SqlKind.AS) {
1916  if (basicCall.operand(0) instanceof SqlIdentifier) {
1917  // we need to check whether basicCall's the first operand is SqlIdentifier
1918  // since sometimes it represents non column identifier like SqlSelect
1919  SqlIdentifier colNameIdentifier = (SqlIdentifier) basicCall.operand(0);
1920  String tblName = colNameIdentifier.names.size() == 1
1921  ? colNameIdentifier.names.get(0)
1922  : colNameIdentifier.names.get(1);
1923  tableAliasMap.put(basicCall.operand(1).toString(), tblName);
1924  }
1925  }
1926  }
1927  return super.visit(call);
1928  }
1929  };
1930  sqlNode.accept(aliasCollector);
1931  }
1932 }
void desugarExpression(SqlNode node, RelDataTypeFactory typeFactory)
boolean containsExpression(SqlNode node, SqlNode targetExpression)
SqlIdentifierCapturer captureIdentifiers(SqlNode node)
SqlNode expandCovariance(final SqlBasicCall proj_call, RelDataTypeFactory typeFactory)
SqlNode expandVariance(final SqlBasicCall proj_call, RelDataTypeFactory typeFactory)
JoinType
Definition: sqldefs.h:165
SqlSelect rewriteSimpleUpdateAsSelect(final SqlUpdate update)
SqlOrderBy desugar(SqlSelect select_node, SqlOrderBy order_by_node, RelDataTypeFactory typeFactory)
RelRoot replaceIsTrue(final RelDataTypeFactory typeFactory, RelRoot root)
std::string join(T const &container, std::string const &delim)
tuple root
Definition: setup.in.py:14
SqlNode expandCase(SqlCase old_case_node, RelDataTypeFactory typeFactory)
SqlNode parseSql(String sql, final boolean legacy_syntax, Planner planner)
static final EnumSet< SqlKind > ARRAY_VALUE
constexpr double f
Definition: Utm.h:31
boolean isColumnHashJoinable(List< String > joinColumnIdentifier, MetaConnect mc)
HashSet< ImmutableList< String > > resolveSelectIdentifiers(SqlIdentifierCapturer capturer)
SqlNode expandStringFunctions(final SqlBasicCall proj_call, RelDataTypeFactory typeFactory)
RelRoot convertSqlToRelNode(final SqlNode sqlNode, final HeavyDBPlanner HeavyDBPlanner, final HeavyDBParserOptions parserOptions)
static final ThreadLocal< HeavyDBParser > CURRENT_PARSER
static final EnumSet< SqlKind > UPDATE
LogicalTableModify getDummyUpdate(SqlUpdate update)
String processSql(final SqlNode sqlNode, final HeavyDBParserOptions parserOptions)
static final EnumSet< SqlKind > IN
static final EnumSet< SqlKind > OTHER_FUNCTION
SqlIdentifierCapturer getAccessedObjects()
static Map< String, Boolean > SubqueryCorrMemo
std::string toString(const ExecutorDeviceType &device_type)
boolean isHashJoinableType(TColumnType type)
SockTransportProperties sock_transport_properties
RelRoot rewriteUpdateAsSelect(SqlUpdate update, HeavyDBParserOptions parserOptions)
String processSql(String sql, final HeavyDBParserOptions parserOptions)
void updateMetaData(String schema, String table)
static final Context DB_CONNECTION_CONTEXT
static final EnumSet< SqlKind > DELETE
String buildRATreeAndPerformQueryOptimization(String query, final HeavyDBParserOptions parserOptions)
SqlIdentifierCapturer captureIdentifiers(String sql, boolean legacy_syntax)
SqlNode expandVariance(final SqlParserPos pos, final SqlNode operand, boolean biased, boolean sqrt, boolean flt, RelDataTypeFactory typeFactory)
void desugar(SqlSelect select_node, RelDataTypeFactory typeFactory)
boolean containsSqlOperator(SqlNode node, SqlKind operatorKind)
static final EnumSet< SqlKind > SCALAR
HeavyDBPlanner.CompletionResult getCompletionHints(String sql, int cursor, List< String > visible_tables)
RelRoot queryToRelNode(final String sql, final HeavyDBParserOptions parserOptions)
SqlNode expandCovariance(SqlParserPos pos, final SqlNode operand0, final SqlNode operand1, boolean pop, boolean flt, RelDataTypeFactory typeFactory)
string name
Definition: setup.in.py:72
constexpr double n
Definition: Utm.h:38
SqlNodeList expand(final SqlNodeList group_by_list, final java.util.Map< String, SqlNode > id_to_expr, RelDataTypeFactory typeFactory)
Set< ImmutableList< String > > addDbContextIfMissing(Set< ImmutableList< String >> names)
SqlNode expandCorrelation(final SqlBasicCall proj_call, RelDataTypeFactory typeFactory)
Pair< String, SqlIdentifierCapturer > process(String sql, final HeavyDBParserOptions parserOptions)
static final SqlArrayValueConstructorAllowingEmpty ARRAY_VALUE_CONSTRUCTOR
static final EnumSet< SqlKind > EXISTS
void tableAliasFinder(SqlNode sqlNode, Map< String, String > tableAliasMap)
boolean isCorrelated(SqlNode expression)
HeavyDBParser(String dataDir, final Supplier< HeavyDBSqlOperatorTable > dbSqlOperatorTable, int dbPort, SockTransportProperties skT)
SqlNode expand(final SqlNode node, final java.util.Map< String, SqlNode > id_to_expr, RelDataTypeFactory typeFactory)
HeavyDBPlanner getPlanner(final boolean allowSubQueryExpansion, final boolean isWatchdogEnabled, final boolean isDistributedMode)
final Supplier< HeavyDBSqlOperatorTable > dbSqlOperatorTable