OmniSciDB  16c4e035a1
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
MapDParser.java
Go to the documentation of this file.
1 /*
2  * Copyright 2021 OmniSci, Inc.
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 package com.mapd.calcite.parser;
17 
18 import static org.apache.calcite.sql.parser.SqlParserPos.ZERO;
19 
20 import com.google.common.collect.ImmutableList;
24 import com.mapd.parser.extension.ddl.ExtendedSqlParser;
27 import com.omnisci.thrift.server.TTableDetails;
28 
29 import org.apache.calcite.avatica.util.Casing;
30 import org.apache.calcite.config.CalciteConnectionConfig;
31 import org.apache.calcite.config.CalciteConnectionConfigImpl;
32 import org.apache.calcite.config.CalciteConnectionProperty;
33 import org.apache.calcite.plan.Context;
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.HepProgramBuilder;
40 import org.apache.calcite.rel.RelNode;
41 import org.apache.calcite.rel.RelRoot;
42 import org.apache.calcite.rel.RelShuttleImpl;
43 import org.apache.calcite.rel.core.TableModify;
44 import org.apache.calcite.rel.core.TableModify.Operation;
45 import org.apache.calcite.rel.logical.LogicalProject;
46 import org.apache.calcite.rel.logical.LogicalTableModify;
47 import org.apache.calcite.rel.rules.CoreRules;
48 import org.apache.calcite.rel.type.RelDataType;
49 import org.apache.calcite.rel.type.RelDataTypeFactory;
50 import org.apache.calcite.rel.type.RelDataTypeSystem;
51 import org.apache.calcite.rex.*;
52 import org.apache.calcite.runtime.CalciteException;
53 import org.apache.calcite.schema.SchemaPlus;
54 import org.apache.calcite.sql.*;
55 import org.apache.calcite.sql.dialect.CalciteSqlDialect;
56 import org.apache.calcite.sql.fun.SqlCase;
57 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
58 import org.apache.calcite.sql.parser.SqlParseException;
59 import org.apache.calcite.sql.parser.SqlParser;
60 import org.apache.calcite.sql.parser.SqlParserPos;
61 import org.apache.calcite.sql.type.SqlTypeName;
62 import org.apache.calcite.sql.type.SqlTypeUtil;
63 import org.apache.calcite.sql.util.SqlBasicVisitor;
64 import org.apache.calcite.sql.util.SqlShuttle;
65 import org.apache.calcite.sql.validate.SqlConformanceEnum;
67 import org.apache.calcite.tools.*;
68 import org.apache.calcite.util.Pair;
69 import org.apache.calcite.util.Util;
70 import org.slf4j.Logger;
71 import org.slf4j.LoggerFactory;
72 
73 import java.io.IOException;
74 import java.lang.reflect.Field;
75 import java.util.*;
76 import java.util.concurrent.ConcurrentHashMap;
77 import java.util.function.BiPredicate;
78 import java.util.function.Supplier;
79 
84 public final class MapDParser {
85  public static final ThreadLocal<MapDParser> CURRENT_PARSER = new ThreadLocal<>();
86  private static final EnumSet<SqlKind> SCALAR =
87  EnumSet.of(SqlKind.SCALAR_QUERY, SqlKind.SELECT);
88  private static final EnumSet<SqlKind> EXISTS = EnumSet.of(SqlKind.EXISTS);
89  private static final EnumSet<SqlKind> DELETE = EnumSet.of(SqlKind.DELETE);
90  private static final EnumSet<SqlKind> UPDATE = EnumSet.of(SqlKind.UPDATE);
91  private static final EnumSet<SqlKind> IN = EnumSet.of(SqlKind.IN);
92  private static final EnumSet<SqlKind> ARRAY_VALUE =
93  EnumSet.of(SqlKind.ARRAY_VALUE_CONSTRUCTOR);
94 
95  final static Logger MAPDLOGGER = LoggerFactory.getLogger(MapDParser.class);
96 
97  private final Supplier<MapDSqlOperatorTable> mapDSqlOperatorTable;
98  private final String dataDir;
99 
100  private int callCount = 0;
101  private final int mapdPort;
104 
105  private static Map<String, Boolean> SubqueryCorrMemo = new ConcurrentHashMap<>();
106 
107  public MapDParser(String dataDir,
108  final Supplier<MapDSqlOperatorTable> mapDSqlOperatorTable,
109  int mapdPort,
111  this.dataDir = dataDir;
112  this.mapDSqlOperatorTable = mapDSqlOperatorTable;
113  this.mapdPort = mapdPort;
114  this.sock_transport_properties = skT;
115  }
116 
117  public void clearMemo() {
118  SubqueryCorrMemo.clear();
119  }
120 
121  private static final Context MAPD_CONNECTION_CONTEXT = new Context() {
122  MapDTypeSystem myTypeSystem = new MapDTypeSystem();
123  CalciteConnectionConfig config = new CalciteConnectionConfigImpl(new Properties()) {
124  {
125  properties.put(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
126  String.valueOf(false));
127  properties.put(CalciteConnectionProperty.CONFORMANCE.camelName(),
128  String.valueOf(SqlConformanceEnum.LENIENT));
129  }
130 
131  @SuppressWarnings("unchecked")
132  public <T extends Object> T typeSystem(
133  java.lang.Class<T> typeSystemClass, T defaultTypeSystem) {
134  return (T) myTypeSystem;
135  };
136 
137  public boolean caseSensitive() {
138  return false;
139  };
140 
141  public org.apache.calcite.sql.validate.SqlConformance conformance() {
142  return SqlConformanceEnum.LENIENT;
143  };
144  };
145 
146  @Override
147  public <C> C unwrap(Class<C> aClass) {
148  if (aClass.isInstance(config)) {
149  return aClass.cast(config);
150  }
151  return null;
152  }
153  };
154 
156  return getPlanner(true, false);
157  }
158 
159  private boolean isCorrelated(SqlNode expression) {
160  String queryString = expression.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
161  Boolean isCorrelatedSubquery = SubqueryCorrMemo.get(queryString);
162  if (null != isCorrelatedSubquery) {
163  return isCorrelatedSubquery;
164  }
165 
166  try {
169  MapDParserOptions options = new MapDParserOptions();
170  parser.setUser(mapdUser);
171  parser.processSql(expression, options);
172  } catch (Exception e) {
173  // if we are not able to parse, then assume correlated
174  SubqueryCorrMemo.put(queryString, true);
175  return true;
176  }
177  SubqueryCorrMemo.put(queryString, false);
178  return false;
179  }
180 
182  final boolean allowSubQueryExpansion, final boolean isWatchdogEnabled) {
183  BiPredicate<SqlNode, SqlNode> expandPredicate = new BiPredicate<SqlNode, SqlNode>() {
184  @Override
185  public boolean test(SqlNode root, SqlNode expression) {
186  if (!allowSubQueryExpansion) {
187  return false;
188  }
189 
190  if (expression.isA(EXISTS) || expression.isA(IN)) {
191  // try to expand subquery by EXISTS and IN clauses by default
192  // note that current Calcite decorrelator fails to flat
193  // NOT-IN clause in some cases, so we do not decorrelate it for now
194 
195  if (expression.isA(IN)) {
196  // If we enable watchdog, we suffer from large projection exception in many
197  // cases since decorrelation needs de-duplication step which adds project -
198  // aggregate logic. And the added project is the source of the exception when
199  // its underlying table is large. Thus, we enable IN-clause decorrelation
200  // under watchdog iff we explicitly have correlated join in IN-clause
201  if (isWatchdogEnabled) {
202  boolean found_expression = false;
203  if (expression instanceof SqlCall) {
204  SqlCall call = (SqlCall) expression;
205  if (call.getOperandList().size() == 2) {
206  // if IN clause is correlated, its second operand of corresponding
207  // expression is SELECT clause which indicates a correlated subquery.
208  // Here, an expression "f.val IN (SELECT ...)" has two operands.
209  // Since we have interest in its subquery, so try to check whether
210  // the second operand, i.e., call.getOperandList().get(1)
211  // is a type of SqlSelect and also is correlated.
212  // Note that the second operand of non-correlated IN clause
213  // does not have SqlSelect as its second operand
214  if (call.getOperandList().get(1) instanceof SqlSelect) {
215  expression = call.getOperandList().get(1);
216  SqlSelect select_call = (SqlSelect) expression;
217  if (select_call.hasWhere()) {
218  found_expression = true;
219  }
220  }
221  }
222  }
223  if (!found_expression) {
224  return false;
225  }
226  }
227 
228  if (root instanceof SqlSelect) {
229  SqlSelect selectCall = (SqlSelect) root;
230  if (new ExpressionListedInSelectClauseChecker().containsExpression(
231  selectCall, expression)) {
232  // occasionally, Calcite cannot properly decorrelate IN-clause listed in
233  // SELECT clause e.g., SELECT x, CASE WHEN x in (SELECT x FROM R) ... FROM
234  // ... in that case we disable input query's decorrelation
235  return false;
236  }
237  if (null != selectCall.getWhere()) {
238  if (new ExpressionListedAsChildOROperatorChecker().containsExpression(
239  selectCall.getWhere(), expression)) {
240  // Decorrelation logic of the current Calcite cannot cover IN-clause
241  // well if it is listed as a child operand of OR-op
242  return false;
243  }
244  }
245  if (null != selectCall.getHaving()) {
246  if (new ExpressionListedAsChildOROperatorChecker().containsExpression(
247  selectCall.getHaving(), expression)) {
248  // Decorrelation logic of the current Calcite cannot cover IN-clause
249  // well if it is listed as a child operand of OR-op
250  return false;
251  }
252  }
253  }
254  }
255 
256  // otherwise, let's decorrelate the expression
257  return true;
258  }
259 
260  // special handling of sub-queries
261  if (expression.isA(SCALAR) && isCorrelated(expression)) {
262  // only expand if it is correlated.
263  SqlSelect select = null;
264  if (expression instanceof SqlCall) {
265  SqlCall call = (SqlCall) expression;
266  if (call.getOperator().equals(SqlStdOperatorTable.SCALAR_QUERY)) {
267  expression = call.getOperandList().get(0);
268  }
269  }
270 
271  if (expression instanceof SqlSelect) {
272  select = (SqlSelect) expression;
273  }
274 
275  if (null != select) {
276  if (null != select.getFetch() || null != select.getOffset()
277  || (null != select.getOrderList()
278  && select.getOrderList().size() != 0)) {
279  throw new CalciteException(
280  "Correlated sub-queries with ordering not supported.", null);
281  }
282  }
283  return true;
284  }
285 
286  // per default we do not want to expand
287  return false;
288  }
289  };
290 
291  // create the default schema
292  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
293  final MapDSchema defaultSchema =
295  final SchemaPlus defaultSchemaPlus = rootSchema.add(mapdUser.getDB(), defaultSchema);
296 
297  // add the other potential schemas
298  // this is where the systyem schema would be added
299  final MetaConnect mc =
301 
302  // TODO MAT for this checkin we are not going to actually allow any additional schemas
303  // Eveything should work and perform as it ever did
304  if (false) {
305  for (String db : mc.getDatabases()) {
306  if (!db.toUpperCase().equals(mapdUser.getDB().toUpperCase())) {
307  rootSchema.add(db,
308  new MapDSchema(dataDir,
309  this,
310  mapdPort,
311  mapdUser,
313  db));
314  }
315  }
316  }
317 
318  final FrameworkConfig config =
319  Frameworks.newConfigBuilder()
320  .defaultSchema(defaultSchemaPlus)
321  .operatorTable(mapDSqlOperatorTable.get())
322  .parserConfig(SqlParser.configBuilder()
323  .setConformance(SqlConformanceEnum.LENIENT)
324  .setUnquotedCasing(Casing.UNCHANGED)
325  .setCaseSensitive(false)
326  // allow identifiers of up to 512 chars
327  .setIdentifierMaxLength(512)
328  .setParserFactory(ExtendedSqlParser.FACTORY)
329  .build())
330  .sqlToRelConverterConfig(
331  SqlToRelConverter
332  .configBuilder()
333  // enable sub-query expansion (de-correlation)
334  .withExpandPredicate(expandPredicate)
335  // allow as many as possible IN operator values
336  .withInSubQueryThreshold(Integer.MAX_VALUE)
337  .withHintStrategyTable(
339  .build())
340  .typeSystem(createTypeSystem())
341  .context(MAPD_CONNECTION_CONTEXT)
342  .build();
343  MapDPlanner planner = new MapDPlanner(config);
344  planner.setRestrictions(mapdUser.getRestrictions());
345  return planner;
346  }
347 
348  public void setUser(MapDUser mapdUser) {
349  this.mapdUser = mapdUser;
350  }
351 
352  public Pair<String, SqlIdentifierCapturer> process(
353  String sql, final MapDParserOptions parserOptions)
354  throws SqlParseException, ValidationException, RelConversionException {
355  final MapDPlanner planner = getPlanner(true, parserOptions.isWatchdogEnabled());
356  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
357  String res = processSql(sqlNode, parserOptions);
358  SqlIdentifierCapturer capture = captureIdentifiers(sqlNode);
359  return new Pair<String, SqlIdentifierCapturer>(res, capture);
360  }
361 
362  public String optimizeRAQuery(String query, final MapDParserOptions parserOptions)
363  throws IOException {
364  MapDSchema schema =
366  MapDPlanner planner = getPlanner(true, parserOptions.isWatchdogEnabled());
367 
368  planner.setFilterPushDownInfo(parserOptions.getFilterPushDownInfo());
369  RelRoot optRel = planner.optimizeRaQuery(query, schema);
370  optRel = replaceIsTrue(planner.getTypeFactory(), optRel);
371  return MapDSerializer.toString(optRel.project());
372  }
373 
374  public String processSql(String sql, final MapDParserOptions parserOptions)
375  throws SqlParseException, ValidationException, RelConversionException {
376  callCount++;
377 
378  final MapDPlanner planner = getPlanner(true, parserOptions.isWatchdogEnabled());
379  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
380 
381  return processSql(sqlNode, parserOptions);
382  }
383 
384  public String processSql(final SqlNode sqlNode, final MapDParserOptions parserOptions)
385  throws SqlParseException, ValidationException, RelConversionException {
386  callCount++;
387 
388  if (sqlNode instanceof JsonSerializableDdl) {
389  return ((JsonSerializableDdl) sqlNode).toJsonString();
390  }
391 
392  if (sqlNode instanceof SqlDdl) {
393  return sqlNode.toString();
394  }
395 
396  final MapDPlanner planner = getPlanner(true, parserOptions.isWatchdogEnabled());
397  planner.advanceToValidate();
398 
399  final RelRoot sqlRel = convertSqlToRelNode(sqlNode, planner, parserOptions);
400  RelNode project = sqlRel.project();
401 
402  if (parserOptions.isExplain()) {
403  return RelOptUtil.toString(sqlRel.project());
404  }
405 
406  String res = MapDSerializer.toString(project);
407 
408  return res;
409  }
410 
411  public MapDPlanner.CompletionResult getCompletionHints(
412  String sql, int cursor, List<String> visible_tables) {
413  return getPlanner().getCompletionHints(sql, cursor, visible_tables);
414  }
415 
416  public HashSet<ImmutableList<String>> resolveSelectIdentifiers(
417  SqlIdentifierCapturer capturer) {
418  MapDSchema schema =
420  HashSet<ImmutableList<String>> resolved = new HashSet<ImmutableList<String>>();
421 
422  for (ImmutableList<String> names : capturer.selects) {
423  MapDTable table = (MapDTable) schema.getTable(names.get(0));
424  if (null == table) {
425  throw new RuntimeException("table/view not found: " + names.get(0));
426  }
427 
428  if (table instanceof MapDView) {
429  MapDView view = (MapDView) table;
430  resolved.addAll(resolveSelectIdentifiers(view.getAccessedObjects()));
431  } else {
432  resolved.add(names);
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  if (select0.isA(EnumSet.of(SqlKind.AS))) {
510  select0 = ((SqlCall) select0).getOperandList().get(0);
511  }
512  select0 = new SqlBasicCall(
513  SqlStdOperatorTable.SINGLE_VALUE, new SqlNode[] {select0}, ZERO);
514  }
515 
516  SqlNodeList selectList = new SqlNodeList(ZERO);
517  selectList.add(select0);
518  selectList.add(new SqlBasicCall(SqlStdOperatorTable.AS,
519  new SqlNode[] {new SqlBasicCall(
520  new SqlUnresolvedFunction(
521  new SqlIdentifier("OFFSET_IN_FRAGMENT", ZERO),
522  null,
523  null,
524  null,
525  null,
526  SqlFunctionCategory.USER_DEFINED_FUNCTION),
527  new SqlNode[0],
528  SqlParserPos.ZERO),
529  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
530  ZERO));
531 
532  SqlNodeList groupBy = new SqlNodeList(ZERO);
533  groupBy.add(new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO));
534 
535  SqlSelect select = new SqlSelect(ZERO,
536  null,
537  selectList,
538  join,
539  where,
540  groupBy,
541  null,
542  null,
543  null,
544  null,
545  null,
546  null);
547  return select;
548  }
549 
550  private LogicalTableModify getDummyUpdate(SqlUpdate update)
551  throws SqlParseException, ValidationException, RelConversionException {
552  SqlIdentifier targetTable = (SqlIdentifier) update.getTargetTable();
553  String targetTableName = targetTable.names.get(targetTable.names.size() - 1);
554  MapDPlanner planner = getPlanner();
555  String dummySql = "DELETE FROM " + targetTableName;
556  SqlNode dummyNode = planner.parse(dummySql);
557  dummyNode = planner.validate(dummyNode);
558  RelRoot dummyRoot = planner.rel(dummyNode);
559  LogicalTableModify dummyModify = (LogicalTableModify) dummyRoot.rel;
560  return dummyModify;
561  }
562 
563  private RelRoot rewriteUpdateAsSelect(SqlUpdate update, MapDParserOptions parserOptions)
564  throws SqlParseException, ValidationException, RelConversionException {
565  int correlatedQueriesCount[] = new int[1];
566  SqlBasicVisitor<Void> correlatedQueriesCounter = new SqlBasicVisitor<Void>() {
567  @Override
568  public Void visit(SqlCall call) {
569  if (call.isA(SCALAR)
570  && ((call instanceof SqlBasicCall && call.operandCount() == 1
571  && !call.operand(0).isA(SCALAR))
572  || !(call instanceof SqlBasicCall))) {
573  if (isCorrelated(call)) {
574  correlatedQueriesCount[0]++;
575  }
576  }
577  return super.visit(call);
578  }
579  };
580 
581  update.accept(correlatedQueriesCounter);
582  if (correlatedQueriesCount[0] > 1) {
583  throw new CalciteException(
584  "table modifications with multiple correlated sub-queries not supported.",
585  null);
586  }
587 
588  boolean allowSubqueryDecorrelation = true;
589  SqlNode updateCondition = update.getCondition();
590  if (null != updateCondition) {
591  boolean hasInClause =
592  new FindSqlOperator().containsSqlOperator(updateCondition, SqlKind.IN);
593  if (hasInClause) {
594  SqlNode updateTargetTable = update.getTargetTable();
595  if (null != updateTargetTable && updateTargetTable instanceof SqlIdentifier) {
596  SqlIdentifier targetTable = (SqlIdentifier) updateTargetTable;
597  if (targetTable.names.size() == 2) {
598  final MetaConnect mc = new MetaConnect(mapdPort,
599  dataDir,
600  mapdUser,
601  this,
602  sock_transport_properties,
603  targetTable.names.get(0));
604  TTableDetails updateTargetTableDetails =
605  mc.get_table_details(targetTable.names.get(1));
606  if (null != updateTargetTableDetails
607  && updateTargetTableDetails.is_temporary) {
608  allowSubqueryDecorrelation = false;
609  }
610  }
611  }
612  }
613  }
614 
615  SqlNodeList sourceExpression = new SqlNodeList(SqlParserPos.ZERO);
616  LogicalTableModify dummyModify = getDummyUpdate(update);
617  RelOptTable targetTable = dummyModify.getTable();
618  RelDataType targetTableType = targetTable.getRowType();
619 
620  SqlSelect select = rewriteSimpleUpdateAsSelect(update);
621  boolean applyRexCast = null == select;
622 
623  if (null == select) {
624  for (int i = 0; i < update.getSourceExpressionList().size(); i++) {
625  SqlNode targetColumn = update.getTargetColumnList().get(i);
626  SqlNode expression = update.getSourceExpressionList().get(i);
627 
628  if (!(targetColumn instanceof SqlIdentifier)) {
629  throw new RuntimeException("Unknown identifier type!");
630  }
631  SqlIdentifier id = (SqlIdentifier) targetColumn;
632  RelDataType fieldType =
633  targetTableType.getField(id.names.get(id.names.size() - 1), false, false)
634  .getType();
635 
636  if (expression.isA(ARRAY_VALUE) && null != fieldType.getComponentType()) {
637  // apply a cast to all array value elements
638 
639  SqlDataTypeSpec elementType = new SqlDataTypeSpec(
640  new SqlBasicTypeNameSpec(fieldType.getComponentType().getSqlTypeName(),
641  fieldType.getPrecision(),
642  fieldType.getScale(),
643  null == fieldType.getCharset() ? null
644  : fieldType.getCharset().name(),
645  SqlParserPos.ZERO),
646  SqlParserPos.ZERO);
647  SqlCall array_expression = (SqlCall) expression;
648  ArrayList<SqlNode> values = new ArrayList<>();
649 
650  for (SqlNode value : array_expression.getOperandList()) {
651  if (value.isA(EnumSet.of(SqlKind.LITERAL))) {
652  SqlNode casted_value = new SqlBasicCall(SqlStdOperatorTable.CAST,
653  new SqlNode[] {value, elementType},
654  value.getParserPosition());
655  values.add(casted_value);
656  } else {
657  values.add(value);
658  }
659  }
660 
661  expression = new SqlBasicCall(MapDSqlOperatorTable.ARRAY_VALUE_CONSTRUCTOR,
662  values.toArray(new SqlNode[0]),
663  expression.getParserPosition());
664  }
665  sourceExpression.add(expression);
666  }
667 
668  sourceExpression.add(new SqlBasicCall(SqlStdOperatorTable.AS,
669  new SqlNode[] {
670  new SqlBasicCall(new SqlUnresolvedFunction(
671  new SqlIdentifier("OFFSET_IN_FRAGMENT",
672  SqlParserPos.ZERO),
673  null,
674  null,
675  null,
676  null,
677  SqlFunctionCategory.USER_DEFINED_FUNCTION),
678  new SqlNode[0],
679  SqlParserPos.ZERO),
680  new SqlIdentifier("EXPR$DELETE_OFFSET_IN_FRAGMENT", ZERO)},
681  ZERO));
682 
683  select = new SqlSelect(SqlParserPos.ZERO,
684  null,
685  sourceExpression,
686  update.getTargetTable(),
687  update.getCondition(),
688  null,
689  null,
690  null,
691  null,
692  null,
693  null,
694  null);
695  }
696 
697  MapDPlanner planner =
698  getPlanner(allowSubqueryDecorrelation, parserOptions.isWatchdogEnabled());
699  SqlNode node = null;
700  try {
701  node = planner.parse(select.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
702  node = planner.validate(node);
703  } catch (Exception e) {
704  MAPDLOGGER.error("Error processing UPDATE rewrite, rewritten stmt was: "
705  + select.toSqlString(CalciteSqlDialect.DEFAULT).getSql());
706  throw e;
707  }
708 
709  RelRoot root = planner.rel(node);
710  LogicalProject project = (LogicalProject) root.project();
711 
712  ArrayList<String> fields = new ArrayList<String>();
713  ArrayList<RexNode> nodes = new ArrayList<RexNode>();
714  final RexBuilder builder = new RexBuilder(planner.getTypeFactory());
715 
716  for (SqlNode n : update.getTargetColumnList()) {
717  if (n instanceof SqlIdentifier) {
718  SqlIdentifier id = (SqlIdentifier) n;
719  fields.add(id.names.get(id.names.size() - 1));
720  } else {
721  throw new RuntimeException("Unknown identifier type!");
722  }
723  }
724 
725  // The magical number here when processing the projection
726  // is skipping the OFFSET_IN_FRAGMENT() expression used by
727  // update and delete
728  int idx = 0;
729  for (RexNode exp : project.getProjects()) {
730  if (applyRexCast && idx + 1 < project.getProjects().size()) {
731  RelDataType expectedFieldType =
732  targetTableType.getField(fields.get(idx), false, false).getType();
733  if (!exp.getType().equals(expectedFieldType) && !exp.isA(ARRAY_VALUE)) {
734  exp = builder.makeCast(expectedFieldType, exp);
735  }
736  }
737 
738  nodes.add(exp);
739  idx++;
740  }
741 
742  ArrayList<RexNode> inputs = new ArrayList<RexNode>();
743  int n = 0;
744  for (int i = 0; i < fields.size(); i++) {
745  inputs.add(
746  new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
747  n++;
748  }
749 
750  fields.add("EXPR$DELETE_OFFSET_IN_FRAGMENT");
751  inputs.add(new RexInputRef(n, project.getRowType().getFieldList().get(n).getType()));
752 
753  project = project.copy(
754  project.getTraitSet(), project.getInput(), nodes, project.getRowType());
755 
756  LogicalTableModify modify = LogicalTableModify.create(targetTable,
757  dummyModify.getCatalogReader(),
758  project,
759  Operation.UPDATE,
760  fields,
761  inputs,
762  true);
763  return RelRoot.of(modify, SqlKind.UPDATE);
764  }
765 
766  RelRoot queryToRelNode(final String sql, final MapDParserOptions parserOptions)
767  throws SqlParseException, ValidationException, RelConversionException {
768  final MapDPlanner planner = getPlanner(true, parserOptions.isWatchdogEnabled());
769  final SqlNode sqlNode = parseSql(sql, parserOptions.isLegacySyntax(), planner);
770  return convertSqlToRelNode(sqlNode, planner, parserOptions);
771  }
772 
773  RelRoot convertSqlToRelNode(final SqlNode sqlNode,
774  final MapDPlanner mapDPlanner,
775  final MapDParserOptions parserOptions)
776  throws SqlParseException, ValidationException, RelConversionException {
777  SqlNode node = sqlNode;
778  MapDPlanner planner = mapDPlanner;
779  boolean allowCorrelatedSubQueryExpansion = true;
780  boolean patchUpdateToDelete = false;
781  if (node.isA(DELETE)) {
782  SqlDelete sqlDelete = (SqlDelete) node;
783  node = new SqlUpdate(node.getParserPosition(),
784  sqlDelete.getTargetTable(),
785  SqlNodeList.EMPTY,
786  SqlNodeList.EMPTY,
787  sqlDelete.getCondition(),
788  sqlDelete.getSourceSelect(),
789  sqlDelete.getAlias());
790 
791  patchUpdateToDelete = true;
792  }
793  if (node.isA(UPDATE)) {
794  SqlUpdate update = (SqlUpdate) node;
795  update = (SqlUpdate) planner.validate(update);
796  RelRoot root = rewriteUpdateAsSelect(update, parserOptions);
797 
798  if (patchUpdateToDelete) {
799  LogicalTableModify modify = (LogicalTableModify) root.rel;
800 
801  try {
802  Field f = TableModify.class.getDeclaredField("operation");
803  f.setAccessible(true);
804  f.set(modify, Operation.DELETE);
805  } catch (Throwable e) {
806  throw new RuntimeException(e);
807  }
808 
809  root = RelRoot.of(modify, SqlKind.DELETE);
810  }
811 
812  return root;
813  }
814  if (parserOptions.isLegacySyntax()) {
815  // close original planner
816  planner.close();
817  // create a new one
818  planner = getPlanner(
819  allowCorrelatedSubQueryExpansion, parserOptions.isWatchdogEnabled());
820  node = parseSql(
821  node.toSqlString(CalciteSqlDialect.DEFAULT).toString(), false, planner);
822  }
823 
824  SqlNode validateR = planner.validate(node);
825  planner.setFilterPushDownInfo(parserOptions.getFilterPushDownInfo());
826  RelRoot relR = planner.rel(validateR);
827  relR = replaceIsTrue(planner.getTypeFactory(), relR);
828  planner.close();
829 
830  HepProgramBuilder builder = new HepProgramBuilder();
831  if (!parserOptions.isViewOptimizeEnabled()) {
832  builder.addRuleInstance(CoreRules.FILTER_PROJECT_TRANSPOSE);
833  builder.addRuleInstance(
834  FilterTableFunctionMultiInputTransposeRule.Config.DEFAULT.toRule());
835  builder.addRuleInstance(CoreRules.FILTER_PROJECT_TRANSPOSE);
836  } else {
837  // check to see if a view is involved in the query
838  boolean foundView = false;
839  MapDSchema schema = new MapDSchema(
840  dataDir, this, mapdPort, mapdUser, sock_transport_properties);
841  SqlIdentifierCapturer capturer = captureIdentifiers(sqlNode);
842  for (ImmutableList<String> names : capturer.selects) {
843  MapDTable table = (MapDTable) schema.getTable(names.get(0));
844  if (null == table) {
845  throw new RuntimeException("table/view not found: " + names.get(0));
846  }
847  if (table instanceof MapDView) {
848  foundView = true;
849  }
850  }
851  if (foundView) {
852  builder.addRuleInstance(CoreRules.JOIN_PROJECT_BOTH_TRANSPOSE_INCLUDE_OUTER);
853  builder.addRuleInstance(CoreRules.FILTER_MERGE);
854  }
855  builder.addRuleInstance(CoreRules.FILTER_PROJECT_TRANSPOSE);
856  builder.addRuleInstance(
857  FilterTableFunctionMultiInputTransposeRule.Config.DEFAULT.toRule());
858  builder.addRuleInstance(CoreRules.FILTER_PROJECT_TRANSPOSE);
859  if (foundView) {
860  builder.addRuleInstance(CoreRules.PROJECT_MERGE);
861  builder.addRuleInstance(ProjectProjectRemoveRule.INSTANCE);
862  }
863  }
864  HepPlanner hepPlanner = MapDPlanner.getHepPlanner(builder.build(), true);
865  final RelNode root = relR.project();
866  hepPlanner.setRoot(root);
867  final RelNode newRel = hepPlanner.findBestExp();
868  return RelRoot.of(newRel, relR.kind);
869  }
870 
871  private RelRoot replaceIsTrue(final RelDataTypeFactory typeFactory, RelRoot root) {
872  final RexShuttle callShuttle = new RexShuttle() {
873  RexBuilder builder = new RexBuilder(typeFactory);
874 
875  public RexNode visitCall(RexCall call) {
876  call = (RexCall) super.visitCall(call);
877  if (call.getKind() == SqlKind.IS_TRUE) {
878  return builder.makeCall(SqlStdOperatorTable.AND,
879  builder.makeCall(
880  SqlStdOperatorTable.IS_NOT_NULL, call.getOperands().get(0)),
881  call.getOperands().get(0));
882  } else if (call.getKind() == SqlKind.IS_NOT_TRUE) {
883  return builder.makeCall(SqlStdOperatorTable.OR,
884  builder.makeCall(
885  SqlStdOperatorTable.IS_NULL, call.getOperands().get(0)),
886  builder.makeCall(SqlStdOperatorTable.NOT, call.getOperands().get(0)));
887  } else if (call.getKind() == SqlKind.IS_FALSE) {
888  return builder.makeCall(SqlStdOperatorTable.AND,
889  builder.makeCall(
890  SqlStdOperatorTable.IS_NOT_NULL, call.getOperands().get(0)),
891  builder.makeCall(SqlStdOperatorTable.NOT, call.getOperands().get(0)));
892  } else if (call.getKind() == SqlKind.IS_NOT_FALSE) {
893  return builder.makeCall(SqlStdOperatorTable.OR,
894  builder.makeCall(
895  SqlStdOperatorTable.IS_NULL, call.getOperands().get(0)),
896  call.getOperands().get(0));
897  }
898 
899  return call;
900  }
901  };
902 
903  RelNode node = root.rel.accept(new RelShuttleImpl() {
904  @Override
905  protected RelNode visitChild(RelNode parent, int i, RelNode child) {
906  RelNode node = super.visitChild(parent, i, child);
907  return node.accept(callShuttle);
908  }
909  });
910 
911  return new RelRoot(node,
912  root.validatedRowType,
913  root.kind,
914  root.fields,
915  root.collation,
916  Collections.emptyList());
917  }
918 
919  private SqlNode parseSql(String sql, final boolean legacy_syntax, Planner planner)
920  throws SqlParseException {
921  SqlNode parseR = null;
922  try {
923  parseR = planner.parse(sql);
924  MAPDLOGGER.debug(" node is \n" + parseR.toString());
925  } catch (SqlParseException ex) {
926  MAPDLOGGER.error("failed to parse SQL '" + sql + "' \n" + ex.toString());
927  throw ex;
928  }
929 
930  if (!legacy_syntax) {
931  return parseR;
932  }
933 
934  RelDataTypeFactory typeFactory = planner.getTypeFactory();
935  SqlSelect select_node = null;
936  if (parseR instanceof SqlSelect) {
937  select_node = (SqlSelect) parseR;
938  desugar(select_node, typeFactory);
939  } else if (parseR instanceof SqlOrderBy) {
940  SqlOrderBy order_by_node = (SqlOrderBy) parseR;
941  if (order_by_node.query instanceof SqlSelect) {
942  select_node = (SqlSelect) order_by_node.query;
943  SqlOrderBy new_order_by_node = desugar(select_node, order_by_node, typeFactory);
944  if (new_order_by_node != null) {
945  return new_order_by_node;
946  }
947  } else if (order_by_node.query instanceof SqlWith) {
948  SqlWith old_with_node = (SqlWith) order_by_node.query;
949  if (old_with_node.body instanceof SqlSelect) {
950  select_node = (SqlSelect) old_with_node.body;
951  desugar(select_node, typeFactory);
952  }
953  }
954  } else if (parseR instanceof SqlWith) {
955  SqlWith old_with_node = (SqlWith) parseR;
956  if (old_with_node.body instanceof SqlSelect) {
957  select_node = (SqlSelect) old_with_node.body;
958  desugar(select_node, typeFactory);
959  }
960  }
961  return parseR;
962  }
963 
964  private void desugar(SqlSelect select_node, RelDataTypeFactory typeFactory) {
965  desugar(select_node, null, typeFactory);
966  }
967 
968  private SqlNode expandCase(SqlCase old_case_node, RelDataTypeFactory typeFactory) {
969  SqlNodeList newWhenList =
970  new SqlNodeList(old_case_node.getWhenOperands().getParserPosition());
971  SqlNodeList newThenList =
972  new SqlNodeList(old_case_node.getThenOperands().getParserPosition());
973  java.util.Map<String, SqlNode> id_to_expr = new java.util.HashMap<String, SqlNode>();
974  for (SqlNode node : old_case_node.getWhenOperands()) {
975  SqlNode newCall = expand(node, id_to_expr, typeFactory);
976  if (null != newCall) {
977  newWhenList.add(newCall);
978  } else {
979  newWhenList.add(node);
980  }
981  }
982  for (SqlNode node : old_case_node.getThenOperands()) {
983  SqlNode newCall = expand(node, id_to_expr, typeFactory);
984  if (null != newCall) {
985  newThenList.add(newCall);
986  } else {
987  newThenList.add(node);
988  }
989  }
990  SqlNode new_else_operand = old_case_node.getElseOperand();
991  if (null != new_else_operand) {
992  SqlNode candidate_else_operand =
993  expand(old_case_node.getElseOperand(), id_to_expr, typeFactory);
994  if (null != candidate_else_operand) {
995  new_else_operand = candidate_else_operand;
996  }
997  }
998  SqlNode new_value_operand = old_case_node.getValueOperand();
999  if (null != new_value_operand) {
1000  SqlNode candidate_value_operand =
1001  expand(old_case_node.getValueOperand(), id_to_expr, typeFactory);
1002  if (null != candidate_value_operand) {
1003  new_value_operand = candidate_value_operand;
1004  }
1005  }
1006  SqlNode newCaseNode = SqlCase.createSwitched(old_case_node.getParserPosition(),
1007  new_value_operand,
1008  newWhenList,
1009  newThenList,
1010  new_else_operand);
1011  return newCaseNode;
1012  }
1013 
1014  private SqlOrderBy desugar(SqlSelect select_node,
1015  SqlOrderBy order_by_node,
1016  RelDataTypeFactory typeFactory) {
1017  MAPDLOGGER.debug("desugar: before: " + select_node.toString());
1018  desugarExpression(select_node.getFrom(), typeFactory);
1019  desugarExpression(select_node.getWhere(), typeFactory);
1020  SqlNodeList select_list = select_node.getSelectList();
1021  SqlNodeList new_select_list = new SqlNodeList(select_list.getParserPosition());
1022  java.util.Map<String, SqlNode> id_to_expr = new java.util.HashMap<String, SqlNode>();
1023  for (SqlNode proj : select_list) {
1024  if (!(proj instanceof SqlBasicCall)) {
1025  if (proj instanceof SqlCase) {
1026  new_select_list.add(expandCase((SqlCase) proj, typeFactory));
1027  } else {
1028  new_select_list.add(proj);
1029  }
1030  } else {
1031  assert proj instanceof SqlBasicCall;
1032  SqlBasicCall proj_call = (SqlBasicCall) proj;
1033  if (proj_call.operands.length > 0) {
1034  for (int i = 0; i < proj_call.operands.length; i++) {
1035  if (proj_call.operand(i) instanceof SqlCase) {
1036  SqlNode new_op = expandCase(proj_call.operand(i), typeFactory);
1037  proj_call.setOperand(i, new_op);
1038  }
1039  }
1040  }
1041  new_select_list.add(expand(proj_call, id_to_expr, typeFactory));
1042  }
1043  }
1044  select_node.setSelectList(new_select_list);
1045  SqlNodeList group_by_list = select_node.getGroup();
1046  if (group_by_list != null) {
1047  select_node.setGroupBy(expand(group_by_list, id_to_expr, typeFactory));
1048  }
1049  SqlNode having = select_node.getHaving();
1050  if (having != null) {
1051  expand(having, id_to_expr, typeFactory);
1052  }
1053  SqlOrderBy new_order_by_node = null;
1054  if (order_by_node != null && order_by_node.orderList != null
1055  && order_by_node.orderList.size() > 0) {
1056  SqlNodeList new_order_by_list =
1057  expand(order_by_node.orderList, id_to_expr, typeFactory);
1058  new_order_by_node = new SqlOrderBy(order_by_node.getParserPosition(),
1059  select_node,
1060  new_order_by_list,
1061  order_by_node.offset,
1062  order_by_node.fetch);
1063  }
1064 
1065  MAPDLOGGER.debug("desugar: after: " + select_node.toString());
1066  return new_order_by_node;
1067  }
1068 
1069  private void desugarExpression(SqlNode node, RelDataTypeFactory typeFactory) {
1070  if (node instanceof SqlSelect) {
1071  desugar((SqlSelect) node, typeFactory);
1072  return;
1073  }
1074  if (!(node instanceof SqlBasicCall)) {
1075  return;
1076  }
1077  SqlBasicCall basic_call = (SqlBasicCall) node;
1078  for (SqlNode operator : basic_call.getOperands()) {
1079  if (operator instanceof SqlOrderBy) {
1080  desugarExpression(((SqlOrderBy) operator).query, typeFactory);
1081  } else {
1082  desugarExpression(operator, typeFactory);
1083  }
1084  }
1085  }
1086 
1087  private SqlNode expand(final SqlNode node,
1088  final java.util.Map<String, SqlNode> id_to_expr,
1089  RelDataTypeFactory typeFactory) {
1090  MAPDLOGGER.debug("expand: " + node.toString());
1091  if (node instanceof SqlBasicCall) {
1092  SqlBasicCall node_call = (SqlBasicCall) node;
1093  SqlNode[] operands = node_call.getOperands();
1094  for (int i = 0; i < operands.length; ++i) {
1095  node_call.setOperand(i, expand(operands[i], id_to_expr, typeFactory));
1096  }
1097  SqlNode expanded_variance = expandVariance(node_call, typeFactory);
1098  if (expanded_variance != null) {
1099  return expanded_variance;
1100  }
1101  SqlNode expanded_covariance = expandCovariance(node_call, typeFactory);
1102  if (expanded_covariance != null) {
1103  return expanded_covariance;
1104  }
1105  SqlNode expanded_correlation = expandCorrelation(node_call, typeFactory);
1106  if (expanded_correlation != null) {
1107  return expanded_correlation;
1108  }
1109  }
1110  if (node instanceof SqlSelect) {
1111  SqlSelect select_node = (SqlSelect) node;
1112  desugar(select_node, typeFactory);
1113  }
1114  return node;
1115  }
1116 
1117  private SqlNodeList expand(final SqlNodeList group_by_list,
1118  final java.util.Map<String, SqlNode> id_to_expr,
1119  RelDataTypeFactory typeFactory) {
1120  SqlNodeList new_group_by_list = new SqlNodeList(new SqlParserPos(-1, -1));
1121  for (SqlNode group_by : group_by_list) {
1122  if (!(group_by instanceof SqlIdentifier)) {
1123  new_group_by_list.add(expand(group_by, id_to_expr, typeFactory));
1124  continue;
1125  }
1126  SqlIdentifier group_by_id = ((SqlIdentifier) group_by);
1127  if (id_to_expr.containsKey(group_by_id.toString())) {
1128  new_group_by_list.add(id_to_expr.get(group_by_id.toString()));
1129  } else {
1130  new_group_by_list.add(group_by);
1131  }
1132  }
1133  return new_group_by_list;
1134  }
1135 
1136  private SqlNode expandVariance(
1137  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1138  // Expand variance aggregates that are not supported natively
1139  if (proj_call.operandCount() != 1) {
1140  return null;
1141  }
1142  boolean biased;
1143  boolean sqrt;
1144  boolean flt;
1145  if (proj_call.getOperator().isName("STDDEV_POP", false)) {
1146  biased = true;
1147  sqrt = true;
1148  flt = false;
1149  } else if (proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_POP_FLOAT")) {
1150  biased = true;
1151  sqrt = true;
1152  flt = true;
1153  } else if (proj_call.getOperator().isName("STDDEV_SAMP", false)
1154  || proj_call.getOperator().getName().equalsIgnoreCase("STDDEV")) {
1155  biased = false;
1156  sqrt = true;
1157  flt = false;
1158  } else if (proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_SAMP_FLOAT")
1159  || proj_call.getOperator().getName().equalsIgnoreCase("STDDEV_FLOAT")) {
1160  biased = false;
1161  sqrt = true;
1162  flt = true;
1163  } else if (proj_call.getOperator().isName("VAR_POP", false)) {
1164  biased = true;
1165  sqrt = false;
1166  flt = false;
1167  } else if (proj_call.getOperator().getName().equalsIgnoreCase("VAR_POP_FLOAT")) {
1168  biased = true;
1169  sqrt = false;
1170  flt = true;
1171  } else if (proj_call.getOperator().isName("VAR_SAMP", false)
1172  || proj_call.getOperator().getName().equalsIgnoreCase("VARIANCE")) {
1173  biased = false;
1174  sqrt = false;
1175  flt = false;
1176  } else if (proj_call.getOperator().getName().equalsIgnoreCase("VAR_SAMP_FLOAT")
1177  || proj_call.getOperator().getName().equalsIgnoreCase("VARIANCE_FLOAT")) {
1178  biased = false;
1179  sqrt = false;
1180  flt = true;
1181  } else {
1182  return null;
1183  }
1184  final SqlNode operand = proj_call.operand(0);
1185  final SqlParserPos pos = proj_call.getParserPosition();
1186  SqlNode expanded_proj_call =
1187  expandVariance(pos, operand, biased, sqrt, flt, typeFactory);
1188  MAPDLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1189  MAPDLOGGER.debug("to : " + expanded_proj_call.toString());
1190  return expanded_proj_call;
1191  }
1192 
1193  private SqlNode expandVariance(final SqlParserPos pos,
1194  final SqlNode operand,
1195  boolean biased,
1196  boolean sqrt,
1197  boolean flt,
1198  RelDataTypeFactory typeFactory) {
1199  // stddev_pop(x) ==>
1200  // power(
1201  // (sum(x * x) - sum(x) * sum(x) / (case count(x) when 0 then NULL else count(x)
1202  // end)) / (case count(x) when 0 then NULL else count(x) end), .5)
1203  //
1204  // stddev_samp(x) ==>
1205  // power(
1206  // (sum(x * x) - sum(x) * sum(x) / (case count(x) when 0 then NULL else count(x)
1207  // )) / ((case count(x) when 1 then NULL else count(x) - 1 end)), .5)
1208  //
1209  // var_pop(x) ==>
1210  // (sum(x * x) - sum(x) * sum(x) / ((case count(x) when 0 then NULL else
1211  // count(x)
1212  // end))) / ((case count(x) when 0 then NULL else count(x) end))
1213  //
1214  // var_samp(x) ==>
1215  // (sum(x * x) - sum(x) * sum(x) / ((case count(x) when 0 then NULL else
1216  // count(x)
1217  // end))) / ((case count(x) when 1 then NULL else count(x) - 1 end))
1218  //
1219  final SqlNode arg = SqlStdOperatorTable.CAST.createCall(pos,
1220  operand,
1221  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1222  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1223  final SqlNode argSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, arg, arg);
1224  final SqlNode sumArgSquared = SqlStdOperatorTable.SUM.createCall(pos, argSquared);
1225  final SqlNode sum = SqlStdOperatorTable.SUM.createCall(pos, arg);
1226  final SqlNode sumSquared = SqlStdOperatorTable.MULTIPLY.createCall(pos, sum, sum);
1227  final SqlNode count = SqlStdOperatorTable.COUNT.createCall(pos, arg);
1228  final SqlLiteral nul = SqlLiteral.createNull(pos);
1229  final SqlNumericLiteral zero = SqlLiteral.createExactNumeric("0", pos);
1230  final SqlNode countEqZero = SqlStdOperatorTable.EQUALS.createCall(pos, count, zero);
1231  SqlNodeList whenList = new SqlNodeList(pos);
1232  SqlNodeList thenList = new SqlNodeList(pos);
1233  whenList.add(countEqZero);
1234  thenList.add(nul);
1235  final SqlNode int_denominator = SqlStdOperatorTable.CASE.createCall(
1236  null, pos, null, whenList, thenList, count);
1237  final SqlNode denominator = SqlStdOperatorTable.CAST.createCall(pos,
1238  int_denominator,
1239  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1240  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1241  final SqlNode avgSumSquared =
1242  SqlStdOperatorTable.DIVIDE.createCall(pos, sumSquared, denominator);
1243  final SqlNode diff =
1244  SqlStdOperatorTable.MINUS.createCall(pos, sumArgSquared, avgSumSquared);
1245  final SqlNode denominator1;
1246  if (biased) {
1247  denominator1 = denominator;
1248  } else {
1249  final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
1250  final SqlNode countEqOne = SqlStdOperatorTable.EQUALS.createCall(pos, count, one);
1251  final SqlNode countMinusOne = SqlStdOperatorTable.MINUS.createCall(pos, count, one);
1252  SqlNodeList whenList1 = new SqlNodeList(pos);
1253  SqlNodeList thenList1 = new SqlNodeList(pos);
1254  whenList1.add(countEqOne);
1255  thenList1.add(nul);
1256  final SqlNode int_denominator1 = SqlStdOperatorTable.CASE.createCall(
1257  null, pos, null, whenList1, thenList1, countMinusOne);
1258  denominator1 = SqlStdOperatorTable.CAST.createCall(pos,
1259  int_denominator1,
1260  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1261  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1262  }
1263  final SqlNode div = SqlStdOperatorTable.DIVIDE.createCall(pos, diff, denominator1);
1264  SqlNode result = div;
1265  if (sqrt) {
1266  final SqlNumericLiteral half = SqlLiteral.createExactNumeric("0.5", pos);
1267  result = SqlStdOperatorTable.POWER.createCall(pos, div, half);
1268  }
1269  return SqlStdOperatorTable.CAST.createCall(pos,
1270  result,
1271  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1272  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1273  }
1274 
1275  private SqlNode expandCovariance(
1276  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1277  // Expand covariance aggregates
1278  if (proj_call.operandCount() != 2) {
1279  return null;
1280  }
1281  boolean pop;
1282  boolean flt;
1283  if (proj_call.getOperator().isName("COVAR_POP", false)) {
1284  pop = true;
1285  flt = false;
1286  } else if (proj_call.getOperator().isName("COVAR_SAMP", false)) {
1287  pop = false;
1288  flt = false;
1289  } else if (proj_call.getOperator().getName().equalsIgnoreCase("COVAR_POP_FLOAT")) {
1290  pop = true;
1291  flt = true;
1292  } else if (proj_call.getOperator().getName().equalsIgnoreCase("COVAR_SAMP_FLOAT")) {
1293  pop = false;
1294  flt = true;
1295  } else {
1296  return null;
1297  }
1298  final SqlNode operand0 = proj_call.operand(0);
1299  final SqlNode operand1 = proj_call.operand(1);
1300  final SqlParserPos pos = proj_call.getParserPosition();
1301  SqlNode expanded_proj_call =
1302  expandCovariance(pos, operand0, operand1, pop, flt, typeFactory);
1303  MAPDLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1304  MAPDLOGGER.debug("to : " + expanded_proj_call.toString());
1305  return expanded_proj_call;
1306  }
1307 
1308  private SqlNode expandCovariance(SqlParserPos pos,
1309  final SqlNode operand0,
1310  final SqlNode operand1,
1311  boolean pop,
1312  boolean flt,
1313  RelDataTypeFactory typeFactory) {
1314  // covar_pop(x, y) ==> avg(x * y) - avg(x) * avg(y)
1315  // covar_samp(x, y) ==> (sum(x * y) - sum(x) * avg(y))
1316  // ((case count(x) when 1 then NULL else count(x) - 1 end))
1317  final SqlNode arg0 = SqlStdOperatorTable.CAST.createCall(operand0.getParserPosition(),
1318  operand0,
1319  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1320  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1321  final SqlNode arg1 = SqlStdOperatorTable.CAST.createCall(operand1.getParserPosition(),
1322  operand1,
1323  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1324  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1325  final SqlNode mulArg = SqlStdOperatorTable.MULTIPLY.createCall(pos, arg0, arg1);
1326  final SqlNode avgArg1 = SqlStdOperatorTable.AVG.createCall(pos, arg1);
1327  if (pop) {
1328  final SqlNode avgMulArg = SqlStdOperatorTable.AVG.createCall(pos, mulArg);
1329  final SqlNode avgArg0 = SqlStdOperatorTable.AVG.createCall(pos, arg0);
1330  final SqlNode mulAvgAvg =
1331  SqlStdOperatorTable.MULTIPLY.createCall(pos, avgArg0, avgArg1);
1332  final SqlNode covarPop =
1333  SqlStdOperatorTable.MINUS.createCall(pos, avgMulArg, mulAvgAvg);
1334  return SqlStdOperatorTable.CAST.createCall(pos,
1335  covarPop,
1336  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1337  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1338  }
1339  final SqlNode sumMulArg = SqlStdOperatorTable.SUM.createCall(pos, mulArg);
1340  final SqlNode sumArg0 = SqlStdOperatorTable.SUM.createCall(pos, arg0);
1341  final SqlNode mulSumAvg =
1342  SqlStdOperatorTable.MULTIPLY.createCall(pos, sumArg0, avgArg1);
1343  final SqlNode sub = SqlStdOperatorTable.MINUS.createCall(pos, sumMulArg, mulSumAvg);
1344  final SqlNode count = SqlStdOperatorTable.COUNT.createCall(pos, operand0);
1345  final SqlNumericLiteral one = SqlLiteral.createExactNumeric("1", pos);
1346  final SqlNode countEqOne = SqlStdOperatorTable.EQUALS.createCall(pos, count, one);
1347  final SqlNode countMinusOne = SqlStdOperatorTable.MINUS.createCall(pos, count, one);
1348  final SqlLiteral nul = SqlLiteral.createNull(pos);
1349  SqlNodeList whenList1 = new SqlNodeList(pos);
1350  SqlNodeList thenList1 = new SqlNodeList(pos);
1351  whenList1.add(countEqOne);
1352  thenList1.add(nul);
1353  final SqlNode int_denominator = SqlStdOperatorTable.CASE.createCall(
1354  null, pos, null, whenList1, thenList1, countMinusOne);
1355  final SqlNode denominator = SqlStdOperatorTable.CAST.createCall(pos,
1356  int_denominator,
1357  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1358  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1359  final SqlNode covarSamp =
1360  SqlStdOperatorTable.DIVIDE.createCall(pos, sub, denominator);
1361  return SqlStdOperatorTable.CAST.createCall(pos,
1362  covarSamp,
1363  SqlTypeUtil.convertTypeToSpec(typeFactory.createSqlType(
1364  flt ? SqlTypeName.FLOAT : SqlTypeName.DOUBLE)));
1365  }
1366 
1367  private SqlNode expandCorrelation(
1368  final SqlBasicCall proj_call, RelDataTypeFactory typeFactory) {
1369  // Expand correlation coefficient
1370  if (proj_call.operandCount() != 2) {
1371  return null;
1372  }
1373  boolean flt;
1374  if (proj_call.getOperator().isName("CORR", false)
1375  || proj_call.getOperator().getName().equalsIgnoreCase("CORRELATION")) {
1376  // expand correlation coefficient
1377  flt = false;
1378  } else if (proj_call.getOperator().getName().equalsIgnoreCase("CORR_FLOAT")
1379  || proj_call.getOperator().getName().equalsIgnoreCase("CORRELATION_FLOAT")) {
1380  // expand correlation coefficient
1381  flt = true;
1382  } else {
1383  return null;
1384  }
1385  // corr(x, y) ==> (avg(x * y) - avg(x) * avg(y)) / (stddev_pop(x) *
1386  // stddev_pop(y))
1387  // ==> covar_pop(x, y) / (stddev_pop(x) * stddev_pop(y))
1388  final SqlNode operand0 = proj_call.operand(0);
1389  final SqlNode operand1 = proj_call.operand(1);
1390  final SqlParserPos pos = proj_call.getParserPosition();
1391  SqlNode covariance =
1392  expandCovariance(pos, operand0, operand1, true, flt, typeFactory);
1393  SqlNode stddev0 = expandVariance(pos, operand0, true, true, flt, typeFactory);
1394  SqlNode stddev1 = expandVariance(pos, operand1, true, true, flt, typeFactory);
1395  final SqlNode mulStddev =
1396  SqlStdOperatorTable.MULTIPLY.createCall(pos, stddev0, stddev1);
1397  final SqlNumericLiteral zero = SqlLiteral.createExactNumeric("0.0", pos);
1398  final SqlNode mulStddevEqZero =
1399  SqlStdOperatorTable.EQUALS.createCall(pos, mulStddev, zero);
1400  final SqlLiteral nul = SqlLiteral.createNull(pos);
1401  SqlNodeList whenList1 = new SqlNodeList(pos);
1402  SqlNodeList thenList1 = new SqlNodeList(pos);
1403  whenList1.add(mulStddevEqZero);
1404  thenList1.add(nul);
1405  final SqlNode denominator = SqlStdOperatorTable.CASE.createCall(
1406  null, pos, null, whenList1, thenList1, mulStddev);
1407  final SqlNode expanded_proj_call =
1408  SqlStdOperatorTable.DIVIDE.createCall(pos, covariance, denominator);
1409  MAPDLOGGER.debug("Expanded select_list SqlCall: " + proj_call.toString());
1410  MAPDLOGGER.debug("to : " + expanded_proj_call.toString());
1411  return expanded_proj_call;
1412  }
1413 
1414  public SqlIdentifierCapturer captureIdentifiers(String sql, boolean legacy_syntax)
1415  throws SqlParseException {
1416  try {
1417  Planner planner = getPlanner();
1418  SqlNode node = parseSql(sql, legacy_syntax, planner);
1419  return captureIdentifiers(node);
1420  } catch (Exception | Error e) {
1421  MAPDLOGGER.error("Error parsing sql: " + sql, e);
1422  return new SqlIdentifierCapturer();
1423  }
1424  }
1425 
1426  public SqlIdentifierCapturer captureIdentifiers(SqlNode node) throws SqlParseException {
1427  try {
1429  capturer.scan(node);
1430  return capturer;
1431  } catch (Exception | Error e) {
1432  MAPDLOGGER.error("Error parsing sql: " + node, e);
1433  return new SqlIdentifierCapturer();
1434  }
1435  }
1436 
1437  public int getCallCount() {
1438  return callCount;
1439  }
1440 
1441  public void updateMetaData(String schema, String table) {
1442  MAPDLOGGER.debug("schema :" + schema + " table :" + table);
1443  MapDSchema mapd =
1444  new MapDSchema(dataDir, this, mapdPort, null, sock_transport_properties);
1445  mapd.updateMetaData(schema, table);
1446  }
1447 
1448  protected RelDataTypeSystem createTypeSystem() {
1449  final MapDTypeSystem typeSystem = new MapDTypeSystem();
1450  return typeSystem;
1451  }
1452 
1454  extends SqlBasicVisitor<Void> {
1455  @Override
1456  public Void visit(SqlCall call) {
1457  if (call instanceof SqlSelect) {
1458  SqlSelect selectNode = (SqlSelect) call;
1459  String targetString = targetExpression.toString();
1460  for (SqlNode listedNode : selectNode.getSelectList()) {
1461  if (listedNode.toString().contains(targetString)) {
1462  throw Util.FoundOne.NULL;
1463  }
1464  }
1465  }
1466  return super.visit(call);
1467  }
1468 
1469  boolean containsExpression(SqlNode node, SqlNode targetExpression) {
1470  try {
1471  this.targetExpression = targetExpression;
1472  node.accept(this);
1473  return false;
1474  } catch (Util.FoundOne e) {
1475  return true;
1476  }
1477  }
1478 
1480  }
1481 
1483  extends SqlBasicVisitor<Void> {
1484  @Override
1485  public Void visit(SqlCall call) {
1486  if (call instanceof SqlBasicCall) {
1487  SqlBasicCall basicCall = (SqlBasicCall) call;
1488  if (basicCall.getKind() == SqlKind.OR) {
1489  String targetString = targetExpression.toString();
1490  for (SqlNode listedOperand : basicCall.operands) {
1491  if (listedOperand.toString().contains(targetString)) {
1492  throw Util.FoundOne.NULL;
1493  }
1494  }
1495  }
1496  }
1497  return super.visit(call);
1498  }
1499 
1500  boolean containsExpression(SqlNode node, SqlNode targetExpression) {
1501  try {
1502  this.targetExpression = targetExpression;
1503  node.accept(this);
1504  return false;
1505  } catch (Util.FoundOne e) {
1506  return true;
1507  }
1508  }
1509 
1511  }
1512 
1513  // this visitor checks whether a parse tree contains at least one
1514  // specific SQL operator we have an interest in
1515  // (do not count the accurate # operators we found)
1516  private static class FindSqlOperator extends SqlBasicVisitor<Void> {
1517  @Override
1518  public Void visit(SqlCall call) {
1519  if (call instanceof SqlBasicCall) {
1520  SqlBasicCall basicCall = (SqlBasicCall) call;
1521  if (basicCall.getKind().equals(targetKind)) {
1522  throw Util.FoundOne.NULL;
1523  }
1524  }
1525  return super.visit(call);
1526  }
1527 
1528  boolean containsSqlOperator(SqlNode node, SqlKind operatorKind) {
1529  try {
1530  targetKind = operatorKind;
1531  node.accept(this);
1532  return false;
1533  } catch (Util.FoundOne e) {
1534  return true;
1535  }
1536  }
1537  private SqlKind targetKind;
1538  }
1539 }
#define DELETE
SqlNode expand(final SqlNode node, final java.util.Map< String, SqlNode > id_to_expr, RelDataTypeFactory typeFactory)
static final EnumSet< SqlKind > IN
Definition: MapDParser.java:91
JoinType
Definition: sqldefs.h:108
static final EnumSet< SqlKind > SCALAR
Definition: MapDParser.java:86
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)
boolean containsExpression(SqlNode node, SqlNode targetExpression)
String optimizeRAQuery(String query, final MapDParserOptions parserOptions)
RelRoot queryToRelNode(final String sql, final MapDParserOptions parserOptions)
#define EXISTS
Pair< String, SqlIdentifierCapturer > process(String sql, final MapDParserOptions parserOptions)
boolean containsExpression(SqlNode node, SqlNode targetExpression)
MapDPlanner getPlanner(final boolean allowSubQueryExpansion, final boolean isWatchdogEnabled)
#define DOUBLE
string name
Definition: setup.in.py:72
std::string join(T const &container, std::string const &delim)
tuple root
Definition: setup.in.py:14
SqlNode expandCase(SqlCase old_case_node, RelDataTypeFactory typeFactory)
static final EnumSet< SqlKind > ARRAY_VALUE
Definition: MapDParser.java:92
final Supplier< MapDSqlOperatorTable > mapDSqlOperatorTable
Definition: MapDParser.java:97
#define IN
SqlNode expandVariance(final SqlParserPos pos, final SqlNode operand, boolean biased, boolean sqrt, boolean flt, RelDataTypeFactory typeFactory)
boolean containsSqlOperator(SqlNode node, SqlKind operatorKind)
SqlIdentifierCapturer getAccessedObjects()
Definition: MapDView.java:66
void updateMetaData(String schema, String table)
MapDParser(String dataDir, final Supplier< MapDSqlOperatorTable > mapDSqlOperatorTable, int mapdPort, SockTransportProperties skT)
String processSql(String sql, final MapDParserOptions parserOptions)
SqlIdentifierCapturer captureIdentifiers(SqlNode node)
SqlSelect rewriteSimpleUpdateAsSelect(final SqlUpdate update)
SockTransportProperties sock_transport_properties
static final SqlArrayValueConstructorAllowingEmpty ARRAY_VALUE_CONSTRUCTOR
HashSet< ImmutableList< String > > resolveSelectIdentifiers(SqlIdentifierCapturer capturer)
int count
static final EnumSet< SqlKind > EXISTS
Definition: MapDParser.java:88
SqlNode parseSql(String sql, final boolean legacy_syntax, Planner planner)
static final EnumSet< SqlKind > UPDATE
Definition: MapDParser.java:90
SqlIdentifierCapturer captureIdentifiers(String sql, boolean legacy_syntax)
static Map< String, Boolean > SubqueryCorrMemo
static final Logger MAPDLOGGER
Definition: MapDParser.java:95
RelRoot convertSqlToRelNode(final SqlNode sqlNode, final MapDPlanner mapDPlanner, final MapDParserOptions parserOptions)
String getTableName(SqlNode node)
boolean isCorrelated(SqlNode expression)
Table getTable(String string)
Definition: MapDSchema.java:60
String processSql(final SqlNode sqlNode, final MapDParserOptions parserOptions)
void setUser(MapDUser mapdUser)
SqlNodeList expand(final SqlNodeList group_by_list, final java.util.Map< String, SqlNode > id_to_expr, RelDataTypeFactory typeFactory)
SqlNode expandCovariance(SqlParserPos pos, final SqlNode operand0, final SqlNode operand1, boolean pop, boolean flt, RelDataTypeFactory typeFactory)
void desugar(SqlSelect select_node, RelDataTypeFactory typeFactory)
#define UPDATE
#define OR
#define DEFAULT
char * f
SqlOrderBy desugar(SqlSelect select_node, SqlOrderBy order_by_node, RelDataTypeFactory typeFactory)
static final ThreadLocal< MapDParser > CURRENT_PARSER
Definition: MapDParser.java:85
SqlNode expandVariance(final SqlBasicCall proj_call, RelDataTypeFactory typeFactory)
constexpr double n
Definition: Utm.h:38
#define AND
MapDPlanner.CompletionResult getCompletionHints(String sql, int cursor, List< String > visible_tables)
static final EnumSet< SqlKind > DELETE
Definition: MapDParser.java:89
if(yyssp >=yyss+yystacksize-1)
static final Context MAPD_CONNECTION_CONTEXT
RelDataTypeSystem createTypeSystem()
#define SELECT
LogicalTableModify getDummyUpdate(SqlUpdate update)
RelRoot rewriteUpdateAsSelect(SqlUpdate update, MapDParserOptions parserOptions)