/*
 *
 * MariaDB Client for Java
 *
 * Copyright (c) 2012-2014 Monty Program Ab.
 * Copyright (c) 2015-2017 MariaDB Ab.
 *
 * This library is free software; you can redistribute it and/or modify it under
 * the terms of the GNU Lesser General Public License as published by the Free
 * Software Foundation; either version 2.1 of the License, or (at your option)
 * any later version.
 *
 * This library is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
 * for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License along
 * with this library; if not, write to Monty Program Ab info@montyprogram.com.
 *
 * This particular MariaDB Client for Java file is work
 * derived from a Drizzle-JDBC. Drizzle-JDBC file which is covered by subject to
 * the following copyright and notice provisions:
 *
 * Copyright (c) 2009-2011, Marcus Eriksson
 *
 * Redistribution and use in source and binary forms, with or without modification,
 * are permitted provided that the following conditions are met:
 * Redistributions of source code must retain the above copyright notice, this list
 * of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice, this
 * list of conditions and the following disclaimer in the documentation and/or
 * other materials provided with the distribution.
 *
 * Neither the name of the driver nor the names of its contributors may not be
 * used to endorse or promote products derived from this software without specific
 * prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS  AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
 * IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
 * NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
 * WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY
 * OF SUCH DAMAGE.
 *
 */

package org.mariadb.jdbc;

import org.junit.Assert;
import org.junit.Assume;
import org.junit.BeforeClass;
import org.junit.Test;

import java.sql.*;

import static org.junit.Assert.*;

public class ResultSetTest extends BaseTest {
    /**
     * Initialisation.
     *
     * @throws SQLException exception
     */
    @BeforeClass()
    public static void initClass() throws SQLException {
        createTable("result_set_test", "id int not null primary key auto_increment, name char(20)");
    }

