Re: [sqlsmith] Failed assertion in joinrels.c

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 feature
39496 | ERROR: invalid regular expression: quantifier operand invalid
27261 | ERROR: canceling statement due to statement timeout
21386 | ERROR: operator does not exist: point = point
8580 | ERROR: cannot compare arrays of different element types
5019 | ERROR: invalid regular expression: brackets [] not balanced
4646 | ERROR: could not determine which collation to use for string comparison
2583 | ERROR: invalid regular expression: nfa has too many states
2248 | 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 a comparison 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