CTE subquery referencing phantom records

Common Table Expression issue in PostgreSQL 15

The final WHERE statement in our CTE appears to be referencing more
records, then the first subquery of our CTE is returning. It works when the
first subquery is filtered by the primary key (We left a commented out
example in the WHERE statement). It fails when the first subquery is
filtered by a string. We have provided a complete example of the problem
below (The following example works in PostgreSQL 11):

Thanks
Dave

create table system_deduction_attr (
system_deduction_attr_id bigint not null,
system_deduction_id bigint not null,
start_date date null,
end_date date null,
field_description varchar(60) not null,
field_code varchar(20) not null,
field_default_value varchar(60) null,
sequence integer null,
required char(1) not null,
field_type char(1) not null,
attribute_type char(1) not null,
constraint system_deduction_attr_pk primary key(system_deduction_attr_id)
);

INSERT INTO system_deduction_attr(system_deduction_attr_id,
system_deduction_id, start_date, end_date, field_description, field_code,
field_default_value, sequence, required, field_type, attribute_type) VALUES
(1, 1229, null, null, 'Single Per Payroll Premium', 'SINGLE', null, 1, 'T',
'N', 'B'),
(2, 1229, null, null, 'Family Per Payroll Premium', 'FAMILY', null, 2, 'T',
'N', 'B'),
(3, 1229, null, null, 'Parent/Children Per Payroll Premium',
'PARENT_CHILD', null, 3, 'T', 'N', 'B'),
(4, 1229, null, null, 'Member/Spouse/Partner Per Payroll Premium',
'MEMBER_SPOUSE', null, 4, 'T', 'N', 'B'),
(5, 1229, null, null, 'Coverage Type', 'COVERAGE_TYPE', 'SINGLE', 1, 'T',
'L', 'E'),
(11, 1229, null, null, 'Effective Date', 'EFFECTIVE_DATE', '06-28-2011', 1,
'T', 'D', 'B'),
(15, 1531, '01/01/2016', null, 'Per Hourly Rate', 'PERHOURRATE', '0.02', 1,
'T', 'N', 'B'),
(17, 1533, null, null, 'Annual Dues Cap Multiplier', 'CAPMULT', '33.6', 1,
'T', 'N', 'B'),
(18, 1533, null, null, 'Percentage Dues Amount', 'PERCENTAGE', '1.45', 2,
'T', 'N', 'B'),
(6, 1230, null, null, 'Single Per Payroll Premium', 'SINGLE', null, 1, 'T',
'N', 'B'),
(7, 1230, null, null, 'Family Per Payroll Premium', 'FAMILY', null, 2, 'T',
'N', 'B'),
(8, 1230, null, null, 'Parent/Children Per Payroll', 'PARENT_CHILD', null,
3, 'T', 'N', 'B'),
(9, 1230, null, null, 'Member/Spouse/Partner Per Payroll Premium',
'MEMBER_SPOUSE', null, 4, 'T', 'N', 'B'),
(10, 1230, null, null, 'Coverage Type', 'COVERAGE_TYPE', 'SINGLE', 1, 'T',
'L', 'E'),
(12, 1230, null, null, 'Phase-In Start Date', 'EFFECTIVE_DATE',
'06-28-2011', 0, 'T', 'D', 'B'),
(20, 1567, '01/01/2020', null, 'Employee + Children : Min. Contrib. Rate',
'EMPCHLD', '0.015', 1, 'T', 'N', 'B'),
(21, 1567, '01/01/2020', null, 'Employee + Family : Min. Contrib. Rate',
'EMPFAM', '0.015', 2, 'T', 'N', 'B'),
(19, 1567, '01/01/2020', null, 'Employee + Spouse : Min. Contrib. Rate',
'EMPSPSE', '0.015', 3, 'T', 'N', 'B'),
(22, 1567, '01/01/2020', null, 'Employee Only : Min. Contrib. Rate',
'EMPONLY', '0.015', 4, 'T', 'N', 'B'),
(23, 1567, '01/01/2020', null, 'Minimum Required Contrib. Rate', 'EMPRATE',
'EMPONLY', 5, 'T', 'L', 'E'),
(31, 1575, null, null, 'Single Per Payroll Premium', 'SINGLE', null, 1,
'T', 'N', 'B'),
(32, 1575, null, null, 'Family Per Payroll Premium', 'FAMILY', null, 2,
'T', 'N', 'B'),
(33, 1575, null, null, 'Parent/Children Per Payroll', 'PARENT_CHILD', null,
3, 'T', 'N', 'B'),
(34, 1575, null, null, 'Member/Spouse/Partner Per Payroll Premium',
'MEMBER_SPOUSE', null, 4, 'T', 'N', 'B'),
(35, 1575, null, null, 'Coverage Type', 'COVERAGE_TYPE', 'SINGLE', 1, 'T',
'L', 'E'),
(25, 1573, null, null, 'Single Per Payroll Premium', 'SINGLE', null, 1,
'T', 'N', 'B'),
(26, 1573, null, null, 'Family Per Payroll Premium', 'FAMILY', null, 2,
'T', 'N', 'B'),
(27, 1573, null, null, 'Parent/Children Per Payroll', 'PARENT_CHILD', null,
3, 'T', 'N', 'B'),
(28, 1573, null, null, 'Member/Spouse/Partner Per Payroll Premium',
'MEMBER_SPOUSE', null, 4, 'T', 'N', 'B'),
(30, 1573, null, null, 'Coverage Type', 'COVERAGE_TYPE', 'SINGLE', 1, 'T',
'L', 'E'),
(14, 3, null, null, 'Application of the Annual Compensation Limit',
'COMPLIMIT', 'IGNORE', 1, 'F', 'L', 'B'),
(40, 1583, null, null, 'Coverage Type', 'COVERAGE_TYPE', 'SINGLE', 1, 'T',
'L', 'E'),
(45, 1585, null, null, 'Coverage Type', 'COVERAGE_TYPE', 'SINGLE', 1, 'T',
'L', 'E');

