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