/*
 * 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.junit.Assert.assertArrayEquals;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;

import java.math.BigDecimal;
import java.sql.Array;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;

import org.apache.phoenix.exception.SQLExceptionCode;
import org.apache.phoenix.util.ByteUtil;
import org.apache.phoenix.util.DateUtil;
import org.junit.Before;
import org.junit.Ignore;
import org.junit.Test;


public class DefaultColumnValueIT extends ParallelStatsDisabledIT {
    private String sharedTable1;
    private String sharedTable2;

    @Before
    public void init() {
        sharedTable1 = generateUniqueName();
        sharedTable2 = generateUniqueName();
    }

    @Test
    public void testDefaultColumnValue() throws Exception {
        String ddl = "CREATE TABLE IF NOT EXISTS " + sharedTable1 + " (" +
                "pk1 INTEGER NOT NULL, " +
                "pk2 INTEGER NOT NULL, " +
                "pk3 INTEGER NOT NULL DEFAULT 10, " +
                "test1 INTEGER, " +
                "CONSTRAINT NAME_PK PRIMARY KEY (pk1, pk2, pk3))";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);
        conn.createStatement().execute("ALTER TABLE " + sharedTable1 + 
                " ADD test2 INTEGER DEFAULT 5, est3 INTEGER");

        String dml = "UPSERT INTO " + sharedTable1 + " VALUES (1, 2)";
        conn.createStatement().execute(dml);
        dml = "UPSERT INTO " + sharedTable1 + " VALUES (11, 12, 13, 14, null, 16)";
        conn.createStatement().execute(dml);
        conn.commit();


        String projection = "*";

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT " + projection + " FROM " + sharedTable1 + " WHERE pk1 = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(2, rs.getInt(2));
        assertEquals(10, rs.getInt(3));
        assertEquals(0, rs.getInt(4));
        assertTrue(rs.wasNull());
        assertEquals(5, rs.getInt(5));
        assertEquals(0, rs.getInt(6));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement()
                .executeQuery("SELECT " + projection + " FROM " + sharedTable1 + " WHERE pk1 = 11");
        assertTrue(rs.next());
        assertEquals(11, rs.getInt(1));
        assertEquals(12, rs.getInt(2));
        assertEquals(13, rs.getInt(3));
        assertEquals(14, rs.getInt(4));
        assertEquals(0, rs.getInt(5));
        assertTrue(rs.wasNull());
        assertEquals(16, rs.getInt(6));
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultColumnValueOnView() throws Exception {
        String ddl = "CREATE TABLE IF NOT EXISTS " + sharedTable1 + " (" +
                "pk1 INTEGER NOT NULL, " +
                "pk2 INTEGER NOT NULL, " +
                "pk3 INTEGER NOT NULL DEFAULT 10, " +
                "test1 INTEGER, " +
                "test2 INTEGER DEFAULT 5, " +
                "test3 INTEGER, " +
                "CONSTRAINT NAME_PK PRIMARY KEY (pk1, pk2, pk3))";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);
        conn.createStatement().execute("CREATE VIEW " + sharedTable2 + 
                "(pk4 INTEGER NOT NULL DEFAULT 20 PRIMARY KEY, test4 VARCHAR DEFAULT 'foo') " +
                "AS SELECT * FROM " + sharedTable1 + " WHERE pk1 = 1");

        String dml = "UPSERT INTO " + sharedTable2 + "(pk2) VALUES (2)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT pk1,pk2,pk3,pk4,test2,test4 FROM " + sharedTable2);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(2, rs.getInt(2));
        assertEquals(10, rs.getInt(3));
        assertEquals(20, rs.getInt(4));
        assertEquals(5, rs.getInt(5));
        assertEquals("foo", rs.getString(6));
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultColumnValueProjected() throws Exception {
        String ddl = "CREATE TABLE IF NOT EXISTS " + sharedTable1 + " (" +
                "pk1 INTEGER NOT NULL, " +
                "pk2 INTEGER NOT NULL, " +
                "pk3 INTEGER NOT NULL DEFAULT 10, " +
                "test1 INTEGER, " +
                "test2 INTEGER DEFAULT 5, " +
                "test3 INTEGER, " +
                "CONSTRAINT NAME_PK PRIMARY KEY (pk1, pk2, pk3))";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + sharedTable1 + " VALUES (1, 2)";
        conn.createStatement().execute(dml);
        dml = "UPSERT INTO " + sharedTable1 + " VALUES (11, 12, 13, 14, null, 16)";
        conn.createStatement().execute(dml);
        conn.commit();

        String projection = "pk1, pk2, pk3, test1, test2, test3";

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT " + projection + " FROM " + sharedTable1 + " WHERE pk1 = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(2, rs.getInt(2));
        assertEquals(10, rs.getInt(3));
        assertEquals(0, rs.getInt(4));
        assertTrue(rs.wasNull());
        assertEquals(5, rs.getInt(5));
        assertEquals(0, rs.getInt(6));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement()
                .executeQuery("SELECT " + projection + " FROM " + sharedTable1 + " WHERE pk1 = 11");
        assertTrue(rs.next());
        assertEquals(11, rs.getInt(1));
        assertEquals(12, rs.getInt(2));
        assertEquals(13, rs.getInt(3));
        assertEquals(14, rs.getInt(4));
        assertEquals(0, rs.getInt(5));
        assertTrue(rs.wasNull());
        assertEquals(16, rs.getInt(6));
        assertFalse(rs.next());

        projection = "pk1, pk3, pk2, test1, test3, test2";

        rs = conn.createStatement()
                .executeQuery("SELECT " + projection + " FROM " + sharedTable1 + " WHERE pk1 = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(10, rs.getInt(2));
        assertEquals(2, rs.getInt(3));
        assertEquals(0, rs.getInt(4));
        assertTrue(rs.wasNull());
        assertEquals(0, rs.getInt(5));
        assertTrue(rs.wasNull());
        assertEquals(5, rs.getInt(6));
        assertFalse(rs.next());

        rs = conn.createStatement()
                .executeQuery("SELECT " + projection + " FROM " + sharedTable1 + " WHERE pk1 = 11");
        assertTrue(rs.next());
        assertEquals(11, rs.getInt(1));
        assertEquals(13, rs.getInt(2));
        assertEquals(12, rs.getInt(3));
        assertEquals(14, rs.getInt(4));
        assertEquals(16, rs.getInt(5));
        assertEquals(0, rs.getInt(6));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());
    }

    @Test
    public void testMultipleDefaults() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + " (" +
                "pk1 INTEGER NOT NULL, " +
                "pk2 INTEGER NOT NULL DEFAULT 5, " +
                "pk3 INTEGER NOT NULL DEFAULT 10, " +
                "test1 INTEGER, " +
                "test2 INTEGER DEFAULT 50, " +
                "test3 INTEGER DEFAULT 100, " +
                "test4 INTEGER, " +
                "CONSTRAINT NAME_PK PRIMARY KEY (pk1, pk2, pk3))";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + table + " VALUES (1)";
        conn.createStatement().execute(dml);
        dml = "UPSERT INTO " + table + " VALUES (11, 12, 13, 21, null, null, 24)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk1 = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(5, rs.getInt(2));
        assertEquals(10, rs.getInt(3));
        assertEquals(0, rs.getInt(4));
        assertTrue(rs.wasNull());
        assertEquals(50, rs.getInt(5));
        assertEquals(100, rs.getInt(6));
        assertEquals(0, rs.getInt(7));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE pk1 = 11");
        assertTrue(rs.next());
        assertEquals(11, rs.getInt(1));
        assertEquals(12, rs.getInt(2));
        assertEquals(13, rs.getInt(3));
        assertEquals(21, rs.getInt(4));
        assertEquals(0, rs.getInt(5));
        assertTrue(rs.wasNull());
        assertEquals(0, rs.getInt(6));
        assertTrue(rs.wasNull());
        assertEquals(24, rs.getInt(7));
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultImmutableRows() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + " (" +
                "pk1 INTEGER NOT NULL, " +
                "pk2 INTEGER NOT NULL DEFAULT 5, " +
                "pk3 INTEGER NOT NULL DEFAULT 10, " +
                "test1 INTEGER, " +
                "test2 INTEGER DEFAULT 50, " +
                "test3 INTEGER DEFAULT 100, " +
                "test4 INTEGER, " +
                "CONSTRAINT NAME_PK PRIMARY KEY (pk1, pk2, pk3))"
                + "IMMUTABLE_ROWS=true";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + table + " VALUES (1)";
        conn.createStatement().execute(dml);
        dml = "UPSERT INTO " + table + " VALUES (11, 12, 13, 21, null, null, 24)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk1 = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(5, rs.getInt(2));
        assertEquals(10, rs.getInt(3));
        assertEquals(0, rs.getInt(4));
        assertTrue(rs.wasNull());
        assertEquals(50, rs.getInt(5));
        assertEquals(100, rs.getInt(6));
        assertEquals(0, rs.getInt(7));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE pk1 = 11");
        assertTrue(rs.next());
        assertEquals(11, rs.getInt(1));
        assertEquals(12, rs.getInt(2));
        assertEquals(13, rs.getInt(3));
        assertEquals(21, rs.getInt(4));
        assertEquals(0, rs.getInt(5));
        assertTrue(rs.wasNull());
        assertEquals(0, rs.getInt(6));
        assertTrue(rs.wasNull());
        assertEquals(24, rs.getInt(7));
        assertFalse(rs.next());
    }

    @Test
    public void testTrailingNullOverwritingDefault() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE " + table + " (" +
                "pk INTEGER PRIMARY KEY, " +
                "mid INTEGER, " +
                "def INTEGER DEFAULT 10)";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + table + " VALUES (1, 10, null)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(10, rs.getInt(2));
        assertEquals(0, rs.getInt(3));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultReinit() throws Exception {
        String ddl = "CREATE TABLE IF NOT EXISTS " + sharedTable1 + " (" +
                "pk1 INTEGER NOT NULL, " +
                "pk2 INTEGER NOT NULL, " +
                "pk3 INTEGER NOT NULL DEFAULT 10, " +
                "test1 INTEGER, " +
                "test2 INTEGER DEFAULT 5, " +
                "test3 INTEGER, " +
                "CONSTRAINT NAME_PK PRIMARY KEY (pk1, pk2, pk3))";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + sharedTable1 + " VALUES (1, 2)";
        conn.createStatement().execute(dml);
        dml = "UPSERT INTO " + sharedTable1 + " VALUES (11, 12, 13, 14, null, 16)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT pk3, test2 FROM " + sharedTable1 + " WHERE pk1 = 1");
        assertTrue(rs.next());
        assertEquals(10, rs.getInt(1));
        assertEquals(5, rs.getInt(2));
        assertFalse(rs.next());

        conn.close();
        Connection conn2 = DriverManager.getConnection(getUrl());

        rs = conn2.createStatement()
                .executeQuery("SELECT pk3, test2 FROM " + sharedTable1 + " WHERE pk1 = 1");
        assertTrue(rs.next());
        assertEquals(10, rs.getInt(1));
        assertEquals(5, rs.getInt(2));
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultMiddlePrimaryKey() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + " (" +
                "pk1 INTEGER NOT NULL, " +
                "pk2 INTEGER NOT NULL DEFAULT 100, " +
                "pk3 INTEGER NOT NULL, " +
                "test1 INTEGER, " +
                "CONSTRAINT NAME_PK PRIMARY KEY (pk1, pk2, pk3))";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + table + " VALUES (1)";
        try {
            conn.createStatement().execute(dml);
            fail();
        } catch (SQLException e) {
            assertEquals(SQLExceptionCode.CONSTRAINT_VIOLATION.getErrorCode(), e.getErrorCode());
        }

        dml = "UPSERT INTO " + table + " VALUES (1, 2)";
        try {
            conn.createStatement().execute(dml);
            fail();
        } catch (SQLException e) {
            assertEquals(SQLExceptionCode.CONSTRAINT_VIOLATION.getErrorCode(), e.getErrorCode());
        }

        dml = "UPSERT INTO " + table + " VALUES (1, 2, 3)";
        conn.createStatement().execute(dml);

        dml = "UPSERT INTO " + table + " (pk1, pk3) VALUES (11, 13)";
        conn.createStatement().execute(dml);

        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk1 = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(2, rs.getInt(2));
        assertEquals(3, rs.getInt(3));
        assertEquals(0, rs.getInt(4));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE pk1 = 11");
        assertTrue(rs.next());
        assertEquals(11, rs.getInt(1));
        assertEquals(100, rs.getInt(2));
        assertEquals(13, rs.getInt(3));
        assertEquals(0, rs.getInt(4));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultMiddleKeyValueCol() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + "("
                + "pk INTEGER PRIMARY KEY,"
                + "c1 INTEGER,"
                + "c2 INTEGER DEFAULT 50,"
                + "c3 INTEGER)";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + table + " VALUES (1)";
        conn.createStatement().execute(dml);
        dml = "UPSERT INTO " + table + " (pk, c3) VALUES (10, 100)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(0, rs.getInt(2));
        assertTrue(rs.wasNull());
        assertEquals(50, rs.getInt(3));
        assertEquals(0, rs.getInt(4));
        assertTrue(rs.wasNull());

        rs = conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE pk = 10");
        assertTrue(rs.next());
        assertEquals(10, rs.getInt(1));
        assertEquals(0, rs.getInt(2));
        assertTrue(rs.wasNull());
        assertEquals(50, rs.getInt(3));
        assertEquals(100, rs.getInt(4));
    }

    @Test
    public void testDefaultAllDataTypesKeyValueCol() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + "("
                + "pk INTEGER PRIMARY KEY,"
                + "int INTEGER DEFAULT -100,"
                + "uint UNSIGNED_INT DEFAULT 100, "
                + "bint BIGINT DEFAULT -200,"
                + "ubint UNSIGNED_LONG DEFAULT 200,"
                + "tint TINYINT DEFAULT -50,"
                + "utint UNSIGNED_TINYINT DEFAULT 50,"
                + "sint SMALLINT DEFAULT -10,"
                + "usint UNSIGNED_SMALLINT DEFAULT 10,"
                + "flo FLOAT DEFAULT -100.8,"
                + "uflo UNSIGNED_FLOAT DEFAULT 100.9,"
                + "doub DOUBLE DEFAULT -200.5,"
                + "udoubl UNSIGNED_DOUBLE DEFAULT 200.8,"
                + "dec DECIMAL DEFAULT -654624562.3462642362,"
                + "bool BOOLEAN DEFAULT true,"
                + "tim TIME DEFAULT time '1900-10-01 14:03:22.559',"
                + "dat DATE DEFAULT date '1900-10-01 14:03:22.559',"
                + "timest TIMESTAMP DEFAULT timestamp '1900-10-01 14:03:22.559',"
                + "utim UNSIGNED_TIME DEFAULT time '2005-10-01 14:03:22.559',"
                + "udat UNSIGNED_DATE DEFAULT date '2005-10-01 14:03:22.559',"
                + "utimest UNSIGNED_TIMESTAMP DEFAULT timestamp '2005-10-01 14:03:22.559',"
                + "vc VARCHAR DEFAULT 'ABCD',"
                + "c CHAR(5) DEFAULT 'EF',"
                + "bin BINARY(5) DEFAULT 'MNOP',"
                + "varbin VARBINARY DEFAULT 'QR'"
                + ")";

        testDefaultAllDataTypes(table, ddl);
    }

    @Test
    public void testDefaultAllDataTypesPrimaryKey() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + "("
                + "pk INTEGER NOT NULL,"
                + "int INTEGER NOT NULL DEFAULT -100,"
                + "uint UNSIGNED_INT NOT NULL DEFAULT 100, "
                + "bint BIGINT NOT NULL DEFAULT -200,"
                + "ubint UNSIGNED_LONG NOT NULL DEFAULT 200,"
                + "tint TINYINT NOT NULL DEFAULT -50,"
                + "utint UNSIGNED_TINYINT NOT NULL DEFAULT 50,"
                + "sint SMALLINT NOT NULL DEFAULT -10,"
                + "usint UNSIGNED_SMALLINT NOT NULL DEFAULT 10,"
                + "flo FLOAT NOT NULL DEFAULT -100.8,"
                + "uflo UNSIGNED_FLOAT NOT NULL DEFAULT 100.9,"
                + "doub DOUBLE NOT NULL DEFAULT -200.5,"
                + "udoub UNSIGNED_DOUBLE NOT NULL DEFAULT 200.8,"
                + "dec DECIMAL NOT NULL DEFAULT -654624562.3462642362,"
                + "bool BOOLEAN NOT NULL DEFAULT true,"
                + "tim TIME NOT NULL DEFAULT time '1900-10-01 14:03:22.559',"
                + "dat DATE NOT NULL DEFAULT date '1900-10-01 14:03:22.559',"
                + "timest TIMESTAMP NOT NULL DEFAULT timestamp '1900-10-01 14:03:22.559',"
                + "utim UNSIGNED_TIME NOT NULL DEFAULT time '2005-10-01 14:03:22.559',"
                + "udat UNSIGNED_DATE NOT NULL DEFAULT date '2005-10-01 14:03:22.559',"
                + "utimest UNSIGNED_TIMESTAMP NOT NULL DEFAULT timestamp '2005-10-01 14:03:22.559',"
                + "vc VARCHAR NOT NULL DEFAULT 'ABCD',"
                + "c CHAR(5) NOT NULL DEFAULT 'EF',"
                + "bin BINARY(5) NOT NULL DEFAULT 'MNOP',"
                + "varbin VARBINARY NOT NULL DEFAULT 'QR'"
                + "CONSTRAINT pk_final PRIMARY KEY (pk, int, uint, bint, ubint, tint, utint,"
                + "sint, usint, flo, uflo, doub, udoub, dec, bool,"
                + "tim, dat, timest, utim, udat, utimest,"
                + "vc, c, bin, varbin)"
                + ")";

        testDefaultAllDataTypes(table, ddl);
    }

    private void testDefaultAllDataTypes(String table, String ddl) throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + table + " VALUES (1)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(-100, rs.getInt(2));
        assertEquals(100, rs.getInt(3));
        assertEquals(-200, rs.getLong(4));
        assertEquals(200, rs.getLong(5));
        assertEquals(-50, rs.getByte(6));
        assertEquals(50, rs.getByte(7));
        assertEquals(-10, rs.getShort(8));
        assertEquals(10, rs.getShort(9));
        assertEquals(new Float(-100.8), rs.getFloat(10), 0);
        assertEquals(new Float(100.9), rs.getFloat(11), 0);
        assertEquals(-200.5, rs.getDouble(12), 0);
        assertEquals(200.8, rs.getDouble(13), 0);
        assertEquals(new BigDecimal("-654624562.3462642362"), rs.getBigDecimal(14));
        assertEquals(true, rs.getBoolean(15));
        assertEquals(DateUtil.parseTime("1900-10-01 14:03:22.559"), rs.getTime(16));
        assertEquals(DateUtil.parseDate("1900-10-01 14:03:22.559"), rs.getDate(17));
        assertEquals(DateUtil.parseTimestamp("1900-10-01 14:03:22.559"), rs.getTimestamp(18));
        assertEquals(DateUtil.parseTime("2005-10-01 14:03:22.559"), rs.getTime(19));
        assertEquals(DateUtil.parseDate("2005-10-01 14:03:22.559"), rs.getDate(20));
        assertEquals(DateUtil.parseTimestamp("2005-10-01 14:03:22.559"), rs.getTimestamp(21));
        assertEquals("ABCD", rs.getString(22));
        assertEquals("EF", rs.getString(23));
        assertArrayEquals(
                ByteUtil.fillKey(new byte[] {'M', 'N', 'O', 'P'}, rs.getBytes(24).length),
                rs.getBytes(24));
        assertArrayEquals(new byte[] {'Q', 'R'}, rs.getBytes(25));
    }

    @Test
    public void testDefaultExpression() throws Exception {
        String ddl = "CREATE TABLE IF NOT EXISTS " + sharedTable2 + " (" +
                "pk INTEGER PRIMARY KEY,"
                + "c1 INTEGER DEFAULT 1 + 9,"
                + "c2 DOUBLE DEFAULT SQRT(91506.25),"
                + "c3 DECIMAL DEFAULT TO_NUMBER('$123.33', '\u00A4###.##'),"
                + "c4 VARCHAR DEFAULT 'AB' || 'CD',"
                + "c5 CHAR(5) DEFAULT 'E' || 'F',"
                + "c6 INTEGER DEFAULT \"MONTH\"(TO_TIMESTAMP('2015-6-05'))"
                + ")";

        verifyDefaultExpression(sharedTable2, ddl);
    }

    @Test
    public void testDefaultExpressionPrimaryKey() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + " (" +
                "pk INTEGER NOT NULL,"
                + "c1 INTEGER NOT NULL DEFAULT 1 + 9,"
                + "c2 DOUBLE NOT NULL DEFAULT SQRT(91506.25),"
                + "c3 DECIMAL NOT NULL DEFAULT TO_NUMBER('$123.33', '\u00A4###.##'),"
                + "c4 VARCHAR NOT NULL DEFAULT 'AB' || 'CD',"
                + "c5 CHAR(5) NOT NULL DEFAULT 'E' || 'F',"
                + "c6 INTEGER NOT NULL DEFAULT \"MONTH\"(TO_TIMESTAMP('2015-6-05')),"
                + "CONSTRAINT pk_key PRIMARY KEY (pk,c1,c2,c3,c4,c5,c6)"
                + ")";

        verifyDefaultExpression(table, ddl);
    }

    private void verifyDefaultExpression(String table, String ddl) throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + table + " VALUES (1)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(10, rs.getInt(2));
        assertEquals(302.5, rs.getDouble(3), 0);
        assertEquals(new BigDecimal("123.33"), rs.getBigDecimal(4));
        assertEquals("ABCD", rs.getString(5));
        assertEquals("EF", rs.getString(6));
        assertEquals(6, rs.getInt(7));
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultUpsertSelectPrimaryKey() throws Exception {
        Connection conn = DriverManager.getConnection(getUrl());
        String selectTable = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + selectTable + " ("
                + "pk INTEGER PRIMARY KEY)";
        conn.createStatement().execute(ddl);

        String table = generateUniqueName();
        ddl = "CREATE TABLE IF NOT EXISTS " + table + " ("
                + "pk1 INTEGER NOT NULL, "
                + "pk2 INTEGER NOT NULL DEFAULT 100,"
                + "CONSTRAINT pk_key PRIMARY KEY(pk1, pk2))";
        conn.createStatement().execute(ddl);
        conn.commit();

        String dml = "UPSERT INTO " + selectTable + " VALUES (1)";
        conn.createStatement().execute(dml);
        dml = "UPSERT INTO " + selectTable + " VALUES (2)";
        conn.createStatement().execute(dml);
        conn.commit();

        dml = "UPSERT INTO " + table + " (pk1) SELECT pk FROM " + selectTable;
        conn.createStatement().executeUpdate(dml);
        dml = "UPSERT INTO " + table + " SELECT pk,pk FROM " + selectTable;
        conn.createStatement().executeUpdate(dml);
        conn.commit();

        ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM " + selectTable);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));

        rs =conn.createStatement().executeQuery("SELECT * FROM " + table);
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(1, rs.getInt(2));
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(100, rs.getInt(2));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertEquals(2, rs.getInt(2));
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertEquals(100, rs.getInt(2));
    }

    @Test
    public void testDefaultArrays() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + "("
                + "pk INTEGER PRIMARY KEY,"
                + "int INTEGER[5] DEFAULT ARRAY[-100, 50],"
                + "uint UNSIGNED_INT[5] DEFAULT ARRAY[100, 50], "
                + "bint BIGINT[5] DEFAULT ARRAY[-200, 100],"
                + "ubint UNSIGNED_LONG[5] DEFAULT ARRAY[200, 100],"
                + "tint TINYINT[5] DEFAULT ARRAY[-50, 25],"
                + "utint UNSIGNED_TINYINT[5] DEFAULT ARRAY[50, 25],"
                + "sint SMALLINT[5] DEFAULT ARRAY[-10, 5],"
                + "usint UNSIGNED_SMALLINT[5] DEFAULT ARRAY[10, 5],"
                + "flo FLOAT[5] DEFAULT ARRAY[-100.8, 50.4],"
                + "uflo UNSIGNED_FLOAT[5] DEFAULT ARRAY[100.9, 50.45],"
                + "doub DOUBLE[5] DEFAULT ARRAY[-200.5, 100.25],"
                + "udoubl UNSIGNED_DOUBLE[5] DEFAULT ARRAY[200.8, 100.4],"
                + "dec DECIMAL[5] DEFAULT ARRAY[-654624562.3462642362, 3462642362.654624562],"
                + "bool BOOLEAN[5] DEFAULT ARRAY[true, false],"
                + "tim TIME[5] DEFAULT ARRAY["
                + "time '1900-10-01 14:03:22.559',"
                + "time '1990-10-01 14:03:22.559'],"
                + "dat DATE[5] DEFAULT ARRAY["
                + "date '1900-10-01 14:03:22.559',"
                + "date '1990-10-01 14:03:22.559'],"
                + "timest TIMESTAMP[5] DEFAULT ARRAY["
                + "timestamp '1900-10-01 14:03:22.559',"
                + "timestamp '1990-10-01 14:03:22.559'],"
                + "utim UNSIGNED_TIME[5] DEFAULT ARRAY["
                + "time '2005-10-01 14:03:22.559',"
                + "time '2006-10-01 14:03:22.559'],"
                + "udat UNSIGNED_DATE[5] DEFAULT ARRAY["
                + "date '2005-10-01 14:03:22.559',"
                + "date '2006-10-01 14:03:22.559'],"
                + "utimest UNSIGNED_TIMESTAMP[5] DEFAULT ARRAY["
                + "timestamp '2005-10-01 14:03:22.559',"
                + "timestamp '2006-10-01 14:03:22.559'],"
                + "vc VARCHAR[5] DEFAULT ARRAY['ABCD', 'XY'],"
                + "c CHAR(5)[5] DEFAULT ARRAY['EF', 'Z'],"
                + "bin BINARY(5)[5] DEFAULT ARRAY ['MNOP', 'mnop']"
                + ")";

        verifyArrays(table, ddl);
    }

    @Test
    public void testDefaultArraysPrimaryKey() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + "("
                + "pk INTEGER NOT NULL,"
                + "int INTEGER[5] DEFAULT ARRAY[-100, 50],"
                + "uint UNSIGNED_INT[5] DEFAULT ARRAY[100, 50], "
                + "bint BIGINT[5] DEFAULT ARRAY[-200, 100],"
                + "ubint UNSIGNED_LONG[5] DEFAULT ARRAY[200, 100],"
                + "tint TINYINT[5] DEFAULT ARRAY[-50, 25],"
                + "utint UNSIGNED_TINYINT[5] DEFAULT ARRAY[50, 25],"
                + "sint SMALLINT[5] DEFAULT ARRAY[-10, 5],"
                + "usint UNSIGNED_SMALLINT[5] DEFAULT ARRAY[10, 5],"
                + "flo FLOAT[5] DEFAULT ARRAY[-100.8, 50.4],"
                + "uflo UNSIGNED_FLOAT[5] DEFAULT ARRAY[100.9, 50.45],"
                + "doub DOUBLE[5] DEFAULT ARRAY[-200.5, 100.25],"
                + "udoubl UNSIGNED_DOUBLE[5] DEFAULT ARRAY[200.8, 100.4],"
                + "dec DECIMAL[5] DEFAULT ARRAY[-654624562.3462642362, 3462642362.654624562],"
                + "bool BOOLEAN[5] DEFAULT ARRAY[true, false],"
                + "tim TIME[5] DEFAULT ARRAY["
                + "time '1900-10-01 14:03:22.559',"
                + "time '1990-10-01 14:03:22.559'],"
                + "dat DATE[5] DEFAULT ARRAY["
                + "date '1900-10-01 14:03:22.559',"
                + "date '1990-10-01 14:03:22.559'],"
                + "timest TIMESTAMP[5] DEFAULT ARRAY["
                + "timestamp '1900-10-01 14:03:22.559',"
                + "timestamp '1990-10-01 14:03:22.559'],"
                + "utim UNSIGNED_TIME[5] DEFAULT ARRAY["
                + "time '2005-10-01 14:03:22.559',"
                + "time '2006-10-01 14:03:22.559'],"
                + "udat UNSIGNED_DATE[5] DEFAULT ARRAY["
                + "date '2005-10-01 14:03:22.559',"
                + "date '2006-10-01 14:03:22.559'],"
                + "utimest UNSIGNED_TIMESTAMP[5] DEFAULT ARRAY["
                + "timestamp '2005-10-01 14:03:22.559',"
                + "timestamp '2006-10-01 14:03:22.559'],"
                + "vc VARCHAR[5] DEFAULT ARRAY['ABCD', 'XY'],"
                + "c CHAR(5)[5] DEFAULT ARRAY['EF', 'Z'],"
                + "bin BINARY(5)[5] NOT NULL DEFAULT ARRAY ['MNOP', 'mnop'],"
                + "CONSTRAINT pk_key PRIMARY KEY (pk, bin)"
                + ")";

        verifyArrays(table, ddl);
    }

    private void verifyArrays(String table, String ddl) throws SQLException {
        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);
        String dml = "UPSERT INTO " + table + " VALUES (1)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertArrayEquals(new int[]{-100, 50}, (int[])(rs.getArray(2).getArray()));
        assertArrayEquals(new int[]{100, 50}, (int[])(rs.getArray(3).getArray()));
        assertArrayEquals(new long[]{-200, 100}, (long[])(rs.getArray(4).getArray()));
        assertArrayEquals(new long[]{200, 100}, (long[])(rs.getArray(5).getArray()));
        assertArrayEquals(new byte[]{-50, 25}, (byte[])(rs.getArray(6).getArray()));
        assertArrayEquals(new byte[]{50, 25}, (byte[])(rs.getArray(7).getArray()));
        assertArrayEquals(new short[]{-10, 5}, (short[])(rs.getArray(8).getArray()));
        assertArrayEquals(new short[]{10, 5}, (short[])(rs.getArray(9).getArray()));
        assertArrayEquals(
                new float[]{new Float(-100.8), new Float(50.4)},
                (float[])(rs.getArray(10).getArray()), 0);
        assertArrayEquals(
                new float[]{new Float(100.9), new Float(50.45)},
                (float[])(rs.getArray(11).getArray()), 0);
        assertArrayEquals(new double[]{-200.5, 100.25}, (double[])(rs.getArray(12).getArray()), 0);
        assertArrayEquals(new double[]{200.8, 100.4}, (double[])(rs.getArray(13).getArray()), 0);
        assertArrayEquals(
                new BigDecimal[]{
                        new BigDecimal("-654624562.3462642362"),
                        new BigDecimal("3462642362.654624562")},
                (BigDecimal[])(rs.getArray(14).getArray()));
        assertArrayEquals(new boolean[]{true, false}, (boolean[])(rs.getArray(15).getArray()));
        assertArrayEquals(
                new Time[]{
                        DateUtil.parseTime("1900-10-01 14:03:22.559"),
                        DateUtil.parseTime("1990-10-01 14:03:22.559")},
                (Time[])(rs.getArray(16).getArray()));
        assertArrayEquals(
                new Date[]{
                        DateUtil.parseDate("1900-10-01 14:03:22.559"),
                        DateUtil.parseDate("1990-10-01 14:03:22.559")},
                (Date[])(rs.getArray(17).getArray()));
        assertArrayEquals(
                new Timestamp[]{
                        DateUtil.parseTimestamp("1900-10-01 14:03:22.559"),
                        DateUtil.parseTimestamp("1990-10-01 14:03:22.559")},
                (Timestamp[])(rs.getArray(18).getArray()));
        assertArrayEquals(
                new Time[]{
                        DateUtil.parseTime("2005-10-01 14:03:22.559"),
                        DateUtil.parseTime("2006-10-01 14:03:22.559")},
                (Time[])(rs.getArray(19).getArray()));
        assertArrayEquals(
                new Date[]{
                        DateUtil.parseDate("2005-10-01 14:03:22.559"),
                        DateUtil.parseDate("2006-10-01 14:03:22.559")},
                (Date[])(rs.getArray(20).getArray()));
        assertArrayEquals(
                new Timestamp[]{
                        DateUtil.parseTimestamp("2005-10-01 14:03:22.559"),
                        DateUtil.parseTimestamp("2006-10-01 14:03:22.559")},
                (Timestamp[])(rs.getArray(21).getArray()));
        assertArrayEquals(new String[]{"ABCD", "XY"}, (String[])(rs.getArray(22).getArray()));

        String[] expected = new String[] {"EF","Z"};
        Array array = conn.createArrayOf("CHAR", expected);
        assertTrue(rs.getArray(23).equals(array));

        byte[][] expectedByteArray = new byte[][] {
                ByteUtil.fillKey(new byte[] {'M', 'N', 'O', 'P'}, 5),
                ByteUtil.fillKey(new byte[] {'m', 'n', 'o', 'p'}, 5)
        };
        assertArrayEquals(expectedByteArray, (byte[][])rs.getArray(24).getArray());
    }

    @Test
    public void testDefaultArrayWithNull() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + "("
                + "pk INTEGER PRIMARY KEY,"
                + "c1 VARCHAR[5] DEFAULT ARRAY[NULL, 'ABCD', 'XY'],"
                + "c2 VARCHAR[5] DEFAULT ARRAY['ABCD', NULL, 'XY'],"
                + "c3 VARCHAR[5] DEFAULT ARRAY['ABCD', 'XY', NULL]"
                + ")";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);
        String dml = "UPSERT INTO " + table + " VALUES (1)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertArrayEquals(new String[]{null, "ABCD", "XY"}, (String[])(rs.getArray(2).getArray()));
        assertArrayEquals(new String[]{"ABCD", null, "XY"}, (String[])(rs.getArray(3).getArray()));
        assertArrayEquals(new String[]{"ABCD", "XY", null}, (String[])(rs.getArray(4).getArray()));
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultArrayWithFixedWidthNull() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + "("
                + "pk INTEGER PRIMARY KEY,"
                + "c1 INTEGER[5] DEFAULT ARRAY[NULL, 2, 3],"
                + "c2 INTEGER[5] DEFAULT ARRAY[1, NULL, 3],"
                + "c3 INTEGER[5] DEFAULT ARRAY[1, 2, NULL]"
                + ")";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);
        String dml = "UPSERT INTO " + table + " VALUES (1)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT * FROM " + table + " WHERE pk = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertArrayEquals(new int[]{0, 2, 3}, (int[])(rs.getArray(2).getArray()));
        assertArrayEquals(new int[]{1, 0, 3}, (int[])(rs.getArray(3).getArray()));
        assertArrayEquals(new int[]{1, 2, 0}, (int[])(rs.getArray(4).getArray()));
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultNull() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE " + table + " (" +
                "pk INTEGER PRIMARY KEY, " +
                "def INTEGER DEFAULT NULL)";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + table + " VALUES (1)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs =
                conn.createStatement().executeQuery("SELECT * FROM " + table + " WHERE pk = 1");
        assertTrue(rs.next());
        assertEquals(1, rs.getInt(1));
        assertEquals(0, rs.getInt(2));
        assertTrue(rs.wasNull());
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultCoveredColumn() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + " ("
                + "pk INTEGER PRIMARY KEY,"
                + "c1 INTEGER,"
                + "c2 INTEGER DEFAULT 100)";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);
        conn.commit();

        String idx = generateUniqueName();
        ddl = "CREATE INDEX " + idx + " on " + table + " (c1) INCLUDE (c2)";
        conn.createStatement().execute(ddl);
        conn.commit();

        String dml = "UPSERT INTO " + table + " VALUES (1, 2)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs =
                conn.createStatement().executeQuery("SELECT c2 FROM " + table + " WHERE c1 = 2");
        assertTrue(rs.next());
        assertEquals(100, rs.getInt(1));
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultIndexed() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + " ("
                + "pk INTEGER PRIMARY KEY,"
                + "c1 INTEGER,"
                + "c2 INTEGER DEFAULT 100)";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);
        conn.commit();

        String idx = generateUniqueName();
        ddl = "CREATE INDEX " + idx + " on " + table + " (c2)";
        conn.createStatement().execute(ddl);
        conn.commit();

        String dml = "UPSERT INTO " + table + " VALUES (1, 2)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs =
                conn.createStatement().executeQuery("SELECT c2 FROM " + table + " WHERE c2 = 100");
        assertTrue(rs.next());
        assertEquals(100, rs.getInt(1));
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("SELECT c2 FROM " + table + " WHERE c2 = 5");
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultLocalIndexed() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + " ("
                + "pk INTEGER PRIMARY KEY,"
                + "c1 INTEGER,"
                + "c2 INTEGER DEFAULT 100)";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);
        conn.commit();

        String idx = generateUniqueName();
        ddl = "CREATE LOCAL INDEX " + idx + " on " + table + " (c2)";
        conn.createStatement().execute(ddl);
        conn.commit();

        String dml = "UPSERT INTO " + table + " VALUES (1, 2)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs =
                conn.createStatement().executeQuery("SELECT c2 FROM " + table + " WHERE c2 = 100");
        assertTrue(rs.next());
        assertEquals(100, rs.getInt(1));
        assertFalse(rs.next());

        rs = conn.createStatement().executeQuery("SELECT c2 FROM " + table + " WHERE c2 = 5");
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultFunctionalIndexed() throws Exception {
        String table = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + table + " ("
                + "pk INTEGER PRIMARY KEY,"
                + "c1 INTEGER,"
                + "c2 INTEGER DEFAULT 100)";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);
        conn.commit();

        String idx = generateUniqueName();
        ddl = "CREATE INDEX " + idx + " on " + table + " (c1 + c2)";
        conn.createStatement().execute(ddl);
        conn.commit();

        String dml = "UPSERT INTO " + table + " VALUES (1, 2)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs = conn.createStatement()
                .executeQuery("SELECT c2 FROM " + table + " WHERE c1 + c2 = 102");
        assertTrue(rs.next());
        assertEquals(100, rs.getInt(1));
        assertFalse(rs.next());
    }

    @Test
    public void testDefaultSelectWhere() throws Exception {
        String ddl = "CREATE TABLE IF NOT EXISTS " + sharedTable2 + " (" +
                "pk INTEGER PRIMARY KEY,"
                + "c1 INTEGER DEFAULT 1 + 9,"
                + "c2 DOUBLE DEFAULT SQRT(91506.25),"
                + "c3 DECIMAL DEFAULT TO_NUMBER('$123.33', '\u00A4###.##'),"
                + "c4 VARCHAR DEFAULT 'AB' || 'CD',"
                + "c5 CHAR(5) DEFAULT 'E' || 'F',"
                + "c6 INTEGER DEFAULT \"MONTH\"(TO_TIMESTAMP('2015-6-05'))"
                + ")";

        Connection conn = DriverManager.getConnection(getUrl());
        conn.createStatement().execute(ddl);

        String dml = "UPSERT INTO " + sharedTable2 + " VALUES (1)";
        conn.createStatement().execute(dml);
        conn.commit();

        ResultSet rs =
                conn.createStatement().executeQuery("SELECT c1 FROM " + sharedTable2 + " WHERE c1 = 10");
        assertTrue(rs.next());
        assertEquals(10, rs.getInt(1));

        rs = conn.createStatement().executeQuery("SELECT c4 FROM " + sharedTable2 + " WHERE c4 = 'ABCD'");
        assertTrue(rs.next());
        assertEquals("ABCD", rs.getString(1));
    }
}