[Commits] 96ec87d: Fixed bug mdev-11907.
IgorBabaev
igor at mariadb.com
Wed Mar 29 00:48:54 EEST 2017
revision-id: 96ec87d2cebc6e5b0b236aaaece79bd81bf4fcef (mariadb-10.2.4-85-g96ec87d)
parent(s): 93dd70ced8919be8ff9df6581f5f1a509998b6ac
author: Igor Babaev
committer: Igor Babaev
timestamp: 2017-03-28 14:48:54 -0700
message:
Fixed bug mdev-11907.
With the current design the function copy_funcs() should ignore
the items with window functions from the array **func_ptr.
---
mysql-test/r/win.result | 109 ++++++++++--------------------------------------
mysql-test/t/win.test | 21 ++++++++++
sql/sql_select.cc | 3 ++
3 files changed, 45 insertions(+), 88 deletions(-)
diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result
index f0a9aea..d48bb34 100644
--- a/mysql-test/r/win.result
+++ b/mysql-test/r/win.result
@@ -2270,17 +2270,6 @@ pk c CNT
8 2 0.5000
9 2 0.6667
10 2 1.0000
-Warnings:
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
create view v1 as select pk, c, c/count(*) over (partition by c order by pk
rows between 1 preceding and 2 following) as CNT
from t1;
@@ -2299,17 +2288,6 @@ pk c CNT
8 2 0.5000
9 2 0.6667
10 2 1.0000
-Warnings:
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
select pk, c, c/count(*) over w1 as CNT from t1
window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
pk c CNT
@@ -2323,17 +2301,6 @@ pk c CNT
8 2 0.5000
9 2 0.6667
10 2 1.0000
-Warnings:
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
create view v2 as select pk, c, c/count(*) over w1 as CNT from t1
window w1 as (partition by c order by pk rows between 1 preceding and 2 following);
show create view v2;
@@ -2351,17 +2318,6 @@ pk c CNT
8 2 0.5000
9 2 0.6667
10 2 1.0000
-Warnings:
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
select pk, c, c/count(*) over w1 as CNT from t1
window w1 as (partition by c order by pk rows unbounded preceding);
pk c CNT
@@ -2375,17 +2331,6 @@ pk c CNT
8 2 0.5000
9 2 0.4000
10 2 0.3333
-Warnings:
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
create view v3 as select pk, c, c/count(*) over w1 as CNT from t1
window w1 as (partition by c order by pk rows unbounded preceding);
show create view v3;
@@ -2403,17 +2348,6 @@ pk c CNT
8 2 0.5000
9 2 0.4000
10 2 0.3333
-Warnings:
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
select pk, c, c/count(*) over (partition by c order by pk
range between 3 preceding and current row) as CNT
from t1;
@@ -2428,17 +2362,6 @@ pk c CNT
8 2 0.5000
9 2 0.5000
10 2 0.5000
-Warnings:
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
create view v4 as select pk, c, c/count(*) over (partition by c order by pk
range between 3 preceding and current row) as CNT
from t1;
@@ -2457,17 +2380,6 @@ pk c CNT
8 2 0.5000
9 2 0.5000
10 2 0.5000
-Warnings:
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
-Warning 1365 Division by 0
drop view v1,v2,v3,v4;
drop table t0,t1;
#
@@ -3096,3 +3008,24 @@ select i, rank() over (order by i) rnk from t1 group by 1+2;
i rnk
2 1
drop table t1;
+#
+# MDEV-11907: window function as the second operand of division
+#
+create table t1 (pk int, c int);
+insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2);
+set @sql_mode_save= @@sql_mode;
+set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
+select pk, c, c/count(*) over
+(partition by c order by pk
+rows between 1 preceding and 2 following) as CNT
+from t1;
+pk c CNT
+1 1 0.3333
+2 1 0.2500
+3 1 0.3333
+4 1 0.5000
+5 2 2.0000
+show warnings;
+Level Code Message
+set sql_mode=@sql_mode_save;
+drop table t1;
diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test
index ec08373..3cd17ae 100644
--- a/mysql-test/t/win.test
+++ b/mysql-test/t/win.test
@@ -1809,3 +1809,24 @@ select i, row_number() over () from t1 group by 1+2;
select i, rank() over (order by i) rnk from t1 group by 1+2;
drop table t1;
+
+--echo #
+--echo # MDEV-11907: window function as the second operand of division
+--echo #
+
+create table t1 (pk int, c int);
+insert into t1 values (1,1),(2,1),(3,1),(4,1),(5,2);
+
+set @sql_mode_save= @@sql_mode;
+set sql_mode='ERROR_FOR_DIVISION_BY_ZERO';
+
+select pk, c, c/count(*) over
+ (partition by c order by pk
+ rows between 1 preceding and 2 following) as CNT
+from t1;
+show warnings;
+
+set sql_mode=@sql_mode_save;
+
+drop table t1;
+
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5633881..6f84b4a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -23338,6 +23338,9 @@ copy_funcs(Item **func_ptr, const THD *thd)
Item *func;
for (; (func = *func_ptr) ; func_ptr++)
{
+ if (func->type() == Item::FUNC_ITEM &&
+ ((Item_func *) func)->with_window_func)
+ continue;
func->save_in_result_field(1);
/*
Need to check the THD error state because Item::val_xxx() don't
More information about the commits
mailing list