* Copyright (C) 2008-2014 VoltDB Inc.

*

* Permission is hereby granted, free of charge, to any person obtaining

* a copy of this software and associated documentation files (the

* "Software"), to deal in the Software without restriction, including

* without limitation the rights to use, copy, modify, merge, publish,

* distribute, sublicense, and/or sell copies of the Software, and to

* permit persons to whom the Software is furnished to do so, subject to

* the following conditions:

*

* The above copyright notice and this permission notice shall be

* included in all copies or substantial portions of the Software.

*

* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,

* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF

* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.

* IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR

* OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,

* ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR

* OTHER DEALINGS IN THE SOFTWARE.

*/

* Tests for SQL that was recently (early 2012) unsupported.

*/

    /** Procedures used by this suite */

    static final Class<?>[] PROCEDURES = { Insert.class };

    // Padding used to purposely exercise non-inline strings.

    private static final String paddedToNonInlineLength =

        "will you still free me (will memcheck see me) when Im sixty-four";

    // Test some false alarm cases in HSQLBackend that were interfering with sqlcoverage.

    public void testFoundHSQLBackendOutOfRange() throws IOException, InterruptedException, ProcCallException {

        System.out.println("STARTING testFoundHSQLBackendOutOfRange");

        Client client = getClient();

        ClientResponse cr = null;

        /*

        CREATE TABLE P1 (

                ID INTEGER DEFAULT '0' NOT NULL,

                DESC VARCHAR(300),

                NUM INTEGER,

                RATIO FLOAT,

                PAST TIMESTAMP DEFAULT NULL,

                PRIMARY KEY (ID) );

        */

        client.callProcedure("@AdHoc", "INSERT INTO P1 VALUES (0, 'wEoiXIuJwSIKBujWv', -405636, 1.38145922788945552107e-01, NULL)");

        client.callProcedure("@AdHoc", "INSERT INTO P1 VALUES (2, 'wEoiXIuJwSIKBujWv', -29914, 8.98500019539639316335e-01, NULL)");

        client.callProcedure("@AdHoc", "INSERT INTO P1 VALUES (4, 'WCfDDvZBPoqhanfGN', -1309657, 9.34160160574919795629e-01, NULL)");

        client.callProcedure("@AdHoc", "INSERT INTO P1 VALUES (6, 'WCfDDvZBPoqhanfGN', 1414568, 1.14383710279231887164e-01, NULL)");

        cr = client.callProcedure("@AdHoc", "select (5.25 + NUM) from P1");

        assertEquals(ClientResponse.SUCCESS, cr.getStatus());

        cr = client.callProcedure("@AdHoc", "SELECT FLOOR(NUM + 5.25) NUMSUM FROM P1 ORDER BY NUMSUM");

        assertEquals(ClientResponse.SUCCESS, cr.getStatus());

        // This test case requires HSQL to be taught to do (truncating) integer division of integers as VoltDB does.

        // While not strictly required by the SQL standard, integer division is at least technically compliant,

        // where HSQL's use of floating point division is not.

        // cr = client.callProcedure("@AdHoc", "SELECT SUM(DISTINCT SQRT(ID / (NUM))) AS Q22 FROM P1");

        // assertEquals(ClientResponse.SUCCESS, cr.getStatus());

    }

    public void testStringExpressionIndex() throws Exception {

        System.out.println("STARTING testStringExpressionIndex");

        Client client = getClient();

        initialLoad(client, "P1");

        ClientResponse cr = null;

        VoltTable result = null;

        /*

        CREATE TABLE P1 (

                ID INTEGER DEFAULT '0' NOT NULL,

                DESC VARCHAR(300),

                NUM INTEGER,

                RATIO FLOAT,

                PAST TIMESTAMP DEFAULT NULL,

                PRIMARY KEY (ID) );

                // Test generalized indexes on a string function and combos.

        CREATE INDEX P1_SUBSTRING_DESC ON P1 ( SUBSTRING(DESC FROM 1 FOR 2) );

        CREATE INDEX P1_SUBSTRING_WITH_COL_DESC ON P1 ( SUBSTRING(DESC FROM 1 FOR 2), DESC );

        CREATE INDEX P1_NUM_EXPR_WITH_STRING_COL ON P1 ( DESC, ABS(ID) );

        CREATE INDEX P1_MIXED_TYPE_EXPRS1 ON P1 ( ABS(ID+2), SUBSTRING(DESC FROM 1 FOR 2) );

        CREATE INDEX P1_MIXED_TYPE_EXPRS2 ON P1 ( SUBSTRING(DESC FROM 1 FOR 2), ABS(ID+2) );

        */

        // Do some rudimentary indexed queries -- the real challenge to string expression indexes is defining and loading/maintaining them.

        // TODO: For that reason, it might make sense to break them out into their own suite to make their specific issues easier to isolate.

        cr = client.callProcedure("@AdHoc", "select ID from P1 where SUBSTRING(DESC FROM 1 for 2) = 'X1' and ABS(ID+2) > 7 order by NUM, ID");

        assertEquals(ClientResponse.SUCCESS, cr.getStatus());

        result = cr.getResults()[0];

        assertEquals(5, result.getRowCount());

        VoltTable r;

        long resultA;

        long resultB;

        // Filters intended to be close enough to bring two different indexes to the same result as no index at all.

        cr = client.callProcedure("@AdHoc", "select count(*) from P1 where ABS(ID+3) = 7 order by NUM, ID");

        assertEquals(ClientResponse.SUCCESS, cr.getStatus());

        r = cr.getResults()[0];

        resultA = r.asScalarLong();

        cr = client.callProcedure("@AdHoc", "select count(*) from P1 where SUBSTRING(DESC FROM 1 for 2) >= 'X1' and ABS(ID+2) = 8");

        assertEquals(ClientResponse.SUCCESS, cr.getStatus());

        r = cr.getResults()[0];

        resultB = r.asScalarLong();

        assertEquals(resultA, resultB);

        cr = client.callProcedure("@AdHoc", "select count(*) from P1 where SUBSTRING(DESC FROM 1 for 2) = 'X1' and ABS(ID+2) > 7 and ABS(ID+2) < 9");

        assertEquals(ClientResponse.SUCCESS, cr.getStatus());

        r = cr.getResults()[0];

        resultB = r.asScalarLong();

        assertEquals(resultA, resultB);

        // Do some updates intended to be non-corrupting and inconsequential to the test query results.

        cr = client.callProcedure("@AdHoc", "delete from P1 where ABS(ID+3) <> 7");