# Run with hypergraph optimizer
--source include/have_hypergraph.inc

CREATE TABLE t1 (
  a int NOT NULL,
  b int NOT NULL,
  c double NOT NULL
);
INSERT INTO t1 VALUES (1,1,5),(1,1,2),(1,2,5),(2,1,4),(2,1,1),(2,2,2),(2,2,3),
(2,3,1),(2,3,1),(3,3,3),(3,3,5),(3,4,5),(4,4,5),(4,4,3),(5,3,1);
ANALYZE TABLE t1;

--sorted_result
SELECT a, b, c, ROW_NUMBER() OVER (PARTITION BY a ORDER BY a,b,c) AS rnk
FROM t1 QUALIFY rnk>1;

--sorted_result
SELECT a, b, c
FROM t1 QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY a,b,c)>1;

--sorted_result
--error ER_SUBQUERY_NO_1_ROW
SELECT a, b, c
FROM t1
QUALIFY ROW_NUMBER() OVER () >
        (SELECT MIN(c) FROM t1 GROUP BY a  HAVING MAX(c) > 3);

--sorted_result
SELECT a, b, c
FROM t1
QUALIFY (MIN(a) OVER () + RANK() OVER () ) > 5;

--sorted_result
SELECT a, b, c,
MIN(b) OVER (PARTITION BY b ORDER BY a DESC,b ASC,c DESC) AS mn
FROM t1
QUALIFY ( RANK() OVER () + mn) >5;

--sorted_result
SELECT a, b, c,
MIN(b) OVER (PARTITION BY b ORDER BY a DESC,b ASC,c DESC) AS mn,
RANK() OVER (PARTITION BY b ORDER BY a DESC,b ASC,c DESC) AS rnk
FROM t1
QUALIFY ( rnk + mn) >5;

--sorted_result
SELECT a, b,
       MIN(b) OVER (PARTITION BY b ORDER BY a DESC,b ASC,c DESC) AS mn,
       RANK() OVER (PARTITION BY b ORDER BY a DESC,b ASC,c DESC) AS rnk
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY b)>1;

--sorted_result
SELECT
a, b, ( SELECT sum(rnk)
        FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY a ORDER BY a,b,c) AS rnk
               FROM t1 qualify rnk > t0.a
              ) as z
      )
FROM (values row(1,10), row(2,20)) as t0(a,b);

--echo ##
--echo ## Syntax checks
--echo ##

--sorted_result
SELECT  b
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY a)>1;

--sorted_result
SELECT  b
FROM t1
QUALIFY ROW_NUMBER() OVER ()>1 and 2*b>2;

--sorted_result
SELECT  b
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY a)>1 and c>2;

--sorted_result
SELECT  b
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY a)>1 and a>2;

--sorted_result
SELECT a, b, c, ROW_NUMBER() OVER () AS rnk
FROM t1
QUALIFY 2*rnk>1;

--sorted_result
SELECT a, b, c, ROW_NUMBER() OVER () AS rnk
FROM t1
QUALIFY a+rnk>5;


## Aggregate over window functions is not supported
--error ER_WINDOW_INVALID_WINDOW_FUNC_USE
SELECT a, b, c
FROM t1
QUALIFY AVG(ROW_NUMBER() OVER (PARTITION BY a ORDER BY a,b,c))>1;

--error ER_QUALIFY_WITHOUT_WINDOW_FUNCTION
SELECT a FROM t1 QUALIFY b> 10;

--sorted_result
SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY a,b) AS rnk
FROM t1
QUALIFY c>1;

--sorted_result
SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY a,b) AS rnk
FROM t1
QUALIFY 2*a>1;

--sorted_result
SELECT a
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a) + b > 0;

--sorted_result
SELECT a, b, ROW_NUMBER() OVER (PARTITION BY a ORDER BY a,b,c) AS rnk
FROM t1
QUALIFY rnk>1 and c>2;

--error ER_WRONG_FIELD_WITH_GROUP
SELECT MAX(a)
FROM t1 GROUP BY b,c WITH ROLLUP
QUALIFY (MIN(a) OVER () + RANK() OVER () ) > 5;

--error ER_WRONG_FIELD_WITH_GROUP
SELECT MAX(a)
FROM t1 GROUP BY b,c WITH ROLLUP
QUALIFY (MIN(a) OVER () + RANK() OVER () ) >= 2;

--sorted_result
SELECT MAX(a) FROM t1
GROUP BY b, 2*c WITH ROLLUP
HAVING AVG(c)>1
QUALIFY MIN(2*b) OVER () + RANK() OVER () > 3;

--sorted_result
SELECT 1 FROM t1
GROUP BY b, 2*c WITH ROLLUP
HAVING AVG(c)>1
QUALIFY MIN(2*b) OVER () + RANK() OVER () + MAX(a) > 4;

