summaryrefslogtreecommitdiff
path: root/mysql-test/main/win_empty_over.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main/win_empty_over.result')
-rw-r--r--mysql-test/main/win_empty_over.result145
1 files changed, 145 insertions, 0 deletions
diff --git a/mysql-test/main/win_empty_over.result b/mysql-test/main/win_empty_over.result
new file mode 100644
index 00000000000..a5ee74b943a
--- /dev/null
+++ b/mysql-test/main/win_empty_over.result
@@ -0,0 +1,145 @@
+create table t1 (
+pk int primary key,
+a int,
+b int,
+c char(10),
+d decimal(10, 3),
+e real
+);
+insert into t1 values
+( 1, 0, 1, 'one', 0.1, 0.001),
+( 2, 0, 2, 'two', 0.2, 0.002),
+( 3, 0, 3, 'three', 0.3, 0.003),
+( 4, 1, 2, 'three', 0.4, 0.004),
+( 5, 1, 1, 'two', 0.5, 0.005),
+( 6, 1, 1, 'one', 0.6, 0.006),
+( 7, 2, NULL, 'n_one', 0.5, 0.007),
+( 8, 2, 1, 'n_two', NULL, 0.008),
+( 9, 2, 2, NULL, 0.7, 0.009),
+(10, 2, 0, 'n_four', 0.8, 0.010),
+(11, 2, 10, NULL, 0.9, NULL);
+select pk, row_number() over () from t1;
+pk row_number() over ()
+1 1
+2 2
+3 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+10 10
+11 11
+explain FORMAT=JSON select pk, row_number() over () from t1;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "window_functions_computation": {
+ "sorts": {
+ "filesort": {
+ "sort_key": "pk"
+ }
+ },
+ "temporary_table": {
+ "table": {
+ "table_name": "t1",
+ "access_type": "index",
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["pk"],
+ "rows": 11,
+ "filtered": 100,
+ "using_index": true
+ }
+ }
+ }
+ }
+}
+explain FORMAT=JSON select row_number() over (), pk from t1;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "window_functions_computation": {
+ "sorts": {
+ "filesort": {
+ "sort_key": "`row_number() over ()`"
+ }
+ },
+ "temporary_table": {
+ "table": {
+ "table_name": "t1",
+ "access_type": "index",
+ "key": "PRIMARY",
+ "key_length": "4",
+ "used_key_parts": ["pk"],
+ "rows": 11,
+ "filtered": 100,
+ "using_index": true
+ }
+ }
+ }
+ }
+}
+select row_number() over () from (select 4) as t;
+row_number() over ()
+1
+select min(a) over (), max(a) over (), a, row_number() over ()
+from t1
+where a = 0;
+min(a) over () max(a) over () a row_number() over ()
+0 0 0 1
+0 0 0 2
+0 0 0 3
+select a, min(a) over (), max(a) over (), row_number() over ()
+from t1
+where a = 0;
+a min(a) over () max(a) over () row_number() over ()
+0 0 0 1
+0 0 0 2
+0 0 0 3
+select min(a) over () + 1, max(a) over (), row_number() over ()
+from t1
+where a = 0;
+min(a) over () + 1 max(a) over () row_number() over ()
+1 0 1
+1 0 2
+1 0 3
+select min(a) over () + a, max(a) over (), row_number() over ()
+from t1
+where a = 1;
+min(a) over () + a max(a) over () row_number() over ()
+2 1 1
+2 1 2
+2 1 3
+select a + min(a) over (), max(a) over (), row_number() over ()
+from t1
+where a = 1;
+a + min(a) over () max(a) over () row_number() over ()
+2 1 1
+2 1 2
+2 1 3
+select a + min(a) over () from t1 where a = 1;
+a + min(a) over ()
+2
+2
+2
+create view win_view
+as (select a, min(a) over () from t1 where a = 1);
+select * from win_view;
+a min(a) over ()
+1 1
+1 1
+1 1
+drop view win_view;
+create view win_view
+as (select a, max(a + 1) over () from t1 where a = 1);
+select * from win_view;
+a max(a + 1) over ()
+1 2
+1 2
+1 2
+drop view win_view;
+drop table t1;