0002-numeric-add-regression-coverage-for-NumericVar-state.patch
application/octet-stream
Filename: 0002-numeric-add-regression-coverage-for-NumericVar-state.patch
Type: application/octet-stream
Part: 1
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch 0002
Subject: numeric: add regression coverage for NumericVar state changes
| File | + | − |
|---|---|---|
| src/test/regress/expected/numeric_op_big.out | 497 | 0 |
| src/test/regress/expected/numeric_op_ddl.out | 167 | 0 |
| src/test/regress/expected/numeric_op_medium.out | 497 | 0 |
| src/test/regress/expected/numeric_op_small.out | 634 | 0 |
| src/test/regress/parallel_schedule | 4 | 0 |
| src/test/regress/sql/numeric_op_big.sql | 267 | 0 |
| src/test/regress/sql/numeric_op_ddl.sql | 173 | 0 |
| src/test/regress/sql/numeric_op_medium.sql | 267 | 0 |
| src/test/regress/sql/numeric_op_small.sql | 333 | 0 |
From c0a30a385671504e5a7751b373ac3dd234d2d105 Mon Sep 17 00:00:00 2001
From: sohardforaname <organic_chemistry@foxmail.com>
Date: Sun, 10 May 2026 21:43:50 +0800
Subject: [PATCH 2/2] numeric: add regression coverage for NumericVar state
changes
Add regression coverage for the NumericVar storage-state refactoring.
The tests cover a wide range of numeric typmods, including high-precision
and high-scale cases, and exercise arithmetic, composite expressions,
comparison-sensitive paths, window functions, join-driven expressions,
aggregates, updates, error cases, and index/index-only related paths.
This is intended to verify that the refactoring preserves SQL-visible
behavior across both ordinary and extreme numeric workloads.
---
src/test/regress/expected/numeric_op_big.out | 497 ++++++++++++++
src/test/regress/expected/numeric_op_ddl.out | 167 +++++
.../regress/expected/numeric_op_medium.out | 497 ++++++++++++++
.../regress/expected/numeric_op_small.out | 634 ++++++++++++++++++
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/numeric_op_big.sql | 267 ++++++++
src/test/regress/sql/numeric_op_ddl.sql | 173 +++++
src/test/regress/sql/numeric_op_medium.sql | 267 ++++++++
src/test/regress/sql/numeric_op_small.sql | 333 +++++++++
9 files changed, 2839 insertions(+)
create mode 100644 src/test/regress/expected/numeric_op_big.out
create mode 100644 src/test/regress/expected/numeric_op_ddl.out
create mode 100644 src/test/regress/expected/numeric_op_medium.out
create mode 100644 src/test/regress/expected/numeric_op_small.out
create mode 100644 src/test/regress/sql/numeric_op_big.sql
create mode 100644 src/test/regress/sql/numeric_op_ddl.sql
create mode 100644 src/test/regress/sql/numeric_op_medium.sql
create mode 100644 src/test/regress/sql/numeric_op_small.sql
diff --git a/src/test/regress/expected/numeric_op_big.out b/src/test/regress/expected/numeric_op_big.out
new file mode 100644
index 00000000000..12c3cf65a83
--- /dev/null
+++ b/src/test/regress/expected/numeric_op_big.out
@@ -0,0 +1,497 @@
+select count(distinct a), sum(hashtext(a::text)) from t_1000_800 where a > b;
+ count | sum
+-------+--------------
+ 2000 | -93674011458
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_1000_995 where a > b;
+ count | sum
+-------+-------------
+ 2000 | 49431437020
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_1000_1000 where a > b;
+ count | sum
+-------+-------------
+ 200 | 81596504540
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_800;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+-------------+-------------+--------------+-------------+--------------+--------------+----------------+----------------+----------------+---------------+---------------+---------------
+ 103787738890 | 71241136711 | 91238480326 | -25880446966 | 85861504452 | 138111163668 | -64934216831 | -3883271108300 | -3883271108300 | -7965490469700 | -263811485216 | -196222417055 | -253470347100
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_995;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+---------------+--------------+-------------+--------------+---------------+--------------+--------------+----------------+----------------+----------------+---------------+---------------+---------------
+ -168835944407 | -37026039983 | -9162875272 | 103006726746 | -108905948664 | -77309522276 | 146591765000 | -3883271108300 | -3883271108300 | -7965490469700 | -111504876460 | -183625584196 | -243750883157
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_1000;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+-------------+-------------+---------------+-----+-----+---------------+-------------+--------------+----------------+----------------+-------------+--------------+--------------
+ 81596504540 | 81596504540 | -448199909000 | | | -448199909000 | 81596504540 | 153330623000 | -1995205883000 | -1995205883000 | 81596504540 | -70370694025 | -81922915700
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_800
+) t1;
+ sum | sum | sum
+--------------+--------------+---------------
+ 122592282760 | 122592282760 | -153072055232
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_995
+) t1;
+ sum | sum | sum
+--------------+--------------+---------------
+ 122592282760 | 122592282760 | -221944827430
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_1000
+) t1;
+ sum | sum | sum
+--------------+--------------+--------------
+ -87303812940 | -87303812940 | -36200000898
+(1 row)
+
+select a/0 from t_1000_800;
+ERROR: division by zero
+select a/0 from t_1000_995;
+ERROR: division by zero
+select a/0 from t_1000_1000;
+ERROR: division by zero
+select a%0 from t_1000_800;
+ERROR: division by zero
+select a%0 from t_1000_995;
+ERROR: division by zero
+select a%0 from t_1000_1000;
+ERROR: division by zero
+select (a-a)/(a-a) from t_1000_800;
+ERROR: division by zero
+select (a-a)/(a-a) from t_1000_995;
+ERROR: division by zero
+select (a-a)/(a-a) from t_1000_1000;
+ERROR: division by zero
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 1dab074815e4209082cd0fff9582bf2c | 5d0bb1be149c34037fddfade54a3adf6 | 6462f616299e10ce9d30f1a9d1bbeb20
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 1a5a255cc4b0646f8cb27448a2df2310 | 3c1165aed3ac2fe402386fd8bc6a7f69 | bb44d6c91ac3138f7712d7062ba1d4c0
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ f3944f455b5fc4deb1eb3c782e38b134 | 1a5a255cc4b0646f8cb27448a2df2310 | 82295228238aae9f4eb3e7e71bb3b1d0
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 1dab074815e4209082cd0fff9582bf2c | 5d0bb1be149c34037fddfade54a3adf6 | 6462f616299e10ce9d30f1a9d1bbeb20
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 1a5a255cc4b0646f8cb27448a2df2310 | 3c1165aed3ac2fe402386fd8bc6a7f69 | bb44d6c91ac3138f7712d7062ba1d4c0
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ f3944f455b5fc4deb1eb3c782e38b134 | 1a5a255cc4b0646f8cb27448a2df2310 | 82295228238aae9f4eb3e7e71bb3b1d0
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 1dab074815e4209082cd0fff9582bf2c | 5d0bb1be149c34037fddfade54a3adf6 | 6462f616299e10ce9d30f1a9d1bbeb20
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 1a5a255cc4b0646f8cb27448a2df2310 | 3c1165aed3ac2fe402386fd8bc6a7f69 | bb44d6c91ac3138f7712d7062ba1d4c0
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ f3944f455b5fc4deb1eb3c782e38b134 | 1a5a255cc4b0646f8cb27448a2df2310 | 82295228238aae9f4eb3e7e71bb3b1d0
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_seqscan = off;
+explain (costs off) select * from t_1000_800 where a > 1000 order by a;
+ QUERY PLAN
+-------------------------------------------------
+ Sort
+ Sort Key: a
+ -> Bitmap Heap Scan on t_1000_800
+ Recheck Cond: (a > '1000'::numeric)
+ -> Bitmap Index Scan on idx_t_1000_800
+ Index Cond: (a > '1000'::numeric)
+(6 rows)
+
+explain (costs off) select a from t_1000_800 where a > 1000 order by a;
+ QUERY PLAN
+----------------------------------------------------
+ Index Only Scan using idx_t_1000_800 on t_1000_800
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select * from t_1000_995 where a > 1000 order by a;
+ QUERY PLAN
+-------------------------------------------------
+ Sort
+ Sort Key: a
+ -> Bitmap Heap Scan on t_1000_995
+ Recheck Cond: (a > '1000'::numeric)
+ -> Bitmap Index Scan on idx_t_1000_995
+ Index Cond: (a > '1000'::numeric)
+(6 rows)
+
+explain (costs off) select a from t_1000_995 where a > 1000 order by a;
+ QUERY PLAN
+----------------------------------------------------
+ Index Only Scan using idx_t_1000_995 on t_1000_995
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select * from t_1000_1000 where a > 1000 order by a;
+ QUERY PLAN
+--------------------------------------------------
+ Sort
+ Sort Key: a
+ -> Bitmap Heap Scan on t_1000_1000
+ Recheck Cond: (a > '1000'::numeric)
+ -> Bitmap Index Scan on idx_t_1000_1000
+ Index Cond: (a > '1000'::numeric)
+(6 rows)
+
+explain (costs off) select a from t_1000_1000 where a > 1000 order by a;
+ QUERY PLAN
+------------------------------------------------------
+ Index Only Scan using idx_t_1000_1000 on t_1000_1000
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+reset enable_seqscan;
+update t_1000_800 set a = a + 1;
+update t_1000_995 set a = a + 1;
+update t_1000_1000 set a = a + 1;
+select count(distinct a), sum(hashtext(a::text)) from t_1000_800 where a > b;
+ count | sum
+-------+--------------
+ 2001 | 187915496731
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_1000_995 where a > b;
+ count | sum
+-------+---------------
+ 2001 | -120277609243
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_1000_1000 where a > b;
+ count | sum
+-------+-------------
+ 200 | -6791326615
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_800;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+-------------+--------------+-------------+------------+-------------+-------------+-------------+----------------+----------------+----------------+--------------+--------------+--------------
+ 69378299805 | -28004625396 | 69165702075 | 6495822165 | 42794676167 | 28778131261 | 23748489343 | -3668417457700 | -3883271108300 | -8180344120300 | 162788189344 | -88035415965 | -14300392972
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_995;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+-----------+--------------+--------------+--------------+------------+--------------+----------------+----------------+----------------+---------------+--------------+---------------
+ 106142378501 | 456865251 | -40344563826 | -82057607022 | -74296237953 | 4474319379 | 111919427572 | -3668417457700 | -3883271108300 | -8180344120300 | -223137274026 | -60159164731 | -114299102966
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_1000;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+-------------+-------------+---------------+-----+-----+---------------+-------------+--------------+----------------+----------------+-------------+---------------+--------------
+ -6791326615 | -6791326615 | -448199909000 | | | -448199909000 | -6791326615 | 153330623000 | -1995205883000 | -1995205883000 | -6791326615 | -116888674645 | -70370694025
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_800
+) t1;
+ sum | sum | sum
+--------------+--------------+--------------
+ 122592282760 | 122592282760 | -23880525307
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_995
+) t1;
+ sum | sum | sum
+--------------+--------------+-------------
+ 122592282760 | 122592282760 | 31543863976
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_1000
+) t1;
+ sum | sum | sum
+--------------+--------------+-------------
+ -87303812940 | -87303812940 | 18056325685
+(1 row)
+
+select a/0 from t_1000_800;
+ERROR: division by zero
+select a/0 from t_1000_995;
+ERROR: division by zero
+select a/0 from t_1000_1000;
+ERROR: division by zero
+select a%0 from t_1000_800;
+ERROR: division by zero
+select a%0 from t_1000_995;
+ERROR: division by zero
+select a%0 from t_1000_1000;
+ERROR: division by zero
+select (a-a)/(a-a) from t_1000_800;
+ERROR: division by zero
+select (a-a)/(a-a) from t_1000_995;
+ERROR: division by zero
+select (a-a)/(a-a) from t_1000_1000;
+ERROR: division by zero
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 9af0d5833a5dc496ac4cd53bca6c8602 | 5d0bb1be149c34037fddfade54a3adf6 | 6462f616299e10ce9d30f1a9d1bbeb20
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 11cbb4649046194d3a521054a6e9d2f1 | 3c1165aed3ac2fe402386fd8bc6a7f69 | bb44d6c91ac3138f7712d7062ba1d4c0
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ a76cd728b4a258a8353a3288df6fa78c | 1a5a255cc4b0646f8cb27448a2df2310 | 82295228238aae9f4eb3e7e71bb3b1d0
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 9af0d5833a5dc496ac4cd53bca6c8602 | 5d0bb1be149c34037fddfade54a3adf6 | 6462f616299e10ce9d30f1a9d1bbeb20
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 11cbb4649046194d3a521054a6e9d2f1 | 3c1165aed3ac2fe402386fd8bc6a7f69 | bb44d6c91ac3138f7712d7062ba1d4c0
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ a76cd728b4a258a8353a3288df6fa78c | 1a5a255cc4b0646f8cb27448a2df2310 | 82295228238aae9f4eb3e7e71bb3b1d0
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 9af0d5833a5dc496ac4cd53bca6c8602 | 5d0bb1be149c34037fddfade54a3adf6 | 6462f616299e10ce9d30f1a9d1bbeb20
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 11cbb4649046194d3a521054a6e9d2f1 | 3c1165aed3ac2fe402386fd8bc6a7f69 | bb44d6c91ac3138f7712d7062ba1d4c0
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ a76cd728b4a258a8353a3288df6fa78c | 1a5a255cc4b0646f8cb27448a2df2310 | 82295228238aae9f4eb3e7e71bb3b1d0
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_1000_800 where a > 100) t1;
+ sum
+--------------
+ -27571036926
+(1 row)
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_1000_995 where a > 100) t1;
+ sum
+--------------
+ 142042826843
+(1 row)
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_1000_1000 where a > 100) t1;
+ sum
+--------------
+ -37293091674
+(1 row)
+
diff --git a/src/test/regress/expected/numeric_op_ddl.out b/src/test/regress/expected/numeric_op_ddl.out
new file mode 100644
index 00000000000..d8a1523a5fc
--- /dev/null
+++ b/src/test/regress/expected/numeric_op_ddl.out
@@ -0,0 +1,167 @@
+DROP TABLE IF EXISTS t_9_2;
+NOTICE: table "t_9_2" does not exist, skipping
+DROP TABLE IF EXISTS t_18_2;
+NOTICE: table "t_18_2" does not exist, skipping
+DROP TABLE IF EXISTS t_38_2;
+NOTICE: table "t_38_2" does not exist, skipping
+DROP TABLE IF EXISTS t_80_16;
+NOTICE: table "t_80_16" does not exist, skipping
+DROP TABLE IF EXISTS t_175_35;
+NOTICE: table "t_175_35" does not exist, skipping
+DROP TABLE IF EXISTS t_400_80;
+NOTICE: table "t_400_80" does not exist, skipping
+DROP TABLE IF EXISTS t_1000_200;
+NOTICE: table "t_1000_200" does not exist, skipping
+DROP TABLE IF EXISTS t_1000_800;
+NOTICE: table "t_1000_800" does not exist, skipping
+DROP TABLE IF EXISTS t_1000_995;
+NOTICE: table "t_1000_995" does not exist, skipping
+DROP TABLE IF EXISTS t_base;
+NOTICE: table "t_base" does not exist, skipping
+CREATE TABLE t_9_2 (id int, id2 int, a NUMERIC(9, 2), b NUMERIC(11, 4));
+CREATE TABLE t_18_2 (id int, id2 int, a NUMERIC(18, 2), b NUMERIC(20, 4));
+CREATE TABLE t_38_2 (id int, id2 int, a NUMERIC(38, 2), b NUMERIC(40, 4));
+CREATE TABLE t_80_16 (id int, id2 int, a NUMERIC(80, 16), b NUMERIC(90, 18));
+CREATE TABLE t_175_35 (id int, id2 int, a NUMERIC(175, 35), b NUMERIC(190, 40));
+CREATE TABLE t_400_80 (id int, id2 int, a NUMERIC(400, 80), b NUMERIC(420, 100));
+CREATE TABLE t_1000_200 (id int, id2 int, a NUMERIC(1000, 200), b NUMERIC(1000, 220));
+CREATE TABLE t_1000_800 (id int, id2 int, a NUMERIC(1000, 800), b NUMERIC(1000, 900));
+CREATE TABLE t_1000_995 (id int, id2 int, a NUMERIC(1000, 995), b NUMERIC(1000, 1000));
+CREATE TABLE t_base (id int, id2 int, astr text, bstr text);
+INSERT INTO t_base
+ SELECT g,
+ g % 20,
+ string_agg(
+ lpad(
+ (((g * 37 + k)::bigint * 1103515245 + 12345) % 1000000007)::text,
+ 9, '0'
+ ),
+ ''
+ ORDER BY k
+ ) AS astr,
+ string_agg(
+ lpad(
+ (((g * 131 + k)::bigint * 1284584123 + 89247) % 998244353)::text,
+ 9, '0'
+ ),
+ ''
+ ORDER BY k
+ ) AS bstr
+ FROM generate_series(1, 2000) g,
+ generate_series(1, 120) k
+ GROUP BY g;
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 7) || '.' || substr(astr, 8, 2))::numeric(9, 2) AS a,
+ (substr(bstr, 1, 7) || '.' || substr(bstr, 8, 4))::numeric(11, 4) AS b
+ FROM t_base
+)
+INSERT INTO t_9_2 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 16) || '.' || substr(astr, 17, 2))::numeric(18, 2) AS a,
+ (substr(bstr, 1, 16) || '.' || substr(bstr, 17, 4))::numeric(20, 4) AS b
+ FROM t_base
+)
+INSERT INTO t_18_2 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 36) || '.' || substr(astr, 37, 2))::numeric(38, 2) AS a,
+ (substr(bstr, 1, 36) || '.' || substr(bstr, 37, 4))::numeric(40, 4) AS b
+ FROM t_base
+)
+INSERT INTO t_38_2 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 64) || '.' || substr(astr, 65, 16))::numeric(80, 16) AS a,
+ (substr(bstr, 1, 72) || '.' || substr(bstr, 73, 18))::numeric(90, 18) AS b
+ FROM t_base
+)
+INSERT INTO t_80_16 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 140) || '.' || substr(astr, 141, 35))::numeric(175, 35) AS a,
+ (substr(bstr, 1, 150) || '.' || substr(bstr, 151, 40))::numeric(190, 40) AS b
+ FROM t_base
+)
+INSERT INTO t_175_35 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 320) || '.' || substr(astr, 321, 80))::numeric(400, 80) AS a,
+ (substr(bstr, 1, 320) || '.' || substr(bstr, 321, 100))::numeric(420, 100) AS b
+ FROM t_base
+)
+INSERT INTO t_400_80 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 800) || '.' || substr(astr, 801, 200))::numeric(1000, 200) AS a,
+ (substr(bstr, 1, 780) || '.' || substr(bstr, 781, 220))::numeric(1000, 220) AS b
+ FROM t_base
+)
+INSERT INTO t_1000_200 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 200) || '.' || substr(astr, 201, 800))::numeric(1000, 800) AS a,
+ (substr(bstr, 1, 100) || '.' || substr(bstr, 101, 900))::numeric(1000, 900) AS b
+ FROM t_base
+)
+INSERT INTO t_1000_800 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 5) || '.' || substr(astr, 6, 995))::numeric(1000, 995) AS a,
+ ('0.' || substr(bstr, 1, 1000))::numeric(1000, 1000) AS b
+ FROM t_base
+)
+INSERT INTO t_1000_995 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+CREATE INDEX idx_t_9_2 ON t_9_2 USING btree(a);
+CREATE INDEX idx_t_18_2 ON t_18_2 USING btree(a);
+CREATE INDEX idx_t_38_2 ON t_38_2 USING btree(a);
+CREATE INDEX idx_t_80_16 ON t_80_16 USING btree(a);
+CREATE INDEX idx_t_175_35 ON t_175_35 USING btree(a);
+CREATE INDEX idx_t_400_80 ON t_400_80 USING btree(a);
+CREATE INDEX idx_t_1000_200 ON t_1000_200 USING btree(a);
+CREATE INDEX idx_t_1000_800 ON t_1000_800 USING btree(a);
+CREATE INDEX idx_t_1000_995 ON t_1000_995 USING btree(a);
+VACUUM FREEZE ANALYZE t_9_2;
+VACUUM FREEZE ANALYZE t_18_2;
+VACUUM FREEZE ANALYZE t_38_2;
+VACUUM FREEZE ANALYZE t_80_16;
+VACUUM FREEZE ANALYZE t_175_35;
+VACUUM FREEZE ANALYZE t_400_80;
+VACUUM FREEZE ANALYZE t_1000_200;
+VACUUM FREEZE ANALYZE t_1000_800;
+VACUUM FREEZE ANALYZE t_1000_995;
+DROP TABLE IF EXISTS t_1000_1000;
+NOTICE: table "t_1000_1000" does not exist, skipping
+CREATE TABLE t_1000_1000 AS
+SELECT
+gs as id,
+gs % 20 as id2,
+(
+ REPEAT('9', (gs % 200)) || '.' || REPEAT('9', 800)
+)::NUMERIC(1000, 800) AS a,
+(
+ REPEAT('0', (gs % 200)) || '.' || REPEAT('0', 800)
+)::NUMERIC(1000, 800) AS b
+FROM GENERATE_SERIES(1, 1000) AS gs;
+CREATE INDEX idx_t_1000_1000 ON t_1000_1000 USING btree(a);
+VACUUM FREEZE ANALYZE t_1000_1000;
diff --git a/src/test/regress/expected/numeric_op_medium.out b/src/test/regress/expected/numeric_op_medium.out
new file mode 100644
index 00000000000..13a0f568726
--- /dev/null
+++ b/src/test/regress/expected/numeric_op_medium.out
@@ -0,0 +1,497 @@
+select count(distinct a), sum(hashtext(a::text)) from t_175_35 where a > b;
+ count | sum
+-------+--------------
+ 2000 | -41380769803
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_400_80 where a > b;
+ count | sum
+-------+--------------
+ 2000 | -17147548455
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_1000_200 where a > b;
+ count | sum
+-------+--------------
+ 2000 | -87880682140
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_175_35;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+--------------+-------------+-------------+--------------+--------------+---------------+----------------+----------------+----------------+--------------+---------------+---------------
+ 328096480219 | 106894971180 | 29998131312 | 16824177090 | -98601154544 | -84446300842 | -142367027518 | -3883271108300 | -3883271108300 | -7965490469700 | -31882620402 | -214909270261 | -312201369170
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_400_80;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+---------------+--------------+--------------+-------------+-------------+-------------+----------------+----------------+----------------+---------------+--------------+---------------
+ -89713139799 | -218213819552 | 100513273638 | -79355358716 | 45044419840 | -3009527768 | 27142855016 | -3883271108300 | -3883271108300 | -7965490469700 | -248734189206 | -85072914373 | -210488126609
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_200;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+-----------+--------------+--------------+--------------+--------------+-------------+-------------+----------------+----------------+----------------+---------------+---------------+---------------
+ 534102535 | 222800148124 | 252518296200 | -40723661604 | -53446320231 | 49858073732 | 23224917403 | -3883271108300 | -3883271108300 | -7965490469700 | -212735400480 | -257086925922 | -206858526458
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_175_35
+) t1;
+ sum | sum | sum
+--------------+--------------+-------------
+ 122592282760 | 122592282760 | 73062391064
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_400_80
+) t1;
+ sum | sum | sum
+--------------+--------------+------------
+ 122592282760 | 122592282760 | 5986675256
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_200
+) t1;
+ sum | sum | sum
+--------------+--------------+--------------
+ 122592282760 | 122592282760 | -10341341254
+(1 row)
+
+select a/0 from t_175_35;
+ERROR: division by zero
+select a/0 from t_400_80;
+ERROR: division by zero
+select a/0 from t_1000_200;
+ERROR: division by zero
+select a%0 from t_175_35;
+ERROR: division by zero
+select a%0 from t_400_80;
+ERROR: division by zero
+select a%0 from t_1000_200;
+ERROR: division by zero
+select (a-a)/(a-a) from t_175_35;
+ERROR: division by zero
+select (a-a)/(a-a) from t_400_80;
+ERROR: division by zero
+select (a-a)/(a-a) from t_1000_200;
+ERROR: division by zero
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 656fe5126aca905220a5f1f28a9f4e4a | fab6538b49575bb053d186e72c71780b | 291c51941460f638c764620bf0ced7d9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ a2c9fcd02be06405b103ae3e0caa69cd | fd0e670d64fcfe8f207ee4e2e721343b | bf371cdb26b1f75fb7ed2a87a68ac6d3
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 6bdaa86b859269f0b1de287472e010c6 | 6e03994c568f38b30983abf096c4e7a2 | b3b8d5cca8a957ee6b072786cd995c29
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 656fe5126aca905220a5f1f28a9f4e4a | fab6538b49575bb053d186e72c71780b | 291c51941460f638c764620bf0ced7d9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ a2c9fcd02be06405b103ae3e0caa69cd | fd0e670d64fcfe8f207ee4e2e721343b | bf371cdb26b1f75fb7ed2a87a68ac6d3
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 6bdaa86b859269f0b1de287472e010c6 | 6e03994c568f38b30983abf096c4e7a2 | b3b8d5cca8a957ee6b072786cd995c29
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 656fe5126aca905220a5f1f28a9f4e4a | fab6538b49575bb053d186e72c71780b | 291c51941460f638c764620bf0ced7d9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ a2c9fcd02be06405b103ae3e0caa69cd | fd0e670d64fcfe8f207ee4e2e721343b | bf371cdb26b1f75fb7ed2a87a68ac6d3
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 6bdaa86b859269f0b1de287472e010c6 | 6e03994c568f38b30983abf096c4e7a2 | b3b8d5cca8a957ee6b072786cd995c29
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_seqscan = off;
+explain (costs off) select * from t_175_35 where a > 1000 order by a;
+ QUERY PLAN
+-------------------------------------------------
+ Sort
+ Sort Key: a
+ -> Bitmap Heap Scan on t_175_35
+ Recheck Cond: (a > '1000'::numeric)
+ -> Bitmap Index Scan on idx_t_175_35
+ Index Cond: (a > '1000'::numeric)
+(6 rows)
+
+explain (costs off) select a from t_175_35 where a > 1000 order by a;
+ QUERY PLAN
+------------------------------------------------
+ Index Only Scan using idx_t_175_35 on t_175_35
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select * from t_400_80 where a > 1000 order by a;
+ QUERY PLAN
+-------------------------------------------------
+ Sort
+ Sort Key: a
+ -> Bitmap Heap Scan on t_400_80
+ Recheck Cond: (a > '1000'::numeric)
+ -> Bitmap Index Scan on idx_t_400_80
+ Index Cond: (a > '1000'::numeric)
+(6 rows)
+
+explain (costs off) select a from t_400_80 where a > 1000 order by a;
+ QUERY PLAN
+------------------------------------------------
+ Index Only Scan using idx_t_400_80 on t_400_80
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select * from t_1000_200 where a > 1000 order by a;
+ QUERY PLAN
+-------------------------------------------------
+ Sort
+ Sort Key: a
+ -> Bitmap Heap Scan on t_1000_200
+ Recheck Cond: (a > '1000'::numeric)
+ -> Bitmap Index Scan on idx_t_1000_200
+ Index Cond: (a > '1000'::numeric)
+(6 rows)
+
+explain (costs off) select a from t_1000_200 where a > 1000 order by a;
+ QUERY PLAN
+----------------------------------------------------
+ Index Only Scan using idx_t_1000_200 on t_1000_200
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+reset enable_seqscan;
+update t_175_35 set a = a + 1;
+update t_400_80 set a = a + 1;
+update t_1000_200 set a = a + 1;
+select count(distinct a), sum(hashtext(a::text)) from t_175_35 where a > b;
+ count | sum
+-------+--------------
+ 2001 | -89558437612
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_400_80 where a > b;
+ count | sum
+-------+--------------
+ 2001 | -19722210906
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_1000_200 where a > b;
+ count | sum
+-------+--------------
+ 2001 | 277783880994
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_175_35;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+-------------+--------------+--------------+-------------+---------------+--------------+-------------+----------------+----------------+----------------+---------------+---------------+--------------
+ 48377864776 | -49822309848 | 178050220784 | 16824177090 | -138513601794 | 194342415281 | 28892192658 | -3668417457700 | -3883271108300 | -8180344120300 | -151559852522 | -152028559294 | -63268316355
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_400_80;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+--------------+--------------+--------------+-------------+--------------+--------------+----------------+----------------+----------------+--------------+--------------+---------------
+ -67452832312 | -89225471931 | 146580491810 | -79355358716 | -9287051683 | 147260222842 | -70335569319 | -3668417457700 | -3883271108300 | -8180344120300 | -32938146279 | -59204444070 | -108234520264
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_200;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+---------------+---------------+-----------+--------------+--------------+--------------+---------------+----------------+----------------+----------------+--------------+---------------+---------------
+ -163275209177 | -193968650050 | 679620265 | -40723661604 | -40601882101 | 147411738034 | -193122653902 | -3668417457700 | -3883271108300 | -8180344120300 | 270045436866 | -108867098352 | -172351504959
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_175_35
+) t1;
+ sum | sum | sum
+--------------+--------------+--------------
+ 122592282760 | 122592282760 | -35696475290
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_400_80
+) t1;
+ sum | sum | sum
+--------------+--------------+---------------
+ 122592282760 | 122592282760 | -203092149118
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_200
+) t1;
+ sum | sum | sum
+--------------+--------------+--------------
+ 122592282760 | 122592282760 | -75070192124
+(1 row)
+
+select a/0 from t_175_35;
+ERROR: division by zero
+select a/0 from t_400_80;
+ERROR: division by zero
+select a/0 from t_1000_200;
+ERROR: division by zero
+select a%0 from t_175_35;
+ERROR: division by zero
+select a%0 from t_400_80;
+ERROR: division by zero
+select a%0 from t_1000_200;
+ERROR: division by zero
+select (a-a)/(a-a) from t_175_35;
+ERROR: division by zero
+select (a-a)/(a-a) from t_400_80;
+ERROR: division by zero
+select (a-a)/(a-a) from t_1000_200;
+ERROR: division by zero
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 0a0a2e6cda8bed8777ff796691554d3c | fab6538b49575bb053d186e72c71780b | 291c51941460f638c764620bf0ced7d9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ ab6a736e3d9a9c028b565f452d3f8f2f | fd0e670d64fcfe8f207ee4e2e721343b | bf371cdb26b1f75fb7ed2a87a68ac6d3
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ cbbfd005c1274880a47452997697e936 | 6e03994c568f38b30983abf096c4e7a2 | b3b8d5cca8a957ee6b072786cd995c29
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 0a0a2e6cda8bed8777ff796691554d3c | fab6538b49575bb053d186e72c71780b | 291c51941460f638c764620bf0ced7d9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ ab6a736e3d9a9c028b565f452d3f8f2f | fd0e670d64fcfe8f207ee4e2e721343b | bf371cdb26b1f75fb7ed2a87a68ac6d3
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ cbbfd005c1274880a47452997697e936 | 6e03994c568f38b30983abf096c4e7a2 | b3b8d5cca8a957ee6b072786cd995c29
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 0a0a2e6cda8bed8777ff796691554d3c | fab6538b49575bb053d186e72c71780b | 291c51941460f638c764620bf0ced7d9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ ab6a736e3d9a9c028b565f452d3f8f2f | fd0e670d64fcfe8f207ee4e2e721343b | bf371cdb26b1f75fb7ed2a87a68ac6d3
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ cbbfd005c1274880a47452997697e936 | 6e03994c568f38b30983abf096c4e7a2 | b3b8d5cca8a957ee6b072786cd995c29
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_175_35 where a > 100) t1;
+ sum
+--------------
+ -27666707523
+(1 row)
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_400_80 where a > 100) t1;
+ sum
+-------------
+ 68424688419
+(1 row)
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_1000_200 where a > 100) t1;
+ sum
+--------------
+ -58373747124
+(1 row)
+
diff --git a/src/test/regress/expected/numeric_op_small.out b/src/test/regress/expected/numeric_op_small.out
new file mode 100644
index 00000000000..1b124ab3dbb
--- /dev/null
+++ b/src/test/regress/expected/numeric_op_small.out
@@ -0,0 +1,634 @@
+select count(distinct a), sum(hashtext(a::text)) from t_9_2 where a > b;
+ count | sum
+-------+-------------
+ 2000 | 25264177945
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_18_2 where a > b;
+ count | sum
+-------+--------------
+ 2000 | -18318735365
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_38_2 where a > b;
+ count | sum
+-------+-------------
+ 2000 | 77086657878
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_80_16 where a > b;
+ count | sum
+-------+-------------
+ 2000 | 51102838669
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_9_2;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+-------------+---------------+---------------+-------------+--------------+-------------+----------------+----------------+----------------+---------------+---------------+---------------
+ 185332843643 | 74295396054 | -265653763404 | -130951372896 | 33926686039 | -93220527164 | 53982021999 | -3883271108300 | -3883271108300 | -7965490469700 | -136748774422 | -156349489824 | -229516261618
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_18_2;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+------------+--------------+---------------+---------------+---------------+-------------+----------------+----------------+----------------+--------------+---------------+---------------
+ 205522402955 | 1737491244 | -98734378124 | -156012333362 | -122318628351 | -265348319222 | 53131151046 | -3883271108300 | -3883271108300 | -7965490469700 | -74716919418 | -132260756970 | -267366558171
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_38_2;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+--------------+---------------+---------------+--------------+--------------+--------------+----------------+----------------+----------------+------------+---------------+---------------
+ 188179625901 | 112456755410 | -343776078076 | -174825681708 | -71003178193 | 142439459768 | 114154703061 | -3883271108300 | -3883271108300 | -7965490469700 | 8740136096 | -345863766775 | -368207427216
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_80_16;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+-------------+---------------+--------------+---------------+---------------+---------------+----------------+----------------+----------------+--------------+---------------+---------------
+ 159659505470 | -1983944819 | -319417093044 | -11104453418 | -158100462876 | -145951162894 | -124997818319 | -3883271108300 | -3883271108300 | -7965490469700 | 146077391644 | -208253897723 | -211323905998
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_9_2
+) t1;
+ sum | sum | sum
+--------------+--------------+-------------
+ 122592282760 | 122592282760 | 47811429266
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_18_2
+) t1;
+ sum | sum | sum
+--------------+--------------+--------------
+ 122592282760 | 122592282760 | -17989105700
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_38_2
+) t1;
+ sum | sum | sum
+--------------+--------------+--------------
+ 122592282760 | 122592282760 | -79591213532
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_80_16
+) t1;
+ sum | sum | sum
+--------------+--------------+---------------
+ 122592282760 | 122592282760 | -190878347020
+(1 row)
+
+select a/0 from t_9_2;
+ERROR: division by zero
+select a/0 from t_18_2;
+ERROR: division by zero
+select a/0 from t_38_2;
+ERROR: division by zero
+select a/0 from t_80_16;
+ERROR: division by zero
+select a%0 from t_9_2;
+ERROR: division by zero
+select a%0 from t_18_2;
+ERROR: division by zero
+select a%0 from t_38_2;
+ERROR: division by zero
+select a%0 from t_80_16;
+ERROR: division by zero
+select (a-a)/(a-a) from t_9_2;
+ERROR: division by zero
+select (a-a)/(a-a) from t_18_2;
+ERROR: division by zero
+select (a-a)/(a-a) from t_38_2;
+ERROR: division by zero
+select (a-a)/(a-a) from t_80_16;
+ERROR: division by zero
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 01e08d38c9dbee1eacb6a7ac987a5cb8 | 2e819a237bbf437ea1758200e1593f4b | cbe8801ed9df407fd42bd90172ac605c
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 01e08d38c9dbee1eacb6a7ac987a5cb8 | 2e927e5aa2c1897e19ceaebbea8ef352 | 233ddd11705b908f63547b2ba13c71b9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 01e08d38c9dbee1eacb6a7ac987a5cb8 | a9bc61161e087c32a19ac2e48da03707 | ccfc186273325fb6fc75d6dbfcbb6885
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ bdef27f6ac65bf7ef274cde752e40409 | dd3a2591160b367edfb70c3ed7f78082 | 3e076e97431eff6fccc15a7f77610928
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 01e08d38c9dbee1eacb6a7ac987a5cb8 | 2e819a237bbf437ea1758200e1593f4b | cbe8801ed9df407fd42bd90172ac605c
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 01e08d38c9dbee1eacb6a7ac987a5cb8 | 2e927e5aa2c1897e19ceaebbea8ef352 | 233ddd11705b908f63547b2ba13c71b9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 01e08d38c9dbee1eacb6a7ac987a5cb8 | a9bc61161e087c32a19ac2e48da03707 | ccfc186273325fb6fc75d6dbfcbb6885
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ bdef27f6ac65bf7ef274cde752e40409 | dd3a2591160b367edfb70c3ed7f78082 | 3e076e97431eff6fccc15a7f77610928
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 01e08d38c9dbee1eacb6a7ac987a5cb8 | 2e819a237bbf437ea1758200e1593f4b | cbe8801ed9df407fd42bd90172ac605c
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 01e08d38c9dbee1eacb6a7ac987a5cb8 | 2e927e5aa2c1897e19ceaebbea8ef352 | 233ddd11705b908f63547b2ba13c71b9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 01e08d38c9dbee1eacb6a7ac987a5cb8 | a9bc61161e087c32a19ac2e48da03707 | ccfc186273325fb6fc75d6dbfcbb6885
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ bdef27f6ac65bf7ef274cde752e40409 | dd3a2591160b367edfb70c3ed7f78082 | 3e076e97431eff6fccc15a7f77610928
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_seqscan = off;
+explain (costs off) select * from t_9_2 where a > 1000 order by a;
+ QUERY PLAN
+-------------------------------------
+ Index Scan using idx_t_9_2 on t_9_2
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select a from t_9_2 where a > 1000 order by a;
+ QUERY PLAN
+------------------------------------------
+ Index Only Scan using idx_t_9_2 on t_9_2
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select * from t_18_2 where a > 1000 order by a;
+ QUERY PLAN
+---------------------------------------
+ Index Scan using idx_t_18_2 on t_18_2
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select a from t_18_2 where a > 1000 order by a;
+ QUERY PLAN
+--------------------------------------------
+ Index Only Scan using idx_t_18_2 on t_18_2
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select * from t_38_2 where a > 1000 order by a;
+ QUERY PLAN
+---------------------------------------
+ Index Scan using idx_t_38_2 on t_38_2
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select a from t_38_2 where a > 1000 order by a;
+ QUERY PLAN
+--------------------------------------------
+ Index Only Scan using idx_t_38_2 on t_38_2
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select * from t_80_16 where a > 1000 order by a;
+ QUERY PLAN
+-----------------------------------------
+ Index Scan using idx_t_80_16 on t_80_16
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+explain (costs off) select a from t_80_16 where a > 1000 order by a;
+ QUERY PLAN
+----------------------------------------------
+ Index Only Scan using idx_t_80_16 on t_80_16
+ Index Cond: (a > '1000'::numeric)
+(2 rows)
+
+reset enable_seqscan;
+update t_9_2 set a = a + 1;
+update t_18_2 set a = a + 1;
+update t_38_2 set a = a + 1;
+update t_80_16 set a = a + 1;
+select count(distinct a), sum(hashtext(a::text)) from t_9_2 where a > b;
+ count | sum
+-------+-------------
+ 2001 | 55192112654
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_18_2 where a > b;
+ count | sum
+-------+-------------
+ 2001 | 79276791029
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_38_2 where a > b;
+ count | sum
+-------+--------------
+ 2001 | 119235302735
+(1 row)
+
+select count(distinct a), sum(hashtext(a::text)) from t_80_16 where a > b;
+ count | sum
+-------+--------------
+ 2001 | 109095039015
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_9_2;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+---------------+---------------+---------------+--------------+-------------+-------------+-------------+----------------+----------------+----------------+-------------+--------------+--------------
+ -198398285973 | -268636739265 | -185796812937 | -80212568293 | 40366812062 | 50022231011 | 86359907719 | -3668417457700 | -3883271108300 | -8180344120300 | 88627277998 | -83578376254 | -56047555653
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_18_2;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+---------------+---------------+---------------+--------------+--------------+---------------+-------------+----------------+----------------+----------------+-------------+---------------+---------------
+ -198076509004 | -185931021498 | -174261325669 | -62721548562 | -87570938488 | -151149981174 | 46905358609 | -3668417457700 | -3883271108300 | -8180344120300 | 66791978874 | -162795673535 | -140859793615
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_38_2;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+---------------+---------------+---------------+---------------+-------------+--------------+-------------+----------------+----------------+----------------+--------------+---------------+---------------
+ -173598401621 | -191141226891 | -153991008984 | -174825681708 | -9994590731 | 103039770436 | 90917337737 | -3668417457700 | -3883271108300 | -8180344120300 | 212488760840 | -157402192456 | -222636044392
+(1 row)
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_80_16;
+ sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum | sum
+--------------+--------------+--------------+--------------+-------------+---------------+-------------+----------------+----------------+----------------+-------------+---------------+---------------
+ 179235436643 | 248686171421 | -90086495035 | -11104453418 | 11551494962 | -229139601272 | 32577271698 | -3668417457700 | -3883271108300 | -8180344120300 | 81696122255 | -125106925825 | -150252904102
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_9_2
+) t1;
+ sum | sum | sum
+--------------+--------------+--------------
+ 122592282760 | 122592282760 | -13202507172
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_18_2
+) t1;
+ sum | sum | sum
+--------------+--------------+------------
+ 122592282760 | 122592282760 | -696258940
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_38_2
+) t1;
+ sum | sum | sum
+--------------+--------------+-------------
+ 122592282760 | 122592282760 | 19679287493
+(1 row)
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_80_16
+) t1;
+ sum | sum | sum
+--------------+--------------+-------------
+ 122592282760 | 122592282760 | -6889081579
+(1 row)
+
+select a/0 from t_9_2;
+ERROR: division by zero
+select a/0 from t_18_2;
+ERROR: division by zero
+select a/0 from t_38_2;
+ERROR: division by zero
+select a/0 from t_80_16;
+ERROR: division by zero
+select a%0 from t_9_2;
+ERROR: division by zero
+select a%0 from t_18_2;
+ERROR: division by zero
+select a%0 from t_38_2;
+ERROR: division by zero
+select a%0 from t_80_16;
+ERROR: division by zero
+select (a-a)/(a-a) from t_9_2;
+ERROR: division by zero
+select (a-a)/(a-a) from t_18_2;
+ERROR: division by zero
+select (a-a)/(a-a) from t_38_2;
+ERROR: division by zero
+select (a-a)/(a-a) from t_80_16;
+ERROR: division by zero
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 517d9317db153749523deef82aa8a952 | 2e819a237bbf437ea1758200e1593f4b | cbe8801ed9df407fd42bd90172ac605c
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 517d9317db153749523deef82aa8a952 | 2e927e5aa2c1897e19ceaebbea8ef352 | 233ddd11705b908f63547b2ba13c71b9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 517d9317db153749523deef82aa8a952 | a9bc61161e087c32a19ac2e48da03707 | ccfc186273325fb6fc75d6dbfcbb6885
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 6f9a5a137cb8760eaf667bc7353b0f82 | dd3a2591160b367edfb70c3ed7f78082 | 3e076e97431eff6fccc15a7f77610928
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 517d9317db153749523deef82aa8a952 | 2e819a237bbf437ea1758200e1593f4b | cbe8801ed9df407fd42bd90172ac605c
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 517d9317db153749523deef82aa8a952 | 2e927e5aa2c1897e19ceaebbea8ef352 | 233ddd11705b908f63547b2ba13c71b9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 517d9317db153749523deef82aa8a952 | a9bc61161e087c32a19ac2e48da03707 | ccfc186273325fb6fc75d6dbfcbb6885
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 6f9a5a137cb8760eaf667bc7353b0f82 | dd3a2591160b367edfb70c3ed7f78082 | 3e076e97431eff6fccc15a7f77610928
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 517d9317db153749523deef82aa8a952 | 2e819a237bbf437ea1758200e1593f4b | cbe8801ed9df407fd42bd90172ac605c
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 517d9317db153749523deef82aa8a952 | 2e927e5aa2c1897e19ceaebbea8ef352 | 233ddd11705b908f63547b2ba13c71b9
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 517d9317db153749523deef82aa8a952 | a9bc61161e087c32a19ac2e48da03707 | ccfc186273325fb6fc75d6dbfcbb6885
+(1 row)
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+ md5 | md5 | md5
+----------------------------------+----------------------------------+----------------------------------
+ 6f9a5a137cb8760eaf667bc7353b0f82 | dd3a2591160b367edfb70c3ed7f78082 | 3e076e97431eff6fccc15a7f77610928
+(1 row)
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_9_2 where a > 100) t1;
+ sum
+--------------
+ -16440552090
+(1 row)
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_18_2 where a > 100) t1;
+ sum
+--------------
+ 104998257177
+(1 row)
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_38_2 where a > 100) t1;
+ sum
+------------
+ 9189839531
+(1 row)
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_80_16 where a > 100) t1;
+ sum
+---------------
+ -126456520242
+(1 row)
+
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 8fa0a6c47fb..926010b47cb 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -146,3 +146,7 @@ test: fast_default
# run tablespace test at the end because it drops the tablespace created during
# setup that other tests may use.
test: tablespace
+
+# this test is to check if numeric operations are correct
+test: numeric_op_ddl
+test: numeric_op_small numeric_op_medium numeric_op_big
\ No newline at end of file
diff --git a/src/test/regress/sql/numeric_op_big.sql b/src/test/regress/sql/numeric_op_big.sql
new file mode 100644
index 00000000000..eb01dfd2091
--- /dev/null
+++ b/src/test/regress/sql/numeric_op_big.sql
@@ -0,0 +1,267 @@
+select count(distinct a), sum(hashtext(a::text)) from t_1000_800 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_1000_995 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_1000_1000 where a > b;
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_800;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_995;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_1000;
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_800
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_995
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_1000
+) t1;
+
+select a/0 from t_1000_800;
+select a/0 from t_1000_995;
+select a/0 from t_1000_1000;
+
+select a%0 from t_1000_800;
+select a%0 from t_1000_995;
+select a%0 from t_1000_1000;
+
+select (a-a)/(a-a) from t_1000_800;
+select (a-a)/(a-a) from t_1000_995;
+select (a-a)/(a-a) from t_1000_1000;
+
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_seqscan = off;
+explain (costs off) select * from t_1000_800 where a > 1000 order by a;
+explain (costs off) select a from t_1000_800 where a > 1000 order by a;
+explain (costs off) select * from t_1000_995 where a > 1000 order by a;
+explain (costs off) select a from t_1000_995 where a > 1000 order by a;
+explain (costs off) select * from t_1000_1000 where a > 1000 order by a;
+explain (costs off) select a from t_1000_1000 where a > 1000 order by a;
+reset enable_seqscan;
+update t_1000_800 set a = a + 1;
+update t_1000_995 set a = a + 1;
+update t_1000_1000 set a = a + 1;
+
+select count(distinct a), sum(hashtext(a::text)) from t_1000_800 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_1000_995 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_1000_1000 where a > b;
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_800;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_995;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_1000;
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_800
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_995
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_1000
+) t1;
+
+select a/0 from t_1000_800;
+select a/0 from t_1000_995;
+select a/0 from t_1000_1000;
+
+select a%0 from t_1000_800;
+select a%0 from t_1000_995;
+select a%0 from t_1000_1000;
+
+select (a-a)/(a-a) from t_1000_800;
+select (a-a)/(a-a) from t_1000_995;
+select (a-a)/(a-a) from t_1000_1000;
+
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_800 t1 join t_1000_800 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_995 t1 join t_1000_995 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_1000 t1 join t_1000_1000 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_1000_800 where a > 100) t1;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_1000_995 where a > 100) t1;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_1000_1000 where a > 100) t1;
diff --git a/src/test/regress/sql/numeric_op_ddl.sql b/src/test/regress/sql/numeric_op_ddl.sql
new file mode 100644
index 00000000000..5d213c75acd
--- /dev/null
+++ b/src/test/regress/sql/numeric_op_ddl.sql
@@ -0,0 +1,173 @@
+DROP TABLE IF EXISTS t_9_2;
+DROP TABLE IF EXISTS t_18_2;
+DROP TABLE IF EXISTS t_38_2;
+DROP TABLE IF EXISTS t_80_16;
+DROP TABLE IF EXISTS t_175_35;
+DROP TABLE IF EXISTS t_400_80;
+DROP TABLE IF EXISTS t_1000_200;
+DROP TABLE IF EXISTS t_1000_800;
+DROP TABLE IF EXISTS t_1000_995;
+
+DROP TABLE IF EXISTS t_base;
+
+CREATE TABLE t_9_2 (id int, id2 int, a NUMERIC(9, 2), b NUMERIC(11, 4));
+CREATE TABLE t_18_2 (id int, id2 int, a NUMERIC(18, 2), b NUMERIC(20, 4));
+CREATE TABLE t_38_2 (id int, id2 int, a NUMERIC(38, 2), b NUMERIC(40, 4));
+CREATE TABLE t_80_16 (id int, id2 int, a NUMERIC(80, 16), b NUMERIC(90, 18));
+CREATE TABLE t_175_35 (id int, id2 int, a NUMERIC(175, 35), b NUMERIC(190, 40));
+CREATE TABLE t_400_80 (id int, id2 int, a NUMERIC(400, 80), b NUMERIC(420, 100));
+CREATE TABLE t_1000_200 (id int, id2 int, a NUMERIC(1000, 200), b NUMERIC(1000, 220));
+CREATE TABLE t_1000_800 (id int, id2 int, a NUMERIC(1000, 800), b NUMERIC(1000, 900));
+CREATE TABLE t_1000_995 (id int, id2 int, a NUMERIC(1000, 995), b NUMERIC(1000, 1000));
+
+CREATE TABLE t_base (id int, id2 int, astr text, bstr text);
+
+INSERT INTO t_base
+ SELECT g,
+ g % 20,
+ string_agg(
+ lpad(
+ (((g * 37 + k)::bigint * 1103515245 + 12345) % 1000000007)::text,
+ 9, '0'
+ ),
+ ''
+ ORDER BY k
+ ) AS astr,
+ string_agg(
+ lpad(
+ (((g * 131 + k)::bigint * 1284584123 + 89247) % 998244353)::text,
+ 9, '0'
+ ),
+ ''
+ ORDER BY k
+ ) AS bstr
+ FROM generate_series(1, 2000) g,
+ generate_series(1, 120) k
+ GROUP BY g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 7) || '.' || substr(astr, 8, 2))::numeric(9, 2) AS a,
+ (substr(bstr, 1, 7) || '.' || substr(bstr, 8, 4))::numeric(11, 4) AS b
+ FROM t_base
+)
+INSERT INTO t_9_2 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 16) || '.' || substr(astr, 17, 2))::numeric(18, 2) AS a,
+ (substr(bstr, 1, 16) || '.' || substr(bstr, 17, 4))::numeric(20, 4) AS b
+ FROM t_base
+)
+INSERT INTO t_18_2 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 36) || '.' || substr(astr, 37, 2))::numeric(38, 2) AS a,
+ (substr(bstr, 1, 36) || '.' || substr(bstr, 37, 4))::numeric(40, 4) AS b
+ FROM t_base
+)
+INSERT INTO t_38_2 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 64) || '.' || substr(astr, 65, 16))::numeric(80, 16) AS a,
+ (substr(bstr, 1, 72) || '.' || substr(bstr, 73, 18))::numeric(90, 18) AS b
+ FROM t_base
+)
+INSERT INTO t_80_16 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 140) || '.' || substr(astr, 141, 35))::numeric(175, 35) AS a,
+ (substr(bstr, 1, 150) || '.' || substr(bstr, 151, 40))::numeric(190, 40) AS b
+ FROM t_base
+)
+INSERT INTO t_175_35 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 320) || '.' || substr(astr, 321, 80))::numeric(400, 80) AS a,
+ (substr(bstr, 1, 320) || '.' || substr(bstr, 321, 100))::numeric(420, 100) AS b
+ FROM t_base
+)
+INSERT INTO t_400_80 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 800) || '.' || substr(astr, 801, 200))::numeric(1000, 200) AS a,
+ (substr(bstr, 1, 780) || '.' || substr(bstr, 781, 220))::numeric(1000, 220) AS b
+ FROM t_base
+)
+INSERT INTO t_1000_200 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 200) || '.' || substr(astr, 201, 800))::numeric(1000, 800) AS a,
+ (substr(bstr, 1, 100) || '.' || substr(bstr, 101, 900))::numeric(1000, 900) AS b
+ FROM t_base
+)
+INSERT INTO t_1000_800 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+WITH t AS (
+ SELECT id, id2,
+ (substr(astr, 1, 5) || '.' || substr(astr, 6, 995))::numeric(1000, 995) AS a,
+ ('0.' || substr(bstr, 1, 1000))::numeric(1000, 1000) AS b
+ FROM t_base
+)
+INSERT INTO t_1000_995 SELECT id, id2, a, b FROM t
+ UNION ALL SELECT id, id2, -a, -b FROM t
+ UNION ALL SELECT g AS id, g % 20 AS id2, 0 AS a, 0 AS b from generate_series(1, 100) AS g;
+
+CREATE INDEX idx_t_9_2 ON t_9_2 USING btree(a);
+CREATE INDEX idx_t_18_2 ON t_18_2 USING btree(a);
+CREATE INDEX idx_t_38_2 ON t_38_2 USING btree(a);
+CREATE INDEX idx_t_80_16 ON t_80_16 USING btree(a);
+CREATE INDEX idx_t_175_35 ON t_175_35 USING btree(a);
+CREATE INDEX idx_t_400_80 ON t_400_80 USING btree(a);
+CREATE INDEX idx_t_1000_200 ON t_1000_200 USING btree(a);
+CREATE INDEX idx_t_1000_800 ON t_1000_800 USING btree(a);
+CREATE INDEX idx_t_1000_995 ON t_1000_995 USING btree(a);
+
+VACUUM FREEZE ANALYZE t_9_2;
+VACUUM FREEZE ANALYZE t_18_2;
+VACUUM FREEZE ANALYZE t_38_2;
+VACUUM FREEZE ANALYZE t_80_16;
+VACUUM FREEZE ANALYZE t_175_35;
+VACUUM FREEZE ANALYZE t_400_80;
+VACUUM FREEZE ANALYZE t_1000_200;
+VACUUM FREEZE ANALYZE t_1000_800;
+VACUUM FREEZE ANALYZE t_1000_995;
+
+DROP TABLE IF EXISTS t_1000_1000;
+CREATE TABLE t_1000_1000 AS
+SELECT
+gs as id,
+gs % 20 as id2,
+(
+ REPEAT('9', (gs % 200)) || '.' || REPEAT('9', 800)
+)::NUMERIC(1000, 800) AS a,
+(
+ REPEAT('0', (gs % 200)) || '.' || REPEAT('0', 800)
+)::NUMERIC(1000, 800) AS b
+FROM GENERATE_SERIES(1, 1000) AS gs;
+
+CREATE INDEX idx_t_1000_1000 ON t_1000_1000 USING btree(a);
+
+VACUUM FREEZE ANALYZE t_1000_1000;
\ No newline at end of file
diff --git a/src/test/regress/sql/numeric_op_medium.sql b/src/test/regress/sql/numeric_op_medium.sql
new file mode 100644
index 00000000000..dd3802e79c1
--- /dev/null
+++ b/src/test/regress/sql/numeric_op_medium.sql
@@ -0,0 +1,267 @@
+select count(distinct a), sum(hashtext(a::text)) from t_175_35 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_400_80 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_1000_200 where a > b;
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_175_35;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_400_80;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_200;
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_175_35
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_400_80
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_200
+) t1;
+
+select a/0 from t_175_35;
+select a/0 from t_400_80;
+select a/0 from t_1000_200;
+
+select a%0 from t_175_35;
+select a%0 from t_400_80;
+select a%0 from t_1000_200;
+
+select (a-a)/(a-a) from t_175_35;
+select (a-a)/(a-a) from t_400_80;
+select (a-a)/(a-a) from t_1000_200;
+
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_seqscan = off;
+explain (costs off) select * from t_175_35 where a > 1000 order by a;
+explain (costs off) select a from t_175_35 where a > 1000 order by a;
+explain (costs off) select * from t_400_80 where a > 1000 order by a;
+explain (costs off) select a from t_400_80 where a > 1000 order by a;
+explain (costs off) select * from t_1000_200 where a > 1000 order by a;
+explain (costs off) select a from t_1000_200 where a > 1000 order by a;
+reset enable_seqscan;
+update t_175_35 set a = a + 1;
+update t_400_80 set a = a + 1;
+update t_1000_200 set a = a + 1;
+
+select count(distinct a), sum(hashtext(a::text)) from t_175_35 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_400_80 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_1000_200 where a > b;
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_175_35;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_400_80;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_1000_200;
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_175_35
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_400_80
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_1000_200
+) t1;
+
+select a/0 from t_175_35;
+select a/0 from t_400_80;
+select a/0 from t_1000_200;
+
+select a%0 from t_175_35;
+select a%0 from t_400_80;
+select a%0 from t_1000_200;
+
+select (a-a)/(a-a) from t_175_35;
+select (a-a)/(a-a) from t_400_80;
+select (a-a)/(a-a) from t_1000_200;
+
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_175_35 t1 join t_175_35 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_400_80 t1 join t_400_80 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_1000_200 t1 join t_1000_200 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_175_35 where a > 100) t1;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_400_80 where a > 100) t1;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_1000_200 where a > 100) t1;
diff --git a/src/test/regress/sql/numeric_op_small.sql b/src/test/regress/sql/numeric_op_small.sql
new file mode 100644
index 00000000000..c90537d6151
--- /dev/null
+++ b/src/test/regress/sql/numeric_op_small.sql
@@ -0,0 +1,333 @@
+select count(distinct a), sum(hashtext(a::text)) from t_9_2 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_18_2 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_38_2 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_80_16 where a > b;
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_9_2;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_18_2;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_38_2;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_80_16;
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_9_2
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_18_2
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_38_2
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_80_16
+) t1;
+
+select a/0 from t_9_2;
+select a/0 from t_18_2;
+select a/0 from t_38_2;
+select a/0 from t_80_16;
+
+select a%0 from t_9_2;
+select a%0 from t_18_2;
+select a%0 from t_38_2;
+select a%0 from t_80_16;
+
+select (a-a)/(a-a) from t_9_2;
+select (a-a)/(a-a) from t_18_2;
+select (a-a)/(a-a) from t_38_2;
+select (a-a)/(a-a) from t_80_16;
+
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_seqscan = off;
+explain (costs off) select * from t_9_2 where a > 1000 order by a;
+explain (costs off) select a from t_9_2 where a > 1000 order by a;
+explain (costs off) select * from t_18_2 where a > 1000 order by a;
+explain (costs off) select a from t_18_2 where a > 1000 order by a;
+explain (costs off) select * from t_38_2 where a > 1000 order by a;
+explain (costs off) select a from t_38_2 where a > 1000 order by a;
+explain (costs off) select * from t_80_16 where a > 1000 order by a;
+explain (costs off) select a from t_80_16 where a > 1000 order by a;
+reset enable_seqscan;
+update t_9_2 set a = a + 1;
+update t_18_2 set a = a + 1;
+update t_38_2 set a = a + 1;
+update t_80_16 set a = a + 1;
+
+select count(distinct a), sum(hashtext(a::text)) from t_9_2 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_18_2 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_38_2 where a > b;
+select count(distinct a), sum(hashtext(a::text)) from t_80_16 where a > b;
+
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_9_2;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_18_2;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_38_2;
+select sum(hashtext((a+b)::text)),
+ sum(hashtext((a-b)::text)),
+ sum(hashtext((a*b)::text)),
+ sum(hashtext((a/nullif(b, 0))::text)),
+ sum(hashtext((a%nullif(b, 0))::text)),
+ sum(hashtext((a*b+b/nullif(a, 0))::text)),
+ sum(hashtext((a-b/nullif((b-a), 0))::text)),
+ sum(hashtext((a > b)::text)),
+ sum(hashtext((a < b)::text)),
+ sum(hashtext((a = b)::text)),
+ sum(hashtext(abs(a)::text)),
+ sum(hashtext(round(a)::text)),
+ sum(hashtext(trunc(a)::text))
+from t_80_16;
+
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_9_2
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_18_2
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_38_2
+) t1;
+select sum(hashtext((rk)::text)), sum(hashtext((rn)::text)), sum(hashtext((s)::text)) from (
+ select rank() over(partition by id2 order by a+b, a, b, id) as rk,
+ row_number() over(partition by id2 order by b, a, id) as rn,
+ sum(a) over(
+ partition by id2
+ order by a, b, id
+ rows between unbounded preceding and current row
+ ) as s
+ from t_80_16
+) t1;
+
+select a/0 from t_9_2;
+select a/0 from t_18_2;
+select a/0 from t_38_2;
+select a/0 from t_80_16;
+
+select a%0 from t_9_2;
+select a%0 from t_18_2;
+select a%0 from t_38_2;
+select a%0 from t_80_16;
+
+select (a-a)/(a-a) from t_9_2;
+select (a-a)/(a-a) from t_18_2;
+select (a-a)/(a-a) from t_38_2;
+select (a-a)/(a-a) from t_80_16;
+
+set enable_hashjoin = on;
+set enable_nestloop = off;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = on;
+set enable_mergejoin = off;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+set enable_hashjoin = off;
+set enable_nestloop = off;
+set enable_mergejoin = on;
+
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_9_2 t1 join t_9_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_18_2 t1 join t_18_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_38_2 t1 join t_38_2 t2 on t1.a = t2.a;
+select md5(sum(t1.a + t2.b)::text), md5(avg(t1.a * t2.b)::text), md5(stddev(t1.a - t2.b)::text) from t_80_16 t1 join t_80_16 t2 on t1.a = t2.a;
+
+reset enable_hashjoin;
+reset enable_nestloop;
+reset enable_mergejoin;
+
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_9_2 where a > 100) t1;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_18_2 where a > 100) t1;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_38_2 where a > 100) t1;
+select sum(hashtext((a*rn)::text)) from (select a, row_number() over(order by a, id) as rn from t_80_16 where a > 100) t1;
--
2.43.0