<commit_before>/*
 * Licensed to CRATE Technology GmbH ("Crate") under one or more contributor
 * license agreements.  See the NOTICE file distributed with this work for
 * additional information regarding copyright ownership.  Crate 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.
 *
 * However, if you have executed another commercial license agreement
 * with Crate these terms will supersede the license and you may use the
 * software solely pursuant to the terms of the relevant commercial agreement.
 */

package io.crate.integrationtests;

import io.crate.Build;
import io.crate.Version;
import io.crate.action.sql.SQLActionException;
import io.crate.testing.SQLResponse;
import io.crate.metadata.settings.CrateSettings;
import io.crate.testing.SQLTransportExecutor;
import io.crate.testing.TestingHelpers;
import io.crate.testing.UseJdbc;
import org.apache.lucene.util.Constants;
import org.elasticsearch.test.ESIntegTestCase;
import org.hamcrest.Matchers;
import org.junit.After;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.TemporaryFolder;

import java.nio.charset.StandardCharsets;
import java.nio.file.DirectoryStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.concurrent.TimeUnit;

import static com.carrotsearch.randomizedtesting.RandomizedTest.$;
import static org.hamcrest.Matchers.*;
import static org.hamcrest.core.Is.is;

@ESIntegTestCase.ClusterScope(numClientNodes = 0, numDataNodes = 2)
@UseJdbc
public class TransportSQLActionClassLifecycleTest extends SQLTransportIntegrationTest {

    @Rule
    public TemporaryFolder folder = new TemporaryFolder();

    @Before
    public void initTestData() throws Exception {
        Setup setup = new Setup(sqlExecutor);
        setup.partitionTableSetup();
        setup.groupBySetup();
    }

    @After
    public void resetSettings() throws Exception {
        // reset stats settings in case of some tests changed it and failed without resetting.
        execute("reset global stats.enabled, stats.jobs_log_size, stats.operations_log_size");
    }

    @Test
    public void testSelectNonExistentGlobalExpression() throws Exception {
        expectedException.expect(SQLActionException.class);
        expectedException.expectMessage("Cannot resolve relation 'suess.cluster'");
        execute("select count(race), suess.cluster.name from characters");
    }

    @Test
    public void testSelectOrderByNullSortingASC() throws Exception {
        SQLResponse response = execute("select age from characters order by age");
        assertEquals(32, response.rows()[0][0]);
        assertEquals(34, response.rows()[1][0]);
        assertEquals(43, response.rows()[2][0]);
        assertEquals(112, response.rows()[3][0]);
        assertEquals(null, response.rows()[4][0]);
        assertEquals(null, response.rows()[5][0]);
        assertEquals(null, response.rows()[6][0]);
    }

    @Test
    public void testSelectDoc() throws Exception {
        SQLResponse response = execute("select _doc from characters order by name desc limit 1");
        assertArrayEquals(new String[]{"_doc"}, response.cols());
        Map<String, Object> _doc = new TreeMap<>((Map) response.rows()[0][0]);
        assertEquals(
            "{age=32, birthdate=276912000000, details={job=Mathematician}, " +
            "gender=female, name=Trillian, race=Human}",
            _doc.toString());
    }

    @Test
    public void testSelectRaw() throws Exception {
        SQLResponse response = execute("select _raw from characters order by name desc limit 1");
        assertEquals(
            "{\"race\":\"Human\",\"gender\":\"female\",\"age\":32,\"birthdate\":276912000000," +
            "\"name\":\"Trillian\",\"details\":{\"job\":\"Mathematician\"}}\n",
            TestingHelpers.printedTable(response.rows()));
    }

    @Test
    public void testSelectRawWithGrouping() throws Exception {
        SQLResponse response = execute("select name, _raw from characters " +
                                       "group by _raw, name order by name desc limit 1");
        assertEquals(
            "Trillian| {\"race\":\"Human\",\"gender\":\"female\",\"age\":32,\"birthdate\":276912000000," +
            "\"name\":\"Trillian\",\"details\":{\"job\":\"Mathematician\"}}\n",
            TestingHelpers.printedTable(response.rows()));
    }

    @Test
    public void testSelectOrderByNullSortingDESC() throws Exception {
        SQLResponse response = execute("select age from characters order by age desc");
        assertEquals(null, response.rows()[0][0]);
        assertEquals(null, response.rows()[1][0]);
        assertEquals(null, response.rows()[2][0]);
        assertEquals(112, response.rows()[3][0]);
        assertEquals(43, response.rows()[4][0]);
        assertEquals(34, response.rows()[5][0]);
        assertEquals(32, response.rows()[6][0]);
    }

