Fabien,

> Moreover, there are many SQL flavors around, so whatever the detailed
> syntax I learn them, it won't be the one they will have to face if the
> database they use is different. So why bother?

There are so many Java flavors around, why bother teaching the students syntax 
at all?  The flavor they work with in your class will probably be different 
from what they have on the job.

See my point?

One of the primary reasons for the existence of international standards is 
education.  If you stick to Beginning & Intermediate SQL92, you can give your 
students a set of SQL syntax that they can use on 80% of the RDBMSes in 
service, including Postgres, HSQLDB, SQLite, Oracle, and SQL Server.   This 
is serving them well.

Were I teaching a class with a SQL component, using PostgreSQL as a tool, I 
would be very careful to avoid letting my students use an extensions to SQL; 
no "!=", no "SELECT DISTINCT ON" and no alias references in the GROUP BY 
clause.

Allowing your students to use a non-standard operator that will fail them the 
instant they leave the classroom is serving them badly indeed.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Wed, 7 Apr 2004 08:33:27 -0700
Josh Berkus <josh@agliodbs.com>
Re: make == as = ?
* Josh Berkus (josh@agliodbs.com) wrote:
> Were I teaching a class with a SQL component, using PostgreSQL as a tool, I 
> would be very careful to avoid letting my students use an extensions to SQL; 
> no "!=", no "SELECT DISTINCT ON" and no alias references in the GROUP BY 
> clause.

I'd really like PostgreSQL to somehow warn me when I start doing things
like this.  It's not my intent to use PostgreSQL-specific SQL in general
so when I start doing it I'd like to know.  In some cases there may not
be an option, of course, but especially in cases where there's a 'right
way' I'd love for PostgreSQL to spit out a warning which tells me what
the right way is.

ie: "select * from table_name where column_name != '1';"
Warning: '!=' is not valid SQL, use '<>' instead.

This would help me, at least, write correct and portable SQL. :)

	Stephen
Wed, 7 Apr 2004 12:24:00 -0400
Stephen Frost <sfrost@snowman.net>
Re: make == as = ?
Stephen Frost wrote:
-- Start of PGP signed section.
> * Josh Berkus (josh@agliodbs.com) wrote:
> > Were I teaching a class with a SQL component, using PostgreSQL as a tool, I 
> > would be very careful to avoid letting my students use an extensions to SQL; 
> > no "!=", no "SELECT DISTINCT ON" and no alias references in the GROUP BY 
> > clause.
> 
> I'd really like PostgreSQL to somehow warn me when I start doing things
> like this.  It's not my intent to use PostgreSQL-specific SQL in general
> so when I start doing it I'd like to know.  In some cases there may not
> be an option, of course, but especially in cases where there's a 'right
> way' I'd love for PostgreSQL to spit out a warning which tells me what
> the right way is.
> 
> ie: "select * from table_name where column_name != '1';"
> Warning: '!=' is not valid SQL, use '<>' instead.
> 
> This would help me, at least, write correct and portable SQL. :)

Added to TODO:

	* Add a session mode to warn about non-standard SQL usage

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 7 Apr 2004 13:04:27 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: make == as = ?

> > This would help me, at least, write correct and portable SQL. :)
>
> Added to TODO:
>
> 	* Add a session mode to warn about non-standard SQL usage

So it seems that having C-like operators would hurt a lot;-)

So you want to generate warnings for SERIAL, TEXT and a bunch of other
types, WITH[OUT] OIDS, RULE, ~ regular expressions, arrays, any use of
pg_catalog instead of information_schema (I may be wrong in the list, I
don't have the standard at hand, but I think I'm right in the spirit)...

