pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL standards in Mysql
9+ messages / 7 participants
[nested] [flat]
* SQL standards in Mysql
@ 2008-02-22 18:57 Ken Johanson <pg-user@kensystem.com>
2008-02-22 19:57 ` Re: SQL standards in Mysql Alvaro Herrera <alvherre@commandprompt.com>
2008-02-23 05:50 ` Re: SQL standards in Mysql Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Ken Johanson @ 2008-02-22 18:57 UTC (permalink / raw)
To: pgsql-sql
Here's one Mysql developer's response to adding (fixing) the
integer/bigint/tinyint types to their CAST function:
http://bugs.mysql.com/bug.php?id=34562
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL standards in Mysql
2008-02-22 18:57 SQL standards in Mysql Ken Johanson <pg-user@kensystem.com>
@ 2008-02-22 19:57 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-02-23 00:13 ` Re: SQL standards in Mysql Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2008-02-22 19:57 UTC (permalink / raw)
To: Ken Johanson <pg-user@kensystem.com>; +Cc: pgsql-sql
Ken Johanson wrote:
> Here's one Mysql developer's response to adding (fixing) the
> integer/bigint/tinyint types to their CAST function:
>
> http://bugs.mysql.com/bug.php?id=34562
So they are anal too, but in the opposite direction?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL standards in Mysql
2008-02-22 18:57 SQL standards in Mysql Ken Johanson <pg-user@kensystem.com>
2008-02-22 19:57 ` Re: SQL standards in Mysql Alvaro Herrera <alvherre@commandprompt.com>
@ 2008-02-23 00:13 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-23 01:39 ` Re: SQL standards in Mysql Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
0 siblings, 1 reply; 9+ messages in thread
From: Scott Marlowe @ 2008-02-23 00:13 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Ken Johanson <pg-user@kensystem.com>; pgsql-sql
On Fri, Feb 22, 2008 at 1:57 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Ken Johanson wrote:
> > Here's one Mysql developer's response to adding (fixing) the
> > integer/bigint/tinyint types to their CAST function:
> >
> > http://bugs.mysql.com/bug.php?id=34562
>
> So they are anal too, but in the opposite direction?
No, they're just hopelessly lost and making good time. Or lazy. Or
both. I'm not sure.
There's example after example of things in the mysql bug database that
should make anyone considering it as a database engine cringe and walk
away shaking their head. for instance, someone JUST submitted this
bug:
http://bugs.mysql.com/bug.php?id=34231
Which is about a problem with the mysql packages including a
statically linked zlib which makes it impossible to compile php
against it. Problem is, their inability to properly package / not
package zlib with mysql is an ongoing problem. As far back as 2004 it
showed up. Then was fixed, then showed up again, then was fixed.
Here's the most recent "we fixed it! Oh shit it's broken again" bug record:
http://bugs.mysql.com/bug.php?id=15255
Before that we had
http://bugs.mysql.com/bug.php?id=3700
After that we had
http://bugs.mysql.com/bug.php?id=28569
and now the latest reports. It's like the Keystone Kops create RPM
packages reading those messages. Pick any subject area. Foreign
keys, SQL compliance, and on and on and you'll find dozens of the same
bugs coming and going as one person stomps it and another apparently
reinstates it.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL standards in Mysql
2008-02-22 18:57 SQL standards in Mysql Ken Johanson <pg-user@kensystem.com>
2008-02-22 19:57 ` Re: SQL standards in Mysql Alvaro Herrera <alvherre@commandprompt.com>
2008-02-23 00:13 ` Re: SQL standards in Mysql Scott Marlowe <scott.marlowe@gmail.com>
@ 2008-02-23 01:39 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-23 05:03 ` Re: SQL standards in Mysql Scott Marlowe <scott.marlowe@gmail.com>
2008-02-23 05:34 ` Re: SQL standards in Mysql Scott Marlowe <scott.marlowe@gmail.com>
2008-02-27 18:31 ` Re: SQL standards in Mysql Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 3 replies; 9+ messages in thread
From: Dean Gibson (DB Administrator) @ 2008-02-23 01:39 UTC (permalink / raw)
To: pgsql-sql
On 2008-02-22 16:13, Scott Marlowe wrote:
> There's example after example of things in the mysql bug database that should make anyone considering it as a database engine cringe and walk away shaking their head.
I don't understand why anyone wanting a real SQL DB would pick MySQL.
Four years ago I knew nothing about SQL (I still pronounce it S-Q-L),
but wanted to set up a real DB with the maximum flexibility. So, I:
1. Asked a friend in the DB world (primarily Oracle) what he
recommended, and he said "MySQL".
2. I then bought a book on SQL ("Using SQL", by Rafe Colburn, ISBN
0-7897-1974-6, © 2000 Que Publishing), which mentions every common SQL
DB __except__ PostgreSQL.
So, I went with PostgreSQL. Why? From the book, it was clear that
MySQL lacked so many features of a decent SQL DB. In particular (at the
time) VIEWs and sub-selects. I didn't know much about VIEWs and
sub-selects, but it appeared to me that they were pretty
important/powerful features that any SQL DB should have. PostgreSQL was
the only one left standing (at least on my budget). (I ran it on a
Pentium 233 for a year before I upgraded the hardware.)
As of now, MySQL has VIEWs and sub-selects, but there appear to be a
number of other little "gotchas" that lurk (which the original poster of
this and the related threads has so amply illustrated). One of the
advantage of standards-compliant software is that, while you may be
surprised by some feature, that feature has been examined by a number of
people and (typically) found to be the best way of being consistent in a
broader view, rather than a feature that has been written (or not) for
the sake of expediency in a particular implementation. Further, many
standards are features that, while sometimes not met by existing
implementations, are at least a goal of consistency and functionality
that is aspired to (and usually planned for in future releases).
The sad fact is that we live in a world of expediency (not to mention a
bit of hype). Many people don't even do the elementary research that I
did before glibly picking a DB for their server, in their rush to be the
next dot-com (or other) success. As a result, PostgreSQL is not
supported by some software packages. For example, I think phpBB is the
only major message board software that supports PostgreSQL (see
http://www.phpbb.com/about/features/compare.php ), and in fact has for
some time. Of course, they have a DB abstraction layer (wow, what an
concept!), which allows them to easily support a number of DBs. Of
course, what does that tell you about the level of design and
professionalism of phpBB versus the others? Note that I have no
connection with the phpBB project, and in fact find many of the
developers arrogant. I'm just saying that any software that doesn't
support a number of DBs probably wasn't designed and/or implemented
properly.
Hopefully, some event in the near future will tip the scales in the
public perception of SQL DBs, and PostgreSQL will get better respect.
-- Dean
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL standards in Mysql
2008-02-22 18:57 SQL standards in Mysql Ken Johanson <pg-user@kensystem.com>
2008-02-22 19:57 ` Re: SQL standards in Mysql Alvaro Herrera <alvherre@commandprompt.com>
2008-02-23 00:13 ` Re: SQL standards in Mysql Scott Marlowe <scott.marlowe@gmail.com>
2008-02-23 01:39 ` Re: SQL standards in Mysql Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2008-02-23 05:03 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2008-02-23 05:03 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; +Cc: pgsql-sql
On Fri, Feb 22, 2008 at 7:39 PM, Dean Gibson (DB Administrator)
<postgresql@ultimeth.com> wrote:
> So, I went with PostgreSQL. Why? From the book, it was clear that
> MySQL lacked so many features of a decent SQL DB. In particular (at the
> time) VIEWs and sub-selects.
Note that unless someone's done some hacking recently, mysql uses
nested loops for subselects. Which is fine for a few dozen or hundred
entries. not so much for 1M rows. Handling subselects is as much
about performance as it is about convenience. There was a time a few
years ago when oracle would beat the pants off postgresql or mysql if
you did this:
delete from tablea where id in (select id from tableb);
when tableb was pretty large. PostgreSQL hackers fixed that issue
some time ago. MySQL, as far as I know, has no plans to fix their
poor performance. Because somebody checked off subselect and that's
all they needed, a check box.
> the only one left standing (at least on my budget). (I ran it on a
> Pentium 233 for a year before I upgraded the hardware.)
Oh man, this takes me back. My first two postgresql servers were both
pg v6.5.3 or so and ran on:
Pentium-100 with 64 Meg of ram,
Quad CPU Sparc-20 (4x50MHz CPUs) with a stack of SCSI hard drives
running the last version of RH to support sparc back then.
> supported by some software packages. For example, I think phpBB is the
> only major message board software that supports PostgreSQL (see
> http://www.phpbb.com/about/features/compare.php ), and in fact has for
> some time. Of course, they have a DB abstraction layer (wow, what an
> concept!), which allows them to easily support a number of DBs. Of
> course, what does that tell you about the level of design and
> professionalism of phpBB versus the others? Note that I have no
> connection with the phpBB project, and in fact find many of the
> developers arrogant. I'm just saying that any software that doesn't
> support a number of DBs probably wasn't designed and/or implemented
> properly.
Well, somebody needs to teach them how this thing called patch works.
The fact that all the hacks are installed by hand makes me queezy
about messing with any modifications.
Note that w-agora is a pretty nice bbs that supports mysql, oracle,
and pgsql. I like the interface, but it doesn't look like there's a
lot of recent development on it. I like the fact that it uses a nice
threaded interface and can work with oracle, pgsql, or mysql.
> Hopefully, some event in the near future will tip the scales in the
> public perception of SQL DBs, and PostgreSQL will get better respect.
I think it's slowly happening, not one big event, but a bunch of
little experiences adding up. I think one of the things helping that
in the long run is the number of very capable DBAs that are slowly
coming to pgsql from other dbs, like DB2 or Oracle or Informix.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL standards in Mysql
2008-02-22 18:57 SQL standards in Mysql Ken Johanson <pg-user@kensystem.com>
2008-02-22 19:57 ` Re: SQL standards in Mysql Alvaro Herrera <alvherre@commandprompt.com>
2008-02-23 00:13 ` Re: SQL standards in Mysql Scott Marlowe <scott.marlowe@gmail.com>
2008-02-23 01:39 ` Re: SQL standards in Mysql Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2008-02-23 05:34 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Scott Marlowe @ 2008-02-23 05:34 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; +Cc: pgsql-sql
On Fri, Feb 22, 2008 at 11:03 PM, Mail Delivery Subsystem
<mailer-daemon@googlemail.com> wrote:
> This is an automatically generated Delivery Status Notification
>
> Delivery to the following recipient failed permanently:
>
> postgresql@ultimeth.com
> To: "Dean Gibson (DB Administrator)" <postgresql@ultimeth.com>
Look, if you're going to send email to this list from an address that
doesn't accept email from anything but this list, and since this list
by default is a "reply to all" list (i.e. people hit reply to all) the
LEAST you can do is reconfigure your Mail client to change the reply
to field to point to whatever list it is you are sending to. Or have
the decency to program your MTA to just throw those messages away.
Bouncing messages from a public list is kinda rude.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL standards in Mysql
2008-02-22 18:57 SQL standards in Mysql Ken Johanson <pg-user@kensystem.com>
2008-02-22 19:57 ` Re: SQL standards in Mysql Alvaro Herrera <alvherre@commandprompt.com>
2008-02-23 00:13 ` Re: SQL standards in Mysql Scott Marlowe <scott.marlowe@gmail.com>
2008-02-23 01:39 ` Re: SQL standards in Mysql Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
@ 2008-02-27 18:31 ` Steve Crawford <scrawford@pinpointresearch.com>
2 siblings, 0 replies; 9+ messages in thread
From: Steve Crawford @ 2008-02-27 18:31 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql@ultimeth.com>; +Cc: pgsql-sql
Dean Gibson (DB Administrator) wrote:
> ...For example, I think phpBB is the only major message board software
> that supports PostgreSQL (see
> http://www.phpbb.com/about/features/compare.php ), and in fact has for
> some time. Of course, they have a DB abstraction layer (wow, what an
> concept!), which allows them to easily support a number of DBs. Of
> course, what does that tell you about the level of design and
> professionalism of phpBB versus the others?...
In most cases it tells me is that the developers are willing to limit
themselves to the lowest-common-denominator of features among the
supported databases in exchange for the ability to run on a variety of
backends. This is a perfectly legitimate decision for products where
only basic database features are necessary.
What I find scary is complex products that have been "ported" from
something like mySQL to PostgreSQL. This often means that the product
has severely limited its use of appropriate PostgreSQL features in order
to remain compatible with the other backends.
Cheers,
Steve
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL standards in Mysql
2008-02-22 18:57 SQL standards in Mysql Ken Johanson <pg-user@kensystem.com>
@ 2008-02-23 05:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-23 21:06 ` Re: SQL standards in Mysql Aarni Ruuhimäki <aarni@kymi.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2008-02-23 05:50 UTC (permalink / raw)
To: Ken Johanson <pg-user@kensystem.com>; +Cc: pgsql-sql
Ken Johanson <pg-user@kensystem.com> writes:
> Here's one Mysql developer's response to adding (fixing) the
> integer/bigint/tinyint types to their CAST function:
> http://bugs.mysql.com/bug.php?id=34562
Hmm ... while I'm certainly not someone to defend mysql on a regular
basis, I can see their point of view here. They are supporting a
limited (and clearly documented) subset of the SQL spec in this area,
so it's surely a feature request rather than a bug that they don't
support more. The disagreement seems to come down to what the priority
of the feature addition ought to be. We have a few sore spots of our
own on questions like this one, so I'm disinclined to throw the first
stone ...
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SQL standards in Mysql
2008-02-22 18:57 SQL standards in Mysql Ken Johanson <pg-user@kensystem.com>
2008-02-23 05:50 ` Re: SQL standards in Mysql Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-23 21:06 ` Aarni Ruuhimäki <aarni@kymi.com>
0 siblings, 0 replies; 9+ messages in thread
From: Aarni Ruuhimäki @ 2008-02-23 21:06 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>
On Saturday 23 February 2008 07:50, Tom Lane wrote:
>Hmm ... while ...
> so I'm disinclined to throw the first
> stone ...
Meanwhile,
Throw cones, not stones.
http://cfx.kymi.com/lotsacones.jpg
These things/projectiles hurt not so much. And it's fun !
BR,
--
Aarni Ruuhimäki
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2008-02-27 18:31 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-22 18:57 SQL standards in Mysql Ken Johanson <pg-user@kensystem.com>
2008-02-22 19:57 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-02-23 00:13 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-23 01:39 ` Dean Gibson (DB Administrator) <postgresql@ultimeth.com>
2008-02-23 05:03 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-23 05:34 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-27 18:31 ` Steve Crawford <scrawford@pinpointresearch.com>
2008-02-23 05:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-23 21:06 ` Aarni Ruuhimäki <aarni@kymi.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox