diff options
Diffstat (limited to 'mysql-test/main/win_empty_over.result')
| -rw-r--r-- | mysql-test/main/win_empty_over.result | 157 |
1 files changed, 157 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 00000000..4fa53bb4 --- /dev/null +++ b/mysql-test/main/win_empty_over.result @@ -0,0 +1,157 @@ +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": { + "nested_loop": [ + { + "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": { + "nested_loop": [ + { + "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; |