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