Debian Codegraph @

mysql-test/suite/encryption/r/tempfiles_encrypted.result

raw
1 #
2 # Tests when the temporary files are encrypted
3 #
4 select @@encrypt_tmp_files;
5 @@encrypt_tmp_files
6 1
7 drop table if exists t1,t2;
8 drop view if exists v1;
9 # ########################################################################
10 # # Parser tests
11 # ########################################################################
12 #
13 # Check what happens when one attempts to use window function without OVER clause
14 create table t1 (a int, b int);
15 insert into t1 values (1,1),(2,2);
16 select row_number() from t1;
17 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from t1' at line 1
18 select rank() from t1;
19 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from t1' at line 1
20 # Attempt to use window function in the WHERE clause
21 select * from t1 where 1=rank() over (order by a);
22 ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
23 select * from t1 where 1>row_number() over (partition by b order by a);
24 ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
25 drop table t1;
26 # ########################################################################
27 # # Functionality tests
28 # ########################################################################
29 #
30 # Check if ROW_NUMBER() works in basic cases
31 create table t1(a int, b int, x char(32));
32 insert into t1 values (2, 10, 'xx');
33 insert into t1 values (2, 10, 'zz');
34 insert into t1 values (2, 20, 'yy');
35 insert into t1 values (3, 10, 'xxx');
36 insert into t1 values (3, 20, 'vvv');
37 select a, row_number() over (partition by a order by b) from t1;
38 a row_number() over (partition by a order by b)
39 2 1
40 2 2
41 2 3
42 3 1
43 3 2
44 select a, b, x, row_number() over (partition by a order by x) from t1;
45 a b x row_number() over (partition by a order by x)
46 2 10 xx 1
47 2 20 yy 2
48 2 10 zz 3
49 3 20 vvv 1
50 3 10 xxx 2
51 drop table t1;
52 create table t1 (pk int primary key, a int, b int);
53 insert into t1 values
54 (1, 10, 22),
55 (2, 11, 21),
56 (3, 12, 20),
57 (4, 13, 19),
58 (5, 14, 18);
59 select
60 pk, a, b,
61 row_number() over (order by a),
62 row_number() over (order by b)
63 from t1
64 order by b;
65 pk a b row_number() over (order by a) row_number() over (order by b)
66 5 14 18 5 1
67 4 13 19 4 2
68 3 12 20 3 3
69 2 11 21 2 4
70 1 10 22 1 5
71 drop table t1;
72 #
73 # Try RANK() function
74 #
75 create table t2 (
76 pk int primary key,
77 a int
78 );
79 insert into t2 values
80 ( 1 , 0),
81 ( 2 , 0),
82 ( 3 , 1),
83 ( 4 , 1),
84 ( 8 , 2),
85 ( 5 , 2),
86 ( 6 , 2),
87 ( 7 , 2),
88 ( 9 , 4),
89 (10 , 4);
90 select pk, a, rank() over (order by a) from t2;
91 pk a rank() over (order by a)
92 1 0 1
93 10 4 9
94 2 0 1
95 3 1 3
96 4 1 3
97 5 2 5
98 6 2 5
99 7 2 5
100 8 2 5
101 9 4 9
102 select pk, a, rank() over (order by a desc) from t2;
103 pk a rank() over (order by a desc)
104 1 0 9
105 10 4 1
106 2 0 9
107 3 1 7
108 4 1 7
109 5 2 3
110 6 2 3
111 7 2 3
112 8 2 3
113 9 4 1
114 drop table t2;
115 #
116 # Try Aggregates as window functions. With frames.
117 #
118 create table t0 (a int);
119 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
120 create table t1 (pk int, c int);
121 insert into t1 select a+1,1 from t0;
122 update t1 set c=2 where pk not in (1,2,3,4);
123 select * from t1;
124 pk c
125 1 1
126 2 1
127 3 1
128 4 1
129 5 2
130 6 2
131 7 2
132 8 2
133 9 2
134 10 2
135 select
136 pk, c,
137 count(*) over (partition by c order by pk
138 rows between 2 preceding and 2 following) as CNT
139 from t1;
140 pk c CNT
141 1 1 3
142 2 1 4
143 3 1 4
144 4 1 3
145 5 2 3
146 6 2 4
147 7 2 5
148 8 2 5
149 9 2 4
150 10 2 3
151 select
152 pk, c,
153 count(*) over (partition by c order by pk
154 rows between 1 preceding and 2 following) as CNT
155 from t1;
156 pk c CNT
157 1 1 3
158 2 1 4
159 3 1 3
160 4 1 2
161 5 2 3
162 6 2 4
163 7 2 4
164 8 2 4
165 9 2 3
166 10 2 2
167 select
168 pk, c,
169 count(*) over (partition by c order by pk
170 rows between 2 preceding and current row) as CNT
171 from t1;
172 pk c CNT
173 1 1 1
174 2 1 2
175 3 1 3
176 4 1 3
177 5 2 1
178 6 2 2
179 7 2 3
180 8 2 3
181 9 2 3
182 10 2 3
183 select
184 pk,c,
185 count(*) over (partition by c order by pk rows
186 between 1 following and 2 following) as CNT
187 from t1;
188 pk c CNT
189 1 1 2
190 2 1 2
191 3 1 1
192 4 1 0
193 5 2 2
194 6 2 2
195 7 2 2
196 8 2 2
197 9 2 1
198 10 2 0
199 select
200 pk,c,
201 count(*) over (partition by c order by pk rows
202 between 2 preceding and 1 preceding) as CNT
203 from t1;
204 pk c CNT
205 1 1 0
206 2 1 1
207 3 1 2
208 4 1 2
209 5 2 0
210 6 2 1
211 7 2 2
212 8 2 2
213 9 2 2
214 10 2 2
215 select
216 pk, c,
217 count(*) over (partition by c order by pk
218 rows between current row and 1 following) as CNT
219 from t1;
220 pk c CNT
221 1 1 2
222 2 1 2
223 3 1 2
224 4 1 1
225 5 2 2
226 6 2 2
227 7 2 2
228 8 2 2
229 9 2 2
230 10 2 1
231 # Check ORDER BY DESC
232 select
233 pk, c,
234 count(*) over (partition by c order by pk desc
235 rows between 2 preceding and 2 following) as CNT
236 from t1;
237 pk c CNT
238 4 1 3
239 3 1 4
240 2 1 4
241 1 1 3
242 10 2 3
243 9 2 4
244 8 2 5
245 7 2 5
246 6 2 4
247 5 2 3
248 drop table t0,t1;
249 #
250 # Resolution of window names
251 #
252 create table t0 (a int);
253 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
254 create table t1 (pk int, c int);
255 insert into t1 select a+1,1 from t0;
256 update t1 set c=2 where pk not in (1,2,3,4);
257 select * from t1;
258 pk c
259 1 1
260 2 1
261 3 1
262 4 1
263 5 2
264 6 2
265 7 2
266 8 2
267 9 2
268 10 2
269 select
270 pk, c,
271 count(*) over w1 as CNT
272 from t1
273 window w1 as (partition by c order by pk
274 rows between 2 preceding and 2 following);
275 pk c CNT
276 1 1 3
277 2 1 4
278 3 1 4
279 4 1 3
280 5 2 3
281 6 2 4
282 7 2 5
283 8 2 5
284 9 2 4
285 10 2 3
286 select
287 pk, c,
288 count(*) over (w1 rows between 2 preceding and 2 following) as CNT
289 from t1
290 window w1 as (partition by c order by pk);
291 pk c CNT
292 1 1 3
293 2 1 4
294 3 1 4
295 4 1 3
296 5 2 3
297 6 2 4
298 7 2 5
299 8 2 5
300 9 2 4
301 10 2 3
302 select
303 pk, c,
304 count(*) over (w1 order by pk rows between 2 preceding and 2 following) as CNT
305 from t1
306 window w1 as (partition by c);
307 pk c CNT
308 1 1 3
309 2 1 4
310 3 1 4
311 4 1 3
312 5 2 3
313 6 2 4
314 7 2 5
315 8 2 5
316 9 2 4
317 10 2 3
318 select
319 pk, c,
320 count(*) over (w2 rows between 2 preceding and 2 following) as CNT
321 from t1
322 window w1 as (partition by c), w2 as (w1 order by pk);
323 pk c CNT
324 1 1 3
325 2 1 4
326 3 1 4
327 4 1 3
328 5 2 3
329 6 2 4
330 7 2 5
331 8 2 5
332 9 2 4
333 10 2 3
334 select
335 pk, c,
336 count(*) over w3 as CNT
337 from t1
338 window
339 w1 as (partition by c),
340 w2 as (w1 order by pk),
341 w3 as (w2 rows between 2 preceding and 2 following);
342 pk c CNT
343 1 1 3
344 2 1 4
345 3 1 4
346 4 1 3
347 5 2 3
348 6 2 4
349 7 2 5
350 8 2 5
351 9 2 4
352 10 2 3
353 select
354 pk, c,
355 count(*) over w as CNT
356 from t1
357 window w1 as (partition by c order by pk
358 rows between 2 preceding and 2 following);
359 ERROR HY000: Window specification with name 'w' is not defined
360 select
361 pk, c,
362 count(*) over (w2 rows between 2 preceding and 2 following) as CNT
363 from t1
364 window w1 as (partition by c), w1 as (order by pk);
365 ERROR HY000: Multiple window specifications with the same name 'w1'
366 select
367 pk, c,
368 count(*) over (w2 rows between 2 preceding and 2 following) as CNT
369 from t1
370 window w1 as (partition by c), w2 as (w partition by c order by pk);
371 ERROR HY000: Window specification with name 'w' is not defined
372 select
373 pk, c,
374 count(*) over (w2 rows between 2 preceding and 2 following) as CNT
375 from t1
376 window w1 as (partition by c), w2 as (w1 partition by c order by pk);
377 ERROR HY000: Window specification referencing another one 'w1' cannot contain partition list
378 select
379 pk, c,
380 count(*) over (w2 rows between 2 preceding and 2 following) as CNT
381 from t1
382 window w1 as (partition by c order by pk), w2 as (w1 order by pk);
383 ERROR HY000: Referenced window specification 'w1' already contains order list
384 select
385 pk, c,
386 count(*) over w3 as CNT
387 from t1
388 window
389 w1 as (partition by c),
390 w2 as (w1 order by pk rows between 3 preceding and 2 following),
391 w3 as (w2 rows between 2 preceding and 2 following);
392 ERROR HY000: Referenced window specification 'w2' cannot contain window frame
393 select
394 pk, c,
395 count(*) over w1 as CNT
396 from t1
397 window w1 as (partition by c order by pk
398 rows between unbounded following and 2 following);
399 ERROR HY000: Unacceptable combination of window frame bound specifications
400 select
401 pk, c,
402 count(*) over (w1 rows between 2 preceding and unbounded preceding) as CNT
403 from t1
404 window w1 as (partition by c order by pk);
405 ERROR HY000: Unacceptable combination of window frame bound specifications
406 select
407 pk, c,
408 count(*) over (w1 order by pk rows between current row and 2 preceding) as CNT
409 from t1
410 window w1 as (partition by c);
411 ERROR HY000: Unacceptable combination of window frame bound specifications
412 select
413 pk, c,
414 count(*) over (w2 rows between 2 following and current row) as CNT
415 from t1
416 window w1 as (partition by c), w2 as (w1 order by pk);
417 ERROR HY000: Unacceptable combination of window frame bound specifications
418 select
419 pk, c
420 from t1 where rank() over w1 > 2
421 window w1 as (partition by c order by pk);
422 ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
423 select
424 c, max(pk) as m
425 from t1
426 group by c + rank() over w1
427 window w1 as (order by m);
428 ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
429 select
430 c, max(pk) as m, rank() over w1 as r
431 from t1
432 group by c+r
433 window w1 as (order by m);
434 ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
435 select
436 c, max(pk) as m, rank() over w1 as r
437 from t1
438 group by c having c+r > 3
439 window w1 as (order by m);
440 ERROR HY000: Window function is allowed only in SELECT list and ORDER BY clause
441 select
442 c, max(pk) as m, rank() over w1 as r,
443 rank() over (partition by r+1 order by m)
444 from t1
445 group by c
446 window w1 as (order by m);
447 ERROR HY000: Window function is not allowed in window specification
448 select
449 c, max(pk) as m, rank() over w1 as r,
450 rank() over (partition by m order by r)
451 from t1
452 group by c
453 window w1 as (order by m);
454 ERROR HY000: Window function is not allowed in window specification
455 select
456 c, max(pk) as m, rank() over w1 as r, dense_rank() over w2 as dr
457 from t1
458 group by c
459 window w1 as (order by m), w2 as (partition by r order by m);
460 ERROR HY000: Window function is not allowed in window specification
461 select
462 pk, c,
463 row_number() over (partition by c order by pk
464 range between unbounded preceding and current row) as r
465 from t1;
466 ERROR HY000: Window frame is not allowed with 'row_number'
467 select
468 pk, c,
469 rank() over w1 as r
470 from t1
471 window w1 as (partition by c order by pk
472 rows between 2 preceding and 2 following);
473 ERROR HY000: Window frame is not allowed with 'rank'
474 select
475 pk, c,
476 dense_rank() over (partition by c order by pk
477 rows between 1 preceding and 1 following) as r
478 from t1;
479 ERROR HY000: Window frame is not allowed with 'dense_rank'
480 select
481 pk, c,
482 rank() over w1 as r
483 from t1
484 window w1 as (partition by c);
485 ERROR HY000: No order list in window specification for 'rank'
486 select
487 pk, c,
488 dense_rank() over (partition by c) as r
489 from t1;
490 ERROR HY000: No order list in window specification for 'dense_rank'
491 drop table t0,t1;
492 #
493 # MDEV-9634: Window function produces incorrect value
494 #
495 create table t0 (a int);
496 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
497 create table t2 (part_id int, pk int, a int);
498 insert into t2 select
499 if(a<5, 0, 1), a, if(a<5, NULL, 1) from t0;
500 select * from t2;
501 part_id pk a
502 0 0 NULL
503 0 1 NULL
504 0 2 NULL
505 0 3 NULL
506 0 4 NULL
507 1 5 1
508 1 6 1
509 1 7 1
510 1 8 1
511 1 9 1
512 select
513 part_id, pk, a,
514 count(a) over (partition by part_id order by pk
515 rows between 1 preceding and 1 following) as CNT
516 from t2;
517 part_id pk a CNT
518 0 0 NULL 0
519 0 1 NULL 0
520 0 2 NULL 0
521 0 3 NULL 0
522 0 4 NULL 0
523 1 5 1 2
524 1 6 1 3
525 1 7 1 3
526 1 8 1 3
527 1 9 1 2
528 drop table t0, t2;
529 #
530 # RANGE-type bounds
531 #
532 create table t3 (
533 pk int,
534 val int
535 );
536 insert into t3 values
537 (0, 1),
538 (1, 1),
539 (2, 1),
540 (3, 2),
541 (4, 2),
542 (5, 2),
543 (6, 2);
544 select
545 val,
546 count(val) over (order by val
547 range between current row and
548 current row)
549 as CNT
550 from t3;
551 val CNT
552 1 3
553 1 3
554 1 3
555 2 4
556 2 4
557 2 4
558 2 4
559 insert into t3 values
560 (7, 3),
561 (8, 3);
562 select
563 val,
564 count(val) over (order by val
565 range between current row and
566 current row)
567 as CNT
568 from t3;
569 val CNT
570 1 3
571 1 3
572 1 3
573 2 4
574 2 4
575 2 4
576 2 4
577 3 2
578 3 2
579 drop table t3;
580 # Now, check with PARTITION BY
581 create table t4 (
582 part_id int,
583 pk int,
584 val int
585 );
586 insert into t4 values
587 (1234, 100, 1),
588 (1234, 101, 1),
589 (1234, 102, 1),
590 (1234, 103, 2),
591 (1234, 104, 2),
592 (1234, 105, 2),
593 (1234, 106, 2),
594 (1234, 107, 3),
595 (1234, 108, 3),
596 (5678, 200, 1),
597 (5678, 201, 1),
598 (5678, 202, 1),
599 (5678, 203, 2),
600 (5678, 204, 2),
601 (5678, 205, 2),
602 (5678, 206, 2),
603 (5678, 207, 3),
604 (5678, 208, 3);
605 select
606 part_id,
607 val,
608 count(val) over (partition by part_id
609 order by val
610 range between current row and
611 current row)
612 as CNT
613 from t4;
614 part_id val CNT
615 1234 1 3
616 1234 1 3
617 1234 1 3
618 1234 2 4
619 1234 2 4
620 1234 2 4
621 1234 2 4
622 1234 3 2
623 1234 3 2
624 5678 1 3
625 5678 1 3
626 5678 1 3
627 5678 2 4
628 5678 2 4
629 5678 2 4
630 5678 2 4
631 5678 3 2
632 5678 3 2
633 #
634 # Try RANGE UNBOUNDED PRECEDING | FOLLOWING
635 #
636 select
637 part_id,
638 val,
639 count(val) over (partition by part_id
640 order by val
641 range between unbounded preceding and
642 current row)
643 as CNT
644 from t4;
645 part_id val CNT
646 1234 1 3
647 1234 1 3
648 1234 1 3
649 1234 2 7
650 1234 2 7
651 1234 2 7
652 1234 2 7
653 1234 3 9
654 1234 3 9
655 5678 1 3
656 5678 1 3
657 5678 1 3
658 5678 2 7
659 5678 2 7
660 5678 2 7
661 5678 2 7
662 5678 3 9
663 5678 3 9
664 select
665 part_id,
666 val,
667 count(val) over (partition by part_id
668 order by val
669 range between current row and
670 unbounded following)
671 as CNT
672 from t4;
673 part_id val CNT
674 1234 1 9
675 1234 1 9
676 1234 1 9
677 1234 2 6
678 1234 2 6
679 1234 2 6
680 1234 2 6
681 1234 3 2
682 1234 3 2
683 5678 1 9
684 5678 1 9
685 5678 1 9
686 5678 2 6
687 5678 2 6
688 5678 2 6
689 5678 2 6
690 5678 3 2
691 5678 3 2
692 select
693 part_id,
694 val,
695 count(val) over (partition by part_id
696 order by val
697 range between unbounded preceding and
698 unbounded following)
699 as CNT
700 from t4;
701 part_id val CNT
702 1234 1 9
703 1234 1 9
704 1234 1 9
705 1234 2 9
706 1234 2 9
707 1234 2 9
708 1234 2 9
709 1234 3 9
710 1234 3 9
711 5678 1 9
712 5678 1 9
713 5678 1 9
714 5678 2 9
715 5678 2 9
716 5678 2 9
717 5678 2 9
718 5678 3 9
719 5678 3 9
720 drop table t4;
721 #
722 # MDEV-9695: Wrong window frame when using RANGE BETWEEN N FOLLOWING AND PRECEDING
723 #
724 create table t1 (pk int, a int, b int);
725 insert into t1 values
726 ( 1 , 0, 1),
727 ( 2 , 0, 2),
728 ( 3 , 1, 4),
729 ( 4 , 1, 8),
730 ( 5 , 2, 32),
731 ( 6 , 2, 64),
732 ( 7 , 2, 128),
733 ( 8 , 2, 16);
734 select pk, a, b,
735 bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as bit_or
736 from t1;
737 pk a b bit_or
738 1 0 1 3
739 2 0 2 3
740 3 1 4 12
741 4 1 8 12
742 5 2 32 96
743 6 2 64 224
744 7 2 128 208
745 8 2 16 144
746 # Extra ROWS n PRECEDING tests
747 select pk, a, b,
748 bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as bit_or
749 from t1;
750 pk a b bit_or
751 1 0 1 0
752 2 0 2 1
753 3 1 4 0
754 4 1 8 4
755 5 2 32 0
756 6 2 64 32
757 7 2 128 64
758 8 2 16 128
759 drop table t1;
760 create table t2 (
761 pk int,
762 a int,
763 b int
764 );
765 insert into t2 values
766 ( 1, 0, 1),
767 ( 2, 0, 2),
768 ( 3, 0, 4),
769 ( 4, 0, 8),
770 ( 5, 1, 16),
771 ( 6, 1, 32),
772 ( 7, 1, 64),
773 ( 8, 1, 128),
774 ( 9, 2, 256),
775 (10, 2, 512),
776 (11, 2, 1024),
777 (12, 2, 2048);
778 select pk, a, b,
779 bit_or(b) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as bit_or
780 from t2;
781 pk a b bit_or
782 1 0 1 0
783 2 0 2 1
784 3 0 4 2
785 4 0 8 4
786 5 1 16 0
787 6 1 32 16
788 7 1 64 32
789 8 1 128 64
790 9 2 256 0
791 10 2 512 256
792 11 2 1024 512
793 12 2 2048 1024
794 select pk, a, b,
795 bit_or(b) over (partition by a order by pk ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) as bit_or
796 from t2;
797 pk a b bit_or
798 1 0 1 0
799 2 0 2 0
800 3 0 4 1
801 4 0 8 2
802 5 1 16 0
803 6 1 32 0
804 7 1 64 16
805 8 1 128 32
806 9 2 256 0
807 10 2 512 0
808 11 2 1024 256
809 12 2 2048 512
810 select pk, a, b,
811 bit_or(b) over (partition by a order by pk ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) as bit_or
812 from t2;
813 pk a b bit_or
814 1 0 1 0
815 2 0 2 1
816 3 0 4 3
817 4 0 8 6
818 5 1 16 0
819 6 1 32 16
820 7 1 64 48
821 8 1 128 96
822 9 2 256 0
823 10 2 512 256
824 11 2 1024 768
825 12 2 2048 1536
826 # Check CURRENT ROW
827 select pk, a, b,
828 bit_or(b) over (partition by a order by pk ROWS BETWEEN CURRENT ROW AND CURRENT ROW) as bit_or
829 from t2;
830 pk a b bit_or
831 1 0 1 1
832 2 0 2 2
833 3 0 4 4
834 4 0 8 8
835 5 1 16 16
836 6 1 32 32
837 7 1 64 64
838 8 1 128 128
839 9 2 256 256
840 10 2 512 512
841 11 2 1024 1024
842 12 2 2048 2048
843 drop table t2;
844 #
845 # Try RANGE PRECEDING|FOLLWING n
846 #
847 create table t1 (
848 part_id int,
849 pk int,
850 a int
851 );
852 insert into t1 values
853 (10, 1, 1),
854 (10, 2, 2),
855 (10, 3, 4),
856 (10, 4, 8),
857 (10, 5,26),
858 (10, 6,27),
859 (10, 7,40),
860 (10, 8,71),
861 (10, 9,72);
862 select
863 pk, a,
864 count(a) over (ORDER BY a
865 RANGE BETWEEN UNBOUNDED PRECEDING
866 AND 10 FOLLOWING) as cnt
867 from t1;
868 pk a cnt
869 1 1 4
870 2 2 4
871 3 4 4
872 4 8 4
873 5 26 6
874 6 27 6
875 7 40 7
876 8 71 9
877 9 72 9
878 select
879 pk, a,
880 count(a) over (ORDER BY a DESC
881 RANGE BETWEEN UNBOUNDED PRECEDING
882 AND 10 FOLLOWING) as cnt
883 from t1;
884 pk a cnt
885 9 72 2
886 8 71 2
887 7 40 3
888 6 27 5
889 5 26 5
890 4 8 9
891 3 4 9
892 2 2 9
893 1 1 9
894 select
895 pk, a,
896 count(a) over (ORDER BY a
897 RANGE BETWEEN UNBOUNDED PRECEDING
898 AND 1 FOLLOWING) as cnt
899 from t1;
900 pk a cnt
901 1 1 2
902 2 2 2
903 3 4 3
904 4 8 4
905 5 26 6
906 6 27 6
907 7 40 7
908 8 71 9
909 9 72 9
910 select
911 pk, a,
912 count(a) over (ORDER BY a
913 RANGE BETWEEN UNBOUNDED PRECEDING
914 AND 10 PRECEDING) as cnt
915 from t1;
916 pk a cnt
917 1 1 0
918 2 2 0
919 3 4 0
920 4 8 0
921 5 26 4
922 6 27 4
923 7 40 6
924 8 71 7
925 9 72 7
926 select
927 pk, a,
928 count(a) over (ORDER BY a DESC
929 RANGE BETWEEN UNBOUNDED PRECEDING
930 AND 10 PRECEDING) as cnt
931 from t1;
932 pk a cnt
933 9 72 0
934 8 71 0
935 7 40 2
936 6 27 3
937 5 26 3
938 4 8 5
939 3 4 5
940 2 2 5
941 1 1 5
942 select
943 pk, a,
944 count(a) over (ORDER BY a
945 RANGE BETWEEN UNBOUNDED PRECEDING
946 AND 1 PRECEDING) as cnt
947 from t1;
948 pk a cnt
949 1 1 0
950 2 2 1
951 3 4 2
952 4 8 3
953 5 26 4
954 6 27 5
955 7 40 6
956 8 71 7
957 9 72 8
958 select
959 pk, a,
960 count(a) over (ORDER BY a
961 RANGE BETWEEN 1 PRECEDING
962 AND CURRENT ROW) as cnt
963 from t1;
964 pk a cnt
965 1 1 1
966 2 2 2
967 3 4 1
968 4 8 1
969 5 26 1
970 6 27 2
971 7 40 1
972 8 71 1
973 9 72 2
974 select
975 pk, a,
976 count(a) over (ORDER BY a DESC
977 RANGE BETWEEN 1 PRECEDING
978 AND CURRENT ROW) as cnt
979 from t1;
980 pk a cnt
981 9 72 1
982 8 71 2
983 7 40 1
984 6 27 1
985 5 26 2
986 4 8 1
987 3 4 1
988 2 2 1
989 1 1 2
990 select
991 pk, a,
992 count(a) over (ORDER BY a
993 RANGE BETWEEN 1 FOLLOWING
994 AND 3 FOLLOWING) as cnt
995 from t1;
996 pk a cnt
997 1 1 2
998 2 2 1
999 3 4 0
1000 4 8 0
1001 5 26 1
1002 6 27 0
1003 7 40 0
1004 8 71 1
1005 9 72 0
1006 # Try CURRENT ROW with[out] DESC
1007 select
1008 pk, a,
1009 count(a) over (ORDER BY a
1010 RANGE BETWEEN CURRENT ROW
1011 AND 1 FOLLOWING) as cnt
1012 from t1;
1013 pk a cnt
1014 1 1 2
1015 2 2 1
1016 3 4 1
1017 4 8 1
1018 5 26 2
1019 6 27 1
1020 7 40 1
1021 8 71 2
1022 9 72 1
1023 select
1024 pk, a,
1025 count(a) over (order by a desc
1026 range between current row
1027 and 1 following) as cnt
1028 from t1;
1029 pk a cnt
1030 9 72 2
1031 8 71 1
1032 7 40 1
1033 6 27 2
1034 5 26 1
1035 4 8 1
1036 3 4 1
1037 2 2 2
1038 1 1 1
1039 insert into t1 select 22, pk, a from t1;
1040 select
1041 part_id, pk, a,
1042 count(a) over (PARTITION BY part_id
1043 ORDER BY a
1044 RANGE BETWEEN UNBOUNDED PRECEDING
1045 AND 10 FOLLOWING) as cnt
1046 from t1;
1047 part_id pk a cnt
1048 10 1 1 4
1049 10 2 2 4
1050 10 3 4 4
1051 10 4 8 4
1052 10 5 26 6
1053 10 6 27 6
1054 10 7 40 7
1055 10 8 71 9
1056 10 9 72 9
1057 22 1 1 4
1058 22 2 2 4
1059 22 3 4 4
1060 22 4 8 4
1061 22 5 26 6
1062 22 6 27 6
1063 22 7 40 7
1064 22 8 71 9
1065 22 9 72 9
1066 select
1067 pk, a,
1068 count(a) over (PARTITION BY part_id
1069 ORDER BY a
1070 RANGE BETWEEN UNBOUNDED PRECEDING
1071 AND 1 PRECEDING) as cnt
1072 from t1;
1073 pk a cnt
1074 1 1 0
1075 2 2 1
1076 3 4 2
1077 4 8 3
1078 5 26 4
1079 6 27 5
1080 7 40 6
1081 8 71 7
1082 9 72 8
1083 1 1 0
1084 2 2 1
1085 3 4 2
1086 4 8 3
1087 5 26 4
1088 6 27 5
1089 7 40 6
1090 8 71 7
1091 9 72 8
1092 drop table t1;
1093 # Try a RANGE frame over non-integer datatype:
1094 create table t1 (
1095 col1 int,
1096 a decimal(5,3)
1097 );
1098 insert into t1 values (1, 0.45);
1099 insert into t1 values (1, 0.5);
1100 insert into t1 values (1, 0.55);
1101 insert into t1 values (1, 1.21);
1102 insert into t1 values (1, 1.22);
1103 insert into t1 values (1, 3.33);
1104 select
1105 a,
1106 count(col1) over (order by a
1107 range between 0.1 preceding
1108 and 0.1 following) as count
1109 from t1;
1110 a count
1111 0.450 3
1112 0.500 3
1113 0.550 3
1114 1.210 2
1115 1.220 2
1116 3.330 1
1117 drop table t1;
1118 #
1119 # RANGE-type frames and NULL values
1120 #
1121 create table t1 (
1122 pk int,
1123 a int,
1124 b int
1125 );
1126 insert into t1 values (1, NULL,1);
1127 insert into t1 values (2, NULL,1);
1128 insert into t1 values (3, NULL,1);
1129 insert into t1 values (4, 10 ,1);
1130 insert into t1 values (5, 11 ,1);
1131 insert into t1 values (6, 12 ,1);
1132 insert into t1 values (7, 13 ,1);
1133 insert into t1 values (8, 14 ,1);
1134 select
1135 pk, a,
1136 count(b) over (order by a
1137 range between 2 preceding
1138 and 2 following) as CNT
1139 from t1
1140 order by a, pk;
1141 pk a CNT
1142 1 NULL 3
1143 2 NULL 3
1144 3 NULL 3
1145 4 10 3
1146 5 11 4
1147 6 12 5
1148 7 13 4
1149 8 14 3
1150 drop table t1;
1151 #
1152 # Try ranges that have bound1 > bound2. The standard actually allows them
1153 #
1154 create table t0 (a int);
1155 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1156 create table t1 (pk int, c int);
1157 insert into t1 select a+1,1 from t0;
1158 update t1 set c=2 where pk not in (1,2,3,4);
1159 select * from t1;
1160 pk c
1161 1 1
1162 2 1
1163 3 1
1164 4 1
1165 5 2
1166 6 2
1167 7 2
1168 8 2
1169 9 2
1170 10 2
1171 select
1172 pk, c,
1173 count(*) over (partition by c
1174 order by pk
1175 rows between 1 preceding
1176 and 2 preceding)
1177 as cnt
1178 from t1;
1179 pk c cnt
1180 1 1 0
1181 2 1 0
1182 3 1 0
1183 4 1 0
1184 5 2 0
1185 6 2 0
1186 7 2 0
1187 8 2 0
1188 9 2 0
1189 10 2 0
1190 select
1191 pk, c,
1192 sum(c) over (partition by c
1193 order by pk
1194 rows between 1 preceding
1195 and 2 preceding)
1196 as sum
1197 from t1;
1198 pk c sum
1199 1 1 NULL
1200 2 1 NULL
1201 3 1 NULL
1202 4 1 NULL
1203 5 2 NULL
1204 6 2 NULL
1205 7 2 NULL
1206 8 2 NULL
1207 9 2 NULL
1208 10 2 NULL
1209 select
1210 pk, c,
1211 sum(c) over (partition by c
1212 order by pk
1213 rows between 2 following
1214 and 1 following)
1215 as sum
1216 from t1;
1217 pk c sum
1218 1 1 NULL
1219 2 1 NULL
1220 3 1 NULL
1221 4 1 NULL
1222 5 2 NULL
1223 6 2 NULL
1224 7 2 NULL
1225 8 2 NULL
1226 9 2 NULL
1227 10 2 NULL
1228 select
1229 pk, c,
1230 count(*) over (partition by c
1231 order by pk
1232 range between 1 preceding
1233 and 2 preceding)
1234 as cnt
1235 from t1;
1236 pk c cnt
1237 1 1 0
1238 2 1 0
1239 3 1 0
1240 4 1 0
1241 5 2 0
1242 6 2 0
1243 7 2 0
1244 8 2 0
1245 9 2 0
1246 10 2 0
1247 drop table t0, t1;
1248 #
1249 # Error checking for frame bounds
1250 #
1251 create table t1 (a int, b int, c varchar(32));
1252 insert into t1 values (1,1,'foo');
1253 insert into t1 values (2,2,'bar');
1254 select
1255 count(*) over (order by a,b
1256 range between 1 preceding and current row) as count
1257 from t1;
1258 ERROR HY000: RANGE-type frame requires ORDER BY clause with single sort key
1259 select
1260 count(*) over (order by a
1261 range between 'abcd' preceding and current row) as count
1262 from t1;
1263 ERROR HY000: Numeric datatype is required for RANGE-type frame
1264 select
1265 count(*) over (order by a
1266 range between current row and 'foo' following) as count
1267 from t1;
1268 ERROR HY000: Numeric datatype is required for RANGE-type frame
1269 # Try range frame with invalid bounds
1270 select
1271 count(*) over (order by a
1272 rows between 0.5 preceding and current row) as count
1273 from t1;
1274 ERROR HY000: Integer is required for ROWS-type frame
1275 select
1276 count(*) over (order by a
1277 rows between current row and 3.14 following) as count
1278 from t1;
1279 ERROR HY000: Integer is required for ROWS-type frame
1280 #
1281 # MDEV-19052 Range-type window frame supports only numeric datatype
1282 #
1283 select
1284 count(*) over (order by c
1285 range between unbounded preceding and current row) as r
1286 from t1;
1287 r
1288 1
1289 2
1290 select
1291 count(*) over (order by c
1292 range between current row and unbounded following) as r
1293 from t1;
1294 r
1295 2
1296 1
1297 select
1298 count(*) over (order by c
1299 range between unbounded preceding and unbounded following) as r
1300 from t1;
1301 r
1302 2
1303 2
1304 create table t2 (a int, b varchar(5));
1305 insert into t2 values (1,'a'), (2, 'b'), (3, 'c');
1306 select sum(a) over (order by b range between unbounded preceding and current row) as r from t2;
1307 r
1308 1
1309 3
1310 6
1311 insert into t1 values (3,3,'goo');
1312 insert into t1 values (3,1,'har');
1313 insert into t1 values (1,4,'har');
1314 select a, b, sum(b) over (order by a, b desc range between unbounded preceding and current row) as r from t1;
1315 a b r
1316 1 4 4
1317 1 1 5
1318 2 2 7
1319 3 3 10
1320 3 1 11
1321 select a, b, sum(b) over (order by a desc, b range between unbounded preceding and current row) as r from t1;
1322 a b r
1323 3 1 1
1324 3 3 4
1325 2 2 6
1326 1 1 7
1327 1 4 11
1328 drop table t2;
1329 delete from t1 where a >= 3 or b = 4;
1330 #
1331 # EXCLUDE clause is parsed but not supported
1332 #
1333 select
1334 count(*) over (order by a
1335 rows between 1 preceding and 1 following
1336 exclude current row) as count
1337 from t1;
1338 ERROR HY000: Frame exclusion is not supported yet
1339 select
1340 count(*) over (order by a
1341 range between 1 preceding and 1 following
1342 exclude ties) as count
1343 from t1;
1344 ERROR HY000: Frame exclusion is not supported yet
1345 select
1346 count(*) over (order by a
1347 range between 1 preceding and 1 following
1348 exclude group) as count
1349 from t1;
1350 ERROR HY000: Frame exclusion is not supported yet
1351 select
1352 count(*) over (order by a
1353 rows between 1 preceding and 1 following
1354 exclude no others) as count
1355 from t1;
1356 count
1357 2
1358 2
1359 drop table t1;
1360 #
1361 # Window function in grouping query
1362 #
1363 create table t1 (
1364 username varchar(32),
1365 amount int
1366 );
1367 insert into t1 values
1368 ('user1',1),
1369 ('user1',5),
1370 ('user1',3),
1371 ('user2',10),
1372 ('user2',20),
1373 ('user2',30);
1374 select
1375 username,
1376 sum(amount) as s,
1377 rank() over (order by s desc)
1378 from t1
1379 group by username;
1380 username s rank() over (order by s desc)
1381 user1 9 2
1382 user2 60 1
1383 drop table t1;
1384 #
1385 # mdev-9719: Window function in prepared statement
1386 #
1387 create table t1(a int, b int, x char(32));
1388 insert into t1 values (2, 10, 'xx');
1389 insert into t1 values (2, 10, 'zz');
1390 insert into t1 values (2, 20, 'yy');
1391 insert into t1 values (3, 10, 'xxx');
1392 insert into t1 values (3, 20, 'vvv');
1393 prepare stmt from 'select a, row_number() over (partition by a order by b) from t1';
1394 execute stmt;
1395 a row_number() over (partition by a order by b)
1396 2 1
1397 2 2
1398 2 3
1399 3 1
1400 3 2
1401 drop table t1;
1402 #
1403 # mdev-9754: Window name resolution in prepared statement
1404 #
1405 create table t0 (a int);
1406 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1407 create table t1 (pk int, c int);
1408 insert into t1 select a+1,1 from t0;
1409 update t1 set c=2 where pk not in (1,2,3,4);
1410 select * from t1;
1411 pk c
1412 1 1
1413 2 1
1414 3 1
1415 4 1
1416 5 2
1417 6 2
1418 7 2
1419 8 2
1420 9 2
1421 10 2
1422 prepare stmt from
1423 'select
1424 pk, c,
1425 count(*) over w1 as CNT
1426 from t1
1427 window w1 as (partition by c order by pk
1428 rows between 2 preceding and 2 following)';
1429 execute stmt;
1430 pk c CNT
1431 1 1 3
1432 2 1 4
1433 3 1 4
1434 4 1 3
1435 5 2 3
1436 6 2 4
1437 7 2 5
1438 8 2 5
1439 9 2 4
1440 10 2 3
1441 drop table t0,t1;
1442 #
1443 # EXPLAIN FORMAT=JSON support for window functions
1444 #
1445 create table t0 (a int);
1446 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1447 explain format=json select rank() over (order by a) from t0;
1448 EXPLAIN
1449 {
1450 "query_block": {
1451 "select_id": 1,
1452 "cost": "COST_REPLACED",
1453 "window_functions_computation": {
1454 "sorts": [
1455 {
1456 "filesort": {
1457 "sort_key": "t0.a"
1458 }
1459 }
1460 ],
1461 "temporary_table": {
1462 "nested_loop": [
1463 {
1464 "table": {
1465 "table_name": "t0",
1466 "access_type": "ALL",
1467 "loops": 1,
1468 "rows": 10,
1469 "cost": "COST_REPLACED",
1470 "filtered": 100
1471 }
1472 }
1473 ]
1474 }
1475 }
1476 }
1477 }
1478 create table t1 (a int, b int, c int);
1479 insert into t1 select a,a,a from t0;
1480 explain format=json
1481 select
1482 a,
1483 rank() over (order by sum(b))
1484 from t1
1485 group by a;
1486 EXPLAIN
1487 {
1488 "query_block": {
1489 "select_id": 1,
1490 "cost": "COST_REPLACED",
1491 "filesort": {
1492 "sort_key": "t1.a",
1493 "window_functions_computation": {
1494 "sorts": [
1495 {
1496 "filesort": {
1497 "sort_key": "sum(t1.b)"
1498 }
1499 }
1500 ],
1501 "temporary_table": {
1502 "nested_loop": [
1503 {
1504 "table": {
1505 "table_name": "t1",
1506 "access_type": "ALL",
1507 "loops": 1,
1508 "rows": 10,
1509 "cost": "COST_REPLACED",
1510 "filtered": 100
1511 }
1512 }
1513 ]
1514 }
1515 }
1516 }
1517 }
1518 }
1519 explain format=json
1520 select
1521 a,
1522 rank() over (order by sum(b))
1523 from t1
1524 group by a
1525 order by null;
1526 EXPLAIN
1527 {
1528 "query_block": {
1529 "select_id": 1,
1530 "cost": "COST_REPLACED",
1531 "window_functions_computation": {
1532 "sorts": [
1533 {
1534 "filesort": {
1535 "sort_key": "sum(t1.b)"
1536 }
1537 }
1538 ],
1539 "temporary_table": {
1540 "nested_loop": [
1541 {
1542 "table": {
1543 "table_name": "t1",
1544 "access_type": "ALL",
1545 "loops": 1,
1546 "rows": 10,
1547 "cost": "COST_REPLACED",
1548 "filtered": 100
1549 }
1550 }
1551 ]
1552 }
1553 }
1554 }
1555 }
1556 #
1557 # Check how window function works together with GROUP BY and HAVING
1558 #
1559 select b,max(a) as MX, rank() over (order by b) from t1 group by b having MX in (3,5,7);
1560 b MX rank() over (order by b)
1561 3 3 1
1562 5 5 2
1563 7 7 3
1564 explain format=json
1565 select b,max(a) as MX, rank() over (order by b) from t1 group by b having MX in (3,5,7);
1566 EXPLAIN
1567 {
1568 "query_block": {
1569 "select_id": 1,
1570 "cost": "COST_REPLACED",
1571 "having_condition": "MX in (3,5,7)",
1572 "filesort": {
1573 "sort_key": "t1.b",
1574 "window_functions_computation": {
1575 "sorts": [
1576 {
1577 "filesort": {
1578 "sort_key": "t1.b"
1579 }
1580 }
1581 ],
1582 "temporary_table": {
1583 "nested_loop": [
1584 {
1585 "table": {
1586 "table_name": "t1",
1587 "access_type": "ALL",
1588 "loops": 1,
1589 "rows": 10,
1590 "cost": "COST_REPLACED",
1591 "filtered": 100
1592 }
1593 }
1594 ]
1595 }
1596 }
1597 }
1598 }
1599 }
1600 drop table t1;
1601 drop table t0;
1602 #
1603 # Building ordering index for window functions
1604 #
1605 create table t1 (
1606 pk int primary key,
1607 a int,
1608 b int,
1609 c int
1610 );
1611 insert into t1 values
1612 (101 , 0, 10, 1),
1613 (102 , 0, 10, 2),
1614 (103 , 1, 10, 3),
1615 (104 , 1, 10, 4),
1616 (108 , 2, 10, 5),
1617 (105 , 2, 20, 6),
1618 (106 , 2, 20, 7),
1619 (107 , 2, 20, 8),
1620 (109 , 4, 20, 9),
1621 (110 , 4, 20, 10),
1622 (111 , 5, NULL, 11),
1623 (112 , 5, 1, 12),
1624 (113 , 5, NULL, 13),
1625 (114 , 5, NULL, 14),
1626 (115 , 5, NULL, 15),
1627 (116 , 6, 1, NULL),
1628 (117 , 6, 1, 10),
1629 (118 , 6, 1, 1),
1630 (119 , 6, 1, NULL),
1631 (120 , 6, 1, NULL),
1632 (121 , 6, 1, NULL),
1633 (122 , 6, 1, 2),
1634 (123 , 6, 1, 20),
1635 (124 , 6, 1, -10),
1636 (125 , 6, 1, NULL),
1637 (126 , 6, 1, NULL),
1638 (127 , 6, 1, NULL);
1639 select sum(b) over (partition by a order by b,pk
1640 rows between unbounded preceding and current row) as c1,
1641 avg(b) over (w1 rows between 1 preceding and 1 following) as c2,
1642 sum(c) over (w2 rows between 1 preceding and 1 following) as c5,
1643 avg(b) over (w1 rows between 5 preceding and 5 following) as c3,
1644 sum(b) over (w1 rows between 1 preceding and 1 following) as c4
1645 from t1
1646 window w1 as (partition by a order by b,pk),
1647 w2 as (partition by b order by c,pk);
1648 c1 c2 c5 c3 c4
1649 1 1.0000 42 1.0000 1
1650 1 1.0000 NULL 1.0000 2
1651 10 1.0000 NULL 1.0000 3
1652 10 10.0000 3 10.0000 20
1653 10 10.0000 9 10.0000 20
1654 10 15.0000 9 17.5000 30
1655 11 1.0000 NULL 1.0000 3
1656 12 1.0000 -10 1.0000 2
1657 2 1.0000 24 1.0000 3
1658 20 10.0000 12 10.0000 20
1659 20 10.0000 6 10.0000 20
1660 20 20.0000 27 20.0000 40
1661 3 1.0000 -7 1.0000 3
1662 30 16.6667 13 17.5000 50
1663 4 1.0000 NULL 1.0000 3
1664 40 20.0000 19 20.0000 40
1665 5 1.0000 NULL 1.0000 3
1666 50 20.0000 21 17.5000 60
1667 6 1.0000 NULL 1.0000 3
1668 7 1.0000 13 1.0000 3
1669 70 20.0000 24 17.5000 40
1670 8 1.0000 32 1.0000 3
1671 9 1.0000 -9 1.0000 3
1672 NULL 1.0000 29 1.0000 1
1673 NULL NULL 24 1.0000 NULL
1674 NULL NULL 38 1.0000 NULL
1675 NULL NULL 42 1.0000 NULL
1676 drop table t1;
1677 #
1678 # MDEV-9848: Window functions: reuse sorting and/or scanning
1679 #
1680 create table t1 (a int, b int, c int);
1681 insert into t1 values
1682 (1,3,1),
1683 (2,2,1),
1684 (3,1,1);
1685 # Check using counters
1686 flush status;
1687 select
1688 rank() over (partition by c order by a) as rank_a,
1689 rank() over (partition by c order by b) as rank_b
1690 from t1;
1691 rank_a rank_b
1692 1 3
1693 2 2
1694 3 1
1695 show status like '%sort%';
1696 Variable_name Value
1697 Sort_merge_passes 0
1698 Sort_priority_queue_sorts 0
1699 Sort_range 0
1700 Sort_rows 6
1701 Sort_scan 2
1702 flush status;
1703 select
1704 rank() over (partition by c order by a) as rank_a,
1705 rank() over (partition by c order by a) as rank_b
1706 from t1;
1707 rank_a rank_b
1708 1 1
1709 2 2
1710 3 3
1711 show status like '%sort%';
1712 Variable_name Value
1713 Sort_merge_passes 0
1714 Sort_priority_queue_sorts 0
1715 Sort_range 0
1716 Sort_rows 3
1717 Sort_scan 1
1718 explain format=json
1719 select
1720 rank() over (partition by c order by a) as rank_a,
1721 rank() over (partition by c order by a) as rank_b
1722 from t1;
1723 EXPLAIN
1724 {
1725 "query_block": {
1726 "select_id": 1,
1727 "cost": "COST_REPLACED",
1728 "window_functions_computation": {
1729 "sorts": [
1730 {
1731 "filesort": {
1732 "sort_key": "t1.c, t1.a"
1733 }
1734 }
1735 ],
1736 "temporary_table": {
1737 "nested_loop": [
1738 {
1739 "table": {
1740 "table_name": "t1",
1741 "access_type": "ALL",
1742 "loops": 1,
1743 "rows": 3,
1744 "cost": "COST_REPLACED",
1745 "filtered": 100
1746 }
1747 }
1748 ]
1749 }
1750 }
1751 }
1752 }
1753 explain format=json
1754 select
1755 rank() over (order by a),
1756 row_number() over (order by a)
1757 from t1;
1758 EXPLAIN
1759 {
1760 "query_block": {
1761 "select_id": 1,
1762 "cost": "COST_REPLACED",
1763 "window_functions_computation": {
1764 "sorts": [
1765 {
1766 "filesort": {
1767 "sort_key": "t1.a"
1768 }
1769 }
1770 ],
1771 "temporary_table": {
1772 "nested_loop": [
1773 {
1774 "table": {
1775 "table_name": "t1",
1776 "access_type": "ALL",
1777 "loops": 1,
1778 "rows": 3,
1779 "cost": "COST_REPLACED",
1780 "filtered": 100
1781 }
1782 }
1783 ]
1784 }
1785 }
1786 }
1787 }
1788 explain format=json
1789 select
1790 rank() over (partition by c order by a) as rank_a,
1791 count(*) over (partition by c) as count_c
1792 from t1;
1793 EXPLAIN
1794 {
1795 "query_block": {
1796 "select_id": 1,
1797 "cost": "COST_REPLACED",
1798 "window_functions_computation": {
1799 "sorts": [
1800 {
1801 "filesort": {
1802 "sort_key": "t1.c, t1.a"
1803 }
1804 }
1805 ],
1806 "temporary_table": {
1807 "nested_loop": [
1808 {
1809 "table": {
1810 "table_name": "t1",
1811 "access_type": "ALL",
1812 "loops": 1,
1813 "rows": 3,
1814 "cost": "COST_REPLACED",
1815 "filtered": 100
1816 }
1817 }
1818 ]
1819 }
1820 }
1821 }
1822 }
1823 explain format=json
1824 select
1825 count(*) over (partition by c) as count_c,
1826 rank() over (partition by c order by a) as rank_a
1827 from t1;
1828 EXPLAIN
1829 {
1830 "query_block": {
1831 "select_id": 1,
1832 "cost": "COST_REPLACED",
1833 "window_functions_computation": {
1834 "sorts": [
1835 {
1836 "filesort": {
1837 "sort_key": "t1.c, t1.a"
1838 }
1839 }
1840 ],
1841 "temporary_table": {
1842 "nested_loop": [
1843 {
1844 "table": {
1845 "table_name": "t1",
1846 "access_type": "ALL",
1847 "loops": 1,
1848 "rows": 3,
1849 "cost": "COST_REPLACED",
1850 "filtered": 100
1851 }
1852 }
1853 ]
1854 }
1855 }
1856 }
1857 }
1858 drop table t1;
1859 #
1860 # MDEV-9847: Window functions: crash with big_tables=1
1861 #
1862 create table t1(a int);
1863 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1864 set tmp_memory_table_size=0;
1865 select rank() over (order by a) from t1;
1866 rank() over (order by a)
1867 1
1868 2
1869 3
1870 4
1871 5
1872 6
1873 7
1874 8
1875 9
1876 10
1877 set tmp_memory_table_size=default;
1878 drop table t1;
1879 #
1880 # Check if "ORDER BY window_func" works
1881 #
1882 create table t1 (s1 int, s2 char(5));
1883 insert into t1 values (1,'a');
1884 insert into t1 values (null,null);
1885 insert into t1 values (1,null);
1886 insert into t1 values (null,'a');
1887 insert into t1 values (2,'b');
1888 insert into t1 values (-1,'');
1889 explain format=json
1890 select *, row_number() over (order by s1, s2) as X from t1 order by X desc;
1891 EXPLAIN
1892 {
1893 "query_block": {
1894 "select_id": 1,
1895 "cost": "COST_REPLACED",
1896 "filesort": {
1897 "sort_key": "row_number() over ( order by t1.s1,t1.s2) desc",
1898 "window_functions_computation": {
1899 "sorts": [
1900 {
1901 "filesort": {
1902 "sort_key": "t1.s1, t1.s2"
1903 }
1904 }
1905 ],
1906 "temporary_table": {
1907 "nested_loop": [
1908 {
1909 "table": {
1910 "table_name": "t1",
1911 "access_type": "ALL",
1912 "loops": 1,
1913 "rows": 6,
1914 "cost": "COST_REPLACED",
1915 "filtered": 100
1916 }
1917 }
1918 ]
1919 }
1920 }
1921 }
1922 }
1923 }
1924 select *, row_number() over (order by s1, s2) as X from t1 order by X desc;
1925 s1 s2 X
1926 2 b 6
1927 1 a 5
1928 1 NULL 4
1929 -1 3
1930 NULL a 2
1931 NULL NULL 1
1932 drop table t1;
1933 #
1934 # Try window functions that are not directly present in the select list
1935 #
1936 create table t1 (a int, b int);
1937 insert into t1 values
1938 (1,3),
1939 (2,2),
1940 (3,1);
1941 select
1942 a, b,
1943 rank() over (order by a), rank() over (order by b),
1944 rank() over (order by a) - rank() over (order by b) as diff
1945 from
1946 t1;
1947 a b rank() over (order by a) rank() over (order by b) diff
1948 1 3 1 3 -2
1949 2 2 2 2 0
1950 3 1 3 1 2
1951 drop table t1;
1952 create table t1 (i int);
1953 insert into t1 values (1),(2);
1954 SELECT MAX(i) OVER (PARTITION BY (i)) FROM t1;
1955 MAX(i) OVER (PARTITION BY (i))
1956 1
1957 2
1958 drop table t1;
1959 #
1960 # Check the 0 in ROWS 0 PRECEDING
1961 #
1962 create table t1 (
1963 part_id int,
1964 pk int,
1965 a int
1966 );
1967 insert into t1 values (1, 1, 1);
1968 insert into t1 values (1, 2, 2);
1969 insert into t1 values (1, 3, 4);
1970 insert into t1 values (1, 4, 8);
1971 select
1972 pk, a,
1973 sum(a) over (order by pk rows between 0 preceding and current row) as sum
1974 from t1;
1975 pk a sum
1976 1 1 1
1977 2 2 2
1978 3 4 4
1979 4 8 8
1980 select
1981 pk, a,
1982 sum(a) over (order by pk rows between 1 preceding and 0 preceding) as sum
1983 from t1;
1984 pk a sum
1985 1 1 1
1986 2 2 3
1987 3 4 6
1988 4 8 12
1989 insert into t1 values (200, 1, 1);
1990 insert into t1 values (200, 2, 2);
1991 insert into t1 values (200, 3, 4);
1992 insert into t1 values (200, 4, 8);
1993 select
1994 part_id, pk, a,
1995 sum(a) over (partition by part_id order by pk rows between 0 preceding and current row) as sum
1996 from t1;
1997 part_id pk a sum
1998 1 1 1 1
1999 1 2 2 2
2000 1 3 4 4
2001 1 4 8 8
2002 200 1 1 1
2003 200 2 2 2
2004 200 3 4 4
2005 200 4 8 8
2006 select
2007 part_id, pk, a,
2008 sum(a) over (partition by part_id order by pk rows between 1 preceding and 0 preceding) as sum
2009 from t1;
2010 part_id pk a sum
2011 1 1 1 1
2012 1 2 2 3
2013 1 3 4 6
2014 1 4 8 12
2015 200 1 1 1
2016 200 2 2 3
2017 200 3 4 6
2018 200 4 8 12
2019 drop table t1;
2020 #
2021 # MDEV-9780, The "DISTINCT must not bet converted into GROUP BY when
2022 # window functions are present" part
2023 #
2024 create table t1 (part_id int, a int);
2025 insert into t1 values
2026 (100, 1),
2027 (100, 2),
2028 (100, 2),
2029 (100, 3),
2030 (2000, 1),
2031 (2000, 2),
2032 (2000, 3),
2033 (2000, 3),
2034 (2000, 3);
2035 select rank() over (partition by part_id order by a) as rank from t1;
2036 rank
2037 1
2038 2
2039 2
2040 4
2041 1
2042 2
2043 3
2044 3
2045 3
2046 select distinct rank() over (partition by part_id order by a) as rank from t1;
2047 rank
2048 1
2049 2
2050 4
2051 3
2052 explain format=json
2053 select distinct rank() over (partition by part_id order by a) as rank from t1;
2054 EXPLAIN
2055 {
2056 "query_block": {
2057 "select_id": 1,
2058 "cost": "COST_REPLACED",
2059 "duplicate_removal": {
2060 "window_functions_computation": {
2061 "sorts": [
2062 {
2063 "filesort": {
2064 "sort_key": "t1.part_id, t1.a"
2065 }
2066 }
2067 ],
2068 "temporary_table": {
2069 "nested_loop": [
2070 {
2071 "table": {
2072 "table_name": "t1",
2073 "access_type": "ALL",
2074 "loops": 1,
2075 "rows": 9,
2076 "cost": "COST_REPLACED",
2077 "filtered": 100
2078 }
2079 }
2080 ]
2081 }
2082 }
2083 }
2084 }
2085 }
2086 drop table t1;
2087 #
2088 # MDEV-9893: Window functions with different ORDER BY lists,
2089 # one of these lists containing an expression
2090 #
2091 create table t1 (s1 int, s2 char(5));
2092 insert into t1 values (1,'a');
2093 insert into t1 values (null,null);
2094 insert into t1 values (3,null);
2095 insert into t1 values (4,'a');
2096 insert into t1 values (2,'b');
2097 insert into t1 values (-1,'');
2098 select
2099 *,
2100 ROW_NUMBER() OVER (order by s1),
2101 CUME_DIST() OVER (order by -s1)
2102 from t1;
2103 s1 s2 ROW_NUMBER() OVER (order by s1) CUME_DIST() OVER (order by -s1)
2104 -1 2 1.0000000000
2105 1 a 3 0.8333333333
2106 2 b 4 0.6666666667
2107 3 NULL 5 0.5000000000
2108 4 a 6 0.3333333333
2109 NULL NULL 1 0.1666666667
2110 drop table t1;
2111 #
2112 # MDEV-9925: Wrong result with aggregate function as a window function
2113 #
2114 create table t1 (i int);
2115 insert into t1 values (1),(2);
2116 select i, sum(i) over (partition by i) from t1;
2117 i sum(i) over (partition by i)
2118 1 1
2119 2 2
2120 drop table t1;
2121 #
2122 # MDEV-9922: Assertion `!join->only_const_tables() && fsort' failed in int create_sort_index
2123 #
2124 create view v1 as select 1 as i;
2125 select rank() over (order by i) from v1;
2126 rank() over (order by i)
2127 1
2128 drop view v1;
2129 #
2130 # MDEV-10097: Assertion `count > 0' failed in Item_sum_sum::add_helper(bool)
2131 #
2132 CREATE TABLE `orders` (
2133 `o_orderkey` int(11) NOT NULL,
2134 `o_custkey` int(11) DEFAULT NULL,
2135 PRIMARY KEY (`o_orderkey`)
2136 ) DEFAULT CHARSET=latin1;
2137 INSERT INTO `orders` VALUES (59908,242);
2138 INSERT INTO `orders` VALUES (59940,238);
2139 SELECT o_custkey, avg(o_custkey) OVER (PARTITION BY abs(o_custkey)
2140 ORDER BY o_custkey
2141 RANGE BETWEEN 15 FOLLOWING
2142 AND 15 FOLLOWING) as avg from orders;
2143 o_custkey avg
2144 238 NULL
2145 242 NULL
2146 DROP table orders;
2147 #
2148 # MDEV-10842: window functions with the same order column
2149 # but different directions
2150 #
2151 create table t1 (
2152 pk int primary key,
2153 a int,
2154 b int,
2155 c char(10)
2156 );
2157 insert into t1 values
2158 ( 1, 0, 1, 'one'),
2159 ( 2, 0, 2, 'two'),
2160 ( 3, 0, 3, 'three'),
2161 ( 4, 1, 1, 'one'),
2162 ( 5, 1, 1, 'two'),
2163 ( 6, 1, 2, 'three'),
2164 ( 7, 2, NULL, 'n_one'),
2165 ( 8, 2, 1, 'n_two'),
2166 ( 9, 2, 2, 'n_three'),
2167 (10, 2, 0, 'n_four'),
2168 (11, 2, 10, NULL);
2169 select pk,
2170 row_number() over (order by pk desc) as r_desc,
2171 row_number() over (order by pk asc) as r_asc
2172 from t1;
2173 pk r_desc r_asc
2174 11 1 11
2175 10 2 10
2176 9 3 9
2177 8 4 8
2178 7 5 7
2179 6 6 6
2180 5 7 5
2181 4 8 4
2182 3 9 3
2183 2 10 2
2184 1 11 1
2185 drop table t1;
2186 #
2187 # MDEV-10874: two window functions with compatible sorting
2188 #
2189 create table t1 (
2190 pk int primary key,
2191 a int,
2192 b int,
2193 c char(10),
2194 d decimal(10, 3),
2195 e real
2196 );
2197 insert into t1 values
2198 ( 1, 0, 1, 'one', 0.1, 0.001),
2199 ( 2, 0, 2, 'two', 0.2, 0.002),
2200 ( 3, 0, 3, 'three', 0.3, 0.003),
2201 ( 4, 1, 2, 'three', 0.4, 0.004),
2202 ( 5, 1, 1, 'two', 0.5, 0.005),
2203 ( 6, 1, 1, 'one', 0.6, 0.006),
2204 ( 7, 2, NULL, 'n_one', 0.5, 0.007),
2205 ( 8, 2, 1, 'n_two', NULL, 0.008),
2206 ( 9, 2, 2, NULL, 0.7, 0.009),
2207 (10, 2, 0, 'n_four', 0.8, 0.010),
2208 (11, 2, 10, NULL, 0.9, NULL);
2209 select pk, a, d,
2210 sum(d) over (partition by a order by pk
2211 ROWS between 1 preceding and current row) as sum_1,
2212 sum(d) over (order by a
2213 ROWS BETWEEN 1 preceding and 2 following) as sum_2
2214 from t1;
2215 pk a d sum_1 sum_2
2216 1 0 0.100 0.100 0.600
2217 2 0 0.200 0.300 1.000
2218 3 0 0.300 0.500 1.400
2219 4 1 0.400 0.400 1.800
2220 5 1 0.500 0.900 2.000
2221 6 1 0.600 1.100 1.600
2222 7 2 0.500 0.500 1.800
2223 8 2 NULL 0.500 2.000
2224 9 2 0.700 0.700 2.400
2225 10 2 0.800 1.500 2.400
2226 11 2 0.900 1.700 1.700
2227 explain format=json
2228 select pk, a, d,
2229 sum(d) over (partition by a order by pk
2230 ROWS between 1 preceding and current row) as sum_1,
2231 sum(d) over (order by a
2232 ROWS BETWEEN 1 preceding and 2 following) as sum_2
2233 from t1;
2234 EXPLAIN
2235 {
2236 "query_block": {
2237 "select_id": 1,
2238 "cost": "COST_REPLACED",
2239 "window_functions_computation": {
2240 "sorts": [
2241 {
2242 "filesort": {
2243 "sort_key": "t1.a, t1.pk"
2244 }
2245 }
2246 ],
2247 "temporary_table": {
2248 "nested_loop": [
2249 {
2250 "table": {
2251 "table_name": "t1",
2252 "access_type": "ALL",
2253 "loops": 1,
2254 "rows": 11,
2255 "cost": "COST_REPLACED",
2256 "filtered": 100
2257 }
2258 }
2259 ]
2260 }
2261 }
2262 }
2263 }
2264 select pk, a, d,
2265 sum(d) over (partition by a order by pk desc
2266 ROWS between 1 preceding and current row) as sum_1,
2267 sum(d) over (order by a
2268 ROWS BETWEEN 1 preceding and 2 following) as sum_2
2269 from t1;
2270 pk a d sum_1 sum_2
2271 3 0 0.300 0.300 0.600
2272 2 0 0.200 0.500 1.200
2273 1 0 0.100 0.300 1.400
2274 6 1 0.600 0.600 1.600
2275 5 1 0.500 1.100 2.400
2276 4 1 0.400 0.900 2.600
2277 11 2 0.900 0.900 2.800
2278 10 2 0.800 1.700 2.400
2279 9 2 0.700 1.500 2.000
2280 8 2 NULL 0.700 1.200
2281 7 2 0.500 0.500 0.500
2282 drop table t1;
2283 #
2284 # MDEV-9941: two window functions with compatible partitions
2285 #
2286 create table t1 (
2287 a int,
2288 b int,
2289 c int
2290 );
2291 insert into t1 values
2292 (10, 1, 1),
2293 (10, 3, 10),
2294 (10, 1, 10),
2295 (10, 3, 100),
2296 (10, 5, 1000),
2297 (10, 1, 100);
2298 explain format=json
2299 select
2300 a,b,c,
2301 row_number() over (partition by a),
2302 row_number() over (partition by a, b)
2303 from t1;
2304 EXPLAIN
2305 {
2306 "query_block": {
2307 "select_id": 1,
2308 "cost": "COST_REPLACED",
2309 "window_functions_computation": {
2310 "sorts": [
2311 {
2312 "filesort": {
2313 "sort_key": "t1.a, t1.b"
2314 }
2315 }
2316 ],
2317 "temporary_table": {
2318 "nested_loop": [
2319 {
2320 "table": {
2321 "table_name": "t1",
2322 "access_type": "ALL",
2323 "loops": 1,
2324 "rows": 6,
2325 "cost": "COST_REPLACED",
2326 "filtered": 100
2327 }
2328 }
2329 ]
2330 }
2331 }
2332 }
2333 }
2334 drop table t1;
2335 #
2336 # MDEV-10815: Window Function Expressions Wrong Results
2337 #
2338 create table t(a decimal(35,10), b int);
2339 insert into t(a,b) values(1,1);
2340 insert into t(a,b) values(2,1);
2341 insert into t(a,b) values(0,1);
2342 insert into t(a,b) values(1, 2);
2343 insert into t(a,b) values(1.5,2);
2344 insert into t(a,b) values(3, 2);
2345 insert into t(a,b) values(4.5,2);
2346 select a, b,
2347 sum(t.a) over (partition by t.b order by a) as simple_sum,
2348 sum(t.a) over (partition by t.b order by a) + 1 as sum_and_const,
2349 sum(t.b) over (partition by t.b order by a) + sum(t.a) over (partition by t.b order by a) as sum_and_sum
2350 from t
2351 order by t.b, t.a;
2352 a b simple_sum sum_and_const sum_and_sum
2353 0.0000000000 1 0.0000000000 1.0000000000 1.0000000000
2354 1.0000000000 1 1.0000000000 2.0000000000 3.0000000000
2355 2.0000000000 1 3.0000000000 4.0000000000 6.0000000000
2356 1.0000000000 2 1.0000000000 2.0000000000 3.0000000000
2357 1.5000000000 2 2.5000000000 3.5000000000 6.5000000000
2358 3.0000000000 2 5.5000000000 6.5000000000 11.5000000000
2359 4.5000000000 2 10.0000000000 11.0000000000 18.0000000000
2360 drop table t;
2361 #
2362 # MDEV-10669: Crash in SELECT with window function used
2363 #
2364 create table t(a decimal(35,10), b int);
2365 insert into t(a,b) values(1,1);
2366 insert into t(a,b) values(2,1);
2367 insert into t(a,b) values(0,1);
2368 SELECT (CASE WHEN sum(t.a) over (partition by t.b)=0 THEN null ELSE null END) AS a FROM t;
2369 a
2370 NULL
2371 NULL
2372 NULL
2373 SELECT ifnull(((t.a) / CASE WHEN sum(t.a) over(partition by t.b) =0 then null else null end) ,0) as result from t;
2374 result
2375 0.00000000000000
2376 0.00000000000000
2377 0.00000000000000
2378 SELECT sum(t.a) over (partition by t.b order by a),
2379 sqrt(ifnull((sum(t.a) over (partition by t.b order by a)), 0)) as sum
2380 from t;
2381 sum(t.a) over (partition by t.b order by a) sum
2382 0.0000000000 0
2383 1.0000000000 1
2384 3.0000000000 1.7320508075688772
2385 drop table t;
2386 #
2387 # MDEV-10868: view definitions with window functions
2388 #
2389 create table t0 (a int);
2390 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2391 create table t1 (pk int, c int);
2392 insert into t1 select a+1,1 from t0;
2393 update t1 set c=2 where pk not in (1,2,3,4);
2394 select * from t1;
2395 pk c
2396 1 1
2397 2 1
2398 3 1
2399 4 1
2400 5 2
2401 6 2
2402 7 2
2403 8 2
2404 9 2
2405 10 2
2406 select pk, c, c/count(*) over (partition by c order by pk
2407 rows between 1 preceding and 2 following) as CNT
2408 from t1;
2409 pk c CNT
2410 1 1 0.3333
2411 2 1 0.2500
2412 3 1 0.3333
2413 4 1 0.5000
2414 5 2 0.6667
2415 6 2 0.5000
2416 7 2 0.5000
2417 8 2 0.5000
2418 9 2 0.6667
2419 10 2 1.0000
2420 create view v1 as select pk, c, c/count(*) over (partition by c order by pk
2421 rows between 1 preceding and 2 following) as CNT
2422 from t1;
2423 show create view v1;
2424 View Create View character_set_client collation_connection
2425 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` rows between 1 preceding and 2 following ) AS `CNT` from `t1` latin1 latin1_swedish_ci
2426 select * from v1;
2427 pk c CNT
2428 1 1 0.3333
2429 2 1 0.2500
2430 3 1 0.3333
2431 4 1 0.5000
2432 5 2 0.6667
2433 6 2 0.5000
2434 7 2 0.5000
2435 8 2 0.5000
2436 9 2 0.6667
2437 10 2 1.0000
2438 select pk, c, c/count(*) over w1 as CNT from t1
2439 window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
2440 pk c CNT
2441 1 1 0.3333
2442 2 1 0.2500
2443 3 1 0.3333
2444 4 1 0.5000
2445 5 2 0.6667
2446 6 2 0.5000
2447 7 2 0.5000
2448 8 2 0.5000
2449 9 2 0.6667
2450 10 2 1.0000
2451 create view v2 as select pk, c, c/count(*) over w1 as CNT from t1
2452 window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
2453 show create view v2;
2454 View Create View character_set_client collation_connection
2455 v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` rows between 1 preceding and 2 following ) AS `CNT` from `t1` latin1 latin1_swedish_ci
2456 select * from v2;
2457 pk c CNT
2458 1 1 0.3333
2459 2 1 0.2500
2460 3 1 0.3333
2461 4 1 0.5000
2462 5 2 0.6667
2463 6 2 0.5000
2464 7 2 0.5000
2465 8 2 0.5000
2466 9 2 0.6667
2467 10 2 1.0000
2468 select pk, c, c/count(*) over w1 as CNT from t1
2469 window w1 as (partition by c order by pk rows unbounded preceding);
2470 pk c CNT
2471 1 1 1.0000
2472 2 1 0.5000
2473 3 1 0.3333
2474 4 1 0.2500
2475 5 2 2.0000
2476 6 2 1.0000
2477 7 2 0.6667
2478 8 2 0.5000
2479 9 2 0.4000
2480 10 2 0.3333
2481 create view v3 as select pk, c, c/count(*) over w1 as CNT from t1
2482 window w1 as (partition by c order by pk rows unbounded preceding);
2483 show create view v3;
2484 View Create View character_set_client collation_connection
2485 v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` rows between unbounded preceding and current row ) AS `CNT` from `t1` latin1 latin1_swedish_ci
2486 select * from v3;
2487 pk c CNT
2488 1 1 1.0000
2489 2 1 0.5000
2490 3 1 0.3333
2491 4 1 0.2500
2492 5 2 2.0000
2493 6 2 1.0000
2494 7 2 0.6667
2495 8 2 0.5000
2496 9 2 0.4000
2497 10 2 0.3333
2498 select pk, c, c/count(*) over (partition by c order by pk
2499 range between 3 preceding and current row) as CNT
2500 from t1;
2501 pk c CNT
2502 1 1 1.0000
2503 2 1 0.5000
2504 3 1 0.3333
2505 4 1 0.2500
2506 5 2 2.0000
2507 6 2 1.0000
2508 7 2 0.6667
2509 8 2 0.5000
2510 9 2 0.5000
2511 10 2 0.5000
2512 create view v4 as select pk, c, c/count(*) over (partition by c order by pk
2513 range between 3 preceding and current row) as CNT
2514 from t1;
2515 show create view v4;
2516 View Create View character_set_client collation_connection
2517 v4 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v4` AS select `t1`.`pk` AS `pk`,`t1`.`c` AS `c`,`t1`.`c` / count(0) over ( partition by `t1`.`c` order by `t1`.`pk` range between 3 preceding and current row ) AS `CNT` from `t1` latin1 latin1_swedish_ci
2518 select * from v4;
2519 pk c CNT
2520 1 1 1.0000
2521 2 1 0.5000
2522 3 1 0.3333
2523 4 1 0.2500
2524 5 2 2.0000
2525 6 2 1.0000
2526 7 2 0.6667
2527 8 2 0.5000
2528 9 2 0.5000
2529 10 2 0.5000
2530 drop view v1,v2,v3,v4;
2531 drop table t0,t1;
2532 #
2533 # MDEV-10875: window function in subquery
2534 #
2535 CREATE TABLE t1 (i INT);
2536 INSERT INTO t1 VALUES (3),(1);
2537 CREATE TABLE t2 (c VARCHAR(8));
2538 INSERT INTO t2 VALUES ('foo'),('bar'),('foo');
2539 SELECT COUNT(*) OVER (PARTITION BY c) FROM t2;
2540 COUNT(*) OVER (PARTITION BY c)
2541 1
2542 2
2543 2
2544 SELECT * FROM t1 WHERE i IN ( SELECT COUNT(*) OVER (PARTITION BY c) FROM t2 );
2545 i
2546 1
2547 DROP TABLE t1, t2;
2548 #
2549 # MDEV-9976: window function without PARTITION BY and ORDER BY
2550 #
2551 CREATE TABLE t1 (id int, a int);
2552 INSERT INTO t1 VALUES
2553 (1,1000), (2,1100), (3,1800), (4,1500), (5,1700), (6,1200),
2554 (7,2000), (8,2100), (9,1600);
2555 SELECT id, sum(a) OVER (PARTITION BY id
2556 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as sum
2557 FROM t1;
2558 id sum
2559 1 1000
2560 2 1100
2561 3 1800
2562 4 1500
2563 5 1700
2564 6 1200
2565 7 2000
2566 8 2100
2567 9 1600
2568 SELECT id, sum(a) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as sum
2569 FROM t1;
2570 id sum
2571 1 14000
2572 2 13000
2573 3 5900
2574 4 10700
2575 5 7600
2576 6 11900
2577 7 4100
2578 8 2100
2579 9 9200
2580 DROP TABLE t1;
2581 #
2582 # MDEV-11867: window function with aggregation
2583 # over the result of grouping
2584 #
2585 create table t1 (
2586 username varchar(32),
2587 amount int
2588 );
2589 insert into t1 values
2590 ('user1',1),
2591 ('user1',5),
2592 ('user1',3),
2593 ('user2',10),
2594 ('user2',20),
2595 ('user2',30);
2596 select username, sum(amount) as s, avg(sum(amount)) over (order by s desc)
2597 from t1
2598 group by username;
2599 username s avg(sum(amount)) over (order by s desc)
2600 user1 9 34.5000
2601 user2 60 60.0000
2602 select username, sum(amount), avg(sum(amount)) over (order by sum(amount) desc)
2603 from t1
2604 group by username;
2605 username sum(amount) avg(sum(amount)) over (order by sum(amount) desc)
2606 user1 9 34.5000
2607 user2 60 60.0000
2608 drop table t1;
2609 #
2610 # MDEV-11594: window function over implicit grouping
2611 #
2612 create table t1 (id int);
2613 insert into t1 values (1), (2), (3), (2);
2614 select sum(id) over (order by sum(id)) from t1;
2615 sum(id) over (order by sum(id))
2616 1
2617 select sum(sum(id)) over (order by sum(id)) from t1;
2618 sum(sum(id)) over (order by sum(id))
2619 8
2620 drop table t1;
2621 #
2622 # MDEV-9923: integer constant in order by list
2623 # of window specification
2624 #
2625 create table t1 (id int);
2626 insert into t1 values (1), (2), (3), (2);
2627 select rank() over (order by 1) from t1;
2628 rank() over (order by 1)
2629 1
2630 1
2631 1
2632 1
2633 select rank() over (order by 2) from t1;
2634 rank() over (order by 2)
2635 1
2636 1
2637 1
2638 1
2639 select rank() over (partition by id order by 2) from t1;
2640 rank() over (partition by id order by 2)
2641 1
2642 1
2643 1
2644 1
2645 drop table t1;
2646 #
2647 # MDEV-10660: view using a simple window function
2648 #
2649 create table t1 (id int);
2650 insert into t1 values (1), (2), (3), (2);
2651 create view v1(id,rnk) as
2652 select id, rank() over (order by id) from t1;
2653 show create view v1;
2654 View Create View character_set_client collation_connection
2655 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`id` AS `id`,rank() over ( order by `t1`.`id`) AS `rnk` from `t1` latin1 latin1_swedish_ci
2656 select id, rank() over (order by id) from t1;
2657 id rank() over (order by id)
2658 1 1
2659 2 2
2660 2 2
2661 3 4
2662 select * from v1;
2663 id rnk
2664 1 1
2665 2 2
2666 2 2
2667 3 4
2668 drop view v1;
2669 drop table t1;
2670 #
2671 # MDEV-11138: window function in the query without tables
2672 #
2673 select row_number() over ();
2674 row_number() over ()
2675 1
2676 select count(*) over ();
2677 count(*) over ()
2678 1
2679 select sum(5) over ();
2680 sum(5) over ()
2681 5
2682 select row_number() over (), sum(5) over ();
2683 row_number() over () sum(5) over ()
2684 1 5
2685 select row_number() over (order by 2);
2686 row_number() over (order by 2)
2687 1
2688 select row_number() over (partition by 2);
2689 row_number() over (partition by 2)
2690 1
2691 select row_number() over (partition by 4 order by 1+2);
2692 row_number() over (partition by 4 order by 1+2)
2693 1
2694 #
2695 # MDEV-11999: execution of prepared statement for
2696 # tableless query with window functions
2697 #
2698 prepare stmt from
2699 "select row_number() over (partition by 4 order by 1+2)";
2700 execute stmt;
2701 row_number() over (partition by 4 order by 1+2)
2702 1
2703 execute stmt;
2704 row_number() over (partition by 4 order by 1+2)
2705 1
2706 deallocate prepare stmt;
2707 #
2708 # MDEV-11745: window function with min/max
2709 #
2710 create table t1 (i int, b int);
2711 insert into t1 values
2712 (1,1),(2,1),(3,1),(4,4),(5,4),(6,4),(7,8),(8,8),(9,8),(10,8);
2713 select b, min(i) over (partition by b) as f
2714 from t1 as tt
2715 order by i;
2716 b f
2717 1 1
2718 1 1
2719 1 1
2720 4 4
2721 4 4
2722 4 4
2723 8 7
2724 8 7
2725 8 7
2726 8 7
2727 select b, min(i) over (partition by b) as f
2728 from (select * from t1) as tt
2729 order by i;
2730 b f
2731 1 1
2732 1 1
2733 1 1
2734 4 4
2735 4 4
2736 4 4
2737 8 7
2738 8 7
2739 8 7
2740 8 7
2741 select b, min(i+10) over (partition by b) as f
2742 from t1 as tt
2743 order by i;
2744 b f
2745 1 11
2746 1 11
2747 1 11
2748 4 14
2749 4 14
2750 4 14
2751 8 17
2752 8 17
2753 8 17
2754 8 17
2755 select b, min(i) over (partition by b) as f
2756 from (select i+10 as i, b from t1) as tt
2757 order by i;
2758 b f
2759 1 11
2760 1 11
2761 1 11
2762 4 14
2763 4 14
2764 4 14
2765 8 17
2766 8 17
2767 8 17
2768 8 17
2769 select b, min(i+20) over (partition by b) as f
2770 from (select i+10 as i, b from t1) as tt
2771 order by i;
2772 b f
2773 1 31
2774 1 31
2775 1 31
2776 4 34
2777 4 34
2778 4 34
2779 8 37
2780 8 37
2781 8 37
2782 8 37
2783 select b, max(i) over (partition by b) as f
2784 from t1 as tt
2785 order by i;
2786 b f
2787 1 3
2788 1 3
2789 1 3
2790 4 6
2791 4 6
2792 4 6
2793 8 10
2794 8 10
2795 8 10
2796 8 10
2797 select b, max(i) over (partition by b) as f
2798 from (select * from t1) as tt
2799 order by i;
2800 b f
2801 1 3
2802 1 3
2803 1 3
2804 4 6
2805 4 6
2806 4 6
2807 8 10
2808 8 10
2809 8 10
2810 8 10
2811 select b, max(i+10) over (partition by b) as f
2812 from t1 as tt
2813 order by i;
2814 b f
2815 1 13
2816 1 13
2817 1 13
2818 4 16
2819 4 16
2820 4 16
2821 8 20
2822 8 20
2823 8 20
2824 8 20
2825 select b, max(i) over (partition by b) as f
2826 from (select i+10 as i, b from t1) as tt
2827 order by i;
2828 b f
2829 1 13
2830 1 13
2831 1 13
2832 4 16
2833 4 16
2834 4 16
2835 8 20
2836 8 20
2837 8 20
2838 8 20
2839 select b, max(i+20) over (partition by b) as f
2840 from (select i+10 as i, b from t1) as tt
2841 order by i;
2842 b f
2843 1 33
2844 1 33
2845 1 33
2846 4 36
2847 4 36
2848 4 36
2849 8 40
2850 8 40
2851 8 40
2852 8 40
2853 select max(i), max(i), sum(i), count(i)
2854 from t1 as tt
2855 group by b;
2856 max(i) max(i) sum(i) count(i)
2857 3 3 6 3
2858 6 6 15 3
2859 10 10 34 4
2860 select max(i), min(sum(i)) over (partition by count(i)) f
2861 from t1 as tt
2862 group by b;
2863 max(i) f
2864 3 6
2865 6 6
2866 10 34
2867 select max(i), min(sum(i)) over (partition by count(i)) f
2868 from (select * from t1) as tt
2869 group by b;
2870 max(i) f
2871 3 6
2872 6 6
2873 10 34
2874 select max(i+10), min(sum(i)+10) over (partition by count(i)) f
2875 from t1 as tt
2876 group by b;
2877 max(i+10) f
2878 13 16
2879 16 16
2880 20 44
2881 select max(i), max(i), sum(i), count(i)
2882 from (select i+10 as i, b from t1) as tt
2883 group by b;
2884 max(i) max(i) sum(i) count(i)
2885 13 13 36 3
2886 16 16 45 3
2887 20 20 74 4
2888 select max(i), min(sum(i)) over (partition by count(i)) f
2889 from (select i+10 as i, b from t1) as tt
2890 group by b;
2891 max(i) f
2892 13 36
2893 16 36
2894 20 74
2895 select max(i), min(i), min(max(i)-min(i)) over (partition by count(i)) f
2896 from (select i+10 as i, b from t1) as tt
2897 group by b;
2898 max(i) min(i) f
2899 13 11 2
2900 16 14 2
2901 20 17 3
2902 drop table t1;
2903 #
2904 # MDEV-12015: window function over select with WHERE
2905 # that is always FALSE
2906 #
2907 CREATE TABLE t1 (i INT);
2908 INSERT INTO t1 VALUES (3), (1), (2);
2909 SELECT i, ROW_NUMBER() OVER () FROM t1 WHERE 1 = 2;
2910 i ROW_NUMBER() OVER ()
2911 SELECT i, COUNT(*) OVER () FROM t1 WHERE 1 = 2;
2912 i COUNT(*) OVER ()
2913 DROP TABLE t1;
2914 #
2915 # MDEV-12051: window function in query with implicit grouping
2916 # on always empty set
2917 #
2918 create table t1 (a int, b varchar(8));
2919 insert into t1 values (1,'foo'),(2,'bar');
2920 select max(a), row_number() over () from t1 where a > 10;
2921 max(a) row_number() over ()
2922 NULL 1
2923 select max(a), sum(max(a)) over () from t1 where a > 10;
2924 max(a) sum(max(a)) over ()
2925 NULL NULL
2926 select max(a), sum(max(a)) over (partition by max(a)) from t1 where a > 10;
2927 max(a) sum(max(a)) over (partition by max(a))
2928 NULL NULL
2929 select max(a), row_number() over () from t1 where 1 = 2;
2930 max(a) row_number() over ()
2931 NULL 1
2932 select max(a), sum(max(a)) over () from t1 where 1 = 2;
2933 max(a) sum(max(a)) over ()
2934 NULL NULL
2935 select max(a), sum(max(a)) over (partition by max(a)) from t1 where 1 = 2;
2936 max(a) sum(max(a)) over (partition by max(a))
2937 NULL NULL
2938 select max(a), row_number() over () from t1 where 1 = 2
2939 having max(a) is not null;
2940 max(a) row_number() over ()
2941 select max(a), sum(max(a)) over () from t1 where 1 = 2
2942 having max(a) is not null;
2943 max(a) sum(max(a)) over ()
2944 drop table t1;
2945 #
2946 # MDEV-10885: window function in query with implicit grouping
2947 # with constant condition evaluated to false
2948 #
2949 CREATE TABLE t1 (a INT, b VARCHAR(8));
2950 INSERT INTO t1 VALUES (1,'foo'),(2,'bar');
2951 CREATE TABLE t2 (c INT);
2952 INSERT INTO t2 VALUES (3),(4);
2953 CREATE TABLE t3 (d INT);
2954 INSERT INTO t3 VALUES (5),(6);
2955 SELECT MAX(a), ROW_NUMBER() OVER (PARTITION BY MAX(a)) FROM t1
2956 WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
2957 MAX(a) ROW_NUMBER() OVER (PARTITION BY MAX(a))
2958 NULL 1
2959 SELECT MAX(a), COUNT(MAX(a)) OVER (PARTITION BY MAX(a)) FROM t1
2960 WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
2961 MAX(a) COUNT(MAX(a)) OVER (PARTITION BY MAX(a))
2962 NULL 0
2963 SELECT MAX(a), SUM(MAX(a)) OVER (PARTITION BY MAX(a)) FROM t1
2964 WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
2965 MAX(a) SUM(MAX(a)) OVER (PARTITION BY MAX(a))
2966 NULL NULL
2967 SELECT MAX(a), ROW_NUMBER() OVER (PARTITION BY MAX(a)) FROM t1
2968 WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) )
2969 HAVING MAX(a) IS NOT NULL;
2970 MAX(a) ROW_NUMBER() OVER (PARTITION BY MAX(a))
2971 SELECT a, MAX(a), ROW_NUMBER() OVER (PARTITION BY b) FROM t1
2972 WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
2973 a MAX(a) ROW_NUMBER() OVER (PARTITION BY b)
2974 NULL NULL 1
2975 SELECT a, COUNT(a), AVG(a) OVER (PARTITION BY b) FROM t1
2976 WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
2977 a COUNT(a) AVG(a) OVER (PARTITION BY b)
2978 NULL 0 NULL
2979 SELECT a, MAX(a), AVG(a) OVER (PARTITION BY b) FROM t1
2980 WHERE EXISTS ( SELECT * FROM t2 WHERE c IN ( SELECT MAX(d) FROM t3 ) );
2981 a MAX(a) AVG(a) OVER (PARTITION BY b)
2982 NULL NULL NULL
2983 DROP TABLE t1,t2,t3;
2984 #
2985 # MDEV-10859: Wrong result of aggregate window function in query
2986 # with HAVING and no ORDER BY
2987 #
2988 create table empsalary (depname varchar(32), empno smallint primary key, salary int);
2989 insert into empsalary values
2990 ('develop', 1, 5000), ('develop', 2, 4000),('sales', 3, '6000'),('sales', 4, 5000);
2991 SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
2992 depname empno salary avg(salary) OVER (PARTITION BY depname)
2993 develop 1 5000 4500.0000
2994 develop 2 4000 4500.0000
2995 sales 3 6000 5500.0000
2996 sales 4 5000 5500.0000
2997 SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary ORDER BY depname;
2998 depname empno salary avg(salary) OVER (PARTITION BY depname)
2999 develop 1 5000 4500.0000
3000 develop 2 4000 4500.0000
3001 sales 3 6000 5500.0000
3002 sales 4 5000 5500.0000
3003 #
3004 # These last 2 should have the same row results, ignoring order.
3005 #
3006 SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1;
3007 depname empno salary avg(salary) OVER (PARTITION BY depname)
3008 develop 2 4000 4000.0000
3009 sales 3 6000 5500.0000
3010 sales 4 5000 5500.0000
3011 SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary HAVING empno > 1 ORDER BY depname;
3012 depname empno salary avg(salary) OVER (PARTITION BY depname)
3013 develop 2 4000 4000.0000
3014 sales 3 6000 5500.0000
3015 sales 4 5000 5500.0000
3016 drop table empsalary;
3017 #
3018 # MDEV-11868: min(distinct) over () returns wrong value
3019 #
3020 create table TDEC (CDEC int, RNUM int);
3021 create view VDEC as select * from TDEC;
3022 insert into TDEC (CDEC) values (null),(-1),(0),(1),(0),(10);
3023 select TDEC.CDEC, min(TDEC.CDEC) over () from TDEC;
3024 CDEC min(TDEC.CDEC) over ()
3025 NULL -1
3026 -1 -1
3027 0 -1
3028 0 -1
3029 1 -1
3030 10 -1
3031 select VDEC.CDEC, min(VDEC.CDEC) over () from VDEC;
3032 CDEC min(VDEC.CDEC) over ()
3033 NULL -1
3034 -1 -1
3035 0 -1
3036 0 -1
3037 1 -1
3038 10 -1
3039 select TDEC.CDEC, max(TDEC.CDEC) over () from TDEC;
3040 CDEC max(TDEC.CDEC) over ()
3041 NULL 10
3042 -1 10
3043 0 10
3044 0 10
3045 1 10
3046 10 10
3047 select VDEC.CDEC, max(VDEC.CDEC) over () from VDEC;
3048 CDEC max(VDEC.CDEC) over ()
3049 NULL 10
3050 -1 10
3051 0 10
3052 0 10
3053 1 10
3054 10 10
3055 select TDEC.CDEC, min(distinct TDEC.CDEC) over () from TDEC;
3056 CDEC min(distinct TDEC.CDEC) over ()
3057 NULL -1
3058 -1 -1
3059 0 -1
3060 0 -1
3061 1 -1
3062 10 -1
3063 select VDEC.CDEC, min(distinct VDEC.CDEC) over () from VDEC;
3064 CDEC min(distinct VDEC.CDEC) over ()
3065 NULL -1
3066 -1 -1
3067 0 -1
3068 0 -1
3069 1 -1
3070 10 -1
3071 select TDEC.CDEC, max(distinct TDEC.CDEC) over () from TDEC;
3072 CDEC max(distinct TDEC.CDEC) over ()
3073 NULL 10
3074 -1 10
3075 0 10
3076 0 10
3077 1 10
3078 10 10
3079 select VDEC.CDEC, max(distinct VDEC.CDEC) over () from VDEC;
3080 CDEC max(distinct VDEC.CDEC) over ()
3081 NULL 10
3082 -1 10
3083 0 10
3084 0 10
3085 1 10
3086 10 10
3087 #
3088 # These should be removed once support for them is added.
3089 #
3090 select TDEC.CDEC, count(distinct TDEC.CDEC) over () from TDEC;
3091 ERROR 42000: This version of MariaDB doesn't yet support 'COUNT(DISTINCT) aggregate as window function'
3092 select VDEC.CDEC, count(distinct VDEC.CDEC) over () from VDEC;
3093 ERROR 42000: This version of MariaDB doesn't yet support 'COUNT(DISTINCT) aggregate as window function'
3094 select TDEC.CDEC, sum(distinct TDEC.CDEC) over () from TDEC;
3095 ERROR 42000: This version of MariaDB doesn't yet support 'SUM(DISTINCT) aggregate as window function'
3096 select VDEC.CDEC, sum(distinct VDEC.CDEC) over () from VDEC;
3097 ERROR 42000: This version of MariaDB doesn't yet support 'SUM(DISTINCT) aggregate as window function'
3098 select TDEC.CDEC, avg(distinct TDEC.CDEC) over () from TDEC;
3099 ERROR 42000: This version of MariaDB doesn't yet support 'AVG(DISTINCT) aggregate as window function'
3100 select VDEC.CDEC, avg(distinct VDEC.CDEC) over () from VDEC;
3101 ERROR 42000: This version of MariaDB doesn't yet support 'AVG(DISTINCT) aggregate as window function'
3102 select TDEC.CDEC, GROUP_CONCAT(TDEC.CDEC) over () from TDEC;
3103 ERROR 42000: This version of MariaDB doesn't yet support 'GROUP_CONCAT() aggregate as window function'
3104 select VDEC.CDEC, GROUP_CONCAT(distinct VDEC.CDEC) over () from VDEC;
3105 ERROR 42000: This version of MariaDB doesn't yet support 'GROUP_CONCAT() aggregate as window function'
3106 drop table TDEC;
3107 drop view VDEC;
3108 #
3109 # MDEV-10700: 10.2.2 windowing function returns incorrect result
3110 #
3111 create table t(a int,b int, c int , d int);
3112 insert into t(a,b,c,d) values(1, rand(10)*1000, rand(10)*1000, rand(10)*1000);
3113 insert into t(a,b,c,d) values(1, rand(10)*1000, rand(10)*1000, rand(10)*1000);
3114 replace into t(a,b,c,d) select 1, rand(10)*1000, rand(10)*1000, rand(10)*1000 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14, t t15, t t16, t t17;
3115 select count(distinct s) from (select sum(d) over(partition by a,b,c) as s from t) Z where s > 0;
3116 count(distinct s)
3117 993
3118 select count(distinct s) from (select sum(d) as s from t group by a,b,c) Z where s > 0;
3119 count(distinct s)
3120 993
3121 select count(distinct s) from (select sum(d) over(partition by a,b) as s from t) Z where s > 0;
3122 count(distinct s)
3123 993
3124 select count(distinct s) from (select sum(d) as s from t group by a,b) Z where s > 0;
3125 count(distinct s)
3126 993
3127 select count(distinct s) from (select sum(d) over(partition by a) as s from t) Z where s > 0;
3128 count(distinct s)
3129 1
3130 select count(distinct s) from (select sum(d) as s from t group by a) Z where s > 0;
3131 count(distinct s)
3132 1
3133 drop table t;
3134 #
3135 # MDEV-9924: window function in query with group by optimized away
3136 #
3137 create table t1 (i int);
3138 insert into t1 values (2),(3),(1);
3139 select row_number() over () from t1 group by 1+2;
3140 row_number() over ()
3141 1
3142 select max(i), row_number() over () from t1 group by 1+2;
3143 max(i) row_number() over ()
3144 3 1
3145 select rank() over (order by max(i)) from t1 group by 1+2;
3146 rank() over (order by max(i))
3147 1
3148 select i, row_number() over () from t1 group by 1+2;
3149 i row_number() over ()
3150 2 1
3151 select i, rank() over (order by i) rnk from t1 group by 1+2;
3152 i rnk
3153 2 1
3154 drop table t1;
3155 #
3156 # MDEV-11907: window function as the second operand of division
3157 #
3158 create table t1 (pk int, c int);
3159 insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2);
3160 set @sql_mode_save= @@sql_mode;
3161 set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
3162 select pk, c, c/count(*) over
3163 (partition by c order by pk
3164 rows between 1 preceding and 2 following) as CNT
3165 from t1;
3166 pk c CNT
3167 1 1 0.3333
3168 2 1 0.2500
3169 3 1 0.3333
3170 4 1 0.5000
3171 5 2 2.0000
3172 show warnings;
3173 Level Code Message
3174 set sql_mode=@sql_mode_save;
3175 drop table t1;
3176 #
3177 # MDEV-12336: several functions over a window function
3178 #
3179 create table t1 (name varchar(10), cnt int);
3180 insert into t1 values ('Fred', 23), ('Fred', 35), ('Joe', 10);
3181 select q.name, q.row_cnt,
3182 round( 100 * ( q.row_cnt /
3183 sum(q.row_cnt) over
3184 (
3185 order by q.name
3186 rows between
3187 unbounded preceding and
3188 unbounded following
3189 )
3190 ),2
3191 ) pct_of_total
3192 from
3193 (
3194 select name, count(*) row_cnt, sum(cnt) sum_cnt
3195 from t1
3196 group by 1
3197 ) q;
3198 name row_cnt pct_of_total
3199 Fred 2 66.67
3200 Joe 1 33.33
3201 drop table t1;
3202 #
3203 # MDEV-11990: window function over min/max aggregation
3204 #
3205 create table t1 (id int);
3206 insert into t1 values (1), (2), (3), (2), (4), (2);
3207 select sum(max(id)) over (order by max(id)) from t1;
3208 sum(max(id)) over (order by max(id))
3209 4
3210 explain
3211 select sum(max(id)) over (order by max(id)) from t1;
3212 id select_type table type possible_keys key key_len ref rows Extra
3213 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary
3214 create index idx on t1(id);
3215 select sum(max(id)) over (order by max(id)) from t1;
3216 sum(max(id)) over (order by max(id))
3217 4
3218 explain
3219 select sum(max(id)) over (order by max(id)) from t1;
3220 id select_type table type possible_keys key key_len ref rows Extra
3221 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
3222 select sum(max(id)) over (order by max(id)) from t1 where id < 3;
3223 sum(max(id)) over (order by max(id))
3224 2
3225 select count(max(id)) over (order by max(id)) from t1 where id < 3;
3226 count(max(id)) over (order by max(id))
3227 1
3228 select max(id), rank() over (order by max(id)) from t1 where id < 3;
3229 max(id) rank() over (order by max(id))
3230 2 1
3231 drop table t1;
3232 #
3233 # main.win failure post MDEV-12336
3234 #
3235 create table t(a decimal(35,10), b int);
3236 insert into t values (1, 10), (2, 20), (3, 30);
3237 prepare stmt from "SELECT (CASE WHEN sum(t.a) over (partition by t.b)=1 THEN 1000 ELSE 300 END) AS a FROM t";
3238 execute stmt;
3239 a
3240 1000
3241 300
3242 300
3243 drop table t;
3244 #
3245 # MDEV-12851 case with window functions query crashes server
3246 #
3247 create table t1(dt datetime);
3248 insert into t1 values ('2017-05-17'), ('2017-05-18');
3249 select dt,
3250 case when (max(dt) over (order by dt rows between 1 following and 1 following) is null)
3251 then '9999-12-31 12:00:00'
3252 else max(dt) over (order by dt rows between 1 following and 1 following)
3253 end x,
3254 case when (max(dt) over (order by dt rows between 1 following and 1 following) is not null)
3255 then '9999-12-31 12:00:00'
3256 else max(dt) over (order by dt rows between 1 following and 1 following)
3257 end x
3258 from t1;
3259 dt x x
3260 2017-05-17 00:00:00 2017-05-18 00:00:00 9999-12-31 12:00:00
3261 2017-05-18 00:00:00 9999-12-31 12:00:00 NULL
3262 drop table t1;
3263 create table t1(i int);
3264 insert into t1 values (null),(1),(2);
3265 select max(i) over (order by i),
3266 max(i) over (order by i) is null,
3267 max(i) over (order by i) is not null
3268 from t1;
3269 max(i) over (order by i) max(i) over (order by i) is null max(i) over (order by i) is not null
3270 NULL 1 0
3271 1 0 1
3272 2 0 1
3273 drop table t1;
3274 #
3275 # MDEV-13189: Window functions crash when using INTERVAL function
3276 #
3277 create table t1(i int);
3278 insert into t1 values (1),(2),(10),(20),(30);
3279 select sum(i) over (order by i), interval(sum(i) over (order by i), 10, 20)
3280 from t1;
3281 sum(i) over (order by i) interval(sum(i) over (order by i), 10, 20)
3282 1 0
3283 3 0
3284 13 1
3285 33 2
3286 63 2
3287 drop table t1;
3288 #
3289 # MDEV-13352: Server crashes in st_join_table::remove_duplicates
3290 #
3291 CREATE TABLE t1 (i INT);
3292 INSERT INTO t1 VALUES (1),(2);
3293 SELECT DISTINCT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
3294 ROW_NUMBER() OVER() i
3295 SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
3296 ROW_NUMBER() OVER() i
3297 DROP TABLE t1;
3298 #
3299 # MDEV-13344: Server crashes in in AGGR_OP::put_record on subquery
3300 # with window function and constant table
3301 # (Testcase only)
3302 #
3303 CREATE TABLE t1 (c CHAR(8)) ENGINE=MyISAM;
3304 INSERT IGNORE INTO t1 VALUES ('foo');
3305 SELECT ('bar',1) IN ( SELECT c, ROW_NUMBER() OVER (PARTITION BY c) FROM t1) as result;
3306 result
3307 0
3308 DROP TABLE t1;
3309 #
3310 # MDEV-13351: Server crashes in st_select_lex::set_explain_type upon UNION with window function
3311 #
3312 CREATE TABLE t1 (i INT);
3313 INSERT INTO t1 VALUES (1),(2);
3314 SELECT Nth_value(i,1) OVER() FROM t1
3315 UNION ALL
3316 ( SELECT Nth_value(i,2) OVER() FROM t1 LIMIT 0 )
3317 ;
3318 Nth_value(i,1) OVER()
3319 1
3320 1
3321 DROP TABLE t1;
3322 #
3323 # A regression after MDEV-13351:
3324 # MDEV-13374 : Server crashes in first_linear_tab / st_select_lex::set_explain_type
3325 # upon UNION with aggregate function
3326 #
3327 CREATE TABLE t1 (i INT) ENGINE=MyISAM;
3328 INSERT INTO t1 VALUES (1),(2);
3329 SELECT i AS fld FROM t1 UNION SELECT COUNT(*) AS fld FROM t1;
3330 fld
3331 1
3332 2
3333 DROP TABLE t1;
3334 #
3335 # MDEV-13240 Wrong warning with MAX(datetime_field) OVER (...)
3336 #
3337 CREATE TABLE t1 (dt DATETIME);
3338 INSERT INTO t1 VALUES ('2017-05-17');
3339 SELECT MAX(dt) OVER (ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) as result FROM t1;
3340 result
3341 NULL
3342 DROP TABLE t1;
3343 #
3344 # MDEV-13358 FIRST_VALUE throws SQL Error (1292): Incorrect datetime value
3345 #
3346 CREATE TABLE IF NOT EXISTS `fv_test` (
3347 `SOME_DATE` datetime NOT NULL
3348 );
3349 INSERT INTO `fv_test` (`SOME_DATE`) VALUES ('2017-07-20 12:47:56');
3350 CREATE TABLE fv_result
3351 SELECT
3352 FIRST_VALUE(SOME_DATE) OVER(ORDER BY SOME_DATE DESC) AS somedate
3353 FROM fv_test;
3354 SHOW CREATE TABLE fv_result;
3355 Table Create Table
3356 fv_result CREATE TABLE `fv_result` (
3357 `somedate` datetime DEFAULT NULL
3358 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
3359 SELECT * FROM fv_result;
3360 somedate
3361 2017-07-20 12:47:56
3362 DROP TABLE fv_test, fv_result;
3363 #
3364 # MDEV-13649: Server crashes in set_field_to_null_with_conversions or in Field::set_notnull
3365 #
3366 CREATE TABLE t1 (a int);
3367 INSERT INTO t1 VALUES (0),(1),(2);
3368 SELECT LEAD(a) OVER (PARTITION BY a ORDER BY a) as lead,
3369 a AND LEAD(a) OVER (PARTITION BY a ORDER BY a) AS a_and_lead_part
3370 FROM t1;
3371 lead a_and_lead_part
3372 NULL 0
3373 NULL NULL
3374 NULL NULL
3375 SELECT a OR LEAD(a) OVER (ORDER BY a) AS a_or_lead_order
3376 FROM t1
3377 ORDER BY a;
3378 a_or_lead_order
3379 1
3380 1
3381 1
3382 SELECT a AND LEAD(a) OVER (ORDER BY a) AS a_and_lead_order
3383 FROM t1
3384 ORDER BY a;
3385 a_and_lead_order
3386 0
3387 1
3388 NULL
3389 SELECT a XOR LEAD(a) OVER (ORDER BY a) AS a_xor_lead_order
3390 FROM t1
3391 ORDER BY a;
3392 a_xor_lead_order
3393 1
3394 0
3395 NULL
3396 SELECT NOT LEAD(a) OVER (ORDER BY a) AS not_lead_order
3397 FROM t1
3398 ORDER BY a;
3399 not_lead_order
3400 0
3401 0
3402 NULL
3403 SELECT LEAD(a) OVER (ORDER BY a) is not null AS is_not_null_lead_order
3404 FROM t1
3405 ORDER BY a;
3406 is_not_null_lead_order
3407 1
3408 1
3409 0
3410 drop table t1;
3411 #
3412 # MDEV-13354: Server crashes in find_field_in_tables upon PS with window function and subquery
3413 #
3414 CREATE TABLE t1 (i INT, a char);
3415 INSERT INTO t1 VALUES (1, 'a'),(2, 'b');
3416 PREPARE stmt FROM "SELECT row_number() over (partition by i order by i), i FROM (SELECT * from t1) as sq";
3417 EXECUTE stmt;
3418 row_number() over (partition by i order by i) i
3419 1 1
3420 1 2
3421 DROP TABLE t1;
3422 #
3423 # MDEV-13384: "window" seems like a reserved column name but it's not listed as one
3424 #
3425 # Currently we allow window as an identifier, except for table aliases.
3426 #
3427 CREATE TABLE door (id INT, window VARCHAR(10));
3428 SELECT id
3429 FROM door as window;
3430 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'window' at line 2
3431 SELECT id, window
3432 FROM door;
3433 id window
3434 SELECT id, window
3435 FROM door as window;
3436 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'window' at line 2
3437 DROP TABLE door;
3438 #
3439 # MDEV-13352: Server crashes in st_join_table::remove_duplicates
3440 #
3441 CREATE TABLE t1 (i INT);
3442 INSERT INTO t1 VALUES (1),(2);
3443 SELECT DISTINCT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
3444 ROW_NUMBER() OVER() i
3445 SELECT ROW_NUMBER() OVER(), i FROM t1 WHERE 0;
3446 ROW_NUMBER() OVER() i
3447 DROP TABLE t1;
3448 #
3449 # MDEV-15853: Assertion `tab->filesort_result == 0' failed
3450 #
3451 CREATE TABLE t1 ( a1 int);
3452 insert into t1 values (1),(2),(3);
3453 CREATE TABLE t2 (b1 int, a1 int, a2 int);
3454 insert into t2 values (1,2,3),(2,3,4),(3,4,5);
3455 SELECT COUNT(DISTINCT t2.a2),
3456 rank() OVER (ORDER BY t2.b1)
3457 FROM t2 ,t1 GROUP BY t2.b1 ORDER BY t1.a1;
3458 COUNT(DISTINCT t2.a2) rank() OVER (ORDER BY t2.b1)
3459 1 1
3460 1 2
3461 1 3
3462 DROP TABLE t1,t2;
3463 #
3464 # MDEV-16990: server crashes in base_list_iterator::next
3465 #
3466 CREATE TABLE t1(i int);
3467 insert into t1 values (1),(2);
3468 SELECT DISTINCT row_number() OVER (), MAX(1) FROM t1;
3469 row_number() OVER () MAX(1)
3470 1 1
3471 SELECT DISTINCT BIT_AND(0) OVER (), MAX(1) FROM t1;
3472 BIT_AND(0) OVER () MAX(1)
3473 0 1
3474 drop table t1;
3475 #
3476 # MDEV-17525: Window functions not working in ONLY_FULL_GROUP_BY mode
3477 #
3478 CREATE TABLE t1 (name CHAR(10), test CHAR(10), score TINYINT);
3479 INSERT INTO t1 VALUES
3480 ('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
3481 ('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
3482 ('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
3483 ('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
3484 SET @save_sql_mode= @@sql_mode;
3485 SET sql_mode = 'ONLY_FULL_GROUP_BY';
3486 SELECT name, test, score,
3487 AVG(score) OVER (PARTITION BY test) AS average_by_test
3488 FROM t1
3489 ORDER BY test, name;
3490 name test score average_by_test
3491 Chun SQL 75 65.2500
3492 Esben SQL 43 65.2500
3493 Kaolin SQL 56 65.2500
3494 Tatiana SQL 87 65.2500
3495 Chun Tuning 73 68.7500
3496 Esben Tuning 31 68.7500
3497 Kaolin Tuning 88 68.7500
3498 Tatiana Tuning 83 68.7500
3499 set @@sql_mode= @save_sql_mode;
3500 SELECT name, test, score,
3501 AVG(score) OVER (PARTITION BY test) AS average_by_test
3502 FROM t1
3503 ORDER BY test, name;
3504 name test score average_by_test
3505 Chun SQL 75 65.2500
3506 Esben SQL 43 65.2500
3507 Kaolin SQL 56 65.2500
3508 Tatiana SQL 87 65.2500
3509 Chun Tuning 73 68.7500
3510 Esben Tuning 31 68.7500
3511 Kaolin Tuning 88 68.7500
3512 Tatiana Tuning 83 68.7500
3513 drop table t1;
3514 #
3515 # MDEV-12575: Server crash in AGGR_OP::put_record or in JOIN_CACHE::free
3516 # or Invalid write in JOIN::make_aggr_tables_info
3517 #
3518 SELECT DISTINCT BIT_OR(100) OVER () FROM dual
3519 GROUP BY LEFT('2018-08-24', 100) order by 1+2;
3520 BIT_OR(100) OVER ()
3521 100
3522 CREATE TABLE t1 (i INT);
3523 INSERT INTO t1 VALUES (1),(2);
3524 SELECT * FROM (
3525 SELECT
3526 ROW_NUMBER() OVER(), i, sum(i)
3527 FROM t1
3528 WHERE 1=0
3529 limit 0
3530 ) AS sq;
3531 ROW_NUMBER() OVER() i sum(i)
3532 SELECT * FROM (
3533 SELECT
3534 ROW_NUMBER() OVER(), i, sum(i)
3535 FROM t1
3536 WHERE 1=0
3537 GROUP BY i
3538 ) AS sq;
3539 ROW_NUMBER() OVER() i sum(i)
3540 drop table t1;
3541 create table t1 (a int);
3542 explain
3543 select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup;
3544 id select_type table type possible_keys key key_len ref rows Extra
3545 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3546 select distinct 1, row_number() over (order by 1) from t1 where a=0 group by a with rollup;
3547 1 row_number() over (order by 1)
3548 drop table t1;
3549 explain
3550 SELECT DISTINCT BIT_OR(100) OVER () FROM dual
3551 GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP
3552 HAVING @A := 'qwerty';
3553 id select_type table type possible_keys key key_len ref rows Extra
3554 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
3555 Warnings:
3556 Warning 1292 Truncated incorrect BOOLEAN value: 'qwerty'
3557 SELECT DISTINCT BIT_OR(100) OVER () FROM dual
3558 GROUP BY LEFT('2018-08-24', 100) WITH ROLLUP
3559 HAVING @A := 'qwerty';
3560 BIT_OR(100) OVER ()
3561 Warnings:
3562 Warning 1292 Truncated incorrect BOOLEAN value: 'qwerty'
3563 explain
3564 SELECT DISTINCT BIT_OR(100) OVER () FROM dual
3565 GROUP BY LEFT('2018-08-24', 100)
3566 HAVING @A := 'qwerty';
3567 id select_type table type possible_keys key key_len ref rows Extra
3568 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
3569 Warnings:
3570 Warning 1292 Truncated incorrect BOOLEAN value: 'qwerty'
3571 SELECT DISTINCT BIT_OR(100) OVER () FROM dual
3572 GROUP BY LEFT('2018-08-24', 100)
3573 HAVING @A := 'qwerty';
3574 BIT_OR(100) OVER ()
3575 Warnings:
3576 Warning 1292 Truncated incorrect BOOLEAN value: 'qwerty'
3577 create table t1 (a int);
3578 explain
3579 SELECT DISTINCT BIT_OR(100) OVER () FROM t1
3580 GROUP BY LEFT('2018-08-24', 100) having 1=1 limit 0;
3581 id select_type table type possible_keys key key_len ref rows Extra
3582 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Zero limit
3583 drop table t1;
3584 #
3585 # MDEV-13170: Database service (MySQL) stops after update with trigger
3586 #
3587 CREATE TABLE t1 ( t1_id int, point_id int, ml_id int, UNIQUE KEY t1_ml_u (ml_id,point_id)) ;
3588 INSERT INTO t1 VALUES (1,1,8884),(2,1,8885);
3589 CREATE TABLE t2 ( db_time datetime, au_nr int, col_id int, new_val int);
3590 CREATE TABLE t3 (id1 int, id2 int, d1 int);
3591 CREATE TRIGGER t1_aurtrg AFTER UPDATE ON t1 FOR EACH ROW begin
3592 CREATE OR REPLACE TEMPORARY TABLE trg_u AS
3593 WITH l AS
3594 (SELECT a.*,
3595 Max(t2.col_id) over (PARTITION BY a.d1),
3596 Max(t2.new_val) over (PARTITION BY a.d1)
3597 FROM
3598 (SELECT d1 , id1, id2 FROM t3) a
3599 JOIN t2 ON (a.d1=t2.db_time AND a.id1=t2.au_nr))
3600 SELECT 1;
3601 END;//
3602 update t1 set ml_id=8884 where point_id=1;
3603 ERROR 23000: Duplicate entry '8884-1' for key 't1_ml_u'
3604 update t1 set ml_id=8884 where point_id=1;
3605 ERROR 23000: Duplicate entry '8884-1' for key 't1_ml_u'
3606 drop table t1, t2,t3;
3607 CREATE TABLE t1 (i INT, a char);
3608 INSERT INTO t1 VALUES (1, 'a'),(2, 'b');
3609 create view v1 as select * from t1;
3610 PREPARE stmt FROM "SELECT i, row_number() over (partition by i order by i) FROM v1";
3611 execute stmt;
3612 i row_number() over (partition by i order by i)
3613 1 1
3614 2 1
3615 deallocate prepare stmt;
3616 drop table t1;
3617 drop view v1;
3618 #
3619 # MDEV-17676: Assertion `inited==NONE || (inited==RND && scan)' failed in handler::ha_rnd_init
3620 #
3621 CREATE TABLE t1 (b1 text NOT NULL);
3622 INSERT INTO t1 VALUES ('2'),('1');
3623 EXPLAIN
3624 SELECT DISTINCT MIN(b1) OVER () FROM t1;
3625 id select_type table type possible_keys key key_len ref rows Extra
3626 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary
3627 SELECT DISTINCT MIN(b1) OVER () FROM t1;
3628 MIN(b1) OVER ()
3629 1
3630 drop table t1;
3631 #
3632 # MDEV-15424: Unreasonal SQL Error (1356) on select from view
3633 #
3634 create table t1 (id int, n1 int);
3635 insert into t1 values (1,1), (2,1), (3,2), (4,4);
3636 create view v1 as SELECT ifnull(max(n1) over (partition by n1),'aaa') FROM t1;
3637 explain select * from v1;
3638 id select_type table type possible_keys key key_len ref rows Extra
3639 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4
3640 2 DERIVED t1 ALL NULL NULL NULL NULL 4 Using temporary
3641 select * from v1;
3642 ifnull(max(n1) over (partition by n1),'aaa')
3643 1
3644 1
3645 2
3646 4
3647 drop table t1;
3648 drop view v1;
3649 #
3650 # MDEV-18431: Select max + row_number giving incorrect result
3651 #
3652 create table t1 (id int, v int);
3653 insert into t1 values (1, 1), (1,2), (1,3), (2, 1), (2, 2);
3654 select e.id,
3655 (select max(t1.v) from t1 where t1.id=e.id) as a,
3656 row_number() over (partition by e.id order by e.v) as b,
3657 (select max(t1.v) from t1 where t1.id=e.id) + (row_number() over (partition by e.id order by e.v)) as sum_a_b
3658 from t1 e;
3659 id a b sum_a_b
3660 1 3 1 4
3661 1 3 2 5
3662 1 3 3 6
3663 2 2 1 3
3664 2 2 2 4
3665 drop table t1;
3666 #
3667 # MDEV-15837: Assertion `item1->type() == Item::FIELD_ITEM && item2->type() == Item::FIELD_ITEM'
3668 # failed in compare_order_elements function
3669 #
3670 CREATE TABLE t1 (a1 int);
3671 insert into t1 values (1),(2),(3);
3672 SELECT rank() OVER (ORDER BY 1), ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4))) FROM t1;
3673 rank() OVER (ORDER BY 1) ROW_NUMBER() OVER (ORDER BY (EXPORT_SET(5,'Y','N',',',4)))
3674 1 1
3675 1 2
3676 1 3
3677 drop table t1;
3678 #
3679 # MDEV-17781: Server crashes in next_linear_tab
3680 #
3681 CREATE TABLE t1 (i1 int);
3682 explain
3683 (SELECT AVG(0) OVER (), MAX('2') FROM t1)
3684 UNION ALL
3685 (SELECT AVG(0) OVER (), MAX('2') FROM t1);
3686 id select_type table type possible_keys key key_len ref rows Extra
3687 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used
3688 2 UNION NULL NULL NULL NULL NULL NULL NULL No tables used
3689 (SELECT AVG(0) OVER (), MAX('2') FROM t1)
3690 UNION ALL
3691 (SELECT AVG(0) OVER (), MAX('2') FROM t1);
3692 AVG(0) OVER () MAX('2')
3693 0.0000 NULL
3694 0.0000 NULL
3695 drop table t1;
3696 #
3697 # MDEV-14791: Crash with order by expression containing window functions
3698 #
3699 CREATE TABLE t1 (b1 int, b2 int);
3700 INSERT INTO t1 VALUES (1,1),(0,0);
3701 explain
3702 SELECT b1 from t1 order by row_number() over (ORDER BY b2) + 1;
3703 id select_type table type possible_keys key key_len ref rows Extra
3704 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3705 SELECT b1 from t1 order by row_number() over (ORDER BY b2) + 1;
3706 b1
3707 0
3708 1
3709 explain
3710 SELECT b1 from t1 order by row_number() over (ORDER BY b2);
3711 id select_type table type possible_keys key key_len ref rows Extra
3712 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
3713 SELECT b1 from t1 order by row_number() over (ORDER BY b2);
3714 b1
3715 0
3716 1
3717 DROP TABLE t1;
3718 CREATE TABLE t1 (a int, b int, c int);
3719 INSERT INTO t1 VALUES (2,3,207), (1,21,909), (7,13,312), (8,64,248);
3720 explain
3721 SELECT * FROM t1 ORDER BY max(t1.a) over (partition by c);
3722 id select_type table type possible_keys key key_len ref rows Extra
3723 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
3724 SELECT * FROM t1 ORDER BY max(t1.a) over (partition by c);
3725 a b c
3726 1 21 909
3727 2 3 207
3728 7 13 312
3729 8 64 248
3730 explain
3731 SELECT max(t1.a) over (partition by c) as x, b, c from t1 order by max(t1.a) over (partition by c);
3732 id select_type table type possible_keys key key_len ref rows Extra
3733 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort
3734 SELECT max(t1.a) over (partition by c) as x, b, c from t1 order by max(t1.a) over (partition by c);
3735 x b c
3736 1 21 909
3737 2 3 207
3738 7 13 312
3739 8 64 248
3740 drop table t1;
3741 #
3742 # MDEV-18373: DENSE_RANK is not calculated correctly
3743 #
3744 create table t1 (a int, b int);
3745 insert into t1 values (60, 1515),(60, 2000),(70, 2000),(55, 1600);
3746 select b, dense_rank() over (order by sum(a)) from t1 group by b;
3747 b dense_rank() over (order by sum(a))
3748 1515 2
3749 1600 1
3750 2000 3
3751 select b, dense_rank() over (order by sum(a)+1) from t1 group by b;
3752 b dense_rank() over (order by sum(a)+1)
3753 1515 2
3754 1600 1
3755 2000 3
3756 select b, row_number() over (partition by sum(a)) from t1 group by b;
3757 b row_number() over (partition by sum(a))
3758 1515 1
3759 1600 1
3760 2000 1
3761 select b, row_number() over (partition by sum(a)+1) from t1 group by b;
3762 b row_number() over (partition by sum(a)+1)
3763 1515 1
3764 1600 1
3765 2000 1
3766 drop table t1;
3767 #
3768 # MDEV-18015: Assertion `global_status_var.global_memory_used == 0' failed when using UDF,
3769 # window functions and views
3770 #
3771 create table t1 (id int, n1 int);
3772 insert into t1 values (1,1),(2,1),(3,2),(4,4);
3773 explain
3774 select max(n1) over (partition by 'abc') from t1;
3775 id select_type table type possible_keys key key_len ref rows Extra
3776 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary
3777 select max(n1) over (partition by 'abc') from t1;
3778 max(n1) over (partition by 'abc')
3779 4
3780 4
3781 4
3782 4
3783 explain
3784 select rank() over (partition by 'abc' order by 'xyz') from t1;
3785 id select_type table type possible_keys key key_len ref rows Extra
3786 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary
3787 select rank() over (partition by 'abc' order by 'xyz') from t1;
3788 rank() over (partition by 'abc' order by 'xyz')
3789 1
3790 1
3791 1
3792 1
3793 drop table t1;
3794 #
3795 # MDEV-19380: ASAN heap-use-after-free in Protocol::net_store_data
3796 #
3797 CREATE TABLE t1 (i int);
3798 INSERT INTO t1 VALUES (1),(2),(3);
3799 SELECT (SELECT MIN('foo') OVER() FROM t1 LIMIT 1) as x;
3800 x
3801 foo
3802 drop table t1;
3803 #
3804 # MDEV-16579: Wrong result of query using DISTINCT COUNT(*) OVER (*)
3805 #
3806 CREATE TABLE t1 (i int) ;
3807 INSERT INTO t1 VALUES (1),(0),(1),(2),(0),(1),(2),(1),(2);
3808 SELECT DISTINCT COUNT(*) OVER (), MOD(MIN(i),2) FROM t1 GROUP BY i ;
3809 COUNT(*) OVER () MOD(MIN(i),2)
3810 3 0
3811 3 1
3812 drop table t1;
3813 #
3814 # MDEV-21318: Wrong results with window functions and implicit grouping
3815 #
3816 CREATE TABLE t1 (a INT);
3817 #
3818 # With empty const table
3819 # The expected result here is 1, NULL
3820 #
3821 explain
3822 SELECT row_number() over(), sum(1) FROM t1;
3823 id select_type table type possible_keys key key_len ref rows Extra
3824 1 SIMPLE t1 system NULL NULL NULL NULL 0 Const row not found; Using temporary
3825 SELECT row_number() over(), sum(1) FROM t1;
3826 row_number() over() sum(1)
3827 1 NULL
3828 insert into t1 values (2);
3829 #
3830 # Const table has 1 row, but still impossible where
3831 # The expected result here is 1, NULL
3832 #
3833 EXPLAIN SELECT row_number() over(), sum(1) FROM t1 where a=1;
3834 id select_type table type possible_keys key key_len ref rows Extra
3835 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
3836 SELECT row_number() over(), sum(1) FROM t1 where a=1;
3837 row_number() over() sum(1)
3838 1 NULL
3839 #
3840 # Impossible HAVING
3841 # Empty result is expected
3842 #
3843 EXPLAIN SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0;
3844 id select_type table type possible_keys key key_len ref rows Extra
3845 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible HAVING
3846 SELECT row_number() over(), sum(1) FROM t1 where a=1 having 1=0;
3847 row_number() over() sum(1)
3848 #
3849 # const table has 1 row, no impossible where
3850 # The expected result here is 1, 2
3851 #
3852 EXPLAIN SELECT row_number() over(), sum(a) FROM t1 where a=2;
3853 id select_type table type possible_keys key key_len ref rows Extra
3854 1 SIMPLE t1 system NULL NULL NULL NULL 1 Using temporary
3855 SELECT row_number() over(), sum(a) FROM t1 where a=2;
3856 row_number() over() sum(a)
3857 1 2
3858 drop table t1;
3859 #
3860 # Impossible Where
3861 #
3862 create table t1(a int);
3863 insert into t1 values (1);
3864 #
3865 # Expected result is NULL, 0, NULL
3866 #
3867 EXPLAIN SELECT MAX(a) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE;
3868 id select_type table type possible_keys key key_len ref rows Extra
3869 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
3870 SELECT MAX(a) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE;
3871 MAX(a) OVER () COUNT(a) abs(a)
3872 NULL 0 NULL
3873 #
3874 # Expected result is 1, 0, NULL
3875 #
3876 EXPLAIN
3877 SELECT MAX(1) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE;
3878 id select_type table type possible_keys key key_len ref rows Extra
3879 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
3880 SELECT MAX(1) OVER (), COUNT(a), abs(a) FROM t1 WHERE FALSE;
3881 MAX(1) OVER () COUNT(a) abs(a)
3882 1 0 NULL
3883 drop table t1;
3884 #
3885 # MDEV-22461: JOIN::make_aggr_tables_info(): Assertion `select_options & (1ULL << 17)' failed.
3886 #
3887 CREATE TEMPORARY TABLE t0 (a INT PRIMARY KEY ) ;
3888 INSERT INTO t0 VALUES (1),(2),(3);
3889 SELECT a FROM t0
3890 WHERE a < 8
3891 GROUP BY 1.5
3892 WINDOW v2 AS ( PARTITION BY a ORDER BY a DESC );
3893 a
3894 1
3895 SELECT a, ROW_NUMBER() OVER v2
3896 FROM t0
3897 WHERE a < 8
3898 GROUP BY 1.5
3899 WINDOW v2 AS ( PARTITION BY a ORDER BY a DESC );
3900 a ROW_NUMBER() OVER v2
3901 1 1
3902 drop table t0;
3903 #
3904 # MDEV-16230:Server crashes when Analyze format=json is run with a window function with
3905 # empty PARTITION BY and ORDER BY clauses
3906 #
3907 CREATE TABLE t1(a INT, b INT);
3908 INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
3909 ANALYZE FORMAT=JSON SELECT row_number() OVER() FROM t1;
3910 ANALYZE
3911 {
3912 "query_optimization": {
3913 "r_total_time_ms": "REPLACED"
3914 },
3915 "query_block": {
3916 "select_id": 1,
3917 "cost": "REPLACED",
3918 "r_loops": 1,
3919 "r_total_time_ms": "REPLACED",
3920 "window_functions_computation": {
3921 "sorts": [
3922 {
3923 "filesort": {
3924 "sort_key": "`row_number() OVER()`",
3925 "r_loops": 1,
3926 "r_total_time_ms": "REPLACED",
3927 "r_used_priority_queue": false,
3928 "r_output_rows": 3,
3929 "r_buffer_size": "REPLACED",
3930 "r_sort_mode": "sort_key,rowid"
3931 }
3932 }
3933 ],
3934 "temporary_table": {
3935 "nested_loop": [
3936 {
3937 "table": {
3938 "table_name": "t1",
3939 "access_type": "ALL",
3940 "loops": 1,
3941 "r_loops": 1,
3942 "rows": 3,
3943 "r_rows": 3,
3944 "cost": "REPLACED",
3945 "r_table_time_ms": "REPLACED",
3946 "r_other_time_ms": "REPLACED",
3947 "r_engine_stats": REPLACED,
3948 "filtered": 100,
3949 "r_total_filtered": 100,
3950 "r_filtered": 100
3951 }
3952 }
3953 ]
3954 }
3955 }
3956 }
3957 }
3958 SELECT row_number() OVER() FROM t1;
3959 row_number() OVER()
3960 1
3961 2
3962 3
3963 DROP TABLE t1;
3964 #
3965 # MDEV-22984: Throw an error when arguments to window functions are window functions
3966 #
3967 CREATE TABLE t1(a INT, b INT);
3968 INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
3969 SELECT NTILE(MAX(a) OVER (PARTITION BY a)) OVER (PARTITION BY a ORDER BY b) FROM t1;
3970 ERROR HY000: Window functions can not be used as arguments to group functions.
3971 SELECT FIRST_VALUE(MAX(a) OVER (PARTITION BY a)) OVER (ORDER BY a) AS x FROM t1 GROUP BY a;
3972 ERROR HY000: Window functions can not be used as arguments to group functions.
3973 DROP TABLE t1;
3974 #
3975 # MDEV-12059: Assertion `precision > 0' failed with a window function or window aggregate function
3976 #
3977 CREATE TABLE t1 (d DECIMAL(1,0) UNSIGNED);
3978 INSERT INTO t1 VALUES (1),(2);
3979 SELECT MIN(d) OVER () FROM t1;
3980 MIN(d) OVER ()
3981 1
3982 1
3983 DROP TABLE t1;
3984 #
3985 # MDEV-22463: Element_type &Bounds_checked_array<Item *>::operator[](size_t) [Element_type = Item *]:
3986 # Assertion `n < m_size' failed
3987 #
3988 CREATE TABLE t1 (a INT, b INT, c INT, d INT, e INT, f INT, g int, h INT, i INT);
3989 INSERT INTO t1 SELECT seq,seq,seq,seq, seq,seq,seq,seq,seq FROM seq_1_to_5;
3990 SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6);
3991 ROW_NUMBER() OVER w2
3992 1
3993 2
3994 3
3995 4
3996 5
3997 SELECT a FROM t1 ORDER BY ROW_NUMBER() OVER (PARTITION BY -1,1,0,2,3,4,5,6,7,8);
3998 a
3999 1
4000 2
4001 3
4002 4
4003 5
4004 SELECT a,b FROM t1 WINDOW w2 AS (PARTITION BY -1,1,0,2,3,4);
4005 a b
4006 1 1
4007 2 2
4008 3 3
4009 4 4
4010 5 5
4011 SELECT ROW_NUMBER() OVER w2 FROM t1 WINDOW w2 AS (PARTITION BY -1,0,1,2,3,4,5,6);
4012 ROW_NUMBER() OVER w2
4013 1
4014 2
4015 3
4016 4
4017 5
4018 DROP TABLE t1;
4019 #
4020 # MDEV-18916: crash in Window_spec::print_partition() with decimals
4021 #
4022 SELECT cast((rank() over w1) as decimal (53,56));
4023 ERROR 42000: Too big scale specified for 'rank() over w1'. Maximum is 38
4024 SELECT cast((rank() over w1) as decimal (53,30));
4025 ERROR HY000: Window specification with name 'w1' is not defined
4026 #
4027 # MDEV-15180: server crashed with NTH_VALUE()
4028 #
4029 CREATE TABLE t1 (i1 int, a int);
4030 INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3);
4031 CREATE TABLE t2 (i2 int);
4032 INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3);
4033 CREATE VIEW v1 AS (SELECT * FROM t1,t2 WHERE t1.i1=t2.i2) ;
4034 SELECT NTH_VALUE(i1, i1) OVER (PARTITION BY i1) FROM v1;
4035 NTH_VALUE(i1, i1) OVER (PARTITION BY i1)
4036 1
4037 1
4038 NULL
4039 NULL
4040 DROP VIEW v1;
4041 DROP TABLE t1,t2;
4042 #
4043 # MDEV-25032 Window functions without column references get removed from ORDER BY
4044 #
4045 create table t1 (id int, score double);
4046 insert into t1 values
4047 (1, 5),
4048 (1, 6),
4049 (1, 6),
4050 (1, 6),
4051 (1, 7),
4052 (1, 8.1),
4053 (1, 9),
4054 (1, 10);
4055 select id, row_number() over () rn
4056 from t1
4057 order by rn desc;
4058 id rn
4059 1 8
4060 1 7
4061 1 6
4062 1 5
4063 1 4
4064 1 3
4065 1 2
4066 1 1
4067 drop table t1;
4068 #
4069 # MDEV-25630: Crash with window function in left expr of IN subquery
4070 #
4071 CREATE TABLE t1 (i int);
4072 INSERT INTO t1 VALUES (1),(2),(3);
4073 SELECT lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a) FROM t1;
4074 lag(i) over (ORDER BY i) IN ( SELECT 1 FROM t1 a)
4075 NULL
4076 1
4077 0
4078 DROP TABLE t1;
4079 CREATE TABLE t1 (i int);
4080 INSERT INTO t1 VALUES (1),(2),(3);
4081 SELECT sum(i) over () IN ( SELECT 1 FROM t1 a) FROM t1;
4082 sum(i) over () IN ( SELECT 1 FROM t1 a)
4083 0
4084 0
4085 0
4086 DROP TABLE t1;
4087 #
4088 # MDEV-25565: 2-nd call of SP with SELECT from view / derived table / CTE
4089 # returning the result of calculation of 2 window
4090 # functions that use the same window specification
4091 #
4092 create table t1 (a int);
4093 insert into t1 values (3), (7), (1), (7), (1), (1), (3), (1), (5);
4094 create view v2 as select a from t1 group by a;
4095 create view v1 as select * from v2;
4096 create procedure sp1() select v1.a,
4097 sum(v1.a) over (partition by v1.a order by v1.a) as k,
4098 avg(v1.a) over (partition by v1.a order by v1.a) as m
4099 from v1;
4100 call sp1();
4101 a k m
4102 1 1 1.0000
4103 3 3 3.0000
4104 5 5 5.0000
4105 7 7 7.0000
4106 call sp1();
4107 a k m
4108 1 1 1.0000
4109 3 3 3.0000
4110 5 5 5.0000
4111 7 7 7.0000
4112 prepare stmt from "select v1.a,
4113 sum(v1.a) over (partition by v1.a order by v1.a) as k,
4114 avg(v1.a) over (partition by v1.a order by v1.a) as m
4115 from v1";
4116 execute stmt;
4117 a k m
4118 1 1 1.0000
4119 3 3 3.0000
4120 5 5 5.0000
4121 7 7 7.0000
4122 execute stmt;
4123 a k m
4124 1 1 1.0000
4125 3 3 3.0000
4126 5 5 5.0000
4127 7 7 7.0000
4128 deallocate prepare stmt;
4129 create procedure sp2() select * from
4130 ( select dt1.a,
4131 sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
4132 avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
4133 from (select * from v2) as dt1
4134 ) as dt;
4135 call sp2();
4136 a k m
4137 1 1 1.0000
4138 3 3 3.0000
4139 5 5 5.0000
4140 7 7 7.0000
4141 call sp2();
4142 a k m
4143 1 1 1.0000
4144 3 3 3.0000
4145 5 5 5.0000
4146 7 7 7.0000
4147 prepare stmt from "select * from
4148 ( select dt1.a,
4149 sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
4150 avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
4151 from (select * from v2) as dt1
4152 ) as dt";
4153 execute stmt;
4154 a k m
4155 1 1 1.0000
4156 3 3 3.0000
4157 5 5 5.0000
4158 7 7 7.0000
4159 execute stmt;
4160 a k m
4161 1 1 1.0000
4162 3 3 3.0000
4163 5 5 5.0000
4164 7 7 7.0000
4165 deallocate prepare stmt;
4166 create procedure sp3() select * from
4167 ( select dt1.a,
4168 sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
4169 avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
4170 from ( select * from (select * from t1 group by a) as dt2 ) as dt1
4171 ) as dt;
4172 call sp3();
4173 a k m
4174 1 1 1.0000
4175 3 3 3.0000
4176 5 5 5.0000
4177 7 7 7.0000
4178 call sp3();
4179 a k m
4180 1 1 1.0000
4181 3 3 3.0000
4182 5 5 5.0000
4183 7 7 7.0000
4184 prepare stmt from "select * from
4185 ( select dt1.a,
4186 sum(dt1.a) over (partition by dt1.a order by dt1.a) as k,
4187 avg(dt1.a) over (partition by dt1.a order by dt1.a) as m
4188 from ( select * from (select * from t1 group by a) as dt2 ) as dt1
4189 ) as dt";
4190 execute stmt;
4191 a k m
4192 1 1 1.0000
4193 3 3 3.0000
4194 5 5 5.0000
4195 7 7 7.0000
4196 execute stmt;
4197 a k m
4198 1 1 1.0000
4199 3 3 3.0000
4200 5 5 5.0000
4201 7 7 7.0000
4202 deallocate prepare stmt;
4203 create procedure sp4() with cte1 as (select * from (select * from t1 group by a) as dt2),
4204 cte as
4205 ( select cte1.a,
4206 sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
4207 avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
4208 from cte1 )
4209 select * from cte;
4210 call sp4();
4211 a k m
4212 1 1 1.0000
4213 3 3 3.0000
4214 5 5 5.0000
4215 7 7 7.0000
4216 call sp4();
4217 a k m
4218 1 1 1.0000
4219 3 3 3.0000
4220 5 5 5.0000
4221 7 7 7.0000
4222 prepare stmt from "with cte1 as (select * from (select * from t1 group by a) as dt2),
4223 cte as
4224 ( select cte1.a,
4225 sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
4226 avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
4227 from cte1 )
4228 select * from cte";
4229 execute stmt;
4230 a k m
4231 1 1 1.0000
4232 3 3 3.0000
4233 5 5 5.0000
4234 7 7 7.0000
4235 execute stmt;
4236 a k m
4237 1 1 1.0000
4238 3 3 3.0000
4239 5 5 5.0000
4240 7 7 7.0000
4241 deallocate prepare stmt;
4242 create procedure sp5() with cte1 as (select * from v2),
4243 cte as
4244 ( select cte1.a,
4245 sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
4246 avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
4247 from cte1 )
4248 select * from cte;
4249 call sp5();
4250 a k m
4251 1 1 1.0000
4252 3 3 3.0000
4253 5 5 5.0000
4254 7 7 7.0000
4255 call sp5();
4256 a k m
4257 1 1 1.0000
4258 3 3 3.0000
4259 5 5 5.0000
4260 7 7 7.0000
4261 prepare stmt from "with cte1 as (select * from v2),
4262 cte as
4263 ( select cte1.a,
4264 sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
4265 avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
4266 from cte1 )
4267 select * from cte";
4268 execute stmt;
4269 a k m
4270 1 1 1.0000
4271 3 3 3.0000
4272 5 5 5.0000
4273 7 7 7.0000
4274 execute stmt;
4275 a k m
4276 1 1 1.0000
4277 3 3 3.0000
4278 5 5 5.0000
4279 7 7 7.0000
4280 deallocate prepare stmt;
4281 create procedure sp6() with
4282 cte1 as (with cte2 as (select * from t1 group by a) select * from cte2),
4283 cte as
4284 ( select cte1.a,
4285 sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
4286 avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
4287 from cte1 )
4288 select * from cte;
4289 call sp6();
4290 a k m
4291 1 1 1.0000
4292 3 3 3.0000
4293 5 5 5.0000
4294 7 7 7.0000
4295 call sp6();
4296 a k m
4297 1 1 1.0000
4298 3 3 3.0000
4299 5 5 5.0000
4300 7 7 7.0000
4301 prepare stmt from "with
4302 cte1 as (with cte2 as (select * from t1 group by a) select * from cte2),
4303 cte as
4304 ( select cte1.a,
4305 sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
4306 avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
4307 from cte1 )
4308 select * from cte";
4309 execute stmt;
4310 a k m
4311 1 1 1.0000
4312 3 3 3.0000
4313 5 5 5.0000
4314 7 7 7.0000
4315 execute stmt;
4316 a k m
4317 1 1 1.0000
4318 3 3 3.0000
4319 5 5 5.0000
4320 7 7 7.0000
4321 deallocate prepare stmt;
4322 create procedure sp7() with
4323 cte2 as (select * from v1),
4324 cte1 as (select * from cte2),
4325 cte as
4326 ( select cte1.a,
4327 sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
4328 avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
4329 from cte1 )
4330 select * from cte;
4331 call sp7();
4332 a k m
4333 1 1 1.0000
4334 3 3 3.0000
4335 5 5 5.0000
4336 7 7 7.0000
4337 call sp7();
4338 a k m
4339 1 1 1.0000
4340 3 3 3.0000
4341 5 5 5.0000
4342 7 7 7.0000
4343 prepare stmt from "with
4344 cte2 as (select * from v1),
4345 cte1 as (select * from cte2),
4346 cte as
4347 ( select cte1.a,
4348 sum(cte1.a) over (partition by cte1.a order by cte1.a) as k,
4349 avg(cte1.a) over (partition by cte1.a order by cte1.a) as m
4350 from cte1 )
4351 select * from cte";
4352 execute stmt;
4353 a k m
4354 1 1 1.0000
4355 3 3 3.0000
4356 5 5 5.0000
4357 7 7 7.0000
4358 execute stmt;
4359 a k m
4360 1 1 1.0000
4361 3 3 3.0000
4362 5 5 5.0000
4363 7 7 7.0000
4364 deallocate prepare stmt;
4365 drop procedure sp1;
4366 drop procedure sp2;
4367 drop procedure sp3;
4368 drop procedure sp4;
4369 drop procedure sp5;
4370 drop procedure sp6;
4371 drop procedure sp7;
4372 drop view v1,v2;
4373 drop table t1;
4374 #
4375 # MDEV-17785: Window functions not working in ONLY_FULL_GROUP_BY mode
4376 #
4377 CREATE TABLE t1(a VARCHAR(10), b int);
4378 INSERT INTO t1 VALUES
4379 ('Maths', 60),('Maths', 60),
4380 ('Maths', 70),('Maths', 55),
4381 ('Biology', 60), ('Biology', 70);
4382 SET @save_sql_mode= @@sql_mode;
4383 SET sql_mode = 'ONLY_FULL_GROUP_BY';
4384 SELECT
4385 RANK() OVER (PARTITION BY a ORDER BY b) AS rank,
4386 a, b FROM t1 ORDER BY a, b DESC;
4387 rank a b
4388 2 Biology 70
4389 1 Biology 60
4390 4 Maths 70
4391 2 Maths 60
4392 2 Maths 60
4393 1 Maths 55
4394 SET sql_mode= @save_sql_mode;
4395 DROP TABLE t1;
4396 CREATE TABLE t1(i int,j int);
4397 INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4);
4398 INSERT INTO t1 VALUES (1,1), (1,5),(1,4), (2,2),(2,5), (3,3),(4,4);
4399 SELECT i, LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j) FROM t1 GROUP BY i;
4400 i LAST_VALUE(COUNT(i)) OVER (PARTITION BY i ORDER BY j)
4401 1 6
4402 2 4
4403 3 2
4404 4 2
4405 DROP TABLE t1;
4406 #
4407 # MDEV-15208: server crashed, when using ORDER BY with window function and UNION
4408 #
4409 CREATE TABLE t1 (a INT);
4410 INSERT INTO t1 VALUES (1),(1),(1),(1),(1),(2),(2),(2),(2),(2),(2);
4411 SELECT 1 UNION SELECT a FROM t1 ORDER BY (row_number() over ());
4412 ERROR HY000: Expression #1 of ORDER BY contains aggregate function and applies to a UNION
4413 DROP TABLE t1;
4414 #
4415 # MDEV-19398: Assertion `item1->type() == Item::FIELD_ITEM &&
4416 # item2->type() == Item::FIELD_ITEM' failed in compare_order_elements
4417 #
4418 CREATE TABLE t1 ( id varchar(10));
4419 INSERT INTO t1 values (1),(2),(3);
4420 SELECT
4421 dense_rank() over (ORDER BY avg(1)+3),
4422 rank() over (ORDER BY avg(1))
4423 FROM t1
4424 GROUP BY nullif(id, 15532);
4425 dense_rank() over (ORDER BY avg(1)+3) rank() over (ORDER BY avg(1))
4426 1 1
4427 1 1
4428 1 1
4429 SELECT
4430 dense_rank() over (ORDER BY avg(1)),
4431 rank() over (ORDER BY avg(1))
4432 FROM t1
4433 GROUP BY nullif(id, 15532);
4434 dense_rank() over (ORDER BY avg(1)) rank() over (ORDER BY avg(1))
4435 1 1
4436 1 1
4437 1 1
4438 drop table t1;
4439 CREATE TABLE t1 ( a char(25), b text);
4440 INSERT INTO t1 VALUES ('foo','bar');
4441 SELECT
4442 SUM(b) OVER (PARTITION BY a),
4443 ROW_NUMBER() OVER (PARTITION BY b)
4444 FROM t1
4445 GROUP BY
4446 LEFT((SYSDATE()), 'foo')
4447 WITH ROLLUP;
4448 SUM(b) OVER (PARTITION BY a) ROW_NUMBER() OVER (PARTITION BY b)
4449 0 1
4450 0 2
4451 Warnings:
4452 Warning 1292 Truncated incorrect INTEGER value: 'foo'
4453 Warning 1292 Truncated incorrect INTEGER value: 'foo'
4454 Warning 1292 Truncated incorrect INTEGER value: 'foo'
4455 Warning 1292 Truncated incorrect DOUBLE value: 'bar'
4456 Warning 1292 Truncated incorrect DOUBLE value: 'bar'
4457 drop table t1;
4458 #
4459 #
4460 # End of 10.2 tests
4461 #
4462 #
4463 # MDEV-16489 when lead() returns null on a datetime field, the result is treated as the literal string '[NULL]'
4464 #
4465 CREATE TABLE t1 (d datetime);
4466 INSERT INTO t1 VALUES ('2018-01-01 00:00:00'),('2018-02-01 00:00:00');
4467 SELECT *, LEAD(d) OVER (ORDER BY d) AS x FROM t1;
4468 d x
4469 2018-01-01 00:00:00 2018-02-01 00:00:00
4470 2018-02-01 00:00:00 NULL
4471 DROP TABLE t1;
4472 CREATE TABLE t1 (d time);
4473 INSERT INTO t1 VALUES ('00:00:01'),('00:00:02');
4474 SELECT *, LEAD(d) OVER (ORDER BY d) AS x FROM t1;
4475 d x
4476 00:00:01 00:00:02
4477 00:00:02 NULL
4478 DROP TABLE t1;
4479 #
4480 # MDEV-20351 Window function BIT_OR() OVER (..) return a wrong data type
4481 #
4482 CREATE TABLE t1 (pk INT, a INT, b BIGINT UNSIGNED);
4483 INSERT INTO t1 VALUES (1, 0, 1), (2, 0, 18446744073709551615);
4484 CREATE TABLE t2 AS
4485 SELECT pk, a, bit_or(b) AS bit_or FROM t1 GROUP BY pk;
4486 SHOW CREATE TABLE t2;
4487 Table Create Table
4488 t2 CREATE TABLE `t2` (
4489 `pk` int(11) DEFAULT NULL,
4490 `a` int(11) DEFAULT NULL,
4491 `bit_or` bigint(21) unsigned NOT NULL
4492 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
4493 SELECT * FROM t1;
4494 pk a b
4495 1 0 1
4496 2 0 18446744073709551615
4497 DROP TABLE t2;
4498 CREATE OR REPLACE TABLE t2 AS
4499 SELECT pk, a, BIT_OR(b) OVER (PARTITION BY a ORDER BY pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS bit_or
4500 FROM t1;
4501 SHOW CREATE TABLE t2;
4502 Table Create Table
4503 t2 CREATE TABLE `t2` (
4504 `pk` int(11) DEFAULT NULL,
4505 `a` int(11) DEFAULT NULL,
4506 `bit_or` bigint(21) unsigned NOT NULL
4507 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
4508 SELECT * FROM t2;
4509 pk a bit_or
4510 1 0 18446744073709551615
4511 2 0 18446744073709551615
4512 DROP TABLE t2;
4513 DROP TABLE t1;
4514 #
4515 # MDEV-15178: Filesort::make_sortorder: Assertion `pos->field != __null |
4516 #
4517 CREATE TABLE t1 (i1 int, a int);
4518 INSERT INTO t1 VALUES (1, 1), (2, 2),(3, 3);
4519 CREATE TABLE t2 (i2 int);
4520 INSERT INTO t2 VALUES (1),(2),(5),(1),(7),(4),(3);
4521 SELECT
4522 a,
4523 RANK() OVER (ORDER BY SUM(DISTINCT i1))
4524 FROM
4525 t1, t2 WHERE t2.i2 = t1.i1
4526 GROUP BY
4527 a;
4528 a RANK() OVER (ORDER BY SUM(DISTINCT i1))
4529 1 1
4530 2 2
4531 3 3
4532 DROP TABLE t1, t2;
4533 #
4534 # MDEV-17014: Crash server using ROW_NUMBER() OVER (PARTITION ..)
4535 #
4536 CREATE TABLE t1 (UID BIGINT);
4537 CREATE TABLE t2 (UID BIGINT);
4538 CREATE TABLE t3 (UID BIGINT);
4539 insert into t1 VALUES (1),(2);
4540 insert into t2 VALUES (1),(2);
4541 insert into t3 VALUES (1),(2);
4542 SELECT
4543 ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID))
4544 FROM t1 TT1,
4545 t2 TT2,
4546 t3 TT3
4547 WHERE TT3.UID = TT1.UID AND TT2.UID = TT3.UID
4548 GROUP BY TT1.UID
4549 ;
4550 ROW_NUMBER() OVER (PARTITION BY GROUP_CONCAT(TT1.UID))
4551 1
4552 1
4553 DROP TABLE t1, t2, t3;
4554 #
4555 # End of 10.3 tests
4556 #
4557 #
4558 # MDEV-16722: Assertion `type() != NULL_ITEM' failed
4559 #
4560 create table t1 (a int);
4561 insert into t1 values (1),(2),(3);
4562 SELECT row_number() OVER (order by a) FROM t1 order by NAME_CONST('myname',NULL);
4563 row_number() OVER (order by a)
4564 1
4565 2
4566 3
4567 drop table t1;
4568 #
4569 # MDEV-29307: join of 2 derived tables over the same grouping view such
4570 # that the first of the joined tables contains a window
4571 # function and the view's specification contains a subquery
4572 # with a set function aggregated on the top level
4573 #
4574 CREATE TABLE t1 (
4575 tst int NOT NULL,
4576 flat tinyint unsigned NOT NULL,
4577 type tinyint unsigned NOT NULL,
4578 val int NOT NULL,
4579 PRIMARY KEY (tst,flat,type)
4580 ) ENGINE=ARIA;
4581 INSERT INTO t1 VALUES
4582 (5, 20, 2, 100),
4583 (7, 20, 2, 150),
4584 (9, 20, 1, 200);
4585 CREATE VIEW v1 AS (
4586 SELECT
4587 flat,
4588 type,
4589 ( SELECT val FROM t1 sw
4590 WHERE sw.tst = MAX(w.tst) AND sw.flat = w.flat AND sw.type = w.type)
4591 AS total
4592 FROM t1 w
4593 GROUP BY flat, type
4594 );
4595 EXPLAIN EXTENDED SELECT w2.total AS w2_total, w1.total AS w1_total
4596 FROM
4597 (
4598 SELECT flat, type, total
4599 FROM v1
4600 WHERE type = 1
4601 ) AS w1
4602 JOIN
4603 (
4604 SELECT flat, type, total
4605 FROM v1
4606 WHERE type = 2
4607 ) AS w2
4608 ON w1.flat = w2.flat;
4609 id select_type table type possible_keys key key_len ref rows filtered Extra
4610 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 3 100.00 Using where
4611 1 PRIMARY <derived6> ref key0 key0 1 v1.flat 1 100.00 Using where
4612 6 DERIVED w index NULL PRIMARY 6 NULL 3 100.00 Using where; Using index; Using temporary; Using filesort
4613 7 DEPENDENT SUBQUERY sw eq_ref PRIMARY PRIMARY 6 func,func,func 1 100.00 Using index condition
4614 4 DERIVED w index NULL PRIMARY 6 NULL 3 100.00 Using where; Using index; Using temporary; Using filesort
4615 5 DEPENDENT SUBQUERY sw eq_ref PRIMARY PRIMARY 6 func,func,func 1 100.00 Using index condition
4616 Warnings:
4617 Note 1276 Field or reference 'test.w.tst' of SELECT #5 was resolved in SELECT #4
4618 Note 1981 Aggregate function 'max()' of SELECT #5 belongs to SELECT #4
4619 Note 1276 Field or reference 'test.w.flat' of SELECT #5 was resolved in SELECT #4
4620 Note 1276 Field or reference 'test.w.type' of SELECT #5 was resolved in SELECT #4
4621 Note 1276 Field or reference 'test.w.tst' of SELECT #7 was resolved in SELECT #6
4622 Note 1981 Aggregate function 'max()' of SELECT #7 belongs to SELECT #6
4623 Note 1276 Field or reference 'test.w.flat' of SELECT #7 was resolved in SELECT #6
4624 Note 1276 Field or reference 'test.w.type' of SELECT #7 was resolved in SELECT #6
4625 Note 1003 /* select#1 */ select `v1`.`total` AS `w2_total`,`v1`.`total` AS `w1_total` from `test`.`v1` join `test`.`v1` where `v1`.`flat` = `v1`.`flat` and `v1`.`type` = 2 and `v1`.`type` = 1
4626 SELECT w2.total AS w2_total, w1.total AS w1_total
4627 FROM
4628 (
4629 SELECT flat, type, total
4630 FROM v1
4631 WHERE type = 1
4632 ) AS w1
4633 JOIN
4634 (
4635 SELECT flat, type, total
4636 FROM v1
4637 WHERE type = 2
4638 ) AS w2
4639 ON w1.flat = w2.flat;
4640 w2_total w1_total
4641 150 200
4642 EXPLAIN EXTENDED SELECT w2.total AS w2_total, w1.total AS w1_total
4643 FROM
4644 (
4645 SELECT flat, type, total,
4646 COUNT(total) OVER (PARTITION BY type ORDER BY type) AS u
4647 FROM v1
4648 WHERE type = 1
4649 ) AS w1
4650 JOIN
4651 (
4652 SELECT flat, type, total
4653 FROM v1
4654 WHERE type = 2
4655 ) AS w2
4656 ON w1.flat = w2.flat;
4657 id select_type table type possible_keys key key_len ref rows filtered Extra
4658 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00 Using where
4659 1 PRIMARY <derived6> ref key0 key0 1 w1.flat 1 100.00 Using where
4660 6 DERIVED w index NULL PRIMARY 6 NULL 3 100.00 Using where; Using index; Using temporary; Using filesort
4661 7 DEPENDENT SUBQUERY sw eq_ref PRIMARY PRIMARY 6 func,func,func 1 100.00 Using index condition
4662 2 DERIVED <derived4> ALL NULL NULL NULL NULL 3 100.00 Using where; Using temporary
4663 4 DERIVED w index NULL PRIMARY 6 NULL 3 100.00 Using where; Using index; Using temporary; Using filesort
4664 5 DEPENDENT SUBQUERY sw eq_ref PRIMARY PRIMARY 6 func,func,func 1 100.00 Using index condition
4665 Warnings:
4666 Note 1276 Field or reference 'test.w.tst' of SELECT #5 was resolved in SELECT #4
4667 Note 1981 Aggregate function 'max()' of SELECT #5 belongs to SELECT #4
4668 Note 1276 Field or reference 'test.w.flat' of SELECT #5 was resolved in SELECT #4
4669 Note 1276 Field or reference 'test.w.type' of SELECT #5 was resolved in SELECT #4
4670 Note 1276 Field or reference 'test.w.tst' of SELECT #7 was resolved in SELECT #6
4671 Note 1981 Aggregate function 'max()' of SELECT #7 belongs to SELECT #6
4672 Note 1276 Field or reference 'test.w.flat' of SELECT #7 was resolved in SELECT #6
4673 Note 1276 Field or reference 'test.w.type' of SELECT #7 was resolved in SELECT #6
4674 Note 1003 /* select#1 */ select `v1`.`total` AS `w2_total`,`w1`.`total` AS `w1_total` from (/* select#2 */ select `v1`.`flat` AS `flat`,`v1`.`type` AS `type`,`v1`.`total` AS `total`,count(`v1`.`total`) over ( partition by `v1`.`type` order by `v1`.`type`) AS `u` from `test`.`v1` where `v1`.`type` = 1) `w1` join `test`.`v1` where `v1`.`flat` = `w1`.`flat` and `v1`.`type` = 2
4675 SELECT w2.total AS w2_total, w1.total AS w1_total
4676 FROM
4677 (
4678 SELECT flat, type, total,
4679 COUNT(total) OVER (PARTITION BY type ORDER BY type) AS u
4680 FROM v1
4681 WHERE type = 1
4682 ) AS w1
4683 JOIN
4684 (
4685 SELECT flat, type, total
4686 FROM v1
4687 WHERE type = 2
4688 ) AS w2
4689 ON w1.flat = w2.flat;
4690 w2_total w1_total
4691 150 200
4692 EXPLAIN EXTENDED SELECT w2.total AS w2_total, w1.total AS w1_total, u
4693 FROM
4694 (
4695 SELECT flat, type, total,
4696 COUNT(total) OVER (PARTITION BY flat ORDER BY flat) AS u
4697 FROM v1
4698 ) AS w1
4699 JOIN
4700 (
4701 SELECT flat, type, total
4702 FROM v1
4703 ) AS w2;
4704 id select_type table type possible_keys key key_len ref rows filtered Extra
4705 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 100.00
4706 1 PRIMARY <derived6> ALL NULL NULL NULL NULL 3 100.00 Using join buffer (flat, BNL join)
4707 6 DERIVED w index NULL PRIMARY 6 NULL 3 100.00 Using index; Using temporary; Using filesort
4708 7 DEPENDENT SUBQUERY sw eq_ref PRIMARY PRIMARY 6 func,func,func 1 100.00 Using index condition
4709 2 DERIVED <derived4> ALL NULL NULL NULL NULL 3 100.00 Using temporary
4710 4 DERIVED w index NULL PRIMARY 6 NULL 3 100.00 Using index; Using temporary; Using filesort
4711 5 DEPENDENT SUBQUERY sw eq_ref PRIMARY PRIMARY 6 func,func,func 1 100.00 Using index condition
4712 Warnings:
4713 Note 1276 Field or reference 'test.w.tst' of SELECT #5 was resolved in SELECT #4
4714 Note 1981 Aggregate function 'max()' of SELECT #5 belongs to SELECT #4
4715 Note 1276 Field or reference 'test.w.flat' of SELECT #5 was resolved in SELECT #4
4716 Note 1276 Field or reference 'test.w.type' of SELECT #5 was resolved in SELECT #4
4717 Note 1276 Field or reference 'test.w.tst' of SELECT #7 was resolved in SELECT #6
4718 Note 1981 Aggregate function 'max()' of SELECT #7 belongs to SELECT #6
4719 Note 1276 Field or reference 'test.w.flat' of SELECT #7 was resolved in SELECT #6
4720 Note 1276 Field or reference 'test.w.type' of SELECT #7 was resolved in SELECT #6
4721 Note 1003 /* select#1 */ select `v1`.`total` AS `w2_total`,`w1`.`total` AS `w1_total`,`w1`.`u` AS `u` from (/* select#2 */ select `v1`.`flat` AS `flat`,`v1`.`type` AS `type`,`v1`.`total` AS `total`,count(`v1`.`total`) over ( partition by `v1`.`flat` order by `v1`.`flat`) AS `u` from `test`.`v1`) `w1` join `test`.`v1`
4722 SELECT w2.total AS w2_total, w1.total AS w1_total, u
4723 FROM
4724 (
4725 SELECT flat, type, total,
4726 COUNT(total) OVER (PARTITION BY flat ORDER BY flat) AS u
4727 FROM v1
4728 ) AS w1
4729 JOIN
4730 (
4731 SELECT flat, type, total
4732 FROM v1
4733 ) AS w2;
4734 w2_total w1_total u
4735 150 150 2
4736 150 200 2
4737 200 150 2
4738 200 200 2
4739 DROP VIEW v1;
4740 DROP TABLE t1;
4741 #
4742 # MDEV-35869: degenerated subquery with window function
4743 #
4744 CREATE TABLE t1 (a int DEFAULT 10);
4745 INSERT INTO t1 VALUES (7), (2), (3);
4746 SELECT * FROM t1 WHERE (SELECT AVG(3)) > 2;
4747 a
4748 7
4749 2
4750 3
4751 SELECT * FROM t1 WHERE (SELECT AVG(3) OVER ()) > 2;
4752 a
4753 7
4754 2
4755 3
4756 INSERT INTO t1 VALUES((SELECT avg(4) OVER ()));
4757 SELECT * FROM t1;
4758 a
4759 7
4760 2
4761 3
4762 4
4763 DROP TABLE t1;
4764 # End of 10.5 tests
4765 #
4766 # MDEV-28206 SIGSEGV in Item_field::fix_fields when using LEAD...OVER
4767 #
4768 CREATE TABLE t(c1 INT);
4769 CREATE FUNCTION f() RETURNS INT READS SQL DATA BEGIN
4770 DECLARE v INT;
4771 SELECT 1 INTO v FROM (SELECT c1,COALESCE(LEAD(a2.c1) OVER (PARTITION BY a2.c1 ORDER BY a2.c1),a2.c1) AS a1 FROM (t a2 JOIN t a3 USING (c1))) a4;
4772 RETURN 1;
4773 END//
4774 SELECT f(),f();
4775 f() f()
4776 1 1
4777 EXECUTE IMMEDIATE "SELECT LEAD(c1) OVER (ORDER BY c1) FROM t NATURAL JOIN t AS a;";
4778 LEAD(c1) OVER (ORDER BY c1)
4779 EXECUTE IMMEDIATE "SELECT SUM(c1) OVER (ORDER BY c1) FROM t NATURAL JOIN t AS a;";
4780 SUM(c1) OVER (ORDER BY c1)
4781 EXECUTE IMMEDIATE "SELECT LEAD(c) OVER (ORDER BY c) FROM (SELECT 1 AS c) AS a NATURAL JOIN (SELECT 1 AS c) AS b;";
4782 LEAD(c) OVER (ORDER BY c)
4783 NULL
4784 DROP FUNCTION f;
4785 DROP TABLE t;
4786 #
4787 # MDEV-31296: Crash in Item_func::fix_fields when prepared statement
4788 # with subqueries and window function is executed with
4789 # sql_mode = ONLY_FULL_GROUP_BY
4790 #
4791 CREATE TABLE t1 ( a INT, i INT) ;
4792 CREATE TABLE t2 ( a INT);
4793 INSERT INTO t2 VALUES (4000);
4794 SET SESSION sql_mode = "ONLY_FULL_GROUP_BY";
4795 EXECUTE IMMEDIATE "SELECT SUM(i) OVER (ORDER BY i) FROM t1 NATURAL JOIN t2";
4796 SUM(i) OVER (ORDER BY i)
4797 # Clean up
4798 DROP TABLE t1, t2;
4799 #
4800 # End of 10.6 tests
4801 #
4802 #
4803 # MDEV-31744: Assertion `order_list->elements == 1' failure
4804 # during Frame_range_n_bottom object creation
4805 #
4806 CREATE TABLE t1 (a int, b int, c int, d int) ENGINE=MyISAM;
4807 INSERT INTO t1 VALUES (1,1,1,1);
4808 CREATE VIEW v1 AS SELECT * FROM t1;
4809 SELECT
4810 LEAD(a) OVER (ORDER BY b, c) AS c1,
4811 COUNT(*) OVER (ORDER BY d RANGE BETWEEN CURRENT ROW AND 5 FOLLOWING) AS c2
4812 FROM v1;
4813 c1 c2
4814 NULL 1
4815 #
4816 # test similar query as above for Frame_range_n_top
4817 #
4818 SELECT
4819 LEAD(a) OVER (ORDER BY b, c) AS c1,
4820 COUNT(*) OVER (ORDER BY d RANGE BETWEEN 5 PRECEDING AND CURRENT ROW) AS c2
4821 FROM v1;
4822 c1 c2
4823 NULL 1
4824 DROP TABLE t1;
4825 DROP VIEW v1;
4826 #
4827 # test queries as above but on the table instead of view
4828 #
4829 CREATE TABLE t1 (a int) ENGINE=MyISAM;
4830 INSERT INTO t1 values (1), (2);
4831 SELECT
4832 COUNT(*) OVER (ORDER BY 'abc', a) AS c1,
4833 COUNT(*) OVER (ORDER BY a RANGE BETWEEN CURRENT ROW AND 5 FOLLOWING) AS c2
4834 from t1;
4835 c1 c2
4836 1 2
4837 2 1
4838 #
4839 # similar query on table with Frame_range_n_top
4840 #
4841 SELECT
4842 COUNT(*) OVER (ORDER BY 'abc', a) AS c1,
4843 COUNT(*) OVER (ORDER BY a RANGE BETWEEN 5 PRECEDING AND CURRENT ROW) AS c2
4844 from t1;
4845 c1 c2
4846 1 1
4847 2 2
4848 DROP TABLE t1;
4849 #
4850 # End of 10.11 tests
4851 #
4852 #
4853 # MDEV-23867: select crash in compute_window_func
4854 #
4855 set @save_sort_buffer_size=@@sort_buffer_size;
4856 set sort_buffer_size= 2000;
4857 CREATE TABLE t1( a INT, b INT, c INT);
4858 INSERT INTO t1 select seq, seq, seq from seq_1_to_5000;
4859 CREATE TABLE t2( a INT, b INT, c INT);
4860 INSERT INTO t2 SELECT a, b, ROW_NUMBER() OVER (PARTITION BY b) FROM t1;
4861 SELECT COUNT(*), MAX(c) FROM t2;
4862 COUNT(*) MAX(c)
4863 5000 1
4864 CREATE TABLE t3( a INT, b INT, c INT);
4865 INSERT INTO t3 SELECT a, b, SUM(a) OVER () FROM t1;
4866 SELECT COUNT(*), MAX(c) FROM t3;
4867 COUNT(*) MAX(c)
4868 5000 12502500
4869 set @@sort_buffer_size=@save_sort_buffer_size;
4870 DROP TABLE t1,t2,t3;
4871 # end of 10.2 test
4872 #
4873 # MDEV-22556: Incorrect result for window function when using encrypt-tmp-files=ON
4874 #
4875 set @save_sort_buffer_size=@@sort_buffer_size;
4876 set sort_buffer_size= 2000;
4877 create table t1( a DECIMAL(12,0) DEFAULT NULL, b VARCHAR(20) DEFAULT NULL, c DECIMAL(12,0) DEFAULT NULL)engine=INNODB;
4878 insert into t1 select seq, seq, seq from seq_1_to_5000;
4879 select count(*) from (select a, b, c, ROW_NUMBER() OVER (PARTITION BY a) FROM t1)q;
4880 count(*)
4881 5000
4882 set @@sort_buffer_size=@save_sort_buffer_size;
4883 drop table t1;
4884 # End of 10.4 tests