| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.GroupByExpressionTest |
| |
| 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.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import junit.extensions.TestSetup; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| |
| public class GroupByExpressionTest extends BaseJDBCTestCase |
| { |
| |
| private static String[] TABLES = { |
| "create table test (c1 int, c2 int, c3 int, c4 int)", |
| "create table coal (vc1 varchar(2), vc2 varchar(2))", |
| |
| "create table alltypes (i int, s smallint, l bigint, " + |
| "c char(10), v varchar(50), lvc long varchar, " + |
| " d double precision, r real, " + |
| " dt date, t time, ts timestamp, " + |
| " b char(2) for bit data, bv varchar(8) for bit data, " + |
| " lbv long varchar for bit data, dc decimal(5,2))", |
| "create table t1 (c1 varchar(30))", |
| "create table t2 (c1 varchar(10))", |
| "create table t3 (c1 int, c2 int)" |
| }; |
| |
| private static String[] FUNCTIONS = { |
| "create function r() returns double external name " + |
| "'java.lang.Math.random' language java parameter style java"}; |
| |
| /** |
| * Basic test case. Checks functionality with simple arithmetic expressions |
| */ |
| public void testSimpleExpressions() throws Exception |
| { |
| verifyQueryResults( |
| "Q1", |
| "select c1,c2,sum(c3) from test group by c2,c1", |
| new int[][] { |
| {1,10,100}, |
| {2,10,100}, |
| {1,11,100}, |
| {2,11,202}}); |
| |
| verifyQueryResults( |
| "Q1_2", |
| "select c1,c2,sum(c3) from test group by c2,c1 having c1=1 and c2=10 and sum(c3) > 40", |
| new int[][] { |
| {1,10,100}}); |
| |
| verifyQueryResults( |
| "Q2", |
| "select c1+c2, sum(c3) from test group by c1,c2", |
| new int[][] { |
| {11, 100}, |
| {12, 100}, |
| {12, 100}, |
| {13, 202}}); |
| verifyQueryResults( |
| "Q3", |
| "select c1+c2, sum(c3) from test group by c1+c2", |
| new int[][] { |
| {11, 100}, |
| {12, 200}, |
| {13, 202}}); |
| verifyQueryResults( |
| "Q4", |
| "select (c1+c2)+1, sum(c3) from test group by c1+c2", |
| new int[][] { |
| {12, 100}, |
| {13, 200}, |
| {14, 202}}); |
| verifyQueryResults( |
| "Q5", |
| "select (c1+c2), sum(c3)+(c1+c2) from test group by c1+c2", |
| new int[][] { |
| {11,111}, |
| {12,212}, |
| {13,215}}); |
| verifyQueryResults( |
| "Q6", |
| "select c2-c1, c1+c2, count(*) from test group by c1+c2, c2-c1", |
| new int[][] { |
| {9,11,1}, |
| {8,12,1}, |
| {10,12,1}, |
| {9,13,2}}); |
| |
| // having clause with expression. same as last query with an additional restriction. |
| verifyQueryResults( |
| "Q6b", |
| "select c2-c1, c1+c2, count(*) from test group by c1+c2, c2-c1 having (c2-c1)+count(*)>10", |
| new int[][] { |
| {10,12,1}, |
| {9,13,2}}); |
| } |
| |
| /** |
| * DERBY-4202 Aggregates not allowed in group by |
| * @throws Exception |
| */ |
| public void testDerby4402AggregateInGroupBy() throws Exception |
| { |
| assertCompileError( |
| "42Y26", "select c1 from test group by sum(c3)"); |
| assertCompileError( |
| "42Y26", "select c1 from test group by (c1+sum(c3))"); |
| } |
| |
| /** |
| * queries which combine compound expressions and simple column refs. |
| */ |
| public void testDerby3094Expressions() throws Exception |
| { |
| verifyQueryResults( |
| "Q1", |
| "select c1+c2, sum(c3) from test group by c1+c2, c1", |
| new int[][] { |
| {11, 100}, |
| {12, 100}, // c1=1, c2=11 |
| {12, 100}, // c1=2, c2=10 |
| {13, 202}}); |
| verifyQueryResults( |
| "Q2", |
| "select c1+c2, sum(c3) from test group by c1, c1+c2", |
| new int[][] { |
| {11, 100}, |
| {12, 100}, // c1=1, c2=11 |
| {12, 100}, // c1=2, c2=10 |
| {13, 202}}); |
| verifyQueryResults( |
| "Q3", |
| "select c1, c1+c2 from test group by c1, c1+c2", |
| new int[][] { |
| {1, 11}, |
| {1, 12}, |
| {2, 12}, |
| {2, 13}}); |
| verifyQueryResults( |
| "Q4", |
| "select c1+c2, sum(c3) from test group by c1+c2", |
| new int[][] { |
| {11, 100}, |
| {12, 200}, |
| {13, 202}}); |
| verifyQueryResults( |
| "Q5", |
| "select c1,c2,c1+c2,sum(c3) from test group by c1,c2,c1+c2", |
| new int[][] { |
| {1, 10, 11, 100}, |
| {1, 11, 12, 100}, |
| {2, 10, 12, 100}, |
| {2, 11, 13, 202}}); |
| verifyQueryResults( |
| "Q6", |
| "select c1,c2,sum(c3) from test group by c2, c1", |
| new int[][] { |
| {1, 10, 100}, |
| {2, 10, 100}, |
| {1, 11, 100}, |
| {2, 11, 202}}); |
| verifyQueryResults( |
| "Q7", |
| "select c1 as c2, sum(c3) from test group by c1,c2", |
| new int[][] { |
| {1, 100}, |
| {1, 100}, |
| {2, 100}, |
| {2, 202}}); |
| verifyQueryResults( |
| "Q8", |
| "select c1 as c2, sum(c3) from test group by c1", |
| new int[][] { |
| {1, 200}, |
| {2, 302}}); |
| verifyQueryResults( |
| "Q9", |
| "select c1+c2, sum(c3) from test group by c1+c2 having c1+c2 > 11", |
| new int[][] { |
| {12, 200}, |
| {13, 202}}); |
| verifyQueryResults( |
| "Q10", |
| "select c1+c2, sum(c3) from test " + |
| "group by c1, c1+c2 having c1+c2 > 11", |
| new int[][] { |
| {12, 100}, |
| {12, 100}, |
| {13, 202}}); |
| verifyQueryResults( |
| "Q11", |
| "select c1*((c1+c2)/2), count(*) from test " + |
| " group by (c1+c2), c1*((c1+c2)/2)", |
| new int[][] { |
| {5, 1}, |
| {6, 1}, |
| {12, 1}, |
| {12, 2}}); |
| verifyQueryResults( |
| "Q12", |
| "select c1, c1+c2, (c1+c2)+c3, count(*) from test " + |
| " group by c1, c1+c2, (c1+c2)+c3", |
| new int[][] { |
| {1, 11, 111, 1}, |
| {1, 12, 112, 1}, |
| {2, 12, 112, 1}, |
| {2, 13, 114, 2}}); |
| verifyQueryResults( |
| "Q13", |
| "select (c1+c2)+c3, count(*) from test " + |
| " group by c3, c1+c2", |
| new int[][] { |
| {111, 1}, |
| {112, 2}, |
| {114, 2}}); |
| assertCompileError( |
| "42Y36", "select c1+c2, sum(c3) from test group by c1"); |
| assertCompileError( |
| "42Y36", "select c1,c2, sum(c3) from test group by c1+c2,c1"); |
| assertCompileError( |
| "42Y36", "select c1+c2, sum(c3) from test group by 1"); |
| assertCompileError( |
| "42X04", "select c1+c2 as expr, sum(c3) from test group by expr"); |
| assertCompileError( |
| "42X04", "select c1 as c1a, c2, sum(c3) from test group by c1a,c2"); |
| assertCompileError( |
| "42Y36", "select c1 as c2, sum(c3) from test group by c2"); |
| assertCompileError( |
| "42Y36", "select c1+(c2+c3), sum(c3) from test group by c3, (c1+c2)"); |
| } |
| |
| public void testSubSelect() throws Exception |
| { |
| /* query with a group by on a subselect */ |
| verifyQueryResults( |
| "Q1", |
| "select a+1, sum(b) from (select c1+1 a , c2+1 b from test) t group by a", |
| new int[][] { |
| {3,23}, {4,35}}); |
| |
| verifyQueryResults( |
| "Q2", |
| "select a+1, sum(b) from (select c1+1 a , c2+1 b from test) t group by a+1", |
| new int[][] { |
| {3,23}, {4,35}}); |
| |
| verifyQueryResults( |
| "Q2", |
| "select a+1, sum(b) from (select c1+1 a , c2+1 b from test) t group by a+1 having a+1 > 3", |
| new int[][] {{4,35}}); |
| |
| verifyQueryResults( |
| "Q3", |
| "select b/2,sum(a) from " + |
| "(select c1+1 a, max(c2) b from test group by c1+1) t " + |
| "group by b/2", |
| new int[][] {{5,5}}); |
| |
| } |
| |
| |
| public void testMiscExpressions() throws Exception |
| { |
| // cast |
| verifyQueryResults( |
| "cast", |
| "select (cast (c1 as char(2))), count(*) from test " + |
| " group by (cast (c1 as char(2)))", |
| new Object[][] { |
| {"1 ", 2}, |
| {"2 ", 3}}); |
| |
| // coalesce |
| verifyQueryResults( |
| "coalesce", |
| "select (coalesce(vc1,vc2)), count(*) from coal " + |
| " group by (coalesce(vc1,vc2))", |
| new Object[][] {{"1", 2}, {"2", 1}}); |
| // concat |
| verifyQueryResults( |
| "concat", |
| "select c||v, count(*) from alltypes group by c||v", |
| new Object[][] { |
| {"duplicate noone is here", 1}, |
| {"duplicate this is duplicated", 13}, |
| {"goodbye this is duplicated", 1}}); |
| // conditional. |
| verifyQueryResults( |
| "cond", |
| "select (case when c1 = 1 then 2 else 1 end), sum(c2) from test " + |
| " group by (case when c1 = 1 then 2 else 1 end)", |
| new int[][] {{1,32}, {2, 21}}); |
| |
| // Conditional Derby-2230. |
| verifyQueryResults( |
| "cond", |
| "select case when 1 = 1 then 'z' else substr('abc',1,1) end from test", |
| new String[][] {{"z"}}); |
| |
| // length |
| verifyQueryResults( |
| "length", |
| "select length(v), count(*) from alltypes group by length(v)", |
| new int[][] {{13,1},{18,14}}); |
| |
| // current time. ignore the value of current time. |
| // just make sure we can group by it and get the right results for |
| // the aggregate. |
| verifyQueryResults( |
| "current_time", |
| "select co from " + |
| "(select current_time ct, count(*) co from test t1, test t2, test t3 group by current_time) t", |
| new int[][] {{125}}); |
| // concat + substr |
| verifyQueryResults( |
| "concat+substr", |
| "select substr(c||v, 1, 4), count(*) from alltypes group by substr(c||v, 1, 4)", |
| new Object[][] { |
| {"dupl", 14}, |
| {"good", 1}}); |
| |
| // DERBY-2008 |
| // substr (2-args) |
| verifyQueryResults( |
| "substr-Q1", |
| "select substr(c1, 3) from t1 group by substr(c1, 3)", |
| new String[][] { {"03-12-08"}, |
| {"28-09-21"} }); |
| // substr (3-args) |
| verifyQueryResults( |
| "substr-Q2", |
| "select substr(c1, 3, 4) from t1 group by substr(c1, 3, 4)", |
| new String[][] { {"03-1"}, |
| {"28-0"} }); |
| |
| // ltrim |
| verifyQueryResults( |
| "ltrim", |
| "select ltrim(c1) from t2 group by ltrim(c1)", |
| new String[][] { {"123 "}, |
| {"abc "} }); |
| |
| // rtrim |
| verifyQueryResults( |
| "rtrim", |
| "select rtrim(c1) from t2 group by rtrim(c1)", |
| new String[][] { {"123"}, |
| {"abc"} }); |
| |
| // rtrim in having clause as well. |
| verifyQueryResults( |
| "rtrim_having", |
| "select rtrim(c1) from t2 group by rtrim(c1) having rtrim(c1) like 'ab%'", |
| new String[][] { {"abc"} }); |
| |
| |
| // locate (2-args) |
| verifyQueryResults( |
| "locate-Q1", |
| "select locate(c1, 'abc') from t2 group by locate(c1, 'abc')", |
| new int[][] { { 0 }, |
| { 1 } }); |
| |
| // locate (3-args) |
| verifyQueryResults( |
| "locate-Q2", |
| "select locate(c1, 'abc', 1) from t2 group by locate(c1, 'abc',1)", |
| new int[][] { { 0 }, |
| { 1 } }); |
| |
| // cast with NULL |
| verifyQueryResults( |
| "cast-Q2", |
| "select (cast (NULL as INTEGER)) from t2 group by (cast (NULL as INTEGER))", |
| new Object[][] { { null } } ); |
| |
| // DERBY-2014 |
| // nullif |
| verifyQueryResults( |
| "nullif-Q1", |
| "select nullif(c1,c1) from t3 group by nullif(c1,c1)", |
| new Object[][] { { null } } ); |
| |
| verifyQueryResults( |
| "nullif-Q2", |
| "select nullif(c1,c2) from t3 group by nullif(c1,c2)", |
| new Object[][] { { 5 }, |
| { null } }); |
| |
| verifyQueryResults( |
| "nullif-Q3", |
| "select nullif(c1,10) from t3 group by nullif(c1,10)", |
| new Object[][] { { 1 }, |
| { 2 }, |
| { 3 }, |
| { 5 }, |
| { null } }); |
| |
| verifyQueryResults( |
| "nullif-Q4", |
| "select nullif(1,c1) from t3 group by nullif(1,c1)", |
| new Object[][] { { 1 }, |
| { null } }); |
| } |
| |
| public void testExtractOperator() throws Exception |
| { |
| verifyQueryResults( |
| "year", |
| "select year(dt), count(*) from alltypes group by year(dt)", |
| new int[][] {{1992, 15}}); |
| verifyQueryResults( |
| "month", |
| "select month(dt), count(*) from alltypes group by month(dt)", |
| new int[][] {{1,5},{2,6},{3,4}}); |
| verifyQueryResults( |
| "day", |
| "select day(dt), count(*) from alltypes group by day(dt)", |
| new int[][] {{1,3},{2,3},{3,3},{4,3},{5,2},{6,1}}); |
| verifyQueryResults( |
| "hour", |
| "select hour(t), count(*) from alltypes group by hour(t)", |
| new int[][] {{12, 15}}); |
| verifyQueryResults( |
| "hour2", |
| "select hour(ts), count(*) from alltypes group by hour(ts)", |
| new int[][] {{12,15}}); |
| verifyQueryResults( |
| "minute", |
| "select minute(ts), count(*) from alltypes group by minute(ts)", |
| new int[][] {{30,14},{55,1}}); |
| verifyQueryResults( |
| "second", |
| "select second(t), count(*) from alltypes group by second(t)", |
| new int[][]{ |
| {30,2},{31,1},{32,1},{33,1},{34,1},{35,1}, |
| {36,1},{37,1},{38,1},{39,1},{40,1},{41,1}, |
| {42,1},{55,1}}); |
| } |
| |
| /** |
| * Check that duplicate columns are now allowed in group by's. Earlier |
| * (pre 883), derby would flag an error. |
| * |
| */ |
| public void testDuplicateColumns() throws Exception |
| { |
| verifyQueryResults( |
| "Q1", |
| "select c1, sum(c2) from test group by c1,c1", |
| new int[][]{ {1,21}, {2,32}}); |
| |
| verifyQueryResults( |
| "Q2", |
| "select c1, c1, sum(c2) from test group by c1,c1", |
| new int[][]{ {1,1,21}, {2,2,32}}); |
| } |
| /** |
| * Negative tests. These queries should not compile at all. |
| */ |
| public void testNegative() |
| { |
| // disallow java function |
| assertCompileError( |
| "42Y30", "select r(), count(*) from test group by r()"); |
| |
| assertCompileError( |
| "42Y30", "select count(*) from test group by r()"); |
| // invalid grouping expression. |
| assertCompileError( |
| "42Y36", "select c1+1, count(*) from test group by c1+2"); |
| |
| // again invalid grouping expression because cast type is different. |
| assertCompileError( |
| "42Y36", "select (cast (c as char(2))), count(*) " + |
| " from alltypes group by (cast (c as char(3)))"); |
| |
| // same column name, same table but different tablenumber in the query |
| assertCompileError( |
| "42Y36", |
| "select t1.c1, count(*) from test t1, test t2 " + |
| " group by t2.c1"); |
| // ternary operator, not equivalent test. |
| assertCompileError( |
| "42Y36", |
| "select substr(c, 3, 4) from alltypes group by substr(v, 3, 4)"); |
| |
| // DERBY-2008 |
| // invalid grouping expression |
| assertCompileError( |
| "42Y36", |
| "select substr(c1, 3, 4) from t1 group by substr(c1, 3)"); |
| assertCompileError( |
| "42Y36", |
| "select substr(c1, 3) from t1 group by substr(c1, 3, 4)"); |
| assertCompileError( |
| "42Y36", |
| "select locate(c1, 'abc') from t2 group by locate(c1, 'abc',3)"); |
| assertCompileError( |
| "42Y36", |
| "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc')"); |
| assertCompileError( |
| "42Y36", |
| "select locate(c1, 'abc',2) from t2 group by locate(c1, 'abc',3)"); |
| |
| // DERBY-2014 |
| // invalid grouping expression |
| assertCompileError( |
| "42Y36", |
| "select nullif(c1,c2) from t3 group by nullif(c2,c1)"); |
| assertCompileError( |
| "42Y36", |
| "select nullif(c1,100) from t3 group by nullif(c1,200)"); |
| |
| // aggregates in group by list. |
| assertCompileError( |
| "42Y26", |
| "select 1, max(c1) from test group by max(c1)"); |
| } |
| /* --------------------- begin helper methods -------------------- */ |
| |
| private Object[] intRow(int[] expected) |
| { |
| Object[] arr = new Object[expected.length]; |
| for (int i = 0; i < expected.length; i++) |
| { |
| arr[i] = expected[i]; |
| } |
| return arr; |
| } |
| |
| private void verifyQueryResults( |
| String assertString, String query, Object[][] golden) |
| throws Exception |
| { |
| |
| PreparedStatement ps = prepareStatement(query); |
| ResultSet rs = ps.executeQuery(); |
| for (int i = 0; i < golden.length; i++) |
| { |
| assertTrue( |
| "Not enough rows. Expected " + golden.length + |
| " but found " + i, |
| rs.next()); |
| |
| assertRow(assertString + ":Row:" + i, rs, golden[i]); |
| } |
| rs.close(); |
| ps.close(); |
| } |
| |
| private void verifyQueryResults( |
| String assertString, String query, int[][] golden) |
| throws Exception |
| { |
| PreparedStatement ps = prepareStatement(query); |
| ResultSet rs = ps.executeQuery(); |
| for (int i = 0; i < golden.length; i++) |
| { |
| assertTrue( |
| "Not enough rows. Expected " + golden.length + |
| " but found " + i, |
| rs.next()); |
| assertRow(assertString + ":Row:" + i, rs, intRow(golden[i])); |
| } |
| assertFalse("more rows than expected", rs.next()); |
| rs.close(); |
| ps.close(); |
| } |
| |
| public void assertRow( |
| String assertString, ResultSet rs, Object[] expectedRow) |
| throws Exception |
| { |
| int count = expectedRow.length; |
| |
| for ( int i = 0; i < count; i++ ) |
| { |
| int columnNumber = i + 1; |
| Object expected = expectedRow[i]; |
| Object actual = rs.getObject(columnNumber); |
| assertEquals(assertString + ":Column number ", expected, actual); |
| } |
| } |
| |
| /* ------------------- end helper methods -------------------------- */ |
| public GroupByExpressionTest(String name) |
| { |
| super(name); |
| } |
| |
| public static Test suite() |
| { |
| BaseTestSuite suite = new BaseTestSuite("GROUP BY expression tests"); |
| suite.addTestSuite(GroupByExpressionTest.class); |
| |
| TestSetup wrapper = new CleanDatabaseTestSetup(suite) { |
| protected void decorateSQL(Statement s) throws SQLException |
| { |
| for (int i = 0; i < TABLES.length; i++) { |
| s.execute(TABLES[i]); |
| } |
| for (int i = 0; i < FUNCTIONS.length; i++) { |
| s.execute(FUNCTIONS[i]); |
| } |
| |
| s.execute("insert into test values (1, 10, 100, 1000)"); |
| s.execute("insert into test values (1, 11, 100, 1001)"); |
| s.execute("insert into test values (2, 10, 100, 1000)"); |
| s.execute("insert into test values (2, 11, 101, 1001)"); |
| s.execute("insert into test values (2, 11, 101, 1000)"); |
| |
| s.execute("insert into coal values ('1', '2')"); |
| s.execute("insert into coal values (null, '2')"); |
| s.execute("insert into coal values ('1', null)"); |
| |
| s.execute( |
| "insert into alltypes values (0, 100, 1000000, " + |
| "'duplicate', 'this is duplicated', 'also duplicated', " + |
| "200.0e0, 200.0e0, " + |
| " date('1992-01-01'), time('12:30:30'), " + |
| " timestamp('1992-01-01 12:30:30'), " + |
| "X'12af', x'0000111100001111', X'1234', 111.11) "); |
| s.execute( |
| "insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-01-02'), time('12:30:31'), " + |
| "timestamp('1992-01-02 12:30:31'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11)"); |
| s.execute( |
| "insert into alltypes values (1, 100, 1000000, " + |
| "'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-01-03'), time('12:30:32'), " + |
| " timestamp('1992-01-03 12:30:32'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11)"); |
| s.execute( |
| "insert into alltypes values (0, 200, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-01-04'), time('12:30:33'), " + |
| " timestamp('1992-01-04 12:30:33'), " + |
| " X'12af', X'0000111100001111', X'1234', 222.22)"); |
| s.execute( |
| "insert into alltypes values (0, 100, 2000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-01-05'), time('12:30:34'), " + |
| " timestamp('1992-01-05 12:30:34'), " + |
| " X'12af', X'0000111100001111', X'1234', 222.22)"); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'goodbye', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-02-01'), time('12:30:35'), " + |
| " timestamp('1992-02-01 12:30:35'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11)"); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'noone is here', 'jimmie noone was here', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-02-02'), time('12:30:36'), " + |
| " timestamp('1992-02-02 12:30:36'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11) "); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-02-03'), time('12:30:37'), " + |
| " timestamp('1992-02-03 12:30:37'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11)"); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 100.0e0, 200.0e0, " + |
| " date('1992-02-04'), time('12:30:38'), " + |
| " timestamp('1992-02-04 12:30:38'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11) "); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 100.0e0, " + |
| " date('1992-02-05'), time('12:30:39'), " + |
| " timestamp('1992-02-05 12:30:39'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11) "); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-02-06'), time('12:30:40'), " + |
| " timestamp('1992-02-06 12:30:40'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11) "); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-03-01'), time('12:55:55'), " + |
| "timestamp('1992-03-01 12:30:30'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11) "); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-03-02'), time('12:30:30'), " + |
| "timestamp('1992-03-02 12:55:55'), " + |
| " X'12af', X'0000111100001111', X'1234', 111.11) "); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-03-03'), time('12:30:41'), " + |
| " timestamp('1992-03-03 12:30:41'), " + |
| " X'ffff', X'0000111100001111', X'1234', 111.11) "); |
| s.execute("insert into alltypes values (0, 100, 1000000, " + |
| " 'duplicate', 'this is duplicated', 'also duplicated', " + |
| " 200.0e0, 200.0e0, " + |
| " date('1992-03-04'), time('12:30:42'), " + |
| " timestamp('1992-03-04 12:30:42'), " + |
| " X'12af', X'1111111111111111', X'1234', 111.11) " ); |
| |
| s.execute("insert into t1 values ('1928-09-21'), ('1903-12-08')"); |
| s.execute("insert into t2 values '123 ', 'abc ', '123', 'abc'") ; |
| s.execute("insert into t3 values (1,1), (2,2), (2,2), (3,3), (null, null), (5,100)"); |
| |
| } |
| }; |
| return wrapper; |
| } |
| } |
