METAMODEL-1173: Fixed issue with evaluating ScalarFunctions in WHERE closes apache/metamodel#171
Project: http://git-wip-us.apache.org/repos/asf/metamodel/repo Commit: http://git-wip-us.apache.org/repos/asf/metamodel/commit/39947f55 Tree: http://git-wip-us.apache.org/repos/asf/metamodel/tree/39947f55 Diff: http://git-wip-us.apache.org/repos/asf/metamodel/diff/39947f55 Branch: refs/heads/master Commit: 39947f559078f3a633cd6b0df6e52744a9929737 Parents: 6fc258f Author: Kasper Sørensen <[email protected]> Authored: Fri Dec 8 22:58:03 2017 +0100 Committer: Dennis Du Krøger <[email protected]> Committed: Fri Dec 8 23:00:33 2017 +0100 ---------------------------------------------------------------------- .travis.yml | 2 + CHANGES.md | 3 +- .../org/apache/metamodel/MetaModelHelper.java | 56 ++++- .../metamodel/QueryPostprocessDataContext.java | 24 +- .../apache/metamodel/data/FilteredDataSet.java | 85 +++---- .../metamodel/data/SimpleDataSetHeader.java | 6 - .../org/apache/metamodel/query/FilterItem.java | 12 +- .../apache/metamodel/query/FunctionType.java | 2 + .../metamodel/query/FunctionTypeFactory.java | 7 + .../metamodel/query/MapValueFunction.java | 4 +- .../org/apache/metamodel/query/SelectItem.java | 93 ++++---- .../metamodel/query/SubstringFunction.java | 116 +++++++++ .../metamodel/query/ToStringFunction.java | 16 ++ .../query/parser/SelectItemParser.java | 27 ++- .../apache/metamodel/util/EqualsBuilder.java | 31 ++- .../apache/metamodel/MetaModelHelperTest.java | 90 +++++-- .../QueryPostprocessDataContextTest.java | 86 ++++++- .../apache/metamodel/query/SelectItemTest.java | 15 ++ .../metamodel/query/SubstringFunctionTest.java | 77 ++++++ .../metamodel/query/parser/QueryParserTest.java | 21 +- .../mongodb/mongo2/MongoDbDataContextTest.java | 2 +- .../mongodb/mongo3/MongoDbDataContext.java | 236 +++++++++++-------- .../mongodb/mongo3/MongoDbDeleteBuilder.java | 6 +- .../mongodb/mongo3/MongoDbDataContextTest.java | 190 ++++++++++----- 24 files changed, 870 insertions(+), 337 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/.travis.yml ---------------------------------------------------------------------- diff --git a/.travis.yml b/.travis.yml index 33b571c..1b1e342 100644 --- a/.travis.yml +++ b/.travis.yml @@ -4,6 +4,8 @@ jdk: - oraclejdk8 before_install: + # copy integration test config + - cp travis-metamodel-integrationtest-configuration.properties /home/travis/metamodel-integrationtest-configuration.properties # install Neo4j locally: - wget dist.neo4j.org/neo4j-community-2.2.3-unix.tar.gz - tar -xzf neo4j-community-2.2.3-unix.tar.gz http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/CHANGES.md ---------------------------------------------------------------------- diff --git a/CHANGES.md b/CHANGES.md index 3fd8879..f442440 100644 --- a/CHANGES.md +++ b/CHANGES.md @@ -1,4 +1,5 @@ - * [METAMODEL-1169] - Fixed issue with SQL Server milliseconds precision in WHERE + * [METAMODEL-1169] - Fixed issue with SQL Server milliseconds precision in WHERE. + * [METAMODEL-1173] - Fixed parsing and handling of scalar functions in WHERE clause. * [METAMODEL-1171] - Fixed parsing of query operators with DATE, TIME, TIMESTAMP prefix to operand date/time values. ### Apache MetaModel 5.0 http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/MetaModelHelper.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/MetaModelHelper.java b/core/src/main/java/org/apache/metamodel/MetaModelHelper.java index 2b547da..59900f9 100644 --- a/core/src/main/java/org/apache/metamodel/MetaModelHelper.java +++ b/core/src/main/java/org/apache/metamodel/MetaModelHelper.java @@ -32,6 +32,7 @@ import java.util.Map.Entry; import java.util.Set; import java.util.stream.Collectors; import java.util.stream.Stream; +import java.util.stream.StreamSupport; import org.apache.metamodel.data.CachingDataSetHeader; import org.apache.metamodel.data.DataSet; @@ -40,7 +41,6 @@ import org.apache.metamodel.data.DefaultRow; import org.apache.metamodel.data.EmptyDataSet; import org.apache.metamodel.data.FilteredDataSet; import org.apache.metamodel.data.FirstRowDataSet; -import org.apache.metamodel.data.IRowFilter; import org.apache.metamodel.data.InMemoryDataSet; import org.apache.metamodel.data.MaxRowsDataSet; import org.apache.metamodel.data.Row; @@ -270,20 +270,40 @@ public final class MetaModelHelper { } public static DataSet getFiltered(DataSet dataSet, Iterable<FilterItem> filterItems) { - List<IRowFilter> filters = CollectionUtils.map(filterItems, filterItem -> { - return filterItem; - }); - if (filters.isEmpty()) { - return dataSet; - } - - return new FilteredDataSet(dataSet, filters.toArray(new IRowFilter[filters.size()])); + final FilterItem[] filterItemsArray = + StreamSupport.stream(filterItems.spliterator(), false).toArray(FilterItem[]::new); + return getFiltered(dataSet, filterItemsArray); } public static DataSet getFiltered(DataSet dataSet, FilterItem... filterItems) { + if (filterItems == null || filterItems.length == 0) { + return dataSet; + } return getFiltered(dataSet, Arrays.asList(filterItems)); } + public static DataSet getFiltered(DataSet dataSet, Collection<FilterItem> filterItems) { + if (filterItems == null || filterItems.isEmpty()) { + return dataSet; + } + final List<SelectItem> selectItemsOnOutput = dataSet.getSelectItems(); + final Iterable<SelectItem> selectItems = + filterItems.stream().map(f -> f.getSelectItem()).filter(s -> s != null)::iterator; + final List<SelectItem> scalarFunctionSelectItems = + getUnmaterializedScalarFunctionSelectItems(selectItems, dataSet); + final boolean calculateScalarFunctions = !scalarFunctionSelectItems.isEmpty(); + if (calculateScalarFunctions) { + // scalar functions are needed in evaluation of the filters + dataSet = new ScalarFunctionDataSet(scalarFunctionSelectItems, dataSet); + } + final FilteredDataSet filteredDataSet = new FilteredDataSet(dataSet, filterItems); + if (calculateScalarFunctions) { + return getSelection(selectItemsOnOutput, filteredDataSet); + } else { + return filteredDataSet; + } + } + public static DataSet getSelection(final List<SelectItem> selectItems, final DataSet dataSet) { final List<SelectItem> dataSetSelectItems = dataSet.getSelectItems(); @@ -317,7 +337,8 @@ public final class MetaModelHelper { return getSelection(Arrays.asList(selectItems), dataSet); } - public static DataSet getGrouped(List<SelectItem> selectItems, DataSet dataSet, Collection<GroupByItem> groupByItems) { + public static DataSet getGrouped(List<SelectItem> selectItems, DataSet dataSet, + Collection<GroupByItem> groupByItems) { DataSet result = dataSet; if (groupByItems != null && groupByItems.size() > 0) { Map<Row, Map<SelectItem, List<Object>>> uniqueRows = new HashMap<Row, Map<SelectItem, List<Object>>>(); @@ -524,8 +545,21 @@ public final class MetaModelHelper { } public static List<SelectItem> getScalarFunctionSelectItems(Iterable<SelectItem> selectItems) { + return getUnmaterializedScalarFunctionSelectItems(selectItems, null); + } + + /** + * Gets select items with scalar functions that haven't already been materialized in a data set. + * + * @param selectItems + * @param dataSetWithMaterializedSelectItems a {@link DataSet} containing the already materialized select items + * @return + */ + public static List<SelectItem> getUnmaterializedScalarFunctionSelectItems(Iterable<SelectItem> selectItems, + DataSet dataSetWithMaterializedSelectItems) { return CollectionUtils.filter(selectItems, arg -> { - return arg.getScalarFunction() != null; + return arg.getScalarFunction() != null && (dataSetWithMaterializedSelectItems == null + || dataSetWithMaterializedSelectItems.indexOf(arg) == -1); }); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/QueryPostprocessDataContext.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/QueryPostprocessDataContext.java b/core/src/main/java/org/apache/metamodel/QueryPostprocessDataContext.java index c77479b..d6e287d 100644 --- a/core/src/main/java/org/apache/metamodel/QueryPostprocessDataContext.java +++ b/core/src/main/java/org/apache/metamodel/QueryPostprocessDataContext.java @@ -22,6 +22,7 @@ import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.HashMap; +import java.util.LinkedHashSet; import java.util.List; import java.util.Map; import java.util.stream.Collectors; @@ -157,7 +158,8 @@ public abstract class QueryPostprocessDataContext extends AbstractDataContext im if (whereItems.size() == 1) { final FilterItem whereItem = whereItems.get(0); final SelectItem selectItem = whereItem.getSelectItem(); - if (!whereItem.isCompoundFilter() && selectItem != null && selectItem.getColumn() != null) { + if (!whereItem.isCompoundFilter() && selectItem != null && !selectItem.hasFunction() + && selectItem.getColumn() != null) { final Column column = selectItem.getColumn(); if (column.isPrimaryKey() && OperatorType.EQUALS_TO.equals(whereItem.getOperator())) { logger.debug( @@ -284,7 +286,7 @@ public abstract class QueryPostprocessDataContext extends AbstractDataContext im // We need to materialize a single table final Table table = MetaModelHelper.resolveTable(fromItem); final List<SelectItem> selectItemsToMaterialize = new ArrayList<SelectItem>(); - + for (final SelectItem selectItem : selectItems) { final FromItem selectedFromItem = selectItem.getFromItem(); if (selectedFromItem != null) { @@ -358,6 +360,7 @@ public abstract class QueryPostprocessDataContext extends AbstractDataContext im if (dataSet == null) { throw new IllegalStateException("FromItem was not succesfully materialized: " + fromItem); } + return dataSet; } @@ -406,18 +409,15 @@ public abstract class QueryPostprocessDataContext extends AbstractDataContext im } private List<SelectItem> buildWorkingSelectItems(List<SelectItem> selectItems, List<FilterItem> whereItems) { - final List<SelectItem> primarySelectItems = new ArrayList<>(selectItems.size()); - for (SelectItem selectItem : selectItems) { - final ScalarFunction scalarFunction = selectItem.getScalarFunction(); - if (scalarFunction == null || isScalarFunctionMaterialized(scalarFunction)) { - primarySelectItems.add(selectItem); - } else { - final SelectItem copySelectItem = selectItem.replaceFunction(null); - primarySelectItems.add(copySelectItem); - } + if (whereItems == null || whereItems.isEmpty()) { + return selectItems; } final List<SelectItem> evaluatedSelectItems = MetaModelHelper.getEvaluatedSelectItems(whereItems); - return CollectionUtils.concat(true, primarySelectItems, evaluatedSelectItems); + + final LinkedHashSet<SelectItem> workingSelectItems = new LinkedHashSet<>(); + workingSelectItems.addAll(selectItems); + workingSelectItems.addAll(evaluatedSelectItems); + return new ArrayList<>(workingSelectItems); } /** http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/data/FilteredDataSet.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/data/FilteredDataSet.java b/core/src/main/java/org/apache/metamodel/data/FilteredDataSet.java index c322401..dc7ec05 100644 --- a/core/src/main/java/org/apache/metamodel/data/FilteredDataSet.java +++ b/core/src/main/java/org/apache/metamodel/data/FilteredDataSet.java @@ -18,54 +18,59 @@ */ package org.apache.metamodel.data; +import java.util.Collection; /** * Wraps another DataSet and transparently applies a set of filters to it. */ public final class FilteredDataSet extends AbstractDataSet implements WrappingDataSet { - private final DataSet _dataSet; - private final IRowFilter[] _filters; - private Row _row; + private final DataSet _dataSet; + private final IRowFilter[] _filters; + private Row _row; - public FilteredDataSet(DataSet dataSet, IRowFilter... filters) { - super(dataSet); - _dataSet = dataSet; - _filters = filters; - } + public FilteredDataSet(DataSet dataSet, Collection<? extends IRowFilter> filterItems) { + this(dataSet, filterItems.stream().toArray(IRowFilter[]::new)); + } - @Override - public void close() { - super.close(); - _dataSet.close(); - } - - @Override - public DataSet getWrappedDataSet() { - return _dataSet; - } + public FilteredDataSet(DataSet dataSet, IRowFilter... filters) { + super(dataSet); + _dataSet = dataSet; + _filters = filters; + } - @Override - public boolean next() { - boolean next = false; - while (_dataSet.next()) { - Row row = _dataSet.getRow(); - for (IRowFilter filter : _filters) { - next = filter.accept(row); - if (!next) { - break; - } - } - if (next) { - _row = row; - break; - } - } - return next; - } + @Override + public void close() { + super.close(); + _dataSet.close(); + } - @Override - public Row getRow() { - return _row; - } + @Override + public DataSet getWrappedDataSet() { + return _dataSet; + } + + @Override + public boolean next() { + boolean next = false; + while (_dataSet.next()) { + Row row = _dataSet.getRow(); + for (IRowFilter filter : _filters) { + next = filter.accept(row); + if (!next) { + break; + } + } + if (next) { + _row = row; + break; + } + } + return next; + } + + @Override + public Row getRow() { + return _row; + } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/data/SimpleDataSetHeader.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/data/SimpleDataSetHeader.java b/core/src/main/java/org/apache/metamodel/data/SimpleDataSetHeader.java index 5dc2ca9..3232bad 100644 --- a/core/src/main/java/org/apache/metamodel/data/SimpleDataSetHeader.java +++ b/core/src/main/java/org/apache/metamodel/data/SimpleDataSetHeader.java @@ -104,12 +104,6 @@ public class SimpleDataSetHeader implements DataSetHeader { } i++; } - - final boolean scalarFunctionQueried = item.getScalarFunction() != null; - if (scalarFunctionQueried) { - final SelectItem itemWithoutFunction = item.replaceFunction(null); - return indexOf(itemWithoutFunction); - } return -1; } http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/query/FilterItem.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/query/FilterItem.java b/core/src/main/java/org/apache/metamodel/query/FilterItem.java index 427844b..fe13250 100644 --- a/core/src/main/java/org/apache/metamodel/query/FilterItem.java +++ b/core/src/main/java/org/apache/metamodel/query/FilterItem.java @@ -252,7 +252,7 @@ public class FilterItem extends BaseObject implements QueryItem, Cloneable, IRow return _expression; } - StringBuilder sb = new StringBuilder(); + final StringBuilder sb = new StringBuilder(); if (_childItems == null) { sb.append(_selectItem.getSameQueryAlias(includeSchemaInColumnPaths)); @@ -268,7 +268,7 @@ public class FilterItem extends BaseObject implements QueryItem, Cloneable, IRow .getSameQueryAlias(includeSchemaInColumnPaths); sb.append(selectItemString); } else { - ColumnType columnType = _selectItem.getExpectedColumnType(); + final ColumnType columnType = _selectItem.getExpectedColumnType(); final String sqlValue = FormatHelper.formatSqlValue(columnType, operand); sb.append(sqlValue); } @@ -310,10 +310,10 @@ public class FilterItem extends BaseObject implements QueryItem, Cloneable, IRow if (_childItems == null) { // Evaluate a single constraint - Object selectItemValue = row.getValue(_selectItem); + final Object selectItemValue = row.getValue(_selectItem); Object operandValue = _operand; if (_operand instanceof SelectItem) { - SelectItem selectItem = (SelectItem) _operand; + final SelectItem selectItem = (SelectItem) _operand; operandValue = row.getValue(selectItem); } if (operandValue == null) { @@ -339,7 +339,7 @@ public class FilterItem extends BaseObject implements QueryItem, Cloneable, IRow if (_logicalOperator == LogicalOperator.AND) { // require all results to be true for (FilterItem item : _childItems) { - boolean result = item.evaluate(row); + final boolean result = item.evaluate(row); if (!result) { return false; } @@ -348,7 +348,7 @@ public class FilterItem extends BaseObject implements QueryItem, Cloneable, IRow } else { // require at least one result to be true for (FilterItem item : _childItems) { - boolean result = item.evaluate(row); + final boolean result = item.evaluate(row); if (result) { return true; } http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/query/FunctionType.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/query/FunctionType.java b/core/src/main/java/org/apache/metamodel/query/FunctionType.java index ecedc97..2dad077 100644 --- a/core/src/main/java/org/apache/metamodel/query/FunctionType.java +++ b/core/src/main/java/org/apache/metamodel/query/FunctionType.java @@ -41,6 +41,8 @@ public interface FunctionType extends Serializable { public static final ScalarFunction TO_NUMBER = new ToNumberFunction(); public static final ScalarFunction TO_DATE = new ToDateFunction(); public static final ScalarFunction TO_BOOLEAN = new ToBooleanFunction(); + public static final ScalarFunction SUBSTRING = SubstringFunction.createSqlStyle(); + public static final ScalarFunction JAVA_SUBSTRING = SubstringFunction.createJavaStyle(); public static final ScalarFunction MAP_VALUE = new MapValueFunction(); public ColumnType getExpectedColumnType(ColumnType type); http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/query/FunctionTypeFactory.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/query/FunctionTypeFactory.java b/core/src/main/java/org/apache/metamodel/query/FunctionTypeFactory.java index 95e25ce..1de7f86 100644 --- a/core/src/main/java/org/apache/metamodel/query/FunctionTypeFactory.java +++ b/core/src/main/java/org/apache/metamodel/query/FunctionTypeFactory.java @@ -67,6 +67,13 @@ public class FunctionTypeFactory { case "TO_DATE": case "DATE": return FunctionType.TO_DATE; + case "SUBSTRING": + case "SUBSTR": + case "SUB_STRING": + case "SUB_STR": + return FunctionType.SUBSTRING; + case "JAVA_SUBSTRING": + return FunctionType.JAVA_SUBSTRING; case "MAP_VALUE": return FunctionType.MAP_VALUE; default: http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/query/MapValueFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/query/MapValueFunction.java b/core/src/main/java/org/apache/metamodel/query/MapValueFunction.java index 099bcab..e017747 100644 --- a/core/src/main/java/org/apache/metamodel/query/MapValueFunction.java +++ b/core/src/main/java/org/apache/metamodel/query/MapValueFunction.java @@ -37,9 +37,9 @@ public final class MapValueFunction extends DefaultScalarFunction { if (parameters.length == 0) { throw new IllegalArgumentException("Expecting path parameter to MAP_VALUE function"); } - Object value = row.getValue(operandItem); + final Object value = row.getValue(operandItem); if (value instanceof Map) { - Map<?, ?> map = (Map<?, ?>) value; + final Map<?, ?> map = (Map<?, ?>) value; return CollectionUtils.find(map, (String) parameters[0]); } return null; http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/query/SelectItem.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/query/SelectItem.java b/core/src/main/java/org/apache/metamodel/query/SelectItem.java index fec900d..40ac3fc 100644 --- a/core/src/main/java/org/apache/metamodel/query/SelectItem.java +++ b/core/src/main/java/org/apache/metamodel/query/SelectItem.java @@ -83,7 +83,7 @@ public class SelectItem extends BaseObject implements QueryItem, Cloneable { _column = column; _fromItem = fromItem; _function = function; - _functionParameters = functionParameters; + _functionParameters = (functionParameters != null && functionParameters.length == 0) ? null : functionParameters; _expression = expression; _subQuerySelectItem = subQuerySelectItem; _alias = alias; @@ -351,21 +351,12 @@ public class SelectItem extends BaseObject implements QueryItem, Cloneable { return _alias; } else if (_column != null) { final StringBuilder sb = new StringBuilder(); - if (_function != null) { - if (_functionApproximationAllowed) { - sb.append(FUNCTION_APPROXIMATION_PREFIX); - } - sb.append(_function.getFunctionName()); - sb.append('('); - } if (includeQuotes) { sb.append(_column.getQuotedName()); } else { sb.append(_column.getName()); } - if (_function != null) { - sb.append(')'); - } + appendFunctionSql(sb); return sb.toString(); } else { logger.debug("Could not resolve a reasonable super-query alias for SelectItem: {}", toSql()); @@ -382,24 +373,18 @@ public class SelectItem extends BaseObject implements QueryItem, Cloneable { */ public String getSameQueryAlias(boolean includeSchemaInColumnPath) { if (_column != null) { - StringBuilder sb = new StringBuilder(); - String columnPrefix = getToStringColumnPrefix(includeSchemaInColumnPath); + final StringBuilder sb = new StringBuilder(); + final String columnPrefix = getToStringColumnPrefix(includeSchemaInColumnPath); sb.append(columnPrefix); sb.append(_column.getQuotedName()); - if (_function != null) { - if (_functionApproximationAllowed) { - sb.insert(0, FUNCTION_APPROXIMATION_PREFIX + _function.getFunctionName() + "("); - } else { - sb.insert(0, _function.getFunctionName() + "("); - } - sb.append(")"); - } + appendFunctionSql(sb); return sb.toString(); } - String alias = getAlias(); + final String alias = getAlias(); if (alias == null) { - alias = toStringNoAlias(includeSchemaInColumnPath).toString(); - logger.debug("Could not resolve a reasonable same-query alias for SelectItem: {}", toSql()); + final String result = toStringNoAlias(includeSchemaInColumnPath).toString(); + logger.debug("Could not resolve a reasonable same-query alias for SelectItem: {}", result); + return result; } return alias; } @@ -438,27 +423,32 @@ public class SelectItem extends BaseObject implements QueryItem, Cloneable { } sb.append(_subQuerySelectItem.getSuperQueryAlias()); } - if (_function != null) { - final StringBuilder functionBeginning = new StringBuilder(); - if (_functionApproximationAllowed) { - functionBeginning.append(FUNCTION_APPROXIMATION_PREFIX); - } + appendFunctionSql(sb); + return sb; + } - functionBeginning.append(_function.getFunctionName()); - functionBeginning.append('('); - final Object[] functionParameters = getFunctionParameters(); - if (functionParameters != null && functionParameters.length != 0) { - for (int i = 0; i < functionParameters.length; i++) { - functionBeginning.append('\''); - functionBeginning.append(functionParameters[i]); - functionBeginning.append('\''); - functionBeginning.append(','); - } + private void appendFunctionSql(StringBuilder sb) { + if (_function == null) { + return; + } + final StringBuilder functionBeginning = new StringBuilder(); + if (_functionApproximationAllowed) { + functionBeginning.append(FUNCTION_APPROXIMATION_PREFIX); + } + + functionBeginning.append(_function.getFunctionName()); + functionBeginning.append('('); + sb.insert(0, functionBeginning.toString()); + final Object[] functionParameters = getFunctionParameters(); + if (functionParameters != null && functionParameters.length != 0) { + for (int i = 0; i < functionParameters.length; i++) { + sb.append(','); + sb.append('\''); + sb.append(functionParameters[i]); + sb.append('\''); } - sb.insert(0, functionBeginning.toString()); - sb.append(")"); } - return sb; + sb.append(")"); } private String getToStringColumnPrefix(boolean includeSchemaInColumnPath) { @@ -509,7 +499,7 @@ public class SelectItem extends BaseObject implements QueryItem, Cloneable { return true; } - EqualsBuilder eb = new EqualsBuilder(); + final EqualsBuilder eb = new EqualsBuilder(); if (exactColumnCompare) { eb.append(this._column == that._column); eb.append(this._fromItem, that._fromItem); @@ -517,6 +507,7 @@ public class SelectItem extends BaseObject implements QueryItem, Cloneable { eb.append(this._column, that._column); } eb.append(this._function, that._function); + eb.appendArrays(this._functionParameters, that._functionParameters); eb.append(this._functionApproximationAllowed, that._functionApproximationAllowed); eb.append(this._expression, that._expression); if (_subQuerySelectItem != null) { @@ -528,13 +519,14 @@ public class SelectItem extends BaseObject implements QueryItem, Cloneable { } return eb.isEquals(); } - + @Override protected void decorateIdentity(List<Object> identifiers) { identifiers.add(_expression); identifiers.add(_alias); identifiers.add(_column); identifiers.add(_function); + identifiers.add(_functionParameters); identifiers.add(_functionApproximationAllowed); if (_fromItem == null && _column != null && _column.getTable() != null) { // add a FromItem representing the column's table - this makes equal @@ -587,7 +579,18 @@ public class SelectItem extends BaseObject implements QueryItem, Cloneable { * @return */ public SelectItem replaceFunction(FunctionType function) { - return new SelectItem(_column, _fromItem, function, _functionParameters, _expression, _subQuerySelectItem, + return replaceFunction(function, new Object[0]); + } + + /** + * Creates a copy of the {@link SelectItem}, with a different {@link FunctionType} and parameters. + * + * @param function + * @param functionParameters + * @return + */ + public SelectItem replaceFunction(FunctionType function, Object... functionParameters) { + return new SelectItem(_column, _fromItem, function, functionParameters, _expression, _subQuerySelectItem, _alias, _functionApproximationAllowed); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/query/SubstringFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/query/SubstringFunction.java b/core/src/main/java/org/apache/metamodel/query/SubstringFunction.java new file mode 100644 index 0000000..d26c3f9 --- /dev/null +++ b/core/src/main/java/org/apache/metamodel/query/SubstringFunction.java @@ -0,0 +1,116 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.metamodel.query; + +import org.apache.metamodel.data.Row; +import org.apache.metamodel.schema.ColumnType; +import org.apache.metamodel.util.NumberComparator; + +public class SubstringFunction implements ScalarFunction { + + private static final long serialVersionUID = 1L; + + public static SubstringFunction createSqlStyle() { + return new SubstringFunction(true, true); + } + + public static SubstringFunction createJavaStyle() { + return new SubstringFunction(false, false); + } + + private final boolean oneBased; + private final boolean secondParamIsCharCount; + + /** + * Creates a {@link SubstringFunction} + * + * @param oneBased true if the character index parameters are 1 based, like most SQL SUBSTRING functions (instead of + * 0 based, like Java). + * @param secondParamIsCharCount true if the (optional) second parameter is a "character count", like most SQL + * SUBSTRING functions (instead of end-index, like Java) + * + */ + public SubstringFunction(boolean oneBased, boolean secondParamIsCharCount) { + this.oneBased = oneBased; + this.secondParamIsCharCount = secondParamIsCharCount; + } + + @Override + public ColumnType getExpectedColumnType(ColumnType type) { + return ColumnType.STRING; + } + + @Override + public String getFunctionName() { + if (!oneBased && !secondParamIsCharCount) { + return "JAVA_SUBSTRING"; + } + return "SUBSTRING"; + } + + @Override + public Object evaluate(Row row, Object[] parameters, SelectItem operandItem) { + final String str = (String) FunctionType.TO_STRING.evaluate(row, null, operandItem); + if (str == null) { + return null; + } + final int numParameters = parameters == null ? 0 : parameters.length; + switch (numParameters) { + case 0: + return str; + case 1: + int begin = toInt(parameters[0]); + if (oneBased) { + begin--; + } + if (begin >= str.length()) { + return ""; + } + return str.substring(begin); + default: + int from = toInt(parameters[0]); + if (oneBased) { + from--; + } + int to = toInt(parameters[1]); + if (secondParamIsCharCount) { + to = from + to; + } else if (oneBased) { + to--; + } + + if (from >= str.length() || from > to) { + return ""; + } + if (to >= str.length()) { + return str.substring(from); + } + return str.substring(from, to); + } + } + + private int toInt(Object parameter) { + final Number number = NumberComparator.toNumber(parameter); + if (number == null) { + throw new IllegalArgumentException("Not a valid substring parameter: " + parameter); + } + return Math.max(0, number.intValue()); + } + +} http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/query/ToStringFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/query/ToStringFunction.java b/core/src/main/java/org/apache/metamodel/query/ToStringFunction.java index 97d3fa9..eb88e23 100644 --- a/core/src/main/java/org/apache/metamodel/query/ToStringFunction.java +++ b/core/src/main/java/org/apache/metamodel/query/ToStringFunction.java @@ -18,8 +18,14 @@ */ package org.apache.metamodel.query; +import java.io.Reader; +import java.sql.Clob; +import java.sql.SQLException; + +import org.apache.metamodel.MetaModelException; import org.apache.metamodel.data.Row; import org.apache.metamodel.schema.ColumnType; +import org.apache.metamodel.util.FileHelper; public class ToStringFunction extends DefaultScalarFunction { @@ -44,6 +50,16 @@ public class ToStringFunction extends DefaultScalarFunction { if (value == null || value instanceof String) { return value; } + if (value instanceof Clob) { + final Clob clob = (Clob) value; + try { + final Reader reader = clob.getCharacterStream(); + final String result = FileHelper.readAsString(reader); + return result; + } catch (SQLException e) { + throw new MetaModelException("Failed to read CLOB to String", e); + } + } return String.valueOf(value); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/query/parser/SelectItemParser.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/query/parser/SelectItemParser.java b/core/src/main/java/org/apache/metamodel/query/parser/SelectItemParser.java index d358d72..79d5dc3 100644 --- a/core/src/main/java/org/apache/metamodel/query/parser/SelectItemParser.java +++ b/core/src/main/java/org/apache/metamodel/query/parser/SelectItemParser.java @@ -18,9 +18,16 @@ */ package org.apache.metamodel.query.parser; +import java.util.Arrays; + import org.apache.metamodel.MetaModelException; import org.apache.metamodel.MetaModelHelper; -import org.apache.metamodel.query.*; +import org.apache.metamodel.query.CountAggregateFunction; +import org.apache.metamodel.query.FromItem; +import org.apache.metamodel.query.FunctionType; +import org.apache.metamodel.query.FunctionTypeFactory; +import org.apache.metamodel.query.Query; +import org.apache.metamodel.query.SelectItem; import org.apache.metamodel.schema.Column; public final class SelectItemParser implements QueryPartProcessor { @@ -108,6 +115,8 @@ public final class SelectItemParser implements QueryPartProcessor { final boolean functionApproximation; final FunctionType function; + Object[] functionParameters = null; + final int startParenthesis = expression.indexOf('('); if (startParenthesis > 0 && expression.endsWith(")")) { functionApproximation = (expression.startsWith(SelectItem.FUNCTION_APPROXIMATION_PREFIX)); @@ -120,10 +129,18 @@ public final class SelectItemParser implements QueryPartProcessor { final SelectItem selectItem = SelectItem.getCountAllItem(); selectItem.setFunctionApproximationAllowed(functionApproximation); return selectItem; + } else { + final String[] expressionSplit = expression.split(","); + if (expressionSplit.length > 1) { + // there are multiple parameters to the function + expression = expressionSplit[0].trim(); + functionParameters = Arrays.copyOfRange(expressionSplit, 1, expressionSplit.length, String[].class); + } } } } else { function = null; + functionParameters = null; functionApproximation = false; } @@ -170,19 +187,19 @@ public final class SelectItemParser implements QueryPartProcessor { column = fromItem.getTable().getColumnByName(part1); if (column != null) { final String part2 = columnName.substring(offset + 1); - return new SelectItem(new MapValueFunction(), new Object[] { part2 }, column, fromItem); + return new SelectItem(FunctionType.MAP_VALUE, new Object[] { part2 }, column, fromItem); } } if (column != null) { - final SelectItem selectItem = new SelectItem(function, column, fromItem); + final SelectItem selectItem = new SelectItem(function, functionParameters, column, fromItem); selectItem.setFunctionApproximationAllowed(functionApproximation); return selectItem; } } else if (fromItem.getSubQuery() != null) { final Query subQuery = fromItem.getSubQuery(); - final SelectItem subQuerySelectItem = new SelectItemParser(subQuery, _allowExpressionBasedSelectItems) - .findSelectItem(columnName); + final SelectItem subQuerySelectItem = + new SelectItemParser(subQuery, _allowExpressionBasedSelectItems).findSelectItem(columnName); if (subQuerySelectItem == null) { return null; } http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/main/java/org/apache/metamodel/util/EqualsBuilder.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/metamodel/util/EqualsBuilder.java b/core/src/main/java/org/apache/metamodel/util/EqualsBuilder.java index 656952f..b27d964 100644 --- a/core/src/main/java/org/apache/metamodel/util/EqualsBuilder.java +++ b/core/src/main/java/org/apache/metamodel/util/EqualsBuilder.java @@ -40,12 +40,31 @@ public final class EqualsBuilder { return this; } - public EqualsBuilder append(Object o1, Object o2) { - if (equals) { - equals = equals(o1, o2); - } - return this; - } + public EqualsBuilder append(Object o1, Object o2) { + if (equals) { + equals = equals(o1, o2); + } + return this; + } + + public EqualsBuilder appendArrays(Object[] o1, Object[] o2) { + if (equals) { + if (o1 == null) { + o1 = new Object[0]; + } + if (o2 == null) { + o2 = new Object[0]; + } + if (o1.length != o2.length) { + equals = false; + } else { + for (int i = 0; i < o1.length; i++) { + append(o1[i], o2[i]); + } + } + } + return this; + } public static boolean equals(final Object obj1, final Object obj2) { if (obj1 == obj2) { http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/test/java/org/apache/metamodel/MetaModelHelperTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/metamodel/MetaModelHelperTest.java b/core/src/test/java/org/apache/metamodel/MetaModelHelperTest.java index 7196bf4..a2425d3 100644 --- a/core/src/test/java/org/apache/metamodel/MetaModelHelperTest.java +++ b/core/src/test/java/org/apache/metamodel/MetaModelHelperTest.java @@ -33,6 +33,7 @@ import org.apache.metamodel.data.SimpleDataSetHeader; import org.apache.metamodel.data.SubSelectionDataSet; import org.apache.metamodel.query.FilterItem; import org.apache.metamodel.query.FromItem; +import org.apache.metamodel.query.FunctionType; import org.apache.metamodel.query.JoinType; import org.apache.metamodel.query.OperatorType; import org.apache.metamodel.query.OrderByItem; @@ -44,9 +45,11 @@ import org.apache.metamodel.schema.MutableColumn; import org.apache.metamodel.schema.MutableTable; import org.apache.metamodel.schema.Schema; import org.apache.metamodel.schema.Table; +import org.junit.Test; public class MetaModelHelperTest extends MetaModelTestCase { + @Test public void testLeftJoin() throws Exception { SelectItem si1 = new SelectItem(new MutableColumn("person_id", ColumnType.INTEGER)); SelectItem si2 = new SelectItem(new MutableColumn("person_name", ColumnType.VARCHAR)); @@ -67,8 +70,8 @@ public class MetaModelHelperTest extends MetaModelTestCase { data2.add(new Object[] { 2, "bad boy", "bb" }); data2.add(new Object[] { 4, "trying harder", "try" }); - DataSet ds1 = createDataSet(Lists.newArrayList(si1, si2, si3, si4 ), data1); - DataSet ds2 = createDataSet(Lists.newArrayList(si5, si6, si7 ), data2); + DataSet ds1 = createDataSet(Lists.newArrayList(si1, si2, si3, si4), data1); + DataSet ds2 = createDataSet(Lists.newArrayList(si5, si6, si7), data2); FilterItem[] onConditions = new FilterItem[1]; onConditions[0] = new FilterItem(si4, OperatorType.EQUALS_TO, si5); @@ -82,6 +85,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { assertEquals(5, objectArrays.size()); } + @Test public void testRightJoin() throws Exception { SelectItem si1 = new SelectItem(new MutableColumn("person_id", ColumnType.INTEGER)); SelectItem si2 = new SelectItem(new MutableColumn("person_name", ColumnType.VARCHAR)); @@ -101,8 +105,8 @@ public class MetaModelHelperTest extends MetaModelTestCase { data2.add(new Object[] { 2, "bad boy", "bb" }); data2.add(new Object[] { 4, "trying harder", "try" }); - DataSet ds1 = createDataSet(Lists.newArrayList(si1, si2, si3, si4 ), data1); - DataSet ds2 = createDataSet(Lists.newArrayList( si5, si6, si7 ), data2); + DataSet ds1 = createDataSet(Lists.newArrayList(si1, si2, si3, si4), data1); + DataSet ds2 = createDataSet(Lists.newArrayList(si5, si6, si7), data2); FilterItem[] onConditions = new FilterItem[1]; onConditions[0] = new FilterItem(si4, OperatorType.EQUALS_TO, si5); @@ -114,11 +118,11 @@ public class MetaModelHelperTest extends MetaModelTestCase { assertEquals(3, objectArrays.size()); } + @Test public void testSimpleCarthesianProduct() throws Exception { DataSet dataSet = MetaModelHelper.getCarthesianProduct(createDataSet1(), createDataSet2()); List<String> results = new ArrayList<String>(); - while (dataSet.next()) { results.add(dataSet.getRow().toString()); } @@ -130,9 +134,45 @@ public class MetaModelHelperTest extends MetaModelTestCase { assertTrue(results.contains("Row[values=[o, b]]")); assertTrue(results.contains("Row[values=[o, a]]")); assertTrue(results.contains("Row[values=[o, r]]")); + } + + @Test + public void testGetFilteredWithScalarFunctionInWhere() throws Exception { + final DataSet ds1 = createDataSet3(); // contains ["w00p",true] and ["yippie",false] + final SelectItem selectItem1 = ds1.getSelectItems().get(0); + final DataSet ds2 = MetaModelHelper.getFiltered(ds1, new FilterItem( + selectItem1.replaceFunction(FunctionType.SUBSTRING, 2, 2), OperatorType.EQUALS_TO, "00")); + final List<Object[]> resultRows = ds2.toObjectArrays(); + + assertEquals(1, resultRows.size()); + assertEquals("[w00p, true]", Arrays.toString(resultRows.get(0))); + } + + @Test + public void testGetSelectionWithScalarFunctionInSelectItem() throws Exception { + final DataSet ds1 = createDataSet3(); // contains ["w00p",true] and ["yippie",false] + final SelectItem selectItem1 = ds1.getSelectItems().get(0); + final DataSet ds2 = MetaModelHelper.getSelection(new SelectItem[] { selectItem1.replaceFunction(FunctionType.SUBSTRING, 2, 2) }, ds1); + final List<Object[]> resultRows = ds2.toObjectArrays(); + + assertEquals(2, resultRows.size()); + assertEquals("[00]", Arrays.toString(resultRows.get(0))); + assertEquals("[ip]", Arrays.toString(resultRows.get(1))); + } + @Test + public void testGetSelectionWithScalarFunctionAndNonFunctionInSelectItem() throws Exception { + final DataSet ds1 = createDataSet3(); // contains ["w00p",true] and ["yippie",false] + final SelectItem selectItem1 = ds1.getSelectItems().get(0); + final DataSet ds2 = MetaModelHelper.getSelection(new SelectItem[] { selectItem1, selectItem1.replaceFunction(FunctionType.SUBSTRING, 2, 2) }, ds1); + final List<Object[]> resultRows = ds2.toObjectArrays(); + + assertEquals(2, resultRows.size()); + assertEquals("[w00p, 00]", Arrays.toString(resultRows.get(0))); + assertEquals("[yippie, ip]", Arrays.toString(resultRows.get(1))); } + @Test public void testTripleCarthesianProduct() throws Exception { DataSet dataSet = MetaModelHelper.getCarthesianProduct(createDataSet1(), createDataSet2(), createDataSet3()); assertEquals(4, dataSet.getSelectItems().size()); @@ -142,6 +182,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { assertFalse(dataSet.next()); } + @Test public void testTripleCarthesianProductWithWhereItems() throws Exception { DataSet ds1 = createDataSet1(); DataSet ds2 = createDataSet2(); @@ -156,6 +197,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { assertFalse(dataSet.next()); } + @Test public void testGetCarthesianProductNoRows() throws Exception { DataSet dataSet = MetaModelHelper.getCarthesianProduct(createDataSet4(), createDataSet2(), createDataSet3()); assertEquals(4, dataSet.getSelectItems().size()); @@ -170,6 +212,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { assertFalse(dataSet.next()); } + @Test public void testGetOrdered() throws Exception { DataSet dataSet = createDataSet3(); List<OrderByItem> orderByItems = new ArrayList<OrderByItem>(); @@ -189,8 +232,8 @@ public class MetaModelHelperTest extends MetaModelTestCase { data1.add(new Object[] { "o" }); data1.add(new Object[] { "o" }); - DataSet dataSet1 = createDataSet( - Lists.newArrayList( new SelectItem(new MutableColumn("foo", ColumnType.VARCHAR)) ), data1); + DataSet dataSet1 = + createDataSet(Lists.newArrayList(new SelectItem(new MutableColumn("foo", ColumnType.VARCHAR))), data1); return dataSet1; } @@ -200,7 +243,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { data2.add(new Object[] { "b" }); data2.add(new Object[] { "a" }); data2.add(new Object[] { "r" }); - DataSet dataSet2 = createDataSet(Lists.newArrayList(new SelectItem("bar", "bar") ), data2); + DataSet dataSet2 = createDataSet(Lists.newArrayList(new SelectItem("bar", "bar")), data2); return dataSet2; } @@ -209,15 +252,15 @@ public class MetaModelHelperTest extends MetaModelTestCase { data3.add(new Object[] { "w00p", true }); data3.add(new Object[] { "yippie", false }); - DataSet dataSet3 = createDataSet(Lists.newArrayList(new SelectItem("expression", "e"), - new SelectItem("webish?", "w") ), data3); + DataSet dataSet3 = createDataSet( + Lists.newArrayList(new SelectItem("expression", "e"), new SelectItem("webish?", "w")), data3); return dataSet3; } private DataSet createDataSet4() { List<Object[]> data4 = new ArrayList<Object[]>(); - DataSet dataSet4 = createDataSet(Lists.newArrayList(new SelectItem("abc", "abc") ), data4); + DataSet dataSet4 = createDataSet(Lists.newArrayList(new SelectItem("abc", "abc")), data4); return dataSet4; } @@ -234,9 +277,9 @@ public class MetaModelHelperTest extends MetaModelTestCase { data5.add(new Object[] { i, "Person_" + i, bigDataSetSize - (i + 1) }); } - DataSet dataSet5 = createDataSet(Lists.newArrayList( new SelectItem(new MutableColumn("nr", ColumnType.BIGINT)), - new SelectItem(new MutableColumn("name", ColumnType.STRING)), new SelectItem(new MutableColumn("dnr", - ColumnType.BIGINT)) ), data5); + DataSet dataSet5 = createDataSet(Lists.newArrayList(new SelectItem(new MutableColumn("nr", ColumnType.BIGINT)), + new SelectItem(new MutableColumn("name", ColumnType.STRING)), + new SelectItem(new MutableColumn("dnr", ColumnType.BIGINT))), data5); return dataSet5; } @@ -256,6 +299,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { return dataSet6; } + @Test public void testGetTables() throws Exception { MutableTable table1 = new MutableTable("table1"); MutableTable table2 = new MutableTable("table2"); @@ -281,6 +325,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { assertTrue(Arrays.asList(tables).contains(table2)); } + @Test public void testGetTableColumns() throws Exception { MutableTable table1 = new MutableTable("table1"); MutableColumn column1 = new MutableColumn("c1", ColumnType.BIGINT); @@ -307,6 +352,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { assertSame(column3, columns[1]); } + @Test public void testGetTableFromItems() throws Exception { Schema schema = getExampleSchema(); Table contributorTable = schema.getTableByName(TABLE_CONTRIBUTOR); @@ -324,6 +370,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { Arrays.toString(fromItems)); } + @Test public void testGetSelectionNoRows() throws Exception { SelectItem item1 = new SelectItem("foo", "f"); SelectItem item2 = new SelectItem("bar", "b"); @@ -337,6 +384,7 @@ public class MetaModelHelperTest extends MetaModelTestCase { assertEquals("[bar AS b, foo AS f]", Arrays.toString(ds.getSelectItems().toArray())); } + @Test public void testLeftJoinNoRowsOrSingleRow() throws Exception { SelectItem item1 = new SelectItem("foo", "f"); SelectItem item2 = new SelectItem("bar", "b"); @@ -347,8 +395,8 @@ public class MetaModelHelperTest extends MetaModelTestCase { DataSet ds1 = new EmptyDataSet(selectItems1); DataSet ds2 = new EmptyDataSet(selectItems2); - DataSet joinedDs = MetaModelHelper.getLeftJoin(ds1, ds2, new FilterItem[] { new FilterItem(item2, - OperatorType.EQUALS_TO, item3) }); + DataSet joinedDs = MetaModelHelper.getLeftJoin(ds1, ds2, + new FilterItem[] { new FilterItem(item2, OperatorType.EQUALS_TO, item3) }); assertEquals(SubSelectionDataSet.class, joinedDs.getClass()); assertEquals("[foo AS f, bar AS b, baz AS z]", Arrays.toString(joinedDs.getSelectItems().toArray())); @@ -357,21 +405,22 @@ public class MetaModelHelperTest extends MetaModelTestCase { Row row = new DefaultRow(header1, new Object[] { 1, 2 }, null); ds1 = new InMemoryDataSet(header1, row); - joinedDs = MetaModelHelper.getLeftJoin(ds1, ds2, new FilterItem[] { new FilterItem(item2, - OperatorType.EQUALS_TO, item3) }); + joinedDs = MetaModelHelper.getLeftJoin(ds1, ds2, + new FilterItem[] { new FilterItem(item2, OperatorType.EQUALS_TO, item3) }); assertEquals("[foo AS f, bar AS b, baz AS z]", Arrays.toString(joinedDs.getSelectItems().toArray())); assertTrue(joinedDs.next()); assertEquals("Row[values=[1, 2, null]]", joinedDs.getRow().toString()); assertFalse(joinedDs.next()); } + @Test public void testCarthesianProductScalability() { DataSet employees = createDataSet5(); DataSet departmens = createDataSet6(); - FilterItem fi = new FilterItem(employees.getSelectItems().get(2), OperatorType.EQUALS_TO, departmens - .getSelectItems().get(0)); + FilterItem fi = new FilterItem(employees.getSelectItems().get(2), OperatorType.EQUALS_TO, + departmens.getSelectItems().get(0)); DataSet joined = MetaModelHelper.getCarthesianProduct(new DataSet[] { employees, departmens }, fi); int count = 0; @@ -380,6 +429,5 @@ public class MetaModelHelperTest extends MetaModelTestCase { } assertTrue(count == bigDataSetSize); - } } http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/test/java/org/apache/metamodel/QueryPostprocessDataContextTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/metamodel/QueryPostprocessDataContextTest.java b/core/src/test/java/org/apache/metamodel/QueryPostprocessDataContextTest.java index 04cfae9..79ca76e 100644 --- a/core/src/test/java/org/apache/metamodel/QueryPostprocessDataContextTest.java +++ b/core/src/test/java/org/apache/metamodel/QueryPostprocessDataContextTest.java @@ -62,18 +62,18 @@ public class QueryPostprocessDataContextTest extends MetaModelTestCase { public void testSchemaTraversalWithAliasTable() { final MockUpdateableDataContext dc = new MockUpdateableDataContext(); - + final Column column = dc.getColumnByQualifiedLabel("foo"); assertEquals("table", column.getTable().getName()); } - + public void testNoAliasTableWhenSystemPropertySet() { System.setProperty(QueryPostprocessDataContext.SYSTEM_PROPERTY_CREATE_DEFAULT_TABLE_ALIAS, "false"); try { final MockUpdateableDataContext dc = new MockUpdateableDataContext(); final List<Table> tables = dc.getDefaultSchema().getTables(); assertEquals(1, tables.size()); - + assertEquals("table", tables.get(0).getName()); } finally { System.clearProperty(QueryPostprocessDataContext.SYSTEM_PROPERTY_CREATE_DEFAULT_TABLE_ALIAS); @@ -87,7 +87,7 @@ public class QueryPostprocessDataContextTest extends MetaModelTestCase { assertEquals("table", tables.get(0).getName()); } - + public void testAliasTableQueries() { final MockUpdateableDataContext dc = new MockUpdateableDataContext(); final List<Table> tables = dc.getDefaultSchema().getTables(); @@ -338,17 +338,15 @@ public class QueryPostprocessDataContextTest extends MetaModelTestCase { } public void testScalarFunctionWhere() throws Exception { - MockDataContext dc = new MockDataContext("sch", "tab", "1"); - Table table = dc.getDefaultSchema().getTable(0); + final MockDataContext dc = new MockDataContext("sch", "tab", "1"); + final Table table = dc.getDefaultSchema().getTable(0); - Query query = dc.query().from(table).select("foo").where(FunctionType.TO_NUMBER, "bar").eq(1).toQuery(); + final Query query = dc.query().from(table).select("foo").where(FunctionType.TO_NUMBER, "bar").eq(1).toQuery(); assertEquals("SELECT tab.foo FROM sch.tab WHERE TO_NUMBER(tab.bar) = 1", query.toSql()); - DataSet ds = dc.executeQuery(query); + final DataSet ds = dc.executeQuery(query); assertTrue(ds.next()); - Row row; - - row = ds.getRow(); + final Row row = ds.getRow(); assertEquals("Row[values=[2]]", row.toString()); assertFalse(ds.next()); @@ -1183,4 +1181,70 @@ public class QueryPostprocessDataContextTest extends MetaModelTestCase { assertEquals("[hello, world]", values.toString()); } + public void testColumnOnlyUsedInScalarFunctionInWhereClause() throws Exception { + final DataContext dc = getDataContext(); + final Query query = dc.parseQuery( + "SELECT contributor_id FROM contributor WHERE JAVA_SUBSTRING(name, 3, 6) = 'per' ORDER BY contributor_id"); + try (DataSet ds = dc.executeQuery(query)) { + assertTrue(ds.next()); + // kasper + assertEquals("1", ds.getRow().getValue(0).toString()); + assertTrue(ds.next()); + // jesper + assertEquals("6", ds.getRow().getValue(0).toString()); + assertFalse(ds.next()); + } + } + + public void testQueryDifferentScalarFunctionsOnSameColumnInBothSelectAndWhere() throws Exception { + final DataContext dc = getDataContext(); + final Query query = dc.parseQuery( + "SELECT SUBSTRING(name, 1, 3) FROM contributor WHERE JAVA_SUBSTRING(name, 3, 6) = 'per' ORDER BY contributor_id"); + + // assert on the parsed select items just to ensure that nothing gets mangled in the parsing + assertSame(FunctionType.SUBSTRING, query.getSelectClause().getItem(0).getScalarFunction()); + assertEquals(" 1", query.getSelectClause().getItem(0).getFunctionParameters()[0]); + assertEquals(" 3", query.getSelectClause().getItem(0).getFunctionParameters()[1]); + assertSame(FunctionType.JAVA_SUBSTRING, query.getWhereClause().getItem(0).getSelectItem().getScalarFunction()); + assertEquals(" 3", query.getWhereClause().getItem(0).getSelectItem().getFunctionParameters()[0]); + assertEquals(" 6", query.getWhereClause().getItem(0).getSelectItem().getFunctionParameters()[1]); + + try (DataSet ds = dc.executeQuery(query)) { + assertTrue(ds.next()); + // name is "kasper" + final Object value1 = ds.getRow().getValue(0); + assertEquals("kas", value1.toString()); + assertTrue(ds.next()); + // name is "jesper" + final Object value2 = ds.getRow().getValue(0); + assertEquals("jes", value2.toString()); + assertFalse(ds.next()); + } + } + + public void testQuerySameScalarFunctionOnSameColumnButDifferentParamsInBothSelectAndWhere() throws Exception { + final DataContext dc = getDataContext(); + final Query query = dc.parseQuery( + "SELECT SUBSTRING(name, 1, 3) FROM contributor WHERE SUBSTRING(name, 4, 3) = 'per' ORDER BY contributor_id"); + + // assert on the parsed select items just to ensure that nothing gets mangled in the parsing + assertSame(FunctionType.SUBSTRING, query.getSelectClause().getItem(0).getScalarFunction()); + assertEquals(" 1", query.getSelectClause().getItem(0).getFunctionParameters()[0]); + assertEquals(" 3", query.getSelectClause().getItem(0).getFunctionParameters()[1]); + assertSame(FunctionType.SUBSTRING, query.getWhereClause().getItem(0).getSelectItem().getScalarFunction()); + assertEquals(" 4", query.getWhereClause().getItem(0).getSelectItem().getFunctionParameters()[0]); + assertEquals(" 3", query.getWhereClause().getItem(0).getSelectItem().getFunctionParameters()[1]); + + try (DataSet ds = dc.executeQuery(query)) { + assertTrue(ds.next()); + // name is "kasper" + final Object value1 = ds.getRow().getValue(0); + assertEquals("kas", value1.toString()); + assertTrue(ds.next()); + // name is "jesper" + final Object value2 = ds.getRow().getValue(0); + assertEquals("jes", value2.toString()); + assertFalse(ds.next()); + } + } } \ No newline at end of file http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/test/java/org/apache/metamodel/query/SelectItemTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/metamodel/query/SelectItemTest.java b/core/src/test/java/org/apache/metamodel/query/SelectItemTest.java index 7f412c0..dd46094 100644 --- a/core/src/test/java/org/apache/metamodel/query/SelectItemTest.java +++ b/core/src/test/java/org/apache/metamodel/query/SelectItemTest.java @@ -24,11 +24,26 @@ import org.apache.metamodel.schema.MutableColumn; import org.apache.metamodel.schema.Schema; import org.apache.metamodel.schema.Table; +import static org.junit.Assert.assertNotEquals; + import java.util.List; public class SelectItemTest extends MetaModelTestCase { private Schema _schema = getExampleSchema(); + + public void testEqualsAndHashCodeWithScalarFunctionParameters() { + final SelectItem selectItem = new SelectItem(_schema.getTableByName(TABLE_PROJECT).getColumns().get(0)); + final SelectItem item1 = selectItem.replaceFunction(FunctionType.SUBSTRING, 2, 2); + final SelectItem item2 = selectItem.replaceFunction(FunctionType.SUBSTRING, 2, 2); + final SelectItem item3 = selectItem.replaceFunction(FunctionType.SUBSTRING, 2, 3); + + assertEquals(item1, item2); + assertEquals(item1.hashCode(), item2.hashCode()); + + assertNotEquals(item1, item3); + assertNotEquals(item1.hashCode(), item3.hashCode()); + } public void testSelectColumnInFromItem() throws Exception { final Table projectTable = _schema.getTableByName(TABLE_PROJECT); http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/test/java/org/apache/metamodel/query/SubstringFunctionTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/metamodel/query/SubstringFunctionTest.java b/core/src/test/java/org/apache/metamodel/query/SubstringFunctionTest.java new file mode 100644 index 0000000..7566bca --- /dev/null +++ b/core/src/test/java/org/apache/metamodel/query/SubstringFunctionTest.java @@ -0,0 +1,77 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.metamodel.query; + +import org.apache.metamodel.data.DataSetHeader; +import org.apache.metamodel.data.DefaultRow; +import org.apache.metamodel.data.SimpleDataSetHeader; +import org.apache.metamodel.schema.MutableColumn; +import org.junit.Assert; +import org.junit.Test; + +public class SubstringFunctionTest { + + private final SubstringFunction javaStyleFunction = SubstringFunction.createJavaStyle(); + private final SubstringFunction sqlStyleFunction = SubstringFunction.createSqlStyle(); + + @Test + public void testSubstringVanilla() { + Assert.assertEquals("2", runTest(javaStyleFunction, "123456", 1, 2)); + Assert.assertEquals("1234", runTest(javaStyleFunction, "123456", 0, 4)); + Assert.assertEquals("34", runTest(javaStyleFunction, "123456", 2, 4)); + + Assert.assertEquals("1", runTest(sqlStyleFunction, "123456", 1, 1)); + Assert.assertEquals("1234", runTest(sqlStyleFunction, "123456", 1, 4)); + Assert.assertEquals("34", runTest(sqlStyleFunction, "123456", 3, 2)); + } + + @Test + public void testSubstringBadOrWeirdParamValues() { + Assert.assertEquals("", runTest(javaStyleFunction, "123456", 0, 0)); + Assert.assertEquals("1234", runTest(javaStyleFunction, "123456", -10, 4)); + Assert.assertEquals("", runTest(javaStyleFunction, "123456", 4, -1)); + } + + @Test + public void testSubstringEndIndexTooLarge() { + Assert.assertEquals("123456", runTest(javaStyleFunction, "123456", 0, 200)); + Assert.assertEquals("56", runTest(javaStyleFunction, "123456", 4, 8)); + + Assert.assertEquals("123456", runTest(sqlStyleFunction, "123456", 1, 200)); + Assert.assertEquals("56", runTest(sqlStyleFunction, "123456", 5, 5)); + } + + @Test + public void testSubstringStartIndexTooLarge() { + Assert.assertEquals("", runTest(javaStyleFunction, "123456", 200, 2)); + } + + @Test + public void testSubstringOnlyStartIndex() { + Assert.assertEquals("123456", runTest(javaStyleFunction, "123456", 0)); + Assert.assertEquals("", runTest(javaStyleFunction, "123456", 10)); + Assert.assertEquals("3456", runTest(javaStyleFunction, "123456", 2)); + } + + private String runTest(ScalarFunction f, String str, Object... params) { + SelectItem selectItem = new SelectItem(new MutableColumn("column")); + DataSetHeader header = new SimpleDataSetHeader(new SelectItem[] { selectItem }); + return (String) f.evaluate(new DefaultRow(header, new Object[] { str }), params, selectItem); + } +} http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/core/src/test/java/org/apache/metamodel/query/parser/QueryParserTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/metamodel/query/parser/QueryParserTest.java b/core/src/test/java/org/apache/metamodel/query/parser/QueryParserTest.java index fa9b66d..9e16fe4 100644 --- a/core/src/test/java/org/apache/metamodel/query/parser/QueryParserTest.java +++ b/core/src/test/java/org/apache/metamodel/query/parser/QueryParserTest.java @@ -27,6 +27,7 @@ import org.apache.metamodel.MockDataContext; import org.apache.metamodel.query.FilterClause; import org.apache.metamodel.query.FilterItem; import org.apache.metamodel.query.FromItem; +import org.apache.metamodel.query.FunctionType; import org.apache.metamodel.query.OperatorType; import org.apache.metamodel.query.OrderByItem; import org.apache.metamodel.query.OrderByItem.Direction; @@ -83,7 +84,25 @@ public class QueryParserTest extends TestCase { Query q = MetaModelHelper.parseQuery(dc, "SELECT sch.tbl.baz.foo.bar, baz.helloworld, baz.hello.world FROM sch.tbl"); assertEquals( - "SELECT MAP_VALUE('foo.bar',tbl.baz), MAP_VALUE('helloworld',tbl.baz), MAP_VALUE('hello.world',tbl.baz) FROM sch.tbl", + "SELECT MAP_VALUE(tbl.baz,'foo.bar'), MAP_VALUE(tbl.baz,'helloworld'), MAP_VALUE(tbl.baz,'hello.world') FROM sch.tbl", + q.toSql()); + } + + public void testWhereMapValueUsingDotNotation() throws Exception { + // set 'baz' column to a MAP column + MutableColumn col = (MutableColumn) dc.getColumnByQualifiedLabel("tbl.baz"); + col.setType(ColumnType.MAP); + + Query q = MetaModelHelper.parseQuery(dc, + "SELECT baz.lorem, baz.ipsum FROM sch.tbl WHERE baz.hello = 'world'"); + + final SelectItem whereSelectItem = q.getWhereClause().getItem(0).getSelectItem(); + assertEquals(whereSelectItem.getScalarFunction(), FunctionType.MAP_VALUE); + assertEquals(col, whereSelectItem.getColumn()); + assertEquals("[hello]", Arrays.toString(whereSelectItem.getFunctionParameters())); + + assertEquals( + "SELECT MAP_VALUE(tbl.baz,'lorem'), MAP_VALUE(tbl.baz,'ipsum') FROM sch.tbl WHERE MAP_VALUE(tbl.baz,'hello') = 'world'", q.toSql()); } http://git-wip-us.apache.org/repos/asf/metamodel/blob/39947f55/mongodb/mongo2/src/test/java/org/apache/metamodel/mongodb/mongo2/MongoDbDataContextTest.java ---------------------------------------------------------------------- diff --git a/mongodb/mongo2/src/test/java/org/apache/metamodel/mongodb/mongo2/MongoDbDataContextTest.java b/mongodb/mongo2/src/test/java/org/apache/metamodel/mongodb/mongo2/MongoDbDataContextTest.java index 398c408..47a009c 100644 --- a/mongodb/mongo2/src/test/java/org/apache/metamodel/mongodb/mongo2/MongoDbDataContextTest.java +++ b/mongodb/mongo2/src/test/java/org/apache/metamodel/mongodb/mongo2/MongoDbDataContextTest.java @@ -278,7 +278,7 @@ public class MongoDbDataContextTest extends MongoDbTestCase { // Instantiate the actual data context final DataContext dataContext = new MongoDbDataContext(db); - assertTrue(Arrays.asList(dataContext.getDefaultSchema().getTableNames()).contains(getCollectionName())); + assertTrue(dataContext.getDefaultSchema().getTableNames().contains(getCollectionName())); Table table = dataContext.getDefaultSchema().getTableByName(getCollectionName()); assertEquals("[_id, baz, foo, id, list, name]", Arrays.toString(table.getColumnNames().toArray()));
