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