This is going to be noisy;-)

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Thu, 8 Apr 2004 08:34:40 +0200 (MEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: make == as = ?
Fabien COELHO wrote:
> 
> > > This would help me, at least, write correct and portable SQL. :)
> >
> > Added to TODO:
> >
> > 	* Add a session mode to warn about non-standard SQL usage
> 
> So it seems that having C-like operators would hurt a lot;-)
> 
> So you want to generate warnings for SERIAL, TEXT and a bunch of other
> types, WITH[OUT] OIDS, RULE, ~ regular expressions, arrays, any use of
> pg_catalog instead of information_schema (I may be wrong in the list, I
> don't have the standard at hand, but I think I'm right in the spirit)...
> 
> This is going to be noisy;-)

Yep, it sure is going to be noisy.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 8 Apr 2004 10:51:05 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: make == as = ?
On Thu, 8 Apr 2004, Bruce Momjian wrote:

> Fabien COELHO wrote:
> > 
> > > > This would help me, at least, write correct and portable SQL. :)
> > >
> > > Added to TODO:
> > >
> > > 	* Add a session mode to warn about non-standard SQL usage
> > 
> > So it seems that having C-like operators would hurt a lot;-)
> > 
> > So you want to generate warnings for SERIAL, TEXT and a bunch of other
> > types, WITH[OUT] OIDS, RULE, ~ regular expressions, arrays, any use of
> > pg_catalog instead of information_schema (I may be wrong in the list, I
> > don't have the standard at hand, but I think I'm right in the spirit)...
> > 
> > This is going to be noisy;-)
> 
> Yep, it sure is going to be noisy.

Could we consider a three (or more) way setting, for what to do?  
Something like:

sql_noncompliance_mode = error;
sql_noncompliance_mode = warn / notice;
sql_noncompliance_mode = ignore;

Just wondering...


Thu, 8 Apr 2004 10:26:03 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: make == as = ?
scott.marlowe wrote:
> On Thu, 8 Apr 2004, Bruce Momjian wrote:
> 
> > Fabien COELHO wrote:
> > > 
> > > > > This would help me, at least, write correct and portable SQL. :)
> > > >
> > > > Added to TODO:
> > > >
> > > > 	* Add a session mode to warn about non-standard SQL usage
> > > 
> > > So it seems that having C-like operators would hurt a lot;-)
> > > 
> > > So you want to generate warnings for SERIAL, TEXT and a bunch of other
> > > types, WITH[OUT] OIDS, RULE, ~ regular expressions, arrays, any use of
> > > pg_catalog instead of information_schema (I may be wrong in the list, I
> > > don't have the standard at hand, but I think I'm right in the spirit)...
> > > 
> > > This is going to be noisy;-)
> > 
> > Yep, it sure is going to be noisy.
> 
> Could we consider a three (or more) way setting, for what to do?  
> Something like:
> 
> sql_noncompliance_mode = error;
> sql_noncompliance_mode = warn / notice;
> sql_noncompliance_mode = ignore;

I think a boolean that turns on warnings would be enough.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 8 Apr 2004 12:39:50 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: make == as = ?

> > Could we consider a three (or more) way setting, for what to do?
> > Something like:
> >
> > sql_noncompliance_mode = error;
> > sql_noncompliance_mode = warn / notice;
> > sql_noncompliance_mode = ignore;
>
> I think a boolean that turns on warnings would be enough.

Hmmm.

I could think of:

	sql_version = sql2/sql92/sql3/sql99/postgresql/oracle/mysql/extended

Or maybe with a simple integer...

	sql_strict_compliance = on/off

On strict, errors could be generated for incompatible features in user
sql queries. Otherwise, only warnings or notice appear for misfeatures.

It would be possible to mimic (theoretically) other SQL implementations.

With "extended", I could have c-like operators;-) : ! && || == !=

Still dreaming.

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Sat, 10 Apr 2004 11:13:18 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: make == as = ?
Fabien,

> With "extended", I could have c-like operators;-) : ! && || == !=
>
> Still dreaming.

And still not listening, I guess.   

