Teradata and MySQL behave different in OVER() and Partition By () Clauses
I want to understand why the same query is producing different result in Teradata and My SQL.
I am trying to write a query for running total and each DB is giving me different solutions.
Below is the code:
CREATE TABLE runn_tot (p_id int, p_name varchar(10), price decimal(5,2));
insert into runn_tot values (1,'p1',34);
insert into runn_tot values (2,'p1',56);
insert into runn_tot values (3,'p1',65);
insert into runn_tot values (4,'p1',12);
insert into runn_tot values (5,'p1',34);
insert into runn_tot values (6,'p1',78);
insert into runn_tot values (7,'p1',23);
insert into runn_tot values (8,'p1',55);
insert into runn_tot values (9,'p1',34);
insert into runn_tot values (10,'p1',66);
The query which i'm using in Both MySQL and Teradata
select p_id, p_name, SUM(price) OVER ( partition by p_name order by p_id) Running_Total
from runn_tot;
Results from MySQL:
+------+--------+---------------+
| p_id | p_name | Running_Total |
+------+--------+---------------+
| 1 | p1 | 34.00 |
| 2 | p1 | 90.00 |
| 3 | p1 | 155.00 |
| 4 | p1 | 167.00 |
| 5 | p1 | 201.00 |
| 6 | p1 | 279.00 |
| 7 | p1 | 302.00 |
| 8 | p1 | 357.00 |
| 9 | p1 | 391.00 |
| 10 | p1 | 457.00 |
+------+--------+---------------+
Results from Teradata:
1 p1 457.00
2 p1 457.00
3 p1 457.00
4 p1 457.00
5 p1 457.00
6 p1 457.00
7 p1 457.00
8 p1 457.00
9 p1 457.00
10 p1 457.00
I am trying to understand why MySQL is able to get the correct running total and teradata is not doing the window function correctly.
mysql teradata
asked Nov 22 at 17:57
Ravi
8210
add a comment |
I want to understand why the same query is producing different result in Teradata and My SQL.
I am trying to write a query for running total and each DB is giving me different solutions.
Below is the code:
CREATE TABLE runn_tot (p_id int, p_name varchar(10), price decimal(5,2));
insert into runn_tot values (1,'p1',34);
insert into runn_tot values (2,'p1',56);
insert into runn_tot values (3,'p1',65);
insert into runn_tot values (4,'p1',12);
insert into runn_tot values (5,'p1',34);
insert into runn_tot values (6,'p1',78);
insert into runn_tot values (7,'p1',23);
insert into runn_tot values (8,'p1',55);
insert into runn_tot values (9,'p1',34);
insert into runn_tot values (10,'p1',66);
The query which i'm using in Both MySQL and Teradata
select p_id, p_name, SUM(price) OVER ( partition by p_name order by p_id) Running_Total
from runn_tot;
Results from MySQL:
+------+--------+---------------+
| p_id | p_name | Running_Total |
+------+--------+---------------+
| 1 | p1 | 34.00 |
| 2 | p1 | 90.00 |
| 3 | p1 | 155.00 |
| 4 | p1 | 167.00 |
| 5 | p1 | 201.00 |
| 6 | p1 | 279.00 |
| 7 | p1 | 302.00 |
| 8 | p1 | 357.00 |
| 9 | p1 | 391.00 |
| 10 | p1 | 457.00 |
+------+--------+---------------+
Results from Teradata:
1 p1 457.00
2 p1 457.00
3 p1 457.00
4 p1 457.00
5 p1 457.00
6 p1 457.00
7 p1 457.00
8 p1 457.00
9 p1 457.00
10 p1 457.00
I am trying to understand why MySQL is able to get the correct running total and teradata is not doing the window function correctly.
mysql teradata
asked Nov 22 at 17:57
Ravi
8210
add a comment |
I want to understand why the same query is producing different result in Teradata and My SQL.
I am trying to write a query for running total and each DB is giving me different solutions.
Below is the code:
CREATE TABLE runn_tot (p_id int, p_name varchar(10), price decimal(5,2));
insert into runn_tot values (1,'p1',34);
insert into runn_tot values (2,'p1',56);
insert into runn_tot values (3,'p1',65);
insert into runn_tot values (4,'p1',12);
insert into runn_tot values (5,'p1',34);
insert into runn_tot values (6,'p1',78);
insert into runn_tot values (7,'p1',23);
insert into runn_tot values (8,'p1',55);
insert into runn_tot values (9,'p1',34);
insert into runn_tot values (10,'p1',66);
The query which i'm using in Both MySQL and Teradata
select p_id, p_name, SUM(price) OVER ( partition by p_name order by p_id) Running_Total
from runn_tot;
Results from MySQL:
+------+--------+---------------+
| p_id | p_name | Running_Total |
+------+--------+---------------+
| 1 | p1 | 34.00 |
| 2 | p1 | 90.00 |
| 3 | p1 | 155.00 |
| 4 | p1 | 167.00 |
| 5 | p1 | 201.00 |
| 6 | p1 | 279.00 |
| 7 | p1 | 302.00 |
| 8 | p1 | 357.00 |
| 9 | p1 | 391.00 |
| 10 | p1 | 457.00 |
+------+--------+---------------+
Results from Teradata:
1 p1 457.00
2 p1 457.00
3 p1 457.00
4 p1 457.00
5 p1 457.00
6 p1 457.00
7 p1 457.00
8 p1 457.00
9 p1 457.00
10 p1 457.00
I am trying to understand why MySQL is able to get the correct running total and teradata is not doing the window function correctly.
mysql teradata
asked Nov 22 at 17:57
Ravi
8210
I want to understand why the same query is producing different result in Teradata and My SQL.
I am trying to write a query for running total and each DB is giving me different solutions.
Below is the code:
CREATE TABLE runn_tot (p_id int, p_name varchar(10), price decimal(5,2));
insert into runn_tot values (1,'p1',34);
insert into runn_tot values (2,'p1',56);
insert into runn_tot values (3,'p1',65);
insert into runn_tot values (4,'p1',12);
insert into runn_tot values (5,'p1',34);
insert into runn_tot values (6,'p1',78);
insert into runn_tot values (7,'p1',23);
insert into runn_tot values (8,'p1',55);
insert into runn_tot values (9,'p1',34);
insert into runn_tot values (10,'p1',66);
The query which i'm using in Both MySQL and Teradata
select p_id, p_name, SUM(price) OVER ( partition by p_name order by p_id) Running_Total
from runn_tot;
Results from MySQL:
+------+--------+---------------+
| p_id | p_name | Running_Total |
+------+--------+---------------+
| 1 | p1 | 34.00 |
| 2 | p1 | 90.00 |
| 3 | p1 | 155.00 |
| 4 | p1 | 167.00 |
| 5 | p1 | 201.00 |
| 6 | p1 | 279.00 |
| 7 | p1 | 302.00 |
| 8 | p1 | 357.00 |
| 9 | p1 | 391.00 |
| 10 | p1 | 457.00 |
+------+--------+---------------+
Results from Teradata:
1 p1 457.00
2 p1 457.00
3 p1 457.00
4 p1 457.00
5 p1 457.00
6 p1 457.00
7 p1 457.00
8 p1 457.00
9 p1 457.00
10 p1 457.00
I am trying to understand why MySQL is able to get the correct running total and teradata is not doing the window function correctly.
mysql teradata
mysql teradata
asked Nov 22 at 17:57
Ravi
8210
asked Nov 22 at 17:57
Ravi
8210
asked Nov 22 at 17:57
Ravi
8210
asked Nov 22 at 17:57
Ravi
8210
asked Nov 22 at 17:57
Ravi
8210
8210
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Teradata implemented some Window Functions almost 20 years ago before they became part of Standard SQL 99 (using a proprietary syntax) and this behaviour is a kind of leftover.
In Standard SQL (and MySQL) when you specify ORDER BY the window defaults to RANGE UNBOUNDED PECEDING, which is not supported by Teradata, which defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. To get the expected result you must add ROWS UNBOUNDED PRECEDING, which is recommended over RANGE in other DBMSes, too (unless you actually need the result of a RANGE), because ROWS is way easier to calculate.
answered Nov 22 at 22:36
dnoeth
44.6k31838
Thanks for the inputs.
– Ravi
Nov 24 at 15:57
The solution would be: with data (p_id, p_name, price) as ( VALUES (1,'p1',34), (2,'p1',56), (3,'p1',65), (4,'p1',12), (5,'p1',34), (6,'p1',78), (7,'p1',23), (8,'p1',55), (9,'p1',34), (10,'p1',66) ) SELECT p_id, p_name, sum(price) OVER (ORDER BY p_name ROWS UNBOUNDED PRECEDING) FROM data ORDER BY p_id
– Ravi
Nov 24 at 15:57
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436163%2fteradata-and-mysql-behave-different-in-over-and-partition-by-clauses%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Teradata implemented some Window Functions almost 20 years ago before they became part of Standard SQL 99 (using a proprietary syntax) and this behaviour is a kind of leftover.
In Standard SQL (and MySQL) when you specify ORDER BY the window defaults to RANGE UNBOUNDED PECEDING, which is not supported by Teradata, which defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. To get the expected result you must add ROWS UNBOUNDED PRECEDING, which is recommended over RANGE in other DBMSes, too (unless you actually need the result of a RANGE), because ROWS is way easier to calculate.
answered Nov 22 at 22:36
dnoeth
44.6k31838
Thanks for the inputs.
– Ravi
Nov 24 at 15:57
The solution would be: with data (p_id, p_name, price) as ( VALUES (1,'p1',34), (2,'p1',56), (3,'p1',65), (4,'p1',12), (5,'p1',34), (6,'p1',78), (7,'p1',23), (8,'p1',55), (9,'p1',34), (10,'p1',66) ) SELECT p_id, p_name, sum(price) OVER (ORDER BY p_name ROWS UNBOUNDED PRECEDING) FROM data ORDER BY p_id
– Ravi
Nov 24 at 15:57
add a comment |
Teradata implemented some Window Functions almost 20 years ago before they became part of Standard SQL 99 (using a proprietary syntax) and this behaviour is a kind of leftover.
In Standard SQL (and MySQL) when you specify ORDER BY the window defaults to RANGE UNBOUNDED PECEDING, which is not supported by Teradata, which defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. To get the expected result you must add ROWS UNBOUNDED PRECEDING, which is recommended over RANGE in other DBMSes, too (unless you actually need the result of a RANGE), because ROWS is way easier to calculate.
answered Nov 22 at 22:36
dnoeth
44.6k31838
Thanks for the inputs.
– Ravi
Nov 24 at 15:57
The solution would be: with data (p_id, p_name, price) as ( VALUES (1,'p1',34), (2,'p1',56), (3,'p1',65), (4,'p1',12), (5,'p1',34), (6,'p1',78), (7,'p1',23), (8,'p1',55), (9,'p1',34), (10,'p1',66) ) SELECT p_id, p_name, sum(price) OVER (ORDER BY p_name ROWS UNBOUNDED PRECEDING) FROM data ORDER BY p_id
– Ravi
Nov 24 at 15:57
add a comment |
Teradata implemented some Window Functions almost 20 years ago before they became part of Standard SQL 99 (using a proprietary syntax) and this behaviour is a kind of leftover.
In Standard SQL (and MySQL) when you specify ORDER BY the window defaults to RANGE UNBOUNDED PECEDING, which is not supported by Teradata, which defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. To get the expected result you must add ROWS UNBOUNDED PRECEDING, which is recommended over RANGE in other DBMSes, too (unless you actually need the result of a RANGE), because ROWS is way easier to calculate.
answered Nov 22 at 22:36
dnoeth
44.6k31838
Teradata implemented some Window Functions almost 20 years ago before they became part of Standard SQL 99 (using a proprietary syntax) and this behaviour is a kind of leftover.
In Standard SQL (and MySQL) when you specify ORDER BY the window defaults to RANGE UNBOUNDED PECEDING, which is not supported by Teradata, which defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. To get the expected result you must add ROWS UNBOUNDED PRECEDING, which is recommended over RANGE in other DBMSes, too (unless you actually need the result of a RANGE), because ROWS is way easier to calculate.
answered Nov 22 at 22:36
dnoeth
44.6k31838
answered Nov 22 at 22:36
dnoeth
44.6k31838
answered Nov 22 at 22:36
dnoeth
44.6k31838
answered Nov 22 at 22:36
dnoeth
44.6k31838
44.6k31838
Thanks for the inputs.
– Ravi
Nov 24 at 15:57
The solution would be: with data (p_id, p_name, price) as ( VALUES (1,'p1',34), (2,'p1',56), (3,'p1',65), (4,'p1',12), (5,'p1',34), (6,'p1',78), (7,'p1',23), (8,'p1',55), (9,'p1',34), (10,'p1',66) ) SELECT p_id, p_name, sum(price) OVER (ORDER BY p_name ROWS UNBOUNDED PRECEDING) FROM data ORDER BY p_id
– Ravi
Nov 24 at 15:57
add a comment |
Thanks for the inputs.
– Ravi
Nov 24 at 15:57
The solution would be: with data (p_id, p_name, price) as ( VALUES (1,'p1',34), (2,'p1',56), (3,'p1',65), (4,'p1',12), (5,'p1',34), (6,'p1',78), (7,'p1',23), (8,'p1',55), (9,'p1',34), (10,'p1',66) ) SELECT p_id, p_name, sum(price) OVER (ORDER BY p_name ROWS UNBOUNDED PRECEDING) FROM data ORDER BY p_id
– Ravi
Nov 24 at 15:57
Thanks for the inputs.
– Ravi
Nov 24 at 15:57
Thanks for the inputs.
– Ravi
Nov 24 at 15:57
The solution would be: with data (p_id, p_name, price) as ( VALUES (1,'p1',34), (2,'p1',56), (3,'p1',65), (4,'p1',12), (5,'p1',34), (6,'p1',78), (7,'p1',23), (8,'p1',55), (9,'p1',34), (10,'p1',66) ) SELECT p_id, p_name, sum(price) OVER (ORDER BY p_name ROWS UNBOUNDED PRECEDING) FROM data ORDER BY p_id
– Ravi
Nov 24 at 15:57
The solution would be: with data (p_id, p_name, price) as ( VALUES (1,'p1',34), (2,'p1',56), (3,'p1',65), (4,'p1',12), (5,'p1',34), (6,'p1',78), (7,'p1',23), (8,'p1',55), (9,'p1',34), (10,'p1',66) ) SELECT p_id, p_name, sum(price) OVER (ORDER BY p_name ROWS UNBOUNDED PRECEDING) FROM data ORDER BY p_id
– Ravi
Nov 24 at 15:57
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53436163%2fteradata-and-mysql-behave-different-in-over-and-partition-by-clauses%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