create table business_deduction_attr (
business_deduction_attr_id bigint not null,
business_deduction_id bigint not null,
system_deduction_attr_id bigint not null,
field_value varchar(40) null,
start_date date not null,
end_date date null,
constraint business_deduction_attr_pk primary
key(business_deduction_attr_id)
);

alter table business_deduction_attr
add constraint business_deduction_attr_system_deduction_attr_id_fkey
foreign key(system_deduction_attr_id)
references system_deduction_attr(system_deduction_attr_id)
match simple
on delete restrict
on update no action;

INSERT INTO business_deduction_attr(business_deduction_attr_id,
business_deduction_id, system_deduction_attr_id, field_value, start_date,
end_date) VALUES
(14578, 47795, 6, '353.66', '01/17/2019', '12/31/2019'),
(16757, 47795, 6, '339.19', '01/01/2020', '12/31/2020'),
(20883, 47795, 6, '344.99', '01/01/2021', '12/31/2021'),
(23384, 47795, 6, '352.19', '01/01/2022', '12/31/2022'),
(27072, 47795, 6, '435.94', '01/01/2023', null),
(14574, 47795, 7, '1084.38', '01/17/2019', '12/31/2019'),
(16754, 47795, 7, '1038.66', '01/01/2020', '12/31/2020'),
(20880, 47795, 7, '1044.01', '01/01/2021', '12/31/2021'),
(23379, 47795, 7, '1067.88', '01/01/2022', '12/31/2022'),
(27074, 47795, 7, '1317.78', '01/01/2023', null),
(14576, 47795, 8, '718.72', '01/17/2019', '12/31/2019'),
(16756, 47795, 8, '682.74', '01/01/2020', '12/31/2020'),
(20882, 47795, 8, '633.66', '01/01/2021', '12/31/2021'),
(23386, 47795, 8, '644.95', '01/01/2022', '12/31/2022'),
(27075, 47795, 8, '801.71', '01/01/2023', null),
(14575, 47795, 9, '707.31', '01/17/2019', '12/31/2019'),
(16755, 47795, 9, '688.99', '01/01/2020', '12/31/2020'),
(20881, 47795, 9, '697.8', '01/01/2021', '12/31/2021'),
(23351, 47795, 9, '712.33', '01/01/2022', '12/31/2022'),
(27073, 47795, 9, '880.53', '01/01/2023', null),
(14577, 47795, 12, '06-28-2011', '01/17/2019', null),
(14566, 47799, 6, '465.14', '01/17/2019', '12/31/2019'),
(16745, 47799, 6, '446.7', '01/01/2020', '12/31/2020'),
(20871, 47799, 6, '454.87', '01/01/2021', '12/31/2021'),
(23331, 47799, 6, '466.71', '01/01/2022', '12/31/2022'),
(27056, 47799, 6, '577.66', '01/01/2023', null),
(14562, 47799, 7, '1395.42', '01/17/2019', '12/31/2019'),
(16742, 47799, 7, '1338.58', '01/01/2020', '12/31/2020'),
(20868, 47799, 7, '1350.57', '01/01/2021', '12/31/2021'),
(23338, 47799, 7, '1387.41', '01/01/2022', '12/31/2022'),
(27058, 47799, 7, '1713.19', '01/01/2023', null),
(14564, 47799, 8, '918.26', '01/17/2019', '12/31/2019'),
(16744, 47799, 8, '875.16', '01/01/2020', '12/31/2020'),
(20870, 47799, 8, '830.34', '01/01/2021', '12/31/2021'),
(23340, 47799, 8, '849.95', '01/01/2022', '12/31/2022'),
(27059, 47799, 8, '1055.4', '01/01/2023', null),
(14563, 47799, 9, '930.27', '01/17/2019', '12/31/2019'),
(16743, 47799, 9, '904', '01/01/2020', '12/31/2020'),
(20869, 47799, 9, '917.51', '01/01/2021', '12/31/2021'),
(23336, 47799, 9, '941.39', '01/01/2022', '12/31/2022'),
(27057, 47799, 9, '1163.98', '01/01/2023', null),
(14565, 47799, 12, '06-28-2011', '01/17/2019', null),
(14572, 47808, 6, '446.52', '01/17/2019', '12/31/2019'),
(16749, 47808, 6, '428.74', '01/01/2020', '12/31/2020'),
(20875, 47808, 6, '436.52', '01/01/2021', '12/31/2021'),
(23352, 47808, 6, '448.05', '01/01/2022', '12/31/2022'),
(27060, 47808, 6, '554.51', '01/01/2023', null),
(14568, 47808, 7, '1343.47', '01/17/2019', '12/31/2019'),
(16746, 47808, 7, '1288.49', '01/01/2020', '12/31/2020'),
(20872, 47808, 7, '1299.37', '01/01/2021', '12/31/2021'),
(23355, 47808, 7, '1335.34', '01/01/2022', '12/31/2022'),
(27062, 47808, 7, '1648.58', '01/01/2023', null),
(14570, 47808, 8, '884.91', '01/17/2019', '12/31/2019'),
(16748, 47808, 8, '843.03', '01/01/2020', '12/31/2020'),
(20874, 47808, 8, '797.11', '01/01/2021', '12/31/2021'),
(23365, 47808, 8, '816.54', '01/01/2022', '12/31/2022'),
(27063, 47808, 8, '1013.94', '01/01/2023', null),
(14569, 47808, 9, '893.03', '01/17/2019', '12/31/2019'),
(16747, 47808, 9, '868.09', '01/01/2020', '12/31/2020'),
(20873, 47808, 9, '880.81', '01/01/2021', '12/31/2021'),
(23354, 47808, 9, '904.06', '01/01/2022', '12/31/2022'),
(27061, 47808, 9, '1117.67', '01/01/2023', null),
(14571, 47808, 12, '06-28-2011', '01/17/2019', null),
(16110, 51242, 6, '338.67', '12/17/2019', '12/31/2019'),
(16753, 51242, 6, '397.14', '01/01/2020', '12/31/2020'),
(20887, 51242, 6, '404.22', '01/01/2021', '12/31/2021'),
(23387, 51242, 6, '415.2', '01/01/2022', '12/31/2022'),
(27078, 51242, 6, '513.76', '01/01/2023', null),
(16106, 51242, 7, '944.88', '12/17/2019', '12/31/2019'),
(16750, 51242, 7, '1200.34', '01/01/2020', '12/31/2020'),
(20884, 51242, 7, '1209.27', '01/01/2021', '12/31/2021'),
(23390, 51242, 7, '1243.69', '01/01/2022', '12/31/2022'),
(27079, 51242, 7, '1534.89', '01/01/2023', null),
(16108, 51242, 8, '606.22', '12/17/2019', '12/31/2019'),
(16752, 51242, 8, '786.47', '01/01/2020', '12/31/2020'),
(20886, 51242, 8, '739.69', '01/01/2021', '12/31/2021'),
(23383, 51242, 8, '757.74', '01/01/2022', '12/31/2022'),
(27076, 51242, 8, '941', '01/01/2023', null),
(16107, 51242, 9, '677.34', '12/17/2019', '12/31/2019'),
(16751, 51242, 9, '804.9', '01/01/2020', '12/31/2020'),
(20885, 51242, 9, '816.22', '01/01/2021', '12/31/2021'),
(23388, 51242, 9, '838.36', '01/01/2022', '12/31/2022'),
(27077, 51242, 9, '1036.17', '01/01/2023', null),
(16109, 51242, 12, '06-28-2011', '12/17/2019', null),
(16762, 51732, 6, '351.42', '12/31/2019', '12/31/2020'),
(20891, 51732, 6, '357.49', '01/01/2021', '12/31/2021'),
(23396, 51732, 6, '367.67', '01/01/2022', '12/31/2022'),
(27083, 51732, 6, '454.79', '01/01/2023', null),
(16758, 51732, 7, '1072.78', '12/31/2019', '12/31/2020'),
(20888, 51732, 7, '1078.89', '01/01/2021', '12/31/2021'),
(23398, 51732, 7, '1111.07', '01/01/2022', '12/31/2022'),
(27081, 51732, 7, '1370.38', '01/01/2023', null),
(16760, 51732, 8, '704.63', '12/31/2019', '12/31/2020'),
(20890, 51732, 8, '656.04', '01/01/2021', '12/31/2021'),
(23400, 51732, 8, '672.66', '01/01/2022', '12/31/2022'),
(27080, 51732, 8, '835.45', '01/01/2023', null),
(16759, 51732, 9, '713.46', '12/31/2019', '12/31/2020'),
(20889, 51732, 9, '722.76', '01/01/2021', '12/31/2021'),
(23397, 51732, 9, '743.29', '01/01/2022', '12/31/2022'),
(27082, 51732, 9, '917.78', '01/01/2023', null),
(16761, 51732, 12, '06-28-2011', '12/31/2019', null),
(16768, 51734, 6, '405.46', '12/31/2019', null),
(16764, 51734, 7, '1223.55', '12/31/2019', null),
(16766, 51734, 8, '801.36', '12/31/2019', null),
(16765, 51734, 9, '821.53', '12/31/2019', null),
(16767, 51734, 12, '06-28-2011', '12/31/2019', null),
(16902, 51788, 6, '418.44', '01/02/2020', '12/31/2020'),
(20879, 51788, 6, '425.99', '01/01/2021', '12/31/2021'),
(23353, 51788, 6, '437.34', '01/01/2022', '12/31/2022'),
(27066, 51788, 6, '541.22', '01/01/2023', null),
(16898, 51788, 7, '1259.75', '01/02/2020', '12/31/2020'),
(20876, 51788, 7, '1270', '01/01/2021', '12/31/2021'),
(23371, 51788, 7, '1305.46', '01/01/2022', '12/31/2022'),
(27064, 51788, 7, '1611.53', '01/01/2023', null),
(16900, 51788, 8, '824.59', '01/02/2020', '12/31/2020'),
(20878, 51788, 8, '778.65', '01/01/2021', '12/31/2021'),
(23374, 51788, 8, '797.37', '01/01/2022', '12/31/2022'),
(27067, 51788, 8, '990.17', '01/01/2023', null),
(16899, 51788, 9, '847.49', '01/02/2020', '12/31/2020'),
(20877, 51788, 9, '859.75', '01/01/2021', '12/31/2021'),
(23372, 51788, 9, '882.64', '01/01/2022', '12/31/2022'),
(27065, 51788, 9, '1091.1', '01/01/2023', null),
(16901, 51788, 12, '06-28-2011', '01/02/2020', null),
(20896, 56710, 6, '425.55', '01/01/2021', '12/31/2021'),
(23376, 56710, 6, '436.89', '01/01/2022', '12/31/2022'),
(27069, 56710, 6, '540.66', '01/01/2023', null),
(20892, 56710, 7, '1268.76', '01/01/2021', '12/31/2021'),
(23378, 56710, 7, '1304.2', '01/01/2022', '12/31/2022'),
(27071, 56710, 7, '1609.97', '01/01/2023', null),
(20894, 56710, 8, '777.85', '01/01/2021', '12/31/2021'),
(23380, 56710, 8, '796.56', '01/01/2022', '12/31/2022'),
(27068, 56710, 8, '989.17', '01/01/2023', null),
(20893, 56710, 9, '858.87', '01/01/2021', '12/31/2021'),
(23377, 56710, 9, '881.74', '01/01/2022', '12/31/2022'),
(27070, 56710, 9, '1089.99', '01/01/2023', null),
(20895, 56710, 12, '06-28-2011', '01/01/2021', null),
(21308, 57782, 6, '454.87', '05/11/2021', '12/31/2021'),
(23402, 57782, 6, '306.3', '01/01/2022', null),
(21304, 57782, 7, '1350.57', '05/11/2021', '12/31/2021'),
(23404, 57782, 7, '939.87', '01/01/2022', null),
(21306, 57782, 8, '830.34', '05/11/2021', '12/31/2021'),
(23406, 57782, 8, '562.82', '01/01/2022', null),
(21305, 57782, 9, '620.57', '05/11/2021', null),
(21307, 57782, 12, '06-28-2011', '05/11/2021', null);

