Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page

On 16 Mar 98 at 17:22, Thomas G. Lockhart wrote:

> > > first the concatenation with ||
> > > the test is select 'abc' || 'def'
> > > and that's not working in postgres. In other db's it's working.
> >
> > Looks like we have a type problem here. That type system again.
>
> "that type system" is A Good Thing(tm).
>
> tgl=> select 'abc' || 'def';
> ERROR: There is more than one operator '||'
> for types 'unknown' and 'unknown'
> You will have to retype this query using an explicit cast
>
> We are planning on working on the automatic type conversion
> capabilities; I'll add this to the list of things to address. However,
> note that this capability is in Postgres; it is misleading to state that
> it is not:
>
> tgl=> select cast('abc' as text) || 'def';
> --------
> abcdef
> (1 row)
>
> Oh, that's right, we don't support CAST() :)

crash-me is intended to test how compatible (ANSI SQL, ODBC or some
other standard) a SQL server is. If the server doesn't follow the ANSI
SQL syntax for a specific function it can't pass that test. MySQL also
fails a couple of test because of this (like the above test).

The only exception to the above is the 'extra functions' section where
we try to add all possible useful functions from all possible
databases.

>
> > > function left:
> > > the test is select left('abcd',2)
> > > looked in the manual of postgres and couldn't find the function
> > > left.
> > > this isn't working and I tried some other ways but couldn't find any
> > > working query.
> > Yep, we don't have it. We have lots of them in the oracle_compat
> > module, but not that one.
>
> Wouldn't this old Oracle/Ingres function be superceded by the SQL92
> function SUBSTRING()? We do have that, in all its glory. Doesn't appear
> in you comparison table though.

left() is an ODBC function and substring() is a SQL92 function. The
left() function is tested in the odbc section and substring is tested
in the ansi sql section.

>
> > > function current_date:
> > > the test is select current_date()
> > > that's postgres not supporting only select current_date
> > > that's also the case with current_time, current_timestamp. I will
> > > make patch so it's tested both ways.
> > Does the standard say we need those parens?
>
> SQL92 defines the syntax to be CURRENT_DATE or CURRENT_DATE(integer),
> where the integer defines a precision for the seconds field. The parens
> without the integer is non-standard afaik. Postgres has all of the forms
> in the SQL92 standard.

is fixed in the next release of crash-me.

> > > case insensitive compare:
> > > query: select b from crash_me where b = 'A'
> > > query: select b from crash_me where b = 'A' didn't return any
> > > result no (field b in table crash_me has a value of 'a'.)
> >
> > We use ~* for this. How do you control case-sensitivity? Are all
> > strings compared for lower and upper case.

MySQL has two classes of strings. Depending on context all
strings are compared binary are case sensitively. ALL compare
functions works with binary and case sensitive strings. The ~* is
more like a shortcut for LOWER(string) = LOWER(string).

> > > the transaction are still not inserted in the crash-me test because
> > > monty and I didn't have time to insert it. But what I mentioned
> > > before if you would like to see some other tests in the crash-me
> > > test you can add them by your self and send us the patch. We will
> > > add it to the release of crash-me.
>
> How about if we send the full Postgres regression test? That should
> uncover a few differences :)

As the PostgreSQL regression test doesn't test any limits of
PostgreSQL they aren't really that important. It would be nice if
some of the PostgreSQL developers really put some time into looking at
crash-me and add some interesting tests to get a better and fairer
comparison, not only between MySQL and PostgreSQL but also against
other databases.

>
> > > I am not sure the results of postgres in the crash-me page is from
> > > the final release of postgres. Because I have so much problems with
> > > postgres it's at this moment hard to test.
>
> ??? Better stick with MySQL then...

the problem is is that postgres isn't very stable and the releases
are not well tested. Approx 6 months ago some postgres developers
mentioned something about including crash-me with postgres and
testing postgres with crash-me before releasing it, but I think that
didn't happen ...

>
> It seems that the result of the test is to verify MySQL compatibility,
> rather than SQL92 compatibility or some other standard. It's OK if
> Postgres doesn't pass every test on non-standard features, but it's
> annoying that the test isn't labeled more appropriately. In fact, it
> actually claims to be trying to, in an unbiased way, help users select a
> database product. Sorry, doesn't quite conform to labeling.

I had the same discussion a while ago in the questions mailinglist.
As the crash-me page states, there is still a lot todo with crash-me.
And I think a lot of developers will agree with me that documention
comes always at last while developing something. We looked around on
the net and didn't find any other test which gives us a good base for
a selection. It shows what doesn't work with different database and
also how compatible a database is to ' standard sql'.

>
> OK, some feedback on specific information in "crash-me":
>
> SQL92 specifies that the allowed range in precision and scale for
> numeric() and decimal() types is implementation-defined. By definition
> Postgres does support both of these ANSI types.

According to ODBC, the precision of numeric() and decimal() must
be at least 15, which is making postgres to fail the above test.

>
> Plus the SQL92 ones already listed and some others which are
> Postgres-specific and not oriented toward application support; we
> wouldn't expect you to need to list those :)

crash-me already list quite many of the above.

