| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.UserDefinedAggregatesTest |
| |
| 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.derbyTesting.functionTests.tests.lang; |
| |
| import java.sql.Blob; |
| import java.sql.Clob; |
| import java.sql.Connection; |
| import java.sql.ResultSet; |
| import junit.framework.Test; |
| import org.apache.derby.iapi.types.HarmonySerialBlob; |
| import org.apache.derby.iapi.types.HarmonySerialClob; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.Decorator; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * <p> |
| * Test user defined aggregates. See DERBY-672. |
| * </p> |
| */ |
| public class UserDefinedAggregatesTest extends GeneratedColumnsHelper |
| { |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // CONSTANTS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| public static final String OBJECT_EXISTS = "X0Y68"; |
| public static final String ILLEGAL_AGGREGATE = "42ZC3"; |
| public static final String NAME_COLLISION = "X0Y87"; |
| public static final String MISSING_FUNCTION = "42Y03"; |
| public static final String MISSING_SCHEMA = "42Y07"; |
| public static final String BAD_AGGREGATE_USAGE = "42903"; |
| public static final String BAD_AGG_PLACEMENT = "42Y35"; |
| public static final String INPUT_MISMATCH = "42Y22"; |
| public static final String BAD_GEN_COL = "42XA1"; |
| public static final String INPUT_OUTSIDE_BOUNDS = "42ZC6"; |
| public static final String RETURN_OUTSIDE_BOUNDS = "42ZC7"; |
| public static final String XML_TYPE = "42ZB3"; |
| public static final String INT_TRUNCATION = "22003"; |
| public static final String CAST_FAILURE = "22018"; |
| public static final String AGG_IN_GROUP_BY = "42Y26"; |
| public static final String NESTED_AGGS = "42Y33"; |
| public static final String UNTYPED_NULL = "42Y83"; |
| public static final String MISSING_CLASS = "42ZC8"; |
| public static final String AGG_IN_ON_CLAUSE = "42Z07"; |
| public static final String BAD_CONSTRAINT = "42Y01"; |
| public static final String DEPENDENCY_VIOLATION = "X0Y30"; |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // STATE |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // CONSTRUCTOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| |
| /** |
| * Create a new instance. |
| */ |
| |
| public UserDefinedAggregatesTest(String name) |
| { |
| super(name); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // JUnit BEHAVIOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| |
| /** |
| * Construct top level suite in this JUnit test |
| */ |
| public static Test suite() |
| { |
| BaseTestSuite suite = new BaseTestSuite("UserDefinedAggregatesTest"); |
| |
| suite.addTest( TestConfiguration.defaultSuite(UserDefinedAggregatesTest.class) ); |
| suite.addTest( collatedSuite( "en" ) ); |
| |
| return suite; |
| } |
| |
| /** |
| * Return a suite that uses a single use database with |
| * a primary fixture from this test plus potentially other |
| * fixtures. |
| * @param locale Locale to use for the database |
| * @return suite of tests to run for the given locale |
| */ |
| private static Test collatedSuite(String locale) |
| { |
| BaseTestSuite suite = |
| new BaseTestSuite("UserDefinedAggregatesTest:territory=" + locale); |
| |
| suite.addTest( TestConfiguration.defaultSuite(UserDefinedAggregatesTest.class) ); |
| |
| return Decorator.territoryCollatedDatabase( suite, locale ); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // TESTS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /** |
| * <p> |
| * Basic syntax. |
| * </p> |
| */ |
| public void test_01_basicSyntax() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create schema uda_schema\n" ); |
| goodStatement( conn, "create schema uda_schema2\n" ); |
| goodStatement( conn, "create schema uda_schema3\n" ); |
| |
| // some good aggregate creations |
| goodStatement( conn, "create derby aggregate mode_01 for int external name 'foo.bar.Wibble'" ); |
| goodStatement( conn, "create derby aggregate uda_schema.mode_012 for int external name 'foo.bar.Wibble'" ); |
| |
| // can't create an aggregate with an existing name |
| expectExecutionError |
| ( conn, OBJECT_EXISTS, "create derby aggregate mode_01 for int external name 'foo.bar.Wibble'" ); |
| expectExecutionError |
| ( conn, OBJECT_EXISTS, "create derby aggregate uda_schema.mode_012 for int external name 'foo.bar.Wibble'" ); |
| |
| // only RESTRICTed drops allowed now |
| expectCompilationError( SYNTAX_ERROR, "drop derby aggregate mode_01" ); |
| |
| // successfully drop an aggregate |
| goodStatement( conn, "drop derby aggregate mode_01 restrict" ); |
| |
| // can't create an aggregate with the same name as a 1-arg function |
| // but no collision with 2-arg function names |
| goodStatement |
| ( conn, "create function uda_schema3.agg_collide( a int ) returns int language\n" + |
| "java parameter style java external name 'Foo.f'" ); |
| goodStatement |
| ( conn, "create function uda_schema3.agg_nocollide( a int, b int ) returns int language java\n" + |
| "parameter style java external name 'Foo.f'" ); |
| expectExecutionError |
| ( conn, NAME_COLLISION, "create derby aggregate uda_schema3.agg_collide for int external name 'foo.bar.Wibble'" ); |
| goodStatement |
| ( conn, "create derby aggregate uda_schema3.agg_nocollide for int external name 'foo.bar.Wibble'" ); |
| |
| goodStatement |
| ( conn, "create function agg_collide( a int ) returns int language java parameter style java external name 'Foo.f'" ); |
| goodStatement |
| ( conn, "create function agg_nocollide( a int, b int ) returns int language java parameter style java external name 'Foo.f'" ); |
| expectExecutionError( conn, NAME_COLLISION, "create derby aggregate agg_collide for int external name 'foo.bar.Wibble'" ); |
| goodStatement( conn, "create derby aggregate agg_nocollide for int external name 'foo.bar.Wibble'" ); |
| |
| // can't create a 1-arg function with same name as an aggregate |
| goodStatement |
| ( conn, "create derby aggregate func_collide for int external name 'foo.bar.Wibble'" ); |
| goodStatement |
| ( conn, "create derby aggregate func_nocollide for int external name 'foo.bar.Wibble'" ); |
| expectExecutionError |
| ( conn, NAME_COLLISION, |
| "create function func_collide( a int ) returns int language java parameter style java external name 'Foo.f'" ); |
| goodStatement |
| ( conn, "create function func_nocollide( a int, b int ) returns int language java parameter style java external name 'Foo.f'" ); |
| |
| goodStatement |
| ( conn, "create derby aggregate uda_schema3.func_collide for int external name 'foo.bar.Wibble'" ); |
| goodStatement |
| ( conn, "create derby aggregate uda_schema3.func_nocollide for int external name 'foo.bar.Wibble'" ); |
| expectExecutionError |
| ( conn, NAME_COLLISION, |
| "create function uda_schema3.func_collide( a int ) returns int language java parameter style java external name 'Foo.f'" ); |
| goodStatement |
| ( conn, "create function uda_schema3.func_nocollide( a int, b int ) returns int language\n" + |
| "java parameter style java external name 'Foo.f'" ); |
| |
| // can't drop a schema which still has an aggregate in it |
| expectExecutionError( conn, NON_EMPTY_SCHEMA, "drop schema uda_schema restrict" ); |
| |
| // drop the aggregate, then drop the schema |
| goodStatement( conn, "drop derby aggregate uda_schema.mode_012 restrict" ); |
| goodStatement( conn, "drop schema uda_schema restrict" ); |
| |
| // can't drop a non-existent aggregate |
| expectCompilationError( NONEXISTENT_OBJECT, "drop derby aggregate mode_01 restrict" ); |
| expectCompilationError( NONEXISTENT_OBJECT, "drop derby aggregate mode_011 restrict" ); |
| expectCompilationError( NONEXISTENT_OBJECT, "drop derby aggregate uda_schema2.mode_01 restrict" ); |
| } |
| |
| /** |
| * <p> |
| * Don't allow aggregates to have the names of builtin functions with 1 argument. |
| * See also DERBY-5901. |
| * </p> |
| */ |
| public void test_02_builtinConflicts() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // 1 argument bad |
| badAggregate( conn, ILLEGAL_AGGREGATE, "abs" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "absval" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "acos" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "asin" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "atan" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "ceil" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "ceiling" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "cos" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "cosh" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "cot" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "date" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "day" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "degrees" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "exp" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "floor" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "lcase" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "length" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "ln" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "log" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "log10" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "month" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "radians" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "rand" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "sign" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "sin" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "sinh" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "sqrt" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "tan" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "tanh" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "time" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "timestamp" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "ucase" ); |
| |
| // no conflict with 2 argument builtin functions |
| goodStatement( conn, "create derby aggregate locate for int external name 'foo.bar.Wibble'" ); |
| goodStatement( conn, "drop derby aggregate locate restrict" ); |
| } |
| private void badAggregate( Connection conn, String expectedSQLState, String name ) throws Exception |
| { |
| String ddl = "create derby aggregate " + name + " for int external name 'foo.bar.Wibble'"; |
| |
| expectCompilationError( expectedSQLState, ddl ); |
| } |
| |
| /** |
| * <p> |
| * Various 1-arg operators and SQL aggregates should not be legal aggregate names because they |
| * are supposed to be reserved keywords. |
| * See also DERBY-5901. |
| * </p> |
| */ |
| public void test_03_keywordConflicts() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // 1-arg functions which are reserved keywords |
| badAggregate( conn, SYNTAX_ERROR, "bigint" ); |
| badAggregate( conn, SYNTAX_ERROR, "char" ); |
| badAggregate( conn, SYNTAX_ERROR, "double" ); |
| badAggregate( conn, SYNTAX_ERROR, "hour" ); |
| badAggregate( conn, SYNTAX_ERROR, "integer" ); |
| badAggregate( conn, SYNTAX_ERROR, "ltrim" ); |
| badAggregate( conn, SYNTAX_ERROR, "lower" ); |
| badAggregate( conn, SYNTAX_ERROR, "minute" ); |
| badAggregate( conn, SYNTAX_ERROR, "rtrim" ); |
| badAggregate( conn, SYNTAX_ERROR, "second" ); |
| badAggregate( conn, SYNTAX_ERROR, "smallint" ); |
| badAggregate( conn, SYNTAX_ERROR, "trim" ); |
| badAggregate( conn, SYNTAX_ERROR, "upper" ); |
| badAggregate( conn, SYNTAX_ERROR, "varchar" ); |
| badAggregate( conn, SYNTAX_ERROR, "year" ); |
| |
| // SQL aggregates which are reserved keywords |
| badAggregate( conn, SYNTAX_ERROR, "any" ); |
| badAggregate( conn, SYNTAX_ERROR, "avg" ); |
| badAggregate( conn, SYNTAX_ERROR, "max" ); |
| badAggregate( conn, SYNTAX_ERROR, "min" ); |
| badAggregate( conn, SYNTAX_ERROR, "some" ); |
| badAggregate( conn, SYNTAX_ERROR, "sum" ); |
| } |
| |
| /** |
| * <p> |
| * Various aggregates defined by the SQL Standard do not appear in the Derby |
| * grammar as reserved keywords. They are, nonetheless, illegal as the names |
| * of user-defined aggregates. |
| * See also DERBY-5901. |
| * </p> |
| */ |
| public void test_04_nonReservedAggregateConflicts() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| badAggregate( conn, ILLEGAL_AGGREGATE, "collect" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "count" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "every" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "fusion" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "intersection" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "stddev_pop" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "stddev_samp" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "var_pop" ); |
| badAggregate( conn, ILLEGAL_AGGREGATE, "var_samp" ); |
| } |
| |
| /** |
| * <p> |
| * Basic test for aggregates in the select list. |
| * </p> |
| */ |
| public void test_05_basicSelectList() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement( conn, "create schema agg_schema\n" ); |
| goodStatement |
| ( conn, "create derby aggregate mode_05 for int\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate'" ); |
| goodStatement |
| ( conn, "create derby aggregate agg_schema.mode_052 for int\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate'" ); |
| goodStatement( conn, "create table mode_05_inputs( a int, b int )" ); |
| goodStatement( conn, "insert into mode_05_inputs( a, b ) values ( 1, 1 ), ( 1, 2 ), ( 1, 2 ), ( 1, 2 ), ( 2, 3 ), ( 2, 3 ), ( 2, 4 )" ); |
| |
| // scalar aggregate |
| assertResults |
| ( |
| conn, |
| "select mode_05( b ) from mode_05_inputs", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select app.mode_05( b ) from mode_05_inputs", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select agg_schema.mode_052( b ) from mode_05_inputs", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| false |
| ); |
| |
| // grouped aggregate |
| assertResults |
| ( |
| conn, |
| "select a, mode_05( b ) from mode_05_inputs group by a", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, app.mode_05( b ) from mode_05_inputs group by a", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| false |
| ); |
| |
| // distinct scalar aggregate |
| assertResults |
| ( |
| conn, |
| "select mode_05( distinct b ) from mode_05_inputs", |
| new String[][] |
| { |
| { "4" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select agg_schema.mode_052( distinct b ) from mode_05_inputs", |
| new String[][] |
| { |
| { "4" }, |
| }, |
| false |
| ); |
| |
| // distinct grouped aggregate |
| assertResults |
| ( |
| conn, |
| "select a, mode_05( distinct b ) from mode_05_inputs group by a", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "4" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, agg_schema.mode_052( distinct b ) from mode_05_inputs group by a", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "4" }, |
| }, |
| false |
| ); |
| |
| // some negative tests for missing aggregates |
| expectCompilationError( MISSING_FUNCTION, "select agg_schema.mode_05( b ) from mode_05_inputs" ); |
| expectCompilationError( OBJECT_DOES_NOT_EXIST, "select agg_schema.mode_05( distinct b ) from mode_05_inputs" ); |
| expectCompilationError( MISSING_SCHEMA, "select missing_schema.mode_05( b ) from mode_05_inputs" ); |
| expectCompilationError( MISSING_SCHEMA, "select missing_schema.mode_05( distinct b ) from mode_05_inputs" ); |
| |
| // some negative tests for aggregates in the WHERE clause |
| expectCompilationError( BAD_AGGREGATE_USAGE, "select * from mode_05_inputs where mode_05( b ) = 4" ); |
| expectCompilationError( BAD_AGGREGATE_USAGE, "select * from mode_05_inputs where mode_05( distinct b ) = 4" ); |
| expectCompilationError( BAD_AGGREGATE_USAGE, "select * from mode_05_inputs where app.mode_05( b ) = 4" ); |
| expectCompilationError( BAD_AGGREGATE_USAGE, "select * from mode_05_inputs where app.mode_05( distinct b ) = 4" ); |
| |
| // negative test: can't put an aggregate in an ORDER BY list unless it's in the SELECT list too |
| expectCompilationError( BAD_AGG_PLACEMENT, "select * from mode_05_inputs order by mode_05( b )" ); |
| |
| // various other syntactically correct placements of user-defined aggregates |
| assertResults |
| ( |
| conn, |
| "select mode_05( b ) from mode_05_inputs order by mode_05( b )", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, mode_05( b ) from mode_05_inputs group by a order by mode_05( b )", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, mode_05( b ) from mode_05_inputs group by a order by mode_05( b ) desc", |
| new String[][] |
| { |
| { "2", "3" }, |
| { "1", "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, mode_05( b ) from mode_05_inputs group by a having mode_05( b ) = 3", |
| new String[][] |
| { |
| { "2", "3" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, count( b ) from mode_05_inputs group by a having mode_05( b ) = 3", |
| new String[][] |
| { |
| { "2", "3" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, sum( b ) from mode_05_inputs group by a having mode_05( b ) = 3", |
| new String[][] |
| { |
| { "2", "10" }, |
| }, |
| false |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select mode_05( b ) from mode_05_inputs order by app.mode_05( b )", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, mode_05( b ) from mode_05_inputs group by a order by app.mode_05( b )", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, mode_05( b ) from mode_05_inputs group by a order by app.mode_05( b ) desc", |
| new String[][] |
| { |
| { "2", "3" }, |
| { "1", "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, mode_05( b ) from mode_05_inputs group by a having app.mode_05( b ) = 3", |
| new String[][] |
| { |
| { "2", "3" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, count( b ) from mode_05_inputs group by a having app.mode_05( b ) = 3", |
| new String[][] |
| { |
| { "2", "3" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, sum( b ) from mode_05_inputs group by a having app.mode_05( b ) = 3", |
| new String[][] |
| { |
| { "2", "10" }, |
| }, |
| false |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select app.mode_05( b ) from mode_05_inputs order by app.mode_05( b )", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, app.mode_05( b ) from mode_05_inputs group by a order by app.mode_05( b )", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, app.mode_05( b ) from mode_05_inputs group by a order by app.mode_05( b ) desc", |
| new String[][] |
| { |
| { "2", "3" }, |
| { "1", "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, app.mode_05( b ) from mode_05_inputs group by a having app.mode_05( b ) = 3", |
| new String[][] |
| { |
| { "2", "3" }, |
| }, |
| false |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select app.mode_05( b ) from mode_05_inputs order by mode_05( b )", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, app.mode_05( b ) from mode_05_inputs group by a order by mode_05( b )", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, app.mode_05( b ) from mode_05_inputs group by a order by mode_05( b ) desc", |
| new String[][] |
| { |
| { "2", "3" }, |
| { "1", "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, app.mode_05( b ) from mode_05_inputs group by a having mode_05( b ) = 3", |
| new String[][] |
| { |
| { "2", "3" }, |
| }, |
| false |
| ); |
| } |
| |
| /** |
| * <p> |
| * Test for parameterized aggregates. |
| * </p> |
| */ |
| public void test_06_parameterizedAggregates() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "intMode", |
| "int", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$IntMode", |
| "( 1, 1 ), ( 1, 2 ), ( 1, 2 ), ( 1, 2 ), ( 2, 3 ), ( 2, 3 ), ( 2, 4 )", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| new String[][] |
| { |
| { "4" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "4" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "varcharMode", |
| "varchar( 5 )", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$StringMode", |
| "( 1, 'a' ), ( 1, 'ab' ), ( 1, 'ab' ), ( 1, 'ab' ), ( 2, 'abc' ), ( 2, 'abc' ), ( 2, 'abcd' )", |
| new String[][] |
| { |
| { "ab" }, |
| }, |
| new String[][] |
| { |
| { "1", "ab" }, |
| { "2", "abc" }, |
| }, |
| new String[][] |
| { |
| { "abcd" }, |
| }, |
| new String[][] |
| { |
| { "1", "ab" }, |
| { "2", "abcd" }, |
| } |
| ); |
| |
| } |
| private void vetParameterizedAggregate |
| ( |
| Connection conn, |
| String aggName, |
| String sqlType, |
| String externalName, |
| String values, |
| String[][] scalarResult, |
| String[][] groupedResult, |
| String[][] distinctScalarResult, |
| String[][] distinctGroupedResult |
| ) |
| throws Exception |
| { |
| String tableName = aggName + "_mode_inputs"; |
| |
| goodStatement |
| ( conn, "create derby aggregate " + aggName + " for " + sqlType + "\n" + |
| "external name '" + externalName + "'" ); |
| goodStatement( conn, "create table " + tableName + "( a int, b " + sqlType + " )" ); |
| goodStatement( conn, "insert into " + tableName + "( a, b ) values " + values ); |
| |
| assertResults |
| ( |
| conn, |
| "select " + aggName + "( b ) from " + tableName, |
| scalarResult, |
| false |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select a, " + aggName + "( b ) from " + tableName + " group by a", |
| groupedResult, |
| false |
| ); |
| |
| if ( distinctScalarResult != null ) |
| { |
| assertResults |
| ( |
| conn, |
| "select " + aggName + "( distinct b ) from " + tableName, |
| distinctScalarResult, |
| false |
| ); |
| } |
| |
| if ( distinctGroupedResult != null ) |
| { |
| assertResults |
| ( |
| conn, |
| "select a, " + aggName + "( distinct b ) from " + tableName + " group by a", |
| distinctGroupedResult, |
| false |
| ); |
| } |
| } |
| |
| /** |
| * <p> |
| * Test restricted drops of aggregates. |
| * </p> |
| */ |
| public void test_07_restrictedDrops() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement |
| ( conn, |
| "create derby aggregate mode_07 for int external name 'org.apache.derbyTesting.functionTests.tests.lang.ModeAggregate'" ); |
| goodStatement |
| ( conn, |
| "create table mode_inputs_07( a int, b int )" ); |
| |
| // restricted drop blocked by a view |
| goodStatement |
| ( conn, |
| "create view v_dbo_07( a, modeOfA ) as select a, mode_07( b ) from mode_inputs_07 group by a" ); |
| expectExecutionError |
| ( conn, VIEW_DEPENDENCY, "drop derby aggregate mode_07 restrict" ); |
| goodStatement |
| ( conn, |
| "drop view v_dbo_07" ); |
| |
| // restricted drop blocked by a trigger |
| goodStatement |
| ( conn, |
| "create table t_source_07( a int )" ); |
| goodStatement |
| ( conn, |
| "create table t_target_07( a int )" ); |
| goodStatement |
| ( conn, |
| "create trigger t_insert_trigger_07\n" + |
| "after insert on t_source_07\n" + |
| "for each row\n" + |
| "insert into t_target_07( a ) select mode_07( b ) from mode_inputs_07\n" |
| ); |
| expectExecutionError |
| ( conn, FORBIDDEN_DROP_TRIGGER, "drop derby aggregate mode_07 restrict" ); |
| goodStatement |
| ( conn, |
| "drop trigger t_insert_trigger_07" ); |
| |
| // blocking objects dropped. aggregate is now droppable |
| goodStatement( conn, "drop derby aggregate mode_07 restrict" ); |
| |
| } |
| |
| /** |
| * <p> |
| * Test aggregates on user defined types. |
| * </p> |
| */ |
| public void test_08_basicUDTaggregates() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement |
| ( conn, |
| "create type FullName external name 'org.apache.derbyTesting.functionTests.tests.lang.FullName' language java" ); |
| goodStatement |
| ( |
| conn, |
| "create function makeFullName( firstName varchar( 32672 ), lastName varchar( 32672 ) )\n" + |
| "returns FullName language java parameter style java\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.FullName.makeFullName'" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate fullNameMode for FullName\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$FullNameMode'" |
| ); |
| goodStatement |
| ( conn, |
| "create table fullNameMode_inputs( a int, b FullName )" ); |
| goodStatement |
| ( |
| conn, |
| "insert into fullNameMode_inputs( a, b )\n" + |
| "values\n" + |
| "( 1, makeFullName( 'one', 'name' ) ),\n" + |
| "( 1, makeFullName( 'two', 'name' ) ),\n" + |
| "( 1, makeFullName( 'two', 'name' ) ),\n" + |
| "( 1, makeFullName( 'two', 'name' ) ),\n" + |
| "( 2, makeFullName( 'three', 'name' ) ),\n" + |
| "( 2, makeFullName( 'three', 'name' ) ),\n" + |
| "( 2, makeFullName( 'four', 'name' ) )\n" |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select fullNameMode( b ) from fullNameMode_inputs", |
| new String[][] |
| { |
| { "two name" } |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, fullNameMode( b ) from fullNameMode_inputs group by a", |
| new String[][] |
| { |
| { "1", "two name" }, |
| { "2", "three name" } |
| }, |
| false |
| ); |
| } |
| |
| /** |
| * <p> |
| * Test aggregates bound to generic classes. |
| * </p> |
| */ |
| public void test_09_genericAggregates() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "intMode_09", |
| "int", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode", |
| "( 1, 1 ), ( 1, 2 ), ( 1, 2 ), ( 1, 2 ), ( 2, 3 ), ( 2, 3 ), ( 2, 4 )", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| }, |
| new String[][] |
| { |
| { "4" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "4" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "varcharMode_09", |
| "varchar( 5 )", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode", |
| "( 1, 'a' ), ( 1, 'ab' ), ( 1, 'ab' ), ( 1, 'ab' ), ( 2, 'abc' ), ( 2, 'abc' ), ( 2, 'abcd' )", |
| new String[][] |
| { |
| { "ab" }, |
| }, |
| new String[][] |
| { |
| { "1", "ab" }, |
| { "2", "abc" }, |
| }, |
| new String[][] |
| { |
| { "abcd" }, |
| }, |
| new String[][] |
| { |
| { "1", "ab" }, |
| { "2", "abcd" }, |
| } |
| ); |
| |
| goodStatement |
| ( |
| conn, |
| "create type FullName_09 external name 'org.apache.derbyTesting.functionTests.tests.lang.FullName' language java" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create function makeFullName_09( firstName varchar( 32672 ), lastName varchar( 32672 ) )\n" + |
| "returns FullName_09 language java parameter style java\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.FullName.makeFullName'\n" |
| ); |
| vetParameterizedAggregate |
| ( |
| conn, |
| "fullNameMode_09", |
| "FullName_09", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode", |
| "( 1, makeFullName_09( 'one', 'name' ) ),\n" + |
| "( 1, makeFullName_09( 'two', 'name' ) ),\n" + |
| "( 1, makeFullName_09( 'two', 'name' ) ),\n" + |
| "( 1, makeFullName_09( 'two', 'name' ) ),\n" + |
| "( 2, makeFullName_09( 'three', 'name' ) ),\n" + |
| "( 2, makeFullName_09( 'three', 'name' ) ),\n" + |
| "( 2, makeFullName_09( 'four', 'name' ) )\n", |
| new String[][] |
| { |
| { "two name" }, |
| }, |
| new String[][] |
| { |
| { "1", "two name" }, |
| { "2", "three name" }, |
| }, |
| null, |
| null |
| ); |
| } |
| |
| /** |
| * <p> |
| * Negative tests. |
| * </p> |
| */ |
| public void test_10_negative() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // |
| // Input operand must agree with input type of aggregate |
| // |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate intMode_10 for int\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$IntMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table intMode_10_inputs( a int, b varchar( 10 ) )" |
| ); |
| expectCompilationError( INPUT_MISMATCH, "select intMode_10( b ) from intMode_10_inputs" ); |
| |
| // aggregates not allowed in generated columns |
| expectCompilationError( BAD_GEN_COL, "create table t_shouldFail( a int, b int generated always as ( intMode_10( a ) ) )" ); |
| |
| // |
| // Input type not within bounds of aggregator class. |
| // |
| goodStatement |
| ( |
| conn, |
| "create type Price_10 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table t_price_10( a int, b Price_10 )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate priceMode_10 for Price_10\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode'\n" |
| ); |
| expectCompilationError( INPUT_OUTSIDE_BOUNDS, "select priceMode_10( b ) from t_price_10" ); |
| expectCompilationError( INPUT_OUTSIDE_BOUNDS, "select a, priceMode_10( b ) from t_price_10 group by a" ); |
| |
| // |
| // Return type not within bounds of aggregator class. |
| // |
| goodStatement |
| ( |
| conn, |
| "create table t_price_10_1( a int, b int )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate priceMode_10_1 for int returns Price_10\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode'" |
| ); |
| expectCompilationError( RETURN_OUTSIDE_BOUNDS, "select priceMode_10_1( b ) from t_price_10_1" ); |
| expectCompilationError( RETURN_OUTSIDE_BOUNDS, "select a, priceMode_10_1( b ) from t_price_10_1 group by a" ); |
| |
| // aggregates not allowed inside aggregates |
| expectCompilationError( NESTED_AGGS, "select max( intMode_10( columnnumber ) ) from sys.syscolumns" ); |
| expectCompilationError( NESTED_AGGS, "select intMode_10( max( columnnumber ) ) from sys.syscolumns" ); |
| |
| // untyped nulls not allowed as args to an aggregate |
| expectCompilationError( UNTYPED_NULL, "select intMode_10( null ) from sys.syscolumns" ); |
| |
| // missing Aggregator class |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate intMode_missing_10 for int external name 'missing.Missing'" |
| ); |
| expectCompilationError( MISSING_CLASS, "select intMode_missing_10( columnnumber ) from sys.syscolumns" ); |
| |
| // invalid context for an aggregate |
| expectCompilationError( BAD_AGGREGATE_USAGE, "select * from sys.syscolumns where intMode_10( columnnumber ) = 1" ); |
| expectCompilationError |
| ( BAD_AGG_PLACEMENT, |
| "select case when columnnumber = 1 then 1 else intMode_10( columnnumber ) end from sys.syscolumns" ); |
| expectCompilationError |
| ( BAD_AGG_PLACEMENT, |
| "select case when columnnumber = 1 then intMode_10( columnnumber ) else 1 end from sys.syscolumns" ); |
| expectCompilationError |
| ( BAD_AGG_PLACEMENT, |
| "select case when columnnumber = intMode_10( columnnumber ) then 0 else 1 end from sys.syscolumns" ); |
| expectCompilationError |
| ( BAD_AGGREGATE_USAGE, "values ( intMode_10( 1 ) )" ); |
| expectCompilationError |
| ( BAD_CONSTRAINT, |
| "create table badTable( a int, b int check ( intMode_10( b ) > 1 ) )" ); |
| |
| // aggregates not permitted in ON clause |
| expectCompilationError |
| ( AGG_IN_ON_CLAUSE, |
| "select * from sys.syscolumns l join sys.syscolumns r on intMode_10( r.columnnumber ) = l.columnnumber" ); |
| |
| // aggregates not allowed in the SET clause of an UPDATE statement |
| goodStatement( conn, "create table intMode_10_inputs_1( a int, b int )" ); |
| expectCompilationError |
| ( BAD_AGG_PLACEMENT, "update intMode_10_inputs_1 set b = intMode_10( b )" ); |
| } |
| |
| /** |
| * <p> |
| * Test datatype coverage. Verify that you can declare user-defined aggregates on all |
| * Derby data types except for XML. |
| * </p> |
| */ |
| public void test_11_datatypes() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // if this fails, then we need to add a new data type to this test |
| vetDatatypeCount( conn, 22 ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "booleanMode_11", |
| "boolean", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$BooleanMode", |
| "( 1, false ), ( 1, true ), (1, false ), ( 2, true ), ( 2, true ), ( 3, false ), ( 3, true ), ( 3, true )", |
| new String[][] |
| { |
| { "true" }, |
| }, |
| new String[][] |
| { |
| { "1", "false" }, |
| { "2", "true" }, |
| { "3", "true" }, |
| }, |
| new String[][] |
| { |
| { "true" }, |
| }, |
| new String[][] |
| { |
| { "1", "true" }, |
| { "2", "true" }, |
| { "3", "true" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "bigintMode_11", |
| "bigint", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$BigintMode", |
| "( 1, 1 ), ( 1, 2 ), (1, 2 ), ( 2, 2 ), ( 2, 3 ), ( 3, 3 ), ( 3, 4 ), ( 3, 5 )", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| { "3", "5" }, |
| }, |
| new String[][] |
| { |
| { "5" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| { "3", "5" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "smallintMode_11", |
| "smallint", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$IntMode", |
| "( 1, 1 ), ( 1, 2 ), (1, 2 ), ( 2, 2 ), ( 2, 3 ), ( 3, 3 ), ( 3, 4 ), ( 3, 5 )", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| { "3", "5" }, |
| }, |
| new String[][] |
| { |
| { "5" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| { "3", "5" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "intMode_11", |
| "int", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$IntMode", |
| "( 1, 1 ), ( 1, 2 ), (1, 2 ), ( 2, 2 ), ( 2, 3 ), ( 3, 3 ), ( 3, 4 ), ( 3, 5 )", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| { "3", "5" }, |
| }, |
| new String[][] |
| { |
| { "5" }, |
| }, |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| { "3", "5" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "decimalMode_11", |
| "decimal( 5, 2 )", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$BigDecimalMode", |
| "( 1, 1.11 ), ( 1, 2.22 ), (1, 2.22 ), ( 2, 2.22 ), ( 2, 3.33 ), ( 3, 3.33 ), ( 3, 4.44 ), ( 3, 5.55 )", |
| new String[][] |
| { |
| { "2.22" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| }, |
| new String[][] |
| { |
| { "5.55" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "numericMode_11", |
| "numeric( 5, 2 )", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$BigDecimalMode", |
| "( 1, 1.11 ), ( 1, 2.22 ), (1, 2.22 ), ( 2, 2.22 ), ( 2, 3.33 ), ( 3, 3.33 ), ( 3, 4.44 ), ( 3, 5.55 )", |
| new String[][] |
| { |
| { "2.22" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| }, |
| new String[][] |
| { |
| { "5.55" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "doubleMode_11", |
| "double", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$DoubleMode", |
| "( 1, 1.11 ), ( 1, 2.22 ), (1, 2.22 ), ( 2, 2.22 ), ( 2, 3.33 ), ( 3, 3.33 ), ( 3, 4.44 ), ( 3, 5.55 )", |
| new String[][] |
| { |
| { "2.22" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| }, |
| new String[][] |
| { |
| { "5.55" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "floatMode_11", |
| "float", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$DoubleMode", |
| "( 1, 1.11 ), ( 1, 2.22 ), (1, 2.22 ), ( 2, 2.22 ), ( 2, 3.33 ), ( 3, 3.33 ), ( 3, 4.44 ), ( 3, 5.55 )", |
| new String[][] |
| { |
| { "2.22" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| }, |
| new String[][] |
| { |
| { "5.55" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "realMode_11", |
| "real", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$RealMode", |
| "( 1, 1.11 ), ( 1, 2.22 ), (1, 2.22 ), ( 2, 2.22 ), ( 2, 3.33 ), ( 3, 3.33 ), ( 3, 4.44 ), ( 3, 5.55 )", |
| new String[][] |
| { |
| { "2.22" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| }, |
| new String[][] |
| { |
| { "5.55" }, |
| }, |
| new String[][] |
| { |
| { "1", "2.22" }, |
| { "2", "3.33" }, |
| { "3", "5.55" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "charMode_11", |
| "char( 4 )", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$StringMode", |
| "( 1, 'aaaa' ), ( 1, 'abaa' ), ( 1, 'aaaa' ), ( 2, 'abaa' ), ( 2, 'abaa' ), ( 2, 'abca' ), ( 3, 'abaa' ), ( 3, 'abaa' ), ( 3, 'abcd' )", |
| new String[][] |
| { |
| { "abaa" }, |
| }, |
| new String[][] |
| { |
| { "1", "aaaa" }, |
| { "2", "abaa" }, |
| { "3", "abaa" }, |
| }, |
| new String[][] |
| { |
| { "abcd" }, |
| }, |
| new String[][] |
| { |
| { "1", "abaa" }, |
| { "2", "abca" }, |
| { "3", "abcd" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "varcharMode_11", |
| "varchar( 4 )", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$StringMode", |
| "( 1, 'aaaa' ), ( 1, 'abaa' ), ( 1, 'aaaa' ), ( 2, 'abaa' ), ( 2, 'abaa' ), ( 2, 'abca' ), ( 3, 'abaa' ), ( 3, 'abaa' ), ( 3, 'abcd' )", |
| new String[][] |
| { |
| { "abaa" }, |
| }, |
| new String[][] |
| { |
| { "1", "aaaa" }, |
| { "2", "abaa" }, |
| { "3", "abaa" }, |
| }, |
| new String[][] |
| { |
| { "abcd" }, |
| }, |
| new String[][] |
| { |
| { "1", "abaa" }, |
| { "2", "abca" }, |
| { "3", "abcd" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "longvarcharMode_11", |
| "long varchar", |
| "org.apache.derbyTesting.functionTests.tests.lang.GenericMode$StringMode", |
| "( 1, 'aaaa' ), ( 1, 'abaa' ), ( 1, 'aaaa' ), ( 2, 'abaa' ), ( 2, 'abaa' ), ( 2, 'abca' ), ( 3, 'abaa' ), ( 3, 'abaa' ), ( 3, 'abcd' )", |
| new String[][] |
| { |
| { "abaa" }, |
| }, |
| new String[][] |
| { |
| { "1", "aaaa" }, |
| { "2", "abaa" }, |
| { "3", "abaa" }, |
| }, |
| null, |
| null |
| ); |
| |
| goodStatement |
| ( |
| conn, |
| "create function makeBlob_11( contents varchar( 32672 ) ) returns blob\n" + |
| "language java parameter style java no sql deterministic\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UserDefinedAggregatesTest.makeBlob'\n" |
| ); |
| vetParameterizedAggregate |
| ( |
| conn, |
| "blobMode_11", |
| "blob", |
| "org.apache.derbyTesting.functionTests.tests.lang.LobMode$BlobMode", |
| "( 1, makeBlob_11( 'a' ) ),\n" + |
| "( 1, makeBlob_11( 'ab' ) ),\n" + |
| "( 1, makeBlob_11( 'ab' ) ),\n" + |
| "( 2, makeBlob_11( 'ab' ) ),\n" + |
| "( 2, makeBlob_11( 'abc' ) ),\n" + |
| "( 3, makeBlob_11( 'a' ) ),\n" + |
| "( 3, makeBlob_11( 'ab' ) ),\n" + |
| "( 3, makeBlob_11( 'abcd' ) )", |
| new String[][] |
| { |
| { "6162" }, |
| }, |
| new String[][] |
| { |
| { "1", "6162" }, |
| { "2", "616263" }, |
| { "3", "61626364" }, |
| }, |
| null, |
| null |
| ); |
| |
| goodStatement |
| ( |
| conn, |
| "create function makeClob_11( contents varchar( 32672 ) ) returns clob\n" + |
| "language java parameter style java no sql deterministic\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UserDefinedAggregatesTest.makeClob'\n" |
| ); |
| vetParameterizedAggregate |
| ( |
| conn, |
| "clobMode_11", |
| "clob", |
| "org.apache.derbyTesting.functionTests.tests.lang.LobMode$ClobMode", |
| "( 1, makeClob_11( 'a' ) ),\n" + |
| "( 1, makeClob_11( 'ab' ) ),\n" + |
| "( 1, makeClob_11( 'ab' ) ),\n" + |
| "( 2, makeClob_11( 'ab' ) ),\n" + |
| "( 2, makeClob_11( 'abc' ) ),\n" + |
| "( 3, makeClob_11( 'a' ) ),\n" + |
| "( 3, makeClob_11( 'ab' ) ),\n" + |
| "( 3, makeClob_11( 'abcd' ) )", |
| new String[][] |
| { |
| { "ab" }, |
| }, |
| new String[][] |
| { |
| { "1", "ab" }, |
| { "2", "abc" }, |
| { "3", "abcd" }, |
| }, |
| null, |
| null |
| ); |
| |
| goodStatement |
| ( |
| conn, |
| "create function makeBinary_11( contents varchar( 32672 ) ) returns char( 4 ) for bit data\n" + |
| "language java parameter style java no sql deterministic\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UserDefinedAggregatesTest.makeBinary'\n" |
| ); |
| vetParameterizedAggregate |
| ( |
| conn, |
| "binaryMode_11", |
| "char( 4 ) for bit data", |
| "org.apache.derbyTesting.functionTests.tests.lang.LobMode$BinaryMode", |
| "( 1, makeBinary_11( 'abaa' ) ),\n" + |
| "( 1, makeBinary_11( 'abaa' ) ),\n" + |
| "( 1, makeBinary_11( 'abca' ) ),\n" + |
| "( 2, makeBinary_11( 'abaa' ) ),\n" + |
| "( 2, makeBinary_11( 'abca' ) ),\n" + |
| "( 2, makeBinary_11( 'abaa' ) ),\n" + |
| "( 3, makeBinary_11( 'aaaa' ) ),\n" + |
| "( 3, makeBinary_11( 'abcd' ) ),\n" + |
| "( 3, makeBinary_11( 'abcd' ) )\n", |
| new String[][] |
| { |
| { "61626161" }, |
| }, |
| new String[][] |
| { |
| { "1", "61626161" }, |
| { "2", "61626161" }, |
| { "3", "61626364" }, |
| }, |
| new String[][] |
| { |
| { "61626364" }, |
| }, |
| new String[][] |
| { |
| { "1", "61626361" }, |
| { "2", "61626361" }, |
| { "3", "61626364" }, |
| } |
| ); |
| |
| goodStatement |
| ( |
| conn, |
| "create function makeVarbinary_11( contents varchar( 32672 ) ) returns varchar( 4 ) for bit data\n" + |
| "language java parameter style java no sql deterministic\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UserDefinedAggregatesTest.makeBinary'\n" |
| ); |
| vetParameterizedAggregate |
| ( |
| conn, |
| "varbinaryMode_11", |
| "varchar( 4 ) for bit data", |
| "org.apache.derbyTesting.functionTests.tests.lang.LobMode$BinaryMode", |
| "( 1, makeVarbinary_11( 'abaa' ) ),\n" + |
| "( 1, makeVarbinary_11( 'abaa' ) ),\n" + |
| "( 1, makeVarbinary_11( 'abca' ) ),\n" + |
| "( 2, makeVarbinary_11( 'abaa' ) ),\n" + |
| "( 2, makeVarbinary_11( 'abca' ) ),\n" + |
| "( 2, makeVarbinary_11( 'abaa' ) ),\n" + |
| "( 3, makeVarbinary_11( 'aaaa' ) ),\n" + |
| "( 3, makeVarbinary_11( 'abcd' ) ),\n" + |
| "( 3, makeVarbinary_11( 'abcd' ) )\n", |
| new String[][] |
| { |
| { "61626161" }, |
| }, |
| new String[][] |
| { |
| { "1", "61626161" }, |
| { "2", "61626161" }, |
| { "3", "61626364" }, |
| }, |
| new String[][] |
| { |
| { "61626364" }, |
| }, |
| new String[][] |
| { |
| { "1", "61626361" }, |
| { "2", "61626361" }, |
| { "3", "61626364" }, |
| } |
| ); |
| |
| goodStatement |
| ( |
| conn, |
| "create function makeLongvarbinary_11( contents varchar( 32672 ) ) returns long varchar for bit data\n" + |
| "language java parameter style java no sql deterministic\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.UserDefinedAggregatesTest.makeBinary'\n" |
| ); |
| vetParameterizedAggregate |
| ( |
| conn, |
| "longvarbinaryMode_11", |
| "long varchar for bit data", |
| "org.apache.derbyTesting.functionTests.tests.lang.LobMode$BinaryMode", |
| "( 1, makeLongvarbinary_11( 'abaa' ) ),\n" + |
| "( 1, makeLongvarbinary_11( 'abaa' ) ),\n" + |
| "( 1, makeLongvarbinary_11( 'abca' ) ),\n" + |
| "( 2, makeLongvarbinary_11( 'abaa' ) ),\n" + |
| "( 2, makeLongvarbinary_11( 'abca' ) ),\n" + |
| "( 2, makeLongvarbinary_11( 'abaa' ) ),\n" + |
| "( 3, makeLongvarbinary_11( 'aaaa' ) ),\n" + |
| "( 3, makeLongvarbinary_11( 'abcd' ) ),\n" + |
| "( 3, makeLongvarbinary_11( 'abcd' ) )\n", |
| new String[][] |
| { |
| { "61626161" }, |
| }, |
| new String[][] |
| { |
| { "1", "61626161" }, |
| { "2", "61626161" }, |
| { "3", "61626364" }, |
| }, |
| null, |
| null |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "dateMode_11", |
| "date", |
| "org.apache.derbyTesting.functionTests.tests.lang.LobMode$DateMode", |
| "( 1, date( '1994-02-23' ) ),\n" + |
| "( 1, date( '1994-02-23' ) ),\n" + |
| "( 1, date( '1995-02-23' ) ),\n" + |
| "( 2, date( '1995-02-23' ) ),\n" + |
| "( 2, date( '1995-02-23' ) ),\n" + |
| "( 2, date( '1996-02-23' ) ),\n" + |
| "( 3, date( '1993-02-23' ) ),\n" + |
| "( 3, date( '1993-02-23' ) ),\n" + |
| "( 3, date( '1995-02-23' ) )\n", |
| new String[][] |
| { |
| { "1995-02-23" }, |
| }, |
| new String[][] |
| { |
| { "1", "1994-02-23" }, |
| { "2", "1995-02-23" }, |
| { "3", "1993-02-23" }, |
| }, |
| new String[][] |
| { |
| { "1996-02-23" }, |
| }, |
| new String[][] |
| { |
| { "1", "1995-02-23" }, |
| { "2", "1996-02-23" }, |
| { "3", "1995-02-23" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "timestampMode_11", |
| "timestamp", |
| "org.apache.derbyTesting.functionTests.tests.lang.LobMode$TimestampMode", |
| "( 1, timestamp( '1994-02-23 03:20:20' ) ),\n" + |
| "( 1, timestamp( '1994-02-23 03:20:20' ) ),\n" + |
| "( 1, timestamp( '1995-02-23 03:20:20' ) ),\n" + |
| "( 2, timestamp( '1995-02-23 03:20:20' ) ),\n" + |
| "( 2, timestamp( '1995-02-23 03:20:20' ) ),\n" + |
| "( 2, timestamp( '1996-02-23 03:20:20' ) ),\n" + |
| "( 3, timestamp( '1993-02-23 03:20:20' ) ),\n" + |
| "( 3, timestamp( '1993-02-23 03:20:20' ) ),\n" + |
| "( 3, timestamp( '1995-02-23 03:20:20' ) )\n", |
| new String[][] |
| { |
| { "1995-02-23 03:20:20.0" }, |
| }, |
| new String[][] |
| { |
| { "1", "1994-02-23 03:20:20.0" }, |
| { "2", "1995-02-23 03:20:20.0" }, |
| { "3", "1993-02-23 03:20:20.0" }, |
| }, |
| new String[][] |
| { |
| { "1996-02-23 03:20:20.0" }, |
| }, |
| new String[][] |
| { |
| { "1", "1995-02-23 03:20:20.0" }, |
| { "2", "1996-02-23 03:20:20.0" }, |
| { "3", "1995-02-23 03:20:20.0" }, |
| } |
| ); |
| |
| vetParameterizedAggregate |
| ( |
| conn, |
| "timeMode_11", |
| "time", |
| "org.apache.derbyTesting.functionTests.tests.lang.LobMode$TimeMode", |
| "( 1, time( '03:20:20' ) ),\n" + |
| "( 1, time( '03:20:20' ) ),\n" + |
| "( 1, time( '04:20:20' ) ),\n" + |
| "( 2, time( '04:20:20' ) ),\n" + |
| "( 2, time( '04:20:20' ) ),\n" + |
| "( 2, time( '05:20:20' ) ),\n" + |
| "( 3, time( '04:20:20' ) ),\n" + |
| "( 3, time( '04:20:20' ) ),\n" + |
| "( 3, time( '06:20:20' ) )\n", |
| new String[][] |
| { |
| { "04:20:20" }, |
| }, |
| new String[][] |
| { |
| { "1", "03:20:20" }, |
| { "2", "04:20:20" }, |
| { "3", "04:20:20" }, |
| }, |
| new String[][] |
| { |
| { "06:20:20" }, |
| }, |
| new String[][] |
| { |
| { "1", "04:20:20" }, |
| { "2", "05:20:20" }, |
| { "3", "06:20:20" }, |
| } |
| ); |
| |
| // XML is not a valid data type for UDA inputs and return values |
| expectCompilationError |
| ( |
| XML_TYPE, |
| "create derby aggregate xmlMode_11 for xml\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.LobMode'\n" |
| ); |
| } |
| |
| /** Blob-creating function */ |
| public static Blob makeBlob( String contents ) throws Exception |
| { |
| return new HarmonySerialBlob( makeBinary( contents ) ); |
| } |
| |
| private int vetDatatypeCount( Connection conn, int expectedTypeCount ) throws Exception |
| { |
| // |
| // If this fails, it means that we need to add another datatype to the |
| // calling test. |
| // |
| |
| ResultSet rs = conn.getMetaData().getTypeInfo(); |
| int actualTypeCount = 0; |
| while ( rs.next() ) { actualTypeCount++; } |
| rs.close(); |
| |
| assertEquals( expectedTypeCount, actualTypeCount ); |
| |
| return actualTypeCount; |
| } |
| |
| /** Blob-creating function */ |
| public static byte[] makeBinary( String contents ) throws Exception |
| { |
| return contents.getBytes( "UTF-8" ); |
| } |
| |
| /** Clob-creating function */ |
| public static Clob makeClob( String contents ) throws Exception |
| { |
| return new HarmonySerialClob( contents ); |
| } |
| |
| /** |
| * <p> |
| * Test implicit casts of input types. |
| * </p> |
| */ |
| public void test_12_coercion() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate charMode_12 for char( 4 )\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$StringMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table charMode_12_mode_inputs_small( a int, b char( 3 ) )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "insert into charMode_12_mode_inputs_small( a, b ) values ( 1, 'aaa' ), ( 1, 'aba' ), ( 1, 'aaa' ), ( 2, 'aba' ), ( 2, 'aba' ), ( 2, 'aba' ), ( 3, 'aba' ), ( 3, 'aba' ), ( 3, 'abc' )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table charMode_12_mode_inputs_big( a int, b char( 5 ) )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "insert into charMode_12_mode_inputs_big( a, b ) values ( 1, 'aaaaa' ), ( 1, 'abaaa' ), ( 1, 'aaaaa' ), ( 2, 'abaaa' ), ( 2, 'abaaa' ), ( 2, 'abcaa' ), ( 3, 'abaaa' ), ( 3, 'abaaa' ), ( 3, 'abcde' )" |
| ); |
| |
| // undersized char values are space-padded at the end |
| assertResults |
| ( |
| conn, |
| "select charMode_12( b ) from charMode_12_mode_inputs_small", |
| new String[][] |
| { |
| { "aba " }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, charMode_12( b ) from charMode_12_mode_inputs_small group by a", |
| new String[][] |
| { |
| { "1", "aaa " }, |
| { "2", "aba " }, |
| { "3", "aba " }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select charMode_12( distinct b ) from charMode_12_mode_inputs_small", |
| new String[][] |
| { |
| { "abc " }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, charMode_12( distinct b ) from charMode_12_mode_inputs_small group by a", |
| new String[][] |
| { |
| { "1", "aba " }, |
| { "2", "aba " }, |
| { "3", "abc " }, |
| }, |
| false |
| ); |
| |
| // oversized char values raise truncation errors |
| expectExecutionError |
| ( conn, STRING_TRUNCATION, "select charMode_12( b ) from charMode_12_mode_inputs_big" ); |
| expectExecutionError |
| ( conn, STRING_TRUNCATION, "select a, charMode_12( b ) from charMode_12_mode_inputs_big group by a" ); |
| expectExecutionError |
| ( conn, STRING_TRUNCATION, "select charMode_12( distinct b ) from charMode_12_mode_inputs_big" ); |
| expectExecutionError |
| ( conn, STRING_TRUNCATION, "select a, charMode_12( distinct b ) from charMode_12_mode_inputs_big group by a" ); |
| |
| // no problem running a BIGINT aggregator on INTs |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate bigintMode_12 for bigint\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$BigintMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table ints_12( a int, b int )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "insert into ints_12( a, b ) values ( 1, 1 ), ( 1, 2 ), (1, 2 ), ( 2, 2 ), ( 2, 3 ), ( 3, 3 ), ( 3, 4 ), ( 3, 5 )" |
| ); |
| assertResults |
| ( |
| conn, |
| "select bigintMode_12( b ) from ints_12", |
| new String[][] |
| { |
| { "2" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, bigintMode_12( b ) from ints_12 group by a", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| { "3", "5" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select bigintMode_12( distinct b ) from ints_12", |
| new String[][] |
| { |
| { "5" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, bigintMode_12( distinct b ) from ints_12 group by a", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "3" }, |
| { "3", "5" }, |
| }, |
| false |
| ); |
| |
| // but you can get runtime errors if you run an INT aggregate on oversized BIGINTs |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate intMode_12 for int\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$IntMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table bigints_12( a int, b bigint )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "insert into bigints_12( a, b ) values ( 1, 1000000000001 ), ( 1, 1000000000002 ), (1, 1000000000002 ), ( 2, 1000000000002 ), ( 2, 1000000000003 ), ( 3, 1000000000003 ), ( 3, 1000000000004 ), ( 3, 1000000000005 )" |
| ); |
| expectExecutionError |
| ( conn, INT_TRUNCATION, "select intMode_12( b ) from bigints_12" ); |
| expectExecutionError |
| ( conn, INT_TRUNCATION, "select a, intMode_12( b ) from bigints_12 group by a" ); |
| expectExecutionError |
| ( conn, INT_TRUNCATION, "select intMode_12( distinct b ) from bigints_12" ); |
| expectExecutionError |
| ( conn, INT_TRUNCATION, "select a, intMode_12( distinct b ) from bigints_12 group by a" ); |
| |
| // implicit cast from char to int fails |
| expectCompilationError( INPUT_MISMATCH, "select intMode_12( b ) from charMode_12_mode_inputs_small" ); |
| |
| // explict cast from char to int can fail at runtime |
| expectExecutionError |
| ( conn, CAST_FAILURE, "select intMode_12( cast (b as int) ) from charMode_12_mode_inputs_small" ); |
| |
| } |
| |
| /** |
| * <p> |
| * Test aggregates whose input and return types are different. |
| * </p> |
| */ |
| public void test_13_differentReturnType() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate intMagnitude_13 for int returns bigint\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.LongMagnitude'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate stringMagnitude_13 for int returns varchar( 10 )\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.LongMagnitude'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table intValues_13( a int, b int )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "insert into intValues_13 values ( 1, 1 ), ( 2, -100 ), ( 1, 2 ), ( 2, -1234 )" |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select intMagnitude_13( b ) from intValues_13", |
| new String[][] |
| { |
| { "1234" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, intMagnitude_13( b ) from intValues_13 group by a", |
| new String[][] |
| { |
| { "1", "2" }, |
| { "2", "1234" }, |
| }, |
| false |
| ); |
| |
| // the declared return type of the aggregate does not match the actual return type |
| expectCompilationError( RETURN_OUTSIDE_BOUNDS, "select stringMagnitude_13( b ) from intValues_13" ); |
| expectCompilationError( RETURN_OUTSIDE_BOUNDS, "select a, stringMagnitude_13( b ) from intValues_13 group by a" ); |
| } |
| |
| /** |
| * <p> |
| * Verify that user-defined aggregates are not allowed in GROUP BY clauses. |
| * </p> |
| */ |
| public void test_14_inGroupBy() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate intMode_14 for int\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$IntMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table intMode_14_mode_inputs( a int, b int )" |
| ); |
| |
| expectCompilationError |
| ( AGG_IN_GROUP_BY, |
| "select intMode_14( b ) from intMode_14_mode_inputs group by intMode_14( b )" ); |
| } |
| |
| /** |
| * <p> |
| * Verify precision mismatches. |
| * </p> |
| */ |
| public void test_15_precisionMismatch() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // truncating string types |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate varcharMode_15 for varchar( 4 )\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$StringMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table varcharMode_15_mode_inputs_big( a int, b varchar( 5 ) )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "insert into varcharMode_15_mode_inputs_big( a, b ) values ( 1, 'aaaaa' ), ( 1, 'abaaa' ), ( 1, 'aaaaa' ), ( 2, 'abaaa' ), ( 2, 'abaaa' ), ( 2, 'abcaa' ), ( 3, 'abaaa' ), ( 3, 'abaaa' ), ( 3, 'abcda' )" |
| ); |
| |
| expectExecutionError |
| ( conn, STRING_TRUNCATION, "select varcharMode_15( b ) from varcharMode_15_mode_inputs_big" ); |
| |
| // truncating numeric precision |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate numericMode_15_bigger for numeric( 5, 3 )\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$BigDecimalMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate numericMode_15 for numeric( 5, 1 )\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$BigDecimalMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table numericMode_15_mode_inputs_big( a int, b numeric( 5, 2 ) )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "insert into numericMode_15_mode_inputs_big( a, b ) values ( 1, 1.11 ), ( 1, 1.12 ), ( 1, 1.13 ), ( 1, 2.12 ), (1, 2.22 ), ( 2, 2.22 ), ( 2, 3.33 ), ( 3, 3.33 ), ( 3, 4.44 ), ( 3, 5.55 )" |
| ); |
| assertResults |
| ( |
| conn, |
| "select numericMode_15_bigger( b ) from numericMode_15_mode_inputs_big", |
| new String[][] |
| { |
| { "3.330" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select numericMode_15( b ) from numericMode_15_mode_inputs_big", |
| new String[][] |
| { |
| { "1.1" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, numericMode_15_bigger( b ) from numericMode_15_mode_inputs_big group by a", |
| new String[][] |
| { |
| { "1", "2.220" }, |
| { "2", "3.330" }, |
| { "3", "5.550" }, |
| }, |
| false |
| ); |
| assertResults |
| ( |
| conn, |
| "select a, numericMode_15( b ) from numericMode_15_mode_inputs_big group by a", |
| new String[][] |
| { |
| { "1", "1.1" }, |
| { "2", "3.3" }, |
| { "3", "5.5" }, |
| }, |
| false |
| ); |
| } |
| |
| /** |
| * <p> |
| * Verify that types fit within the most exact bound possible. |
| * </p> |
| */ |
| public void test_16_exactBound() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| // input bounds |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate bigintMode_16 for bigint\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$IntMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table bigintMode_16_mode_inputs( a int, b bigint )" |
| ); |
| expectCompilationError |
| ( INPUT_OUTSIDE_BOUNDS, |
| "select bigintMode_16( b ) from bigintMode_16_mode_inputs" ); |
| |
| // return bounds |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate intMode_16 for int returns varchar( 10 )\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode$IntMode'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create table intMode_16_mode_inputs( a int, b int )" |
| ); |
| expectCompilationError |
| ( RETURN_OUTSIDE_BOUNDS, |
| "select intMode_16( b ) from intMode_16_mode_inputs" ); |
| |
| } |
| |
| /** |
| * <p> |
| * Verify that you can't drop a user-defined type if a user-defined aggregate depends on it. |
| * </p> |
| */ |
| public void test_17_udtDependencies() throws Exception |
| { |
| Connection conn = getConnection(); |
| |
| goodStatement |
| ( |
| conn, |
| "create type Price_17 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create type Price_17_2 external name 'org.apache.derbyTesting.functionTests.tests.lang.Price' language java" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create derby aggregate priceMode_17 for Price_17 returns Price_17_2\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.GenericMode'\n" |
| ); |
| |
| // can't drop the types because the aggregate depends on them |
| expectExecutionError |
| ( conn, DEPENDENCY_VIOLATION, "drop type Price_17 restrict" ); |
| expectExecutionError |
| ( conn, DEPENDENCY_VIOLATION, "drop type Price_17_2 restrict" ); |
| |
| // once you drop the aggregate, you can drop the types |
| goodStatement |
| ( |
| conn, |
| "drop derby aggregate priceMode_17 restrict" |
| ); |
| goodStatement |
| ( |
| conn, |
| "drop type Price_17 restrict" |
| ); |
| goodStatement |
| ( |
| conn, |
| "drop type Price_17_2 restrict" |
| ); |
| } |
| |
| } |