--sorted_result
SELECT MAX(b) FROM t1
GROUP BY b, 2*c WITH ROLLUP
HAVING AVG(c)>1
QUALIFY MIN(2*b) OVER () + RANK() OVER () + MAX(a) > 4;

--error ER_WRONG_FIELD_WITH_GROUP
SELECT MAX(a) FROM t1
GROUP BY b,c WITH ROLLUP
QUALIFY (MIN(a) OVER ()) > 1;

--error ER_WRONG_FIELD_WITH_GROUP
SELECT MAX(a)
FROM t1
GROUP BY b,c WITH ROLLUP
QUALIFY (MIN(a) OVER () + RANK() OVER () ) > 5;

SET @orig_sql_mode = @@sql_mode;
SET @@sql_mode     = '';

--sorted_result
SELECT MAX(a)
FROM t1 GROUP BY b WITH ROLLUP
QUALIFY (MIN(c) OVER () + RANK() OVER () ) >= 2;

--sorted_result
SELECT MAX(a)
FROM t1 GROUP BY b,c WITH ROLLUP
QUALIFY (MIN(a) OVER () + RANK() OVER () ) >= 2;

--sorted_result
SELECT MAX(a)
FROM t1 GROUP BY b,c WITH ROLLUP
QUALIFY (MIN(a) OVER () + RANK() OVER () ) > 5;

--sorted_result
SELECT MAX(a) FROM t1
GROUP BY b,c WITH ROLLUP
QUALIFY (MIN(a) OVER ()) > 1;

--sorted_result
SELECT MAX(a)
FROM t1
GROUP BY b,c WITH ROLLUP
QUALIFY (MIN(a) OVER () + RANK() OVER () ) > 5;

--sorted_result
SELECT a FROM t1 GROUP BY b, 2*c WITH ROLLUP
HAVING AVG(c)>1
QUALIFY ROW_NUMBER() OVER () + MAX(a) > 4;

--sorted_result
SELECT a FROM t1
GROUP BY b, 2*c WITH ROLLUP
QUALIFY LEAD(c,2) OVER () > 4;

--sorted_result
SELECT a FROM t1
GROUP BY b, 2*c WITH ROLLUP
QUALIFY LAG(c,6) OVER () > 4;

--sorted_result
SELECT b,c, MAX(a)
FROM t1 GROUP BY b,c WITH ROLLUP
QUALIFY LEAD(b) OVER(ORDER BY a,b,c) >1 AND b IS NULL;

SET @@sql_mode = @orig_sql_mode;
DROP TABLE t1;

--echo #
--echo # Bug 35800175 - #wl15864-Query does not gets offloaded when QUALIFY does not use alias name
--echo #

CREATE TABLE sales (salesperson_id INT, sale_amount DECIMAL(10, 2));
INSERT INTO sales (salesperson_id, sale_amount)
VALUES (1, 100.00), (2, 200.00), (1, 150.00),
       (3, 300.00),(2, 120.00), (3, 250.00);
ANALYZE TABLE sales;

--sorted_result
SELECT RANK() OVER () AS sales_rank
FROM sales QUALIFY SUM(sale_amount) > 300;

--echo #
--echo # Qualify with VIEW
--echo #

CREATE VIEW v1 as
SELECT salesperson_id,   SUM(sale_amount) AS total_sales,
RANK() OVER (ORDER BY SUM(sale_amount) DESC) AS sales_rank
FROM sales GROUP BY salesperson_id
QUALIFY SUM(sale_amount) > 3000;

SHOW CREATE VIEW v1;

--sorted_result
SELECT * FROM v1;

SET @start_value_optimizer_switch = @@optimizer_switch;
SET optimizer_switch="hypergraph_optimizer=off";

SHOW CREATE VIEW v1;

--sorted_result
--error ER_SUPPORTED_ONLY_WITH_HYPERGRAPH
SELECT * FROM v1;

CREATE VIEW v2 as
SELECT SUM(sale_amount) AS total_sales,
RANK() OVER (ORDER BY SUM(sale_amount) DESC) AS sales_rank
FROM sales GROUP BY salesperson_id
QUALIFY SUM(sale_amount) > 3000;

SHOW CREATE VIEW v2;

--error ER_SUPPORTED_ONLY_WITH_HYPERGRAPH
SELECT * FROM v2;

SET @@optimizer_switch = @start_value_optimizer_switch;
SHOW CREATE VIEW v2;

--sorted_result
SELECT * FROM v2;

DROP VIEW v1, v2;
DROP TABLE sales;

--echo #
--echo # Bug 35809648 - #wl15864-Mysqld Crash-Assertion-Item_func::contributes_to_filter
--echo #

CREATE TABLE t1 (pk INT);
CREATE TABLE t2 (pk INT, col_decimal_10_8 DECIMAL(10,8));
ANALYZE TABLE t1, t2;
SET @start_value_optimizer_switch = @@optimizer_switch;
SET optimizer_switch="subquery_to_derived=on,hypergraph_optimizer=on";

