OmniSciDB  a987f07e93
 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  // create the db schema for the user session
393  final HeavyDBSchema defaultSchema =
395  final SchemaPlus dbSchema =
396  Frameworks.createRootSchema(true).add(dbUser.getDB(), defaultSchema);
397 
398  final FrameworkConfig config =
399  Frameworks.newConfigBuilder()
400  .defaultSchema(dbSchema)
401  .operatorTable(dbSqlOperatorTable.get())
402  .parserConfig(SqlParser.configBuilder()
403  .setConformance(SqlConformanceEnum.LENIENT)
404  .setUnquotedCasing(Casing.UNCHANGED)
405  .setCaseSensitive(false)
406  // allow identifiers of up to 512 chars
407  .setIdentifierMaxLength(512)
408  .setParserFactory(ExtendedSqlParser.FACTORY)
409  .build())
410  .sqlToRelConverterConfig(
411  SqlToRelConverter
412  .configBuilder()
413  // enable sub-query expansion (de-correlation)
414  .withExpandPredicate(expandPredicate)
415  // allow as many as possible IN operator values
416  .withInSubQueryThreshold(Integer.MAX_VALUE)
417  .withHintStrategyTable(
419  .build())
420 
421  .typeSystem(createTypeSystem())
422  .context(DB_CONNECTION_CONTEXT)
423  .build();
424  HeavyDBPlanner planner = new HeavyDBPlanner(config);
425  planner.setRestrictions(dbUser.getRestrictions());
426  return planner;
427  }
428 
429  public void setUser(HeavyDBUser dbUser) {
430  this.dbUser = dbUser;
431  }
432 
433  public Pair<String, SqlIdentifierCapturer> process(
434  String sql, final HeavyDBParserOptions parserOptions)
435  throws SqlParseException, ValidationException, RelConversionException {
436  final HeavyDBPlanner planner = getPlanner(
437  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
438  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
439  String res = processSql(sqlNode, parserOptions);
440  SqlIdentifierCapturer capture = captureIdentifiers(sqlNode);
441  return new Pair<String, SqlIdentifierCapturer>(res, capture);
442  }
443 
445  String query, final HeavyDBParserOptions parserOptions) throws IOException {
446  HeavyDBSchema schema =
448  HeavyDBPlanner planner = getPlanner(
449  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
450 
451  planner.setFilterPushDownInfo(parserOptions.getFilterPushDownInfo());
452  RelRoot optRel = planner.buildRATreeAndPerformQueryOptimization(query, schema);
453  optRel = replaceIsTrue(planner.getTypeFactory(), optRel);
454  return HeavyDBSerializer.toString(optRel.project());
455  }
456 
457  public String processSql(String sql, final HeavyDBParserOptions parserOptions)
458  throws SqlParseException, ValidationException, RelConversionException {
459  callCount++;
460 
461  final HeavyDBPlanner planner = getPlanner(
462  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
463  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
464 
465  return processSql(sqlNode, parserOptions);
466  }
467 
468  public String processSql(
469  final SqlNode sqlNode, final HeavyDBParserOptions parserOptions)
470  throws SqlParseException, ValidationException, RelConversionException {
471  callCount++;
472 
473  if (sqlNode instanceof JsonSerializableDdl) {
474  return ((JsonSerializableDdl) sqlNode).toJsonString();
475  }
476 
477  if (sqlNode instanceof SqlDdl) {
478  return sqlNode.toString();
479  }
480 
481  final HeavyDBPlanner planner = getPlanner(
482  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
483  planner.advanceToValidate();
484 
485  final RelRoot sqlRel = convertSqlToRelNode(sqlNode, planner, parserOptions);
486  RelNode project = sqlRel.project();
487 
488  if (parserOptions.isExplain()) {
489  return RelOptUtil.toString(sqlRel.project());
490  }
491 
492  String res = HeavyDBSerializer.toString(project);
493 
494  return res;
495  }
496 
497  public HeavyDBPlanner.CompletionResult getCompletionHints(
498  String sql, int cursor, List<String> visible_tables) {
499  return getPlanner().getCompletionHints(sql, cursor, visible_tables);
500  }
501 
502  public HashSet<ImmutableList<String>> resolveSelectIdentifiers(
503  SqlIdentifierCapturer capturer) {
504  HeavyDBSchema schema =
506  HashSet<ImmutableList<String>> resolved = new HashSet<ImmutableList<String>>();
507 
508  for (ImmutableList<String> names : capturer.selects) {
509  HeavyDBTable table = (HeavyDBTable) schema.getTable(names.get(0));
510  if (null == table) {
511  throw new RuntimeException("table/view not found: " + names.get(0));
512  }
513 
514  if (table instanceof HeavyDBView) {
515  HeavyDBView view = (HeavyDBView) table;
516  resolved.addAll(resolveSelectIdentifiers(view.getAccessedObjects()));
517  } else {
518  resolved.add(names);
519  }
520  }
521 
522  return resolved;
523  }
524 
525  private String getTableName(SqlNode node) {
526  if (node.isA(EnumSet.of(SqlKind.AS))) {
527  node = ((SqlCall) node).getOperandList().get(1);
528  }
529  if (node instanceof SqlIdentifier) {
530  SqlIdentifier id = (SqlIdentifier) node;
531  return id.names.get(id.names.size() - 1);
532  }
533  return null;
534  }
535 
536  private SqlSelect rewriteSimpleUpdateAsSelect(final SqlUpdate update) {
537  SqlNode where = update.getCondition();
538 
539  if (update.getSourceExpressionList().size() != 1) {
540  return null;
541  }
542 
543  if (!(update.getSourceExpressionList().get(0) instanceof SqlSelect)) {
544  return null;
545  }
546 
547  final SqlSelect inner = (SqlSelect) update.getSourceExpressionList().get(0);
548 
549  if (null != inner.getGroup() || null != inner.getFetch() || null != inner.getOffset()
550  || (null != inner.getOrderList() && inner.getOrderList().size() != 0)
551  || (null != inner.getGroup() && inner.getGroup().size() != 0)
552  || null == getTableName(inner.getFrom())) {
553  return null;
554  }
555 
556  if (!isCorrelated(inner)) {
557  return null;
558  }
559 
560  final String updateTableName = getTableName(update.getTargetTable());
561 
562  if (null != where) {
563  where = where.accept(new SqlShuttle() {
564  @Override
565  public SqlNode visit(SqlIdentifier id) {
566  if (id.isSimple()) {
567  id = new SqlIdentifier(Arrays.asList(updateTableName, id.getSimple()),
568  id.getParserPosition());
569  }
570 
571  return id;
572  }
573  });
574  }
575 
576  SqlJoin join = new SqlJoin(ZERO,
577  update.getTargetTable(),
578  SqlLiteral.createBoolean(false, ZERO),
579  SqlLiteral.createSymbol(JoinType.LEFT, ZERO),
580  inner.getFrom(),
581  SqlLiteral.createSymbol(JoinConditionType.ON, ZERO),
582  inner.getWhere());
583 
584  SqlNode select0 = inner.getSelectList().get(0);
585 
586  boolean wrapInSingleValue = true;
587  if (select0 instanceof SqlCall) {
588  SqlCall selectExprCall = (SqlCall) select0;
589  if (Util.isSingleValue(selectExprCall)) {
590  wrapInSingleValue = false;
591  }
592  }
593 
594  if (wrapInSingleValue) {
595  if (select0.isA(EnumSet.of(SqlKind.AS))) {
596  select0 = ((SqlCall) select0).getOperandList().get(0);
597  }
598  select0 = new SqlBasicCall(
599  SqlStdOperatorTable.SINGLE_VALUE, new SqlNode[] {select0}, ZERO);
600  }
601 
602  SqlNodeList selectList = new SqlNodeList(ZERO);
603  selectList.add(select0);
604  selectList.add(new SqlBasicCall(SqlStdOperatorTable.AS,
605  new SqlNode[] {new SqlBasicCall(
606  new SqlUnresolvedFunction(
607  new SqlIdentifier("OFFSET_IN_FRAGMENT", ZERO),
608  null,
609  null,
610  null,
611  null,
612  SqlFunctionCategory.USER_DEFINED_FUNCTION),
613  new SqlNode[0],
614  SqlParserPos.ZERO),
615  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
616  ZERO));
617 
618  SqlNodeList groupBy = new SqlNodeList(ZERO);
619  groupBy.add(new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO));
620 
621  SqlSelect select = new SqlSelect(ZERO,
622  null,
623  selectList,
624  join,
625  where,
626  groupBy,
627  null,
628  null,
629  null,
630  null,
631  null,
632  null);
633  return select;
634  }
635 
636  private LogicalTableModify getDummyUpdate(SqlUpdate update)
637  throws SqlParseException, ValidationException, RelConversionException {
638  SqlIdentifier targetTable = (SqlIdentifier) update.getTargetTable();
639  String targetTableName = targetTable.names.get(targetTable.names.size() - 1);
640  HeavyDBPlanner planner = getPlanner();
641  String dummySql = "DELETE FROM " + targetTableName;
642  SqlNode dummyNode = planner.parse(dummySql);
643  dummyNode = planner.validate(dummyNode);
644  RelRoot dummyRoot = planner.rel(dummyNode);
645  LogicalTableModify dummyModify = (LogicalTableModify) dummyRoot.rel;
646  return dummyModify;
647  }
648 
649  private RelRoot rewriteUpdateAsSelect(
650  SqlUpdate update, HeavyDBParserOptions parserOptions)
651  throws SqlParseException, ValidationException, RelConversionException {
652  int correlatedQueriesCount[] = new int[1];
653  SqlBasicVisitor<Void> correlatedQueriesCounter = new SqlBasicVisitor<Void>() {
654  @Override
655  public Void visit(SqlCall call) {
656  if (call.isA(SCALAR)
657  && ((call instanceof SqlBasicCall && call.operandCount() == 1
658  && !call.operand(0).isA(SCALAR))
659  || !(call instanceof SqlBasicCall))) {
660  if (isCorrelated(call)) {
661  correlatedQueriesCount[0]++;
662  }
663  }
664  return super.visit(call);
665  }
666  };
667 
668  update.accept(correlatedQueriesCounter);
669  if (correlatedQueriesCount[0] > 1) {
670  throw new CalciteException(
671  "table modifications with multiple correlated sub-queries not supported.",
672  null);
673  }
674 
675  boolean allowSubqueryDecorrelation = true;
676  SqlNode updateCondition = update.getCondition();
677  if (null != updateCondition) {
678  boolean hasInClause =
679  new FindSqlOperator().containsSqlOperator(updateCondition, SqlKind.IN);
680  if (hasInClause) {
681  SqlNode updateTargetTable = update.getTargetTable();
682  if (null != updateTargetTable && updateTargetTable instanceof SqlIdentifier) {
683  SqlIdentifier targetTable = (SqlIdentifier) updateTargetTable;
684  if (targetTable.names.size() == 2) {
685  final MetaConnect mc = new MetaConnect(dbPort,
686  dataDir,
687  dbUser,
688  this,
689  sock_transport_properties,
690  targetTable.names.get(0));
691  TTableDetails updateTargetTableDetails =
692  mc.get_table_details(targetTable.names.get(1));
693  if (null != updateTargetTableDetails
694  && updateTargetTableDetails.is_temporary) {
695  allowSubqueryDecorrelation = false;
696  }
697  }
698  }
699  }
700  }
701 
702  SqlNodeList sourceExpression = new SqlNodeList(SqlParserPos.ZERO);
703  LogicalTableModify dummyModify = getDummyUpdate(update);
704  RelOptTable targetTable = dummyModify.getTable();
705  RelDataType targetTableType = targetTable.getRowType();
706 
707  SqlSelect select = rewriteSimpleUpdateAsSelect(update);
708  boolean applyRexCast = null == select;
709 
710  if (null == select) {
711  for (int i = 0; i < update.getSourceExpressionList().size(); i++) {
712  SqlNode targetColumn = update.getTargetColumnList().get(i);
713  SqlNode expression = update.getSourceExpressionList().get(i);
714 
715  if (!(targetColumn instanceof SqlIdentifier)) {
716  throw new RuntimeException("Unknown identifier type!");
717  }
718  SqlIdentifier id = (SqlIdentifier) targetColumn;
719  RelDataType fieldType =
720  targetTableType.getField(id.names.get(id.names.size() - 1), false, false)
721  .getType();
722 
723  if (expression.isA(ARRAY_VALUE) && null != fieldType.getComponentType()) {
724  // apply a cast to all array value elements
725 
726  SqlDataTypeSpec elementType = new SqlDataTypeSpec(
727  new SqlBasicTypeNameSpec(fieldType.getComponentType().getSqlTypeName(),
728  fieldType.getPrecision(),
729  fieldType.getScale(),
730  null == fieldType.getCharset() ? null
731  : fieldType.getCharset().name(),
732  SqlParserPos.ZERO),
733  SqlParserPos.ZERO);
734  SqlCall array_expression = (SqlCall) expression;
735  ArrayList<SqlNode> values = new ArrayList<>();
736 
737  for (SqlNode value : array_expression.getOperandList()) {
738  if (value.isA(EnumSet.of(SqlKind.LITERAL))) {
739  SqlNode casted_value = new SqlBasicCall(SqlStdOperatorTable.CAST,
740  new SqlNode[] {value, elementType},
741  value.getParserPosition());
742  values.add(casted_value);
743  } else {
744  values.add(value);
745  }
746  }
747 
748  expression = new SqlBasicCall(HeavyDBSqlOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
749  values.toArray(new SqlNode[0]),
750  expression.getParserPosition());
751  }
752  sourceExpression.add(expression);
753  }
754 
755  sourceExpression.add(new SqlBasicCall(SqlStdOperatorTable.AS,
756  new SqlNode[] {
757  new SqlBasicCall(new SqlUnresolvedFunction(
758  new SqlIdentifier("OFFSET_IN_FRAGMENT",
759  SqlParserPos.ZERO),
760  null,
761  null,
762  null,
763  null,
764  SqlFunctionCategory.USER_DEFINED_FUNCTION),
765  new SqlNode[0],
766  SqlParserPos.ZERO),
767  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
768  ZERO));
769 
770  select = new SqlSelect(SqlParserPos.ZERO,
771  null,
772  sourceExpression,
773  update.getTargetTable(),
774  update.getCondition(),
775  null,
776  null,
777  null,
778  null,
779  null,
780  null,
781  null);
782  }
783 
784  HeavyDBPlanner planner = getPlanner(allowSubqueryDecorrelation,
785  parserOptions.isWatchdogEnabled(),
786  parserOptions.isDistributedMode());
787  SqlNode node = null;
788  try {
789  node = planner.parse(select.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
790  node = planner.validate(node);
791  } catch (Exception e) {
792  HEAVYDBLOGGER.error("Error processing UPDATE rewrite, rewritten stmt was: "
793  + select.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
794  throw e;
795  }
796 
797  RelRoot root = planner.rel(node);
798  LogicalProject project = (LogicalProject) root.project();
799 
800  ArrayList<String> fields = new ArrayList<String>();
801  ArrayList<RexNode> nodes = new ArrayList<RexNode>();
802  final RexBuilder builder = new RexBuilder(planner.getTypeFactory());
803 
804  for (SqlNode n : update.getTargetColumnList()) {
805  if (n instanceof SqlIdentifier) {
806  SqlIdentifier id = (SqlIdentifier) n;
807  fields.add(id.names.get(id.names.size() - 1));
808  } else {
809  throw new RuntimeException("Unknown identifier type!");
810  }
811  }
812 
813  // The magical number here when processing the projection
814  // is skipping the OFFSET_IN_FRAGMENT() expression used by
815  // update and delete
816  int idx = 0;
817  for (RexNode exp : project.getProjects()) {
818  if (applyRexCast && idx + 1 < project.getProjects().size()) {
819  RelDataType expectedFieldType =
820  targetTableType.getField(fields.get(idx), false, false).getType();
821  boolean is_array_kind = exp.isA(ARRAY_VALUE);
822  boolean is_func_kind = exp.isA(OTHER_FUNCTION);
823  // runtime functions have expression kind == OTHER_FUNCTION, even if they
824  // return an array
825  if (!exp.getType().equals(expectedFieldType)
826  && !(is_array_kind || is_func_kind)) {
827  exp = builder.makeCast(expectedFieldType, exp);
828  }
829  }
830 
831  nodes.add(exp);
832  idx++;
833  }
834 
835  ArrayList<RexNode> inputs = new ArrayList<RexNode>();
836  int n = 0;
837  for (int i = 0; i < fields.size(); i++) {
838  inputs.add(
839  new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
840  n++;
841  }
842 
843  fields.add("EXPR$DELETE_OFFSET_IN_FRAGMENT");
844  inputs.add(new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
845 
846  project = project.copy(
847  project.getTraitSet(), project.getInput(), nodes, project.getRowType());
848 
849  LogicalTableModify modify = LogicalTableModify.create(targetTable,
850  dummyModify.getCatalogReader(),
851  project,
852  Operation.UPDATE,
853  fields,
854  inputs,
855  true);
856  return RelRoot.of(modify, SqlKind.UPDATE);
857  }
858 
859  RelRoot queryToRelNode(final String sql, final HeavyDBParserOptions parserOptions)
860  throws SqlParseException, ValidationException, RelConversionException {
861  final HeavyDBPlanner planner = getPlanner(
862  true, parserOptions.isWatchdogEnabled(), parserOptions.isDistributedMode());
863  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
864  return convertSqlToRelNode(sqlNode, planner, parserOptions);
865  }
866 
867  RelRoot convertSqlToRelNode(final SqlNode sqlNode,
869  final HeavyDBParserOptions parserOptions)
870  throws SqlParseException, ValidationException, RelConversionException {
871  SqlNode node = sqlNode;
872  HeavyDBPlanner planner = HeavyDBPlanner;
873  boolean allowCorrelatedSubQueryExpansion = true;
874  boolean patchUpdateToDelete = false;
875  if (node.isA(DELETE)) {
876  SqlDelete sqlDelete = (SqlDelete) node;
877  node = new SqlUpdate(node.getParserPosition(),
878  sqlDelete.getTargetTable(),
879  SqlNodeList.EMPTY,
880  SqlNodeList.EMPTY,
881  sqlDelete.getCondition(),
882  sqlDelete.getSourceSelect(),
883  sqlDelete.getAlias());
884 
885  patchUpdateToDelete = true;
886  }
887  if (node.isA(UPDATE)) {
888  SqlUpdate update = (SqlUpdate) node;
889  update = (SqlUpdate) planner.validate(update);
890  RelRoot root = rewriteUpdateAsSelect(update, parserOptions);
891 
892  if (patchUpdateToDelete) {
893  LogicalTableModify modify = (LogicalTableModify) root.rel;
894 
895  try {
896  Field f = TableModify.class.getDeclaredField("operation");
897  f.setAccessible(true);
898  f.set(modify, Operation.DELETE);
899  } catch (Throwable e) {
900  throw new RuntimeException(e);
901  }
902 
903  root = RelRoot.of(modify, SqlKind.DELETE);
904  }
905 
906  return root;
907  }
908  if (parserOptions.isLegacySyntax()) {
909  // close original planner
910  planner.close();
911  // create a new one
912  planner = getPlanner(allowCorrelatedSubQueryExpansion,
913  parserOptions.isWatchdogEnabled(),
914  parserOptions.isDistributedMode());
915  node = parseSql(
916  node.toSqlString(CalciteSqlDialect.DEFAULT).toString(), false, planner);
917  }
918 
919  SqlNode validateR = planner.validate(node);
920  planner.setFilterPushDownInfo(parserOptions.getFilterPushDownInfo());
921  // check to see if a view is involved in the query
922  boolean foundView = false;
923  HeavyDBSchema schema =
924  new HeavyDBSchema(dataDir, this, dbPort, dbUser, sock_transport_properties);
925  SqlIdentifierCapturer capturer = captureIdentifiers(sqlNode);
926  for (ImmutableList<String> names : capturer.selects) {
927  HeavyDBTable table = (HeavyDBTable) schema.getTable(names.get(0));
928  if (null == table) {
929  throw new RuntimeException("table/view not found: " + names.get(0));
930  }
931  if (table instanceof HeavyDBView) {
932  foundView = true;
933  }
934  }
935  RelRoot relRootNode = planner.getRelRoot(validateR);
936  relRootNode = replaceIsTrue(planner.getTypeFactory(), relRootNode);
937  RelNode rootNode = planner.optimizeRATree(
938  relRootNode.project(), parserOptions.isViewOptimizeEnabled(), foundView);
939  planner.close();
940  return new RelRoot(rootNode,
941  relRootNode.validatedRowType,
942  relRootNode.kind,
943  relRootNode.fields,
944  relRootNode.collation,
945  Collections.emptyList());
946  }
947 
948  private RelRoot replaceIsTrue(final RelDataTypeFactory typeFactory, RelRoot root) {
949  final RexShuttle callShuttle = new RexShuttle() {
950  RexBuilder builder = new RexBuilder(typeFactory);
951 
952  public RexNode visitCall(RexCall call) {
953  call = (RexCall) super.visitCall(call);
954  if (call.getKind() == SqlKind.IS_TRUE) {
955  return builder.makeCall(SqlStdOperatorTable.AND,
956  builder.makeCall(
957  SqlStdOperatorTable.IS_NOT_NULL, call.getOperands().get(0)),
958  call.getOperands().get(0));
959  } else if (call.getKind() == SqlKind.IS_NOT_TRUE) {
960  return builder.makeCall(SqlStdOperatorTable.OR,
961  builder.makeCall(
962  SqlStdOperatorTable.IS_NULL, call.getOperands().get(0)),
963  builder.makeCall(SqlStdOperatorTable.NOT, call.getOperands().get(0)));
964  } else if (call.getKind() == SqlKind.IS_FALSE) {
965  return builder.makeCall(SqlStdOperatorTable.AND,
966  builder.makeCall(
967  SqlStdOperatorTable.IS_NOT_NULL, call.getOperands().get(0)),
968  builder.makeCall(SqlStdOperatorTable.NOT, call.getOperands().get(0)));
969  } else if (call.getKind() == SqlKind.IS_NOT_FALSE) {
970  return builder.makeCall(SqlStdOperatorTable.OR,
971  builder.makeCall(
972  SqlStdOperatorTable.IS_NULL, call.getOperands().get(0)),
973  call.getOperands().get(0));
974  }
975 
976  return call;
977  }
978  };
979 
980  RelNode node = root.rel.accept(new RelShuttleImpl() {
981  @Override
982  protected RelNode visitChild(RelNode parent, int i, RelNode child) {
983  RelNode node = super.visitChild(parent, i, child);
984  return node.accept(callShuttle);
985  }
986  });
987 
988  return new RelRoot(node,
989  root.validatedRowType,
990  root.kind,
991  root.fields,
992  root.collation,
993  Collections.emptyList());
994  }
995 
996  private SqlNode parseSql(String sql, final boolean legacy_syntax, Planner planner)
997  throws SqlParseException {
998  SqlNode parseR = null;
999  try {
1000  parseR = planner.parse(sql);
1001  HEAVYDBLOGGER.debug(" node is \n" + parseR.toString());
1002  } catch (SqlParseException ex) {
1003  HEAVYDBLOGGER.error("failed to parse SQL '" + sql + "' \n" + ex.toString());
1004  throw ex;
1005  }
1006 
1007  if (!legacy_syntax) {
1008  return parseR;
1009  }
1010 
1011  RelDataTypeFactory typeFactory = planner.getTypeFactory();
1012  SqlSelect select_node = null;
1013  if (parseR instanceof SqlSelect) {
1014  select_node = (SqlSelect) parseR;
1015  desugar(select_node, typeFactory);
1016  } else if (parseR instanceof SqlOrderBy) {
1017  SqlOrderBy order_by_node = (SqlOrderBy) parseR;
1018  if (order_by_node.query instanceof SqlSelect) {
1019  select_node = (SqlSelect) order_by_node.query;
1020  SqlOrderBy new_order_by_node = desugar(select_node, order_by_node, typeFactory);
1021  if (new_order_by_node != null) {
1022  return new_order_by_node;
1023  }
1024  } else if (order_by_node.query instanceof SqlWith) {
1025  SqlWith old_with_node = (SqlWith) order_by_node.query;
1026  if (old_with_node.body instanceof SqlSelect) {
1027  select_node = (SqlSelect) old_with_node.body;
1028  desugar(select_node, typeFactory);
1029  }
1030  }
1031  } else if (parseR instanceof SqlWith) {
1032  SqlWith old_with_node = (SqlWith) parseR;
1033  if (old_with_node.body instanceof SqlSelect) {
1034  select_node = (SqlSelect) old_with_node.body;
1035  desugar(select_node, typeFactory);
1036  }
1037  }
1038  return parseR;
1039  }
1040 
1041  private void desugar(SqlSelect select_node, RelDataTypeFactory typeFactory) {
1042  desugar(select_node, null, typeFactory);
1043  }
1044 
1045  private SqlNode expandCase(SqlCase old_case_node, RelDataTypeFactory typeFactory) {
1046  SqlNodeList newWhenList =
1047  new SqlNodeList(old_case_node.getWhenOperands().getParserPosition());
1048  SqlNodeList newThenList =
1049  new SqlNodeList(old_case_node.getThenOperands().getParserPosition());
1050  java.util.Map<String, SqlNode> id_to_expr = new java.util.HashMap<String, SqlNode>();
1051  for (SqlNode node : old_case_node.getWhenOperands()) {
1052  SqlNode newCall = expand(node, id_to_expr, typeFactory);
1053  if (null != newCall) {
1054  newWhenList.add(newCall);
1055  } else {
1056  newWhenList.add(node);
1057  }
1058  }
1059  for (SqlNode node : old_case_node.getThenOperands()) {
1060  SqlNode newCall = expand(node, id_to_expr, typeFactory);
1061  if (null != newCall) {
1062  newThenList.add(newCall);
1063  } else {
1064  newThenList.add(node);
1065  }
1066  }
1067  SqlNode new_else_operand = old_case_node.getElseOperand();
1068  if (null != new_else_operand) {
1069  SqlNode candidate_else_operand =
1070  expand(old_case_node.getElseOperand(), id_to_expr, typeFactory);
1071  if (null != candidate_else_operand) {
1072  new_else_operand = candidate_else_operand;
1073  }
1074  }
1075  SqlNode new_value_operand = old_case_node.getValueOperand();
1076  if (null != new_value_operand) {
1077  SqlNode candidate_value_operand =
1078  expand(old_case_node.getValueOperand(), id_to_expr, typeFactory);
1079  if (null != candidate_value_operand) {
1080  new_value_operand = candidate_value_operand;
1081  }
1082  }
1083  SqlNode newCaseNode = SqlCase.createSwitched(old_case_node.getParserPosition(),
1084  new_value_operand,
1085  newWhenList,
1086  newThenList,
1087  new_else_operand);
1088  return newCaseNode;
1089  }
1090 
1091  private SqlOrderBy desugar(SqlSelect select_node,
1092  SqlOrderBy order_by_node,
1093  RelDataTypeFactory typeFactory) {
1094  HEAVYDBLOGGER.debug("desugar: before: " + select_node.toString());
1095  desugarExpression(select_node.getFrom(), typeFactory);
1096  desugarExpression(select_node.getWhere(), typeFactory);
1097  SqlNodeList select_list = select_node.getSelectList();
1098  SqlNodeList new_select_list = new SqlNodeList(select_list.getParserPosition());
1099  java.util.Map<String, SqlNode> id_to_expr = new java.util.HashMap<String, SqlNode>();
1100  for (SqlNode proj : select_list) {
1101  if (!(proj instanceof SqlBasicCall)) {
1102  if (proj instanceof SqlCase) {
1103  new_select_list.add(expandCase((SqlCase) proj, typeFactory));
1104  } else {
1105  new_select_list.add(proj);
1106  }
1107  } else {
1108  assert proj instanceof SqlBasicCall;
1109  SqlBasicCall proj_call = (SqlBasicCall) proj;
1110  if (proj_call.operands.length > 0) {
1111  for (int i = 0; i < proj_call.operands.length; i++) {
1112  if (proj_call.operand(i) instanceof SqlCase) {
1113  SqlNode new_op = expandCase(proj_call.operand(i), typeFactory);
1114  proj_call.setOperand(i, new_op);
1115  }
1116  }
1117  }
1118  new_select_list.add(expand(proj_call, id_to_expr, typeFactory));
1119  }
1120  }
1121  select_node.setSelectList(new_select_list);
1122  SqlNodeList group_by_list = select_node.getGroup();
1123  if (group_by_list != null) {
1124  select_node.setGroupBy(expand(group_by_list, id_to_expr, typeFactory));
1125  }
1126  SqlNode having = select_node.getHaving();
1127  if (having != null) {
1128  expand(having, id_to_expr, typeFactory);
1129  }
1130  SqlOrderBy new_order_by_node = null;
1131  if (order_by_node != null && order_by_node.orderList != null
1132  && order_by_node.orderList.size() > 0) {
1133  SqlNodeList new_order_by_list =
1134  expand(order_by_node.orderList, id_to_expr, typeFactory);
1135  new_order_by_node = new SqlOrderBy(order_by_node.getParserPosition(),
1136  select_node,
1137  new_order_by_list,
1138  order_by_node.offset,
1139  order_by_node.fetch);
1140  }
1141 
1142  HEAVYDBLOGGER.debug("desugar: after: " + select_node.toString());
1143  return new_order_by_node;
1144  }
1145 
1146  private void desugarExpression(SqlNode node, RelDataTypeFactory typeFactory) {
1147  if (node instanceof SqlSelect) {
1148  desugar((SqlSelect) node, typeFactory);
1149  return;
1150  }
1151  if (!(node instanceof SqlBasicCall)) {
1152  return;
1153  }
1154  SqlBasicCall basic_call = (SqlBasicCall) node;
1155  for (SqlNode operator : basic_call.getOperands()) {
1156  if (operator instanceof SqlOrderBy) {
1157  desugarExpression(((SqlOrderBy) operator).query, typeFactory);
1158  } else {
1159  desugarExpression(operator, typeFactory);
1160  }
1161  }
1162  }
1163 
1164  private SqlNode expand(final SqlNode node,
1165  final java.util.Map<String, SqlNode> id_to_expr,
1166  RelDataTypeFactory typeFactory) {
1167  HEAVYDBLOGGER.debug("expand: " + node.toString());
1168  if (node instanceof SqlBasicCall) {
1169  SqlBasicCall node_call = (SqlBasicCall) node;
1170  SqlNode[] operands = node_call.getOperands();
1171  for (int i = 0; i < operands.length; ++i) {
1172  node_call.setOperand(i, expand(operands[i], id_to_expr, typeFactory));
1173  }
1174  SqlNode expanded_string_function = expandStringFunctions(node_call, typeFactory);
1175  if (expanded_string_function != null) {
1176  return expanded_string_function;
1177  }
1178  SqlNode expanded_variance = expandVariance(node_call, typeFactory);
1179  if (expanded_variance != null) {
1180  return expanded_variance;
1181  }
1182  SqlNode expanded_covariance = expandCovariance(node_call, typeFactory);
1183  if (expanded_covariance != null) {
1184  return expanded_covariance;
1185  }
1186  SqlNode expanded_correlation = expandCorrelation(node_call, typeFactory);
1187  if (expanded_correlation != null) {
1188  return expanded_correlation;
1189  }
1190  }
1191  if (node instanceof SqlSelect) {
1192  SqlSelect select_node = (SqlSelect) node;
1193  desugar(select_node, typeFactory);
1194  }
1195  return node;
1196  }
1197 
1198  private SqlNodeList expand(final SqlNodeList group_by_list,
1199  final java.util.Map<String, SqlNode> id_to_expr,
1200  RelDataTypeFactory typeFactory) {
1201  SqlNodeList new_group_by_list = new SqlNodeList(new SqlParserPos(-1, -1));
1202  for (SqlNode group_by : group_by_list) {
1203  if (!(group_by instanceof SqlIdentifier)) {
1204  new_group_by_list.add(expand(group_by, id_to_expr, typeFactory));
1205  continue;
1206  }
1207  SqlIdentifier group_by_id = ((SqlIdentifier) group_by);
1208  if (id_to_expr.containsKey(group_by_id.toString())) {
1209  new_group_by_list.add(id_to_expr.get(group_by_id.toString()));
1210  } else {
1211  new_group_by_list.add(group_by);
1212  }
1213  }
1214  return new_group_by_list;
1215  }
1216 
1217  private SqlNode expandStringFunctions(
1218  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1219  //
1220  // Expand string functions
1221  //
1222 
1223  final int operandCount = proj_call.operandCount();
1224 
1225  if (proj_call.getOperator().isName("MID", false)
1226  || proj_call.getOperator().isName("SUBSTR", false)) {
1227  // Replace MID/SUBSTR with SUBSTRING
1228  //
1229  // Note: SUBSTRING doesn't offer much flexibility for the numeric arg's type
1230  // "only constant, column, or other string operator arguments are allowed"
1231  final SqlParserPos pos = proj_call.getParserPosition();
1232  if (operandCount == 2) {
1233  final SqlNode primary_operand = proj_call.operand(0);
1234  final SqlNode from_operand = proj_call.operand(1);
1235  return SqlStdOperatorTable.SUBSTRING.createCall(
1236  pos, primary_operand, from_operand);
1237 
1238  } else if (operandCount == 3) {
1239  final SqlNode primary_operand = proj_call.operand(0);
1240  final SqlNode from_operand = proj_call.operand(1);
1241  final SqlNode for_operand = proj_call.operand(2);
1242  return SqlStdOperatorTable.SUBSTRING.createCall(
1243  pos, primary_operand, from_operand, for_operand);
1244  }
1245  return null;
1246 
1247  } else if (proj_call.getOperator().isName("CONTAINS", false)) {
1248  // Replace CONTAINS with LIKE
1249  // as noted by TABLEAU's own published documention
1250  final SqlParserPos pos = proj_call.getParserPosition();
1251  if (operandCount == 2) {
1252  final SqlNode primary = proj_call.operand(0);
1253  final SqlNode pattern = proj_call.operand(1);
1254 
1255  if (pattern instanceof SqlLiteral) {
1256  // LIKE only supports Literal patterns ... at the moment
1257  SqlLiteral literalPattern = (SqlLiteral) pattern;
1258  String sPattern = literalPattern.getValueAs(String.class);
1259  SqlLiteral withWildcards =
1260  SqlLiteral.createCharString("%" + sPattern + "%", pos);
1261  return SqlStdOperatorTable.LIKE.createCall(pos, primary, withWildcards);
1262  }
1263  }
1264  return null;
1265 
1266  } else if (proj_call.getOperator().isName("ENDSWITH", false)) {
1267  // Replace ENDSWITH with LIKE
1268  final SqlParserPos pos = proj_call.getParserPosition();
1269  if (operandCount == 2) {
1270  final SqlNode primary = proj_call.operand(0);
1271  final SqlNode pattern = proj_call.operand(1);
1272 
1273  if (pattern instanceof SqlLiteral) {
1274  // LIKE only supports Literal patterns ... at the moment
1275  SqlLiteral literalPattern = (SqlLiteral) pattern;
1276  String sPattern = literalPattern.getValueAs(String.class);
1277  SqlLiteral withWildcards = SqlLiteral.createCharString("%" + sPattern, pos);
1278  return SqlStdOperatorTable.LIKE.createCall(pos, primary, withWildcards);
1279  }
1280  }
1281  return null;
1282  } else if (proj_call.getOperator().isName("LCASE", false)) {
1283  // Expand LCASE with LOWER
1284  final SqlParserPos pos = proj_call.getParserPosition();
1285  if (operandCount == 1) {
1286  final SqlNode primary = proj_call.operand(0);
1287  return SqlStdOperatorTable.LOWER.createCall(pos, primary);
1288  }
1289  return null;
1290 
1291  } else if (proj_call.getOperator().isName("LEFT", false)) {
1292  // Replace LEFT with SUBSTRING
1293  final SqlParserPos pos = proj_call.getParserPosition();
1294 
1295  if (operandCount == 2) {
1296  final SqlNode primary = proj_call.operand(0);
1297  SqlNode start = SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO);
1298  final SqlNode count = proj_call.operand(1);
1299  return SqlStdOperatorTable.SUBSTRING.createCall(pos, primary, start, count);
1300  }
1301  return null;
1302 
1303  } else if (proj_call.getOperator().isName("LEN", false)) {
1304  // Replace LEN with CHARACTER_LENGTH
1305  final SqlParserPos pos = proj_call.getParserPosition();
1306  if (operandCount == 1) {
1307  final SqlNode primary = proj_call.operand(0);
1308  return SqlStdOperatorTable.CHARACTER_LENGTH.createCall(pos, primary);
1309  }
1310  return null;
1311 
1312  } else if (proj_call.getOperator().isName("MAX", false)
1313  || proj_call.getOperator().isName("MIN", false)) {
1314  // Replace MAX(a,b), MIN(a,b) with CASE
1315  final SqlParserPos pos = proj_call.getParserPosition();
1316 
1317  if (operandCount == 2) {
1318  final SqlNode arg1 = proj_call.operand(0);
1319  final SqlNode arg2 = proj_call.operand(1);
1320 
1321  SqlNodeList whenList = new SqlNodeList(pos);
1322  SqlNodeList thenList = new SqlNodeList(pos);
1323  SqlNodeList elseClause = new SqlNodeList(pos);
1324 
1325  if (proj_call.getOperator().isName("MAX", false)) {
1326  whenList.add(
1327  SqlStdOperatorTable.GREATER_THAN_OR_EQUAL.createCall(pos, arg1, arg2));
1328  } else {
1329  whenList.add(
1330  SqlStdOperatorTable.LESS_THAN_OR_EQUAL.createCall(pos, arg1, arg2));
1331  }
1332  thenList.add(arg1);
1333  elseClause.add(arg2);
1334 
1335  SqlNode caseIdentifier = null;
1336  return SqlCase.createSwitched(
1337  pos, caseIdentifier, whenList, thenList, elseClause);
1338  }
1339  return null;
1340 
1341  } else if (proj_call.getOperator().isName("RIGHT", false)) {
1342  // Replace RIGHT with SUBSTRING
1343  final SqlParserPos pos = proj_call.getParserPosition();
1344 
1345  if (operandCount == 2) {
1346  final SqlNode primary = proj_call.operand(0);
1347  final SqlNode count = proj_call.operand(1);
1348  if (count instanceof SqlNumericLiteral) {
1349  SqlNumericLiteral numericCount = (SqlNumericLiteral) count;
1350  if (numericCount.intValue(true) > 0) {
1351  // common case
1352  final SqlNode negativeCount =
1353  SqlNumericLiteral.createNegative(numericCount, pos);
1354  return SqlStdOperatorTable.SUBSTRING.createCall(pos, primary, negativeCount);
1355  }
1356  // allow zero (or negative) to return an empty string
1357  // matches behavior of LEFT
1358  SqlNode zero = SqlLiteral.createExactNumeric("0", SqlParserPos.ZERO);
1359  return SqlStdOperatorTable.SUBSTRING.createCall(pos, primary, zero, zero);
1360  }
1361  // if not a simple literal ... attempt to evaluate
1362  // expected to fail ... with a useful error message
1363  return SqlStdOperatorTable.SUBSTRING.createCall(pos, primary, count);
1364  }
1365  return null;
1366 
1367  } else if (proj_call.getOperator().isName("SPACE", false)) {
1368  // Replace SPACE with REPEAT
1369  final SqlParserPos pos = proj_call.getParserPosition();
1370  if (operandCount == 1) {
1371  final SqlNode count = proj_call.operand(0);
1372  SqlFunction fn_repeat = new SqlFunction("REPEAT",
1373  SqlKind.OTHER_FUNCTION,
1374  ReturnTypes.ARG0_NULLABLE,
1375  null,
1376  OperandTypes.CHARACTER,
1377  SqlFunctionCategory.STRING);
1378  SqlLiteral space = SqlLiteral.createCharString(" ", pos);
1379  return fn_repeat.createCall(pos, space, count);
1380  }
1381  return null;
1382 
1383  } else if (proj_call.getOperator().isName("SPLIT", false)) {
1384  // Replace SPLIT with SPLIT_PART
1385  final SqlParserPos pos = proj_call.getParserPosition();
1386  if (operandCount == 3) {
1387  final SqlNode primary = proj_call.operand(0);
1388  final SqlNode delimeter = proj_call.operand(1);
1389  final SqlNode count = proj_call.operand(2);
1390  SqlFunction fn_split = new SqlFunction("SPLIT_PART",
1391  SqlKind.OTHER_FUNCTION,
1392  ReturnTypes.ARG0_NULLABLE,
1393  null,
1394  OperandTypes.CHARACTER,
1395  SqlFunctionCategory.STRING);
1396 
1397  return fn_split.createCall(pos, primary, delimeter, count);
1398  }
1399  return null;
1400 
1401  } else if (proj_call.getOperator().isName("STARTSWITH", false)) {
1402  // Replace STARTSWITH with LIKE
1403  final SqlParserPos pos = proj_call.getParserPosition();
1404  if (operandCount == 2) {
1405  final SqlNode primary = proj_call.operand(0);
1406  final SqlNode pattern = proj_call.operand(1);
1407 
1408  if (pattern instanceof SqlLiteral) {
1409  // LIKE only supports Literal patterns ... at the moment
1410  SqlLiteral literalPattern = (SqlLiteral) pattern;
1411  String sPattern = literalPattern.getValueAs(String.class);
1412  SqlLiteral withWildcards = SqlLiteral.createCharString(sPattern + "%", pos);
1413  return SqlStdOperatorTable.LIKE.createCall(pos, primary, withWildcards);
1414  }
1415  }
1416  return null;
1417 
1418  } else if (proj_call.getOperator().isName("UCASE", false)) {
1419  // Replace UCASE with UPPER
1420  final SqlParserPos pos = proj_call.getParserPosition();
1421  if (operandCount == 1) {
1422  final SqlNode primary = proj_call.operand(0);
1423  return SqlStdOperatorTable.UPPER.createCall(pos, primary);
1424  }
1425  return null;
1426  }
1427 
1428  return null;
1429  }
1430 
1431  private SqlNode expandVariance(
1432  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1433  // Expand variance aggregates that are not supported natively
1434  if (proj_call.operandCount() != 1) {
1435  return null;
1436  }
1437  boolean biased;
1438  boolean sqrt;
1439  boolean flt;
1440  if (proj_call.getOperator().isName("STDDEV_POP", false)) {
1441  biased = true;
1442  sqrt = true;
1443  flt = false;
1444  } else if (proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_POP_FLOAT")) {
1445  biased = true;
1446  sqrt = true;
1447  flt = true;
1448  } else if (proj_call.getOperator().isName("STDDEV_SAMP", false)
1449  || proj_call.getOperator().getName().equalsIgnoreCase("STDDEV")) {
1450  biased = false;
1451  sqrt = true;
1452  flt = false;
1453  } else if (proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_SAMP_FLOAT")
1454  || proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_FLOAT")) {
1455  biased = false;
1456  sqrt = true;
1457  flt = true;
1458  } else if (proj_call.getOperator().isName("VAR_POP", false)) {
1459  biased = true;
1460  sqrt = false;
1461  flt = false;
1462  } else if (proj_call.getOperator().getName().equalsIgnoreCase("VAR_POP_FLOAT")) {
1463  biased = true;
1464  sqrt = false;
1465  flt = true;
1466  } else if (proj_call.getOperator().isName("VAR_SAMP", false)
1467  || proj_call.getOperator().getName().equalsIgnoreCase("VARIANCE")) {
1468  biased = false;
1469  sqrt = false;
1470  flt = false;
1471  } else if (proj_call.getOperator().getName().equalsIgnoreCase("VAR_SAMP_FLOAT")
1472  || proj_call.getOperator().getName().equalsIgnoreCase("VARIANCE_FLOAT")) {
1473  biased = false;
1474  sqrt = false;
1475  flt = true;
1476  } else {
1477  return null;
1478  }
1479  final SqlNode operand = proj_call.operand(0);
1480  final SqlParserPos pos = proj_call.getParserPosition();
1481  SqlNode expanded_proj_call =
1482  expandVariance(pos, operand, biased, sqrt, flt, typeFactory);
1483  HEAVYDBLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1484  HEAVYDBLOGGER.debug("to : " + expanded_proj_call.toString());
1485  return expanded_proj_call;
1486  }
1487 
1488  private SqlNode expandVariance(final SqlParserPos pos,
1489  final SqlNode operand,
1490  boolean biased,
1491  boolean sqrt,
1492  boolean flt,
1493  RelDataTypeFactory typeFactory) {
1494  // stddev_pop(x) ==>
1495  // power(
1496  // (sum(x * x) - sum(x) * sum(x) / (case count(x) when 0 then NULL else count(x)
1497  // end)) / (case count(x) when 0 then NULL else count(x) end), .5)
1498  //
1499  // stddev_samp(x) ==>
1500  // power(
1501  // (sum(x * x) - sum(x) * sum(x) / (case count(x) when 0 then NULL else count(x)
1502  // )) / ((case count(x) when 1 then NULL else count(x) - 1 end)), .5)
1503  //
1504  // var_pop(x) ==>
1505  // (sum(x * x) - sum(x) * sum(x) / ((case count(x) when 0 then NULL else
1506  // count(x)
1507  // end))) / ((case count(x) when 0 then NULL else count(x) end))
1508  //
1509  // var_samp(x) ==>
1510  // (sum(x * x) - sum(x) * sum(x) / ((case count(x) when 0 then NULL else
1511  // count(x)
1512  // end))) / ((case count(x) when 1 then NULL else count(x) - 1 end))
1513  //
1514  final SqlNode arg = SqlStdOperatorTable.CAST.createCall(pos,
1515  operand,
1516  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1517  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1518  final SqlNode argSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, arg, arg);
1519  final SqlNode sumArgSquared = SqlStdOperatorTable.SUM.createCall(pos, argSquared);
1520  final SqlNode sum = SqlStdOperatorTable.SUM.createCall(pos, arg);
1521  final SqlNode sumSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, sum, sum);
1522  final SqlNode count = SqlStdOperatorTable.COUNT.createCall(pos, arg);
1523  final SqlLiteral nul = SqlLiteral.createNull(pos);
1524  final SqlNumericLiteral zero = SqlLiteral.createExactNumeric("0", pos);
1525  final SqlNode countEqZero = SqlStdOperatorTable.EQUALS.createCall(pos, count, zero);
1526  SqlNodeList whenList = new SqlNodeList(pos);
1527  SqlNodeList thenList = new SqlNodeList(pos);
1528  whenList.add(countEqZero);
1529  thenList.add(nul);
1530  final SqlNode int_denominator = SqlStdOperatorTable.CASE.createCall(
1531  null, pos, null, whenList, thenList, count);
1532  final SqlNode denominator = SqlStdOperatorTable.CAST.createCall(pos,
1533  int_denominator,
1534  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1535  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1536  final SqlNode avgSumSquared =
1537  SqlStdOperatorTable.DIVIDE.createCall(pos, sumSquared, denominator);
1538  final SqlNode diff =
1539  SqlStdOperatorTable.MINUS.createCall(pos, sumArgSquared, avgSumSquared);
1540  final SqlNode denominator1;
1541  if (biased) {
1542  denominator1 = denominator;
1543  } else {
1544  final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
1545  final SqlNode countEqOne = SqlStdOperatorTable.EQUALS.createCall(pos, count, one);
1546  final SqlNode countMinusOne = SqlStdOperatorTable.MINUS.createCall(pos, count, one);
1547  SqlNodeList whenList1 = new SqlNodeList(pos);
1548  SqlNodeList thenList1 = new SqlNodeList(pos);
1549  whenList1.add(countEqOne);
1550  thenList1.add(nul);
1551  final SqlNode int_denominator1 = SqlStdOperatorTable.CASE.createCall(
1552  null, pos, null, whenList1, thenList1, countMinusOne);
1553  denominator1 = SqlStdOperatorTable.CAST.createCall(pos,
1554  int_denominator1,
1555  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1556  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1557  }
1558  final SqlNode div = SqlStdOperatorTable.DIVIDE.createCall(pos, diff, denominator1);
1559  SqlNode result = div;
1560  if (sqrt) {
1561  final SqlNumericLiteral half = SqlLiteral.createExactNumeric("0.5", pos);
1562  result = SqlStdOperatorTable.POWER.createCall(pos, div, half);
1563  }
1564  return SqlStdOperatorTable.CAST.createCall(pos,
1565  result,
1566  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1567  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1568  }
1569 
1570  private SqlNode expandCovariance(
1571  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1572  // Expand covariance aggregates
1573  if (proj_call.operandCount() != 2) {
1574  return null;
1575  }
1576  boolean pop;
1577  boolean flt;
1578  if (proj_call.getOperator().isName("COVAR_POP", false)) {
1579  pop = true;
1580  flt = false;
1581  } else if (proj_call.getOperator().isName("COVAR_SAMP", false)) {
1582  pop = false;
1583  flt = false;
1584  } else if (proj_call.getOperator().getName().equalsIgnoreCase("COVAR_POP_FLOAT")) {
1585  pop = true;
1586  flt = true;
1587  } else if (proj_call.getOperator().getName().equalsIgnoreCase("COVAR_SAMP_FLOAT")) {
1588  pop = false;
1589  flt = true;
1590  } else {
1591  return null;
1592  }
1593  final SqlNode operand0 = proj_call.operand(0);
1594  final SqlNode operand1 = proj_call.operand(1);
1595  final SqlParserPos pos = proj_call.getParserPosition();
1596  SqlNode expanded_proj_call =
1597  expandCovariance(pos, operand0, operand1, pop, flt, typeFactory);
1598  HEAVYDBLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1599  HEAVYDBLOGGER.debug("to : " + expanded_proj_call.toString());
1600  return expanded_proj_call;
1601  }
1602 
1603  private SqlNode expandCovariance(SqlParserPos pos,
1604  final SqlNode operand0,
1605  final SqlNode operand1,
1606  boolean pop,
1607  boolean flt,
1608  RelDataTypeFactory typeFactory) {
1609  // covar_pop(x, y) ==> avg(x * y) - avg(x) * avg(y)
1610  // covar_samp(x, y) ==> (sum(x * y) - sum(x) * avg(y))
1611  // ((case count(x) when 1 then NULL else count(x) - 1 end))
1612  final SqlNode arg0 = SqlStdOperatorTable.CAST.createCall(operand0.getParserPosition(),
1613  operand0,
1614  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1615  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1616  final SqlNode arg1 = SqlStdOperatorTable.CAST.createCall(operand1.getParserPosition(),
1617  operand1,
1618  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1619  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1620  final SqlNode mulArg = SqlStdOperatorTable.MULTIPLY.createCall(pos, arg0, arg1);
1621  final SqlNode avgArg1 = SqlStdOperatorTable.AVG.createCall(pos, arg1);
1622  if (pop) {
1623  final SqlNode avgMulArg = SqlStdOperatorTable.AVG.createCall(pos, mulArg);
1624  final SqlNode avgArg0 = SqlStdOperatorTable.AVG.createCall(pos, arg0);
1625  final SqlNode mulAvgAvg =
1626  SqlStdOperatorTable.MULTIPLY.createCall(pos, avgArg0, avgArg1);
1627  final SqlNode covarPop =
1628  SqlStdOperatorTable.MINUS.createCall(pos, avgMulArg, mulAvgAvg);
1629  return SqlStdOperatorTable.CAST.createCall(pos,
1630  covarPop,
1631  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1632  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1633  }
1634  final SqlNode sumMulArg = SqlStdOperatorTable.SUM.createCall(pos, mulArg);
1635  final SqlNode sumArg0 = SqlStdOperatorTable.SUM.createCall(pos, arg0);
1636  final SqlNode mulSumAvg =
1637  SqlStdOperatorTable.MULTIPLY.createCall(pos, sumArg0, avgArg1);
1638  final SqlNode sub = SqlStdOperatorTable.MINUS.createCall(pos, sumMulArg, mulSumAvg);
1639  final SqlNode count = SqlStdOperatorTable.COUNT.createCall(pos, operand0);
1640  final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
1641  final SqlNode countEqOne = SqlStdOperatorTable.EQUALS.createCall(pos, count, one);
1642  final SqlNode countMinusOne = SqlStdOperatorTable.MINUS.createCall(pos, count, one);
1643  final SqlLiteral nul = SqlLiteral.createNull(pos);
1644  SqlNodeList whenList1 = new SqlNodeList(pos);
1645  SqlNodeList thenList1 = new SqlNodeList(pos);
1646  whenList1.add(countEqOne);
1647  thenList1.add(nul);
1648  final SqlNode int_denominator = SqlStdOperatorTable.CASE.createCall(
1649  null, pos, null, whenList1, thenList1, countMinusOne);
1650  final SqlNode denominator = SqlStdOperatorTable.CAST.createCall(pos,
1651  int_denominator,
1652  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1653  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1654  final SqlNode covarSamp =
1655  SqlStdOperatorTable.DIVIDE.createCall(pos, sub, denominator);
1656  return SqlStdOperatorTable.CAST.createCall(pos,
1657  covarSamp,
1658  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1659  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1660  }
1661 
1662  private SqlNode expandCorrelation(
1663  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1664  // Expand correlation coefficient
1665  if (proj_call.operandCount() != 2) {
1666  return null;
1667  }
1668  boolean flt;
1669  if (proj_call.getOperator().isName("CORR", false)
1670  || proj_call.getOperator().getName().equalsIgnoreCase("CORRELATION")) {
1671  // expand correlation coefficient
1672  flt = false;
1673  } else if (proj_call.getOperator().getName().equalsIgnoreCase("CORR_FLOAT")
1674  || proj_call.getOperator().getName().equalsIgnoreCase("CORRELATION_FLOAT")) {
1675  // expand correlation coefficient
1676  flt = true;
1677  } else {
1678  return null;
1679  }
1680  // corr(x, y) ==> (avg(x * y) - avg(x) * avg(y)) / (stddev_pop(x) *
1681  // stddev_pop(y))
1682  // ==> covar_pop(x, y) / (stddev_pop(x) * stddev_pop(y))
1683  final SqlNode operand0 = proj_call.operand(0);
1684  final SqlNode operand1 = proj_call.operand(1);
1685  final SqlParserPos pos = proj_call.getParserPosition();
1686  SqlNode covariance =
1687  expandCovariance(pos, operand0, operand1, true, flt, typeFactory);
1688  SqlNode stddev0 = expandVariance(pos, operand0, true, true, flt, typeFactory);
1689  SqlNode stddev1 = expandVariance(pos, operand1, true, true, flt, typeFactory);
1690  final SqlNode mulStddev =
1691  SqlStdOperatorTable.MULTIPLY.createCall(pos, stddev0, stddev1);
1692  final SqlNumericLiteral zero = SqlLiteral.createExactNumeric("0.0", pos);
1693  final SqlNode mulStddevEqZero =
1694  SqlStdOperatorTable.EQUALS.createCall(pos, mulStddev, zero);
1695  final SqlLiteral nul = SqlLiteral.createNull(pos);
1696  SqlNodeList whenList1 = new SqlNodeList(pos);
1697  SqlNodeList thenList1 = new SqlNodeList(pos);
1698  whenList1.add(mulStddevEqZero);
1699  thenList1.add(nul);
1700  final SqlNode denominator = SqlStdOperatorTable.CASE.createCall(
1701  null, pos, null, whenList1, thenList1, mulStddev);
1702  final SqlNode expanded_proj_call =
1703  SqlStdOperatorTable.DIVIDE.createCall(pos, covariance, denominator);
1704  HEAVYDBLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1705  HEAVYDBLOGGER.debug("to : " + expanded_proj_call.toString());
1706  return expanded_proj_call;
1707  }
1708 
1709  public SqlIdentifierCapturer captureIdentifiers(String sql, boolean legacy_syntax)
1710  throws SqlParseException {
1711  try {
1712  Planner planner = getPlanner();
1713  SqlNode node = parseSql(sql, legacy_syntax, planner);
1714  return captureIdentifiers(node);
1715  } catch (Exception | Error e) {
1716  HEAVYDBLOGGER.error("Error parsing sql: " + sql, e);
1717  return new SqlIdentifierCapturer();
1718  }
1719  }
1720 
1721  public SqlIdentifierCapturer captureIdentifiers(SqlNode node) throws SqlParseException {
1722  try {
1724  capturer.scan(node);
1725  return capturer;
1726  } catch (Exception | Error e) {
1727  HEAVYDBLOGGER.error("Error parsing sql: " + node, e);
1728  return new SqlIdentifierCapturer();
1729  }
1730  }
1731 
1732  public int getCallCount() {
1733  return callCount;
1734  }
1735 
1736  public void updateMetaData(String schema, String table) {
1737  HEAVYDBLOGGER.debug("schema :" + schema + " table :" + table);
1738  HeavyDBSchema db =
1739  new HeavyDBSchema(dataDir, this, dbPort, null, sock_transport_properties);
1740  db.updateMetaData(schema, table);
1741  }
1742 
1743  protected RelDataTypeSystem createTypeSystem() {
1744  final HeavyDBTypeSystem typeSystem = new HeavyDBTypeSystem();
1745  return typeSystem;
1746  }
1747 
1749  extends SqlBasicVisitor<Void> {
1750  @Override
1751  public Void visit(SqlCall call) {
1752  if (call instanceof SqlSelect) {
1753  SqlSelect selectNode = (SqlSelect) call;
1754  String targetString = targetExpression.toString();
1755  for (SqlNode listedNode : selectNode.getSelectList()) {
1756  if (listedNode.toString().contains(targetString)) {
1757  throw Util.FoundOne.NULL;
1758  }
1759  }
1760  }
1761  return super.visit(call);
1762  }
1763 
1764  boolean containsExpression(SqlNode node, SqlNode targetExpression) {
1765  try {
1766  this.targetExpression = targetExpression;
1767  node.accept(this);
1768  return false;
1769  } catch (Util.FoundOne e) {
1770  return true;
1771  }
1772  }
1773 
1775  }
1776 
1778  extends SqlBasicVisitor<Void> {
1779  @Override
1780  public Void visit(SqlCall call) {
1781  if (call instanceof SqlBasicCall) {
1782  SqlBasicCall basicCall = (SqlBasicCall) call;
1783  if (basicCall.getKind() == SqlKind.OR) {
1784  String targetString = targetExpression.toString();
1785  for (SqlNode listedOperand : basicCall.operands) {
1786  if (listedOperand.toString().contains(targetString)) {
1787  throw Util.FoundOne.NULL;
1788  }
1789  }
1790  }
1791  }
1792  return super.visit(call);
1793  }
1794 
1795  boolean containsExpression(SqlNode node, SqlNode targetExpression) {
1796  try {
1797  this.targetExpression = targetExpression;
1798  node.accept(this);
1799  return false;
1800  } catch (Util.FoundOne e) {
1801  return true;
1802  }
1803  }
1804 
1806  }
1807 
1808  private static class JoinOperatorChecker extends SqlBasicVisitor<Void> {
1809  Set<SqlBasicCall> targetCalls = new HashSet<>();
1810 
1811  public boolean isEqualityJoinOperator(SqlBasicCall basicCall) {
1812  if (null != basicCall) {
1813  if (basicCall.operands.length == 2
1814  && (basicCall.getKind() == SqlKind.EQUALS
1815  || basicCall.getKind() == SqlKind.NOT_EQUALS)
1816  && basicCall.operand(0) instanceof SqlIdentifier
1817  && basicCall.operand(1) instanceof SqlIdentifier) {
1818  return true;
1819  }
1820  }
1821  return false;
1822  }
1823 
1824  @Override
1825  public Void visit(SqlCall call) {
1826  if (call instanceof SqlBasicCall) {
1827  targetCalls.add((SqlBasicCall) call);
1828  }
1829  for (SqlNode node : call.getOperandList()) {
1830  if (null != node && !targetCalls.contains(node)) {
1831  node.accept(this);
1832  }
1833  }
1834  return super.visit(call);
1835  }
1836 
1837  boolean containsExpression(SqlNode node) {
1838  try {
1839  if (null != node) {
1840  node.accept(this);
1841  for (SqlBasicCall basicCall : targetCalls) {
1842  if (isEqualityJoinOperator(basicCall)) {
1843  throw Util.FoundOne.NULL;
1844  }
1845  }
1846  }
1847  return false;
1848  } catch (Util.FoundOne e) {
1849  return true;
1850  }
1851  }
1852  }
1853 
1854  // this visitor checks whether a parse tree contains at least one
1855  // specific SQL operator we have an interest in
1856  // (do not count the accurate # operators we found)
1857  private static class FindSqlOperator extends SqlBasicVisitor<Void> {
1858  @Override
1859  public Void visit(SqlCall call) {
1860  if (call instanceof SqlBasicCall) {
1861  SqlBasicCall basicCall = (SqlBasicCall) call;
1862  if (basicCall.getKind().equals(targetKind)) {
1863  throw Util.FoundOne.NULL;
1864  }
1865  }
1866  return super.visit(call);
1867  }
1868 
1869  boolean containsSqlOperator(SqlNode node, SqlKind operatorKind) {
1870  try {
1871  targetKind = operatorKind;
1872  node.accept(this);
1873  return false;
1874  } catch (Util.FoundOne e) {
1875  return true;
1876  }
1877  }
1878 
1879  private SqlKind targetKind;
1880  }
1881 
1882  public void tableAliasFinder(SqlNode sqlNode, Map<String, String> tableAliasMap) {
1883  final SqlVisitor<Void> aliasCollector = new SqlBasicVisitor<Void>() {
1884  @Override
1885  public Void visit(SqlCall call) {
1886  if (call instanceof SqlBasicCall) {
1887  SqlBasicCall basicCall = (SqlBasicCall) call;
1888  if (basicCall.getKind() == SqlKind.AS) {
1889  if (basicCall.operand(0) instanceof SqlIdentifier) {
1890  // we need to check whether basicCall's the first operand is SqlIdentifier
1891  // since sometimes it represents non column identifier like SqlSelect
1892  SqlIdentifier colNameIdentifier = (SqlIdentifier) basicCall.operand(0);
1893  String tblName = colNameIdentifier.names.size() == 1
1894  ? colNameIdentifier.names.get(0)
1895  : colNameIdentifier.names.get(1);
1896  tableAliasMap.put(basicCall.operand(1).toString(), tblName);
1897  }
1898  }
1899  }
1900  return super.visit(call);
1901  }
1902  };
1903  sqlNode.accept(aliasCollector);
1904  }
1905 }
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:164
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)
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