<commit_before>/* This file is part of VoltDB.
 * Copyright (C) 2008-2013 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.
 */

package org.voltdb.regressionsuites;

import java.io.IOException;

import org.voltdb.BackendTarget;
import org.voltdb.VoltTable;
import org.voltdb.client.Client;
import org.voltdb.client.ClientResponse;
import org.voltdb.client.NoConnectionsException;
import org.voltdb.client.ProcCallException;
import org.voltdb.client.SyncCallback;
import org.voltdb.compiler.VoltProjectBuilder;

public class TestLimitOffsetSuite extends RegressionSuite {
    public TestLimitOffsetSuite(String name) {
        super(name);
    }

    private void load(Client client)
    throws NoConnectionsException, IOException, InterruptedException {
        for (int i = 0; i < 10; i++) {
            SyncCallback cb = new SyncCallback();
            client.callProcedure(cb, "InsertA", i, i);
            cb.waitForResponse();
            assertEquals(1, cb.getResponse().getResults()[0].asScalarLong());
        }

        for (int i = 0; i < 10; i++) {
            SyncCallback cb = new SyncCallback();
            client.callProcedure(cb, "InsertB", i, i);
            cb.waitForResponse();
            assertEquals(1, cb.getResponse().getResults()[0].asScalarLong());
        }
    }

    private void doLimitOffsetAndCheck(String proc)
    throws IOException, InterruptedException, ProcCallException {
        Client client = this.getClient();
        load(client);

        ClientResponse resp = client.callProcedure(proc, 4, 0);
        assertEquals(ClientResponse.SUCCESS, resp.getStatus());
        VoltTable[] results = resp.getResults();
        assertEquals(1, results.length);
        VoltTable vt = results[0];
        int i = 0;
        while (vt.advanceRow()) {
            assertEquals(i++, vt.getLong(1));
        }
        assertEquals(4, i);

        resp = client.callProcedure(proc, 3, 1);
        assertEquals(ClientResponse.SUCCESS, resp.getStatus());
        results = resp.getResults();
        assertEquals(1, results.length);
        vt = results[0];
        i = 1;
        while (vt.advanceRow()) {
            assertEquals(i++, vt.getLong(1));
        }
        assertEquals(4, i);
    }

    public void testMultiPartInlineLimit() throws IOException, InterruptedException, ProcCallException {
        doLimitOffsetAndCheck("LimitAPKEY");
    }

    public void testMultiPartLimit() throws IOException, InterruptedException, ProcCallException {
        doLimitOffsetAndCheck("LimitAI");
    }

    public void testReplicatedInlineLimit() throws IOException, InterruptedException, ProcCallException {
        doLimitOffsetAndCheck("LimitBPKEY");
    }

    public void testReplicatedLimit() throws IOException, InterruptedException, ProcCallException {
        doLimitOffsetAndCheck("LimitBI");
    }

    public void testDistinctLimitOffset() throws NoConnectionsException, IOException, ProcCallException {
        Client client = this.getClient();
        client.callProcedure("InsertA", 0, 1);
        client.callProcedure("InsertA", 1, 1);
        client.callProcedure("InsertA", 2, 2);
        VoltTable result = null;

        result = client.callProcedure("@AdHoc", "SELECT DISTINCT I FROM A LIMIT 1 OFFSET 1;").getResults()[0];
        assertEquals(1, result.getRowCount());

        result = client.callProcedure("@AdHoc", "SELECT DISTINCT I FROM A LIMIT 0 OFFSET 1;").getResults()[0];
        assertEquals(0, result.getRowCount());
}