You can create your own, C-like operators any time you want to.   In fact, you 
could launch a project on pgFoundry (as soon as it's up) for a package of 
C-like operators.   

But they're not going to be included in the core distribution.   We value the 
SQL standard on this project, and we're not going to include a bunch of 
non-standard operators just becuase it's convenient for some people to  
remember.

Please see my previous e-mail about the value of international standards for 
educators.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Sat, 10 Apr 2004 07:31:59 -0700
Josh Berkus <josh@agliodbs.com>
Re: make == as = ?

Dear Josh,

> > Still dreaming.
> And still not listening, I guess.

I agree that listening is something difficult, for me as for every body.
Also, listening and talking in another language is not easy.


> You can create your own, C-like operators any time you want to.

I ***already*** did that. It does not work correctly.
Maybe because of my lack of know how:

1/ the operator precedence is not right.

   a || b && c is interpreted as (a || b) && c because new operators
   are simply left associative. I have found where I can set the
   operator precedence in pg_operator.

2/ you cannot add simply '==' for not-yet-defined types.

3/ the optimizer should need to know about AND/OR/NOT semantics.
   Also, a partial index with a AND  won't be used with "&&".


> In fact, you could launch a project on pgFoundry (as soon as it's up)
> for a package of C-like operators.

Sure. The package just need to fix the backend parser. Adding these
features is a 10 lines patch. I'm going to found a new project for a 10
lines patch...

Also, I thought - wrongly - that it might be useful to others, and
would not hurt anyone.

Definitly not a shared point of view from the answers I get!

Moreover, I noticed so many "convenient non standard" features in
postgresql that suggesting one more did not seem so stupid to me at first
glance.


> But they're not going to be included in the core distribution. We value
> the SQL standard on this project, and we're not going to include a bunch
> of non-standard operators just becuase it's convenient for some people
> to remember.

Good. I'll try to remember that.


> Please see my previous e-mail about the value of international standards
> for educators.

I read your email. I noticed that you want to educate me as an educator;-)
I partially agree with your point.

We have two words in French: "education" and "formation".

- "education" means teaching how to think right.
   so I teach "programming".
   It may be with java or pascal or c#... the syntax is not important.
   what is important is types, functions, control structures...

- "formation" means learning a specific skill.
   for this purpose, I could have "java-programming", and java details
   are really important in this course. "int and long differ, although in
   C int and long may or may not differ".

That will serve them differently.

Have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Mon, 12 Apr 2004 14:15:56 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: make == as = ?

On Mon, 12 Apr 2004, Fabien COELHO wrote:

> > Please see my previous e-mail about the value of international standards
> > for educators.
>
> I read your email. I noticed that you want to educate me as an educator;-)
> I partially agree with your point.
>
> We have two words in French: "education" and "formation".
>
> - "education" means teaching how to think right.
>    so I teach "programming".
>    It may be with java or pascal or c#... the syntax is not important.
>    what is important is types, functions, control structures...
>
> - "formation" means learning a specific skill.
>    for this purpose, I could have "java-programming", and java details
>    are really important in this course. "int and long differ, although in
>    C int and long may or may not differ".

However given that java's == and SQL = and java's && and SQL AND have
different semantics in some cases are you sure you want to teach them
something that's actually incorrect in any case.  Saying "same as java" is
not any help to your students when they run into cases where string or
date comparison with == is not the same as in java or foo.a=bar.a && <some
condition containing 1/foo.a> errors with a division by 0 when there are
no 0 values in bar.a.

Mon, 12 Apr 2004 07:16:28 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: make == as = ?
Fabien,

> I agree that listening is something difficult, for me as for every body.
> Also, listening and talking in another language is not easy.

I can understand that.   I'm going to reply at length, here, because there is 
a principle behind so many of us rejecting your suggestion, and I'd like you 
to understand it because you seem interested in being an active participant 
in the community.

> Also, I thought - wrongly - that it might be useful to others, and
> would not hurt anyone.
>
> Definitly not a shared point of view from the answers I get!

Correct.  What a lot of people disagree on is the "would not hurt anybody".

