SUM() of a negated typed NULL fails with "SUM or AVG on wrong data type"; CREATE TABLE AS gives the column data type NULL
Maintainers usually reply within 1 day
A pull request for this has already been merged.
- #4437 by @aadrian — merged
Assessment
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Newbie friendliness
- 35/100
Research direction
The issue identifies UnaryOperation.optimize() and BinaryOperation.optimize() as the entry points. Read how constant folding handles a NULL result and how TypedValueExpression.getTypedIfNull(value, type) preserves its type; check nearby tests for expression typing, then run the relevant tests. Done means negated and binary-operation typed NULLs retain their type in aggregates and CREATE TABLE AS, without regressing the working cases. A linked pull request is already open.
Written by the indexing model from the issue text.
Description
CREATE TABLE sales(region VARCHAR(10), amount DECIMAL(12, 2));
INSERT INTO sales VALUES ('EU', 100.50), ('US', 20.00);
SELECT region, SUM(amount) AS revenue FROM sales GROUP BY region;
-- works
SELECT region, SUM(-CAST(NULL AS DECIMAL(12, 2))) AS refunds FROM sales GROUP BY region;
-- SUM or AVG on wrong data type for "SUM(NULL)" [90015-259]
-- SUM(CAST(NULL AS DECIMAL(12, 2))) works and returns NULL
The type is also lost when the value is stored:
CREATE TABLE report AS
SELECT region, -CAST(NULL AS DECIMAL(12, 2)) AS refunds FROM sales;
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'REPORT';
-- REGION CHARACTER VARYING
-- REFUNDS NULL (expected NUMERIC(12, 2))
Binary operators do the same: SUM(0 - CAST(NULL AS DECIMAL(12, 2))), SUM(CAST(NULL AS DECIMAL(12, 2)) + 1) and SUM(CAST(NULL AS DECIMAL(12, 2)) * 2) fail alike. SUM(ABS(CAST(NULL AS DECIMAL(12, 2)))) works.
UnaryOperation.optimize() and BinaryOperation.optimize() fold a constant argument with ValueExpression.get(getValue(session)). A NULL result becomes an untyped NULL. TypedValueExpression.getTypedIfNull(value, type) keeps the type.
Seen on 2.5.259-SNAPSHOT (master) and 2.5.252.
- Dominant language
- Java
- Stars
- 4.6k
- Forks
- 1.3k
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 33
Getting set up
This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from h2database/h2database
-
It's not necessary to copy the memory block in the readWrite() of org.h2.store.fs.mem.FileMemDataOpen
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
h2database/h2database#4435 ·
Maintainers usually reply within 1 day
-
Difficulty 4/5 3-5 days Newbie friendliness 50/100
h2database/h2database#4445 ·
Maintainers usually reply within 1 day
-
Difficulty 3/5 1-2 days Newbie friendliness 66/100
h2database/h2database#4444 ·
Maintainers usually reply within 1 day
-
Difficulty 4/5 3-5 days Newbie friendliness 55/100
h2database/h2database#4425 · 3 comments ·
Maintainers usually reply within 1 day
-
BUG: Parenthesized UNION term returns stale result when only its FETCH / OFFSET / ORDER BY parameter changesPossibly taken @kalayciburak claimed this 10 days ago. Open
Difficulty 3/5 1-2 days Newbie friendliness 76/100
h2database/h2database#4421 ·
Maintainers usually reply within 1 day
All issues in h2database/h2database
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
PCL-Community/PCL-CE#3658 ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
Maintainers usually reply within 1 day
-
Team/Identity Server Core Type/Improvement U2
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
wso2/product-is#28553 ·
Maintainers usually reply within 1 day
-
frontend
Difficulty 1/5 Under an hour Newbie friendliness 72/100
Paul-Austin-Oswego-CSC480-HCI521/gift-app#116 ·
Maintainers usually reply within 2 days
-
dependencies java
Difficulty 1/5 Under an hour Newbie friendliness 62/100
micrometer-metrics/tracing#1588 ·
Maintainers usually reply within 1 day