subselect.sql-spec

// To mute tests follow example in file: example.sql-spec

basic
SELECT first_name, last_name FROM (SELECT * FROM test_emp);
basicProjection
SELECT first_name, last_name FROM (SELECT first_name, last_name FROM test_emp);
basicAliasedProjection
SELECT last_name, f FROM (SELECT first_name AS f, last_name FROM test_emp);
basicCondition
SELECT first_name, last_name FROM (SELECT * FROM test_emp WHERE gender IS NOT NULL) ORDER BY first_name NULLS first;
basicOrderByAlias
SELECT last_name, f FROM (SELECT first_name AS f, last_name FROM test_emp) ORDER BY f DESC NULLS last;
basicOrderByFieldName
SELECT last_name, f FROM (SELECT first_name AS f, last_name FROM test_emp) ORDER BY last_name ASC;
basicGroupBy
SELECT gender FROM (SELECT first_name AS f, last_name, gender FROM test_emp) GROUP BY gender ORDER BY gender ASC;
basicGroupByAlias
SELECT g FROM (SELECT first_name AS f, last_name, gender AS g FROM test_emp) GROUP BY g ORDER BY g ASC;
basicGroupByWithFilterByAlias
SELECT g FROM (SELECT first_name AS f, last_name, gender AS g FROM test_emp) WHERE g IS NOT NULL GROUP BY g ORDER BY g ASC;
basicGroupByRealiased
SELECT g AS h FROM (SELECT first_name AS f, last_name, gender AS g FROM test_emp) GROUP BY g ORDER BY g DESC NULLS last;
basicGroupByRealiasedTwice
SELECT g AS h FROM (SELECT first_name AS f, last_name, gender AS g FROM test_emp) GROUP BY h ORDER BY h DESC NULLS last;
basicOrderByRealiasedField
SELECT g AS h FROM (SELECT first_name AS f, last_name, gender AS g FROM test_emp) ORDER BY g DESC NULLS first;

groupAndOrderByRealiasedExpression
SELECT emp_group AS e, min_high_salary AS s
FROM (
    SELECT emp_no % 2 AS emp_group, MIN(salary) AS min_high_salary 
    FROM test_emp
    WHERE salary > 50000 
    GROUP BY emp_group
)
ORDER BY e DESC;

multiLevelSelectStar
SELECT * FROM (SELECT * FROM ( SELECT * FROM test_emp ));

multiLevelSelectStarWithAlias
SELECT * FROM (SELECT * FROM ( SELECT * FROM test_emp ) b) c;

// AwaitsFix: https://github.com/elastic/elasticsearch/issues/69758
filterAfterGroupBy-Ignore
SELECT s2 AS s3 FROM (SELECT s AS s2 FROM ( SELECT salary AS s FROM test_emp) GROUP BY s2) WHERE s2 < 5 ORDER BY s3 DESC NULLS last;

countAndComplexCondition
SELECT COUNT(*) as c FROM (SELECT * FROM test_emp WHERE gender IS NOT NULL) WHERE ABS(salary) > 0 GROUP BY gender ORDER BY gender;
countAndGroupBy
SELECT COUNT(*), gender FROM (SELECT first_name AS f, last_name, gender FROM test_emp) GROUP BY gender ORDER BY gender ASC;
countAndGroupByByAlias
SELECT COUNT(*), g FROM (SELECT first_name AS f, last_name, gender AS g FROM test_emp) GROUP BY g ORDER BY g ASC;
countAndMax
SELECT COUNT(*), gender, MAX("salary") FROM (SELECT first_name AS f, last_name, gender, salary FROM test_emp) GROUP BY gender ORDER BY gender ASC;
countAndMaxByAlias
SELECT COUNT(*), g, MAX("s") FROM (SELECT first_name AS f, last_name, gender AS g, salary AS s FROM test_emp) GROUP BY g ORDER BY g ASC;
// @AwaitsFix(bugUrl = "follow-up to https://github.com/elastic/elasticsearch/pull/67216")
countAndMaxByAliasAndFilter-Ignore
SELECT COUNT(*), g, MAX("s") FROM (SELECT first_name AS f, last_name, gender AS g, salary AS s FROM test_emp) WHERE g IS NOT NULL GROUP BY g ORDER BY g ASC;
countAndMultipleAggregates
SELECT COUNT(*), gender, MAX("salary"), MAX(salary) m, MIN("salary") FROM (SELECT first_name AS f, last_name, gender, salary FROM test_emp) GROUP BY gender ORDER BY gender ASC;

castAndGroupBy
SELECT TRUE as employed, gender g, CAST(SUM(emp_no) AS BIGINT) s FROM (SELECT * FROM test_emp) GROUP BY g ORDER BY g DESC;
castAndGroupByAndLiteralsInSubQuery
SELECT TRUE as employed, gender g, CAST(SUM(emp_no) AS BIGINT) s FROM (SELECT TRUE, FALSE, gender, emp_no FROM test_emp) GROUP BY g ORDER BY g ASC;
literalsAndConditionInSubQuery
SELECT 10, 'foo', COUNT(*), '20', 'bar' FROM (SELECT * FROM test_emp WHERE gender = 'M') GROUP BY gender ORDER BY 3;
sameConditionInQueryAndSubQuery
SELECT emp_no e, 5 FROM (SELECT * FROM test_emp WHERE emp_no < 10020) WHERE emp_no < 10020 GROUP BY e ORDER BY emp_no DESC;
havingCount
SELECT languages, COUNT(*) c FROM (SELECT languages FROM test_emp WHERE languages > 2 AND gender IS NOT NULL) GROUP BY languages HAVING COUNT(*) IS NOT NULL ORDER BY languages DESC;

conditionOnCountAndMultipleConditions
SELECT MIN("salary") min, MAX("salary") max, gender g, languages l, COUNT(*) c FROM (SELECT last_name, salary, languages, gender, first_name FROM test_emp WHERE languages > 3) WHERE languages < 5 GROUP BY g, languages HAVING c > 4 ORDER BY gender, languages;
countDistinct
SELECT gender, COUNT(DISTINCT languages) AS c FROM (SELECT * FROM test_emp) GROUP BY gender HAVING count(DISTINCT languages) > 0 ORDER BY gender;

noMatchCondition
SELECT last_name l FROM (SELECT last_name FROM test_emp) WHERE 1 = 2 ORDER BY 1 LIMIT 10;
noMatchConditionInSubQuery
SELECT last_name l FROM (SELECT last_name FROM test_emp WHERE 1 = 2) WHERE 1 = 1 ORDER BY 1 LIMIT 10;
matchInSubQueryAndNoMatchInRootQuery
SELECT last_name l FROM (SELECT last_name FROM test_emp WHERE TRUE) WHERE 1 = 1 ORDER BY 1 LIMIT 10;

greatestOrderBy
SELECT GREATEST(10096, ABS(emp_no + 1)) AS gt FROM (SELECT * FROM test_emp) ORDER BY gt LIMIT 10;

inAndIsNotNull
SELECT NOT((languages = 2) IS NULL) AS col1, NOT((languages = 2) IS NOT NULL) AS col2 FROM (SELECT * FROM test_emp WHERE emp_no IN (10019, 10020, 10021)) WHERE emp_no IN (10018, 10019, 10020) ORDER BY emp_no;