/*
 * 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.phoenix.end2end;

import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
import static org.junit.Assert.assertArrayEquals;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.phoenix.util.PropertiesUtil;
import org.apache.phoenix.util.QueryUtil;
import org.junit.Before;
import org.junit.Test;

public class DistinctPrefixFilterIT extends ParallelStatsDisabledIT {
    private static final String PREFIX = "SERVER DISTINCT PREFIX";
    private String testTableF;
    private String testTableV;
    private String testSeq;
    private Connection conn;

    @Before
    public void initTables() throws Exception {
        testTableF = generateUniqueName();
        testTableV = generateUniqueName();
        testSeq = "SEQ_" + generateUniqueName();

        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        conn = DriverManager.getConnection(getUrl(), props);
        conn.setAutoCommit(false);
        String ddl = "CREATE TABLE " + testTableF +
                "  (prefix1 INTEGER NOT NULL, prefix2 INTEGER NOT NULL, prefix3 INTEGER NOT NULL, " +
                "col1 FLOAT, col2 INTEGER, CONSTRAINT pk PRIMARY KEY(prefix1, prefix2, prefix3)) DISABLE_WAL=true, IMMUTABLE_ROWS=true";
        createTestTable(getUrl(), ddl);

        ddl = "CREATE TABLE " + testTableV +
                "  (prefix1 varchar NOT NULL, prefix2 varchar NOT NULL, prefix3 INTEGER NOT NULL, " +
                "col1 FLOAT, col2 INTEGER, CONSTRAINT pk PRIMARY KEY(prefix1, prefix2, prefix3)) DISABLE_WAL=true, IMMUTABLE_ROWS=true, SALT_BUCKETS=8";
        createTestTable(getUrl(), ddl);

        conn.prepareStatement("CREATE INDEX " + testTableF + "_idx ON "+testTableF+"(col2) DISABLE_WAL=true").execute();
        conn.prepareStatement("CREATE LOCAL INDEX " + testTableV + "_idx ON "+testTableV+"(col2) DISABLE_WAL=true").execute();

        conn.prepareStatement("CREATE SEQUENCE " + testSeq + " CACHE 1000").execute();

        insertPrefixF(1, 1);
        insertPrefixF(1, 2);
        insertPrefixF(1, 3);
        insertPrefixF(2, 1);
        insertPrefixF(2, 2);
        insertPrefixF(2, 3);
        insertPrefixF(3, 1);
        insertPrefixF(3, 2);
        insertPrefixF(2147483647, 2147483647); // all xFF
        insertPrefixF(3, 2147483647); // all xFF
        insertPrefixF(3, 3);
        conn.commit();

        insertPrefixV("1", "1");
        insertPrefixV("1", "2");
        insertPrefixV("1", "3");
        insertPrefixV("2", "1");
        insertPrefixV("2", "2");
        insertPrefixV("2", "3");
        insertPrefixV("22", "1");
        insertPrefixV("3", "22");
        insertPrefixV("3", "1");
        insertPrefixV("3", "2");
        insertPrefixV("3", "3");
        conn.commit();
        ResultSet rs;
        rs = conn.createStatement().executeQuery("select /*+ NO_INDEX */ count(*) from " + testTableV);
        assertTrue(rs.next());
        long count1 = rs.getLong(1);
        rs = conn.createStatement().executeQuery("select count(*) from " + testTableV + "_idx");
        assertTrue(rs.next());
        long count2 = rs.getLong(1);
        assertEquals(count1,count2);

        multiply();
        multiply();
        multiply();
        multiply();
        multiply();
        multiply();
        multiply();
        multiply(); // 256 per unique prefix
    }

    @Test
    public void testCornerCases() throws Exception {
        String testTable = generateUniqueName();
        String ddl = "CREATE TABLE " + testTable +
                "  (prefix1 INTEGER NOT NULL, prefix2 SMALLINT NOT NULL, prefix3 INTEGER NOT NULL, " +
                "col1 FLOAT, CONSTRAINT pk PRIMARY KEY(prefix1, prefix2, prefix3))";
        createTestTable(getUrl(), ddl);

        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + testTable
                + "(prefix1, prefix2, prefix3, col1) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand())");
        stmt.setInt(1, 1);
        stmt.setInt(2, 2);
        stmt.execute();

        stmt = conn.prepareStatement("UPSERT INTO " + testTable
                + "(prefix1, prefix2, prefix3, col1) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand())");
        stmt.setInt(1, 2);
        stmt.setInt(2, 32767);
        stmt.execute();

        stmt = conn.prepareStatement("UPSERT INTO " + testTable
                + "(prefix1, prefix2, prefix3, col1) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand())");
        stmt.setInt(1, 3);
        stmt.setInt(2, 1);
        stmt.execute();

        stmt = conn.prepareStatement("UPSERT INTO " + testTable
                + "(prefix1, prefix2, prefix3, col1) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand())");
        stmt.setInt(1, 3);
        stmt.setInt(2, 2);
        stmt.execute();
        conn.commit();

        testSkipRange("SELECT %s prefix1 FROM "+ testTable + " GROUP BY prefix1 ORDER BY prefix1 DESC", 3);
        testSkipRange("SELECT %s DISTINCT prefix1 FROM " + testTable + " ORDER BY prefix1 DESC", 3);
        testSkipRange("SELECT %s DISTINCT prefix1 FROM " + testTable + " ORDER BY prefix1 DESC LIMIT 2", 2);
    }

    @Test
    public void testPlans() throws Exception {
        // use the filter even when the SkipScan filter is used
        testPlan("SELECT DISTINCT prefix1, prefix2 FROM "+testTableF+ " WHERE prefix1 IN (1,2)", true);
        testPlan("SELECT prefix1, 1, 2 FROM "+testTableF+" GROUP BY prefix1 HAVING prefix1 = 1", true);
        testPlan("SELECT prefix1 FROM "+testTableF+" GROUP BY prefix1, TRUNC(prefix1), TRUNC(prefix2)", true);
        testPlan("SELECT DISTINCT prefix1, prefix2 FROM "+testTableV+ " WHERE prefix1 IN ('1','2')", true);
        testPlan("SELECT prefix1, 1, 2 FROM "+testTableV+" GROUP BY prefix1 HAVING prefix1 = '1'", true);
        // make sure we do not mis-optimize this case
        testPlan("SELECT DISTINCT SUM(prefix1) FROM "+testTableF+" GROUP BY prefix1", false);

        testCommonPlans(testTableF, PREFIX);
        testCommonPlans(testTableV, PREFIX);
    }

    private void testCommonPlans(String testTable, String contains) throws Exception {
        testPlan("SELECT DISTINCT prefix1 FROM "+testTable, true);

        testPlan("SELECT COUNT(DISTINCT prefix1) FROM "+testTable, true);
        testPlan("SELECT COUNT(DISTINCT prefix1), COUNT(DISTINCT prefix2) FROM "+testTable, true);
        testPlan("SELECT COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1,prefix2)) FROM "+testTable, true);
        // a plain aggregate, cannot optimize
        testPlan("SELECT COUNT(prefix1), COUNT(DISTINCT prefix1) FROM "+testTable, false);
        testPlan("SELECT COUNT(*) FROM (SELECT DISTINCT(prefix1) FROM "+testTable+")", true);
        testPlan("SELECT /*+ RANGE_SCAN */ DISTINCT prefix1 FROM "+testTable, false);
        testPlan("SELECT DISTINCT prefix1, prefix2 FROM "+testTable, true);
        // do not use the filter when the distinct is on the entire key
        testPlan("SELECT DISTINCT prefix1, prefix2, prefix3 FROM "+testTable, false);
        testPlan("SELECT DISTINCT (prefix1, prefix2, prefix3) FROM "+testTable, false);
        testPlan("SELECT DISTINCT prefix1, prefix2, col1, prefix3 FROM "+testTable, false);
        testPlan("SELECT DISTINCT prefix1, prefix2, col1 FROM "+testTable, false);
        testPlan("SELECT DISTINCT col1, prefix1, prefix2 FROM "+testTable, false);
        testPlan("SELECT DISTINCT col1 FROM "+testTable, false);
        testPlan("SELECT COUNT(DISTINCT col1) FROM "+testTable, false);
        testPlan("SELECT DISTINCT col2 FROM "+testTable, true);
        testPlan("SELECT COUNT(DISTINCT col2) FROM "+testTable, true);
        testPlan("SELECT prefix1 FROM "+testTable+" GROUP BY prefix1", true);
        testPlan("SELECT COUNT(prefix1) FROM (SELECT prefix1 FROM "+testTable+" GROUP BY prefix1)", true);
        // aggregate over the group by, cannot optimize
        testPlan("SELECT prefix1, count(*) FROM "+testTable+" GROUP BY prefix1", false);
        testPlan("SELECT prefix1 FROM "+testTable+" GROUP BY prefix1, prefix2", true);
        // again using full key
        testPlan("SELECT prefix1 FROM "+testTable+" GROUP BY prefix1, prefix2, prefix3", false);
        testPlan("SELECT (prefix1, prefix2, prefix3) FROM "+testTable+" GROUP BY (prefix1, prefix2, prefix3)", false);
        testPlan("SELECT prefix1, 1, 2 FROM "+testTable+" GROUP BY prefix1", true);
        testPlan("SELECT prefix1 FROM "+testTable+" GROUP BY prefix1, col1", false);

        testPlan("SELECT COUNT(DISTINCT prefix1) FROM "+testTable+" HAVING COUNT(col1) > 10", false);
        testPlan("SELECT COUNT(DISTINCT prefix1) FROM "+testTable+" ORDER BY COUNT(col1)", true);
        testPlan("SELECT COUNT(DISTINCT prefix1) FROM "+testTable+" ORDER BY COUNT(prefix1)", true);
        testPlan("SELECT COUNT(DISTINCT prefix1) FROM "+testTable+" ORDER BY COUNT(prefix2)", true);

        // can't optimize the following, yet, even though it would be possible
        testPlan("SELECT COUNT(DISTINCT prefix1) FROM "+testTable+" HAVING COUNT(DISTINCT prefix2) > 10", false);
        testPlan("SELECT COUNT(DISTINCT prefix1) FROM "+testTable+" HAVING COUNT(DISTINCT prefix1) > 10", false);
        testPlan("SELECT COUNT(DISTINCT prefix1) / 10 FROM "+testTable, false);
        // do not use the filter when the boolean expression filter is used
        testPlan("SELECT DISTINCT prefix1, prefix2 FROM "+testTable+" WHERE col1 > 0.5", false);
    }

    private void testPlan(String query, boolean optimizable) throws Exception {
        ResultSet rs = conn.createStatement().executeQuery("EXPLAIN "+query);
        assertEquals(optimizable, QueryUtil.getExplainPlan(rs).contains(PREFIX));
    }

    @Test
    public void testGroupBy() throws Exception {
        testSkipRange("SELECT %s prefix1 FROM "+ testTableF + " GROUP BY prefix1, prefix2 HAVING prefix1 IN (1,2)", 6);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableF + " GROUP BY prefix1, prefix2 HAVING prefix1 IN (1,2) AND prefix2 IN (1,2)", 4);
        // this leads to a scan along [prefix1,prefix2], but work correctly
        testSkipRange("SELECT %s prefix1 FROM "+ testTableF + " GROUP BY prefix1, prefix2 HAVING prefix2 = 2", 3);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableF + " GROUP BY prefix1, prefix2 HAVING prefix2 = 2147483647", 2);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableF + " GROUP BY prefix1, prefix2 HAVING prefix1 = 2147483647", 1);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableF + " WHERE col1 > 0.99 GROUP BY prefix1, prefix2 HAVING prefix2 = 2", -1);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableF + " WHERE col1 >=0 and col2 > 990 GROUP BY prefix1, prefix2 HAVING prefix2 = 2", -1);

        testSkipRange("SELECT %s prefix1 FROM "+ testTableV + " GROUP BY prefix1, prefix2 HAVING prefix1 IN ('1','2')", 6);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableV + " GROUP BY prefix1, prefix2 HAVING prefix1 IN ('1','2') AND prefix2 IN ('1','2')", 4);
        // this leads to a scan along [prefix1,prefix2], but work correctly
        testSkipRange("SELECT %s prefix1 FROM "+ testTableV + " GROUP BY prefix1, prefix2 HAVING prefix2 = '2'", 3);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableV + " GROUP BY prefix1, prefix2 HAVING prefix2 = '22'", 1);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableV + " GROUP BY prefix1, prefix2 HAVING prefix1 = '22'", 1);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableV + " WHERE col1 > 0.99 GROUP BY prefix1, prefix2 HAVING prefix2 = '2'", -1);
        testSkipRange("SELECT %s prefix1 FROM "+ testTableV + " WHERE col1 >= 0 and col2 > 990 GROUP BY prefix1, prefix2 HAVING prefix2 = '2'", -1);

        testCommonGroupBy(testTableF);
        testCommonGroupBy(testTableV);
    }

    private void testCommonGroupBy(String testTable) throws Exception {
        testSkipRange("SELECT %s prefix1 FROM "+ testTable + " GROUP BY prefix1", 4);
        testSkipRange("SELECT %s prefix1 FROM "+ testTable + " GROUP BY prefix1 ORDER BY prefix1 DESC", 4);
        testSkipRange("SELECT %s prefix1 FROM "+ testTable + " GROUP BY prefix1, prefix2", 11);
        testSkipRange("SELECT %s prefix1 FROM "+ testTable + " GROUP BY prefix1, prefix2 ORDER BY prefix1 DESC", 11);
        testSkipRange("SELECT %s prefix1 FROM "+ testTable + " GROUP BY prefix1, prefix2 ORDER BY prefix2 DESC", 11);
        testSkipRange("SELECT %s prefix1 FROM "+ testTable + " GROUP BY prefix1, prefix2 ORDER BY prefix1, prefix2 DESC", 11);
    }

    @Test
    public void testDistinct() throws Exception {
        // mix distinct prefix and SkipScan filters
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableF + " WHERE prefix1 IN (1,2)", 6);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableF + " WHERE prefix1 IN (3,2147483647)", 5);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableF + " WHERE prefix1 IN (3,2147483647) ORDER BY prefix1 DESC", 5);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableF + " WHERE prefix1 IN (3,2147483647) ORDER BY prefix2 DESC", 5);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableF + " WHERE prefix1 IN (2147483647,2147483647)", 1);
        // mix distinct and boolean expression filters
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableF + " WHERE col1 > 0.99 AND prefix1 IN (1,2)", -1);

        testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1, prefix2)) FROM " + testTableF + " WHERE prefix2=2", 3, 3);
        testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1, prefix2)) FROM " + testTableF + " WHERE prefix1=2", 1, 3);

        // mix distinct prefix and SkipScan filters
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableV + " WHERE prefix1 IN ('1','2')", 6);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableV + " WHERE prefix1 IN ('3','22')", 5);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableV + " WHERE prefix1 IN ('3','22') ORDER BY prefix1 DESC", 5);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableV + " WHERE prefix1 IN ('3','22') ORDER BY prefix2 DESC", 5);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableV + " WHERE prefix1 IN ('2','22')", 4);
        // mix distinct and boolean expression filters
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTableV + " WHERE col1 > 0.99 AND prefix1 IN ('1','2')", -1);

        testCommonDistinct(testTableF);
        testCommonDistinct(testTableV);
    }

    private void testCommonDistinct(String testTable) throws Exception {
        testSkipRange("SELECT %s DISTINCT prefix1 FROM " + testTable, 4);
        testSkipRange("SELECT %s DISTINCT prefix1 FROM " + testTable + " ORDER BY prefix1 DESC", 4);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable, 11);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " ORDER BY prefix1 DESC", 11);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " ORDER BY prefix2 DESC", 11);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " ORDER BY prefix1, prefix2 DESC", 11);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " WHERE col1 > 0.99", -1);
        testSkipRange("SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " WHERE col1 > 0.99 ORDER BY prefix1, prefix2 DESC", -1);

        testCount("SELECT %s COUNT(DISTINCT prefix1) FROM " + testTable, 4);
        testCount("SELECT COUNT(*) FROM (SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + ")", 11);
        testCount("SELECT %s COUNT(DISTINCT prefix1) FROM " + testTable + " WHERE col1 > 0.99", -1);
        testCount("SELECT COUNT(*) FROM (SELECT %s DISTINCT prefix1, prefix2 FROM " + testTable + " WHERE col1 > 0.99)", -1);
        testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT prefix2) FROM " + testTable, 4, 4);
        testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1, prefix2)) FROM " + testTable, 4, 11);
        testCount("SELECT %s COUNT(DISTINCT prefix1), COUNT(DISTINCT (prefix1, prefix2)) FROM " + testTable + " WHERE col1 > 0.99", -1, -1);

        testCount("SELECT %s COUNT(DISTINCT col1) FROM " + testTable, -1);
        testCount("SELECT %s COUNT(DISTINCT col2) FROM " + testTable, -1);

        testCount("SELECT %s COUNT(DISTINCT prefix1) FROM " + testTable + " WHERE col1 < 0", -1);
    }

    @Test
    public void testRVC() throws Exception {
        int count = 0;
        ResultSet res1 = conn.createStatement().executeQuery("SELECT (prefix1, prefix2) FROM "+ testTableF + " GROUP BY (prefix1, prefix2)");
        ResultSet res2 = conn.createStatement().executeQuery("SELECT /*+ RANGE_SCAN */ (prefix1, prefix2) FROM "+ testTableF + " GROUP BY (prefix1, prefix2)");
        ResultSet res3 = conn.createStatement().executeQuery("SELECT DISTINCT(prefix1, prefix2) FROM "+ testTableF);
        ResultSet res4 = conn.createStatement().executeQuery("SELECT /*+ RANGE_SCAN */ DISTINCT(prefix1, prefix2) FROM "+ testTableF);
        while (res1.next()) {
            byte[] r1 = res1.getBytes(1);
    
            assertTrue(res2.next());
            byte[] r2 = res2.getBytes(1);
            assertArrayEquals(r1, r2);
    
            assertTrue(res3.next());
            byte[] r3 = res3.getBytes(1);
            assertArrayEquals(r1, r3);
    
            assertTrue(res4.next());
            byte[] r4 = res4.getBytes(1);
            assertArrayEquals(r1, r4);

            count++;
        }
        assertFalse(res2.next());
        assertFalse(res3.next());
        assertFalse(res4.next());
        assertEquals(11,count);
    }

    private void testSkipRange(String q, int expected) throws SQLException {
        String q1 = String.format(q, "");
        PreparedStatement stmt = conn.prepareStatement(q1);
        ResultSet res = stmt.executeQuery();
        int count = 0;
        while(res.next()) {
            count++;
        }

        if (expected > 0) assertEquals(expected, count);

        q1 = String.format(q, "/*+ RANGE_SCAN */");
        stmt = conn.prepareStatement(q1);
        res = stmt.executeQuery();
        int count1 = 0;
        while(res.next()) {
            count1++;
        }
        assertEquals(count, count1);
    }

    private void testCount(String q, int... expected) throws SQLException {
        String q1 = String.format(q, "");
        PreparedStatement stmt = conn.prepareStatement(q1);
        ResultSet res = stmt.executeQuery();
        int[] count = new int[expected.length];
        assertTrue(res.next());
        for (int i=0; i<expected.length; i++) {
            count[i] = res.getInt(i+1);
            if (expected[i] > 0) assertEquals(expected[i], count[i]);
        }
        assertFalse(res.next());

        q1 = String.format(q, "/*+ RANGE_SCAN */");
        stmt = conn.prepareStatement(q1);
        res = stmt.executeQuery();
        assertTrue(res.next());
        for (int i=0; i<expected.length; i++) {
            assertEquals(count[i], res.getInt(i+1));
        }
        assertFalse(res.next());
    }

    private void insertPrefixF(int prefix1, int prefix2) throws SQLException {
        String query = "UPSERT INTO " + testTableF
                + "(prefix1, prefix2, prefix3, col1, col2) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand(), trunc(rand()*1000))";
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.setInt(1, prefix1);
            stmt.setInt(2, prefix2);
            stmt.execute();
    }

    private void insertPrefixV(String prefix1, String prefix2) throws SQLException {
        String query = "UPSERT INTO " + testTableV
                + "(prefix1, prefix2, prefix3, col1, col2) VALUES(?,?,NEXT VALUE FOR "+testSeq+",rand(), trunc(rand()*1000))";
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.setString(1, prefix1);
            stmt.setString(2, prefix2);
            stmt.execute();
    }

    private void multiply() throws SQLException {
        conn.prepareStatement("UPSERT INTO " + testTableF
                + " SELECT prefix1,prefix2,NEXT VALUE FOR "+testSeq+",rand(), trunc(rand()*1000) FROM "+testTableF).execute();
        conn.prepareStatement("UPSERT INTO " + testTableV
                + " SELECT prefix1,prefix2,NEXT VALUE FOR "+testSeq+",rand(), trunc(rand()*1000) FROM "+testTableV).execute();
        conn.commit();
    }
}