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