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