>
> We support the CAST() function; I had the syntax wrong prior to v6.3.
> We support the "||" operator, even though your specific test causes
> trouble because of the unspecified types in the test.
> We support CHAR_LENGTH() and CHARACTER_LENGTH(), but which have the same
> problem in your test as "||".

when we test that sort of functions we test it the ANSI SQL way and
not all the ways the different databases are doing it else we could
write thousands of queries for all different db's.

>
> We support CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP.
that's fixed as I mailed before.
> We support CURRENT_USER, which does not show up in your comparison chart
> at all. My reference books do not mention SYSDATE; what standard is that
> from?

we looked in our references and couldn't find it that fast we shall
take a look at it where we found it.

>
> In "Other Functions", ~800 of the 835 Postgres functions are not listed.
> As you might know, most capabilities in Postgres are implemented as
> functions, so perhaps only ~300 of these unlisted ones are oriented
> toward application support. Shall I send you a list? :)
>
> My reference books do not list COUNT in a context other than COUNT(*)
> and COUNT(column). What is the Oracle COUNT and why is it an ANSI
> function?

It's an old label which was changed while developing. It will be
changed to the good label.

>
> In the "Function Use" table, why would PostgreSQL be listed as not
> supporting "update of column = -column"?
>
> tgl=> update t set i = -i;
> UPDATE 2
>
> seems to work for me...

it didn't in the latest test with me ... I will take a look at it
what is happening ...

>
> In "Join Methods", Postgres supports subqueries.

the crash-me test output which is now in html on crash-me.html is
from a snapshot of postgres (beta version). I have almost run all
tests on all db's again and soon you will see the new crash-me.html
page with the output of the official release of postgres 6.3. There
is subqueries ok ...

>
> In "String Handling", Postgres does have extensive comparision support,
> including "case insensitive compare". We do not allow double-quote to
> delimit strings, since that character is defined for other uses in
> SQL92. Do you show "Has Delimited Identifiers" as a feature? Postgres
> has them, as called for in SQL92.

No, this isn't tested in crash-me yet, Maybe you could send us some
tests?

>
> How about binary and hexadecimal integer input? Postgres has all of
> these SQL92 features using SQL92 syntax. Is this the same comparison as
> the non-standard "binary items (0x41)" in the Type Limits table?

the 0x41 is legal according to the ODBC standard.

>
> In the "Index Limits" table, what do you mean by "null in index"?
> Postgres supports indices containing null values. I guess you mean
> something more specific than is indicated in the label.

This is indead a test if one can have a NULL value in an index
column.
We test this by creating a index, adding a null value and selecting
this. Postgres isn't returning the NULL value.

>
> In the "Type Limits" table, Postgres does support both items "YY-MM-DD
> dates" and "0000-00-00 dates", as well as other permutations not
> appearing in your table. Postgres supports "European dates" (e.g.
> "16-03-1998") as well as "German dates" and "free-form text" dates (e.g.
> "Mon Mar 16 17:55:54 1998 MET"). Postgres has full support for time
> zones, not just the limited and brain-damaged support called for in
> SQL92.

the tests weren't run on postgres yet. But in the run of mine it is
and the test for YY-MM-DD isn't giving the good value back and
the 000-00-00 test is failing.

>
> In "Comments", Postgres supports "--" as a comment delimiter.

Yep I know. I tested it on the prompt but I had todo an return
before I could do \g. I don't know why but the same query is failing
in the test while it is just parsed as all other queries to the
postgres db.

>
> In "Other Features", Postgres supports "foreign key syntax", although it
> does not yet support foreign keys. Postgres supports "LOCK table", but
> does not have "LOCK TABLE table". Neither are SQL92 standard anyway.

maybe not the full foreign key syntax is supported.
Empress,oracle,mysql is supporting lock table and that's why we added
this test to crash-me.

>
> In "Other Limits", Postgres' maximum number of connections can be
> specified at compile-time. There is no theoretical limit to these other
> than operating system limits. Those maximums have a different meaning
> for Open Source software than for commercial packages, where you cannot
> change these kinds of parameters.

You can change most important limits on all commercial SQL servers.
crash-me only tests a default installation if not noted elsewhere. The
crash-me page also states very clearly that some limits may be
changeable with startup options.

Forget the 'Open source software' argument. This isn't true as long as
the default user can't change the code. A lot of things are also
bound by algorithms that can't be changed very easily.

>
> "crash-me" is a specific test which does not warrant extending its
> results to the tabular summary you are posting. It may be a helpful tool
> to elicit many of the entries in that summary, but any other use is
> misleading at best.
>

crash-me is a open test suite and we encourage anyone to
add new tests to it. crash-me is also the only SQL server test that
truly shows the most common limits of some of the big database
servers. (We hope to add Sybase, Informix and Adabas shortly).
If you are trying to decide how to code an compatible application,
crash-me is the only source of information you can depend on!

crash-me has also found numerous bugs in the postgreSQL code (and some
other SQL servers) and this also variants its existence :)

Greetz....
Luuk

|-----------------------------------------------------------------------|
| Luuk de Boer -> <luuk(at)pi(dot)net> |
| When you want to go fast ..... |
| Drive a cobra on the road and take a T3 line for internet ...... :-) |
| http://www.luuk.com/ -> The AC Cobra site |
|-----------------------------------------------------------------------|