Re: MySQL's "crashme" (was Re: Performance)

Hi,

Tom Lane:
> I would not like to see us labeled "crashme unsafe" merely because
> someone is too impatient to let the test run to conclusion.

There's not much anybody can do about a backend which dies because of a
"hard" out-of-memory error which the OS only notices when all it can do
is segfault the client.

Anyway, I'll go through your list of problems and create an appropriate
patch for the beast.

Not to forget: THANK YOU for taking the time to go through this.

> > alter_drop_col=no # Alter table drop column
>
> While our ALTER TABLE support is certainly pretty weak, it should be
> noted that this test will continue to fail even when we have ALTER TABLE
> DROP COLUMN, because crashme is testing for a non-SQL-compliant syntax.
>
You mean because the COLUMN keyword is missing? Added.

> > alter_rename_table=no # Alter table rename table
>
> We have ALTER TABLE RENAME ... but not under the syntax crashme is
> testing.

TO keyword added.

> > atomic_updates=no # atomic updates
>
Test clarified and two new tests added. The result now is:

atomic_updates=no # update constraint check are deferred
atomic_updates_ok=yes # failed atomic update

MySQL has "no" and "no".

> > automatic_rowid=no # Automatic rowid
>
> Test is actually looking for a system column named "_rowid". Our OIDs
> serve the same purpose

I'll add a test for OIDs.

> > binary_items=no # binary items (0x41)
>
> We have binary literals (per the test name) and hex literals (what
> it actually appears to be testing). Unfortunately for us, ours are
> SQL92-compliant syntax, and what crashme is looking for isn't.
>
I'll tell them to fix that.

> > comment_#=no # # as comment
>
> It'd be helpful to the reader if they indicated which two of these
> conventions are SQL-compliant ... of course, that might expose the
> fact that MySQL isn't ...
>
Are there any problems with using '#' as a comment character, given that
MySQL doesn't support user-defined operators?

> > connections=32 # Simultaneous connections
>
> Should probably be noted that this is just the default limit

Noted.

> > crash_me_safe=no # crash me safe
>
> I get "yes", and I'd *really* appreciate it if you not submit this
> misleading statement.
>
I won't submit test results (not until the thing is cleaned up to
everybody's satisfaction), but I'll change the documentation of the
thing to state that

>>> Some of the tests you are about to execute require a lot of memory.
>>> Your tests _will_ adversely affect system performance. Either this
>>> crash-me test program, or the actual database back-end, _will_ die with
>>> an out-of-memory error. So might any other program on your system if it
>>> requests more memory at the wrong time.

Good enough?

> > date_zero=no # Supports 0000-00-00 dates
> Another test that only MySQL "passes".
... and SOLID.

> > except=no # except
> Unsurprisingly, hardly anyone else accepts it either.
SOLID again.

I'll mark the features that are necessary for SQL compliancy (as well as
those that actually are detrimental to it). _After_ the actual test
results are cleaned up.

> What they are looking for here is "SELECT NOT 0", which Postgres rejects
> as a type violation. SQL-compliant "NOT FALSE" would work.
>
... not with MySQL, which doesn't have FALSE in the first place. :-(

I added another test for TRUE/FALSE, and fixed the NOT-0 thing.

> > having_with_alias=no # Having on alias
>
> Again, how curious that MySQL is the only DBMS shown as passing this
> test. Couldn't be because it violates SQL92, could it?
>
No, but it's an extremely nice feature to have, IMHO.

I will not do anything about tests for extensions that won't hurt one
way or another. Classifying them, as noted above, should be sufficient.

> > insert_select=no # insert INTO ... SELECT ...
>
> We would pass this test if the crashme script weren't buggy: it fails
> to clean up after a prior test that creates a crash_q table with
> different column names.

Fixed.

> > logical_value=1 # Value of logical operation (1=1)
>
> A rather odd result, considering that what Postgres actually returns for
> "SELECT (1=1)" is 't'. But showing the correct answer isn't one of
> crashme's highest priorities...
>
> > minus_neg=no # Calculate 1--1
>
> Another case where "passing" the test means accepting MySQL's version of
> reality instead of SQL92's. All the SQL-compliant DBMSs think -- is a
> comment introducer

So does MySQL -- when the '--' is followed by a space.

They do explain that in their documentation. I have to agree with them
-- you can turn a perfectly legal SQL statement into dangerous nonsense
with this kind of comment.

>>> $dbh->do("update foo set bar = baz-$adjust where something-or-other").

That kind of mistake can be rather expensive.

> > select_string_size=+16208 # constant string size in SELECT
>
> I got 1048567 here, roughly corresponding to where I set max_buffer_size.
> Not sure why you get a smaller answer.
>
Note the '+'. I have changed the test to 2*max_row_size since anything
bigger will return an empty answer anyway.

> > select_table_update=no # Update with sub select
>
> We certainly have update with sub select. What they're looking for is
> the non-SQL-compliant syntax
> update crash_q set crash_q.b=
> (select b from crash_me where crash_q.a = crash_me.a);

Gah. Thanks; fixed.

> One could also complain about the very large number of tests that are
> checking features that are non-SQL if not downright SQL-contradictory,
> but are listed simply as bullet points with no pro or con. A naive
> reader would think that green stars are always good; they are not,
> but how are you to tell without a copy of the SQL spec in hand?
>
I'll adapt the comments, but that is quite time consuming (and the
changes are extensive) and thus will have to wait until after the first
round is in one of their next alpha-test releases.

--
Matthias Urlichs | noris network GmbH | smurf(at)noris(dot)de | ICQ: 20193661
The quote was selected randomly. Really. | http://smurf.noris.de/
--
"What did you do when the ship sank?"
"I grabbed a cake of soap and washed myself ashore."