with
phase_in_date as (
select
bda.business_deduction_id,
sda.system_deduction_attr_id,
btrim(sda.field_code) as field_code,
date(btrim(bda.field_value)) as phase_in_date,
date(date(btrim(bda.field_value)) + interval '13 years') as
offset_table_max_date
from
business_deduction_attr bda
inner join system_deduction_attr sda
on (bda.system_deduction_attr_id = sda.system_deduction_attr_id)
where
--sda.system_deduction_attr_id = 12
btrim(sda.field_code) = 'EFFECTIVE_DATE'
)
select
*
from
phase_in_date
where
current_date <= offset_table_max_date;

--

<https://www.primepoint.com/>

David Bothwell

Chairman of the Board, Chief technology Officer | Primepoint, LLC

Address: 2 Springside Road, Westampton, NJ 08060

Phone: 800-600-5257

<https://www.primepoint.com/>-
<https://www.facebook.com/Primepoint.Payroll.HR/>-
<https://www.instagram.com/primepointllc/>-
<https://www.linkedin.com/company/505608?trk=tyah&trkInfo=clickedVertical%3Acompany%2CclickedEntityId%3A505608%2Cidx%3A2-2-3%2CtarId%3A1447790586761%2Ctas%3Aprimepoint>
- <https://twitter.com/primepoint>- <https://vimeo.com/user55649759>

--
This communication, and any information or attachments contained within,
may contain privileged or confidential information that is intended for the
sole use of the recipient or recipients named above. If the reader of this
message is not an intended recipient, or authorized to receive such
messages for an intended recipient, you are hereby notified that any
review, use, dissemination, copying, or distribution of this communication,
or any of its contents is strictly prohibited. If you have received this
message in error, please notify us immediately of the error by return email
and permanently remove the original message, its contents, and any copies
from your system. Thank you.