> Moreover, I noticed so many "convenient non standard" features in
> postgresql that suggesting one more did not seem so stupid to me at first
> glance.

Well, what several people have been trying to explain is that this is 
different.   We have non-standard syntax for 3 reasons:

1) It's a legacy from when we didn't know any better (example: !=)
2) The syntax supports features that are simply not available under the SQL 
spec (example, LIMIT or SELECT DISTINCT ON).
3) The syntax supports the Object-Relational extensions of PostgreSQL, and 
were developed before the publication of O-R syntax in the SQL99 spec, so our 
syntax differs.

The addition of an == operator as a synonym for = does not fall into any of 
those three groups.

Adding == would cause harm in the following three ways:

1) It would impair portability between PostgreSQL and other databases that 
support the SQL standard.
2) It would be one more operator to maintain between distributions
3) It would, in my opinion, confuse Java and C coders instead of helping them.

The last point is very important for your purposes.  Let me explain by 
allegory:
I speak a little Spanish and a little Italian, but neither fluently.  When I 
was learning Italian, one of the biggest things to get in my way was my 
knowledge of Spanish ... the grammar is almost the same, some words are the 
same, and some of the phonetics are the same ... but not all.   I can't tell 
you how many times I embarassed myself in Italian class by swapping in a 
Spaish word in an Italian sentence, or worse, speaking Italian with a Mexican 
accent!   But I had no problem whatsoever keeping my Italian and my Nepali 
seperate, because the two languages are not at all similar.

Adding an == operator to the SQL syntax would be the same.  Your Java students 
would be lulled into a false sense of understanding out of the belief that == 
in PostgreSQL would work exactly like == in Java ... when it wouldn't work 
the same in corner cases.  SQL and Java are radically different languages 
based on entirely different priniciples (which is why SQLJ is a stupid idea 
as well, IMHO).

Finally, when your students leave your classroom and need to write a program 
that interfaces with Oracle or SQL Server, then what?  They'll look at their 
notebooks from class and try the examples, and get a "syntax error." 

> - "formation" means learning a specific skill.
>    for this purpose, I could have "java-programming", and java details
>    are really important in this course. "int and long differ, although in
>    C int and long may or may not differ".
>
> That will serve them differently.

Remind me to avoid "formation" courses in France, then.   From my perspective, 
it is better to teach a student nothing at all than to teach them something 
which is wrong.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Mon, 12 Apr 2004 09:32:15 -0700
Josh Berkus <josh@agliodbs.com>
Re: make == as = ?

Dear Josh,

Thanks for you reply at length.

It helps me understand the "raw" about my suggestion.
Some short comments and joke signs:


> Adding == would cause harm in the following three ways:
> 1) It would impair portability between PostgreSQL and other databases that
> support the SQL standard.

Oracle, M$ and others spend a lot of energy so that what you write for
their tool won't be so easilly portable, thus once you've started with
them, you'll stay forever.

You're arguing that pg should help its "customers" to leave it easilly,
what is quite paradoxical! ;-)


> 3) It would, in my opinion, confuse Java and C coders

I have different classes, and not all classes would be elligible to a '=='
for '=' shorthand (well, I should write long-hand as it is longer;-).
So I partially aggree with your opinion.


> Your Java students would be lulled into a false sense of understanding
> out of the belief that == in PostgreSQL would work exactly like == in
> Java ... when it wouldn't work the same in corner cases.

For the class I have in mind, there are no corner cases, just concepts and
basic practice.  They are not going to be db developers, not even computer
professionnals for most of them. I want them to remember that there is
something beside word, powerpoint and excel;-)


> Finally, when your students leave your classroom and need to write a program
> that interfaces with Oracle or SQL Server, then what?  They'll look at their
> notebooks from class and try the examples, and get a "syntax error."

Sure. They should have used postgresql ;-)


> From my perspective, it is better to teach a student nothing at all than
> to teach them something which is wrong.