    @Test
    public void testSelectOrderByNullSortingASCWithFunction() throws Exception {
        execute("select abs(age) from characters order by 1 asc");
        assertThat(TestingHelpers.printedTable(response.rows()),
            is("32\n" +
               "34\n" +
               "43\n" +
               "112\n" +
               "NULL\n" +
               "NULL\n" +
               "NULL\n"));
    }

    @Test
    public void testSelectOrderByNullSortingDESCWithFunction() throws Exception {
        execute("select abs(age) from characters order by 1 desc");
        assertThat(TestingHelpers.printedTable(response.rows()),
            is("NULL\n" +
               "NULL\n" +
               "NULL\n" +
               "112\n" +
               "43\n" +
               "34\n" +
               "32\n"));
    }

    @Test
    public void testSelectGroupByOrderByNullSortingASC() throws Exception {
        SQLResponse response = execute("select age from characters group by age order by age");
        assertEquals(32, response.rows()[0][0]);
        assertEquals(34, response.rows()[1][0]);
        assertEquals(43, response.rows()[2][0]);
        assertEquals(112, response.rows()[3][0]);
        assertEquals(null, response.rows()[4][0]);
    }

    @Test
    public void testSelectGroupByOrderByNullSortingDESC() throws Exception {
        SQLResponse response = execute("select age from characters group by age order by age desc");
        assertEquals(null, response.rows()[0][0]);
        assertEquals(112, response.rows()[1][0]);
        assertEquals(43, response.rows()[2][0]);
        assertEquals(34, response.rows()[3][0]);
        assertEquals(32, response.rows()[4][0]);
    }

