This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/calcite.git
commit b3353015ad5528837effda14b8edbd792e688e27 Author: shenlang <[email protected]> AuthorDate: Mon Aug 7 23:35:22 2023 +0800 [CALCITE-5895] TABLESAMPLE (0) should return no rows TABLESAMPLE(100) should return the underlying relation, with no sampling. Change type of 'sample percentage' fields in SqlSampleSpec and RelOptSamplingParameters from float to BigDecimal. Rename the fields to 'sampleRate' (to make it clear that the value they hold is a probability, between 0 and 1, rather than a percentage, between 0 and 100), and make them public (so that people can use the fields directly, rather than the deprecated accessor method). Deprecate SqlTableSampleSpec#getSamplePercentage() and RelOptSamplingParameters.getSamplingPercentage(). Add Quidem tests for TABLESAMPLE queries in new tablesample.iq file. Close apache/calcite#3360 --- core/src/main/codegen/templates/Parser.jj | 42 ++++-------- .../calcite/plan/RelOptSamplingParameters.java | 38 +++++++---- .../main/java/org/apache/calcite/rel/RelInput.java | 13 +++- .../java/org/apache/calcite/rel/core/Sample.java | 27 ++++++-- .../apache/calcite/rel/externalize/RelJson.java | 4 ++ .../calcite/rel/externalize/RelJsonReader.java | 6 ++ .../apache/calcite/rel/mutable/MutableSample.java | 2 +- .../java/org/apache/calcite/sql/SqlSampleSpec.java | 77 +++++++++++++--------- .../calcite/sql/validate/SqlValidatorImpl.java | 9 +++ .../apache/calcite/sql2rel/SqlToRelConverter.java | 31 +++++++-- .../apache/calcite/test/SqlToRelConverterTest.java | 17 +++++ .../org/apache/calcite/test/SqlValidatorTest.java | 6 ++ .../apache/calcite/test/SqlToRelConverterTest.xml | 30 +++++++++ core/src/test/resources/sql/tablesample.iq | 45 +++++++++++++ .../apache/calcite/sql/parser/SqlParserTest.java | 14 ++++ 15 files changed, 272 insertions(+), 89 deletions(-) diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj index 6a4991fc1b..ee9a9c02a7 100644 --- a/core/src/main/codegen/templates/Parser.jj +++ b/core/src/main/codegen/templates/Parser.jj @@ -157,6 +157,7 @@ public class ${parser.class} extends SqlAbstractParserImpl SqlLiteral.createExactNumeric("1", SqlParserPos.ZERO); private static final SqlLiteral LITERAL_MINUS_ONE = SqlLiteral.createExactNumeric("-1", SqlParserPos.ZERO); + private static final BigDecimal ONE_HUNDRED = BigDecimal.valueOf(100L); private static Metadata metadata; @@ -2279,7 +2280,7 @@ SqlNode Tablesample(SqlNode tableRef) : SqlSampleSpec sampleSpec = SqlSampleSpec.createNamed(sampleName); final SqlLiteral sampleLiteral = SqlLiteral.createSample(sampleSpec, s.end(this)); - tableRef = SqlStdOperatorTable.TABLESAMPLE.createCall( + return SqlStdOperatorTable.TABLESAMPLE.createCall( s.add(tableRef).end(this), tableRef, sampleLiteral); } | @@ -2296,36 +2297,19 @@ SqlNode Tablesample(SqlNode tableRef) : } ] { - final BigDecimal ONE_HUNDRED = BigDecimal.valueOf(100L); - BigDecimal rate = samplePercentage.bigDecimalValue(); - if (rate.compareTo(BigDecimal.ZERO) < 0 - || rate.compareTo(ONE_HUNDRED) > 0) - { - throw SqlUtil.newContextException(getPos(), RESOURCE.invalidSampleSize()); - } - - // Treat TABLESAMPLE(0) and TABLESAMPLE(100) as no table - // sampling at all. Not strictly correct: TABLESAMPLE(0) - // should produce no output, but it simplifies implementation - // to know that some amount of sampling will occur. - // In practice values less than ~1E-43% are treated as 0.0 and - // values greater than ~99.999997% are treated as 1.0 - float fRate = rate.divide(ONE_HUNDRED).floatValue(); - if (fRate > 0.0f && fRate < 1.0f) { - SqlSampleSpec tableSampleSpec = - isRepeatable - ? SqlSampleSpec.createTableSample( - isBernoulli, fRate, repeatableSeed) - : SqlSampleSpec.createTableSample(isBernoulli, fRate); - - SqlLiteral tableSampleLiteral = - SqlLiteral.createSample(tableSampleSpec, s.end(this)); - tableRef = SqlStdOperatorTable.TABLESAMPLE.createCall( - s.end(this), tableRef, tableSampleLiteral); - } + BigDecimal rate = + samplePercentage.bigDecimalValue().divide(ONE_HUNDRED); + SqlSampleSpec tableSampleSpec = + isRepeatable + ? SqlSampleSpec.createTableSample(isBernoulli, rate, + repeatableSeed) + : SqlSampleSpec.createTableSample(isBernoulli, rate); + SqlLiteral tableSampleLiteral = + SqlLiteral.createSample(tableSampleSpec, s.end(this)); + return SqlStdOperatorTable.TABLESAMPLE.createCall( + s.end(this), tableRef, tableSampleLiteral); } ) - { return tableRef; } } /** Wraps a table reference in a call to EXTEND if an optional "EXTEND" clause diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptSamplingParameters.java b/core/src/main/java/org/apache/calcite/plan/RelOptSamplingParameters.java index 99eb3da907..3cc7736d4b 100644 --- a/core/src/main/java/org/apache/calcite/plan/RelOptSamplingParameters.java +++ b/core/src/main/java/org/apache/calcite/plan/RelOptSamplingParameters.java @@ -16,31 +16,42 @@ */ package org.apache.calcite.plan; +import java.math.BigDecimal; + +import static java.util.Objects.requireNonNull; + /** * RelOptSamplingParameters represents the parameters necessary to produce a * sample of a relation. * - * <p>It's parameters are derived from the SQL 2003 TABLESAMPLE clause. + * <p>Its parameters are derived from the SQL 2003 TABLESAMPLE clause. */ public class RelOptSamplingParameters { //~ Instance fields -------------------------------------------------------- - private final boolean isBernoulli; - private final float samplingPercentage; - private final boolean isRepeatable; + private final boolean bernoulli; + public final BigDecimal sampleRate; + private final boolean repeatable; private final int repeatableSeed; //~ Constructors ----------------------------------------------------------- + public RelOptSamplingParameters(boolean bernoulli, BigDecimal sampleRate, + boolean repeatable, int repeatableSeed) { + this.bernoulli = bernoulli; + this.sampleRate = requireNonNull(sampleRate, "sampleRate"); + this.repeatable = repeatable; + this.repeatableSeed = repeatableSeed; + } + + @Deprecated // to be removed before 2.0 public RelOptSamplingParameters( - boolean isBernoulli, - float samplingPercentage, + boolean bernoulli, + float sampleRate, boolean isRepeatable, int repeatableSeed) { - this.isBernoulli = isBernoulli; - this.samplingPercentage = samplingPercentage; - this.isRepeatable = isRepeatable; - this.repeatableSeed = repeatableSeed; + this(bernoulli, BigDecimal.valueOf(sampleRate), isRepeatable, + repeatableSeed); } //~ Methods ---------------------------------------------------------------- @@ -55,7 +66,7 @@ public class RelOptSamplingParameters { * sampling */ public boolean isBernoulli() { - return isBernoulli; + return bernoulli; } /** @@ -66,8 +77,9 @@ public class RelOptSamplingParameters { * * @return the sampling percentage between 0.0 and 1.0, exclusive */ + @Deprecated // to be removed before 2.0 public float getSamplingPercentage() { - return samplingPercentage; + return sampleRate.floatValue(); } /** @@ -80,7 +92,7 @@ public class RelOptSamplingParameters { * @return true if the sample results should be repeatable */ public boolean isRepeatable() { - return isRepeatable; + return repeatable; } /** diff --git a/core/src/main/java/org/apache/calcite/rel/RelInput.java b/core/src/main/java/org/apache/calcite/rel/RelInput.java index a360c14556..c3d8e41b57 100644 --- a/core/src/main/java/org/apache/calcite/rel/RelInput.java +++ b/core/src/main/java/org/apache/calcite/rel/RelInput.java @@ -29,6 +29,7 @@ import com.google.common.collect.ImmutableList; import org.checkerframework.checker.nullness.qual.Nullable; +import java.math.BigDecimal; import java.util.List; /** @@ -64,15 +65,23 @@ public interface RelInput { @Nullable Object get(String tag); /** - * Returns a {@code string} value. Throws if wrong type. + * Returns a {@code string} value. + * Throws if wrong type, returns null if not present. */ @Nullable String getString(String tag); /** - * Returns a {@code float} value. Throws if not present or wrong type. + * Returns a {@code float} value. + * Throws if not present or wrong type. */ float getFloat(String tag); + /** + * Returns a {@code BigDecimal} value. + * Throws if not present or wrong type. + */ + BigDecimal getBigDecimal(String tag); + /** * Returns an enum value. Throws if not a valid member. */ diff --git a/core/src/main/java/org/apache/calcite/rel/core/Sample.java b/core/src/main/java/org/apache/calcite/rel/core/Sample.java index 3b5bff1c06..112291e5bb 100644 --- a/core/src/main/java/org/apache/calcite/rel/core/Sample.java +++ b/core/src/main/java/org/apache/calcite/rel/core/Sample.java @@ -25,7 +25,13 @@ import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.RelWriter; import org.apache.calcite.rel.SingleRel; +import org.checkerframework.checker.nullness.qual.Nullable; + +import java.math.BigDecimal; import java.util.List; +import java.util.Objects; + +import static java.util.Objects.requireNonNull; /** * Relational expression that returns a sample of the rows from its input. @@ -58,12 +64,19 @@ public class Sample extends SingleRel { private static RelOptSamplingParameters getSamplingParameters( RelInput input) { String mode = input.getString("mode"); - float percentage = input.getFloat("rate"); - Object repeatableSeed = input.get("repeatableSeed"); - boolean repeatable = repeatableSeed instanceof Number; - return new RelOptSamplingParameters( - "bernoulli".equals(mode), percentage, repeatable, - repeatable && repeatableSeed != null ? ((Number) repeatableSeed).intValue() : 0); + final boolean bernoulli = "bernoulli".equals(mode); + final BigDecimal rate = input.getBigDecimal("rate"); + final Object repeatableSeed = input.get("repeatableSeed"); + final int seed; + final boolean repeatable; + if (repeatableSeed instanceof Number) { + repeatable = true; + seed = ((Number) repeatableSeed).intValue(); + } else { + repeatable = false; + seed = 0; + } + return new RelOptSamplingParameters(bernoulli, rate, repeatable, seed); } @Override public RelNode copy(RelTraitSet traitSet, List<RelNode> inputs) { @@ -81,7 +94,7 @@ public class Sample extends SingleRel { @Override public RelWriter explainTerms(RelWriter pw) { return super.explainTerms(pw) .item("mode", params.isBernoulli() ? "bernoulli" : "system") - .item("rate", params.getSamplingPercentage()) + .item("rate", params.sampleRate) .item("repeatableSeed", params.isRepeatable() ? params.getRepeatableSeed() : "-"); } diff --git a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java index f8b441ba65..e128002849 100644 --- a/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java +++ b/core/src/main/java/org/apache/calcite/rel/externalize/RelJson.java @@ -1103,6 +1103,10 @@ public class RelJson { throw new UnsupportedOperationException(); } + @Override public BigDecimal getBigDecimal(String tag) { + throw new UnsupportedOperationException(); + } + @Override public <E extends Enum<E>> @Nullable E getEnum( String tag, Class<E> enumClass) { throw new UnsupportedOperationException(); diff --git a/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java b/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java index 80624bb122..61a86d9b5f 100644 --- a/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java +++ b/core/src/main/java/org/apache/calcite/rel/externalize/RelJsonReader.java @@ -31,6 +31,7 @@ import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.rex.RexLiteral; import org.apache.calcite.rex.RexNode; +import org.apache.calcite.runtime.SqlFunctions; import org.apache.calcite.schema.Schema; import org.apache.calcite.sql.SqlAggFunction; import org.apache.calcite.util.ImmutableBitSet; @@ -47,6 +48,7 @@ import org.checkerframework.checker.nullness.qual.Nullable; import java.io.IOException; import java.lang.reflect.Constructor; import java.lang.reflect.InvocationTargetException; +import java.math.BigDecimal; import java.util.AbstractList; import java.util.ArrayList; import java.util.LinkedHashMap; @@ -227,6 +229,10 @@ public class RelJsonReader { return ((Number) getNonNull(tag)).floatValue(); } + @Override public BigDecimal getBigDecimal(String tag) { + return SqlFunctions.toBigDecimal(getNonNull(tag)); + } + @Override public boolean getBoolean(String tag, boolean default_) { final Boolean b = (Boolean) get(tag); return b != null ? b : default_; diff --git a/core/src/main/java/org/apache/calcite/rel/mutable/MutableSample.java b/core/src/main/java/org/apache/calcite/rel/mutable/MutableSample.java index 1fe63f6cf9..7345fdbf9b 100644 --- a/core/src/main/java/org/apache/calcite/rel/mutable/MutableSample.java +++ b/core/src/main/java/org/apache/calcite/rel/mutable/MutableSample.java @@ -57,7 +57,7 @@ public class MutableSample extends MutableSingleRel { return buf.append("Sample(mode: ") .append(params.isBernoulli() ? "bernoulli" : "system") .append("rate") - .append(params.getSamplingPercentage()) + .append(params.sampleRate) .append("repeatableSeed") .append(params.isRepeatable() ? params.getRepeatableSeed() : "-") .append(")"); diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSampleSpec.java b/core/src/main/java/org/apache/calcite/sql/SqlSampleSpec.java index 974c23d6f4..ede6bcb8e2 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlSampleSpec.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlSampleSpec.java @@ -18,6 +18,10 @@ package org.apache.calcite.sql; import org.apache.calcite.sql.dialect.CalciteSqlDialect; +import java.math.BigDecimal; + +import static java.util.Objects.requireNonNull; + /** * Specification of a SQL sample. * @@ -35,6 +39,8 @@ import org.apache.calcite.sql.dialect.CalciteSqlDialect; * {@link SqlLiteral#createSample(SqlSampleSpec, org.apache.calcite.sql.parser.SqlParserPos)}. */ public abstract class SqlSampleSpec { + private static final BigDecimal ONE_HUNDRED = BigDecimal.valueOf(100); + //~ Constructors ----------------------------------------------------------- protected SqlSampleSpec() { @@ -54,12 +60,19 @@ public abstract class SqlSampleSpec { * * @param isBernoulli true if Bernoulli style sampling is to be used; * false for implementation specific sampling - * @param samplePercentage likelihood of a row appearing in the sample + * @param sampleRate likelihood of a row appearing in the sample */ public static SqlSampleSpec createTableSample( boolean isBernoulli, - float samplePercentage) { - return new SqlTableSampleSpec(isBernoulli, samplePercentage); + BigDecimal sampleRate) { + return new SqlTableSampleSpec(isBernoulli, sampleRate); + } + + @Deprecated // to be removed before 2.0 + public static SqlSampleSpec createTableSample( + boolean isBernoulli, + float sampleRate) { + return createTableSample(isBernoulli, BigDecimal.valueOf(sampleRate)); } /** @@ -67,16 +80,22 @@ public abstract class SqlSampleSpec { * * @param isBernoulli true if Bernoulli style sampling is to be used; * false for implementation specific sampling - * @param samplePercentage likelihood of a row appearing in the sample + * @param sampleRate likelihood of a row appearing in the sample * @param repeatableSeed seed value used to reproduce the same sample */ public static SqlSampleSpec createTableSample( boolean isBernoulli, - float samplePercentage, + BigDecimal sampleRate, + int repeatableSeed) { + return new SqlTableSampleSpec(isBernoulli, sampleRate, true, repeatableSeed); + } + + @Deprecated // to be removed before 2.0 + public static SqlSampleSpec createTableSample( + boolean isBernoulli, + float sampleRate, int repeatableSeed) { - return new SqlTableSampleSpec( - isBernoulli, - samplePercentage, + return createTableSample(isBernoulli, BigDecimal.valueOf(sampleRate), repeatableSeed); } @@ -103,25 +122,20 @@ public abstract class SqlSampleSpec { /** Sample specification. */ public static class SqlTableSampleSpec extends SqlSampleSpec { - private final boolean isBernoulli; - private final float samplePercentage; - private final boolean isRepeatable; + private final boolean bernoulli; + public final BigDecimal sampleRate; + private final boolean repeatable; private final int repeatableSeed; - private SqlTableSampleSpec(boolean isBernoulli, float samplePercentage) { - this.isBernoulli = isBernoulli; - this.samplePercentage = samplePercentage; - this.isRepeatable = false; - this.repeatableSeed = 0; + private SqlTableSampleSpec(boolean bernoulli, BigDecimal sampleRate) { + this(bernoulli, sampleRate, false, 0); } - private SqlTableSampleSpec( - boolean isBernoulli, - float samplePercentage, - int repeatableSeed) { - this.isBernoulli = isBernoulli; - this.samplePercentage = samplePercentage; - this.isRepeatable = true; + private SqlTableSampleSpec(boolean bernoulli, BigDecimal sampleRate, + boolean repeatable, int repeatableSeed) { + this.bernoulli = bernoulli; + this.sampleRate = requireNonNull(sampleRate, "sampleRate"); + this.repeatable = repeatable; this.repeatableSeed = repeatableSeed; } @@ -129,21 +143,24 @@ public abstract class SqlSampleSpec { * Indicates Bernoulli vs. System sampling. */ public boolean isBernoulli() { - return isBernoulli; + return bernoulli; } /** - * Returns sampling percentage. Range is 0.0 to 1.0, exclusive + * Returns the sampling rate. + * The range is 0.0 to 1.0. + * 0.0 returns no rows, and 1.0 returns all rows. */ + @Deprecated public float getSamplePercentage() { - return samplePercentage; + return sampleRate.floatValue(); } /** * Indicates whether repeatable seed should be used. */ public boolean isRepeatable() { - return isRepeatable; + return repeatable; } /** @@ -155,12 +172,12 @@ public abstract class SqlSampleSpec { @Override public String toString() { StringBuilder b = new StringBuilder(); - b.append(isBernoulli ? "BERNOULLI" : "SYSTEM"); + b.append(bernoulli ? "BERNOULLI" : "SYSTEM"); b.append('('); - b.append(samplePercentage * 100.0); + b.append(sampleRate.multiply(ONE_HUNDRED)); b.append(')'); - if (isRepeatable) { + if (repeatable) { b.append(" REPEATABLE("); b.append(repeatableSeed); b.append(')'); diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java index db6d937a70..a5f3488b04 100644 --- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java +++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java @@ -1104,6 +1104,15 @@ public class SqlValidatorImpl implements SqlValidatorWithHints { List<SqlNode> operands = ((SqlCall) node).getOperandList(); SqlSampleSpec sampleSpec = SqlLiteral.sampleValue(operands.get(1)); if (sampleSpec instanceof SqlSampleSpec.SqlTableSampleSpec) { + // The sampling percentage must be between 0 (0%) and 1 (100%). + BigDecimal samplePercentage = + ((SqlSampleSpec.SqlTableSampleSpec) sampleSpec).sampleRate; + // Check the samplePercentage whether is between 0 and 1 + if (samplePercentage.compareTo(BigDecimal.ZERO) < 0 + || samplePercentage.compareTo(BigDecimal.ONE) > 0) { + throw SqlUtil.newContextException(node.getParserPosition(), + RESOURCE.invalidSampleSize()); + } validateFeature(RESOURCE.sQLFeature_T613(), node.getParserPosition()); } else if (sampleSpec instanceof SqlSampleSpec.SqlSubstitutionSampleSpec) { diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java index 6e44e8e764..09a5a5479f 100644 --- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java +++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java @@ -2361,13 +2361,30 @@ public class SqlToRelConverter { SqlSampleSpec.SqlTableSampleSpec tableSampleSpec = (SqlSampleSpec.SqlTableSampleSpec) sampleSpec; convertFrom(bb, operands.get(0)); - RelOptSamplingParameters params = - new RelOptSamplingParameters( - tableSampleSpec.isBernoulli(), - tableSampleSpec.getSamplePercentage(), - tableSampleSpec.isRepeatable(), - tableSampleSpec.getRepeatableSeed()); - bb.setRoot(new Sample(cluster, bb.root(), params), false); + + // Treat TABLESAMPLE(0) and TABLESAMPLE(100) as no table + // sampling at all. Not strictly correct: TABLESAMPLE(0) + // should produce no output, but it simplifies implementation + // to know that some amount of sampling will occur. + // In practice values less than ~1E-43% are treated as 0.0 and + // values greater than ~99.999997% are treated as 1.0 + relBuilder.push(bb.root()); + if (tableSampleSpec.sampleRate.compareTo(BigDecimal.ZERO) == 0) { + // The table sample rate is 0; the query should return empty. + relBuilder.empty(); + } else if (tableSampleSpec.sampleRate.compareTo(BigDecimal.ONE) == 0) { + // The table sample rate is 1; the query should return the contents + // of the underlying table. + } else { + RelOptSamplingParameters params = + new RelOptSamplingParameters( + tableSampleSpec.isBernoulli(), + tableSampleSpec.sampleRate, + tableSampleSpec.isRepeatable(), + tableSampleSpec.getRepeatableSeed()); + relBuilder.push(new Sample(cluster, relBuilder.build(), params)); + } + bb.setRoot(relBuilder.build(), true); } else { throw new AssertionError("unknown TABLESAMPLE type: " + sampleSpec); } diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java index 19b36f4aea..3da46f282b 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -1522,6 +1522,14 @@ class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).ok(); } + @Test void testSampleBernoulliWithRateZero() { + final String sql = "select *\n" + + "from (\n" + + " select * from emp limit 10\n" + + ") as e tablesample bernoulli(0)"; + sql(sql).ok(); + } + @Test void testSampleSystem() { final String sql = "select * from emp tablesample system(50) where empno > 5"; @@ -1537,6 +1545,15 @@ class SqlToRelConverterTest extends SqlToRelTestBase { sql(sql).ok(); } + @Test void testSampleSystemWithRateZero() { + final String sql = "select * from (\n" + + " select * from emp as e\n" + + " join dept on e.deptno = dept.deptno\n" + + ") tablesample system(0)\n" + + "where empno > 5"; + sql(sql).ok(); + } + @Test void testCollectionTableWithCursorParam() { final String sql = "select * from table(dedup(" + "cursor(select ename from emp)," diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index 563b60bca9..cb6184d65d 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -7973,6 +7973,12 @@ public class SqlValidatorTest extends SqlValidatorTestCase { + " select * from emp\n" + " join dept on emp.deptno = dept.deptno\n" + ") tablesample system(10)").ok(); + + sql("select * from ^emp TABLESAMPLE BERNOULLI(1000)^") + .fails("TABLESAMPLE percentage must be between 0 and 100, inclusive"); + + sql("select * from ^emp TABLESAMPLE SYSTEM(101)^") + .fails("TABLESAMPLE percentage must be between 0 and 100, inclusive"); } @Test void testRewriteWithoutIdentifierExpansion() { diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml index 7f948f9240..ea7e9dbf24 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -6336,6 +6336,20 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ Sample(mode=[bernoulli], rate=[0.1], repeatableSeed=[1]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testSampleBernoulliWithRateZero"> + <Resource name="sql"> + <![CDATA[select * +from ( + select * from emp limit 10 +) as e tablesample bernoulli(0)]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalValues(tuples=[[]]) ]]> </Resource> </TestCase> @@ -6389,6 +6403,22 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ Sample(mode=[system], rate=[0.1], repeatableSeed=[1]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testSampleSystemWithRateZero"> + <Resource name="sql"> + <![CDATA[select * from ( + select * from emp as e + join dept on e.deptno = dept.deptno +) tablesample system(0) +where empno > 5]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10]) + LogicalFilter(condition=[>($0, 5)]) + LogicalValues(tuples=[[]]) ]]> </Resource> </TestCase> diff --git a/core/src/test/resources/sql/tablesample.iq b/core/src/test/resources/sql/tablesample.iq new file mode 100644 index 0000000000..379dc5929c --- /dev/null +++ b/core/src/test/resources/sql/tablesample.iq @@ -0,0 +1,45 @@ +# tablesample.iq - Tests for TABLESAMPLE +# +# 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. +# +!use scott +!set outputformat mysql + +# [CALCITE-5895] TABLESAMPLE(0) should return empty result +select comm from "scott".emp tablesample system(0); ++------+ +| COMM | ++------+ ++------+ +(0 rows) + +!ok + +# Should always return all rows +select deptno from "scott".dept tablesample system(100); ++--------+ +| DEPTNO | ++--------+ +| 10 | +| 20 | +| 30 | +| 40 | ++--------+ +(4 rows) + +!ok + +# End tablesample.iq diff --git a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java index c23377c0c3..7cadf913bb 100644 --- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java +++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java @@ -3337,6 +3337,20 @@ public class SqlParserTest { + "tablesample bernoulli(50) REPEATABLE(-^100000000000000000000^) ") .fails("Literal '100000000000000000000' " + "can not be parsed to type 'java\\.lang\\.Integer'"); + + // test bernoulli sample percentage with zero. + final String sql4 = "select * " + + "from emp as x tablesample bernoulli(0)"; + final String expected4 = "SELECT *\n" + + "FROM `EMP` AS `X` TABLESAMPLE BERNOULLI(0)"; + sql(sql4).ok(expected4); + + // test system sample percentage with zero. + final String sql5 = "select * " + + "from emp as x tablesample system(0)"; + final String expected5 = "SELECT *\n" + + "FROM `EMP` AS `X` TABLESAMPLE SYSTEM(0)"; + sql(sql5).ok(expected5); } @Test void testLiteral() {
