OmniSciDB  0264ff685a
MapDParser.java
Go to the documentation of this file.
1 /*
2  * Copyright 2019 OmniSci, Inc.
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 package com.mapd.calcite.parser;
17 
18 import static org.apache.calcite.sql.parser.SqlParserPos.ZERO;
19 
20 import com.google.common.collect.ImmutableList;
22 import com.mapd.parser.extension.ddl.ExtendedSqlParser;
26 
27 import org.apache.calcite.avatica.util.Casing;
28 import org.apache.calcite.config.CalciteConnectionConfig;
29 import org.apache.calcite.config.CalciteConnectionConfigImpl;
30 import org.apache.calcite.config.CalciteConnectionProperty;
31 import org.apache.calcite.plan.Context;
32 import org.apache.calcite.plan.RelOptCluster;
33 import org.apache.calcite.plan.RelOptLattice;
34 import org.apache.calcite.plan.RelOptMaterialization;
35 import org.apache.calcite.plan.RelOptPlanner;
36 import org.apache.calcite.plan.RelOptTable;
37 import org.apache.calcite.plan.RelOptUtil;
38 import org.apache.calcite.plan.hep.HepPlanner;
39 import org.apache.calcite.plan.hep.HepProgram;
40 import org.apache.calcite.plan.hep.HepProgramBuilder;
41 import org.apache.calcite.plan.volcano.VolcanoPlanner;
42 import org.apache.calcite.prepare.CalciteCatalogReader;
45 import org.apache.calcite.rel.RelNode;
46 import org.apache.calcite.rel.RelRoot;
47 import org.apache.calcite.rel.RelShuttleImpl;
48 import org.apache.calcite.rel.core.Join;
49 import org.apache.calcite.rel.core.RelFactories;
50 import org.apache.calcite.rel.core.TableModify;
51 import org.apache.calcite.rel.core.TableModify.Operation;
53 import org.apache.calcite.rel.hint.HintStrategyTable;
54 import org.apache.calcite.rel.logical.LogicalProject;
55 import org.apache.calcite.rel.logical.LogicalTableModify;
56 import org.apache.calcite.rel.metadata.DefaultRelMetadataProvider;
57 import org.apache.calcite.rel.rules.CoreRules;
58 import org.apache.calcite.rel.rules.FilterMergeRule;
59 import org.apache.calcite.rel.rules.FilterProjectTransposeRule;
60 import org.apache.calcite.rel.rules.JoinProjectTransposeRule;
61 import org.apache.calcite.rel.rules.ProjectMergeRule;
62 import org.apache.calcite.rel.rules.ProjectRemoveRule;
63 import org.apache.calcite.rel.type.RelDataType;
64 import org.apache.calcite.rel.type.RelDataTypeFactory;
65 import org.apache.calcite.rel.type.RelDataTypeField;
66 import org.apache.calcite.rel.type.RelDataTypeSystem;
67 import org.apache.calcite.rex.RexBuilder;
68 import org.apache.calcite.rex.RexCall;
69 import org.apache.calcite.rex.RexInputRef;
70 import org.apache.calcite.rex.RexNode;
71 import org.apache.calcite.rex.RexShuttle;
72 import org.apache.calcite.runtime.CalciteException;
73 import org.apache.calcite.schema.SchemaPlus;
74 import org.apache.calcite.sql.JoinConditionType;
75 import org.apache.calcite.sql.JoinType;
76 import org.apache.calcite.sql.SqlBasicCall;
77 import org.apache.calcite.sql.SqlBasicTypeNameSpec;
78 import org.apache.calcite.sql.SqlCall;
79 import org.apache.calcite.sql.SqlDataTypeSpec;
80 import org.apache.calcite.sql.SqlDdl;
81 import org.apache.calcite.sql.SqlDelete;
82 import org.apache.calcite.sql.SqlFunctionCategory;
83 import org.apache.calcite.sql.SqlIdentifier;
84 import org.apache.calcite.sql.SqlJoin;
85 import org.apache.calcite.sql.SqlKind;
86 import org.apache.calcite.sql.SqlLiteral;
87 import org.apache.calcite.sql.SqlNode;
88 import org.apache.calcite.sql.SqlNodeList;
89 import org.apache.calcite.sql.SqlNumericLiteral;
90 import org.apache.calcite.sql.SqlOrderBy;
91 import org.apache.calcite.sql.SqlSelect;
92 import org.apache.calcite.sql.SqlUnresolvedFunction;
93 import org.apache.calcite.sql.SqlUpdate;
94 import org.apache.calcite.sql.SqlWith;
95 import org.apache.calcite.sql.dialect.CalciteSqlDialect;
96 import org.apache.calcite.sql.fun.SqlCase;
97 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
98 import org.apache.calcite.sql.parser.SqlParseException;
99 import org.apache.calcite.sql.parser.SqlParser;
100 import org.apache.calcite.sql.parser.SqlParserPos;
101 import org.apache.calcite.sql.type.SqlTypeName;
102 import org.apache.calcite.sql.type.SqlTypeUtil;
103 import org.apache.calcite.sql.util.SqlBasicVisitor;
104 import org.apache.calcite.sql.util.SqlShuttle;
105 import org.apache.calcite.sql.validate.SqlConformanceEnum;
107 import org.apache.calcite.tools.FrameworkConfig;
108 import org.apache.calcite.tools.Frameworks;
109 import org.apache.calcite.tools.Planner;
110 import org.apache.calcite.tools.Program;
111 import org.apache.calcite.tools.Programs;
112 import org.apache.calcite.tools.RelConversionException;
113 import org.apache.calcite.tools.ValidationException;
114 import org.apache.calcite.util.Pair;
115 import org.apache.calcite.util.Util;
116 import org.slf4j.Logger;
117 import org.slf4j.LoggerFactory;
118 
119 import java.io.IOException;
120 import java.lang.reflect.Field;
121 import java.util.ArrayList;
122 import java.util.Arrays;
123 import java.util.Collections;
124 import java.util.EnumSet;
125 import java.util.HashSet;
126 import java.util.List;
127 import java.util.Map;
128 import java.util.Properties;
129 import java.util.Set;
130 import java.util.concurrent.ConcurrentHashMap;
131 import java.util.function.BiPredicate;
132 import java.util.function.Supplier;
133 
138 public final class MapDParser {
139  public static final ThreadLocal<MapDParser> CURRENT_PARSER = new ThreadLocal<>();
140  private static final EnumSet<SqlKind> SCALAR =
141  EnumSet.of(SqlKind.SCALAR_QUERY, SqlKind.SELECT);
142  private static final EnumSet<SqlKind> EXISTS = EnumSet.of(SqlKind.EXISTS);
143  private static final EnumSet<SqlKind> DELETE = EnumSet.of(SqlKind.DELETE);
144  private static final EnumSet<SqlKind> UPDATE = EnumSet.of(SqlKind.UPDATE);
145  private static final EnumSet<SqlKind> IN = EnumSet.of(SqlKind.IN);
146  private static final EnumSet<SqlKind> ARRAY_VALUE =
147  EnumSet.of(SqlKind.ARRAY_VALUE_CONSTRUCTOR);
148 
149  final static Logger MAPDLOGGER = LoggerFactory.getLogger(MapDParser.class);
150 
151  private final Supplier<MapDSqlOperatorTable> mapDSqlOperatorTable;
152  private final String dataDir;
153 
154  private int callCount = 0;
155  private final int mapdPort;
157  SqlNode sqlNode_;
159 
160  private static Map<String, Boolean> SubqueryCorrMemo = new ConcurrentHashMap<>();
161 
162  public MapDParser(String dataDir,
163  final Supplier<MapDSqlOperatorTable> mapDSqlOperatorTable,
164  int mapdPort,
166  this.dataDir = dataDir;
167  this.mapDSqlOperatorTable = mapDSqlOperatorTable;
168  this.mapdPort = mapdPort;
169  this.sock_transport_properties = skT;
170  }
171 
172  public void clearMemo() {
173  SubqueryCorrMemo.clear();
174  }
175 
176  private static final Context MAPD_CONNECTION_CONTEXT = new Context() {
177  MapDTypeSystem myTypeSystem = new MapDTypeSystem();
178  CalciteConnectionConfig config = new CalciteConnectionConfigImpl(new Properties()) {
179  {
180  properties.put(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
181  String.valueOf(false));
182  properties.put(CalciteConnectionProperty.CONFORMANCE.camelName(),
183  String.valueOf(SqlConformanceEnum.LENIENT));
184  }
185 
186  @SuppressWarnings("unchecked")
187  public <T extends Object> T typeSystem(
188  java.lang.Class<T> typeSystemClass, T defaultTypeSystem) {
189  return (T) myTypeSystem;
190  };
191 
192  public boolean caseSensitive() {
193  return false;
194  };
195 
196  public org.apache.calcite.sql.validate.SqlConformance conformance() {
197  return SqlConformanceEnum.LENIENT;
198  };
199  };
200 
201  @Override
202  public <C> C unwrap(Class<C> aClass) {
203  if (aClass.isInstance(config)) {
204  return aClass.cast(config);
205  }
206  return null;
207  }
208  };
209 
211  return getPlanner(true, true);
212  }
213 
214  private boolean isCorrelated(SqlNode expression) {
215  String queryString = expression.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
216  Boolean isCorrelatedSubquery = SubqueryCorrMemo.get(queryString);
217  if (null != isCorrelatedSubquery) {
218  return isCorrelatedSubquery;
219  }
220 
221  try {
223  dataDir, mapDSqlOperatorTable, mapdPort, sock_transport_properties);
224  MapDParserOptions options = new MapDParserOptions();
225  parser.setUser(mapdUser);
226  parser.processSql(expression, options);
227  } catch (Exception e) {
228  // if we are not able to parse, then assume correlated
229  SubqueryCorrMemo.put(queryString, true);
230  return true;
231  }
232  SubqueryCorrMemo.put(queryString, false);
233  return false;
234  }
235 
236  private MapDPlanner getPlanner(final boolean allowSubQueryExpansion,
237  final boolean allowPushdownJoinCondition) {
238  final MapDSchema mapd =
239  new MapDSchema(dataDir, this, mapdPort, mapdUser, sock_transport_properties);
240  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
241 
242  BiPredicate<SqlNode, SqlNode> expandPredicate = new BiPredicate<SqlNode, SqlNode>() {
243  @Override
244  public boolean test(SqlNode root, SqlNode expression) {
245  if (!allowSubQueryExpansion) {
246  return false;
247  }
248 
249  // special handling of sub-queries
250  if (expression.isA(SCALAR) || expression.isA(EXISTS) || expression.isA(IN)) {
251  // only expand if it is correlated.
252 
253  if (expression.isA(EXISTS)) {
254  // always expand subquery by EXISTS clause
255  return true;
256  }
257 
258  if (expression.isA(IN)) {
259  // expand subquery by IN clause
260  // but correlated subquery by NOT_IN clause is not available
261  // currently due to a lack of supporting in Calcite
262  boolean found_expression = false;
263  if (expression instanceof SqlCall) {
264  SqlCall call = (SqlCall) expression;
265  if (call.getOperandList().size() == 2) {
266  // if IN clause is correlated, its second operand of corresponding
267  // expression is SELECT clause which indicates a correlated subquery.
268  // Here, an expression "f.val IN (SELECT ...)" has two operands.
269  // Since we have interest in its subquery, so try to check whether
270  // the second operand, i.e., call.getOperandList().get(1)
271  // is a type of SqlSelect and also is correlated.
272  // Note that the second operand of non-correlated IN clause
273  // does not have SqlSelect as its second operand
274  if (call.getOperandList().get(1) instanceof SqlSelect) {
275  expression = call.getOperandList().get(1);
276  SqlSelect select_call = (SqlSelect) expression;
277  if (select_call.hasWhere()) {
278  found_expression = true;
279  }
280  }
281  }
282  }
283  if (!found_expression) {
284  return false;
285  }
286  }
287 
288  if (isCorrelated(expression)) {
289  SqlSelect select = null;
290  if (expression instanceof SqlCall) {
291  SqlCall call = (SqlCall) expression;
292  if (call.getOperator().equals(SqlStdOperatorTable.SCALAR_QUERY)) {
293  expression = call.getOperandList().get(0);
294  }
295  }
296 
297  if (expression instanceof SqlSelect) {
298  select = (SqlSelect) expression;
299  }
300 
301  if (null != select) {
302  if (null != select.getFetch() || null != select.getOffset()
303  || (null != select.getOrderList()
304  && select.getOrderList().size() != 0)) {
305  throw new CalciteException(
306  "Correlated sub-queries with ordering not supported.", null);
307  }
308  }
309  return true;
310  }
311  }
312 
313  // per default we do not want to expand
314  return false;
315  }
316  };
317 
318  BiPredicate<SqlNode, Join> pushdownJoinPredicate = new BiPredicate<SqlNode, Join>() {
319  @Override
320  public boolean test(SqlNode t, Join u) {
321  if (!allowPushdownJoinCondition) {
322  return false;
323  }
324 
325  return !hasGeoColumns(u.getRowType());
326  }
327 
328  private boolean hasGeoColumns(RelDataType type) {
329  for (RelDataTypeField f : type.getFieldList()) {
330  if ("any".equalsIgnoreCase(f.getType().getFamily().toString())) {
331  // any indicates geo types at the moment
332  return true;
333  }
334  }
335 
336  return false;
337  }
338  };
339 
340  final FrameworkConfig config =
341  Frameworks.newConfigBuilder()
342  .defaultSchema(rootSchema.add(mapdUser.getDB(), mapd))
343  .operatorTable(mapDSqlOperatorTable.get())
344  .parserConfig(SqlParser.configBuilder()
345  .setConformance(SqlConformanceEnum.LENIENT)
346  .setUnquotedCasing(Casing.UNCHANGED)
347  .setCaseSensitive(false)
348  // allow identifiers of up to 512 chars
349  .setIdentifierMaxLength(512)
350  .setParserFactory(ExtendedSqlParser.FACTORY)
351  .build())
352  .sqlToRelConverterConfig(
354  .configBuilder()
355  // enable sub-query expansion (de-correlation)
356  .withExpandPredicate(expandPredicate)
357  // allow as many as possible IN operator values
358  .withInSubQueryThreshold(Integer.MAX_VALUE)
359  .withPushdownJoinCondition(pushdownJoinPredicate)
360  .withHintStrategyTable(
362  .build())
363  .typeSystem(createTypeSystem())
364  .context(MAPD_CONNECTION_CONTEXT)
365  .build();
366  return new MapDPlanner(config);
367  }
368 
369  public void setUser(MapDUser mapdUser) {
370  this.mapdUser = mapdUser;
371  }
372 
373  public Pair<String, SqlIdentifierCapturer> process(
374  String sql, final MapDParserOptions parserOptions)
375  throws SqlParseException, ValidationException, RelConversionException {
376  final MapDPlanner planner = getPlanner(true, true);
377  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
378  String res = processSql(sqlNode, parserOptions);
379  SqlIdentifierCapturer capture = captureIdentifiers(sqlNode);
380 
381  return new Pair<String, SqlIdentifierCapturer>(res, capture);
382  }
383 
384  public String optimizeRAQuery(String query) throws IOException {
385  MapDSchema schema =
386  new MapDSchema(dataDir, this, mapdPort, mapdUser, sock_transport_properties);
387  MapDPlanner planner = getPlanner(true, true);
388  RelRoot optRel = planner.optimizeRaQuery(query, schema);
389  optRel = replaceIsTrue(planner.getTypeFactory(), optRel);
390  return MapDSerializer.toString(optRel.project());
391  }
392 
393  public String processSql(String sql, final MapDParserOptions parserOptions)
394  throws SqlParseException, ValidationException, RelConversionException {
395  callCount++;
396 
397  final MapDPlanner planner = getPlanner(true, true);
398  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
399 
400  return processSql(sqlNode, parserOptions);
401  }
402 
403  public String processSql(final SqlNode sqlNode, final MapDParserOptions parserOptions)
404  throws SqlParseException, ValidationException, RelConversionException {
405  callCount++;
406 
407  if (sqlNode instanceof JsonSerializableDdl) {
408  return ((JsonSerializableDdl) sqlNode).toJsonString();
409  }
410 
411  if (sqlNode instanceof SqlDdl) {
412  return sqlNode.toString();
413  }
414 
415  final MapDPlanner planner = getPlanner(true, true);
416  planner.advanceToValidate();
417 
418  final RelRoot sqlRel = convertSqlToRelNode(sqlNode, planner, parserOptions);
419  RelNode project = sqlRel.project();
420 
421  if (parserOptions.isExplain()) {
422  return RelOptUtil.toString(sqlRel.project());
423  }
424 
425  String res = MapDSerializer.toString(project);
426 
427  return res;
428  }
429 
431  String sql, int cursor, List<String> visible_tables) {
432  return getPlanner().getCompletionHints(sql, cursor, visible_tables);
433  }
434 
435  public Set<String> resolveSelectIdentifiers(SqlIdentifierCapturer capturer) {
436  MapDSchema schema =
437  new MapDSchema(dataDir, this, mapdPort, mapdUser, sock_transport_properties);
438  HashSet<String> resolved = new HashSet<>();
439 
440  for (String name : capturer.selects) {
441  MapDTable table = (MapDTable) schema.getTable(name);
442  if (null == table) {
443  throw new RuntimeException("table/view not found: " + name);
444  }
445 
446  if (table instanceof MapDView) {
447  MapDView view = (MapDView) table;
448  resolved.addAll(resolveSelectIdentifiers(view.getAccessedObjects()));
449  } else {
450  resolved.add(name);
451  }
452  }
453 
454  return resolved;
455  }
456 
457  private String getTableName(SqlNode node) {
458  if (node.isA(EnumSet.of(SqlKind.AS))) {
459  node = ((SqlCall) node).getOperandList().get(1);
460  }
461  if (node instanceof SqlIdentifier) {
462  SqlIdentifier id = (SqlIdentifier) node;
463  return id.names.get(id.names.size() - 1);
464  }
465  return null;
466  }
467 
468  private SqlSelect rewriteSimpleUpdateAsSelect(final SqlUpdate update) {
469  SqlNode where = update.getCondition();
470 
471  if (update.getSourceExpressionList().size() != 1) {
472  return null;
473  }
474 
475  if (!(update.getSourceExpressionList().get(0) instanceof SqlSelect)) {
476  return null;
477  }
478 
479  final SqlSelect inner = (SqlSelect) update.getSourceExpressionList().get(0);
480 
481  if (null != inner.getGroup() || null != inner.getFetch() || null != inner.getOffset()
482  || (null != inner.getOrderList() && inner.getOrderList().size() != 0)
483  || (null != inner.getGroup() && inner.getGroup().size() != 0)
484  || null == getTableName(inner.getFrom())) {
485  return null;
486  }
487 
488  if (!isCorrelated(inner)) {
489  return null;
490  }
491 
492  final String updateTableName = getTableName(update.getTargetTable());
493 
494  if (null != where) {
495  where = where.accept(new SqlShuttle() {
496  @Override
497  public SqlNode visit(SqlIdentifier id) {
498  if (id.isSimple()) {
499  id = new SqlIdentifier(Arrays.asList(updateTableName, id.getSimple()),
500  id.getParserPosition());
501  }
502 
503  return id;
504  }
505  });
506  }
507 
508  SqlJoin join = new SqlJoin(ZERO,
509  update.getTargetTable(),
510  SqlLiteral.createBoolean(false, ZERO),
511  SqlLiteral.createSymbol(JoinType.LEFT, ZERO),
512  inner.getFrom(),
513  SqlLiteral.createSymbol(JoinConditionType.ON, ZERO),
514  inner.getWhere());
515 
516  SqlNode select0 = inner.getSelectList().get(0);
517 
518  boolean wrapInSingleValue = true;
519  if (select0 instanceof SqlCall) {
520  SqlCall selectExprCall = (SqlCall) select0;
521  if (Util.isSingleValue(selectExprCall)) {
522  wrapInSingleValue = false;
523  }
524  }
525 
526  if (wrapInSingleValue) {
527  select0 = new SqlBasicCall(
528  SqlStdOperatorTable.SINGLE_VALUE, new SqlNode[] {select0}, ZERO);
529  }
530 
531  SqlNodeList selectList = new SqlNodeList(ZERO);
532  selectList.add(select0);
533  selectList.add(new SqlBasicCall(SqlStdOperatorTable.AS,
534  new SqlNode[] {new SqlBasicCall(
535  new SqlUnresolvedFunction(
536  new SqlIdentifier("OFFSET_IN_FRAGMENT", ZERO),
537  null,
538  null,
539  null,
540  null,
541  SqlFunctionCategory.USER_DEFINED_FUNCTION),
542  new SqlNode[0],
543  SqlParserPos.ZERO),
544  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
545  ZERO));
546 
547  SqlNodeList groupBy = new SqlNodeList(ZERO);
548  groupBy.add(new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO));
549 
550  SqlSelect select = new SqlSelect(ZERO,
551  null,
552  selectList,
553  join,
554  where,
555  groupBy,
556  null,
557  null,
558  null,
559  null,
560  null,
561  null);
562  return select;
563  }
564 
565  private LogicalTableModify getDummyUpdate(SqlUpdate update)
566  throws SqlParseException, ValidationException, RelConversionException {
567  SqlIdentifier targetTable = (SqlIdentifier) update.getTargetTable();
568  String targetTableName = targetTable.names.get(targetTable.names.size() - 1);
569  MapDPlanner planner = getPlanner();
570  String dummySql = "DELETE FROM " + targetTableName;
571  SqlNode dummyNode = planner.parse(dummySql);
572  dummyNode = planner.validate(dummyNode);
573  RelRoot dummyRoot = planner.rel(dummyNode);
574  LogicalTableModify dummyModify = (LogicalTableModify) dummyRoot.rel;
575  return dummyModify;
576  }
577 
578  private RelRoot rewriteUpdateAsSelect(SqlUpdate update, MapDParserOptions parserOptions)
579  throws SqlParseException, ValidationException, RelConversionException {
580  int correlatedQueriesCount[] = new int[1];
581  SqlBasicVisitor<Void> correlatedQueriesCounter = new SqlBasicVisitor<Void>() {
582  @Override
583  public Void visit(SqlCall call) {
584  if (call.isA(SCALAR)
585  && ((call instanceof SqlBasicCall && call.operandCount() == 1
586  && !call.operand(0).isA(SCALAR))
587  || !(call instanceof SqlBasicCall))) {
588  if (isCorrelated(call)) {
589  correlatedQueriesCount[0]++;
590  }
591  }
592  return super.visit(call);
593  }
594  };
595 
596  update.accept(correlatedQueriesCounter);
597  if (correlatedQueriesCount[0] > 1) {
598  throw new CalciteException(
599  "table modifications with multiple correlated sub-queries not supported.",
600  null);
601  }
602 
603  boolean allowPushdownJoinCondition = false;
604  SqlNodeList sourceExpression = new SqlNodeList(SqlParserPos.ZERO);
605  LogicalTableModify dummyModify = getDummyUpdate(update);
606  RelOptTable targetTable = dummyModify.getTable();
607  RelDataType targetTableType = targetTable.getRowType();
608 
609  SqlSelect select = rewriteSimpleUpdateAsSelect(update);
610  boolean applyRexCast = null == select;
611 
612  if (null == select) {
613  for (int i = 0; i < update.getSourceExpressionList().size(); i++) {
614  SqlNode targetColumn = update.getTargetColumnList().get(i);
615  SqlNode expression = update.getSourceExpressionList().get(i);
616 
617  if (!(targetColumn instanceof SqlIdentifier)) {
618  throw new RuntimeException("Unknown identifier type!");
619  }
620  SqlIdentifier id = (SqlIdentifier) targetColumn;
621  RelDataType fieldType =
622  targetTableType.getField(id.names.get(id.names.size() - 1), false, false)
623  .getType();
624 
625  if (expression.isA(ARRAY_VALUE) && null != fieldType.getComponentType()) {
626  // apply a cast to all array value elements
627 
628  SqlDataTypeSpec elementType = new SqlDataTypeSpec(
629  new SqlBasicTypeNameSpec(fieldType.getComponentType().getSqlTypeName(),
630  fieldType.getPrecision(),
631  fieldType.getScale(),
632  null == fieldType.getCharset() ? null
633  : fieldType.getCharset().name(),
634  SqlParserPos.ZERO),
635  SqlParserPos.ZERO);
636  SqlCall array_expression = (SqlCall) expression;
637  ArrayList<SqlNode> values = new ArrayList<>();
638 
639  for (SqlNode value : array_expression.getOperandList()) {
640  if (value.isA(EnumSet.of(SqlKind.LITERAL))) {
641  SqlNode casted_value = new SqlBasicCall(SqlStdOperatorTable.CAST,
642  new SqlNode[] {value, elementType},
643  value.getParserPosition());
644  values.add(casted_value);
645  } else {
646  values.add(value);
647  }
648  }
649 
650  expression = new SqlBasicCall(MapDSqlOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
651  values.toArray(new SqlNode[0]),
652  expression.getParserPosition());
653  }
654  sourceExpression.add(expression);
655  }
656 
657  sourceExpression.add(new SqlBasicCall(SqlStdOperatorTable.AS,
658  new SqlNode[] {
659  new SqlBasicCall(new SqlUnresolvedFunction(
660  new SqlIdentifier("OFFSET_IN_FRAGMENT",
661  SqlParserPos.ZERO),
662  null,
663  null,
664  null,
665  null,
666  SqlFunctionCategory.USER_DEFINED_FUNCTION),
667  new SqlNode[0],
668  SqlParserPos.ZERO),
669  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
670  ZERO));
671 
672  select = new SqlSelect(SqlParserPos.ZERO,
673  null,
674  sourceExpression,
675  update.getTargetTable(),
676  update.getCondition(),
677  null,
678  null,
679  null,
680  null,
681  null,
682  null,
683  null);
684  }
685 
686  MapDPlanner planner = getPlanner(true, allowPushdownJoinCondition);
687  SqlNode node = planner.parse(select.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
688  node = planner.validate(node);
689  RelRoot root = planner.rel(node);
690  LogicalProject project = (LogicalProject) root.project();
691 
692  ArrayList<String> fields = new ArrayList<String>();
693  ArrayList<RexNode> nodes = new ArrayList<RexNode>();
694  final RexBuilder builder = new RexBuilder(planner.getTypeFactory());
695 
696  for (SqlNode n : update.getTargetColumnList()) {
697  if (n instanceof SqlIdentifier) {
698  SqlIdentifier id = (SqlIdentifier) n;
699  fields.add(id.names.get(id.names.size() - 1));
700  } else {
701  throw new RuntimeException("Unknown identifier type!");
702  }
703  }
704 
705  int idx = 0;
706  for (RexNode exp : project.getChildExps()) {
707  if (applyRexCast && idx + 1 < project.getChildExps().size()) {
708  RelDataType expectedFieldType =
709  targetTableType.getField(fields.get(idx), false, false).getType();
710  if (!exp.getType().equals(expectedFieldType) && !exp.isA(ARRAY_VALUE)) {
711  exp = builder.makeCast(expectedFieldType, exp);
712  }
713  }
714 
715  nodes.add(exp);
716  idx++;
717  }
718 
719  ArrayList<RexNode> inputs = new ArrayList<RexNode>();
720  int n = 0;
721  for (int i = 0; i < fields.size(); i++) {
722  inputs.add(
723  new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
724  n++;
725  }
726 
727  fields.add("EXPR$DELETE_OFFSET_IN_FRAGMENT");
728  inputs.add(new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
729 
730  project = project.copy(
731  project.getTraitSet(), project.getInput(), nodes, project.getRowType());
732 
733  LogicalTableModify modify = LogicalTableModify.create(targetTable,
734  dummyModify.getCatalogReader(),
735  project,
736  Operation.UPDATE,
737  fields,
738  inputs,
739  true);
740  return RelRoot.of(modify, SqlKind.UPDATE);
741  }
742 
743  RelRoot queryToRelNode(final String sql, final MapDParserOptions parserOptions)
744  throws SqlParseException, ValidationException, RelConversionException {
745  final MapDPlanner planner = getPlanner(true, true);
746  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
747  return convertSqlToRelNode(sqlNode, planner, parserOptions);
748  }
749 
750  RelRoot convertSqlToRelNode(final SqlNode sqlNode,
751  final MapDPlanner mapDPlanner,
752  final MapDParserOptions parserOptions)
753  throws SqlParseException, ValidationException, RelConversionException {
754  SqlNode node = sqlNode;
755  MapDPlanner planner = mapDPlanner;
756  boolean allowCorrelatedSubQueryExpansion = true;
757  boolean allowPushdownJoinCondition = true;
758  boolean patchUpdateToDelete = false;
759 
760  if (node.isA(DELETE)) {
761  SqlDelete sqlDelete = (SqlDelete) node;
762  node = new SqlUpdate(node.getParserPosition(),
763  sqlDelete.getTargetTable(),
764  SqlNodeList.EMPTY,
765  SqlNodeList.EMPTY,
766  sqlDelete.getCondition(),
767  sqlDelete.getSourceSelect(),
768  sqlDelete.getAlias());
769 
770  patchUpdateToDelete = true;
771  }
772 
773  if (node.isA(UPDATE)) {
774  SqlUpdate update = (SqlUpdate) node;
775  update = (SqlUpdate) planner.validate(update);
776  RelRoot root = rewriteUpdateAsSelect(update, parserOptions);
777 
778  if (patchUpdateToDelete) {
779  LogicalTableModify modify = (LogicalTableModify) root.rel;
780 
781  try {
782  Field f = TableModify.class.getDeclaredField("operation");
783  f.setAccessible(true);
784  f.set(modify, Operation.DELETE);
785  } catch (Throwable e) {
786  throw new RuntimeException(e);
787  }
788 
789  root = RelRoot.of(modify, SqlKind.DELETE);
790  }
791 
792  return root;
793  }
794 
795  if (parserOptions.isLegacySyntax()) {
796  // close original planner
797  planner.close();
798  // create a new one
799  planner = getPlanner(allowCorrelatedSubQueryExpansion, allowPushdownJoinCondition);
800  node = parseSql(
801  node.toSqlString(CalciteSqlDialect.DEFAULT).toString(), false, planner);
802  }
803 
804  SqlNode validateR = planner.validate(node);
805  planner.setFilterPushDownInfo(parserOptions.getFilterPushDownInfo());
806  RelRoot relR = planner.rel(validateR);
807  relR = replaceIsTrue(planner.getTypeFactory(), relR);
808  planner.close();
809 
810  if (!parserOptions.isViewOptimizeEnabled()) {
811  return relR;
812  } else {
813  // check to see if a view is involved in the query
814  boolean foundView = false;
815  MapDSchema schema = new MapDSchema(
816  dataDir, this, mapdPort, mapdUser, sock_transport_properties);
817  SqlIdentifierCapturer capturer = captureIdentifiers(sqlNode);
818  for (String name : capturer.selects) {
819  MapDTable table = (MapDTable) schema.getTable(name);
820  if (null == table) {
821  throw new RuntimeException("table/view not found: " + name);
822  }
823  if (table instanceof MapDView) {
824  foundView = true;
825  }
826  }
827 
828  if (!foundView) {
829  return relR;
830  }
831 
832  HepProgramBuilder builder = new HepProgramBuilder();
833  builder.addRuleInstance(CoreRules.JOIN_PROJECT_BOTH_TRANSPOSE_INCLUDE_OUTER);
834  builder.addRuleInstance(CoreRules.FILTER_MERGE);
835  builder.addRuleInstance(CoreRules.FILTER_PROJECT_TRANSPOSE);
836  builder.addRuleInstance(CoreRules.PROJECT_MERGE);
837  builder.addRuleInstance(ProjectProjectRemoveRule.INSTANCE);
838 
839  HepPlanner hepPlanner = new HepPlanner(builder.build());
840  final RelNode root = relR.project();
841  hepPlanner.setRoot(root);
842  final RelNode newRel = hepPlanner.findBestExp();
843 
844  return RelRoot.of(newRel, relR.kind);
845  }
846  }
847 
848  private RelRoot replaceIsTrue(final RelDataTypeFactory typeFactory, RelRoot root) {
849  final RexShuttle callShuttle = new RexShuttle() {
850  RexBuilder builder = new RexBuilder(typeFactory);
851 
852  public RexNode visitCall(RexCall call) {
853  call = (RexCall) super.visitCall(call);
854  if (call.getKind() == SqlKind.IS_TRUE) {
855  return builder.makeCall(SqlStdOperatorTable.AND,
856  builder.makeCall(
857  SqlStdOperatorTable.IS_NOT_NULL, call.getOperands().get(0)),
858  call.getOperands().get(0));
859  } else if (call.getKind() == SqlKind.IS_NOT_TRUE) {
860  return builder.makeCall(SqlStdOperatorTable.OR,
861  builder.makeCall(
862  SqlStdOperatorTable.IS_NULL, call.getOperands().get(0)),
863  builder.makeCall(SqlStdOperatorTable.NOT, call.getOperands().get(0)));
864  } else if (call.getKind() == SqlKind.IS_FALSE) {
865  return builder.makeCall(SqlStdOperatorTable.AND,
866  builder.makeCall(
867  SqlStdOperatorTable.IS_NOT_NULL, call.getOperands().get(0)),
868  builder.makeCall(SqlStdOperatorTable.NOT, call.getOperands().get(0)));
869  } else if (call.getKind() == SqlKind.IS_NOT_FALSE) {
870  return builder.makeCall(SqlStdOperatorTable.OR,
871  builder.makeCall(
872  SqlStdOperatorTable.IS_NULL, call.getOperands().get(0)),
873  call.getOperands().get(0));
874  }
875 
876  return call;
877  }
878  };
879 
880  RelNode node = root.rel.accept(new RelShuttleImpl() {
881  @Override
882  protected RelNode visitChild(RelNode parent, int i, RelNode child) {
883  RelNode node = super.visitChild(parent, i, child);
884  return node.accept(callShuttle);
885  }
886  });
887 
888  return new RelRoot(node,
889  root.validatedRowType,
890  root.kind,
891  root.fields,
892  root.collation,
893  Collections.emptyList());
894  }
895 
896  private SqlNode parseSql(String sql, final boolean legacy_syntax, Planner planner)
897  throws SqlParseException {
898  SqlNode parseR = null;
899  try {
900  parseR = planner.parse(sql);
901  MAPDLOGGER.debug(" node is \n" + parseR.toString());
902  } catch (SqlParseException ex) {
903  MAPDLOGGER.error("failed to parse SQL '" + sql + "' \n" + ex.toString());
904  throw ex;
905  }
906 
907  if (!legacy_syntax) {
908  return parseR;
909  }
910 
911  RelDataTypeFactory typeFactory = planner.getTypeFactory();
912  SqlSelect select_node = null;
913  if (parseR instanceof SqlSelect) {
914  select_node = (SqlSelect) parseR;
915  desugar(select_node, typeFactory);
916  } else if (parseR instanceof SqlOrderBy) {
917  SqlOrderBy order_by_node = (SqlOrderBy) parseR;
918  if (order_by_node.query instanceof SqlSelect) {
919  select_node = (SqlSelect) order_by_node.query;
920  SqlOrderBy new_order_by_node = desugar(select_node, order_by_node, typeFactory);
921  if (new_order_by_node != null) {
922  return new_order_by_node;
923  }
924  } else if (order_by_node.query instanceof SqlWith) {
925  SqlWith old_with_node = (SqlWith) order_by_node.query;
926  if (old_with_node.body instanceof SqlSelect) {
927  select_node = (SqlSelect) old_with_node.body;
928  desugar(select_node, typeFactory);
929  }
930  }
931  } else if (parseR instanceof SqlWith) {
932  SqlWith old_with_node = (SqlWith) parseR;
933  if (old_with_node.body instanceof SqlSelect) {
934  select_node = (SqlSelect) old_with_node.body;
935  desugar(select_node, typeFactory);
936  }
937  }
938  return parseR;
939  }
940 
941  private void desugar(SqlSelect select_node, RelDataTypeFactory typeFactory) {
942  desugar(select_node, null, typeFactory);
943  }
944 
945  private SqlNode expandCase(SqlCase old_case_node, RelDataTypeFactory typeFactory) {
946  SqlNodeList newWhenList =
947  new SqlNodeList(old_case_node.getWhenOperands().getParserPosition());
948  SqlNodeList newThenList =
949  new SqlNodeList(old_case_node.getThenOperands().getParserPosition());
950  java.util.Map<String, SqlNode> id_to_expr = new java.util.HashMap<String, SqlNode>();
951  for (SqlNode node : old_case_node.getWhenOperands()) {
952  SqlNode newCall = expand(node, id_to_expr, typeFactory);
953  if (null != newCall) {
954  newWhenList.add(newCall);
955  } else {
956  newWhenList.add(node);
957  }
958  }
959  for (SqlNode node : old_case_node.getThenOperands()) {
960  SqlNode newCall = expand(node, id_to_expr, typeFactory);
961  if (null != newCall) {
962  newThenList.add(newCall);
963  } else {
964  newThenList.add(node);
965  }
966  }
967  SqlNode new_else_operand = old_case_node.getElseOperand();
968  if (null != new_else_operand) {
969  SqlNode candidate_else_operand =
970  expand(old_case_node.getElseOperand(), id_to_expr, typeFactory);
971  if (null != candidate_else_operand) {
972  new_else_operand = candidate_else_operand;
973  }
974  }
975  SqlNode new_value_operand = old_case_node.getValueOperand();
976  if (null != new_value_operand) {
977  SqlNode candidate_value_operand =
978  expand(old_case_node.getValueOperand(), id_to_expr, typeFactory);
979  if (null != candidate_value_operand) {
980  new_value_operand = candidate_value_operand;
981  }
982  }
983  SqlNode newCaseNode = SqlCase.createSwitched(old_case_node.getParserPosition(),
984  new_value_operand,
985  newWhenList,
986  newThenList,
987  new_else_operand);
988  return newCaseNode;
989  }
990 
991  private SqlOrderBy desugar(SqlSelect select_node,
992  SqlOrderBy order_by_node,
993  RelDataTypeFactory typeFactory) {
994  MAPDLOGGER.debug("desugar: before: " + select_node.toString());
995  desugarExpression(select_node.getFrom(), typeFactory);
996  desugarExpression(select_node.getWhere(), typeFactory);
997  SqlNodeList select_list = select_node.getSelectList();
998  SqlNodeList new_select_list = new SqlNodeList(select_list.getParserPosition());
999  java.util.Map<String, SqlNode> id_to_expr = new java.util.HashMap<String, SqlNode>();
1000  for (SqlNode proj : select_list) {
1001  if (!(proj instanceof SqlBasicCall)) {
1002  if (proj instanceof SqlCase) {
1003  new_select_list.add(expandCase((SqlCase) proj, typeFactory));
1004  } else {
1005  new_select_list.add(proj);
1006  }
1007  } else {
1008  assert proj instanceof SqlBasicCall;
1009  SqlBasicCall proj_call = (SqlBasicCall) proj;
1010  if (proj_call.operands.length > 0) {
1011  for (int i = 0; i < proj_call.operands.length; i++) {
1012  if (proj_call.operand(i) instanceof SqlCase) {
1013  SqlNode new_op = expandCase(proj_call.operand(i), typeFactory);
1014  proj_call.setOperand(i, new_op);
1015  }
1016  }
1017  }
1018  new_select_list.add(expand(proj_call, id_to_expr, typeFactory));
1019  }
1020  }
1021  select_node.setSelectList(new_select_list);
1022  SqlNodeList group_by_list = select_node.getGroup();
1023  if (group_by_list != null) {
1024  select_node.setGroupBy(expand(group_by_list, id_to_expr, typeFactory));
1025  }
1026  SqlNode having = select_node.getHaving();
1027  if (having != null) {
1028  expand(having, id_to_expr, typeFactory);
1029  }
1030  SqlOrderBy new_order_by_node = null;
1031  if (order_by_node != null && order_by_node.orderList != null
1032  && order_by_node.orderList.size() > 0) {
1033  SqlNodeList new_order_by_list =
1034  expand(order_by_node.orderList, id_to_expr, typeFactory);
1035  new_order_by_node = new SqlOrderBy(order_by_node.getParserPosition(),
1036  select_node,
1037  new_order_by_list,
1038  order_by_node.offset,
1039  order_by_node.fetch);
1040  }
1041 
1042  MAPDLOGGER.debug("desugar: after: " + select_node.toString());
1043  return new_order_by_node;
1044  }
1045 
1046  private void desugarExpression(SqlNode node, RelDataTypeFactory typeFactory) {
1047  if (node instanceof SqlSelect) {
1048  desugar((SqlSelect) node, typeFactory);
1049  return;
1050  }
1051  if (!(node instanceof SqlBasicCall)) {
1052  return;
1053  }
1054  SqlBasicCall basic_call = (SqlBasicCall) node;
1055  for (SqlNode operator : basic_call.getOperands()) {
1056  if (operator instanceof SqlOrderBy) {
1057  desugarExpression(((SqlOrderBy) operator).query, typeFactory);
1058  } else {
1059  desugarExpression(operator, typeFactory);
1060  }
1061  }
1062  }
1063 
1064  private SqlNode expand(final SqlNode node,
1065  final java.util.Map<String, SqlNode> id_to_expr,
1066  RelDataTypeFactory typeFactory) {
1067  MAPDLOGGER.debug("expand: " + node.toString());
1068  if (node instanceof SqlBasicCall) {
1069  SqlBasicCall node_call = (SqlBasicCall) node;
1070  SqlNode[] operands = node_call.getOperands();
1071  for (int i = 0; i < operands.length; ++i) {
1072  node_call.setOperand(i, expand(operands[i], id_to_expr, typeFactory));
1073  }
1074  SqlNode expanded_variance = expandVariance(node_call, typeFactory);
1075  if (expanded_variance != null) {
1076  return expanded_variance;
1077  }
1078  SqlNode expanded_covariance = expandCovariance(node_call, typeFactory);
1079  if (expanded_covariance != null) {
1080  return expanded_covariance;
1081  }
1082  SqlNode expanded_correlation = expandCorrelation(node_call, typeFactory);
1083  if (expanded_correlation != null) {
1084  return expanded_correlation;
1085  }
1086  }
1087  if (node instanceof SqlSelect) {
1088  SqlSelect select_node = (SqlSelect) node;
1089  desugar(select_node, typeFactory);
1090  }
1091  return node;
1092  }
1093 
1094  private SqlNodeList expand(final SqlNodeList group_by_list,
1095  final java.util.Map<String, SqlNode> id_to_expr,
1096  RelDataTypeFactory typeFactory) {
1097  SqlNodeList new_group_by_list = new SqlNodeList(new SqlParserPos(-1, -1));
1098  for (SqlNode group_by : group_by_list) {
1099  if (!(group_by instanceof SqlIdentifier)) {
1100  new_group_by_list.add(expand(group_by, id_to_expr, typeFactory));
1101  continue;
1102  }
1103  SqlIdentifier group_by_id = ((SqlIdentifier) group_by);
1104  if (id_to_expr.containsKey(group_by_id.toString())) {
1105  new_group_by_list.add(id_to_expr.get(group_by_id.toString()));
1106  } else {
1107  new_group_by_list.add(group_by);
1108  }
1109  }
1110  return new_group_by_list;
1111  }
1112 
1113  private SqlNode expandVariance(
1114  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1115  // Expand variance aggregates that are not supported natively
1116  if (proj_call.operandCount() != 1) {
1117  return null;
1118  }
1119  boolean biased;
1120  boolean sqrt;
1121  boolean flt;
1122  if (proj_call.getOperator().isName("STDDEV_POP", false)) {
1123  biased = true;
1124  sqrt = true;
1125  flt = false;
1126  } else if (proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_POP_FLOAT")) {
1127  biased = true;
1128  sqrt = true;
1129  flt = true;
1130  } else if (proj_call.getOperator().isName("STDDEV_SAMP", false)
1131  || proj_call.getOperator().getName().equalsIgnoreCase("STDDEV")) {
1132  biased = false;
1133  sqrt = true;
1134  flt = false;
1135  } else if (proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_SAMP_FLOAT")
1136  || proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_FLOAT")) {
1137  biased = false;
1138  sqrt = true;
1139  flt = true;
1140  } else if (proj_call.getOperator().isName("VAR_POP", false)) {
1141  biased = true;
1142  sqrt = false;
1143  flt = false;
1144  } else if (proj_call.getOperator().getName().equalsIgnoreCase("VAR_POP_FLOAT")) {
1145  biased = true;
1146  sqrt = false;
1147  flt = true;
1148  } else if (proj_call.getOperator().isName("VAR_SAMP", false)
1149  || proj_call.getOperator().getName().equalsIgnoreCase("VARIANCE")) {
1150  biased = false;
1151  sqrt = false;
1152  flt = false;
1153  } else if (proj_call.getOperator().getName().equalsIgnoreCase("VAR_SAMP_FLOAT")
1154  || proj_call.getOperator().getName().equalsIgnoreCase("VARIANCE_FLOAT")) {
1155  biased = false;
1156  sqrt = false;
1157  flt = true;
1158  } else {
1159  return null;
1160  }
1161  final SqlNode operand = proj_call.operand(0);
1162  final SqlParserPos pos = proj_call.getParserPosition();
1163  SqlNode expanded_proj_call =
1164  expandVariance(pos, operand, biased, sqrt, flt, typeFactory);
1165  MAPDLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1166  MAPDLOGGER.debug("to : " + expanded_proj_call.toString());
1167  return expanded_proj_call;
1168  }
1169 
1170  private SqlNode expandVariance(final SqlParserPos pos,
1171  final SqlNode operand,
1172  boolean biased,
1173  boolean sqrt,
1174  boolean flt,
1175  RelDataTypeFactory typeFactory) {
1176  // stddev_pop(x) ==>
1177  // power(
1178  // (sum(x * x) - sum(x) * sum(x) / (case count(x) when 0 then NULL else count(x)
1179  // end)) / (case count(x) when 0 then NULL else count(x) end), .5)
1180  //
1181  // stddev_samp(x) ==>
1182  // power(
1183  // (sum(x * x) - sum(x) * sum(x) / (case count(x) when 0 then NULL else count(x)
1184  // )) / ((case count(x) when 1 then NULL else count(x) - 1 end)), .5)
1185  //
1186  // var_pop(x) ==>
1187  // (sum(x * x) - sum(x) * sum(x) / ((case count(x) when 0 then NULL else
1188  // count(x)
1189  // end))) / ((case count(x) when 0 then NULL else count(x) end))
1190  //
1191  // var_samp(x) ==>
1192  // (sum(x * x) - sum(x) * sum(x) / ((case count(x) when 0 then NULL else
1193  // count(x)
1194  // end))) / ((case count(x) when 1 then NULL else count(x) - 1 end))
1195  //
1196  final SqlNode arg = SqlStdOperatorTable.CAST.createCall(pos,
1197  operand,
1198  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1199  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1200  final SqlNode argSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, arg, arg);
1201  final SqlNode sumArgSquared = SqlStdOperatorTable.SUM.createCall(pos, argSquared);
1202  final SqlNode sum = SqlStdOperatorTable.SUM.createCall(pos, arg);
1203  final SqlNode sumSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, sum, sum);
1204  final SqlNode count = SqlStdOperatorTable.COUNT.createCall(pos, arg);
1205  final SqlLiteral nul = SqlLiteral.createNull(pos);
1206  final SqlNumericLiteral zero = SqlLiteral.createExactNumeric("0", pos);
1207  final SqlNode countEqZero = SqlStdOperatorTable.EQUALS.createCall(pos, count, zero);
1208  SqlNodeList whenList = new SqlNodeList(pos);
1209  SqlNodeList thenList = new SqlNodeList(pos);
1210  whenList.add(countEqZero);
1211  thenList.add(nul);
1212  final SqlNode int_denominator = SqlStdOperatorTable.CASE.createCall(
1213  null, pos, null, whenList, thenList, count);
1214  final SqlNode denominator = SqlStdOperatorTable.CAST.createCall(pos,
1215  int_denominator,
1216  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1217  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1218  final SqlNode avgSumSquared =
1219  SqlStdOperatorTable.DIVIDE.createCall(pos, sumSquared, denominator);
1220  final SqlNode diff =
1221  SqlStdOperatorTable.MINUS.createCall(pos, sumArgSquared, avgSumSquared);
1222  final SqlNode denominator1;
1223  if (biased) {
1224  denominator1 = denominator;
1225  } else {
1226  final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
1227  final SqlNode countEqOne = SqlStdOperatorTable.EQUALS.createCall(pos, count, one);
1228  final SqlNode countMinusOne = SqlStdOperatorTable.MINUS.createCall(pos, count, one);
1229  SqlNodeList whenList1 = new SqlNodeList(pos);
1230  SqlNodeList thenList1 = new SqlNodeList(pos);
1231  whenList1.add(countEqOne);
1232  thenList1.add(nul);
1233  final SqlNode int_denominator1 = SqlStdOperatorTable.CASE.createCall(
1234  null, pos, null, whenList1, thenList1, countMinusOne);
1235  denominator1 = SqlStdOperatorTable.CAST.createCall(pos,
1236  int_denominator1,
1237  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1238  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1239  }
1240  final SqlNode div = SqlStdOperatorTable.DIVIDE.createCall(pos, diff, denominator1);
1241  SqlNode result = div;
1242  if (sqrt) {
1243  final SqlNumericLiteral half = SqlLiteral.createExactNumeric("0.5", pos);
1244  result = SqlStdOperatorTable.POWER.createCall(pos, div, half);
1245  }
1246  return SqlStdOperatorTable.CAST.createCall(pos,
1247  result,
1248  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1249  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1250  }
1251 
1252  private SqlNode expandCovariance(
1253  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1254  // Expand covariance aggregates
1255  if (proj_call.operandCount() != 2) {
1256  return null;
1257  }
1258  boolean pop;
1259  boolean flt;
1260  if (proj_call.getOperator().isName("COVAR_POP", false)) {
1261  pop = true;
1262  flt = false;
1263  } else if (proj_call.getOperator().isName("COVAR_SAMP", false)) {
1264  pop = false;
1265  flt = false;
1266  } else if (proj_call.getOperator().getName().equalsIgnoreCase("COVAR_POP_FLOAT")) {
1267  pop = true;
1268  flt = true;
1269  } else if (proj_call.getOperator().getName().equalsIgnoreCase("COVAR_SAMP_FLOAT")) {
1270  pop = false;
1271  flt = true;
1272  } else {
1273  return null;
1274  }
1275  final SqlNode operand0 = proj_call.operand(0);
1276  final SqlNode operand1 = proj_call.operand(1);
1277  final SqlParserPos pos = proj_call.getParserPosition();
1278  SqlNode expanded_proj_call =
1279  expandCovariance(pos, operand0, operand1, pop, flt, typeFactory);
1280  MAPDLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1281  MAPDLOGGER.debug("to : " + expanded_proj_call.toString());
1282  return expanded_proj_call;
1283  }
1284 
1285  private SqlNode expandCovariance(SqlParserPos pos,
1286  final SqlNode operand0,
1287  final SqlNode operand1,
1288  boolean pop,
1289  boolean flt,
1290  RelDataTypeFactory typeFactory) {
1291  // covar_pop(x, y) ==> avg(x * y) - avg(x) * avg(y)
1292  // covar_samp(x, y) ==> (sum(x * y) - sum(x) * avg(y))
1293  // ((case count(x) when 1 then NULL else count(x) - 1 end))
1294  final SqlNode arg0 = SqlStdOperatorTable.CAST.createCall(operand0.getParserPosition(),
1295  operand0,
1296  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1297  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1298  final SqlNode arg1 = SqlStdOperatorTable.CAST.createCall(operand1.getParserPosition(),
1299  operand1,
1300  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1301  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1302  final SqlNode mulArg = SqlStdOperatorTable.MULTIPLY.createCall(pos, arg0, arg1);
1303  final SqlNode avgArg1 = SqlStdOperatorTable.AVG.createCall(pos, arg1);
1304  if (pop) {
1305  final SqlNode avgMulArg = SqlStdOperatorTable.AVG.createCall(pos, mulArg);
1306  final SqlNode avgArg0 = SqlStdOperatorTable.AVG.createCall(pos, arg0);
1307  final SqlNode mulAvgAvg =
1308  SqlStdOperatorTable.MULTIPLY.createCall(pos, avgArg0, avgArg1);
1309  final SqlNode covarPop =
1310  SqlStdOperatorTable.MINUS.createCall(pos, avgMulArg, mulAvgAvg);
1311  return SqlStdOperatorTable.CAST.createCall(pos,
1312  covarPop,
1313  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1314  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1315  }
1316  final SqlNode sumMulArg = SqlStdOperatorTable.SUM.createCall(pos, mulArg);
1317  final SqlNode sumArg0 = SqlStdOperatorTable.SUM.createCall(pos, arg0);
1318  final SqlNode mulSumAvg =
1319  SqlStdOperatorTable.MULTIPLY.createCall(pos, sumArg0, avgArg1);
1320  final SqlNode sub = SqlStdOperatorTable.MINUS.createCall(pos, sumMulArg, mulSumAvg);
1321  final SqlNode count = SqlStdOperatorTable.COUNT.createCall(pos, operand0);
1322  final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
1323  final SqlNode countEqOne = SqlStdOperatorTable.EQUALS.createCall(pos, count, one);
1324  final SqlNode countMinusOne = SqlStdOperatorTable.MINUS.createCall(pos, count, one);
1325  final SqlLiteral nul = SqlLiteral.createNull(pos);
1326  SqlNodeList whenList1 = new SqlNodeList(pos);
1327  SqlNodeList thenList1 = new SqlNodeList(pos);
1328  whenList1.add(countEqOne);
1329  thenList1.add(nul);
1330  final SqlNode int_denominator = SqlStdOperatorTable.CASE.createCall(
1331  null, pos, null, whenList1, thenList1, countMinusOne);
1332  final SqlNode denominator = SqlStdOperatorTable.CAST.createCall(pos,
1333  int_denominator,
1334  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1335  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1336  final SqlNode covarSamp =
1337  SqlStdOperatorTable.DIVIDE.createCall(pos, sub, denominator);
1338  return SqlStdOperatorTable.CAST.createCall(pos,
1339  covarSamp,
1340  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1341  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1342  }
1343 
1344  private SqlNode expandCorrelation(
1345  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1346  // Expand correlation coefficient
1347  if (proj_call.operandCount() != 2) {
1348  return null;
1349  }
1350  boolean flt;
1351  if (proj_call.getOperator().isName("CORR", false)
1352  || proj_call.getOperator().getName().equalsIgnoreCase("CORRELATION")) {
1353  // expand correlation coefficient
1354  flt = false;
1355  } else if (proj_call.getOperator().getName().equalsIgnoreCase("CORR_FLOAT")
1356  || proj_call.getOperator().getName().equalsIgnoreCase("CORRELATION_FLOAT")) {
1357  // expand correlation coefficient
1358  flt = true;
1359  } else {
1360  return null;
1361  }
1362  // corr(x, y) ==> (avg(x * y) - avg(x) * avg(y)) / (stddev_pop(x) *
1363  // stddev_pop(y))
1364  // ==> covar_pop(x, y) / (stddev_pop(x) * stddev_pop(y))
1365  final SqlNode operand0 = proj_call.operand(0);
1366  final SqlNode operand1 = proj_call.operand(1);
1367  final SqlParserPos pos = proj_call.getParserPosition();
1368  SqlNode covariance =
1369  expandCovariance(pos, operand0, operand1, true, flt, typeFactory);
1370  SqlNode stddev0 = expandVariance(pos, operand0, true, true, flt, typeFactory);
1371  SqlNode stddev1 = expandVariance(pos, operand1, true, true, flt, typeFactory);
1372  final SqlNode mulStddev =
1373  SqlStdOperatorTable.MULTIPLY.createCall(pos, stddev0, stddev1);
1374  final SqlNumericLiteral zero = SqlLiteral.createExactNumeric("0.0", pos);
1375  final SqlNode mulStddevEqZero =
1376  SqlStdOperatorTable.EQUALS.createCall(pos, mulStddev, zero);
1377  final SqlLiteral nul = SqlLiteral.createNull(pos);
1378  SqlNodeList whenList1 = new SqlNodeList(pos);
1379  SqlNodeList thenList1 = new SqlNodeList(pos);
1380  whenList1.add(mulStddevEqZero);
1381  thenList1.add(nul);
1382  final SqlNode denominator = SqlStdOperatorTable.CASE.createCall(
1383  null, pos, null, whenList1, thenList1, mulStddev);
1384  final SqlNode expanded_proj_call =
1385  SqlStdOperatorTable.DIVIDE.createCall(pos, covariance, denominator);
1386  MAPDLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1387  MAPDLOGGER.debug("to : " + expanded_proj_call.toString());
1388  return expanded_proj_call;
1389  }
1390 
1391  public SqlIdentifierCapturer captureIdentifiers(String sql, boolean legacy_syntax)
1392  throws SqlParseException {
1393  try {
1394  Planner planner = getPlanner();
1395  SqlNode node = parseSql(sql, legacy_syntax, planner);
1396  return captureIdentifiers(node);
1397  } catch (Exception | Error e) {
1398  MAPDLOGGER.error("Error parsing sql: " + sql, e);
1399  return new SqlIdentifierCapturer();
1400  }
1401  }
1402 
1403  public SqlIdentifierCapturer captureIdentifiers(SqlNode node) throws SqlParseException {
1404  try {
1406  capturer.scan(node);
1407  return capturer;
1408  } catch (Exception | Error e) {
1409  MAPDLOGGER.error("Error parsing sql: " + node, e);
1410  return new SqlIdentifierCapturer();
1411  }
1412  }
1413 
1414  public int getCallCount() {
1415  return callCount;
1416  }
1417 
1418  public void updateMetaData(String schema, String table) {
1419  MAPDLOGGER.debug("schema :" + schema + " table :" + table);
1420  MapDSchema mapd =
1422  mapd.updateMetaData(schema, table);
1423  }
1424 
1425  protected RelDataTypeSystem createTypeSystem() {
1426  final MapDTypeSystem typeSystem = new MapDTypeSystem();
1427  return typeSystem;
1428  }
1429 }
SqlNode expand(final SqlNode node, final java.util.Map< String, SqlNode > id_to_expr, RelDataTypeFactory typeFactory)
static final EnumSet< SqlKind > IN
JoinType
Definition: sqldefs.h:108
static final EnumSet< SqlKind > SCALAR
RelRoot replaceIsTrue(final RelDataTypeFactory typeFactory, RelRoot root)
void desugarExpression(SqlNode node, RelDataTypeFactory typeFactory)
SqlNode expandCovariance(final SqlBasicCall proj_call, RelDataTypeFactory typeFactory)
SqlNode expandCorrelation(final SqlBasicCall proj_call, RelDataTypeFactory typeFactory)
RelRoot queryToRelNode(final String sql, final MapDParserOptions parserOptions)
static String toString(final RelNode rel)
Pair< String, SqlIdentifierCapturer > process(String sql, final MapDParserOptions parserOptions)
std::string join(T const &container, std::string const &delim)
SqlNode expandCase(SqlCase old_case_node, RelDataTypeFactory typeFactory)
static final EnumSet< SqlKind > ARRAY_VALUE
void updateMetaData(String schema, String table)
Definition: MapDSchema.java:95
final Supplier< MapDSqlOperatorTable > mapDSqlOperatorTable
SqlNode expandVariance(final SqlParserPos pos, final SqlNode operand, boolean biased, boolean sqrt, boolean flt, RelDataTypeFactory typeFactory)
name
Definition: setup.py:35
SqlIdentifierCapturer getAccessedObjects()
Definition: MapDView.java:66
void updateMetaData(String schema, String table)
MapDParser(String dataDir, final Supplier< MapDSqlOperatorTable > mapDSqlOperatorTable, int mapdPort, SockTransportProperties skT)
String processSql(String sql, final MapDParserOptions parserOptions)
SqlIdentifierCapturer captureIdentifiers(SqlNode node)
SqlSelect rewriteSimpleUpdateAsSelect(final SqlUpdate update)
SockTransportProperties sock_transport_properties
static final SqlArrayValueConstructorAllowingEmpty ARRAY_VALUE_CONSTRUCTOR
static final EnumSet< SqlKind > EXISTS
RelRoot optimizeRaQuery(String query, MapDSchema schema)
SqlNode parseSql(String sql, final boolean legacy_syntax, Planner planner)
static final EnumSet< SqlKind > UPDATE
SqlIdentifierCapturer captureIdentifiers(String sql, boolean legacy_syntax)
static Map< String, Boolean > SubqueryCorrMemo
Set< String > resolveSelectIdentifiers(SqlIdentifierCapturer capturer)
RelRoot convertSqlToRelNode(final SqlNode sqlNode, final MapDPlanner mapDPlanner, final MapDParserOptions parserOptions)
String getTableName(SqlNode node)
boolean isCorrelated(SqlNode expression)
Table getTable(String string)
Definition: MapDSchema.java:51
String processSql(final SqlNode sqlNode, final MapDParserOptions parserOptions)
void setUser(MapDUser mapdUser)
SqlNodeList expand(final SqlNodeList group_by_list, final java.util.Map< String, SqlNode > id_to_expr, RelDataTypeFactory typeFactory)
SqlNode expandCovariance(SqlParserPos pos, final SqlNode operand0, final SqlNode operand1, boolean pop, boolean flt, RelDataTypeFactory typeFactory)
void desugar(SqlSelect select_node, RelDataTypeFactory typeFactory)
CompletionResult getCompletionHints(final String sql, final int cursor, final List< String > visibleTables)
String optimizeRAQuery(String query)
SqlOrderBy desugar(SqlSelect select_node, SqlOrderBy order_by_node, RelDataTypeFactory typeFactory)
static final ThreadLocal< MapDParser > CURRENT_PARSER
SqlNode expandVariance(final SqlBasicCall proj_call, RelDataTypeFactory typeFactory)
MapDPlanner.CompletionResult getCompletionHints(String sql, int cursor, List< String > visible_tables)
static final EnumSet< SqlKind > DELETE
MapDPlanner getPlanner(final boolean allowSubQueryExpansion, final boolean allowPushdownJoinCondition)
static final Context MAPD_CONNECTION_CONTEXT
RelDataTypeSystem createTypeSystem()
LogicalTableModify getDummyUpdate(SqlUpdate update)
RelRoot rewriteUpdateAsSelect(SqlUpdate update, MapDParserOptions parserOptions)