    @Test
    public void testGlobalAggregateSimple() throws Exception {
        SQLResponse response = execute("select max(age) from characters");

        assertEquals(1, response.rowCount());
        assertEquals("max(age)", response.cols()[0]);
        assertEquals(112, response.rows()[0][0]);

        response = execute("select min(name) from characters");

        assertEquals(1, response.rowCount());
        assertEquals("min(name)", response.cols()[0]);
        assertEquals("Anjie", response.rows()[0][0]);

        response = execute("select avg(age) as median_age from characters");
        assertEquals(1, response.rowCount());
        assertEquals("median_age", response.cols()[0]);
        assertEquals(55.25d, response.rows()[0][0]);

        response = execute("select sum(age) as sum_age from characters");<commit_msg>Cleanup tests for order by with nulls<commit_after>/*
 * Licensed to CRATE Technology GmbH ("Crate") under one or more contributor
 * license agreements.  See the NOTICE file distributed with this work for
 * additional information regarding copyright ownership.  Crate 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.
 *
 * However, if you have executed another commercial license agreement
 * with Crate these terms will supersede the license and you may use the
 * software solely pursuant to the terms of the relevant commercial agreement.
 */

package io.crate.integrationtests;

import io.crate.Build;
import io.crate.Version;
import io.crate.action.sql.SQLActionException;
import io.crate.metadata.settings.CrateSettings;
import io.crate.testing.SQLResponse;
import io.crate.testing.SQLTransportExecutor;
import io.crate.testing.TestingHelpers;
import io.crate.testing.UseJdbc;
import org.apache.lucene.util.Constants;
import org.elasticsearch.test.ESIntegTestCase;
import org.hamcrest.Matchers;
import org.junit.After;
import org.junit.Before;
import org.junit.Rule;
import org.junit.Test;
import org.junit.rules.TemporaryFolder;

import java.nio.charset.StandardCharsets;
import java.nio.file.DirectoryStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.concurrent.TimeUnit;

import static com.carrotsearch.randomizedtesting.RandomizedTest.$;
import static org.hamcrest.Matchers.*;
import static org.hamcrest.core.Is.is;

@ESIntegTestCase.ClusterScope(numClientNodes = 0, numDataNodes = 2)
@UseJdbc
public class TransportSQLActionClassLifecycleTest extends SQLTransportIntegrationTest {

    @Rule
    public TemporaryFolder folder = new TemporaryFolder();

    @Before
    public void initTestData() throws Exception {
        Setup setup = new Setup(sqlExecutor);
        setup.partitionTableSetup();
        setup.groupBySetup();
    }

    @After
    public void resetSettings() throws Exception {
        // reset stats settings in case of some tests changed it and failed without resetting.
        execute("reset global stats.enabled, stats.jobs_log_size, stats.operations_log_size");
    }

    @Test
    public void testSelectNonExistentGlobalExpression() throws Exception {
        expectedException.expect(SQLActionException.class);
        expectedException.expectMessage("Cannot resolve relation 'suess.cluster'");
        execute("select count(race), suess.cluster.name from characters");
    }

    @Test
    public void testSelectDoc() throws Exception {
        SQLResponse response = execute("select _doc from characters order by name desc limit 1");
        assertArrayEquals(new String[]{"_doc"}, response.cols());
        Map<String, Object> _doc = new TreeMap<>((Map) response.rows()[0][0]);
        assertEquals(
            "{age=32, birthdate=276912000000, details={job=Mathematician}, " +
            "gender=female, name=Trillian, race=Human}",
            _doc.toString());
    }

    @Test
    public void testSelectRaw() throws Exception {
        SQLResponse response = execute("select _raw from characters order by name desc limit 1");
        assertEquals(
            "{\"race\":\"Human\",\"gender\":\"female\",\"age\":32,\"birthdate\":276912000000," +
            "\"name\":\"Trillian\",\"details\":{\"job\":\"Mathematician\"}}\n",
            TestingHelpers.printedTable(response.rows()));
    }

    @Test
    public void testSelectRawWithGrouping() throws Exception {
        SQLResponse response = execute("select name, _raw from characters " +
                                       "group by _raw, name order by name desc limit 1");
        assertEquals(
            "Trillian| {\"race\":\"Human\",\"gender\":\"female\",\"age\":32,\"birthdate\":276912000000," +
            "\"name\":\"Trillian\",\"details\":{\"job\":\"Mathematician\"}}\n",
            TestingHelpers.printedTable(response.rows()));
    }

    @Test
    public void testSelectOrderByNullSortingASC() throws Exception {
        execute("select age from characters order by age");
        assertThat(TestingHelpers.printedTable(response.rows()),
            is("32\n" +
               "34\n" +
               "43\n" +
               "112\n" +
               "NULL\n" +
               "NULL\n" +
               "NULL\n"));
    }

    @Test
    public void testSelectOrderByNullSortingDESC() throws Exception {
        execute("select age from characters order by age desc");
        assertThat(TestingHelpers.printedTable(response.rows()),
            is("NULL\n" +
               "NULL\n" +
               "NULL\n" +
               "112\n" +
               "43\n" +
               "34\n" +
               "32\n"));
    }

    @Test
    public void testSelectOrderByNullSortingASCWithFunction() throws Exception {
        execute("select abs(age) from characters order by 1 asc");
        assertThat(TestingHelpers.printedTable(response.rows()),
            is("32\n" +
               "34\n" +
               "43\n" +
               "112\n" +
               "NULL\n" +
               "NULL\n" +
               "NULL\n"));
    }

    @Test
    public void testSelectOrderByNullSortingDESCWithFunction() throws Exception {
        execute("select abs(age) from characters order by 1 desc");
        assertThat(TestingHelpers.printedTable(response.rows()),
            is("NULL\n" +
               "NULL\n" +
               "NULL\n" +
               "112\n" +
               "43\n" +
               "34\n" +
               "32\n"));
    }


    @Test
    public void testSelectGroupByOrderByNullSortingASC() throws Exception {
        execute("select age from characters group by age order by age");
        assertThat(TestingHelpers.printedTable(response.rows()),
            is("32\n" +
               "34\n" +
               "43\n" +
               "112\n" +
               "NULL\n"));
    }

    @Test
    public void testSelectGroupByOrderByNullSortingDESC() throws Exception {
        execute("select age from characters group by age order by age desc");
        assertThat(TestingHelpers.printedTable(response.rows()),
            is("NULL\n" +
               "112\n" +
               "43\n" +
               "34\n" +
               "32\n"));
    }

    @Test
    public void testGlobalAggregateSimple() throws Exception {
        SQLResponse response = execute("select max(age) from characters");

        assertEquals(1, response.rowCount());
        assertEquals("max(age)", response.cols()[0]);
        assertEquals(112, response.rows()[0][0]);

        response = execute("select min(name) from characters");

        assertEquals(1, response.rowCount());
        assertEquals("min(name)", response.cols()[0]);
        assertEquals("Anjie", response.rows()[0][0]);

        response = execute("select avg(age) as median_age from characters");
        assertEquals(1, response.rowCount());
        assertEquals("median_age", response.cols()[0]);
        assertEquals(55.25d, response.rows()[0][0]);

        response = execute("select sum(age) as sum_age from characters");
{'fraction_non_alphanumeric': 0.1239286394514634, 'fraction_numerical': 0.019300361881785282, 'mean_word_length': 2.912568306010929, 'pattern_counts': {'":': 28, '<': 7, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2897117', 'n_tokens_mistral': 4368, 'n_tokens_neox': 4164, 'n_words': 1117}