Google Git
Sign in
apache / derby / d07d421ba6bc6d96f3165a2af971eea663d8d4e1 / . / java / testing / org / apache / derbyTesting / functionTests / tests / lang / TableFunctionTest.java
blob: 193b0c7c6489f228fc50a1bac84e22b288f1c6cc [file] [log] [blame]
/*
Derby - Class org.apache.derbyTesting.functionTests.tests.lang.TableFunctionTest
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.io.LineNumberReader;
import java.io.StringReader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Types;
import java.text.NumberFormat;
import java.util.Arrays;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.Decorator;
import org.apache.derbyTesting.junit.JDBC;
/**
* Test Table Functions. See DERBY-716 for a description of
* this feature.
*/
public class TableFunctionTest extends BaseJDBCTestCase
{
///////////////////////////////////////////////////////////////////////////////////
//
// CONSTANTS
//
///////////////////////////////////////////////////////////////////////////////////
private static final String UTF8 = "UTF-8";
private static final String BAD_ARG_JOIN = "42ZB7";
private static final int MAX_VARIABLE_DATA_TYPE_LENGTH = 32700;
// functions to drop at teardown time
private static final String[] FUNCTION_NAMES =
{
"SIMPLEFUNCTIONTABLE",
"invert",
"returnsACoupleRows",
"getXXXrecord",
"returnsAllLegalDatatypes",
"missingConstructor",
"zeroArgConstructorNotPublic",
"constructorException",
"goodVTICosting",
"allStringTypesFunction",
"coercionFunction",
};
// tables to drop at teardown time
private static final String[] TABLE_NAMES =
{
"allStringTypesTable",
"fooTestTable",
};
private static final String[][] SIMPLE_ROWS =
{
{ "who", "put" },
{ "the", "bop" },
{ (String) null, "in" },
{ "the", (String) null },
};
private static final String[][] SIMPLY_ROWS =
{
{ "the ", (String) null },
{ "the ", "bop " },
{ "who ", "put " },
{ (String) null, "in " },
};
private static final String[][] DOUBLY_SIMPLE_ROWS =
{
{ "the ", (String) null },
{ "the ", "bop " },
{ "the ", (String) null },
{ "the ", "bop " },
{ "who ", "put " },
{ "who ", "put " },
{ (String) null, "in " },
{ (String) null, "in " },
};
private static final String[][] BOOLEAN_ROWS =
{
{ "tRuE", "true" },
{ "fAlSe", "false" },
};
private static final String[][] BULK_INSERT_ROWS =
{
{ "1", "red" },
{ "2", "blue" },
};
private static final String[][] BULK_INSERT_SELF_JOIN_ROWS =
{
{ "2", "blue" },
};
private static final String[][] DOUBLY_INSERTED_ROWS =
{
{ "1", "red" },
{ "1", "red" },
{ "2", "blue" },
{ "2", "blue" },
};
private static final String[][] WARNING_VTI_ROWS =
{
{ "1", "red" },
{ "2", "blue" },
};
private static final String[][] ALL_TYPES_ROWS =
{
{
null, // BIGINT
null, // BLOB
null, // CHAR
null, // CHAR FOR BIT DATA
null, // CLOB
null, // DATE
null, // DECIMAL
null, // DOUBLE
null, // DOUBLE PRECISION
null, // FLOAT( 23 )
null, // FLOAT( 24 )
null, // INTEGER
null, // LONG VARCHAR
null, // LONG VARCHAR FOR BIT DATA
null, // NUMERIC
null, // REAL
null, // SMALLINT
null, // TIME
null, // TIMESTAMP
null, // VARCHAR
null, // VARCHAR FOR BIT DATA
null, // BOOLEAN
},
};
private static final String EXPECTED_GET_XXX_CALLS =
"getLong " + // BIGINT
"getBlob " + // BLOB
"getString " + // CHAR
"getBytes " + // CHAR FOR BIT DATA
"getString " + // CLOB
"getDate " + // DATE
"getBigDecimal " + // DECIMAL
"getDouble " + // DOUBLE
"getDouble " + // DOUBLE PRECISION
"getFloat " + // FLOAT( 23 )
"getDouble " + // FLOAT( 24 )
"getInt " + // INTEGER
"getString " + // LONG VARCHAR
"getBytes " + // LONG VARCHAR FOR BIT DATA
"getBigDecimal " + // NUMERIC
"getFloat " + // REAL
"getShort " + // SMALLINT
"getTime " + // TIME
"getTimestamp " + // TIMESTAMP
"getString " + // VARCHAR
"getBytes " + // VARCHAR FOR BIT DATA
"getBoolean "; // BOOLEAN
private static final String EXPECTED_GET_XXX_CALLS_JSR169 =
"getLong " + // BIGINT
"getBlob " + // BLOB
"getString " + // CHAR
"getBytes " + // CHAR FOR BIT DATA
"getString " + // CLOB
"getDate " + // DATE
"getString " + // DECIMAL
"getDouble " + // DOUBLE
"getDouble " + // DOUBLE PRECISION
"getFloat " + // FLOAT( 23 )
"getDouble " + // FLOAT( 24 )
"getInt " + // INTEGER
"getString " + // LONG VARCHAR
"getBytes " + // LONG VARCHAR FOR BIT DATA
"getString " + // NUMERIC
"getFloat " + // REAL
"getShort " + // SMALLINT
"getTime " + // TIME
"getTimestamp " + // TIMESTAMP
"getString " + // VARCHAR
"getBytes " + // VARCHAR FOR BIT DATA
"getBoolean "; // BOOLEAN
private static final String[] STRING_TYPES =
{
"CHAR( 20 )",
//"CLOB", long string types are not comparable
//"LONG VARCHAR", long string types are not comparable
"VARCHAR( 20 )",
};
private static final int[] STRING_JDBC_TYPES =
{
Types.CHAR,
//Types.CLOB, long string types are not comparable
//Types.LONGVARCHAR, long string types are not comparable
Types.VARCHAR,
};
private static final String[][] ALL_STRING_TYPES_ROWS =
{
{
"char col ", // CHAR
//"clob col", long string types are not comparable
//"long varchar col", // LONG VARCHAR long string types are not comparable
"varchar col", // VARCHAR
},
};
private static final String SFT_RETURN_TYPE = "TABLE ( \"INTCOL\" INTEGER, \"VARCHARCOL\" VARCHAR(10) )";
private static final String RADT_RETURN_TYPE = "TABLE ( \"COLUMN0\" BIGINT, \"COLUMN1\" BLOB(2147483647), \"COLUMN2\" CHAR(10), \"COLUMN3\" CHAR (10) FOR BIT DATA, \"COLUMN4\" CLOB(2147483647), \"COLUMN5\" DATE, \"COLUMN6\" DECIMAL(5,0), \"COLUMN7\" DOUBLE, \"COLUMN8\" DOUBLE, \"COLUMN9\" REAL, \"COLUMN10\" DOUBLE, \"COLUMN11\" INTEGER, \"COLUMN12\" LONG VARCHAR, \"COLUMN13\" LONG VARCHAR FOR BIT DATA, \"COLUMN14\" NUMERIC(5,0), \"COLUMN15\" REAL, \"COLUMN16\" SMALLINT, \"COLUMN17\" TIME, \"COLUMN18\" TIMESTAMP, \"COLUMN19\" VARCHAR(10), \"COLUMN20\" VARCHAR (10) FOR BIT DATA, \"COLUMN21\" BOOLEAN )";
private static final Integer FUNCTION_COLUMN_IN = DatabaseMetaData.functionColumnIn;
private static final Integer FUNCTION_RETURN_VALUE = DatabaseMetaData.functionReturn;
private static final Integer FUNCTION_RESULT_COLUMN = DatabaseMetaData.functionColumnResult;
private static final Integer FUNCTION_RETURNS_TABLE = DatabaseMetaData.functionReturnsTable;
private static final Integer JDBC_TYPE_OTHER = Types.OTHER ;
private static final Integer JDBC_TYPE_INT = Types.INTEGER ;
private static final Integer JDBC_TYPE_VARCHAR = Types.VARCHAR ;
private static final Integer JDBC_TYPE_BIGINT = Types.BIGINT ;
private static final Integer JDBC_TYPE_BLOB = Types.BLOB ;
private static final Integer JDBC_TYPE_CHAR = Types.CHAR ;
private static final Integer JDBC_TYPE_CLOB = Types.CLOB ;
private static final Integer JDBC_TYPE_DATE = Types.DATE ;
private static final Integer JDBC_TYPE_DECIMAL = Types.DECIMAL ;
private static final Integer JDBC_TYPE_DOUBLE = Types.DOUBLE ;
private static final Integer JDBC_TYPE_REAL = Types.REAL ;
private static final Integer JDBC_TYPE_NUMERIC = Types.NUMERIC ;
private static final Integer JDBC_TYPE_SMALLINT = Types.SMALLINT ;
private static final Integer JDBC_TYPE_TIME = Types.TIME ;
private static final Integer JDBC_TYPE_TIMESTAMP = Types.TIMESTAMP ;
private static final Integer JDBC_TYPE_BINARY = Types.BINARY ;
private static final Integer JDBC_TYPE_LONGVARBINARY = Types.LONGVARBINARY ;
private static final Integer JDBC_TYPE_LONGVARCHAR = Types.LONGVARCHAR ;
private static final Integer JDBC_TYPE_VARBINARY = Types.VARBINARY ;
private static final Integer JDBC_TYPE_BOOLEAN = Types.BOOLEAN ;
private static final Integer PRECISION_NONE = 0 ;
private static final Integer PRECISION_INTEGER = 10 ;
private static final Integer PRECISION_BIGINT = 19 ;
private static final Integer PRECISION_MAX = 2147483647 ;
private static final Integer LENGTH_UNDEFINED = -1 ;
private static final Integer LENGTH_INTEGER = 4 ;
private static final Integer LENGTH_BIGINT = 40 ;
private static final Integer LENGTH_MAX = 2147483647 ;
private static final Integer SCALE_UNDEFINED = null;
private static final Integer SCALE_INTEGER = 0 ;
private static final Integer RADIX_UNDEFINED = null;
private static final Integer RADIX_INTEGER = 10 ;
private static final Object NO_CATALOG = null;
private static final String RETURN_VALUE_NAME = "";
private static final Integer ALLOWS_NULLS = DatabaseMetaData.functionNullable;
private static final Object EMPTY_REMARKS = null;
private static final Object UNDEFINED_CHAR_OCTET_LENGTH = null;
private static final String IS_NULLABLE = "YES";
private static final Integer ROW_ORDER_RETURN_VALUE = -1 ;
private static final Integer ROW_ORDER_1 = 0 ;
private static final Integer ROW_ORDER_2 = 1 ;
private static final Integer POSITION_RETURN_VALUE = 0 ;
private static final Integer POSITION_ARG_1 = 1 ;
private static final Integer POSITION_ARG_2 = 2 ;
private static final Integer ARG_COUNT_0 = 0 ;
private static final Integer ARG_COUNT_1 = 1 ;
private static final Integer ARG_COUNT_2 = 2 ;
private static final Integer ARG_COUNT_3 = 3 ;
private static final JDBC.GeneratedId GENERIC_NAME = new JDBC.GeneratedId();
/** Expected rows from getFunctions() for SIMPLEFUNCTIONTABLE */
private static final Object[][] GF_SFT= {
{ NO_CATALOG, "APP", "SIMPLEFUNCTIONTABLE", "com.scores.proc.Functions.weighQuestion", FUNCTION_RETURNS_TABLE, GENERIC_NAME }
};
/** Expected rows from getFunctionColumns() for SIMPLEFUNCTIONTABLE */
private static final Object[][] GFC_SFT= {
{
NO_CATALOG,
"APP",
"SIMPLEFUNCTIONTABLE",
"INTCOL",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_INT,
"INTEGER",
PRECISION_INTEGER,
LENGTH_INTEGER,
SCALE_INTEGER,
RADIX_INTEGER,
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
1 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_0,
ROW_ORDER_1
},
{
NO_CATALOG,
"APP",
"SIMPLEFUNCTIONTABLE",
"VARCHARCOL",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_VARCHAR,
"VARCHAR",
10 , // PRECISION
20 , // LENGTH
SCALE_UNDEFINED,
RADIX_UNDEFINED,
ALLOWS_NULLS,
EMPTY_REMARKS,
20 , // CHAR_OCTET_LENGTH
2 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_0,
ROW_ORDER_2
},
};
/** Expected rows from getFunctions() for returnsAllLegalDatatypes */
private static final Object[][] GF_RADT= {
{ NO_CATALOG, "APP", "RETURNSALLLEGALDATATYPES", "org.apache.derbyTesting.functionTests.tests.lang.TableFunctionTest.returnsAllLegalDatatypes", FUNCTION_RETURNS_TABLE, GENERIC_NAME }
};
/** Expected rows from getFunctionColumns() for returnsAllLegalDatatypes */
private static final Object[][] GFC_RADT= {
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"INTARGUMENT",
FUNCTION_COLUMN_IN,
JDBC_TYPE_INT,
"INTEGER",
PRECISION_INTEGER,
LENGTH_INTEGER,
SCALE_INTEGER,
RADIX_INTEGER,
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
POSITION_ARG_1,
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
ROW_ORDER_1
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"VARCHARARGUMENT",
FUNCTION_COLUMN_IN,
JDBC_TYPE_VARCHAR,
"VARCHAR",
10 , // PRECISION
20 , // LENGTH
SCALE_UNDEFINED,
RADIX_UNDEFINED,
ALLOWS_NULLS,
EMPTY_REMARKS,
20 , // CHAR_OCTET_LENGTH
POSITION_ARG_2,
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
ROW_ORDER_2
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN0",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_BIGINT,
"BIGINT",
PRECISION_BIGINT,
LENGTH_BIGINT,
SCALE_INTEGER,
RADIX_INTEGER,
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
1 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
2
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN1",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_BLOB,
"BLOB",
PRECISION_MAX ,
LENGTH_MAX,
SCALE_UNDEFINED,
RADIX_UNDEFINED,
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
2 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
3
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN2",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_CHAR,
"CHAR",
10 , // PRECISION
20 , // LENGTH
SCALE_UNDEFINED,
RADIX_UNDEFINED,
ALLOWS_NULLS,
EMPTY_REMARKS,
20 , // CHAR_OCTET_LENGTH
3 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
4
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN3",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_BINARY,
"CHAR () FOR BIT DATA",
10 , // PRECISION
10 , // LENGTH
SCALE_UNDEFINED,
RADIX_UNDEFINED,
ALLOWS_NULLS,
EMPTY_REMARKS,
10 , // CHAR_OCTET_LENGTH
4 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
5
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN4",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_CLOB,
"CLOB",
PRECISION_MAX , // PRECISION
LENGTH_MAX, // LENGTH
SCALE_UNDEFINED,
RADIX_UNDEFINED,
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
5 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
6
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN5",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_DATE,
"DATE",
10 , // PRECISION
6 , // LENGTH
0 , // SCALE
10 , // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
6 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
7
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN6",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_DECIMAL,
"DECIMAL",
5 , // PRECISION
14 , // LENGTH
0 , // SCALE
10 , // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
7 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
8
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN7",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_DOUBLE,
"DOUBLE",
52 , // PRECISION
8 , // LENGTH
SCALE_UNDEFINED, // SCALE
2 , // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
8 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
9
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN8",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_DOUBLE,
"DOUBLE",
52 , // PRECISION
8 , // LENGTH
SCALE_UNDEFINED, // SCALE
2 , // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
9 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
10
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN9",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_REAL,
"REAL",
23 , // PRECISION
4 , // LENGTH
SCALE_UNDEFINED, // SCALE
2 , // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
10 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
11
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN10",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_DOUBLE,
"DOUBLE",
52 , // PRECISION
8 , // LENGTH
SCALE_UNDEFINED, // SCALE
2 , // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
11 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
12
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN11",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_INT,
"INTEGER",
PRECISION_INTEGER , // PRECISION
LENGTH_INTEGER, // LENGTH
SCALE_INTEGER, // SCALE
RADIX_INTEGER, // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
12 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
13
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN12",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_LONGVARCHAR,
"LONG VARCHAR",
32700 , // PRECISION
65400 , // LENGTH
SCALE_UNDEFINED, // SCALE
RADIX_UNDEFINED, // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
13 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
14
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN13",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_LONGVARBINARY,
"LONG VARCHAR FOR BIT DATA",
32700 , // PRECISION
32700 , // LENGTH
SCALE_UNDEFINED, // SCALE
RADIX_UNDEFINED, // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
14 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
15
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN14",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_NUMERIC,
"NUMERIC",
5 , // PRECISION
14 , // LENGTH
0 , // SCALE
10 , // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
15 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
16
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN15",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_REAL,
"REAL",
23 , // PRECISION
4 , // LENGTH
SCALE_UNDEFINED, // SCALE
2 , // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
16 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
17
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN16",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_SMALLINT,
"SMALLINT",
5 , // PRECISION
2 , // LENGTH
SCALE_INTEGER, // SCALE
RADIX_INTEGER, // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
17 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
18
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN17",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_TIME,
"TIME",
8 , // PRECISION
6 , // LENGTH
SCALE_INTEGER, // SCALE
RADIX_INTEGER, // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
18 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
19
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN18",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_TIMESTAMP,
"TIMESTAMP",
29 , // PRECISION
16 , // LENGTH
9 , // SCALE
RADIX_INTEGER, // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH,
19 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
20
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN19",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_VARCHAR,
"VARCHAR",
10 , // PRECISION
20 , // LENGTH
SCALE_UNDEFINED, // SCALE
RADIX_UNDEFINED, // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
20 , // CHAR_OCTET_LENGTH
20 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
21
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN20",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_VARBINARY,
"VARCHAR () FOR BIT DATA",
10 , // PRECISION
10 , // LENGTH
SCALE_UNDEFINED, // SCALE
RADIX_UNDEFINED, // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
10 , // CHAR_OCTET_LENGTH
21 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
22
},
{
NO_CATALOG,
"APP",
"RETURNSALLLEGALDATATYPES",
"COLUMN21",
FUNCTION_RESULT_COLUMN,
JDBC_TYPE_BOOLEAN,
"BOOLEAN",
1 , // PRECISION
1 , // LENGTH
SCALE_UNDEFINED, // SCALE
RADIX_UNDEFINED, // RADIX
ALLOWS_NULLS,
EMPTY_REMARKS,
UNDEFINED_CHAR_OCTET_LENGTH, // CHAR_OCTET_LENGTH
22 , // ORDINAL_POSITION
IS_NULLABLE,
GENERIC_NAME,
ARG_COUNT_2,
23
},
};
private static final String ESTIMATED_ROW_COUNT = "optimizer estimated row count:";
private static final String ESTIMATED_COST = "optimizer estimated cost:";
///////////////////////////////////////////////////////////////////////////////////
//
// INNER CLASSES
//
///////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////
//
// STATE
//
///////////////////////////////////////////////////////////////////////////////////
private boolean _usingLocaleSpecificCollation;
private DatabaseMetaData _databaseMetaData;
///////////////////////////////////////////////////////////////////////////////////
//
// CONSTRUCTORS
//
///////////////////////////////////////////////////////////////////////////////////
public TableFunctionTest
(
String name
)
{
super( name );
}
///////////////////////////////////////////////////////////////////////////////////
//
// JUnit MACHINERY
//
///////////////////////////////////////////////////////////////////////////////////
/**
* Tests to run.
*/
public static Test suite()
{
BaseTestSuite suite = new BaseTestSuite( "TableFunctionTest" );
suite.addTest( new TableFunctionTest( "noSpecialCollation" ) );
suite.addTest( collatedSuite( "en", "specialCollation" ) );
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
* @param baseFixture Base fixture from this test.
* @return suite of tests to run for the given locale
*/
private static Test collatedSuite(String locale, String baseFixture)
{
BaseTestSuite suite =
new BaseTestSuite( "TableFunctionTest:territory=" + locale );
suite.addTest( new TableFunctionTest( baseFixture ) );
return Decorator.territoryCollatedDatabase( suite, locale );
}
protected void setUp()
throws Exception
{
super.setUp();
_databaseMetaData = getConnection().getMetaData();
dropSchema();
}
protected void tearDown()
throws Exception
{
dropSchema();
_databaseMetaData = null;
super.tearDown();
}
///////////////////////////////////////////////////////////////////////////////////
//
// JUnit TESTS
//
///////////////////////////////////////////////////////////////////////////////////
/**
* Verify table functions in a vanilla database without locale-specific collations.
*/
public void noSpecialCollation()
throws Exception
{
_usingLocaleSpecificCollation = false;
tableFunctionTest();
}
/**
* Verify table functions in a database with a special collation.
*/
public void specialCollation()
throws Exception
{
_usingLocaleSpecificCollation = true;
tableFunctionTest();
}
/**
* Verify table functions.
*/
public void tableFunctionTest()
throws Exception
{
badDDL();
simpleDDL();
notTableFunction();
simpleVTIResults();
allLegalDatatypesVTIResults();
vtiCosting();
collationTest();
subqueryTest();
coercionTest();
bulkInsert();
miscBugs();
classpathError();
}
/**
* Verify bad DDL.
*/
private void badDDL()
throws Exception
{
//
// Only table functions can have parameter style DERBY_JDBC_RESULT_SET
//
expectError
(
"42ZB1",
"create function badParameterStyle()\n" +
"returns varchar(10)\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name 'com.scores.proc.Functions.weighQuestion'\n"
);
//
// Procedures can not have parameter style DERBY_JDBC_RESULT_SET
//
expectError
(
"42ZB1",
"create procedure badParameterStyle\n" +
"( in takingID int )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"modifies sql data\n" +
"external name 'com.scores.proc.Procedures.ScoreTestTaking'\n"
);
//
// Table functions must have parameter style DERBY_JDBC_RESULT_SET
//
expectError
(
"42ZB2",
"create function badParameterStyleForTableFunction()\n" +
"returns TABLE\n" +
" (\n" +
" intCol int,\n" +
" varcharCol varchar( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style java\n" +
"no sql\n" +
"external name 'com.scores.proc.Functions.weighQuestion'\n"
);
//
// XML column types not allowed in table functions.
//
expectError
(
"42ZB3",
"create function xmlForbiddenInReturnedColumns()\n" +
"returns TABLE\n" +
" (\n" +
" intCol int,\n" +
" xmlCol xml\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name 'com.scores.proc.Functions.weighQuestion'\n"
);
}
/**
* Verify simple good DDL.
*/
private void simpleDDL()
throws Exception
{
goodStatement
(
"create function simpleFunctionTable()\n" +
"returns TABLE\n" +
" (\n" +
" intCol int,\n" +
" varcharCol varchar( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name 'com.scores.proc.Functions.weighQuestion'\n"
);
verifyReturnType
(
"SIMPLEFUNCTIONTABLE",
"weighQuestion() " +
"RETURNS " + SFT_RETURN_TYPE +
" LANGUAGE JAVA PARAMETER STYLE DERBY_JDBC_RESULT_SET NO SQL CALLED ON NULL INPUT"
);
assertFunctionDBMD( "SIMPLEFUNCTIONTABLE", GF_SFT , GFC_SFT );
}
/**
* Verify that you can't invoke an ordinary function as a VTI.
*/
private void notTableFunction()
throws Exception
{
goodStatement
(
"create function invert( intValue int )\n" +
"returns int\n" +
"language java\n" +
"parameter style java\n" +
"no sql\n" +
"external name '" + getClass().getName() + ".invert'\n"
);
//
// Can't invoke a simple function as a table function.
//
expectError
(
"42ZB4",
"select s.*\n" +
" from TABLE( invert( 1 ) ) s\n"
);
}
/**
* test for DERBY-5585
*/
private void classpathError()
throws Exception
{
goodStatement
(
"create function foo( a int )\n" +
"returns int\n" +
"language java\n" +
"parameter style java\n" +
"no sql\n" +
"external name 'Bop.doowop'\n"
);
expectError
(
"42X51",
"values ( foo( 1 ) )"
);
goodStatement
(
"create function bar( a int )\n" +
"returns int\n" +
"language java\n" +
"parameter style java\n" +
"no sql\n" +
"external name 'java.lang.Integer.doowop'\n"
);
expectError
(
"42X50",
"values ( bar( 1 ) )"
);
}
/**
* Verify that a simple VTI returns the correct results.
*/
private void simpleVTIResults()
throws Exception
{
goodStatement
(
"create function returnsACoupleRows()\n" +
"returns TABLE\n" +
" (\n" +
" column0 varchar( 10 ),\n" +
" column1 varchar( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name '" + getClass().getName() + ".returnsACoupleRows'\n"
);
assertResults
(
"select s.*\n" +
" from TABLE( returnsACoupleRows() ) s\n",
SIMPLE_ROWS,
new int[] { Types.VARCHAR, Types.VARCHAR }
);
goodStatement
(
"create function returnsACoupleRowsAsCHAR()\n" +
"returns TABLE\n" +
" (\n" +
" column0 char( 10 ),\n" +
" column1 char( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name '" + getClass().getName() + ".returnsACoupleRows'\n"
);
String[][] CHAR_ROWS = new String[SIMPLE_ROWS.length][];
for (int r = 0; r < CHAR_ROWS.length; r++)
{
CHAR_ROWS[r] = new String[SIMPLE_ROWS[r].length];
for (int c = 0; c < CHAR_ROWS[r].length; c++)
{
String cv = SIMPLE_ROWS[r][c];
if (cv != null)
{
if (cv.length() < 10)
{
StringBuffer sb = new StringBuffer(cv);
for (int p = cv.length(); p < 10; p++)
sb.append(' ');
CHAR_ROWS[r][c] = sb.toString();
}
}
}
}
assertResults
(
"select s.*\n" +
" from TABLE( returnsACoupleRowsAsCHAR() ) s\n",
CHAR_ROWS,
new int[] { Types.CHAR, Types.CHAR }
);
// boolean valued columns
goodStatement
(
"create function returnsBooleans()\n" +
"returns TABLE\n" +
" (\n" +
" column0 varchar( 10 ),\n" +
" column1 boolean\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name '" + getClass().getName() + ".returnsBooleans'\n"
);
assertResults
(
"select s.*\n" +
" from TABLE( returnsBooleans() ) s\n",
BOOLEAN_ROWS,
new int[] { Types.VARCHAR, Types.BOOLEAN }
);
}
/**
* Verify bulk insert using a VTI
*/
private void bulkInsert()
throws Exception
{
Connection conn = getConnection();
goodStatement
(
"create table bulkInsertTable\n" +
" (\n" +
" column0 int,\n" +
" column1 varchar( 10 )\n" +
" )\n"
);
goodStatement
(
"create table biSourceTable\n" +
" (\n" +
" column0 int,\n" +
" column1 varchar( 10 )\n" +
" )\n"
);
goodStatement
(
"create function bulkInsertVTI()\n" +
"returns TABLE\n" +
" (\n" +
" column0 int,\n" +
" column1 varchar( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name '" + getClass().getName() + ".bulkInsertVTI'\n"
);
goodStatement
(
"create view bulkInsertView( column0, column1 ) as select column0, column1\n" +
"from table( bulkInsertVTI() ) s\n"
);
goodStatement
(
"insert into biSourceTable select * from bulkInsertView\n"
);
//Test table with null value on bulk insert
goodStatement
(
"create table bulkInsertSimpleTable\n" +
" (\n" +
" column0 varchar( 10 ),\n" +
" column1 varchar( 10 )\n" +
" )\n"
);
//
// Inserting from a table function into an empty table should trigger
// the bulk-insert optimization, resulting in a new conglomerate for
// the target table
//
// Inserting from a table function into a non-empty table should NOT trigger
// the bulk-insert optimization. The conglomerate number of the target table
// should not change.
//
vetBulkInsert
(
conn,
"insert into bulkInsertTable select * from table( bulkInsertVTI() ) s",
true
);
// You still get bulk-insert if you wrap the table function in a view
vetBulkInsert
(
conn,
"insert into bulkInsertTable select * from bulkInsertView",
true
);
// You still get bulk-insert if it is a union that wrap a table
// function
vetBulkInsert
(
conn,
"insert into bulkInsertTable select * from table( bulkInsertVTI() ) s union select * from table (bulkInsertVTI()) t",
true
);
// You still get bulk-insert if it is a table function wrap subquery
vetBulkInsert
(
conn,
"insert into bulkInsertTable select * from table( bulkInsertVTI()) b where b.column0 in (select c.column0 from table( bulkInsertVTI()) c)",
true
);
// You still get bulk-insert if it is a self join that wrap a table
// function in a view
goodStatement("delete from bulkInsertTable");
vetBulkInsert
(
conn,
"insert into bulkInsertTable select * from bulkInsertView b where 1 = (select count(*) from bulkInsertView bc where b.column0 > bc.column0)",
true,
BULK_INSERT_SELF_JOIN_ROWS
);
// You don't get bulk-insert if you insert from an ordinary table
vetBulkInsert
(
conn,
"insert into bulkInsertTable select * from biSourceTable",
false
);
vetBulkInsert
(
conn,
"insert into bulkInsertTable select * from table( bulkInsertVTI() ) s",
true
);
// You still get bulk-insert if you wrap the table function in a view
vetBulkInsertSimple
(
conn,
"insert into bulkInsertSimpleTable select * from table(RETURNSACOUPLEROWSASCHAR()) r",
true
);
// You still get bulk-insert if it is a union that wrap a table
// function
vetBulkInsertSimple
(
conn,
"insert into bulkInsertSimpleTable select * from table( RETURNSACOUPLEROWSASCHAR() ) s union select * from table ( RETURNSACOUPLEROWSASCHAR() ) t",
true
);
// You still get bulk-insert if it is a table function wrap subquery
vetBulkInsertSimple
(
conn,
"insert into bulkInsertSimpleTable select c.column0, c.column1 from table( RETURNSACOUPLEROWSASCHAR() ) c left outer join (select * from table( RETURNSACOUPLEROWSASCHAR() ) d) e on c.column0 = e.column0 and c.column1 = e.column1",
true
);
}
private void vetBulkInsert( Connection conn, String insert, boolean bulkInsertExpected )
throws Exception
{
goodStatement( "delete from bulkInsertTable" );
vetBulkInsert( conn, insert, bulkInsertExpected, BULK_INSERT_ROWS );
//
// Inserting from a table function into a non-empty table should NOT trigger
// the bulk-insert optimization. The conglomerate number of the target table
// should not change.
//
vetBulkInsert( conn, insert, false, DOUBLY_INSERTED_ROWS );
}
private void vetBulkInsert( Connection conn, String insert, boolean bulkInsertExpected, String[][] expectedRows )
throws Exception
{
long originalConglomerateID = getConglomerateID( conn, "BULKINSERTTABLE" );
goodStatement( insert );
long conglomerateIDAfterInsert = getConglomerateID( conn, "BULKINSERTTABLE" );
assertEquals( bulkInsertExpected, originalConglomerateID != conglomerateIDAfterInsert );
assertResults
(
"select * from bulkInsertTable order by column0",
expectedRows,
new int[] { Types.INTEGER, Types.VARCHAR }
);
}
private void vetBulkInsertSimple( Connection conn, String insert, boolean bulkInsertSimpleExpected )
throws Exception
{
goodStatement( "delete from bulkInsertSimpleTable" );
vetBulkInsertSimple( conn, insert, bulkInsertSimpleExpected, SIMPLY_ROWS );
//
// Inserting from a table function into a non-empty table should NOT triOgger
// the bulk-insert optimization. The conglomerate number of the target table
// should not change.
//
vetBulkInsertSimple( conn, insert, false, DOUBLY_SIMPLE_ROWS );
}
private void vetBulkInsertSimple( Connection conn, String insert, boolean bulkInsertSimpleExpected, String[][] expectedRows )
throws Exception
{
long originalConglomerateID = getConglomerateID( conn, "BULKINSERTSIMPLETABLE" );
goodStatement( insert );
long conglomerateIDAfterInsert = getConglomerateID( conn, "BULKINSERTSIMPLETABLE" );
assertEquals( bulkInsertSimpleExpected, originalConglomerateID != conglomerateIDAfterInsert );
assertResults
(
"select * from bulkInsertSimpleTable order by column0",
expectedRows,
new int[] { Types.VARCHAR, Types.VARCHAR }
);
}
/**
* Verify that Derby handles VTI columns of all known datatypes.
*/
private void allLegalDatatypesVTIResults()
throws Exception
{
goodStatement
(
"create function getXXXrecord()\n" +
"returns varchar( 1000 )\n" +
"language java\n" +
"parameter style java\n" +
"no sql\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI.getXXXrecord'\n"
);
goodStatement
(
"create function returnsAllLegalDatatypes( intArgument int, varcharArgument varchar( 10 ) )\n" +
"returns TABLE\n" +
" (\n" +
"column0 BIGINT,\n" +
"column1 BLOB,\n" +
"column2 CHAR( 10 ),\n" +
"column3 CHAR( 10 ) FOR BIT DATA,\n" +
"column4 CLOB,\n" +
"column5 DATE,\n" +
"column6 DECIMAL,\n" +
"column7 DOUBLE,\n" +
"column8 DOUBLE PRECISION,\n" +
"column9 FLOAT( 23 ),\n" +
"column10 FLOAT( 24 ),\n" +
"column11 INTEGER,\n" +
"column12 LONG VARCHAR,\n" +
"column13 LONG VARCHAR FOR BIT DATA,\n" +
"column14 NUMERIC,\n" +
"column15 REAL,\n" +
"column16 SMALLINT,\n" +
"column17 TIME,\n" +
"column18 TIMESTAMP,\n" +
"column19 VARCHAR( 10 ),\n" +
"column20 VARCHAR( 10 ) FOR BIT DATA,\n" +
"column21 BOOLEAN\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name '" + getClass().getName() + ".returnsAllLegalDatatypes'\n"
);
assertResults
(
"select s.*\n" +
" from TABLE( returnsAllLegalDatatypes( 1, 'one' ) ) s\n",
ALL_TYPES_ROWS,
new int[]
{
Types.BIGINT,
Types.BLOB,
Types.CHAR,
Types.BINARY,
Types.CLOB,
Types.DATE,
Types.DECIMAL,
Types.DOUBLE,
Types.DOUBLE,
Types.REAL,
Types.DOUBLE,
Types.INTEGER,
Types.LONGVARCHAR,
Types.LONGVARBINARY,
Types.NUMERIC,
Types.REAL,
Types.SMALLINT,
Types.TIME,
Types.TIMESTAMP,
Types.VARCHAR,
Types.VARBINARY,
Types.BOOLEAN,
}
);
assertFunctionDBMD( "RETURNSALLLEGALDATATYPES", GF_RADT , GFC_RADT );
checkGetXXXCalls();
}
/**
* Verify that the correct getXXX() methods are called by Derby. If Derby
* changes so that different getXXX() methods are called for these
* datatypes, then the user documentation will have to be adjusted. These
* are the methods which we tell users they must implement.
*/
private void checkGetXXXCalls()
throws Exception
{
int datatypeCount = ALL_TYPES_ROWS[ 0 ].length;
StringBuffer buffer = new StringBuffer();
buffer.append( "select s.*\n" );
buffer.append( " from TABLE( returnsAllLegalDatatypes( 1, 'one' ) ) s\n" );
buffer.append( " where\n" );
for ( int i = 0; i < datatypeCount; i++ )
{
String rc = "s.column" + i;
if ( i > 0 ) { buffer.append( " and " ); }
buffer.append( "( " + rc + " is null )\n" );
}
assertResults
(
buffer.toString(),
ALL_TYPES_ROWS,
new int[]
{
Types.BIGINT,
Types.BLOB,
Types.CHAR,
Types.BINARY,
Types.CLOB,
Types.DATE,
Types.DECIMAL,
Types.DOUBLE,
Types.DOUBLE,
Types.REAL,
Types.DOUBLE,
Types.INTEGER,
Types.LONGVARCHAR,
Types.LONGVARBINARY,
Types.NUMERIC,
Types.REAL,
Types.SMALLINT,
Types.TIME,
Types.TIMESTAMP,
Types.VARCHAR,
Types.VARBINARY,
Types.BOOLEAN,
}
);
PreparedStatement ps = prepareStatement( "values getXXXrecord()" );
ResultSet rs = ps.executeQuery();
rs.next();
String actualGetXXXCalls = rs.getString( 1 );
rs.close();
ps.close();
println( actualGetXXXCalls );
String expectedGetXXXCalls;
if ( JDBC.vmSupportsJSR169() )
{ expectedGetXXXCalls = EXPECTED_GET_XXX_CALLS_JSR169; }
else { expectedGetXXXCalls = EXPECTED_GET_XXX_CALLS; }
assertEquals( expectedGetXXXCalls, actualGetXXXCalls );
}
/**
* Verify the VTICosting optimizer api.
*/
private void vtiCosting()
throws Exception
{
//
// Doesn't have a public no-arg constructor.
//
goodStatement
(
"create function missingConstructor()\n" +
"returns TABLE\n" +
" (\n" +
" varcharCol varchar( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI$MissingConstructor.dummyVTI'\n"
);
expectError
(
"42ZB5",
"select s.*\n" +
" from TABLE( missingConstructor() ) s\n"
);
//
// Has a no-arg constructor but it isn't public.
//
goodStatement
(
"create function zeroArgConstructorNotPublic()\n" +
"returns TABLE\n" +
" (\n" +
" varcharCol varchar( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI$ZeroArgConstructorNotPublic.dummyVTI'\n"
);
expectError
(
"42ZB5",
"select s.*\n" +
" from TABLE( missingConstructor() ) s\n"
);
//
// Has a public, no-arg constructor but it raises an exception.
//
goodStatement
(
"create function constructorException()\n" +
"returns TABLE\n" +
" (\n" +
" varcharCol varchar( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI$ConstructorException.dummyVTI'\n"
);
expectError
(
"38000",
"select s.*\n" +
" from TABLE( constructorException() ) s\n"
);
//
// Good implementation of VTICosting. Verify that the optimizer costs
// are overridden.
//
goodStatement
(
"create function goodVTICosting()\n" +
"returns TABLE\n" +
" (\n" +
" varcharCol varchar( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI$GoodVTICosting.dummyVTI'\n"
);
String optimizerStats = getOptimizerStats
(
"select s.*\n" +
" from TABLE( goodVTICosting() ) s\n"
);
assertEquals( StringArrayVTI.FAKE_ROW_COUNT, readDoubleTag( optimizerStats, ESTIMATED_ROW_COUNT ), 0.0 );
assertEquals( StringArrayVTI.FAKE_INSTANTIATION_COST, readDoubleTag( optimizerStats, ESTIMATED_COST ), 0.0 );
}
/**
* Verify that Derby uses the same collation logic on columns in real Tables
* and in Table Functions.
*/
private void collationTest()
throws Exception
{
assertEquals( STRING_TYPES.length, ALL_STRING_TYPES_ROWS[ 0 ].length );
StringBuffer rowSet = new StringBuffer();
int stringTypeCount = STRING_TYPES.length;
rowSet.append( "(\n" );
for ( int i = 0; i < stringTypeCount; i++ )
{
rowSet.append( '\t' );
if ( i > 0 ) { rowSet.append( ", " ); }
rowSet.append( "column" + i + " " + STRING_TYPES[ i ] + "\n" );
}
rowSet.append( ")\n" );
goodStatement
(
"create table allStringTypesTable\n" +
rowSet.toString()
);
goodStatement
(
"create function allStringTypesFunction()\n" +
"returns TABLE\n" +
rowSet.toString() +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name '" + getClass().getName() + ".allStringTypesFunction'\n"
);
// populate table
StringBuffer insertSql = new StringBuffer();
insertSql.append( "insert into allStringTypesTable values\n" );
insertSql.append( "(\n" );
for ( int i = 0; i < stringTypeCount; i++ )
{
if ( i > 0 ) { insertSql.append( ", " ); }
insertSql.append( "?" );
}
insertSql.append( ")\n" );
PreparedStatement ps = chattyPrepare( insertSql.toString() );
int rowCount = ALL_STRING_TYPES_ROWS.length;
for ( int i = 0; i < rowCount; i++ )
{
for ( int j = 0; j < stringTypeCount; j++ )
{
ps.setString( j + 1, ALL_STRING_TYPES_ROWS[ i ][ j ] );
}
ps.execute();
}
ps.close();
// now verify that the string columns in the table are comparable to the
// string columns returned by the function. they would not be comparable
// if they had different collations.
StringBuffer compareRows = new StringBuffer();
compareRows.append
(
"select f.*\n" +
" from TABLE( allStringTypesFunction() ) f,\n" +
" allStringTypesTable t\n" +
"where\n" );
for ( int i = 0; i < stringTypeCount; i++ )
{
String fcol = "f.column" + i;
String tcol = "t.column" + i;
if ( i > 0 ) { compareRows.append( " and " ); }
compareRows.append( fcol + " = " + tcol );
}
assertResults
(
compareRows.toString(),
ALL_STRING_TYPES_ROWS,
STRING_JDBC_TYPES
);
// now verify that with default collation, we can compare the function
// columns to system identifiers. however, with locale-specific
// collations, these comparisons should fail.
compareRows = new StringBuffer();
compareRows.append
(
"select f.*\n" +
" from TABLE( allStringTypesFunction() ) f,\n" +
" sys.systables t\n" +
"where\n" );
for ( int i = 0; i < stringTypeCount; i++ )
{
String fcol = "f.column" + i;
String tcol = "t.tablename";
if ( i > 0 ) { compareRows.append( " and " ); }
compareRows.append( fcol + " = " + tcol );
}
if ( _usingLocaleSpecificCollation )
{
expectError
(
"42818",
compareRows.toString()
);
}
else
{
assertResults
(
compareRows.toString(),
new String[][] {},
STRING_JDBC_TYPES
);
}
}
/**
* Verify that table functions work correctly when invoked in
* a subuery with correlated references to outer query blocks.
*/
private void subqueryTest()
throws Exception
{
goodStatement
(
"create table fooTestTable\n" +
"(\n" +
" inputCol varchar( 20 ),\n" +
" outputCol varchar( 30 )\n" +
")\n"
);
goodStatement
(
"insert into fooTestTable\n" +
"values\n" +
"( 'succeed1', 'succeed1 foo' ),\n" +
"( 'fail1', 'ladeedah' ),\n" +
"( 'succeed2', 'succeed2 bar' ),\n" +
"( 'fail2', 'hoopla' )\n"
);
goodStatement
(
"create function appendFooAndBar( inputArg varchar( 20 ) )\n" +
"returns TABLE\n" +
" (\n" +
" inputText varchar( 20 ),\n" +
" outputText varchar( 30 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.TableFunctionTest.appendFooAndBar'\n"
);
assertResults
(
"select * from fooTestTable\n" +
"where outputCol in\n" +
"(\n" +
" select f.outputText\n" +
" from TABLE( appendFooAndBar( inputCol ) ) as f\n" +
")\n",
new String[] { "INPUTCOL", "OUTPUTCOL" },
new String[][]
{
{ "succeed1", "succeed1 foo" },
{ "succeed2", "succeed2 bar" },
},
new int[] { Types.VARCHAR, Types.VARCHAR }
);
}
/**
* Verify that variable length data values are coerced to their
* declared types, regardless of what actually is returned by the
* user-coded ResultSet. See DERBY-3341.
*/
private void coercionTest()
throws Exception
{
goodStatement
(
"create function coercionFunction( )\n" +
"returns TABLE\n" +
" (\n" +
" keyCol int,\n" +
" charCol char( 5 ),\n" +
" varcharCol varchar( 5 ),\n" +
" charForBitDataCol char( 5 ) for bit data,\n" +
" varcharForBitDataCol varchar( 5 ) for bit data,\n" +
" decimalCol decimal( 5, 2 ),\n" +
" longvarcharCol long varchar,\n" +
" longvarcharForBitDataCol long varchar for bit data\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.TableFunctionTest.coercionFunction'\n"
);
assertResults
(
"select *\n" +
"from TABLE( coercionFunction( ) ) as f order by keyCol\n",
new String[]
{
"KEYCOL",
"CHARCOL",
"VARCHARCOL",
"CHARFORBITDATACOL",
"VARCHARFORBITDATACOL",
"DECIMALCOL",
"LONGVARCHARCOL",
"LONGVARCHARFORBITDATACOL",
},
makeCoercionOutputs(),
new int[]
{
Types.INTEGER,
Types.CHAR,
Types.VARCHAR,
Types.BINARY,
Types.VARBINARY,
Types.DECIMAL,
Types.LONGVARCHAR,
Types.LONGVARBINARY,
}
);
}
/**
* <p>
* Miscellaneous bugs.
* </p>
*/
private void miscBugs()
throws Exception
{
derby_4092();
derby_5779();
derby_6040();
derby_6151();
}
/**
* <p>
* Don't allow table functions to appear where scalar functions are expected.
* </p>
*/
private void derby_4092()
throws Exception
{
goodStatement
(
"create function derby_4092()\n" +
"returns TABLE\n" +
" (\n" +
" column0 varchar( 10 ),\n" +
" column1 varchar( 10 )\n" +
" )\n" +
"language java\n" +
"parameter style DERBY_JDBC_RESULT_SET\n" +
"no sql\n" +
"external name '" + getClass().getName() + ".returnsACoupleRows'\n"
);
expectError
(
"42ZB6",
"values( derby_4092() )"
);
expectError
(
"42ZB6",
"select derby_4092(), tablename from sys.systables"
);
}
/**
* <p>
* Don't allow table functions to take arguments built out of references
* to other tables in the FROM list of their own query block.
* </p>
*/
private void derby_5779()
throws Exception
{
goodStatement
(
"create function lowerCaseRow( contents varchar( 32672 ) )\n" +
"returns table\n" +
"(\n" +
" contents varchar( 32672 )\n" +
")\n" +
"language java parameter style DERBY_JDBC_RESULT_SET no sql\n" +
"external name '" + getClass().getName() + ".lowerCaseRow'\n"
);
goodStatement
(
"create table t_5779( a int )\n"
);
// constant arguments are ok
assertResults
(
"select contents column0 from table( lowerCaseRow( 'FOO' ) ) t\n",
new String[][] { { "foo" } },
new int[] { Types.VARCHAR }
);
// ? parameters still ok as arguments
PreparedStatement ps = prepareStatement
( "select contents from table( lowerCaseRow( ? ) ) t\n" );
ps.setString( 1, "FOO" );
ResultSet rs = ps.executeQuery();
assertResults
(
new int[] { Types.VARCHAR },
new String[] { "CONTENTS" },
rs,
new String[][] { { "foo" } }
);
rs.close();
ps.close();
// constant arguments in subquery are ok
assertResults
(
"select tablename column0\n" +
"from sys.systables t\n" +
"where lower( cast (tablename as varchar( 32672 )) ) in\n" +
"( select contents from table( lowerCaseRow( 'SYSCOLUMNS' ) ) s )\n",
new String[][] { { "SYSCOLUMNS" } },
new int[] { Types.VARCHAR }
);
// table function correlated to outer query block is ok
assertResults
(
"select tablename column0\n" +
"from sys.systables t\n" +
"where lower( cast (tablename as varchar( 32672 )) ) in\n" +
"( select contents from table( lowerCaseRow( cast (t.tablename as varchar(32672)) ) ) s )\n" +
"and length( tablename ) = 16\n",
new String[][] { { "SYSCONGLOMERATES" } },
new int[] { Types.VARCHAR }
);
// vti arguments can still reference tables in the same query block.
assertResults
(
"select t2.conglomeratename column0\n" +
" from \n" +
" sys.systables systabs,\n" +
" table (syscs_diag.space_table(systabs.tablename)) as t2\n" +
" where cast (systabs.tablename as varchar(10)) = 'T_5779'\n",
new String[][] { { "T_5779" } },
new int[] { Types.VARCHAR }
);
// uncorrelated inner query blocks still unaffected
assertResults
(
"select contents column0\n" +
"from table( lowerCaseRow( 'FOO' ) ) s\n" +
"where exists ( select tableid from sys.systables t )\n",
new String[][] { { "foo" } },
new int[] { Types.VARCHAR }
);
// should fail. table function correlated to table in FROM list
// of same query block. this is the new error condition introduced
// by DERBY-5779.
expectError
(
BAD_ARG_JOIN,
"select tablename, contents\n" +
"from sys.systables t, table( lowerCaseRow( cast (t.tablename as varchar(32672)) ) ) s\n"
);
// diagnostic vti arg joining to another table in a <joined table> clause
expectError
(
BAD_ARG_JOIN,
"select tt.* from table(syscs_diag.space_table(st.tablename)) tt join sys.systables st using(tableid)"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from sys.systables st join table(syscs_diag.space_table(st.tablename)) tt using(tableid)"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from table(syscs_diag.space_table(st.tablename)) tt right join sys.systables st using(tableid)"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from sys.systables st right join table(syscs_diag.space_table(st.tablename)) tt using(tableid)"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from table(syscs_diag.space_table(st.tablename)) tt left join sys.systables st using(tableid)"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from sys.systables st left join table(syscs_diag.space_table(st.tablename)) tt using(tableid)"
);
// table function arg joining to another table in a <joined table> clause
expectError
(
BAD_ARG_JOIN,
"select tt.* from table( lowerCaseRow(st.tablename)) tt join sys.systables st on tt.contents = st.tablename"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from sys.systables st join table( lowerCaseRow(st.tablename)) tt on tt.contents = st.tablename"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from table( lowerCaseRow(st.tablename)) tt right join sys.systables st on tt.contents = st.tablename"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from sys.systables st right join table( lowerCaseRow(st.tablename)) tt on tt.contents = st.tablename"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from table( lowerCaseRow(st.tablename)) tt left join sys.systables st on tt.contents = st.tablename"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from sys.systables st left join table( lowerCaseRow(st.tablename)) tt on tt.contents = st.tablename"
);
// 3-way <joined table>
expectError
(
BAD_ARG_JOIN,
"select tt.* from ( table(syscs_diag.space_table('foo')) tt join sys.systables st using(tableid) ) join table(syscs_diag.space_table(st.tablename)) tr using(tableid)"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from ( table( lowerCaseRow('foo')) tt join sys.systables st on tt.contents = st.tablename ) join table( lowerCaseRow(st.tablename)) tr on tr.contents = st.tablename"
);
// cross joins
expectError
(
BAD_ARG_JOIN,
"select tt.* from table(syscs_diag.space_table(st.tablename)) tt cross join sys.systables st"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from table( lowerCaseRow(st.tablename)) tt cross join sys.systables st"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from ( table(syscs_diag.space_table('foo')) tt cross join sys.systables st ) cross join table(syscs_diag.space_table(st.tablename)) tr"
);
expectError
(
BAD_ARG_JOIN,
"select tt.* from ( table( lowerCaseRow('foo')) tt cross join sys.systables st ) cross join table( lowerCaseRow(st.tablename)) tr"
);
// subqueries in the FROM list
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" sys.systables systabs,\n" +
" ( select * from table (syscs_diag.space_table( systabs.tablename )) as t2 ) tt\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" sys.systables systabs,\n" +
" ( select * from table (lowerCaseRow( systabs.tablename )) as t2 ) tt\n" +
" where systabs.tabletype = 'T' and systabs.tablename = tt.contents\n"
);
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" ( select tablename from table (syscs_diag.space_table( systabs.tablename )) as t2 ) tt,\n" +
" sys.systables systabs\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" ( select * from table (lowerCaseRow( systabs.tablename )) as t2 ) tt,\n" +
" sys.systables systabs\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
// union subquery
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" sys.systables systabs,\n" +
" (\n" +
" select columnname from sys.syscolumns\n" +
" union\n" +
" select tablename from table (syscs_diag.space_table( systabs.tablename )) as t2\n" +
" ) tt\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" (\n" +
" select columnname from sys.syscolumns\n" +
" union\n" +
" select tablename from table (syscs_diag.space_table( systabs.tablename )) as t2\n" +
" ) tt,\n" +
" sys.systables systabs\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" sys.systables systabs,\n" +
" (\n" +
" select columnname from sys.syscolumns\n" +
" union\n" +
" select contents from table (lowerCaseRow( systabs.tablename )) as t2\n" +
" ) tt\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" (\n" +
" select columnname from sys.syscolumns\n" +
" union\n" +
" select contents from table (lowerCaseRow( systabs.tablename )) as t\n" +
" ) tt,\n" +
" sys.systables systabs\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
// nested subqueries
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" sys.systables systabs,\n" +
" (\n" +
" select * from\n" +
" sys.syscolumns col,\n" +
" ( select tablename from table (syscs_diag.space_table( systabs.tablename )) as t2 ) ti\n" +
" where col.columnname = ti.tablename\n" +
" ) tt\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" sys.systables systabs,\n" +
" (\n" +
" select * from\n" +
" sys.syscolumns col,\n" +
" ( select contents from table (lowerCaseRow( systabs.tablename )) as t2 ) ti\n" +
" where col.columnname = ti.contents\n" +
" ) tt\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" (\n" +
" select * from\n" +
" sys.syscolumns col,\n" +
" ( select tablename from table (syscs_diag.space_table( systabs.tablename )) as t2 ) ti\n" +
" where col.columnname = ti.tablename\n" +
" ) tt,\n" +
" sys.systables systabs\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
expectError
(
BAD_ARG_JOIN,
"select tt.*\n" +
" from\n" +
" (\n" +
" select * from\n" +
" sys.syscolumns col,\n" +
" ( select contents from table (lowerCaseRow( systabs.tablename )) as t2 ) ti\n" +
" where col.columnname = ti.contents\n" +
" ) tt,\n" +
" sys.systables systabs\n" +
" where systabs.tabletype = 'T' and systabs.tableid = tt.tableid\n"
);
// pre-existing error not affected: table function correlated
// to inner query block
expectError
(
"42X04",
"select contents\n" +
"from table( lowerCaseRow( cast( t.tablename as varchar(32672)) ) ) s\n" +
"where exists ( select tableid from sys.systables t )\n"
);
}
/**
* <p>
* Make sure that ORDER BY columns are not mistakenly pruned
* when projection eliminates a column and one of the columns is
* compared to a constant.
* </p>
*/
private void derby_6040()
throws Exception
{
// this test uses varargs routines, which aren't available unless the VM
// is at least at level 5
if ( JDBC.vmSupportsJSR169() ) { return; }
goodStatement
(
"create function leftTable\n" +
"(\n" +
" columnNames varchar( 32672 ),\n" +
" rowContents varchar( 32672 ) ...\n" +
")\n" +
"returns table\n" +
"(\n" +
" a0 varchar( 5 ),\n" +
" a1 varchar( 5 ),\n" +
" a2 varchar( 5 ),\n" +
" a3 varchar( 5 )\n" +
")\n" +
"language java parameter style derby_jdbc_result_set no sql\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.stringArrayTable'\n"
);
goodStatement
(
"create function rightTable\n" +
"(\n" +
" columnNames varchar( 32672 ),\n" +
" rowContents varchar( 32672 ) ...\n" +
")\n" +
"returns table\n" +
"(\n" +
" b1 varchar( 5 ),\n" +
" b2 varchar( 5 ),\n" +
" b3 varchar( 5 )\n" +
")\n" +
"language java parameter style derby_jdbc_result_set no sql\n" +
"external name 'org.apache.derbyTesting.functionTests.tests.lang.VarargsRoutines.stringArrayTable'\n"
);
assertResults
(
"select l.a2 column0, r.b3 column1\n" +
"from\n" +
" table( leftTable\n" +
" (\n" +
" 'A0 A1 A2 A3',\n" +
" 'X APP T Z',\n" +
" 'X APP S Z'\n" +
" ) ) l,\n" +
" table( rightTable\n" +
" (\n" +
" 'B1 B2 B3',\n" +
" 'APP T A',\n" +
" 'APP T B',\n" +
" 'APP S A',\n" +
" 'APP S B'\n" +
" ) ) r\n" +
"where r.b2 = l.a2\n" +
"and l.a3 = 'Z'\n" +
"and r.b1 = l.a1\n" +
"order by column0, column1\n",
new String[][]
{
{ "S", "A" },
{ "S", "B" },
{ "T", "A" },
{ "T", "B" },
},
new int[] { Types.VARCHAR, Types.VARCHAR }
);
}
/**
* <p>
* Verify that warnings percolate back from table functions.
* </p>
*/
private void derby_6151()
throws Exception
{
goodStatement
(
"create function warningVTI() returns table( a int, b varchar( 5 ) )\n" +
"language java parameter style derby_jdbc_result_set no sql\n" +
"external name '" + getClass().getName() + ".warningVTI'\n"
);
ResultSet rs = getConnection().prepareStatement( "select * from table( warningVTI() ) t" ).executeQuery();
rs.next();
assertEquals( "Warning for row 1", rs.getWarnings().getMessage() );
rs.clearWarnings();
rs.next();
assertEquals( "Warning for row 2", rs.getWarnings().getMessage() );
rs.close();
goodStatement( "drop function warningVTI" );
}
/**
* <p>
* Make the input rows for the coercion function.
* </p>
*/
private static String[][] makeCoercionInputs()
{
return new String[][]
{
{ "1", "abc", "abc", "abc", "abc", "12.3", makeString( 5 ), makeByteString( 5 ) }, // too short
{ "2", "abcdef", "abcdef", "abcdef", "abcdef", "12.345", makeString( 32700 + 1 ), makeByteString( 32700 + 1 ) }, // too long
{ "3", "abcde", "abcde", "abcde", "abcde", "123.45", makeString( 5 ), makeByteString( 5 ) }, // just right
};
}
/**
* <p>
* Make the expected output rows which should come back from the coercion function.
* </p>
*/
private static String[][] makeCoercionOutputs()
{
return new String[][]
{
{ "1", "abc ", "abc", "abc ", "abc", "12.30", makeString( 5 ), makeByteString( 5 ) },
{ "2", "abcde", "abcde", "abcde", "abcde", "12.34", makeString( 32700 ), makeByteString( 32700 ) },
{ "3", "abcde", "abcde", "abcde", "abcde", "123.45", makeString( 5 ), makeByteString( 5 ) },
};
}
/**
* <p>
* Return a String of the specified length.
* </p>
*/
private static String makeString( int count )
{
char[] raw = new char[ count ];
Arrays.fill( raw, 'a' );
return new String( raw );
}
/**
* <p>
* Return a String encoding a byte array of the specified length.
* </p>
*/
private static String makeByteString( int count )
{
try {
byte[] raw = new byte[ count ];
byte value = (byte) 1;
Arrays.fill( raw, value );
return new String( raw, UTF8 );
}
catch (Throwable t)
{
println( t.getMessage() );
return null;
}
}
///////////////////////////////////////////////////////////////////////////////////
//
// Derby FUNCTIONS
//
///////////////////////////////////////////////////////////////////////////////////
/**
* Just a simple function which is not a VTI.
*/
public static int invert( int value )
{
return -value;
}
/**
* A VTI which returns some boolean values
*/
public static ResultSet returnsBooleans()
{
return makeVTI( BOOLEAN_ROWS );
}
/**
* A VTI which returns a couple rows.
*/
public static ResultSet returnsACoupleRows()
{
return makeVTI( SIMPLE_ROWS );
}
/**
* A VTI for use in bulk insert
*/
public static ResultSet bulkInsertVTI()
{
return makeVTI( BULK_INSERT_ROWS );
}
/**
* A VTI which returns rows having columns of all legal datatypes.
*/
public static ResultSet returnsAllLegalDatatypes( int intArg, String varcharArg )
{
return makeVTI( ALL_TYPES_ROWS );
}
/**
* A VTI which returns rows having columns of all string datatypes.
*/
public static ResultSet allStringTypesFunction()
{
return makeVTI( ALL_STRING_TYPES_ROWS );
}
/**
* A VTI which returns rows based on a passed-in parameter
*/
public static ResultSet appendFooAndBar( String text )
{
String[][] kernel = new String[][]
{
{ text, text + " foo" },
{ text, text + " bar" },
};
return makeVTI( kernel );
}
/**
* A VTI which returns variable-length data typed columns.
*/
public static ResultSet coercionFunction()
{
return makeVTI( makeCoercionInputs() );
}
/**
* A table function which returns one row, containing one column, the lowercased
* content string.
*/
public static ResultSet lowerCaseRow( String contents )
{
return makeVTI( new String[][] { new String[] { contents.toLowerCase() } } );
}
///////////////////////////////////////////////////////////////////////////////////
//
// MINIONS
//
///////////////////////////////////////////////////////////////////////////////////
/**
* Assert that the ResultSet returns the desired rows.
*/
public void assertResults( String sql, String[][] rows, int[] expectedJdbcTypes )
throws Exception
{
String[] columnNames = makeColumnNames( expectedJdbcTypes.length, "COLUMN" );
assertResults( sql, columnNames, rows, expectedJdbcTypes );
}
/**
* Assert that the ResultSet returns the desired rows.
*/
public void assertResults( String sql, String[] columnNames, String[][] rows, int[] expectedJdbcTypes )
throws Exception
{
println( "\nExpecting good results from " + sql );
PreparedStatement ps = prepareStatement( sql );
ResultSet rs = ps.executeQuery();
assertResults( expectedJdbcTypes, columnNames, rs, rows );
rs.close();
ps.close();
}
/**
* Assert that the statement text, when compiled, raises an exception
*/
private void expectError( String sqlState, String query )
{
println( "\nExpecting " + sqlState + " when preparing:\n\t" + query );
assertCompileError( sqlState, query );
}
/**
* Run good DDL.
* @throws SQLException
*/
private void goodStatement( String ddl ) throws SQLException
{
PreparedStatement ps = chattyPrepare( ddl );
ps.execute();
ps.close();
}
/**
* Prepare a statement and report its sql text.
*/
private PreparedStatement chattyPrepare( String text )
throws SQLException
{
println( "Preparing statement:\n\t" + text );
return prepareStatement( text );
}
/**
* Verify that the return type of function looks good.
* @throws SQLException
*/
private void verifyReturnType( String functionName, String expectedReturnType ) throws SQLException
{
println( functionName + " should have return type = " + expectedReturnType );
String ddl = "select aliasinfo from sys.sysaliases where alias=?";
PreparedStatement ps = prepareStatement(ddl);
ps.setString(1, functionName);
JDBC.assertSingleValueResultSet(ps.executeQuery(), expectedReturnType);
}
/**
* Assert that the function has the correct database metadata.
*/
public void assertFunctionDBMD
( String functionName, Object[][] expectedGetFunctionsResult, Object[][] expectedGetFunctionColumnsResult )
throws Exception
{
println( "\nExpecting correct function metadata from " + functionName );
ResultSet rs =
_databaseMetaData.getFunctions( null, "APP", functionName );
JDBC.assertFullResultSet( rs, expectedGetFunctionsResult, false );
rs.close();
println( "\nExpecting correct function column metadata from " + functionName );
rs = _databaseMetaData.getFunctionColumns( null, "APP", functionName, "%" );
//prettyPrint( getConnection(), getFunctionColumns( null, "APP", functionName, "%" ) );
JDBC.assertFullResultSet( rs, expectedGetFunctionColumnsResult, false );
rs.close();
}
/**
* Drop the schema that we are going to use so that we can recreate it.
*/
private void dropSchema()
throws Exception
{
int functionCount = FUNCTION_NAMES.length;
for ( int i = 0; i < functionCount; i++ ) { dropFunction( FUNCTION_NAMES[ i ] ); }
int tableCount = TABLE_NAMES.length;
for ( int i = 0; i < tableCount; i++ ) { dropTable( TABLE_NAMES[ i ] ); }
}
/**
* Drop a function so that we can recreate it.
*/
private void dropFunction( String functionName )
throws Exception
{
// swallow the "object doesn't exist" diagnostic
try {
PreparedStatement ps = prepareStatement( "drop function " + functionName );
ps.execute();
ps.close();
}
catch( SQLException se) {}
}
/**
* Assert that the ResultSet returns the desired rows.
*/
private void assertResults( int[] expectedJdbcTypes, String[] columnNames, ResultSet rs, String[][] rows )
throws Exception
{
int rowCount = rows.length;
int[] actualJdbcTypes = getJdbcColumnTypes( rs );
compareJdbcTypes( expectedJdbcTypes, actualJdbcTypes );
compareColumnNames( columnNames, rs );
for ( int i = 0; i < rowCount; i++ )
{
String[] row = rows[ i ];
int columnCount = row.length;
assertTrue( rs.next() );
for ( int j = 0; j < columnCount; j++ )
{
String columnName = columnNames[ j ];
String expectedValue = row[ j ];
String actualValue = null;
String actualValueByName = null;
int column = j+1;
int actualJdbcType = actualJdbcTypes[ j ];
switch( actualJdbcType )
{
case Types.BOOLEAN:
actualValue = Boolean.toString( rs.getBoolean( column ) );
actualValueByName = Boolean.toString( rs.getBoolean( columnName ) );
if ( rs.wasNull() ) { actualValue = actualValueByName = null; }
break;
case Types.BIGINT:
actualValue = Long.toString( rs.getLong( column ) );
actualValueByName = Long.toString( rs.getLong( columnName ) ).toString();
if ( rs.wasNull() ) { actualValue = actualValueByName = null; }
break;
case Types.INTEGER:
actualValue = Integer.toString( rs.getInt( column ) );
actualValueByName = Integer.toString( rs.getInt( columnName ) );
if ( rs.wasNull() ) { actualValue = actualValueByName = null; }
break;
case Types.SMALLINT:
actualValue = Short.toString( rs.getShort( column ) );
actualValueByName = Short.toString( rs.getShort( columnName ) );
if ( rs.wasNull() ) { actualValue = actualValueByName = null; }
break;
case Types.TINYINT:
actualValue = Byte.toString( rs.getByte( column ) );
actualValueByName = Byte.toString( rs.getByte( columnName ) );
if ( rs.wasNull() ) { actualValue = actualValueByName = null; }
break;
case Types.DOUBLE:
actualValue = Double.toString( rs.getDouble( column ) );
actualValueByName = Double.toString( rs.getDouble( columnName ) );
if ( rs.wasNull() ) { actualValue = actualValueByName = null; }
break;
case Types.REAL:
case Types.FLOAT:
actualValue = Float.toString( rs.getFloat( column ) );
actualValueByName = Float.toString( rs.getFloat( columnName ) );
if ( rs.wasNull() ) { actualValue = actualValueByName = null; }
break;
case Types.DECIMAL:
case Types.NUMERIC:
// with JSR169, we cannot execute resultSet.getBigDecimal...
if (JDBC.vmSupportsJDBC3()) {
actualValue = squeezeString( rs.getBigDecimal( column ) );
actualValueByName = squeezeString( rs.getBigDecimal( columnName ) );
break;
}
else {
actualValue = squeezeString( rs.getString( column ) );
actualValueByName = squeezeString( rs.getString( columnName ) );
break;
}
case Types.DATE:
actualValue = squeezeString( rs.getDate( column ) );
actualValueByName = squeezeString( rs.getDate( columnName ) );
break;
case Types.TIME:
actualValue = squeezeString( rs.getTime( column ) );
actualValueByName = squeezeString( rs.getTime( columnName ) );
break;
case Types.TIMESTAMP:
actualValue = squeezeString( rs.getTimestamp( column ) );
actualValueByName = squeezeString( rs.getTimestamp( columnName ) );
break;
case Types.BLOB:
Blob blob = rs.getBlob(column);
actualValue = squeezeString(blob);
actualValueByName = squeezeString(blob);
break;
case Types.CLOB:
Clob clob = rs.getClob(column);
actualValue = squeezeString(clob);
actualValueByName = squeezeString(clob);
break;
case Types.BINARY:
case Types.VARBINARY:
case Types.LONGVARBINARY:
byte[] bytes = rs.getBytes( column );
actualValue = squeezeString( bytes );
actualValueByName = squeezeString( rs.getBytes( columnName ) );
break;
case Types.JAVA_OBJECT:
actualValue = squeezeString( rs.getObject( column ) );
actualValueByName = squeezeString( rs.getObject( columnName ) );
break;
case Types.CHAR:
case Types.LONGVARCHAR:
case Types.VARCHAR:
actualValue = rs.getString( column );
actualValueByName = rs.getString( columnName );
break;
default:
fail( "Can't handle jdbc type " + actualJdbcType );
}
//println( "Comparing " + expectedValue + " to " + actualValue + " and " + actualValueByName );
if ( actualValue == null ) { assertNull( actualValueByName ); }
else { assertTrue( actualValue.equals( actualValueByName ) ); }
assertEquals( (expectedValue == null), rs.wasNull() );
if ( expectedValue == null ) { assertNull( actualValue ); }
else { assertEquals(expectedValue, actualValue); }
}
}
assertFalse( rs.next() );
}
/**
* Verify that we saw the jdbc types that we expected.
*/
private void compareJdbcTypes( int[] expected, int[] actual )
throws Exception
{
int count = expected.length;
assertEquals( count, actual.length );
for ( int i = 0; i < count; i++ )
{
assertEquals( "Type at position " + i, expected[ i ], actual[ i ] );
}
}
/**
* Verify that we have the correct column names.
*/
private void compareColumnNames( String[] expectedNames, ResultSet rs )
throws Exception
{
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
println( "Expecting " + expectedNames.length + " columns." );
assertEquals( expectedNames.length, count );
for ( int i = 0; i < count; i++ )
{
assertEquals( expectedNames[ i ], rsmd.getColumnName( i+1 ) );
}
}
/**
* Get the datatypes of returned columns
*/
private int[] getJdbcColumnTypes( ResultSet rs )
throws Exception
{
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
int[] actualJdbcTypes = new int[ count ];
for ( int i = 0; i < count; i++ ) { actualJdbcTypes[ i ] = rsmd.getColumnType( i + 1 ); }
return actualJdbcTypes;
}
/**
* Squeeze a string out of an object
*/
private String squeezeString( Object obj )
throws Exception
{
if ( obj == null ) { return null; }
else if ( obj instanceof Blob )
{
Blob blob = (Blob) obj;
return new String( blob.getBytes( (long) 0, (int) blob.length() ), UTF8 );
}
else if ( obj instanceof Clob )
{
Clob clob = (Clob) obj;
return clob.getSubString( (long) 0, (int) clob.length() );
}
else if ( obj instanceof byte[] )
{
byte[] bytes = (byte[]) obj;
return new String( bytes, UTF8 );
}
else { return obj.toString(); }
}
/**
* Make a VTI given its rows.
*/
private static StringArrayVTI makeVTI( String[][] rows )
{
int columnCount = rows[ 0 ].length;
return new StringArrayVTI( makeColumnNames( columnCount, "mycol" ), rows );
}
/**
* Make column names.
*/
private static String[] makeColumnNames( int columnCount, String stub )
{
String[] names = new String[ columnCount ];
for ( int i = 0; i < columnCount; i++ ) { names[ i ] = stub + i; }
return names;
}
/**
* <p>
* Print a ResultSet, using Derby's pretty-printing tool.
* </p>
*/
public static void prettyPrint( Connection conn, ResultSet rs )
throws SQLException
{
org.apache.derby.tools.JDBCDisplayUtil.DisplayResults
( System.out, rs, conn );
}
//////////////////
//
// OPTIMIZER STATS
//
//////////////////
/**
* <p>
* Get the optimizer stats for a query.
* </p>
*/
private String getOptimizerStats( String query )
throws Exception
{
goodStatement( "CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)" );
goodStatement( "CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)" );
goodStatement( query );
PreparedStatement ps = prepareStatement( "values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()" );
ResultSet rs = ps.executeQuery();
rs.next();
String retval = rs.getString( 1 );
rs.close();
ps.close();
goodStatement( "CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0)" );
goodStatement( "CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)" );
return retval;
}
/**
* <p>
* Read the value of a tag in some optimizer output.
* </p>
*/
private double readDoubleTag( String optimizerOutput, String tag )
throws Exception
{
StringReader stringReader = new StringReader( optimizerOutput );
LineNumberReader lineNumberReader = new LineNumberReader( stringReader );
while ( true )
{
String line = lineNumberReader.readLine();
if ( line == null ) { break; }
int idx = line.indexOf( tag );
if ( idx < 0 ) { continue; }
String remnant = line.substring(idx + tag.length()).trim();
// Use NumberFormat.parse() instead of Double.parseDouble() to
// avoid localization issues (DERBY-3100)
Number result = NumberFormat.getInstance().parse(remnant);
println( "Read " + result + " from optimizer output." );
return result.doubleValue();
}
return 0.0;
}
/** Get the conglomerate id of a table */
private long getConglomerateID( Connection conn, String tableName ) throws Exception
{
PreparedStatement ps = conn.prepareStatement
(
"select c.conglomeratenumber\n" +
"from sys.sysconglomerates c, sys.systables t\n" +
"where t.tablename = ? and t.tableid = c.tableid"
);
ps.setString( 1, tableName );
long result = getScalarLong( ps );
ps.close();
return result;
}
/** Get a scalar long result from a query */
private long getScalarLong( PreparedStatement ps ) throws Exception
{
ResultSet rs = ps.executeQuery();
rs.next();
long retval = rs.getLong( 1 );
rs.close();
ps.close();
return retval;
}
///////////////////////////////////////////////////////////////////////////////////
//
// NESTED CLASSES
//
///////////////////////////////////////////////////////////////////////////////////
public static WarningVTI warningVTI() { return new WarningVTI(); }
public static final class WarningVTI extends StringArrayVTI
{
private int _count;
public WarningVTI()
{
super( makeColumnNames( 2, "mycol" ), WARNING_VTI_ROWS );
}
// override
public boolean next() throws SQLException
{
boolean retval = super.next();
if ( retval ) { _count++; }
return retval;
}
public SQLWarning getWarnings()
{
return new SQLWarning( "Warning for row " + _count );
}
}
}