Skip to content

Fix incorrect results returned by min, max and avg#145

Merged
Yury-Fridlyand merged 4 commits intointeg-fix-aggreg-on-nullsfrom
dev-fix-aggreg-on-nulls
Nov 1, 2022
Merged

Fix incorrect results returned by min, max and avg#145
Yury-Fridlyand merged 4 commits intointeg-fix-aggreg-on-nullsfrom
dev-fix-aggreg-on-nulls

Conversation

@Yury-Fridlyand
Copy link
Copy Markdown

@Yury-Fridlyand Yury-Fridlyand commented Oct 26, 2022 •
edited
Loading

Fix incorrect results returned by min, max and avg when processing null/missing values only.

Signed-off-by: Yury-Fridlyand yuryf@bitquilltech.com

Description

Before

opensearchsql> select min(int0) from calcs where int0 IS NULL;
fetched rows / total rows = 1/1
+-------------+
| min(int0)   |
|-------------|
| 2147483647  |
+-------------+

After

opensearchsql> select min(int0) from calcs where int0 IS NULL;
fetched rows / total rows = 1/1
+-------------+
| min(int0)   |
|-------------|
| null        |
+-------------+

Fixed:

  • min
  • max
  • avg

Not fixed:

  • sum
opensearchsql> select sum(int0) from calcs where int0 IS NULL;
fetched rows / total rows = 1/1
+-------------+
| sum(int0)   |
|-------------|
| 0           |
+-------------+

mysql> select sum(int0) from calcs where int0 IS NULL;

+-----------+
| sum(int0) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

This requires much more changes.

Not affected

  • var_samp
  • var_pop
  • stddev_samp
  • stddev_pop

Issues Resolved

opensearch-project#817

Check List

  • New functionality includes testing.
    • All tests pass, including unit test, integration test and doctest
  • New functionality has been documented.
    • New functionality has javadoc added
    • New functionality has user manual doc added
  • Commits are signed per the DCO using --signoff

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license.
For more information on following Developer Certificate of Origin and signing off your commits, please check here.

@Yury-Fridlyand
Fix incorrect results returned by min, max and avg aggregations…
8dff1c4 
… on null/missing values.

Signed-off-by: Yury-Fridlyand <yuryf@bitquilltech.com>
@Yury-Fridlyand
Fix indentation.
d302429 
Signed-off-by: Yury-Fridlyand <yuryf@bitquilltech.com>
@Yury-Fridlyand Yury-Fridlyand marked this pull request as draft October 26, 2022 23:42
@Yury-Fridlyand
Activate and fix integration tests.
951d2ca 
Signed-off-by: Yury-Fridlyand <yuryf@bitquilltech.com>
@Yury-Fridlyand Yury-Fridlyand marked this pull request as ready for review October 27, 2022 00:40
@Bit-Quill Bit-Quill deleted a comment from codecov bot Oct 27, 2022
@codecov

This comment was marked as abuse.

Sign in to view
acarbonetto
acarbonetto reviewed Oct 27, 2022
View reviewed changes
integ-test/src/test/resources/indexDefinitions/null_missing_index_mapping.json
"format": "yyyy-MM-dd"
},
"time" : {
"type" : "date",
Copy link
Copy Markdown

@acarbonetto acarbonetto Oct 27, 2022

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

😆

@acarbonetto
Copy link
Copy Markdown

acarbonetto commented Oct 27, 2022

What the MySQL result for sum(null)? Is it supposed to be null or zero?

acarbonetto
acarbonetto approved these changes Oct 27, 2022
View reviewed changes
integ-test/src/test/resources/null_missing.json
@@ -0,0 +1,18 @@
{"index":{}}
Copy link
Copy Markdown

@acarbonetto acarbonetto Oct 27, 2022

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

you don't want to give these index numbers?

Copy link
Copy Markdown
Author

@Yury-Fridlyand Yury-Fridlyand Oct 27, 2022 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

They are assigned automatically if not given.

integ-test/src/test/resources/null_missing.json
{"index":{}}
{"key" : "values", "int" : 42, "dbl" : 3.1415, "bool" : true, "str" : "pewpew", "date" : "1984-05-22", "time" : "22:13:37", "datetime" : "1984-05-22 22:13:37", "timestamp" : "2000-01-02 03:04:05" }
{"index":{}}
{"key" : "missing"}
Copy link
Copy Markdown

@acarbonetto acarbonetto Oct 27, 2022

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

is this not confusing?

Copy link
Copy Markdown
Author

@Yury-Fridlyand Yury-Fridlyand Oct 27, 2022

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

key is not a PK. Just a column.

integ-test/src/test/resources/null_missing.json
{"index":{}}
{"key" : "missing"}
{"index":{}}
{"key" : "null", "int" : null, "dbl" : null, "bool" : null, "str" : null, "date" : null, "time" : null, "datetime" : null, "timestamp" : null}
Copy link
Copy Markdown

@acarbonetto acarbonetto Oct 27, 2022

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

is "key": "null" also not confusing?

@Yury-Fridlyand
Copy link
Copy Markdown
Author

Yury-Fridlyand commented Oct 27, 2022

What the MySQL result for sum(null)? Is it supposed to be null or zero?

mysql> select sum(num0) from calcs where num0 is null;
+-----------+
| sum(num0) |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

@Yury-Fridlyand Yury-Fridlyand mentioned this pull request Oct 27, 2022
Merged
6 tasks
MitchellGale
MitchellGale approved these changes Oct 27, 2022
View reviewed changes
Copy link
Copy Markdown

@MitchellGale MitchellGale left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM

@Yury-Fridlyand
Add more tests.
17e1ae9 
Signed-off-by: Yury-Fridlyand <yuryf@bitquilltech.com>
@Yury-Fridlyand Yury-Fridlyand mentioned this pull request Oct 28, 2022
Closed
@Yury-Fridlyand Yury-Fridlyand merged commit 7d60f22 into integ-fix-aggreg-on-nulls Nov 1, 2022
@Yury-Fridlyand Yury-Fridlyand deleted the dev-fix-aggreg-on-nulls branch November 1, 2022 00:50
@Yury-Fridlyand Yury-Fridlyand mentioned this pull request Nov 1, 2022
Merged
6 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@forestmvey forestmvey forestmvey approved these changes

@acarbonetto acarbonetto acarbonetto approved these changes

@MitchellGale MitchellGale MitchellGale approved these changes

@GabeFernandez310 GabeFernandez310 Awaiting requested review from GabeFernandez310

@MaxKsyunz MaxKsyunz Awaiting requested review from MaxKsyunz

@margarit-h margarit-h Awaiting requested review from margarit-h

+1 more reviewer

@GumpacG GumpacG GumpacG approved these changes

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

5 participants

@Yury-Fridlyand @acarbonetto @GumpacG @forestmvey @MitchellGale