How best to test near equality with numeric values?

[Kyle Jensen]

Ahoy friends, I continue (with decent success) in my use of pgtap for grading student-submitted SQL homework assignments.

I wish to test for near equality in numeric value, e.g. I would like a test like this to pass

# Failed test 1: "should match correct answer results"
#     Extra records:
#         (1.00,4.82,97.00)
#     Missing records:
#         (1.0,4.8,97.0)

Therein a student included more significant figures than they ought to have, but I don't care and want to count it as correct.

** How do you think I could approach that problem?

Sincerely, Kyle

[Steve Baldwin]

Can you use the postgres round() function to compare values rounded to the specified number of decimal places?

--
You received this message because you are subscribed to the Google Groups "pgTAP Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgtap-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pgtap-users/1d7ad2a5-7f32-4a21-82e7-fce3b61d5368n%40googlegroups.com.

[Kyle Jensen]

Definitely! I suppose though I’ll need to write a version of `results_eq` that checks types upon row mismatch and then applies ROUND as needed. I’m not sure how to do that. 

[David E. Wheeler]

On May 5, 2021, at 15:39, Kyle Jensen <klje...@gmail.com> wrote:

> Definitely! I suppose though I’ll need to write a version of `results_eq` that checks types upon row mismatch and then applies ROUND as needed. I’m not sure how to do that.

Or just round the values in both sets of records in the queries you pass to results_eq(), no?

D

[Kyle Jensen]

Yes, but I don't know what queries I'm getting from students. Take for instance this case

# Failed test 1: "should match correct answer results"

#     Results differ beginning at row 1:
#         have: (7,Robert,King,124568.23485103557,16727.755251732353,0.13428588172366873)
#         want: (7,Robert,King,124568.23485103557,16727.755139052868,0.13428588081910842)
# Looks like you failed 1 test of 1

I know what query I sent in. I don't know what query the student made...could have been totally different. Some students, of course, will even have incorrect columns and such. Here, I can tell the student did the computation correctly but in such a way that the results differ ever-so-slightly. I have many different questions of this nature wherein I've asked students to compute some stats and there might have been different ways to accomplish that (CTEs, window functions, different orders, etc)

That is to say, I can think of a *general* way in which to alter the incoming student query to compare it to my query.

Sincerely, Kyle

[Kyle Jensen]

Sorry - edit, "I _can't_ think of a general way"...

[Steve Baldwin]

I can't see any way to easily influence a number format, or rounding (at the session level). Given that you have control over _your_ query, could you stipulate that the query your students submit should have "numeric results rounded to n decimal places"?

Making a version of results_eq that examines the data type of each column and if a numeric type, rounds the column value _may_ be possible, but it would be quite complex, and a lot slower than the existing function. If you want to go down this path, the postgres mailing list (pgsql-...@lists.postgresql.org) may provide guidance.

Cheers,

Steve

[Kyle Jensen]

Friends, I achieved what I needed (but sorely lamented the absence of first class functions and dynamic access to columns in pl/pgsql - whew!). You can see my solution here https://github.com/kljensen/pgtap-results-approx-eq

I wrote a pgTAP test function that looks like

SELECT pg_temp.results_approx_equal(
    $$select * from (values (1,50), (1, 10)) vals(a, b)$$,
    $$select * from (values (2,55), (0, 5)) vals(a, b)$$,
    json_build_object('a', 1, 'b', 5)
);

That test passes. In that code block I'm saying "consider two rows to be equivalent if their a columns differ by no more than 1 and their b columns differ by no more than 5.

Thanks for the input and help.

Sincerely, Kyle

[Nasby, Jim]

Another option would be to create a numeric operator that ignores trailing zeros.

 

From: <pgtap...@googlegroups.com> on behalf of Kyle Jensen <klje...@gmail.com>
Date: Sunday, May 9, 2021 at 6:26 PM
To: Steve Baldwin <steve....@gmail.com>
Cc: "David E. Wheeler" <da...@justatheory.com>, pgTAP Users <pgtap...@googlegroups.com>
Subject: RE: [EXTERNAL] [pgtap-users] How best to test near equality with numeric values?

 

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you can confirm the sender and know the content is safe.

 

--

You received this message because you are subscribed to the Google Groups "pgTAP Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgtap-users...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/pgtap-users/CAHV6TV4b-kdm3ztFh9zbfwVf9iaAkL1946861G1SvcNTH%2BYWgg%40mail.gmail.com.

[David E. Wheeler]

On May 11, 2021, at 17:12, 'Nasby, Jim' via pgTAP Users <pgtap...@googlegroups.com> wrote:

> Another option would be to create a numeric operator that ignores trailing zeros.

Oh that’s a fun idea. How would you use it in comparing result sets, though?

D