"Formation" is what you need to get a job.
"Education" is what you need as a human being.

We need both...


Have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Tue, 13 Apr 2004 15:05:42 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: make == as = ?

On Tue, 13 Apr 2004, Fabien COELHO wrote:

>
> > Your Java students would be lulled into a false sense of understanding
> > out of the belief that == in PostgreSQL would work exactly like == in
> > Java ... when it wouldn't work the same in corner cases.
>
> For the class I have in mind, there are no corner cases, just concepts and
> basic practice.  They are not going to be db developers, not even computer

So no string comparisons? I know that's a mostly unused corner case and
all, but... ;)

Tue, 13 Apr 2004 07:47:09 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: make == as = ?

Dear Stephan,

> > For the class I have in mind, there are no corner cases, just concepts and
> > basic practice.  They are not going to be db developers, not even computer
>
> So no string comparisons? I know that's a mostly unused corner case and
> all, but... ;)

They survive to the idea that text/date/...  are "basic" types in SQL.
Maybe I'm lucky... they could prefer java references with new/equals...;-)

If I take your example about details of && vs AND semantics, while
teaching "programming concepts" I'm not going to discuss the fact that &&
is shortcut by the evaluator, as this is very specific.
I'm not planing my students to know what "i=++i+i++;" could mean.

If I teach about "java/c/c++/java", this may be an issue.

So it depends on the course goal.


Well, I'm happy that so many people have ideas about what to teach and how
to teach it;-)

Have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Tue, 13 Apr 2004 17:14:41 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: make == as = ?
Fabien,

> You're arguing that pg should help its "customers" to leave it easilly,
> what is quite paradoxical! ;-)

<grin>

If we're going to "embrace and extend", we need more market share first.   ;-)

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Tue, 13 Apr 2004 09:35:32 -0700
Josh Berkus <josh@agliodbs.com>
Re: make == as = ?

>
> Dear Stephan,
>
> > > For the class I have in mind, there are no corner cases, just concepts and
> > > basic practice.  They are not going to be db developers, not even computer
> >
> > So no string comparisons? I know that's a mostly unused corner case and
> > all, but... ;)
>
> They survive to the idea that text/date/...  are "basic" types in SQL.
> Maybe I'm lucky... they could prefer java references with new/equals...;-)
>
> If I take your example about details of && vs AND semantics, while
> teaching "programming concepts" I'm not going to discuss the fact that &&
> is shortcut by the evaluator, as this is very specific.
>
> I'm not planing my students to know what "i=++i+i++;" could mean.

And I wouldn't expect that in a programming concepts course.  But, if
you're going to (for example) say that, "preincrement and postincrement
work exactly as in C," you've got to realize that there's a chance a
student will know that the i++ + ++i is undefined and expect it to be
undefined in the language you're talking about.  That's the problem with
using shorthand phrases like "exactly in <X>" without the "except ..."


Tue, 13 Apr 2004 09:47:02 -0700 (PDT)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: make == as = ?
Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> 
> 
> Dear Josh,
> 
> Thanks for you reply at length.
> 
> It helps me understand the "raw" about my suggestion.
> Some short comments and joke signs:
> 
> 
> > Adding == would cause harm in the following three ways:
> > 1) It would impair portability between PostgreSQL and other databases that
> > support the SQL standard.
> 
> Oracle, M$ and others spend a lot of energy so that what you write for
> their tool won't be so easilly portable, thus once you've started with
> them, you'll stay forever.
> 
> You're arguing that pg should help its "customers" to leave it easilly,
> what is quite paradoxical! ;-)
[remainder elided]

Perhaps the PostgreSQL community is sufficiently confident in the
quality of its product that it doesn't feel the need to resort to
odious lock-in tricks? ;)

Jim

Tue, 13 Apr 2004 16:37:37 -0400 (EDT)
jseymour@LinxNet.com (Jim Seymour)
Re: make == as = ?