810 lines
74 KiB
Plaintext
810 lines
74 KiB
Plaintext
SET SESSION windowing_use_high_precision=FALSE;
|
|
CREATE TABLE t(i INT, li BIGINT, b VARBINARY(8), lb VARBINARY(24), d DOUBLE GENERATED ALWAYS AS (i*1.0));
|
|
INSERT INTO t(i, li, b, lb) VALUES
|
|
(NULL, NULL, NULL, NULL),
|
|
(0, 0, X'0000000000000000',
|
|
X'000000000000000000000000000000000000000000000000'),
|
|
(0, 0, X'0000000000000000',
|
|
X'000000000000000000000000000000000000000000000000'),
|
|
(1, 1, X'0000000000000001',
|
|
X'000000000000000000000000000000000000000000000001'),
|
|
(1, 1, X'0000000000000001',
|
|
X'000000000000000000000000000000000000000000000001'),
|
|
(2, 2, X'0000000000000002',
|
|
X'000000000000000000000000000000000000000000000002'),
|
|
(2, 2, X'0000000000000002',
|
|
X'000000000000000000000000000000000000000000000002'),
|
|
(3, 3, X'0000000000000003',
|
|
X'000000000000000000000000000000000000000000000003'),
|
|
(3, 3, X'0000000000000003',
|
|
X'000000000000000000000000000000000000000000000003'),
|
|
(4, 4, X'0000000000000004',
|
|
X'000000000000000000000000000000000000000000000004'),
|
|
(4, 4, X'0000000000000004',
|
|
X'000000000000000000000000000000000000000000000004'),
|
|
(5, 5, X'0000000000000005',
|
|
X'000000000000000000000000000000000000000000000005'),
|
|
(6, 6, X'0000000000000006',
|
|
X'000000000000000000000000000000000000000000000006'),
|
|
(X'01234567', X'0123456789ABCDEF', X'0123456789ABCDEF',
|
|
X'000000000000000000000000000000000123456789ABCDEF'),
|
|
(X'01200567', X'0120056789ABCDEF', X'0120056789ABCDEF',
|
|
X'0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF'),
|
|
(X'01200567', X'0120056789ABCDEF', X'0120056789ABCDEF',
|
|
X'0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF'),
|
|
(X'01200117', X'0120011789ABCDEF', X'0120011789ABCDEF',
|
|
X'0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF');
|
|
Ascending
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_OR(i) OVER w), HEX(BIT_OR(li) OVER w),
|
|
HEX(BIT_OR(b) OVER w), HEX(BIT_OR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_OR(i) OVER w) HEX(BIT_OR(li) OVER w) HEX(BIT_OR(b) OVER w) HEX(BIT_OR(lb) OVER w) dsum
|
|
NULL NULL NULL NULL 0 0 0000000000000000 000000000000000000000000000000000000000000000000 NULL
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 1
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 2
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 3 3 0000000000000003 000000000000000000000000000000000000000000000003 4
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 3 3 0000000000000003 000000000000000000000000000000000000000000000003 5
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 7
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 8
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 7 7 0000000000000007 000000000000000000000000000000000000000000000007 10
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 7 7 0000000000000007 000000000000000000000000000000000000000000000007 11
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 5 5 0000000000000005 000000000000000000000000000000000000000000000005 13
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 7 7 0000000000000007 000000000000000000000000000000000000000000000007 15
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 18874658
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200577 120057789ABCDEF 0120057789ABCDEF 0120057789ABCDEF0120057789ABCDEF0120057789ABCDEF 37750404
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200577 120057789ABCDEF 0120057789ABCDEF 0120057789ABCDEF0120057789ABCDEF0120057789ABCDEF 56626149
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1234567 123456789ABCDEF 0123456789ABCDEF 0120056789ABCDEF0120056789ABCDEF0123456789ABCDEF 56840245
|
|
Show whether we run in optimized or non-optimized mode, cf.
|
|
our dummy double d SUM which forces non-optimized mode
|
|
iff windowing_use_high_precision==TRUE
|
|
ANALYZE TABLE t;
|
|
Table Op Msg_type Msg_text
|
|
test.t analyze status OK
|
|
EXPLAIN FORMAT=JSON
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_OR(i) OVER w), HEX(BIT_OR(li) OVER w),
|
|
HEX(BIT_OR(b) OVER w), HEX(BIT_OR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "18.95"
|
|
},
|
|
"windowing": {
|
|
"windows": [
|
|
{
|
|
"name": "w",
|
|
"using_filesort": true,
|
|
"filesort_key": [
|
|
"`i`"
|
|
],
|
|
"frame_buffer": {
|
|
"using_temporary_table": true,
|
|
"optimized_frame_evaluation": true
|
|
},
|
|
"functions": [
|
|
"bit_or",
|
|
"bit_or",
|
|
"bit_or",
|
|
"bit_or",
|
|
"sum"
|
|
]
|
|
}
|
|
],
|
|
"cost_info": {
|
|
"sort_cost": "17.00"
|
|
},
|
|
"table": {
|
|
"table_name": "t",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 17,
|
|
"rows_produced_per_join": 17,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "0.25",
|
|
"eval_cost": "1.70",
|
|
"prefix_cost": "1.95",
|
|
"data_read_per_join": "952"
|
|
},
|
|
"used_columns": [
|
|
"i",
|
|
"li",
|
|
"b",
|
|
"lb",
|
|
"d"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select hex(`test`.`t`.`i`) AS `HEX(i)`,hex(`test`.`t`.`li`) AS `HEX(li)`,hex(`test`.`t`.`b`) AS `HEX(b)`,hex(`test`.`t`.`lb`) AS `HEX(lb)`,hex(bit_or(`test`.`t`.`i`) OVER `w`) AS `HEX(BIT_OR(i) OVER w)`,hex(bit_or(`test`.`t`.`li`) OVER `w`) AS `HEX(BIT_OR(li) OVER w)`,hex(bit_or(`test`.`t`.`b`) OVER `w`) AS `HEX(BIT_OR(b) OVER w)`,hex(bit_or(`test`.`t`.`lb`) OVER `w`) AS `HEX(BIT_OR(lb) OVER w)`,sum(`test`.`t`.`d`) OVER `w` AS `dsum` from `test`.`t` window `w` AS (ORDER BY `test`.`t`.`i` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_XOR(i) OVER w), HEX(BIT_XOR(li) OVER w),
|
|
HEX(BIT_XOR(b) OVER w), HEX(BIT_XOR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_XOR(i) OVER w) HEX(BIT_XOR(li) OVER w) HEX(BIT_XOR(b) OVER w) HEX(BIT_XOR(lb) OVER w) dsum
|
|
NULL NULL NULL NULL 0 0 0000000000000000 000000000000000000000000000000000000000000000000 NULL
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 1
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 2
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 2 2 0000000000000002 000000000000000000000000000000000000000000000002 4
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 1 1 0000000000000001 000000000000000000000000000000000000000000000001 5
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 7
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 8
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 4 4 0000000000000004 000000000000000000000000000000000000000000000004 10
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 3 3 0000000000000003 000000000000000000000000000000000000000000000003 11
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 5 5 0000000000000005 000000000000000000000000000000000000000000000005 13
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 7 7 0000000000000007 000000000000000000000000000000000000000000000007 15
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 1200114 120011789ABCDEC 0120011789ABCDEC 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEC 18874658
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 476 47000000006 0000047000000006 000004700000000000000470000000000000047000000006 37750404
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 56626149
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 56840245
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_AND(i) OVER w), HEX(BIT_AND(li) OVER w),
|
|
HEX(BIT_AND(b) OVER w), HEX(BIT_AND(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_AND(i) OVER w) HEX(BIT_AND(li) OVER w) HEX(BIT_AND(b) OVER w) HEX(BIT_AND(lb) OVER w) dsum
|
|
NULL NULL NULL NULL FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF NULL
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 1
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 2
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 0 0 0000000000000000 000000000000000000000000000000000000000000000000 4
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 0 0 0000000000000000 000000000000000000000000000000000000000000000000 5
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 7
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 8
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 0 0 0000000000000000 000000000000000000000000000000000000000000000000 10
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 0 0 0000000000000000 000000000000000000000000000000000000000000000000 11
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 4 4 0000000000000004 000000000000000000000000000000000000000000000004 13
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 4 4 0000000000000004 000000000000000000000000000000000000000000000004 15
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 4 4 0000000000000004 000000000000000000000000000000000000000000000004 18874658
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 6 6 0000000000000006 000000000000000000000000000000000000000000000006 37750404
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200107 120010789ABCDEF 0120010789ABCDEF 0120010789ABCDEF0120010789ABCDEF0120010789ABCDEF 56626149
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1200567 120056789ABCDEF 0120056789ABCDEF 000000000000000000000000000000000120056789ABCDEF 56840245
|
|
Descending: NULLS last
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_OR(i) OVER w), HEX(BIT_OR(li) OVER w),
|
|
HEX(BIT_OR(b) OVER w), HEX(BIT_OR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_OR(i) OVER w) HEX(BIT_OR(li) OVER w) HEX(BIT_OR(b) OVER w) HEX(BIT_OR(lb) OVER w) dsum
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1234567 123456789ABCDEF 0123456789ABCDEF 0120056789ABCDEF0120056789ABCDEF0123456789ABCDEF 56840245
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200577 120057789ABCDEF 0120057789ABCDEF 0120057789ABCDEF0120057789ABCDEF0120057789ABCDEF 56626149
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200577 120057789ABCDEF 0120057789ABCDEF 0120057789ABCDEF0120057789ABCDEF0120057789ABCDEF 37750404
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 18874658
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 7 7 0000000000000007 000000000000000000000000000000000000000000000007 15
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 5 5 0000000000000005 000000000000000000000000000000000000000000000005 13
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 7 7 0000000000000007 000000000000000000000000000000000000000000000007 11
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 7 7 0000000000000007 000000000000000000000000000000000000000000000007 10
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 8
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 7
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 3 3 0000000000000003 000000000000000000000000000000000000000000000003 5
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 3 3 0000000000000003 000000000000000000000000000000000000000000000003 4
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 2
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 1
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
NULL NULL NULL NULL 0 0 0000000000000000 000000000000000000000000000000000000000000000000 NULL
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_XOR(i) OVER w), HEX(BIT_XOR(li) OVER w),
|
|
HEX(BIT_XOR(b) OVER w), HEX(BIT_XOR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_XOR(i) OVER w) HEX(BIT_XOR(li) OVER w) HEX(BIT_XOR(b) OVER w) HEX(BIT_XOR(lb) OVER w) dsum
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 56840245
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 56626149
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 476 47000000006 0000047000000006 000004700000000000000470000000000000047000000006 37750404
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 1200114 120011789ABCDEC 0120011789ABCDEC 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEC 18874658
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 7 7 0000000000000007 000000000000000000000000000000000000000000000007 15
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 5 5 0000000000000005 000000000000000000000000000000000000000000000005 13
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 3 3 0000000000000003 000000000000000000000000000000000000000000000003 11
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 4 4 0000000000000004 000000000000000000000000000000000000000000000004 10
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 8
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 7
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 1 1 0000000000000001 000000000000000000000000000000000000000000000001 5
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 2 2 0000000000000002 000000000000000000000000000000000000000000000002 4
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 2
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 1
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
NULL NULL NULL NULL 0 0 0000000000000000 000000000000000000000000000000000000000000000000 NULL
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_AND(i) OVER w), HEX(BIT_AND(li) OVER w),
|
|
HEX(BIT_AND(b) OVER w), HEX(BIT_AND(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_AND(i) OVER w) HEX(BIT_AND(li) OVER w) HEX(BIT_AND(b) OVER w) HEX(BIT_AND(lb) OVER w) dsum
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1200567 120056789ABCDEF 0120056789ABCDEF 000000000000000000000000000000000120056789ABCDEF 56840245
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200107 120010789ABCDEF 0120010789ABCDEF 0120010789ABCDEF0120010789ABCDEF0120010789ABCDEF 56626149
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 6 6 0000000000000006 000000000000000000000000000000000000000000000006 37750404
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 4 4 0000000000000004 000000000000000000000000000000000000000000000004 18874658
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 4 4 0000000000000004 000000000000000000000000000000000000000000000004 15
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 4 4 0000000000000004 000000000000000000000000000000000000000000000004 13
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 0 0 0000000000000000 000000000000000000000000000000000000000000000000 11
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 0 0 0000000000000000 000000000000000000000000000000000000000000000000 10
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 8
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 7
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 0 0 0000000000000000 000000000000000000000000000000000000000000000000 5
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 0 0 0000000000000000 000000000000000000000000000000000000000000000000 4
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 2
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 1
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
NULL NULL NULL NULL FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF NULL
|
|
|
|
Test that varying length string give an error if in same partition
|
|
|
|
Insert a short b at end of partition
|
|
INSERT INTO t(i, li, b, lb) VALUES
|
|
(10, 10, x'0A', x'00000000000000000000000000000000000000000000000A');
|
|
SELECT HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
ERROR HY000: Binary operands of bitwise operators must be of equal length
|
|
DELETE FROM t WHERE i = 10;
|
|
Insert a short b at start of partition
|
|
INSERT INTO t(i, li, b, lb) VALUES
|
|
(-1, -1, x'0A', x'00000000000000000000000000000000000000000000000A');
|
|
SELECT HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
ERROR HY000: Binary operands of bitwise operators must be of equal length
|
|
DELETE FROM t WHERE i = -1;
|
|
Insert a short b in middle of partition
|
|
INSERT INTO t(i, li, b, lb) VALUES
|
|
(4, 4, x'04', x'000000000000000000000000000000000000000000000004');
|
|
SELECT HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
ERROR HY000: Binary operands of bitwise operators must be of equal length
|
|
Partitioning on bit string length should make it work again
|
|
SELECT HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (PARTITION BY LENGTH(b) ORDER BY i ROWS 2 PRECEDING);
|
|
HEX(BIT_AND(b) OVER w) dsum
|
|
FFFFFFFFFFFFFFFF NULL
|
|
04 4
|
|
0000000000000000 0
|
|
0000000000000000 0
|
|
0000000000000000 1
|
|
0000000000000000 2
|
|
0000000000000000 4
|
|
0000000000000000 5
|
|
0000000000000002 7
|
|
0000000000000002 8
|
|
0000000000000000 10
|
|
0000000000000000 11
|
|
0000000000000004 13
|
|
0000000000000004 15
|
|
0000000000000004 18874658
|
|
0000000000000006 37750404
|
|
0120010789ABCDEF 56626149
|
|
0120056789ABCDEF 56840245
|
|
DROP TABLE t;
|
|
Test with empty frame at beginning and end: bitops don't get
|
|
NULL then but a "neutral" value
|
|
CREATE TABLE t(
|
|
c0004 LONGTEXT CHARACTER SET latin1 COLLATE latin1_bin
|
|
GENERATED ALWAYS AS (_utf8mb4'1') VIRTUAL NOT NULL,
|
|
d DOUBLE GENERATED ALWAYS AS (1.0))
|
|
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
INSERT INTO t VALUES (), (), (), (), ();
|
|
SELECT c0004,
|
|
BIT_AND(c0004) OVER w,
|
|
BIT_OR(c0004) OVER w,
|
|
BIT_XOR(c0004) OVER w,
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY c0004 ROWS BETWEEN 5 PRECEDING AND 3 PRECEDING);
|
|
c0004 BIT_AND(c0004) OVER w BIT_OR(c0004) OVER w BIT_XOR(c0004) OVER w dsum
|
|
1 18446744073709551615 0 0 NULL
|
|
1 18446744073709551615 0 0 NULL
|
|
1 18446744073709551615 0 0 NULL
|
|
1 1 1 1 1
|
|
1 1 1 0 2
|
|
SELECT c0004,
|
|
BIT_AND(c0004) OVER w,
|
|
BIT_OR(c0004) OVER w,
|
|
BIT_XOR(c0004) OVER w,
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY c0004 ROWS BETWEEN 3 FOLLOWING AND 5 FOLLOWING);
|
|
c0004 BIT_AND(c0004) OVER w BIT_OR(c0004) OVER w BIT_XOR(c0004) OVER w dsum
|
|
1 1 1 0 2
|
|
1 1 1 1 1
|
|
1 18446744073709551615 0 0 NULL
|
|
1 18446744073709551615 0 0 NULL
|
|
1 18446744073709551615 0 0 NULL
|
|
DROP TABLE t;
|
|
CREATE TABLE t(i int, b VARBINARY(511), d DOUBLE GENERATED ALWAYS AS (i*1.0));
|
|
INSERT INTO t(i,b) VALUES (1, REPEAT(x'00', 511)),
|
|
(1, REPEAT(x'FF', 511)),
|
|
(1, REPEAT(x'77', 511)),
|
|
(1, REPEAT(x'11', 511));
|
|
SELECT '---',
|
|
HEX(BIT_OR(b) OVER w) AS `OR`,
|
|
HEX(BIT_AND(b) OVER w) AS `AND`,
|
|
HEX(BIT_XOR(b) OVER w) AS `XOR`,
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
--- ---
|
|
OR 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
AND 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
XOR 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
dsum 1
|
|
--- ---
|
|
OR FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
|
|
AND 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
XOR FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
|
|
dsum 2
|
|
--- ---
|
|
OR FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
|
|
AND 77777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777
|
|
XOR 88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
|
|
dsum 2
|
|
--- ---
|
|
OR 77777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777
|
|
AND 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
|
|
XOR 66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
|
|
dsum 2
|
|
DROP TABLE t;
|
|
CREATE TABLE t(i int, b VARBINARY(512), d DOUBLE GENERATED ALWAYS AS (i*1.0));
|
|
INSERT INTO t(i,b) VALUES (1, REPEAT(x'00', 512)),
|
|
(1, REPEAT(x'FF', 512)),
|
|
(1, REPEAT(x'77', 512)),
|
|
(1, REPEAT(x'11', 512));
|
|
SELECT '---',
|
|
HEX(BIT_OR(b) OVER w) AS `OR`,
|
|
HEX(BIT_AND(b) OVER w) AS `AND`,
|
|
HEX(BIT_XOR(b) OVER w) AS `XOR`,
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
ERROR HY000: Aggregate bitwise functions cannot accept arguments longer than 511 bytes; consider using the SUBSTRING() function
|
|
DROP TABLE t;
|
|
# When switching from a partition to another, Item_sum_bit::clear()
|
|
# is called; it must reset Item_sum::m_digit_cnt if inversion is used
|
|
# inside the new frame.
|
|
CREATE TABLE t (id DECIMAL(3,1), b BINARY(9), d DOUBLE GENERATED ALWAYS AS (id*1.0));
|
|
INSERT INTO t(id, b)
|
|
VALUES (5.0,0x000000000000000000), (7.0,0x070000000000000007),
|
|
(6.0,0xFFFFFFFFFFFFFFFFFF), (5.5,0xFFFFFFFFFFFFFFFFFF);
|
|
SELECT id, id<=6 AS part, HEX(b), HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (PARTITION BY id<=6 ORDER BY id
|
|
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
id part HEX(b) HEX(BIT_AND(b) OVER w) dsum
|
|
7.0 0 070000000000000007 070000000000000007 7
|
|
5.0 1 000000000000000000 000000000000000000 5
|
|
5.5 1 FFFFFFFFFFFFFFFFFF 000000000000000000 10.5
|
|
6.0 1 FFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFF 11.5
|
|
DROP TABLE t;
|
|
SET SESSION windowing_use_high_precision=TRUE;
|
|
CREATE TABLE t(i INT, li BIGINT, b VARBINARY(8), lb VARBINARY(24), d DOUBLE GENERATED ALWAYS AS (i*1.0));
|
|
INSERT INTO t(i, li, b, lb) VALUES
|
|
(NULL, NULL, NULL, NULL),
|
|
(0, 0, X'0000000000000000',
|
|
X'000000000000000000000000000000000000000000000000'),
|
|
(0, 0, X'0000000000000000',
|
|
X'000000000000000000000000000000000000000000000000'),
|
|
(1, 1, X'0000000000000001',
|
|
X'000000000000000000000000000000000000000000000001'),
|
|
(1, 1, X'0000000000000001',
|
|
X'000000000000000000000000000000000000000000000001'),
|
|
(2, 2, X'0000000000000002',
|
|
X'000000000000000000000000000000000000000000000002'),
|
|
(2, 2, X'0000000000000002',
|
|
X'000000000000000000000000000000000000000000000002'),
|
|
(3, 3, X'0000000000000003',
|
|
X'000000000000000000000000000000000000000000000003'),
|
|
(3, 3, X'0000000000000003',
|
|
X'000000000000000000000000000000000000000000000003'),
|
|
(4, 4, X'0000000000000004',
|
|
X'000000000000000000000000000000000000000000000004'),
|
|
(4, 4, X'0000000000000004',
|
|
X'000000000000000000000000000000000000000000000004'),
|
|
(5, 5, X'0000000000000005',
|
|
X'000000000000000000000000000000000000000000000005'),
|
|
(6, 6, X'0000000000000006',
|
|
X'000000000000000000000000000000000000000000000006'),
|
|
(X'01234567', X'0123456789ABCDEF', X'0123456789ABCDEF',
|
|
X'000000000000000000000000000000000123456789ABCDEF'),
|
|
(X'01200567', X'0120056789ABCDEF', X'0120056789ABCDEF',
|
|
X'0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF'),
|
|
(X'01200567', X'0120056789ABCDEF', X'0120056789ABCDEF',
|
|
X'0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF'),
|
|
(X'01200117', X'0120011789ABCDEF', X'0120011789ABCDEF',
|
|
X'0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF');
|
|
Ascending
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_OR(i) OVER w), HEX(BIT_OR(li) OVER w),
|
|
HEX(BIT_OR(b) OVER w), HEX(BIT_OR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_OR(i) OVER w) HEX(BIT_OR(li) OVER w) HEX(BIT_OR(b) OVER w) HEX(BIT_OR(lb) OVER w) dsum
|
|
NULL NULL NULL NULL 0 0 0000000000000000 000000000000000000000000000000000000000000000000 NULL
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 1
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 2
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 3 3 0000000000000003 000000000000000000000000000000000000000000000003 4
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 3 3 0000000000000003 000000000000000000000000000000000000000000000003 5
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 7
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 8
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 7 7 0000000000000007 000000000000000000000000000000000000000000000007 10
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 7 7 0000000000000007 000000000000000000000000000000000000000000000007 11
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 5 5 0000000000000005 000000000000000000000000000000000000000000000005 13
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 7 7 0000000000000007 000000000000000000000000000000000000000000000007 15
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 18874658
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200577 120057789ABCDEF 0120057789ABCDEF 0120057789ABCDEF0120057789ABCDEF0120057789ABCDEF 37750404
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200577 120057789ABCDEF 0120057789ABCDEF 0120057789ABCDEF0120057789ABCDEF0120057789ABCDEF 56626149
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1234567 123456789ABCDEF 0123456789ABCDEF 0120056789ABCDEF0120056789ABCDEF0123456789ABCDEF 56840245
|
|
Show whether we run in optimized or non-optimized mode, cf.
|
|
our dummy double d SUM which forces non-optimized mode
|
|
iff windowing_use_high_precision==TRUE
|
|
ANALYZE TABLE t;
|
|
Table Op Msg_type Msg_text
|
|
test.t analyze status OK
|
|
EXPLAIN FORMAT=JSON
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_OR(i) OVER w), HEX(BIT_OR(li) OVER w),
|
|
HEX(BIT_OR(b) OVER w), HEX(BIT_OR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "18.95"
|
|
},
|
|
"windowing": {
|
|
"windows": [
|
|
{
|
|
"name": "w",
|
|
"using_filesort": true,
|
|
"filesort_key": [
|
|
"`i`"
|
|
],
|
|
"frame_buffer": {
|
|
"using_temporary_table": true
|
|
},
|
|
"functions": [
|
|
"bit_or",
|
|
"bit_or",
|
|
"bit_or",
|
|
"bit_or",
|
|
"sum"
|
|
]
|
|
}
|
|
],
|
|
"cost_info": {
|
|
"sort_cost": "17.00"
|
|
},
|
|
"table": {
|
|
"table_name": "t",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 17,
|
|
"rows_produced_per_join": 17,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "0.25",
|
|
"eval_cost": "1.70",
|
|
"prefix_cost": "1.95",
|
|
"data_read_per_join": "952"
|
|
},
|
|
"used_columns": [
|
|
"i",
|
|
"li",
|
|
"b",
|
|
"lb",
|
|
"d"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select hex(`test`.`t`.`i`) AS `HEX(i)`,hex(`test`.`t`.`li`) AS `HEX(li)`,hex(`test`.`t`.`b`) AS `HEX(b)`,hex(`test`.`t`.`lb`) AS `HEX(lb)`,hex(bit_or(`test`.`t`.`i`) OVER `w`) AS `HEX(BIT_OR(i) OVER w)`,hex(bit_or(`test`.`t`.`li`) OVER `w`) AS `HEX(BIT_OR(li) OVER w)`,hex(bit_or(`test`.`t`.`b`) OVER `w`) AS `HEX(BIT_OR(b) OVER w)`,hex(bit_or(`test`.`t`.`lb`) OVER `w`) AS `HEX(BIT_OR(lb) OVER w)`,sum(`test`.`t`.`d`) OVER `w` AS `dsum` from `test`.`t` window `w` AS (ORDER BY `test`.`t`.`i` ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_XOR(i) OVER w), HEX(BIT_XOR(li) OVER w),
|
|
HEX(BIT_XOR(b) OVER w), HEX(BIT_XOR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_XOR(i) OVER w) HEX(BIT_XOR(li) OVER w) HEX(BIT_XOR(b) OVER w) HEX(BIT_XOR(lb) OVER w) dsum
|
|
NULL NULL NULL NULL 0 0 0000000000000000 000000000000000000000000000000000000000000000000 NULL
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 1
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 2
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 2 2 0000000000000002 000000000000000000000000000000000000000000000002 4
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 1 1 0000000000000001 000000000000000000000000000000000000000000000001 5
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 7
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 8
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 4 4 0000000000000004 000000000000000000000000000000000000000000000004 10
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 3 3 0000000000000003 000000000000000000000000000000000000000000000003 11
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 5 5 0000000000000005 000000000000000000000000000000000000000000000005 13
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 7 7 0000000000000007 000000000000000000000000000000000000000000000007 15
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 1200114 120011789ABCDEC 0120011789ABCDEC 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEC 18874658
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 476 47000000006 0000047000000006 000004700000000000000470000000000000047000000006 37750404
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 56626149
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 56840245
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_AND(i) OVER w), HEX(BIT_AND(li) OVER w),
|
|
HEX(BIT_AND(b) OVER w), HEX(BIT_AND(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_AND(i) OVER w) HEX(BIT_AND(li) OVER w) HEX(BIT_AND(b) OVER w) HEX(BIT_AND(lb) OVER w) dsum
|
|
NULL NULL NULL NULL FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF NULL
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 1
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 2
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 0 0 0000000000000000 000000000000000000000000000000000000000000000000 4
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 0 0 0000000000000000 000000000000000000000000000000000000000000000000 5
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 7
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 8
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 0 0 0000000000000000 000000000000000000000000000000000000000000000000 10
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 0 0 0000000000000000 000000000000000000000000000000000000000000000000 11
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 4 4 0000000000000004 000000000000000000000000000000000000000000000004 13
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 4 4 0000000000000004 000000000000000000000000000000000000000000000004 15
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 4 4 0000000000000004 000000000000000000000000000000000000000000000004 18874658
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 6 6 0000000000000006 000000000000000000000000000000000000000000000006 37750404
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200107 120010789ABCDEF 0120010789ABCDEF 0120010789ABCDEF0120010789ABCDEF0120010789ABCDEF 56626149
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1200567 120056789ABCDEF 0120056789ABCDEF 000000000000000000000000000000000120056789ABCDEF 56840245
|
|
Descending: NULLS last
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_OR(i) OVER w), HEX(BIT_OR(li) OVER w),
|
|
HEX(BIT_OR(b) OVER w), HEX(BIT_OR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_OR(i) OVER w) HEX(BIT_OR(li) OVER w) HEX(BIT_OR(b) OVER w) HEX(BIT_OR(lb) OVER w) dsum
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1234567 123456789ABCDEF 0123456789ABCDEF 0120056789ABCDEF0120056789ABCDEF0123456789ABCDEF 56840245
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200577 120057789ABCDEF 0120057789ABCDEF 0120057789ABCDEF0120057789ABCDEF0120057789ABCDEF 56626149
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200577 120057789ABCDEF 0120057789ABCDEF 0120057789ABCDEF0120057789ABCDEF0120057789ABCDEF 37750404
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 18874658
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 7 7 0000000000000007 000000000000000000000000000000000000000000000007 15
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 5 5 0000000000000005 000000000000000000000000000000000000000000000005 13
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 7 7 0000000000000007 000000000000000000000000000000000000000000000007 11
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 7 7 0000000000000007 000000000000000000000000000000000000000000000007 10
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 8
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 7
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 3 3 0000000000000003 000000000000000000000000000000000000000000000003 5
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 3 3 0000000000000003 000000000000000000000000000000000000000000000003 4
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 2
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 1
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
NULL NULL NULL NULL 0 0 0000000000000000 000000000000000000000000000000000000000000000000 NULL
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_XOR(i) OVER w), HEX(BIT_XOR(li) OVER w),
|
|
HEX(BIT_XOR(b) OVER w), HEX(BIT_XOR(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_XOR(i) OVER w) HEX(BIT_XOR(li) OVER w) HEX(BIT_XOR(b) OVER w) HEX(BIT_XOR(lb) OVER w) dsum
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 56840245
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 56626149
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 476 47000000006 0000047000000006 000004700000000000000470000000000000047000000006 37750404
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 1200114 120011789ABCDEC 0120011789ABCDEC 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEC 18874658
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 7 7 0000000000000007 000000000000000000000000000000000000000000000007 15
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 5 5 0000000000000005 000000000000000000000000000000000000000000000005 13
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 3 3 0000000000000003 000000000000000000000000000000000000000000000003 11
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 4 4 0000000000000004 000000000000000000000000000000000000000000000004 10
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 8
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 3 3 0000000000000003 000000000000000000000000000000000000000000000003 7
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 1 1 0000000000000001 000000000000000000000000000000000000000000000001 5
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 2 2 0000000000000002 000000000000000000000000000000000000000000000002 4
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 2
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 1 1 0000000000000001 000000000000000000000000000000000000000000000001 1
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
NULL NULL NULL NULL 0 0 0000000000000000 000000000000000000000000000000000000000000000000 NULL
|
|
SELECT HEX(i), HEX(li), HEX(b), HEX(lb),
|
|
HEX(BIT_AND(i) OVER w), HEX(BIT_AND(li) OVER w),
|
|
HEX(BIT_AND(b) OVER w), HEX(BIT_AND(lb) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i DESC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
HEX(i) HEX(li) HEX(b) HEX(lb) HEX(BIT_AND(i) OVER w) HEX(BIT_AND(li) OVER w) HEX(BIT_AND(b) OVER w) HEX(BIT_AND(lb) OVER w) dsum
|
|
1234567 123456789ABCDEF 0123456789ABCDEF 000000000000000000000000000000000123456789ABCDEF 1200567 120056789ABCDEF 0120056789ABCDEF 000000000000000000000000000000000120056789ABCDEF 56840245
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 1200107 120010789ABCDEF 0120010789ABCDEF 0120010789ABCDEF0120010789ABCDEF0120010789ABCDEF 56626149
|
|
1200567 120056789ABCDEF 0120056789ABCDEF 0120056789ABCDEF0120056789ABCDEF0120056789ABCDEF 6 6 0000000000000006 000000000000000000000000000000000000000000000006 37750404
|
|
1200117 120011789ABCDEF 0120011789ABCDEF 0120011789ABCDEF0120011789ABCDEF0120011789ABCDEF 4 4 0000000000000004 000000000000000000000000000000000000000000000004 18874658
|
|
6 6 0000000000000006 000000000000000000000000000000000000000000000006 4 4 0000000000000004 000000000000000000000000000000000000000000000004 15
|
|
5 5 0000000000000005 000000000000000000000000000000000000000000000005 4 4 0000000000000004 000000000000000000000000000000000000000000000004 13
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 0 0 0000000000000000 000000000000000000000000000000000000000000000000 11
|
|
4 4 0000000000000004 000000000000000000000000000000000000000000000004 0 0 0000000000000000 000000000000000000000000000000000000000000000000 10
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 8
|
|
3 3 0000000000000003 000000000000000000000000000000000000000000000003 2 2 0000000000000002 000000000000000000000000000000000000000000000002 7
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 0 0 0000000000000000 000000000000000000000000000000000000000000000000 5
|
|
2 2 0000000000000002 000000000000000000000000000000000000000000000002 0 0 0000000000000000 000000000000000000000000000000000000000000000000 4
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 2
|
|
1 1 0000000000000001 000000000000000000000000000000000000000000000001 0 0 0000000000000000 000000000000000000000000000000000000000000000000 1
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
0 0 0000000000000000 000000000000000000000000000000000000000000000000 0 0 0000000000000000 000000000000000000000000000000000000000000000000 0
|
|
NULL NULL NULL NULL FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF NULL
|
|
|
|
Test that varying length string give an error if in same partition
|
|
|
|
Insert a short b at end of partition
|
|
INSERT INTO t(i, li, b, lb) VALUES
|
|
(10, 10, x'0A', x'00000000000000000000000000000000000000000000000A');
|
|
SELECT HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
ERROR HY000: Binary operands of bitwise operators must be of equal length
|
|
DELETE FROM t WHERE i = 10;
|
|
Insert a short b at start of partition
|
|
INSERT INTO t(i, li, b, lb) VALUES
|
|
(-1, -1, x'0A', x'00000000000000000000000000000000000000000000000A');
|
|
SELECT HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
ERROR HY000: Binary operands of bitwise operators must be of equal length
|
|
DELETE FROM t WHERE i = -1;
|
|
Insert a short b in middle of partition
|
|
INSERT INTO t(i, li, b, lb) VALUES
|
|
(4, 4, x'04', x'000000000000000000000000000000000000000000000004');
|
|
SELECT HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS 2 PRECEDING);
|
|
ERROR HY000: Binary operands of bitwise operators must be of equal length
|
|
Partitioning on bit string length should make it work again
|
|
SELECT HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (PARTITION BY LENGTH(b) ORDER BY i ROWS 2 PRECEDING);
|
|
HEX(BIT_AND(b) OVER w) dsum
|
|
FFFFFFFFFFFFFFFF NULL
|
|
04 4
|
|
0000000000000000 0
|
|
0000000000000000 0
|
|
0000000000000000 1
|
|
0000000000000000 2
|
|
0000000000000000 4
|
|
0000000000000000 5
|
|
0000000000000002 7
|
|
0000000000000002 8
|
|
0000000000000000 10
|
|
0000000000000000 11
|
|
0000000000000004 13
|
|
0000000000000004 15
|
|
0000000000000004 18874658
|
|
0000000000000006 37750404
|
|
0120010789ABCDEF 56626149
|
|
0120056789ABCDEF 56840245
|
|
DROP TABLE t;
|
|
Test with empty frame at beginning and end: bitops don't get
|
|
NULL then but a "neutral" value
|
|
CREATE TABLE t(
|
|
c0004 LONGTEXT CHARACTER SET latin1 COLLATE latin1_bin
|
|
GENERATED ALWAYS AS (_utf8mb4'1') VIRTUAL NOT NULL,
|
|
d DOUBLE GENERATED ALWAYS AS (1.0))
|
|
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
INSERT INTO t VALUES (), (), (), (), ();
|
|
SELECT c0004,
|
|
BIT_AND(c0004) OVER w,
|
|
BIT_OR(c0004) OVER w,
|
|
BIT_XOR(c0004) OVER w,
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY c0004 ROWS BETWEEN 5 PRECEDING AND 3 PRECEDING);
|
|
c0004 BIT_AND(c0004) OVER w BIT_OR(c0004) OVER w BIT_XOR(c0004) OVER w dsum
|
|
1 18446744073709551615 0 0 NULL
|
|
1 18446744073709551615 0 0 NULL
|
|
1 18446744073709551615 0 0 NULL
|
|
1 1 1 1 1
|
|
1 1 1 0 2
|
|
SELECT c0004,
|
|
BIT_AND(c0004) OVER w,
|
|
BIT_OR(c0004) OVER w,
|
|
BIT_XOR(c0004) OVER w,
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY c0004 ROWS BETWEEN 3 FOLLOWING AND 5 FOLLOWING);
|
|
c0004 BIT_AND(c0004) OVER w BIT_OR(c0004) OVER w BIT_XOR(c0004) OVER w dsum
|
|
1 1 1 0 2
|
|
1 1 1 1 1
|
|
1 18446744073709551615 0 0 NULL
|
|
1 18446744073709551615 0 0 NULL
|
|
1 18446744073709551615 0 0 NULL
|
|
DROP TABLE t;
|
|
CREATE TABLE t(i int, b VARBINARY(511), d DOUBLE GENERATED ALWAYS AS (i*1.0));
|
|
INSERT INTO t(i,b) VALUES (1, REPEAT(x'00', 511)),
|
|
(1, REPEAT(x'FF', 511)),
|
|
(1, REPEAT(x'77', 511)),
|
|
(1, REPEAT(x'11', 511));
|
|
SELECT '---',
|
|
HEX(BIT_OR(b) OVER w) AS `OR`,
|
|
HEX(BIT_AND(b) OVER w) AS `AND`,
|
|
HEX(BIT_XOR(b) OVER w) AS `XOR`,
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
--- ---
|
|
OR 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
AND 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
XOR 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
dsum 1
|
|
--- ---
|
|
OR FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
|
|
AND 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
|
|
XOR FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
|
|
dsum 2
|
|
--- ---
|
|
OR FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
|
|
AND 77777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777
|
|
XOR 88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
|
|
dsum 2
|
|
--- ---
|
|
OR 77777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777
|
|
AND 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
|
|
XOR 66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666
|
|
dsum 2
|
|
DROP TABLE t;
|
|
CREATE TABLE t(i int, b VARBINARY(512), d DOUBLE GENERATED ALWAYS AS (i*1.0));
|
|
INSERT INTO t(i,b) VALUES (1, REPEAT(x'00', 512)),
|
|
(1, REPEAT(x'FF', 512)),
|
|
(1, REPEAT(x'77', 512)),
|
|
(1, REPEAT(x'11', 512));
|
|
SELECT '---',
|
|
HEX(BIT_OR(b) OVER w) AS `OR`,
|
|
HEX(BIT_AND(b) OVER w) AS `AND`,
|
|
HEX(BIT_XOR(b) OVER w) AS `XOR`,
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
ERROR HY000: Aggregate bitwise functions cannot accept arguments longer than 511 bytes; consider using the SUBSTRING() function
|
|
DROP TABLE t;
|
|
# When switching from a partition to another, Item_sum_bit::clear()
|
|
# is called; it must reset Item_sum::m_digit_cnt if inversion is used
|
|
# inside the new frame.
|
|
CREATE TABLE t (id DECIMAL(3,1), b BINARY(9), d DOUBLE GENERATED ALWAYS AS (id*1.0));
|
|
INSERT INTO t(id, b)
|
|
VALUES (5.0,0x000000000000000000), (7.0,0x070000000000000007),
|
|
(6.0,0xFFFFFFFFFFFFFFFFFF), (5.5,0xFFFFFFFFFFFFFFFFFF);
|
|
SELECT id, id<=6 AS part, HEX(b), HEX(BIT_AND(b) OVER w),
|
|
SUM(d) OVER w AS dsum
|
|
FROM t
|
|
WINDOW w AS (PARTITION BY id<=6 ORDER BY id
|
|
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
id part HEX(b) HEX(BIT_AND(b) OVER w) dsum
|
|
7.0 0 070000000000000007 070000000000000007 7
|
|
5.0 1 000000000000000000 000000000000000000 5
|
|
5.5 1 FFFFFFFFFFFFFFFFFF 000000000000000000 10.5
|
|
6.0 1 FFFFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFFFF 11.5
|
|
DROP TABLE t;
|