Re: [HACKERS] Re: [QUESTIONS] MySQL benchmark page - Mailing list pgsql-hackers

> > 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() :)

> > 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.

> > 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.

> > Function IN on strings in SELECT:
> > the test is: select 'monty' in ('david','monty','allan')
> > and that's not working. it gives: using an explicit cast
> Darn type system again.

Now, Bruce, learn to say "that great type system again", with feeling :)

> > 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.
> > 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 :)

> > 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...

> > Further it's taking a very
> > lot of time to test the postgres db because it's eating my memory.
> > When I test the limit of the number of joins I can do it's eating my
> > memory till there is no memory left anymore. And that's a lot (96 mb
> > real + 470mb swap).

Yup. Those joins do like to chew up memory at the moment.

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.

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.

For "Other Types", Postgres has:

abstime
bool
box
char16
char2
char4
char8
circle
datetime
filename
float4
float8
int2
int28
int4
line
lseg
money
name
path
point
polygon
reltime
text
timespan
tinterval
unknown

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 :)

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 "||".

We support CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP.
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?

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?

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...

In "Join Methods", Postgres supports subqueries.

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.

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?

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.

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.

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

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.

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.

"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.

Thanks for your time...

                     - Tom