--sorted_result
SELECT  RANK() OVER () AS field3
FROM t1
 WHERE ( SELECT  MAX( t2.col_decimal_10_8 ) AS derived_1
         FROM  t2) IS NOT NULL
QUALIFY field3 <=> 'a';

SET @@optimizer_switch = @start_value_optimizer_switch;
DROP TABLE t1, t2;

--echo #
--echo # Bug 35826727 - #wl15864-Mysqld-crash-Segmentation Fault- Item_ref::fix_fields
--echo #

CREATE TABLE t1 (col_varchar137 VARCHAR(137), col_float FLOAT);
CREATE TABLE t2 (pk INT);
ANALYZE TABLE t1, t2;

--error ER_QUALIFY_WITHOUT_WINDOW_FUNCTION
SELECT t1.col_varchar137 AS field1
FROM t1, LATERAL ( SELECT t1.col_float AS field2 FROM  t2 ) AS table6
QUALIFY field2 != 'USA';

DROP TABLE t1, t2;

--echo #
--echo # Bug 35831314 - #wl15864-Mysqld Crash-Assertion-Item_func::contributes_to_filter
--echo #

CREATE TABLE t1 (pk INT, col_float_key FLOAT );
CREATE TABLE t2 (col_varchar132 VARCHAR(132), col_timestamp_key TIMESTAMP);
ANALYZE TABLE t1, t2;

--sorted_result
SELECT CUME_DIST() OVER () AS field1
FROM t1
WHERE
  EXISTS ( SELECT LEAST(t2.col_varchar132, t2.col_timestamp_key) AS SQ1_field1
           FROM  t2
         )
  AND ( FLOOR(ATAN(t1.col_float_key) ) IS NULL )
QUALIFY field1 = 'V' COLLATE utf8mb4_icelandic_ci;

DROP TABLE t1, t2;


--echo #
--echo # Bug 35846292 - #wl15864-Mysqld Crash-Item_func::contributes_to_filter
--echo #

CREATE TABLE cc (col_time TIME, col_year YEAR, col_double_key DOUBLE,
                 col_char_255 CHAR(255));
ANALYZE TABLE cc;
SET @orig_sql_mode = @@sql_mode;
SET @@sql_mode     = '';

SELECT SUM( col_time + CAST(col_year AS FLOAT) ) AS field1,
       ROW_NUMBER() OVER () AS field2
FROM cc
WHERE (12, 'H') IN ( SELECT
                        DENSE_RANK() OVER ( ORDER BY col_double_key ),
                        FIRST_VALUE(col_char_255)
                        OVER ( ROWS BETWEEN CURRENT ROW
                                          AND UNBOUNDED FOLLOWING )
                      FROM cc )
QUALIFY field1 <=> 'M';

SET @@sql_mode = @orig_sql_mode;

DROP TABLE cc;

--echo #
--echo # Bug#36172350: Wrong result with QUALIFY referencing columns not present
--echo #               in the SELECT list
--echo #

CREATE TABLE t(id INT PRIMARY KEY, x INT, b BLOB);
INSERT INTO t(id, x) VALUES (1,1),(2,1),(3,1),(4,1),(5,0);

SELECT id, b FROM t GROUP BY id WINDOW w AS (ORDER BY id) QUALIFY x <> 1;
SELECT id, b, ROW_NUMBER() OVER w FROM t
GROUP BY id WINDOW w AS (ORDER BY id) QUALIFY x <> 1;
SELECT id, b, ROW_NUMBER() OVER w FROM t
WINDOW w AS (ORDER BY id) QUALIFY x + SUM(x) OVER w <> 4;
SELECT id, x, ROW_NUMBER() OVER w FROM t GROUP BY id, x
WINDOW w AS (ORDER BY id) QUALIFY x <> 1;
SELECT id, x, ROW_NUMBER() OVER w FROM t GROUP BY id, x WITH ROLLUP
WINDOW w AS (ORDER BY id) QUALIFY x <> 1;
SELECT id FROM t GROUP BY id, x WINDOW w AS (ORDER BY id) QUALIFY x <> 1;
SELECT id, x, ROW_NUMBER() OVER w FROM (SELECT * FROM t) AS dt
GROUP BY id, x WITH ROLLUP WINDOW w AS (ORDER BY id) QUALIFY x <> 1;
SELECT id, ROW_NUMBER() OVER w FROM (SELECT * FROM t) AS dt
GROUP BY id, x WITH ROLLUP WINDOW w AS (ORDER BY id) QUALIFY x <> 1;

DROP TABLE t;

--echo #
--echo # Bug#36313072: mysqld crash in Item_ref::real_item
--echo #

CREATE TABLE t1 (f1 INTEGER);

SELECT NTILE(1) OVER() FROM (SELECT * FROM t1) as dt QUALIFY f1 AND FALSE;

DROP TABLE t1;