    public void testJoinAndLimitOffset() throws IOException, ProcCallException, InterruptedException {
        Client client = this.getClient();
        load(client);
        client.callProcedure("InsertA", -1, 0);
        VoltTable result = client.callProcedure("@AdHoc", "SELECT * FROM A, B WHERE A.PKEY < B.PKEY LIMIT 1 OFFSET 1;")
                                 .getResults()[0];
        assertEquals(1, result.getRowCount());
        result = client.callProcedure("@AdHoc", "SELECT A.PKEY FROM A, B WHERE A.PKEY = B.PKEY LIMIT 1;")
                .getResults()[0];
        assertEquals(1, result.getRowCount());
        result.advanceRow();
        result = client.callProcedure("@AdHoc", "SELECT A.PKEY FROM A, B WHERE A.PKEY = B.PKEY LIMIT 2 OFFSET 2;")
                .getResults()[0];
        assertEquals(2, result.getRowCount());
        result = client.callProcedure("@AdHoc", "SELECT A.PKEY FROM A LEFT JOIN B ON A.I = B.I LIMIT 10 OFFSET 5;")
                .getResults()[0];
        assertEquals(6, result.getRowCount());
    }

    public void testENG3487() throws IOException, ProcCallException {
        Client client = this.getClient();

        client.callProcedure("A.insert", 1, 1);
        client.callProcedure("A.insert", 2, 1);
        client.callProcedure("A.insert", 3, 1);
        client.callProcedure("A.insert", 4, 4);
        client.callProcedure("A.insert", 5, 4);
        client.callProcedure("A.insert", 6, 9);


        VoltTable result = client.callProcedure("@AdHoc", "select I, count(*) as tag from A group by I order by tag, I limit 1")
                .getResults()[0];

        assertEquals(1, result.getRowCount());
        assertTrue(result.advanceRow());
        //System.err.println("Result:\n" + result);<commit_msg>ENG-5396 add join with limit test queries<commit_after>/* This file is part of VoltDB.
 * Copyright (C) 2008-2013 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.
 */

package org.voltdb.regressionsuites;

import java.io.IOException;

import org.voltdb.BackendTarget;
import org.voltdb.VoltTable;
import org.voltdb.client.Client;
import org.voltdb.client.ClientResponse;
import org.voltdb.client.NoConnectionsException;
import org.voltdb.client.ProcCallException;
import org.voltdb.client.SyncCallback;
import org.voltdb.compiler.VoltProjectBuilder;

public class TestLimitOffsetSuite extends RegressionSuite {
    public TestLimitOffsetSuite(String name) {
        super(name);
    }

    private void load(Client client)
    throws NoConnectionsException, IOException, InterruptedException {
        for (int i = 0; i < 10; i++) {
            SyncCallback cb = new SyncCallback();
            client.callProcedure(cb, "InsertA", i, i);
            cb.waitForResponse();
            assertEquals(1, cb.getResponse().getResults()[0].asScalarLong());
        }

        for (int i = 0; i < 10; i++) {
            SyncCallback cb = new SyncCallback();
            client.callProcedure(cb, "InsertB", i, i);
            cb.waitForResponse();
            assertEquals(1, cb.getResponse().getResults()[0].asScalarLong());
        }
    }

    private void doLimitOffsetAndCheck(String proc)
    throws IOException, InterruptedException, ProcCallException {
        Client client = this.getClient();
        load(client);

        ClientResponse resp = client.callProcedure(proc, 4, 0);
        assertEquals(ClientResponse.SUCCESS, resp.getStatus());
        VoltTable[] results = resp.getResults();
        assertEquals(1, results.length);
        VoltTable vt = results[0];
        int i = 0;
        while (vt.advanceRow()) {
            assertEquals(i++, vt.getLong(1));
        }
        assertEquals(4, i);

        resp = client.callProcedure(proc, 3, 1);
        assertEquals(ClientResponse.SUCCESS, resp.getStatus());
        results = resp.getResults();
        assertEquals(1, results.length);
        vt = results[0];
        i = 1;
        while (vt.advanceRow()) {
            assertEquals(i++, vt.getLong(1));
        }
        assertEquals(4, i);
    }

    public void testMultiPartInlineLimit() throws IOException, InterruptedException, ProcCallException {
        doLimitOffsetAndCheck("LimitAPKEY");
    }

    public void testMultiPartLimit() throws IOException, InterruptedException, ProcCallException {
        doLimitOffsetAndCheck("LimitAI");
    }

    public void testReplicatedInlineLimit() throws IOException, InterruptedException, ProcCallException {
        doLimitOffsetAndCheck("LimitBPKEY");
    }

    public void testReplicatedLimit() throws IOException, InterruptedException, ProcCallException {
        doLimitOffsetAndCheck("LimitBI");
    }

    public void testDistinctLimitOffset() throws NoConnectionsException, IOException, ProcCallException {
        Client client = this.getClient();
        client.callProcedure("InsertA", 0, 1);
        client.callProcedure("InsertA", 1, 1);
        client.callProcedure("InsertA", 2, 2);
        VoltTable result = null;

        result = client.callProcedure("@AdHoc", "SELECT DISTINCT I FROM A LIMIT 1 OFFSET 1;").getResults()[0];
        assertEquals(1, result.getRowCount());

        result = client.callProcedure("@AdHoc", "SELECT DISTINCT I FROM A LIMIT 0 OFFSET 1;").getResults()[0];
        assertEquals(0, result.getRowCount());
}

    public void testJoinAndLimitOffset() throws IOException, ProcCallException, InterruptedException {
        Client client = this.getClient();
        load(client);
        int limits[] = new int[] { 1, 2, 5, 10, 12, 25 };
        int offsets[] = new int[] { 0, 1, 2, 5, 10, 12, 25 };
        String selecteds[] = new String[] { "*", "A.PKEY" };
        String joinops[] = new String[] { ",", "LEFT JOIN", "RIGHT JOIN" };
        String conditions[] = new String[] { " A.PKEY < B.PKEY ", " A.PKEY = B.PKEY ", " A.I = B.I " };
        client.callProcedure("InsertA", -1, 0);
        for (String joinop : joinops) {
            String onwhere = "ON";
            if (joinop.equals(",")) {
                onwhere = "WHERE";
            }
            for (String selected : selecteds) {
                for (int limit : limits) {
                    for (int offset : offsets) {
                        for (String condition : conditions) {
                            String query;
                            VoltTable result;
                            query = "SELECT COUNT(*) FROM A " + joinop + " B " +
                                    onwhere + condition +
                                    ";";
                            result = client.callProcedure("@AdHoc", query).getResults()[0];
                            long found = result.asScalarLong();
                            query = "SELECT " + selected +
                                    " FROM A " + joinop + " B " +
                                    onwhere + condition +
                                    " ORDER BY A.PKEY, B.PKEY " +
                                    "LIMIT " + limit +
                                    ((offset == 0) ? "" : " OFFSET " + offset) +
                                    ";";
                            result = client.callProcedure("@AdHoc", query).getResults()[0];
                            assertEquals(Math.max(0, Math.min(limit, found-offset)), result.getRowCount());
                        }
                    }
                }
            }
        }
    }

    public void testENG3487() throws IOException, ProcCallException {
        Client client = this.getClient();

        client.callProcedure("A.insert", 1, 1);
        client.callProcedure("A.insert", 2, 1);
        client.callProcedure("A.insert", 3, 1);
        client.callProcedure("A.insert", 4, 4);
        client.callProcedure("A.insert", 5, 4);
        client.callProcedure("A.insert", 6, 9);


        VoltTable result = client.callProcedure("@AdHoc", "select I, count(*) as tag from A group by I order by tag, I limit 1")
                .getResults()[0];

        assertEquals(1, result.getRowCount());
        assertTrue(result.advanceRow());
        //System.err.println("Result:\n" + result);
{'fraction_non_alphanumeric': 0.11126636270039712, 'fraction_numerical': 0.013016620091189882, 'mean_word_length': 2.5580847723704867, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1219648', 'n_tokens_mistral': 3859, 'n_tokens_neox': 3628, 'n_words': 1082}