Re: [sqlsmith] Failed assertion in joinrels.c - Mailing list pgsql-hackers

Tom Lane writes:

> What concerns me more is that what you're finding is only cases that trip
> an assertion sanity check.  It seems likely that you're also managing to
> trigger other bugs with less drastic consequences, such as "could not
> devise a query plan" failures or just plain wrong answers.

Ja, some of these are logged as well[1], but most of them are really as
undrastic as can get, and I was afraid reporting them would be more of a
nuisance.  I analysed a couple of the cache lookup failures, and they
all had a similar severreness than the example in the README[2].  The
operator ones I analysed seem due to intentionally broken operators in
the regression db.  The NestLoopParams and subplan reference one sound
interesting though…

> I'm not sure how we could identify wrong answers automatically :-(

Csmith isn't doing this either.  They discuss differential testing
though in their papers, i.e., comparing the results of different
products.  Maybe a simple metric like numbers of rows returned might
already be valuable for correctness checks.

I also thought about doing some sampling on the data and simulating
relational operations and check for witness tuples, but it is probably
not appropriate to start implementing a mini-rdbms on the client side.

> but it might be worth checking for XX000 SQLSTATE responses, since
> generally that should be a can't-happen case.  (Or if it can happen,
> we need to change the errcode.)

The sqlstate is currently missing in the reports because libpqxx is not
putting it in it's exceptions :-/.

regards,
Andreas

Footnotes:
[1]  smith=# select * from report24h;count |                                  error
-------+--------------------------------------------------------------------------43831 | ERROR:  unsupported XML
feature39496| ERROR:  invalid regular expression: quantifier operand invalid27261 | ERROR:  canceling statement due to
statementtimeout21386 | ERROR:  operator does not exist: point = point 8580 | ERROR:  cannot compare arrays of
differentelement types 5019 | ERROR:  invalid regular expression: brackets [] not balanced 4646 | ERROR:  could not
determinewhich collation to use for string comparison 2583 | ERROR:  invalid regular expression: nfa has too many
states2248 | ERROR:  operator does not exist: xml = xml 1198 | ERROR:  operator does not exist: polygon = polygon 1171
|ERROR:  cache lookup failed for index 16862  677 | ERROR:  invalid regular expression: parentheses () not balanced
172| ERROR:  cache lookup failed for index 257148   84 | ERROR:  could not find member 1(34520,34520) of opfamily 1976
55 | ERROR:  missing support function 1(34516,34516) in opfamily 1976   42 | ERROR:  operator does not exist:
city_budget= city_budget   13 | ERROR:  could not find commutator for operator 34538   10 | ERROR:  could not identify
acomparison function for type xid    4 | Connection to database failed    4 | ERROR:  cache lookup failed for index
2619   3 | ERROR:  plan should not reference subplan's variable    2 | ERROR:  cache lookup failed for index 12322    2
|ERROR:  failed to assign all NestLoopParams to plan nodes    2 | ERROR:  invalid regular expression: invalid character
range   1 | ERROR:  could not find pathkey item to sort 
(25 rows)
Time: 1158,990 ms

[2]  https://github.com/anse1/sqlsmith/blob/master/README.org