OmniSciDB  fe05a0c208
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros 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 org.apache.calcite.avatica.util.Casing;
30 import org.apache.calcite.config.CalciteConnectionConfig;
31 import org.apache.calcite.config.CalciteConnectionConfigImpl;
32 import org.apache.calcite.config.CalciteConnectionProperty;
33 import org.apache.calcite.linq4j.function.Functions;
34 import org.apache.calcite.plan.*;
35 import org.apache.calcite.plan.hep.HepPlanner;
36 import org.apache.calcite.plan.hep.HepProgram;
37 import org.apache.calcite.plan.hep.HepProgramBuilder;
38 import org.apache.calcite.plan.volcano.VolcanoPlanner;
39 import org.apache.calcite.prepare.CalciteCatalogReader;
42 import org.apache.calcite.rel.RelNode;
43 import org.apache.calcite.rel.RelRoot;
44 import org.apache.calcite.rel.RelShuttleImpl;
45 import org.apache.calcite.rel.core.Join;
46 import org.apache.calcite.rel.core.RelFactories;
47 import org.apache.calcite.rel.core.TableModify;
48 import org.apache.calcite.rel.core.TableModify.Operation;
50 import org.apache.calcite.rel.hint.HintStrategyTable;
51 import org.apache.calcite.rel.logical.LogicalProject;
52 import org.apache.calcite.rel.logical.LogicalTableModify;
53 import org.apache.calcite.rel.metadata.DefaultRelMetadataProvider;
54 import org.apache.calcite.rel.rules.CoreRules;
55 import org.apache.calcite.rel.rules.FilterMergeRule;
56 import org.apache.calcite.rel.rules.FilterProjectTransposeRule;
57 import org.apache.calcite.rel.rules.JoinProjectTransposeRule;
58 import org.apache.calcite.rel.rules.ProjectMergeRule;
59 import org.apache.calcite.rel.rules.ProjectRemoveRule;
60 import org.apache.calcite.rel.type.RelDataType;
61 import org.apache.calcite.rel.type.RelDataTypeFactory;
62 import org.apache.calcite.rel.type.RelDataTypeField;
63 import org.apache.calcite.rel.type.RelDataTypeSystem;
64 import org.apache.calcite.rex.RexBuilder;
65 import org.apache.calcite.rex.RexCall;
66 import org.apache.calcite.rex.RexInputRef;
67 import org.apache.calcite.rex.RexNode;
68 import org.apache.calcite.rex.RexShuttle;
69 import org.apache.calcite.runtime.CalciteException;
70 import org.apache.calcite.schema.SchemaPlus;
71 import org.apache.calcite.sql.JoinConditionType;
73 import org.apache.calcite.sql.SqlBasicCall;
74 import org.apache.calcite.sql.SqlBasicTypeNameSpec;
75 import org.apache.calcite.sql.SqlCall;
76 import org.apache.calcite.sql.SqlDataTypeSpec;
77 import org.apache.calcite.sql.SqlDdl;
78 import org.apache.calcite.sql.SqlDelete;
79 import org.apache.calcite.sql.SqlDynamicParam;
80 import org.apache.calcite.sql.SqlFunctionCategory;
81 import org.apache.calcite.sql.SqlIdentifier;
82 import org.apache.calcite.sql.SqlIntervalQualifier;
83 import org.apache.calcite.sql.SqlJoin;
84 import org.apache.calcite.sql.SqlKind;
85 import org.apache.calcite.sql.SqlLiteral;
86 import org.apache.calcite.sql.SqlNode;
87 import org.apache.calcite.sql.SqlNodeList;
88 import org.apache.calcite.sql.SqlNumericLiteral;
89 import org.apache.calcite.sql.SqlOrderBy;
90 import org.apache.calcite.sql.SqlSelect;
91 import org.apache.calcite.sql.SqlUnresolvedFunction;
92 import org.apache.calcite.sql.SqlUpdate;
93 import org.apache.calcite.sql.SqlWith;
94 import org.apache.calcite.sql.dialect.CalciteSqlDialect;
95 import org.apache.calcite.sql.fun.SqlCase;
96 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
97 import org.apache.calcite.sql.parser.SqlParseException;
98 import org.apache.calcite.sql.parser.SqlParser;
99 import org.apache.calcite.sql.parser.SqlParserPos;
100 import org.apache.calcite.sql.type.SqlTypeName;
101 import org.apache.calcite.sql.type.SqlTypeUtil;
102 import org.apache.calcite.sql.util.SqlBasicVisitor;
103 import org.apache.calcite.sql.util.SqlShuttle;
104 import org.apache.calcite.sql.util.SqlVisitor;
105 import org.apache.calcite.sql.validate.SqlConformanceEnum;
107 import org.apache.calcite.tools.FrameworkConfig;
108 import org.apache.calcite.tools.Frameworks;
109 import org.apache.calcite.tools.Planner;
110 import org.apache.calcite.tools.Program;
111 import org.apache.calcite.tools.Programs;
112 import org.apache.calcite.tools.RelConversionException;
113 import org.apache.calcite.tools.ValidationException;
114 import org.apache.calcite.util.Pair;
115 import org.apache.calcite.util.Util;
116 import org.slf4j.Logger;
117 import org.slf4j.LoggerFactory;
118 
119 import java.io.IOException;
120 import java.lang.reflect.Field;
121 import java.util.ArrayList;
122 import java.util.Arrays;
123 import java.util.Collections;
124 import java.util.EnumSet;
125 import java.util.HashSet;
126 import java.util.List;
127 import java.util.Map;
128 import java.util.Properties;
129 import java.util.Set;
130 import java.util.concurrent.ConcurrentHashMap;
131 import java.util.function.BiPredicate;
132 import java.util.function.Supplier;
133 
138 public final class MapDParser {
139  public static final ThreadLocal<MapDParser> CURRENT_PARSER = new ThreadLocal<>();
140  private static final EnumSet<SqlKind> SCALAR =
141  EnumSet.of(SqlKind.SCALAR_QUERY, SqlKind.SELECT);
142  private static final EnumSet<SqlKind> EXISTS = EnumSet.of(SqlKind.EXISTS);
143  private static final EnumSet<SqlKind> DELETE = EnumSet.of(SqlKind.DELETE);
144  private static final EnumSet<SqlKind> UPDATE = EnumSet.of(SqlKind.UPDATE);
145  private static final EnumSet<SqlKind> IN = EnumSet.of(SqlKind.IN);
146  private static final EnumSet<SqlKind> ARRAY_VALUE =
147  EnumSet.of(SqlKind.ARRAY_VALUE_CONSTRUCTOR);
148 
149  final static Logger MAPDLOGGER = LoggerFactory.getLogger(MapDParser.class);
150 
151  private final Supplier<MapDSqlOperatorTable> mapDSqlOperatorTable;
152  private final String dataDir;
153 
154  private int callCount = 0;
155  private final int mapdPort;
157  SqlNode sqlNode_;
159 
160  private static Map<String, Boolean> SubqueryCorrMemo = new ConcurrentHashMap<>();
161 
162  public MapDParser(String dataDir,
163  final Supplier<MapDSqlOperatorTable> mapDSqlOperatorTable,
164  int mapdPort,
166  this.dataDir = dataDir;
167  this.mapDSqlOperatorTable = mapDSqlOperatorTable;
168  this.mapdPort = mapdPort;
169  this.sock_transport_properties = skT;
170  }
171 
172  public void clearMemo() {
173  SubqueryCorrMemo.clear();
174  }
175 
176  private static final Context MAPD_CONNECTION_CONTEXT = new Context() {
177  MapDTypeSystem myTypeSystem = new MapDTypeSystem();
178  CalciteConnectionConfig config = new CalciteConnectionConfigImpl(new Properties()) {
179  {
180  properties.put(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
181  String.valueOf(false));
182  properties.put(CalciteConnectionProperty.CONFORMANCE.camelName(),
183  String.valueOf(SqlConformanceEnum.LENIENT));
184  }
185 
186  @SuppressWarnings("unchecked")
187  public <T extends Object> T typeSystem(
188  java.lang.Class<T> typeSystemClass, T defaultTypeSystem) {
189  return (T) myTypeSystem;
190  };
191 
192  public boolean caseSensitive() {
193  return false;
194  };
195 
196  public org.apache.calcite.sql.validate.SqlConformance conformance() {
197  return SqlConformanceEnum.LENIENT;
198  };
199  };
200 
201  @Override
202  public <C> C unwrap(Class<C> aClass) {
203  if (aClass.isInstance(config)) {
204  return aClass.cast(config);
205  }
206  return null;
207  }
208  };
209 
211  return getPlanner(true);
212  }
213 
214  private boolean isCorrelated(SqlNode expression) {
215  String queryString = expression.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
216  Boolean isCorrelatedSubquery = SubqueryCorrMemo.get(queryString);
217  if (null != isCorrelatedSubquery) {
218  return isCorrelatedSubquery;
219  }
220 
221  try {
224  MapDParserOptions options = new MapDParserOptions();
225  parser.setUser(mapdUser);
226  parser.processSql(expression, options);
227  } catch (Exception e) {
228  // if we are not able to parse, then assume correlated
229  SubqueryCorrMemo.put(queryString, true);
230  return true;
231  }
232  SubqueryCorrMemo.put(queryString, false);
233  return false;
234  }
235 
236  private MapDPlanner getPlanner(final boolean allowSubQueryExpansion) {
237  BiPredicate<SqlNode, SqlNode> expandPredicate = new BiPredicate<SqlNode, SqlNode>() {
238  @Override
239  public boolean test(SqlNode root, SqlNode expression) {
240  if (!allowSubQueryExpansion) {
241  return false;
242  }
243 
244  // special handling of sub-queries
245  if (expression.isA(SCALAR) || expression.isA(EXISTS) || expression.isA(IN)) {
246  // only expand if it is correlated.
247 
248  if (expression.isA(EXISTS)) {
249  // always expand subquery by EXISTS clause
250  return true;
251  }
252 
253  if (expression.isA(IN)) {
254  // expand subquery by IN clause
255  // but correlated subquery by NOT_IN clause is not available
256  // currently due to a lack of supporting in Calcite
257  boolean found_expression = false;
258  if (expression instanceof SqlCall) {
259  SqlCall call = (SqlCall) expression;
260  if (call.getOperandList().size() == 2) {
261  // if IN clause is correlated, its second operand of corresponding
262  // expression is SELECT clause which indicates a correlated subquery.
263  // Here, an expression "f.val IN (SELECT ...)" has two operands.
264  // Since we have interest in its subquery, so try to check whether
265  // the second operand, i.e., call.getOperandList().get(1)
266  // is a type of SqlSelect and also is correlated.
267  // Note that the second operand of non-correlated IN clause
268  // does not have SqlSelect as its second operand
269  if (call.getOperandList().get(1) instanceof SqlSelect) {
270  expression = call.getOperandList().get(1);
271  SqlSelect select_call = (SqlSelect) expression;
272  if (select_call.hasWhere()) {
273  found_expression = true;
274  }
275  }
276  }
277  }
278  if (!found_expression) {
279  return false;
280  }
281  }
282 
283  if (isCorrelated(expression)) {
284  SqlSelect select = null;
285  if (expression instanceof SqlCall) {
286  SqlCall call = (SqlCall) expression;
287  if (call.getOperator().equals(SqlStdOperatorTable.SCALAR_QUERY)) {
288  expression = call.getOperandList().get(0);
289  }
290  }
291 
292  if (expression instanceof SqlSelect) {
293  select = (SqlSelect) expression;
294  }
295 
296  if (null != select) {
297  if (null != select.getFetch() || null != select.getOffset()
298  || (null != select.getOrderList()
299  && select.getOrderList().size() != 0)) {
300  throw new CalciteException(
301  "Correlated sub-queries with ordering not supported.", null);
302  }
303  }
304  return true;
305  }
306  }
307 
308  // per default we do not want to expand
309  return false;
310  }
311  };
312 
313  // create the default schema
314  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
315  final MapDSchema defaultSchema =
317  final SchemaPlus defaultSchemaPlus = rootSchema.add(mapdUser.getDB(), defaultSchema);
318 
319  // add the other potential schemas
320  // this is where the systyem schema would be added
321  final MetaConnect mc =
323 
324  // TODO MAT for this checkin we are not going to actually allow any additional schemas
325  // Eveything should work and perform as it ever did
326  if (false) {
327  for (String db : mc.getDatabases()) {
328  if (!db.toUpperCase().equals(mapdUser.getDB().toUpperCase())) {
329  rootSchema.add(db,
330  new MapDSchema(dataDir,
331  this,
332  mapdPort,
333  mapdUser,
335  db));
336  }
337  }
338  }
339 
340  final FrameworkConfig config =
341  Frameworks.newConfigBuilder()
342  .defaultSchema(defaultSchemaPlus)
343  .operatorTable(mapDSqlOperatorTable.get())
344  .parserConfig(SqlParser.configBuilder()
345  .setConformance(SqlConformanceEnum.LENIENT)
346  .setUnquotedCasing(Casing.UNCHANGED)
347  .setCaseSensitive(false)
348  // allow identifiers of up to 512 chars
349  .setIdentifierMaxLength(512)
350  .setParserFactory(ExtendedSqlParser.FACTORY)
351  .build())
352  .sqlToRelConverterConfig(
353  SqlToRelConverter
354  .configBuilder()
355  // enable sub-query expansion (de-correlation)
356  .withExpandPredicate(expandPredicate)
357  // allow as many as possible IN operator values
358  .withInSubQueryThreshold(Integer.MAX_VALUE)
359  .withHintStrategyTable(
361  .build())
362  .typeSystem(createTypeSystem())
363  .context(MAPD_CONNECTION_CONTEXT)
364  .build();
365  MapDPlanner planner = new MapDPlanner(config);
366  planner.setRestriction(mapdUser.getRestriction());
367  return planner;
368  }
369 
370  public void setUser(MapDUser mapdUser) {
371  this.mapdUser = mapdUser;
372  }
373 
374  public Pair<String, SqlIdentifierCapturer> process(
375  String sql, final MapDParserOptions parserOptions)
376  throws SqlParseException, ValidationException, RelConversionException {
377  final MapDPlanner planner = getPlanner(true);
378  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
379  String res = processSql(sqlNode, parserOptions);
380  SqlIdentifierCapturer capture = captureIdentifiers(sqlNode);
381  return new Pair<String, SqlIdentifierCapturer>(res, capture);
382  }
383 
384  public String optimizeRAQuery(String query, final MapDParserOptions parserOptions)
385  throws IOException {
386  MapDSchema schema =
388  MapDPlanner planner = getPlanner(true);
389 
390  planner.setFilterPushDownInfo(parserOptions.getFilterPushDownInfo());
391  RelRoot optRel = planner.optimizeRaQuery(query, schema);
392  optRel = replaceIsTrue(planner.getTypeFactory(), optRel);
393  return MapDSerializer.toString(optRel.project());
394  }
395 
396  public String processSql(String sql, final MapDParserOptions parserOptions)
397  throws SqlParseException, ValidationException, RelConversionException {
398  callCount++;
399 
400  final MapDPlanner planner = getPlanner(true);
401  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
402 
403  return processSql(sqlNode, parserOptions);
404  }
405 
406  public String processSql(final SqlNode sqlNode, final MapDParserOptions parserOptions)
407  throws SqlParseException, ValidationException, RelConversionException {
408  callCount++;
409 
410  if (sqlNode instanceof JsonSerializableDdl) {
411  return ((JsonSerializableDdl) sqlNode).toJsonString();
412  }
413 
414  if (sqlNode instanceof SqlDdl) {
415  return sqlNode.toString();
416  }
417 
418  final MapDPlanner planner = getPlanner(true);
419  planner.advanceToValidate();
420 
421  final RelRoot sqlRel = convertSqlToRelNode(sqlNode, planner, parserOptions);
422  RelNode project = sqlRel.project();
423 
424  if (parserOptions.isExplain()) {
425  return RelOptUtil.toString(sqlRel.project());
426  }
427 
428  String res = MapDSerializer.toString(project);
429 
430  return res;
431  }
432 
433  public MapDPlanner.CompletionResult getCompletionHints(
434  String sql, int cursor, List<String> visible_tables) {
435  return getPlanner().getCompletionHints(sql, cursor, visible_tables);
436  }
437 
438  public HashSet<ImmutableList<String>> resolveSelectIdentifiers(
439  SqlIdentifierCapturer capturer) {
440  MapDSchema schema =
442  HashSet<ImmutableList<String>> resolved = new HashSet<ImmutableList<String>>();
443 
444  for (ImmutableList<String> names : capturer.selects) {
445  MapDTable table = (MapDTable) schema.getTable(names.get(0));
446  if (null == table) {
447  throw new RuntimeException("table/view not found: " + names.get(0));
448  }
449 
450  if (table instanceof MapDView) {
451  MapDView view = (MapDView) table;
452  resolved.addAll(resolveSelectIdentifiers(view.getAccessedObjects()));
453  } else {
454  resolved.add(names);
455  }
456  }
457 
458  return resolved;
459  }
460 
461  private String getTableName(SqlNode node) {
462  if (node.isA(EnumSet.of(SqlKind.AS))) {
463  node = ((SqlCall) node).getOperandList().get(1);
464  }
465  if (node instanceof SqlIdentifier) {
466  SqlIdentifier id = (SqlIdentifier) node;
467  return id.names.get(id.names.size() - 1);
468  }
469  return null;
470  }
471 
472  private SqlSelect rewriteSimpleUpdateAsSelect(final SqlUpdate update) {
473  SqlNode where = update.getCondition();
474 
475  if (update.getSourceExpressionList().size() != 1) {
476  return null;
477  }
478 
479  if (!(update.getSourceExpressionList().get(0) instanceof SqlSelect)) {
480  return null;
481  }
482 
483  final SqlSelect inner = (SqlSelect) update.getSourceExpressionList().get(0);
484 
485  if (null != inner.getGroup() || null != inner.getFetch() || null != inner.getOffset()
486  || (null != inner.getOrderList() && inner.getOrderList().size() != 0)
487  || (null != inner.getGroup() && inner.getGroup().size() != 0)
488  || null == getTableName(inner.getFrom())) {
489  return null;
490  }
491 
492  if (!isCorrelated(inner)) {
493  return null;
494  }
495 
496  final String updateTableName = getTableName(update.getTargetTable());
497 
498  if (null != where) {
499  where = where.accept(new SqlShuttle() {
500  @Override
501  public SqlNode visit(SqlIdentifier id) {
502  if (id.isSimple()) {
503  id = new SqlIdentifier(Arrays.asList(updateTableName, id.getSimple()),
504  id.getParserPosition());
505  }
506 
507  return id;
508  }
509  });
510  }
511 
512  SqlJoin join = new SqlJoin(ZERO,
513  update.getTargetTable(),
514  SqlLiteral.createBoolean(false, ZERO),
515  SqlLiteral.createSymbol(JoinType.LEFT, ZERO),
516  inner.getFrom(),
517  SqlLiteral.createSymbol(JoinConditionType.ON, ZERO),
518  inner.getWhere());
519 
520  SqlNode select0 = inner.getSelectList().get(0);
521 
522  boolean wrapInSingleValue = true;
523  if (select0 instanceof SqlCall) {
524  SqlCall selectExprCall = (SqlCall) select0;
525  if (Util.isSingleValue(selectExprCall)) {
526  wrapInSingleValue = false;
527  }
528  }
529 
530  if (wrapInSingleValue) {
531  if (select0.isA(EnumSet.of(SqlKind.AS))) {
532  select0 = ((SqlCall) select0).getOperandList().get(0);
533  }
534  select0 = new SqlBasicCall(
535  SqlStdOperatorTable.SINGLE_VALUE, new SqlNode[] {select0}, ZERO);
536  }
537 
538  SqlNodeList selectList = new SqlNodeList(ZERO);
539  selectList.add(select0);
540  selectList.add(new SqlBasicCall(SqlStdOperatorTable.AS,
541  new SqlNode[] {new SqlBasicCall(
542  new SqlUnresolvedFunction(
543  new SqlIdentifier("OFFSET_IN_FRAGMENT", ZERO),
544  null,
545  null,
546  null,
547  null,
548  SqlFunctionCategory.USER_DEFINED_FUNCTION),
549  new SqlNode[0],
550  SqlParserPos.ZERO),
551  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
552  ZERO));
553 
554  SqlNodeList groupBy = new SqlNodeList(ZERO);
555  groupBy.add(new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO));
556 
557  SqlSelect select = new SqlSelect(ZERO,
558  null,
559  selectList,
560  join,
561  where,
562  groupBy,
563  null,
564  null,
565  null,
566  null,
567  null,
568  null);
569  return select;
570  }
571 
572  private LogicalTableModify getDummyUpdate(SqlUpdate update)
573  throws SqlParseException, ValidationException, RelConversionException {
574  SqlIdentifier targetTable = (SqlIdentifier) update.getTargetTable();
575  String targetTableName = targetTable.names.get(targetTable.names.size() - 1);
576  MapDPlanner planner = getPlanner();
577  String dummySql = "DELETE FROM " + targetTableName;
578  SqlNode dummyNode = planner.parse(dummySql);
579  dummyNode = planner.validate(dummyNode);
580  RelRoot dummyRoot = planner.rel(dummyNode);
581  LogicalTableModify dummyModify = (LogicalTableModify) dummyRoot.rel;
582  return dummyModify;
583  }
584 
585  private RelRoot rewriteUpdateAsSelect(SqlUpdate update, MapDParserOptions parserOptions)
586  throws SqlParseException, ValidationException, RelConversionException {
587  int correlatedQueriesCount[] = new int[1];
588  SqlBasicVisitor<Void> correlatedQueriesCounter = new SqlBasicVisitor<Void>() {
589  @Override
590  public Void visit(SqlCall call) {
591  if (call.isA(SCALAR)
592  && ((call instanceof SqlBasicCall && call.operandCount() == 1
593  && !call.operand(0).isA(SCALAR))
594  || !(call instanceof SqlBasicCall))) {
595  if (isCorrelated(call)) {
596  correlatedQueriesCount[0]++;
597  }
598  }
599  return super.visit(call);
600  }
601  };
602 
603  update.accept(correlatedQueriesCounter);
604  if (correlatedQueriesCount[0] > 1) {
605  throw new CalciteException(
606  "table modifications with multiple correlated sub-queries not supported.",
607  null);
608  }
609 
610  SqlNodeList sourceExpression = new SqlNodeList(SqlParserPos.ZERO);
611  LogicalTableModify dummyModify = getDummyUpdate(update);
612  RelOptTable targetTable = dummyModify.getTable();
613  RelDataType targetTableType = targetTable.getRowType();
614 
615  SqlSelect select = rewriteSimpleUpdateAsSelect(update);
616  boolean applyRexCast = null == select;
617 
618  if (null == select) {
619  for (int i = 0; i < update.getSourceExpressionList().size(); i++) {
620  SqlNode targetColumn = update.getTargetColumnList().get(i);
621  SqlNode expression = update.getSourceExpressionList().get(i);
622 
623  if (!(targetColumn instanceof SqlIdentifier)) {
624  throw new RuntimeException("Unknown identifier type!");
625  }
626  SqlIdentifier id = (SqlIdentifier) targetColumn;
627  RelDataType fieldType =
628  targetTableType.getField(id.names.get(id.names.size() - 1), false, false)
629  .getType();
630 
631  if (expression.isA(ARRAY_VALUE) && null != fieldType.getComponentType()) {
632  // apply a cast to all array value elements
633 
634  SqlDataTypeSpec elementType = new SqlDataTypeSpec(
635  new SqlBasicTypeNameSpec(fieldType.getComponentType().getSqlTypeName(),
636  fieldType.getPrecision(),
637  fieldType.getScale(),
638  null == fieldType.getCharset() ? null
639  : fieldType.getCharset().name(),
640  SqlParserPos.ZERO),
641  SqlParserPos.ZERO);
642  SqlCall array_expression = (SqlCall) expression;
643  ArrayList<SqlNode> values = new ArrayList<>();
644 
645  for (SqlNode value : array_expression.getOperandList()) {
646  if (value.isA(EnumSet.of(SqlKind.LITERAL))) {
647  SqlNode casted_value = new SqlBasicCall(SqlStdOperatorTable.CAST,
648  new SqlNode[] {value, elementType},
649  value.getParserPosition());
650  values.add(casted_value);
651  } else {
652  values.add(value);
653  }
654  }
655 
656  expression = new SqlBasicCall(MapDSqlOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
657  values.toArray(new SqlNode[0]),
658  expression.getParserPosition());
659  }
660  sourceExpression.add(expression);
661  }
662 
663  sourceExpression.add(new SqlBasicCall(SqlStdOperatorTable.AS,
664  new SqlNode[] {
665  new SqlBasicCall(new SqlUnresolvedFunction(
666  new SqlIdentifier("OFFSET_IN_FRAGMENT",
667  SqlParserPos.ZERO),
668  null,
669  null,
670  null,
671  null,
672  SqlFunctionCategory.USER_DEFINED_FUNCTION),
673  new SqlNode[0],
674  SqlParserPos.ZERO),
675  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
676  ZERO));
677 
678  select = new SqlSelect(SqlParserPos.ZERO,
679  null,
680  sourceExpression,
681  update.getTargetTable(),
682  update.getCondition(),
683  null,
684  null,
685  null,
686  null,
687  null,
688  null,
689  null);
690  }
691 
692  MapDPlanner planner = getPlanner(true);
693  SqlNode node = null;
694  try {
695  node = planner.parse(select.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
696  node = planner.validate(node);
697  } catch (Exception e) {
698  MAPDLOGGER.error("Error processing UPDATE rewrite, rewritten stmt was: "
699  + select.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
700  throw e;
701  }
702 
703  RelRoot root = planner.rel(node);
704  LogicalProject project = (LogicalProject) root.project();
705 
706  ArrayList<String> fields = new ArrayList<String>();
707  ArrayList<RexNode> nodes = new ArrayList<RexNode>();
708  final RexBuilder builder = new RexBuilder(planner.getTypeFactory());
709 
710  for (SqlNode n : update.getTargetColumnList()) {
711  if (n instanceof SqlIdentifier) {
712  SqlIdentifier id = (SqlIdentifier) n;
713  fields.add(id.names.get(id.names.size() - 1));
714  } else {
715  throw new RuntimeException("Unknown identifier type!");
716  }
717  }
718 
719  // The magical number here when processing the projection
720  // is skipping the OFFSET_IN_FRAGMENT() expression used by
721  // update and delete
722  int idx = 0;
723  for (RexNode exp : project.getProjects()) {
724  if (applyRexCast && idx + 1 < project.getProjects().size()) {
725  RelDataType expectedFieldType =
726  targetTableType.getField(fields.get(idx), false, false).getType();
727  if (!exp.getType().equals(expectedFieldType) && !exp.isA(ARRAY_VALUE)) {
728  exp = builder.makeCast(expectedFieldType, exp);
729  }
730  }
731 
732  nodes.add(exp);
733  idx++;
734  }
735 
736  ArrayList<RexNode> inputs = new ArrayList<RexNode>();
737  int n = 0;
738  for (int i = 0; i < fields.size(); i++) {
739  inputs.add(
740  new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
741  n++;
742  }
743 
744  fields.add("EXPR$DELETE_OFFSET_IN_FRAGMENT");
745  inputs.add(new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
746 
747  project = project.copy(
748  project.getTraitSet(), project.getInput(), nodes, project.getRowType());
749 
750  LogicalTableModify modify = LogicalTableModify.create(targetTable,
751  dummyModify.getCatalogReader(),
752  project,
753  Operation.UPDATE,
754  fields,
755  inputs,
756  true);
757  return RelRoot.of(modify, SqlKind.UPDATE);
758  }
759 
760  RelRoot queryToRelNode(final String sql, final MapDParserOptions parserOptions)
761  throws SqlParseException, ValidationException, RelConversionException {
762  final MapDPlanner planner = getPlanner(true);
763  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
764  return convertSqlToRelNode(sqlNode, planner, parserOptions);
765  }
766 
767  RelRoot convertSqlToRelNode(final SqlNode sqlNode,
768  final MapDPlanner mapDPlanner,
769  final MapDParserOptions parserOptions)
770  throws SqlParseException, ValidationException, RelConversionException {
771  SqlNode node = sqlNode;
772  MapDPlanner planner = mapDPlanner;
773  boolean allowCorrelatedSubQueryExpansion = true;
774  boolean patchUpdateToDelete = false;
775 
776  if (node.isA(DELETE)) {
777  SqlDelete sqlDelete = (SqlDelete) node;
778  node = new SqlUpdate(node.getParserPosition(),
779  sqlDelete.getTargetTable(),
780  SqlNodeList.EMPTY,
781  SqlNodeList.EMPTY,
782  sqlDelete.getCondition(),
783  sqlDelete.getSourceSelect(),
784  sqlDelete.getAlias());
785 
786  patchUpdateToDelete = true;
787  }
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 
811  if (parserOptions.isLegacySyntax()) {
812  // close original planner
813  planner.close();
814  // create a new one
815  planner = getPlanner(allowCorrelatedSubQueryExpansion);
816  node = parseSql(
817  node.toSqlString(CalciteSqlDialect.DEFAULT).toString(), false, planner);
818  }
819 
820  SqlNode validateR = planner.validate(node);
821  planner.setFilterPushDownInfo(parserOptions.getFilterPushDownInfo());
822  RelRoot relR = planner.rel(validateR);
823  relR = replaceIsTrue(planner.getTypeFactory(), relR);
824  planner.close();
825 
826  if (!parserOptions.isViewOptimizeEnabled()) {
827  return relR;
828  } else {
829  // check to see if a view is involved in the query
830  boolean foundView = false;
831  MapDSchema schema = new MapDSchema(
832  dataDir, this, mapdPort, mapdUser, sock_transport_properties);
833  SqlIdentifierCapturer capturer = captureIdentifiers(sqlNode);
834  for (ImmutableList<String> names : capturer.selects) {
835  MapDTable table = (MapDTable) schema.getTable(names.get(0));
836  if (null == table) {
837  throw new RuntimeException("table/view not found: " + names.get(0));
838  }
839  if (table instanceof MapDView) {
840  foundView = true;
841  }
842  }
843 
844  if (!foundView) {
845  return relR;
846  }
847 
848  HepProgramBuilder builder = new HepProgramBuilder();
849  builder.addRuleInstance(CoreRules.JOIN_PROJECT_BOTH_TRANSPOSE_INCLUDE_OUTER);
850  builder.addRuleInstance(CoreRules.FILTER_MERGE);
851  builder.addRuleInstance(CoreRules.FILTER_PROJECT_TRANSPOSE);
852  builder.addRuleInstance(CoreRules.PROJECT_MERGE);
853  builder.addRuleInstance(ProjectProjectRemoveRule.INSTANCE);
854  HepPlanner hepPlanner = MapDPlanner.getHepPlanner(builder.build(), true);
855  final RelNode root = relR.project();
856  hepPlanner.setRoot(root);
857  final RelNode newRel = hepPlanner.findBestExp();
858 
859  return RelRoot.of(newRel, relR.kind);
860  }
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)
#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)