    @Test
    public void isBeforeFirstFetchTest() throws SQLException {
        insertRows(1);
        Statement statement = sharedConnection.createStatement();
        statement.setFetchSize(1);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_test");
        assertTrue(resultSet.isBeforeFirst());
        while (resultSet.next()) {
            assertFalse(resultSet.isBeforeFirst());
        }
        assertFalse(resultSet.isBeforeFirst());
        resultSet.close();
        try {
            resultSet.isBeforeFirst();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }

    /**
     * CONJ-424: Calling getGeneratedKeys() two times on the same connection, with different
     * PreparedStatement on a table that does not have an auto increment.
     */
    @Test
    public void testGeneratedKeysWithoutTableAutoIncrementCalledTwice() throws SQLException {
        createTable("gen_key_test_resultset", "name VARCHAR(40) NOT NULL, xml MEDIUMTEXT");
        String sql = "INSERT INTO gen_key_test_resultset (name, xml) VALUES (?, ?)";

        for (int i = 0; i < 2; i++) {
            try (PreparedStatement preparedStatement = sharedConnection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

                preparedStatement.setString(1, "John");
                preparedStatement.setString(2, "<xml/>");
                preparedStatement.executeUpdate();

                try (ResultSet generatedKeysResultSet = preparedStatement.getGeneratedKeys()) {
                    assertFalse(generatedKeysResultSet.next());
                }

            }
        }
    }

    @Test
    public void isBeforeFirstFetchZeroRowsTest() throws SQLException {
        insertRows(2);
        Statement statement = sharedConnection.createStatement();
        statement.setFetchSize(1);
        try (ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_test")) {
            assertTrue(resultSet.isBeforeFirst());
            assertTrue(resultSet.next());
            assertFalse(resultSet.isBeforeFirst());
            resultSet.close();
            try {
                resultSet.isBeforeFirst();
                fail("The above row should have thrown an SQLException");
            } catch (SQLException e) {
                //Make sure an exception has been thrown informing us that the ResultSet was closed
                assertTrue(e.getMessage().contains("closed"));
            }
        }
    }

    @Test
    public void isClosedTest() throws SQLException {
        insertRows(1);
        ResultSet resultSet = sharedConnection.createStatement().executeQuery("SELECT * FROM result_set_test");
        assertFalse(resultSet.isClosed());
        while (resultSet.next()) {
            assertFalse(resultSet.isClosed());
        }
        assertFalse(resultSet.isClosed());
        resultSet.close();
        assertTrue(resultSet.isClosed());
    }

    @Test
    public void isBeforeFirstTest() throws SQLException {
        insertRows(1);
        ResultSet resultSet = sharedConnection.createStatement().executeQuery("SELECT * FROM result_set_test");
        assertTrue(resultSet.isBeforeFirst());
        while (resultSet.next()) {
            assertFalse(resultSet.isBeforeFirst());
        }
        assertFalse(resultSet.isBeforeFirst());
        resultSet.close();
        try {
            resultSet.isBeforeFirst();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }

    }

    @Test
    public void isFirstZeroRowsTest() throws SQLException {
        insertRows(0);
        Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = stmt.executeQuery("SELECT * FROM result_set_test");
        assertFalse(resultSet.isFirst());
        assertFalse(resultSet.next()); //No more rows after this
        assertFalse(resultSet.isFirst()); // connectorj compatibility
        assertFalse(resultSet.first());
        resultSet.close();
        try {
            resultSet.isFirst();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }

    @Test
    public void isFirstOneRowsTest() throws SQLException {
        insertRows(1);
        Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = stmt.executeQuery("SELECT * FROM result_set_test");
        assertFalse(resultSet.isFirst());
        assertTrue(resultSet.next());
        assertTrue(resultSet.isFirst());
        assertFalse(resultSet.next());
        assertFalse(resultSet.isFirst());
        assertTrue(resultSet.first());
        assertEquals(1, resultSet.getInt(1));
        resultSet.close();
        try {
            resultSet.isFirst();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }

    @Test
    public void isFirstTwoRowsTest() throws SQLException {
        isFirstTwoRowsTest(true);
        isFirstTwoRowsTest(false);
    }

    private void isFirstTwoRowsTest(boolean fetching) throws SQLException {
        insertRows(2);
        try (Statement statement = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
            if (fetching) statement.setFetchSize(1);
            ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_test");
            assertFalse(resultSet.isFirst());
            assertTrue(resultSet.next());
            assertTrue(resultSet.isFirst());
            assertTrue(resultSet.next());
            assertFalse(resultSet.isFirst());
            assertFalse(resultSet.next()); //No more rows after this
            assertFalse(resultSet.isFirst());
            assertTrue(resultSet.first());
            assertEquals(1, resultSet.getInt(1));
            resultSet.close();
            try {
                resultSet.isFirst();
                fail("The above row should have thrown an SQLException");
            } catch (SQLException e) {
                //Make sure an exception has been thrown informing us that the ResultSet was closed
                assertTrue(e.getMessage().contains("closed"));
            }
        }
    }

    @Test
    public void isLastZeroRowsTest() throws SQLException {
        insertRows(0);
        ResultSet resultSet = sharedConnection.createStatement().executeQuery("SELECT * FROM result_set_test");
        assertFalse(resultSet.isLast()); // connectorj compatibility
        assertFalse(resultSet.next()); //No more rows after this
        assertFalse(resultSet.isLast());
        assertFalse(resultSet.last());
        resultSet.close();
        try {
            resultSet.isLast();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }

    @Test
    public void isLastOneRowsTest() throws SQLException {
        insertRows(1);
        ResultSet resultSet = sharedConnection.createStatement().executeQuery("SELECT * FROM result_set_test");
        assertFalse(resultSet.isLast());
        assertTrue(resultSet.next());
        assertTrue(resultSet.isLast());
        assertTrue(resultSet.last());
        assertFalse(resultSet.next()); //No more rows after this
        assertFalse(resultSet.isLast());
        assertTrue(resultSet.last());

        resultSet.close();
        try {
            resultSet.isLast();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }

    @Test
    public void isLastTwoRowsTest() throws SQLException {
        isLastTwoRowsTest(true);
        isLastTwoRowsTest(false);
    }

    private void isLastTwoRowsTest(boolean fetching) throws SQLException {
        insertRows(2);
        try (Statement statement = sharedConnection.createStatement()) {
            if (fetching) statement.setFetchSize(1);
            ResultSet resultSet = statement.executeQuery("SELECT * FROM result_set_test");
            assertFalse(resultSet.isLast());
            assertTrue(resultSet.next());
            assertFalse(resultSet.isLast());
            assertTrue(resultSet.next());
            assertTrue(resultSet.isLast());
            assertFalse(resultSet.next()); //No more rows after this
            assertFalse(resultSet.isLast());
            assertTrue(resultSet.last());
            assertEquals(2, resultSet.getInt(1));
            resultSet.close();
            try {
                resultSet.isLast();
                fail("The above row should have thrown an SQLException");
            } catch (SQLException e) {
                //Make sure an exception has been thrown informing us that the ResultSet was closed
                assertTrue(e.getMessage().contains("closed"));
            }
        }
    }

    @Test
    public void isAfterLastZeroRowsTest() throws SQLException {
        insertRows(0);
        ResultSet resultSet = sharedConnection.createStatement().executeQuery("SELECT * FROM result_set_test");
        assertFalse(resultSet.isAfterLast());
        assertFalse(resultSet.next()); //No more rows after this
        assertFalse(resultSet.isAfterLast());
        resultSet.close();
        try {
            resultSet.isAfterLast();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }

    @Test
    public void isAfterLastTwoRowsTest() throws SQLException {
        insertRows(2);
        ResultSet resultSet = sharedConnection.createStatement().executeQuery("SELECT * FROM result_set_test");
        assertFalse(resultSet.isAfterLast());
        assertTrue(resultSet.next());
        assertFalse(resultSet.isAfterLast());
        assertTrue(resultSet.next());
        assertFalse(resultSet.isAfterLast());
        assertFalse(resultSet.next()); //No more rows after this
        assertTrue(resultSet.isAfterLast());
        assertTrue(resultSet.last());
        assertEquals(2, resultSet.getInt(1));
        resultSet.close();
        try {
            resultSet.isAfterLast();
            fail("The above row should have thrown an SQLException");
        } catch (SQLException e) {
            //Make sure an exception has been thrown informing us that the ResultSet was closed
            assertTrue(e.getMessage().contains("closed"));
        }
    }

    @Test
    public void previousTest() throws SQLException {
        insertRows(2);
        Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        try (ResultSet rs = stmt.executeQuery("SELECT * FROM result_set_test")) {
            assertFalse(rs.previous());
            assertTrue(rs.next());
            assertEquals(1, rs.getInt(1));
            assertFalse(rs.previous());
            assertTrue(rs.next());
            assertEquals(1, rs.getInt(1));
            assertTrue(rs.next());
            assertEquals(2, rs.getInt(1));
            assertTrue(rs.previous());
            assertEquals(1, rs.getInt(1));
            assertTrue(rs.last());
            assertEquals(2, rs.getInt(1));
        }
    }

    @Test
    public void firstTest() throws SQLException {
        insertRows(2);
        Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery("SELECT * FROM result_set_test");
        assertTrue(rs.next());
        assertTrue(rs.next());
        assertTrue(rs.first());
        assertTrue(rs.isFirst());
        rs.close();
        try {
            rs.first();
            fail("cannot call first() on a closed result set");
        } catch (SQLException sqlex) {
            //eat exception
        }
    }

    @Test
    public void lastTest() throws SQLException {
        insertRows(2);
        Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery("SELECT * FROM result_set_test");
        assertTrue(rs.last());
        assertTrue(rs.isLast());
        assertFalse(rs.next());
        rs.first();
        rs.close();
        try {
            rs.last();
            fail("cannot call last() on a closed result set");
        } catch (SQLException sqlex) {
            //eat exception
        }
    }

    @Test
    public void relativeForwardTest() throws SQLException {
        insertRows(3);
        Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery("SELECT * FROM result_set_test");
        assertTrue(rs.isBeforeFirst());
        assertFalse(rs.isFirst());
        assertFalse(rs.isLast());
        assertFalse(rs.isAfterLast());

        assertTrue(rs.relative(2));
        assertFalse(rs.isBeforeFirst());
        assertFalse(rs.isFirst());
        assertFalse(rs.isLast());
        assertFalse(rs.isAfterLast());

        assertTrue(rs.relative(1));
        assertFalse(rs.isBeforeFirst());
        assertFalse(rs.isFirst());
        assertTrue(rs.isLast());
        assertFalse(rs.isAfterLast());

        assertFalse(rs.relative(1));
        assertFalse(rs.isBeforeFirst());
        assertFalse(rs.isFirst());
        assertFalse(rs.isLast());
        assertTrue(rs.isAfterLast());

        assertFalse(rs.next());
        rs.close();
    }

    @Test
    public void relativeBackwardTest() throws SQLException {
        insertRows(3);
        Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = stmt.executeQuery("SELECT * FROM result_set_test");
        rs.afterLast();
        assertFalse(rs.isBeforeFirst());
        assertFalse(rs.isFirst());
        assertFalse(rs.isLast());
        assertTrue(rs.isAfterLast());

        assertTrue(rs.relative(-2));
        assertFalse(rs.isBeforeFirst());
        assertFalse(rs.isFirst());
        assertFalse(rs.isLast());
        assertFalse(rs.isAfterLast());

        assertTrue(rs.relative(-1));
        assertFalse(rs.isBeforeFirst());
        assertTrue(rs.isFirst());
        assertFalse(rs.isLast());
        assertFalse(rs.isAfterLast());

        assertFalse(rs.relative(-1));
        assertTrue(rs.isBeforeFirst());
        assertFalse(rs.isFirst());
        assertFalse(rs.isLast());
        assertFalse(rs.isAfterLast());

        assertFalse(rs.previous());
        rs.close();
    }

    private void insertRows(int numberOfRowsToInsert) throws SQLException {
        sharedConnection.createStatement().execute("truncate result_set_test ");
        for (int i = 1; i <= numberOfRowsToInsert; i++) {
            sharedConnection.createStatement().executeUpdate("INSERT INTO result_set_test VALUES(" + i
                    + ", 'row" + i + "')");
        }
    }

    /**
     * CONJ-403: NPE in getGenerated keys.
     *
     * @throws SQLException if error occur
     */
    @Test
    public void generatedKeyNpe() throws SQLException {
        createTable("generatedKeyNpe", "id int not null primary key auto_increment, val int");
        Statement statement = sharedConnection.createStatement();
        statement.execute("INSERT INTO generatedKeyNpe(val) values (0)", Statement.RETURN_GENERATED_KEYS);
        try (ResultSet rs = statement.getGeneratedKeys()) {
            assertTrue(rs.next());
        }
    }

    @Test
    public void generatedKeyError() throws SQLException {
        createTable("generatedKeyNpe", "id int not null primary key auto_increment, val int");
        Statement statement = sharedConnection.createStatement();
        statement.execute("INSERT INTO generatedKeyNpe(val) values (0)");
        try {
            statement.getGeneratedKeys();
            fail();
        } catch (SQLException sqle) {
            assertEquals("Cannot return generated keys : query was not set with Statement.RETURN_GENERATED_KEYS", sqle.getMessage());
        }
    }

    @Test
    public void testResultSetAbsolute() throws Exception {
        insertRows(50);
        try (Statement statement = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY)) {
            statement.setFetchSize(10);
            try (ResultSet rs = statement.executeQuery("SELECT * FROM result_set_test")) {
                assertFalse(rs.absolute(52));
                assertFalse(rs.absolute(-52));

                assertTrue(rs.absolute(42));
                assertEquals("row42", rs.getString(2));

                assertTrue(rs.absolute(-11));
                assertEquals("row40", rs.getString(2));

                assertTrue(rs.absolute(0));
                assertTrue(rs.isBeforeFirst());

                assertFalse(rs.absolute(51));
                assertTrue(rs.isAfterLast());

                assertTrue(rs.absolute(-1));
                assertEquals("row50", rs.getString(2));

                assertTrue(rs.absolute(-50));
                assertEquals("row1", rs.getString(2));
            }
        }
    }

    @Test
    public void testResultSetIsAfterLast() throws Exception {
        insertRows(2);
        try (Statement statement = sharedConnection.createStatement()) {
            statement.setFetchSize(1);
            try (ResultSet rs = statement.executeQuery("SELECT * FROM result_set_test")) {
                assertFalse(rs.isLast());
                assertFalse(rs.isAfterLast());
                assertTrue(rs.next());
                assertFalse(rs.isLast());
                assertFalse(rs.isAfterLast());
                assertTrue(rs.next());
                assertTrue(rs.isLast());
                assertFalse(rs.isAfterLast());
                assertFalse(rs.next());
                assertFalse(rs.isLast());
                assertTrue(rs.isAfterLast());
            }

            insertRows(0);
            try (ResultSet rs = statement.executeQuery("SELECT * FROM result_set_test")) {
                assertFalse(rs.isAfterLast());
                assertFalse(rs.isLast());
                assertFalse(rs.next());
                assertFalse(rs.isLast());
                assertFalse(rs.isAfterLast()); //jdbc indicate that results with no rows return false.
            }
        }
    }


    @Test
    public void testResultSetAfterLast() throws Exception {
        try (Statement statement = sharedConnection.createStatement()) {
            checkLastResultSet(statement);
            statement.setFetchSize(1);
            checkLastResultSet(statement);

        }
    }

    private void checkLastResultSet(Statement statement) throws SQLException {

        insertRows(10);
        try (ResultSet rs = statement.executeQuery("SELECT * FROM result_set_test")) {

            assertTrue(rs.last());
            assertFalse(rs.isAfterLast());
            assertTrue(rs.isLast());

            rs.afterLast();
            assertTrue(rs.isAfterLast());
            assertFalse(rs.isLast());

        }

        insertRows(0);
        try (ResultSet rs = statement.executeQuery("SELECT * FROM result_set_test")) {

            assertFalse(rs.last());
            assertFalse(rs.isAfterLast());
            assertFalse(rs.isLast());

            rs.afterLast();
            assertFalse(rs.isAfterLast()); //jdbc indicate that results with no rows return false.
            assertFalse(rs.isLast());
        }

    }

    @Test
    public void testStreamInsensitive() throws Exception {
        createTable("testStreamInsensitive", "s1 varchar(20)");

        for (int r = 0; r < 20; r++) {
            sharedConnection.createStatement().executeUpdate("insert into testStreamInsensitive values('V" + r + "')");
        }
        Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(10);

        //reading forward
        ResultSet rs = stmt.executeQuery("select * from testStreamInsensitive");
        for (int i = 0; i < 20; i++) {
            assertTrue(rs.next());
            assertEquals("V" + i, rs.getString(1));
        }
        assertFalse(rs.next());

        rs = stmt.executeQuery("select * from testStreamInsensitive");
        for (int i = 0; i < 20; i++) {
            assertFalse(rs.isAfterLast());
            assertTrue(rs.next());
            assertEquals("V" + i, rs.getString(1));
            assertFalse(rs.isAfterLast());
        }
        assertFalse(rs.isAfterLast());
        assertFalse(rs.next());
        assertTrue(rs.isAfterLast());

        rs = stmt.executeQuery("select * from testStreamInsensitive");
        assertTrue(rs.absolute(20));
        assertEquals("V19", rs.getString(1));
        assertFalse(rs.isAfterLast());
        assertFalse(rs.absolute(21));
        assertTrue(rs.isAfterLast());

        //reading backward
        rs = stmt.executeQuery("select * from testStreamInsensitive");
        rs.afterLast();
        for (int i = 19; i >= 0; i--) {
            assertTrue(rs.previous());
            assertEquals("V" + i, rs.getString(1));
        }
        assertFalse(rs.previous());

        rs = stmt.executeQuery("select * from testStreamInsensitive");
        assertTrue(rs.last());
        assertEquals("V19", rs.getString(1));

        assertTrue(rs.first());
        assertEquals("V0", rs.getString(1));

    }

    @Test
    public void testStreamForward() throws Exception {
        createTable("testStreamForward", "s1 varchar(20)");

        for (int r = 0; r < 20; r++) {
            sharedConnection.createStatement().executeUpdate("insert into testStreamForward values('V" + r + "')");
        }
        Statement stmt = sharedConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(10);

        //reading forward
        ResultSet rs = stmt.executeQuery("select * from testStreamForward");
        for (int i = 0; i < 20; i++) {
            assertTrue(rs.next());
            assertEquals("V" + i, rs.getString(1));
        }
        assertFalse(rs.next());

        //checking isAfterLast that may need to fetch next result
        rs = stmt.executeQuery("select * from testStreamForward");
        for (int i = 0; i < 20; i++) {
            assertFalse(rs.isAfterLast());
            assertTrue(rs.next());
            assertEquals("V" + i, rs.getString(1));
            assertFalse(rs.isAfterLast());
        }
        assertFalse(rs.isAfterLast());
        assertFalse(rs.next());
        assertTrue(rs.isAfterLast());

        //reading backward
        rs = stmt.executeQuery("select * from testStreamForward");
        rs.afterLast();
        try {
            rs.previous();
            fail("Must have thrown exception since previous is not possible when fetching");
        } catch (SQLException sqle) {
            assertTrue(sqle.getMessage().contains("Invalid operation for result set type TYPE_FORWARD_ONLY"));
        }

        rs = stmt.executeQuery("select * from testStreamForward");
        assertTrue(rs.last());
        assertEquals("V19", rs.getString(1));

        try {
            rs.first();
            fail("Must have thrown exception since previous is not possible when fetching");
        } catch (SQLException sqle) {
            assertTrue(sqle.getMessage().contains("Invalid operation for result set type TYPE_FORWARD_ONLY"));
        }

    }

    /**
     * [CONJ-437] getString on field with ZEROFILL doesn't have the '0' leading chars when using binary protocol.
     *
     * @throws SQLException if any abnormal error occur
     */
    @Test
    public void leadingZeroTest() throws SQLException {
        createTable("leadingZero", "t1 TINYINT(3) unsigned zerofill"
                + ", t2 TINYINT(8) unsigned zerofill"
                + ", t3 TINYINT unsigned zerofill"
                + ", t4 smallint(3) unsigned zerofill"
                + ", t5 smallint(8) unsigned zerofill"
                + ", t6 smallint unsigned zerofill"
                + ", t7 MEDIUMINT(3) unsigned zerofill"
                + ", t8 MEDIUMINT(8) unsigned zerofill"
                + ", t9 MEDIUMINT unsigned zerofill"
                + ", t10 INT(3) unsigned zerofill"
                + ", t11 INT(8) unsigned zerofill"
                + ", t12 INT unsigned zerofill"
                + ", t13 BIGINT(3) unsigned zerofill"
                + ", t14 BIGINT(8) unsigned zerofill"
                + ", t15 BIGINT unsigned zerofill"
                + ", t16 DECIMAL(6,3) unsigned zerofill"
                + ", t17 DECIMAL(11,3) unsigned zerofill"
                + ", t18 DECIMAL unsigned zerofill"
                + ", t19 FLOAT(6,3) unsigned zerofill"
                + ", t20 FLOAT(11,3) unsigned zerofill"
                + ", t21 FLOAT unsigned zerofill"
                + ", t22 DOUBLE(6,3) unsigned zerofill"
                + ", t23 DOUBLE(11,3) unsigned zerofill"
                + ", t24 DOUBLE unsigned zerofill");
        Statement stmt = sharedConnection.createStatement();
        stmt.executeUpdate("insert into leadingZero values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1.1,1.1,1.1,1.1,1.1,1.1,1.1,1.1,1.1), "
                + "(20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20.2,20.2,20.2,20.2,20.2,20.2,20.2,20.2,20.2)");

        //test text resultSet
        testLeadingZeroResult(stmt.executeQuery("select * from leadingZero"));

        //test binary resultSet
        if (!sharedUsePrepare()) {
            PreparedStatement pst1 = sharedConnection.prepareStatement("select * from leadingZero");
            ResultSet rs1 = pst1.executeQuery();
            testLeadingZeroResult(rs1);
        }

    }

    private void testLeadingZeroResult(ResultSet rs1) throws SQLException {
        assertTrue(rs1.next());
        assertEquals("001", rs1.getString(1));
        assertEquals("00000001", rs1.getString(2));
        assertEquals("001", rs1.getString(3));
        assertEquals("001", rs1.getString(4));
        assertEquals("00000001", rs1.getString(5));
        assertEquals("00001", rs1.getString(6));
        assertEquals("001", rs1.getString(7));
        assertEquals("00000001", rs1.getString(8));
        assertEquals("00000001", rs1.getString(9));
        assertEquals("001", rs1.getString(10));
        assertEquals("00000001", rs1.getString(11));
        assertEquals("0000000001", rs1.getString(12));
        assertEquals("001", rs1.getString(13));
        assertEquals("00000001", rs1.getString(14));
        assertEquals("00000000000000000001", rs1.getString(15));
        assertEquals("001.100", rs1.getString(16));
        assertEquals("00000001.100", rs1.getString(17));
        assertEquals("0000000001", rs1.getString(18));
        assertEquals("01.100", rs1.getString(19));
        assertEquals("0000001.100", rs1.getString(20));
        assertEquals("0000000001.1", rs1.getString(21));
        assertEquals("01.100", rs1.getString(22));
        assertEquals("0000001.100", rs1.getString(23));
        assertEquals("00000000000000000001.1", rs1.getString(24));

        assertTrue(rs1.next());
        assertEquals("020", rs1.getString(1));
        assertEquals("00000020", rs1.getString(2));
        assertEquals("020", rs1.getString(3));
        assertEquals("020", rs1.getString(4));
        assertEquals("00000020", rs1.getString(5));
        assertEquals("00020", rs1.getString(6));
        assertEquals("020", rs1.getString(7));
        assertEquals("00000020", rs1.getString(8));
        assertEquals("00000020", rs1.getString(9));
        assertEquals("020", rs1.getString(10));
        assertEquals("00000020", rs1.getString(11));
        assertEquals("0000000020", rs1.getString(12));
        assertEquals("020", rs1.getString(13));
        assertEquals("00000020", rs1.getString(14));
        assertEquals("00000000000000000020", rs1.getString(15));
        assertEquals("020.200", rs1.getString(16));
        assertEquals("00000020.200", rs1.getString(17));
        assertEquals("0000000020", rs1.getString(18));
        assertEquals("20.200", rs1.getString(19));
        assertEquals("0000020.200", rs1.getString(20));
        assertEquals("0000000020.2", rs1.getString(21));
        assertEquals("20.200", rs1.getString(22));
        assertEquals("0000020.200", rs1.getString(23));
        assertEquals("00000000000000000020.2", rs1.getString(24));
        assertFalse(rs1.next());

    }

    @Test
    public void firstForwardTest() throws SQLException {
        //first must always work when not streaming
        Statement stmt = sharedConnection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT 1");
        assertTrue(rs.first());
        assertFalse(rs.previous());
        assertTrue(rs.absolute(1));
        assertFalse(rs.relative(-1));

        //absolute operation must fail when streaming
        stmt.setFetchSize(1);
        rs = stmt.executeQuery("SELECT 1");
        try {
            rs.first();
            fail("absolute operation must fail when TYPE_FORWARD_ONLY and streaming");
        } catch (SQLException sqle) {
            assertTrue(sqle.getMessage().contains("Invalid operation for result set type TYPE_FORWARD_ONLY"));
        }
        try {
            rs.previous();
            fail("absolute operation must fail when TYPE_FORWARD_ONLY and streaming");
        } catch (SQLException sqle) {
            assertTrue(sqle.getMessage().contains("Invalid operation for result set type TYPE_FORWARD_ONLY"));
        }
        try {
            rs.absolute(1);
            fail("absolute operation must fail when TYPE_FORWARD_ONLY and streaming");
        } catch (SQLException sqle) {
            assertTrue(sqle.getMessage().contains("Invalid operation for result set type TYPE_FORWARD_ONLY"));
        }
        try {
            rs.relative(-1);
            fail("absolute operation must fail when TYPE_FORWARD_ONLY and streaming");
        } catch (SQLException sqle) {
            assertTrue(sqle.getMessage().contains("Invalid operation for result set type TYPE_FORWARD_ONLY"));
        }

    }

    /**
     * CONJ-429 : ResultSet.getDouble/getFloat may throws a NumberFormatException.
     *
     * @throws SQLException if any abnormal error occur
     */
    @Test
    public void testNumericType() throws SQLException {
        createTable("numericTypeTable",
                "t1 tinyint, "
                        + "t2 boolean, "
                        + "t3 smallint,  "
                        + "t4 mediumint, "
                        + "t5 int, "
                        + "t6 bigint, "
                        + "t7 decimal, "
                        + "t8 float, "
                        + "t9 double, "
                        + "t10 bit,"
                        + "t11 char(10),"
                        + "t12 varchar(10),"
                        + "t13 binary(10),"
                        + "t14 varbinary(10),"
                        + "t15 text,"
                        + "t16 blob,"
                        + "t17 date");

        try (Statement stmt = sharedConnection.createStatement()) {
            stmt.execute("INSERT into numericTypeTable values (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 'a', 'a', 'a', 'a', 'a', 'a', now())");
            try (ResultSet rs = stmt.executeQuery("select * from numericTypeTable")) {
                assertTrue(rs.next());
                floatDoubleCheckResult(rs);
            }
        }
        try (PreparedStatement preparedStatement = sharedConnection.prepareStatement("select * from numericTypeTable")) {
            try (ResultSet rs = preparedStatement.executeQuery()) {
                assertTrue(rs.next());
                floatDoubleCheckResult(rs);
            }

        }
    }

    private void floatDoubleCheckResult(ResultSet rs) throws SQLException {

        //getDouble
        //supported JDBC type :
        //TINYINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE, DECIMAL, NUMERIC, BIT, BOOLEAN, CHAR, VARCHAR, LONGVARCHAR
        for (int i = 1; i < 11; i++) {
            rs.getDouble(i);
            rs.getFloat(i);
        }

        for (int i = 11; i < 16; i++) {
            try {
                rs.getDouble(i);
                fail();
            } catch (SQLException sqle) {
                assertTrue(sqle.getMessage().contains("Incorrect format "));
            }
            try {
                rs.getFloat(i);
                fail();
            } catch (SQLException sqle) {
                assertTrue(sqle.getMessage().contains("Incorrect format "));
            }
        }
        for (int i = 16; i < 18; i++) {
            try {
                rs.getDouble(i);
            } catch (SQLException sqle) {
                assertTrue(sqle.getMessage().contains("not available"));
            }
            try {
                rs.getFloat(i);
                fail();
            } catch (SQLException sqle) {
                assertTrue(sqle.getMessage().contains("not available"));
            }
        }
    }

    /**
     * CONJ-496 : Driver not dealing with non zero decimal values.
     *
     * @throws SQLException if any abnormal error occur
     */
    @Test
    public void numericTestWithDecimal() throws SQLException {
        Statement stmt = sharedConnection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT 1 as test");
        assertTrue(rs.next());
        assertTrue(rs.getInt("test") == 1);
        assertTrue(rs.getByte("test") == 1);
        assertTrue(rs.getShort("test") == 1);

        rs = stmt.executeQuery("SELECT 1.3333 as test");
        assertTrue(rs.next());
        assertTrue(rs.getInt("test") == 1);
        assertTrue(rs.getByte("test") == 1);
        assertTrue(rs.getShort("test") == 1);
        assertTrue(rs.getLong("test") == 1);
        assertTrue(rs.getFloat("test") == 1.3333F);

        rs = stmt.executeQuery("SELECT 1.0 as test");
        assertTrue(rs.next());
        assertTrue(rs.getInt("test") == 1);
        assertTrue(rs.getByte("test") == 1);
        assertTrue(rs.getShort("test") == 1);
        assertTrue(rs.getLong("test") == 1);
        assertTrue(rs.getFloat("test") == 1.0F);

        rs = stmt.executeQuery("SELECT -1 as test");
        assertTrue(rs.next());
        assertTrue(rs.getInt("test") == -1);
        assertTrue(rs.getByte("test") == -1);
        assertTrue(rs.getShort("test") == -1);
        assertTrue(rs.getLong("test") == -1);

        rs = stmt.executeQuery("SELECT -1.0 as test");
        assertTrue(rs.next());
        assertTrue(rs.getInt("test") == -1);
        assertTrue(rs.getByte("test") == -1);
        assertTrue(rs.getShort("test") == -1);
        assertTrue(rs.getLong("test") == -1);
        assertTrue(rs.getFloat("test") == -1.0F);

        rs = stmt.executeQuery("SELECT -1.3333 as test");
        assertTrue(rs.next());
        assertTrue(rs.getInt("test") == -1);
        assertTrue(rs.getByte("test") == -1);
        assertTrue(rs.getShort("test") == -1);
        assertTrue(rs.getLong("test") == -1);
        assertTrue(rs.getFloat("test") == -1.3333F);
    }

    @Test
    public void nullField() throws SQLException {
        Assume.assumeTrue(isMariadbServer()); //'0000-00-00' doesn't work anymore on mysql 5.7.
        createTable("nullField", "t1 varchar(50), t2 timestamp NULL, t3 date, t4 year(4)");
        Statement stmt = sharedConnection.createStatement();
        stmt.execute("INSERT INTO nullField(t1,t2,t3,t4) values "
                + "(null, '0000-00-00 00:00:00', '0000-00-00', '0000'), "
                + "(null, null, null, null),"
                + "('aa', now(), now(), '2017')");

        ResultSet rs = stmt.executeQuery("SELECT * FROM nullField");
        assertTrue(rs.next());
        if (!sharedOptions().useServerPrepStmts) {
            assertNull(rs.getString(1));
            assertTrue(rs.wasNull());

            assertNull(rs.getTimestamp(2));
            assertTrue(rs.wasNull());

            assertEquals("0000-00-00 00:00:00", rs.getString(2));
            assertFalse(rs.wasNull());

            assertNull(rs.getDate(3));
            assertTrue(rs.wasNull());

            assertEquals("0000-00-00", rs.getString(3));
            assertFalse(rs.wasNull());

            assertEquals(Date.valueOf("0000-01-01"), rs.getDate(4));
            assertFalse(rs.wasNull());

            assertEquals(0, rs.getInt(4));
            assertFalse(rs.wasNull());

            assertEquals("0001-01-01", rs.getString(4));
            assertFalse(rs.wasNull());

        }

        assertTrue(rs.next());

        assertNull(rs.getTimestamp(2));
        assertTrue(rs.wasNull());

        assertNull(rs.getString(2));
        assertTrue(rs.wasNull());

        assertNull(rs.getDate(3));
        assertTrue(rs.wasNull());

        assertNull(rs.getString(3));
        assertTrue(rs.wasNull());

        assertNull(rs.getDate(4));
        assertTrue(rs.wasNull());

        assertNull(rs.getString(2));
        assertTrue(rs.wasNull());

        assertTrue(rs.next());

        assertNotNull(rs.getTimestamp(2));
        assertFalse(rs.wasNull());

        assertNotNull(rs.getString(2));
        assertFalse(rs.wasNull());

        assertNotNull(rs.getDate(3));
        assertFalse(rs.wasNull());

        assertNotNull(rs.getString(3));
        assertFalse(rs.wasNull());

        assertNotNull(rs.getDate(4));
        assertFalse(rs.wasNull());

        assertNotNull(rs.getString(2));
        assertFalse(rs.wasNull());

    }

    @Test
    public void doubleStringResults() throws SQLException {
        createTable("doubleStringResults", "i double, j float");
        Statement stmt = sharedConnection.createStatement();
        stmt.execute("INSERT INTO doubleStringResults VALUES (1.1, 1.2), (23, 24)");
        ResultSet rs = stmt.executeQuery("SELECT * FROM doubleStringResults");

        assertTrue(rs.next());
        assertEquals("1.1", rs.getString(1));
        assertEquals(1, rs.getInt(1));
        assertEquals(1.1, rs.getDouble(1), 0.0001);
        assertEquals("1.2", rs.getString(2));
        assertEquals(1, rs.getInt(2));
        assertEquals(1.2, rs.getFloat(2), 0.0001);

        assertTrue(rs.next());
        assertEquals("23", rs.getString(1));
        assertEquals(23, rs.getInt(1));
        assertEquals(23, rs.getDouble(1), 0.0001);
        assertEquals("24", rs.getString(2));
        assertEquals(24, rs.getInt(2));
        assertEquals(24, rs.getFloat(2), 0.0001);
    }

    @Test
    public void invisibleColumn() throws SQLException {

        //since 10.3.3
        Assume.assumeTrue(isMariadbServer() && minVersion(10, 3));
        cancelForVersion(10, 3, 0);
        cancelForVersion(10, 3, 1);
        cancelForVersion(10, 3, 2);

        createTable("invisible", "x INT, y INT INVISIBLE, z INT INVISIBLE NOT NULL DEFAULT 4");
        Statement stmt = sharedConnection.createStatement();
        stmt.execute("INSERT INTO invisible(x,y) VALUES (1,2)");

        try (PreparedStatement preparedStatement = sharedConnection.prepareStatement("SELECT * FROM invisible WHERE x = ?")) {
            ResultSetMetaData resultSetMetaData = preparedStatement.getMetaData();
            Assert.assertEquals(1, resultSetMetaData.getColumnCount());
            Assert.assertEquals("x", resultSetMetaData.getColumnName(1));
        }

        try (PreparedStatement preparedStatement = sharedConnection.prepareStatement("SELECT x,z FROM invisible WHERE x = ?")) {
            ResultSetMetaData resultSetMetaData = preparedStatement.getMetaData();
            Assert.assertEquals(2, resultSetMetaData.getColumnCount());
            Assert.assertEquals("x", resultSetMetaData.getColumnName(1));
            Assert.assertEquals("z", resultSetMetaData.getColumnName(2));
        }

    }


    @Test
    public void checkInvisibleMetaData() throws SQLException {

        //since 10.3.3
        Assume.assumeTrue(isMariadbServer() && minVersion(10, 3));
        cancelForVersion(10, 3, 0);
        cancelForVersion(10, 3, 1);
        cancelForVersion(10, 3, 2);

        createTable("checkInvisibleMetaData",
                "xx tinyint(1), x2 tinyint(1) unsigned INVISIBLE primary key auto_increment, yy year(4), zz bit, uu smallint");
        DatabaseMetaData meta = sharedConnection.getMetaData();
        ResultSet rs = meta.getColumns(null, null, "checkInvisibleMetaData", null);
        assertTrue(rs.next());
        assertEquals("BIT", rs.getString(6));
        assertTrue(rs.next());
        assertEquals("BIT", rs.getString(6));
        assertTrue(rs.next());
        assertEquals("YEAR", rs.getString(6));
        assertEquals(null, rs.getString(7)); // column size
        assertEquals(null, rs.getString(9)); // decimal digit

    }

    @Test
    public void columnNamesMappingError() throws SQLException {
        createTable("columnNamesMappingError", "xx tinyint(1)");
        Statement stmt = sharedConnection.createStatement();
        stmt.executeUpdate("INSERT INTO columnNamesMappingError VALUES (4)");
        ResultSet rs = stmt.executeQuery("SELECT * FROM columnNamesMappingError");
        assertTrue(rs.next());
        assertEquals(4, rs.getInt("xx"));
        try {
            rs.getInt("wrong_column_name");
            fail("must have fail, column 'wrong_column_name' does not exists");
        } catch (SQLException e) {
            assertEquals("42S22", e.getSQLState());
            assertEquals(1054, e.getErrorCode());
            assertEquals("No such column: wrong_column_name", e.getMessage());
        }

    }

}
