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