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