The following bug has been logged online: Bug reference: 6131 Logged by: David Johnston Email address: pol...@yahoo.com PostgreSQL version: 9.0.4 Operating system: Windows 7 64-bit Description: Query Returning Incorrect Results Details:
The embedded script exhibits some strange behavior. When the query is run the [adjustment_paid] column for [technician] "6338B" should show +/- 25.00. Instead, if I run the last query immediately after creating the schema and inserting the data the results I get for that technician is 0.00 for both records. However, if I continue to run the query, or explicitly run ANALYZE , I then get the correct results. BTW, the other two records (techs 500A and 600A) show the expected values each time the query is run. Also, only those four records are expected. While I understand that ANALYZE should be run after loading in data I should not get blatantly incorrect results - at worse I should suffer a performance hit; though with only 40 or so records in the table performance is not really a concern. I can get this behavior to manifest consistently on my machine: PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 64-bit FWIW, I execute all the following from a commercial GUI called PostgreSQL Maestro. Thanks, David J. --Begin Script DROP VIEW IF EXISTS laborwip_payroll_entry_adjusted_ticket; DROP VIEW IF EXISTS laborwip_payroll_technician_effectiverate; DROP TABLE IF EXISTS laborwip_payroll_ticket; CREATE TABLE laborwip_payroll_ticket ( s_id varchar NOT NULL, accountnumber varchar NOT NULL, date_reference date NOT NULL, ticketnumber varchar NOT NULL, technician varchar NOT NULL, date_paid date NOT NULL, detail_category varchar NOT NULL CHECK (detail_category IN ('++','--','+0','0+','-0','0-')), hours_paid numeric(7,2) NOT NULL, amount_paid numeric(7,2) NOT NULL, rate_paid numeric(7,2) NULL, CONSTRAINT laborwip_payroll_ticket_houramountconsistency CHECK ((amount_paid >= 0 AND hours_paid >= 0) OR (amount_paid <= 0 AND hours_paid <= 0)), PRIMARY KEY (s_id, accountnumber, date_reference, ticketnumber, technician, date_paid, detail_category) ); INSERT INTO laborwip_payroll_ticket (s_id, accountnumber, date_reference, ticketnumber, technician, date_paid, detail_category, amount_paid, hours_paid, rate_paid) VALUES ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318421', '7001', '1976-06-04', '++', 25, 2.5, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318426', '6120', '1976-06-01', '++', 10, 1, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318427', '6120', '1976-06-02', '++', 15, 1.5, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318210', '6124', '1976-06-02', '++', 30, 2, 15.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318322', '6126', '1976-06-01', '++', 10, 0.5, 20.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318361', '6126', '1976-06-01', '++', 30, 1.5, 20.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318285', '6126', '1976-06-01', '++', 10, 0.5, 20.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318227', '6124', '1976-06-01', '++', 45, 3, 15.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318396', '6126', '1976-06-02', '++', 80, 4, 20.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318431', '6126', '1976-06-02', '++', 50, 2.5, 20.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '317873', '6124', '1976-06-02', '++', 15, 1, 15.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318421', '6126', '1976-06-02', '++', 50, 2.5, 20.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318262', '6287', '1976-06-01', '++', 2, 0.2, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318352', '6126', '1976-06-01', '++', 25, 1, 25.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318364', '6126', '1976-06-02', '++', 30, 1.5, 20.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318210', '6124', '1976-06-01', '++', 45, 3, 15.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318267', '7003', '1976-06-04', '--', -10, -1, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318274', '6287', '1976-06-01', '++', 2, 0.2, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318412', '6120', '1976-06-02', '++', 5, 0.5, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318182', '6338', '1976-06-02', '++', 25, 1, 25.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318279', '6338B', '1976-06-01', '++', 25, 1, 25.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318274', '7002', '1976-06-04', '--', -30, -3, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318379', '6126', '1976-06-02', '++', 30, 1.5, 20.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318349', '6126', '1976-06-01', '++', 20, 1, 20.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318257', '6287', '1976-06-01', '++', 4, 0.4, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318213', '6016', '1976-06-03', '++', 20, 1, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318254', '6124', '1976-06-01', '++', 45, 3, 15.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318431', '7002', '1976-06-04', '++', 20, 2, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318262', '7003', '1976-06-04', '--', -20, -2, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318182', '6338', '1976-06-01', '++', 75, 3, 25.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318279', '6338B', '1976-06-02', '++', 25, 1, 25.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '318267', '6287', '1976-06-01', '++', 2, 0.2, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '400000', '6338B', '1976-06-01', '+0', 0, 1, 0.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '400001', '6338B', '1976-06-01', '-0', 0, -1, 0.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '400002', '6338B', '1976-06-05', '++', 50, 5, 10.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '400003', '500A', '1976-06-05', '++', 120, 8, 15.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '400003', '500A', '1976-06-05', '+0', 0, 2, 15.00), ('[TESTSG]STORE01', 'LABORWIP', '1976-06-15', '400003', '500B', '1976-06-05', '++', 150, 10, 15.00), ('[TESTSG]STORE02', 'LABORWIP', '1976-06-15', '6000', '600A', '1976-06-05', '++', 100.00, 10, 10.00), ('[TESTSG]STORE02', 'LABORWIP', '1976-06-15', '6001', '600A', '1976-06-06', '++', 20.00, 2, 10.00), ('[TESTSG]STORE02', 'LABORWIP', '1976-06-15', '6002', '600A', '1976-06-07', '++', 30.00, 3, 10.00), ('[TESTSG]STORE02', 'LABORWIP', '1976-06-15', '6000', '600A', '1976-06-05', '--', -30.00, -3, 10.00), ('[TESTSG]STORE02', 'LABORWIP', '1976-06-15', '6000', '600A', '1976-06-05', '+0', 0.00, 1, 0.00), ('[TESTSG]STORE02', 'LABORWIP', '1976-06-15', '6000', '600A', '1976-06-05', '0+', 20.00, 0, 0.00), ('[TESTSG]STORE02', 'LABORWIP', '1976-06-15', '6003', '600B', '1976-06-05', '0+', 20.00, 0, 0.00) ; CREATE OR REPLACE VIEW laborwip_payroll_technician_effectiverate AS SELECT s_id, date_reference, accountnumber, technician, rate_paid::numeric(7,2) AS technician_rate FROM ( SELECT s_id, date_reference, accountnumber, technician, rate_paid, rate_count, (row_number() OVER (PARTITION BY s_id, date_reference, accountnumber, technician ORDER BY rate_count DESC)) AS rate_index FROM ( SELECT s_id, date_reference, accountnumber, technician, rate_paid, COUNT(*) AS rate_count FROM laborwip_payroll_ticket --WHERE detail_category IN ( '++', '--' ) GROUP BY s_id, date_reference, accountnumber, technician, rate_paid ) calc ) ranking WHERE rate_index = 1 ; ; CREATE VIEW laborwip_payroll_entry_adjusted_ticket AS SELECT s_id, date_reference, accountnumber, ticketnumber, technician, (SUM(hours_paid) * technician_rate)::numeric(10,2) AS adjustment_paid, 'ADJ' || CASE WHEN SUM(hours_paid) < 0.00 THEN '-' ELSE '+' END || 'H'::varchar AS description FROM (SELECT s_id, date_reference, accountnumber, technician, ticketnumber, hours_paid, detail_category FROM laborwip_payroll_ticket WHERE detail_category IN ('-0','+0') ) ticket_info NATURAL LEFT JOIN laborwip_payroll_technician_effectiverate GROUP BY s_id, date_reference, accountnumber, ticketnumber, detail_category, technician, technician_rate ; --DO NOT RUN ANALYZE BEFORE EXECUTING THE FOLLOWING SELECT * FROM laborwip_payroll_entry_adjusted_ticket; --You should see the incorrect (0.00) results here ANALYZE; SELECT * FROM laborwip_payroll_entry_adjusted_ticket; --And now you should have the correct (+/- 25.00) results --End Script -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs