Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Check validity of group by clause

23 posts in Product Futures Discussion Last posting was on 2003-04-19 03:45:24.0Z
Olivier Posted on 2003-03-31 08:33:32.0Z
From: "Olivier" <ociteau-NOCANSPAM@yahoo.fr>
Organization: 212.234.59.104
X-Newsreader: AspNNTP 1.50 (C I Host)
Subject: Check validity of group by clause
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <ump13A29CHA.266@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Mon, 31 Mar 2003 00:33:32 -0800
NNTP-Posting-Host: 66.221.40.1
Lines: 11
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1090
Article PK: 95331

I post it as an enhancement request, but i consider this as a bug in ASE.
When i write :
select col1, sum(col2), col3
from table
group by col1

This is an invalid request because 'col3' doesn't belong to the 'group by'
clause.
With Oracle i get an error : great.
With ASE 12.0 : no errors, it returns me a result set.
I wish that ASE Stops me with an error.


Please, do not drop SQL Advantage again.


Rob Verschoor Posted on 2003-03-31 09:41:05.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <ump13A29CHA.266@forums-1-dub>
Subject: Re: Check validity of group by clause
Date: Mon, 31 Mar 2003 11:41:05 +0200
Lines: 45
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <uwPqVp29CHA.185@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: a66085.upc-a.chello.nl 62.163.66.85
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1091
Article PK: 95330


"Olivier" <ociteau-NOCANSPAM@yahoo.fr> wrote in message
news:ump13A29CHA.266@forums-1-dub...
> I post it as an enhancement request, but i consider this as a bug in
ASE.
> When i write :
> select col1, sum(col2), col3
> from table
> group by col1
>
> This is an invalid request because 'col3' doesn't belong to the
'group by'
> clause.
> With Oracle i get an error : great.
> With ASE 12.0 : no errors, it returns me a result set.
> I wish that ASE Stops me with an error.
>
>
> Please, do not drop SQL Advantage again.

You're looking at a vendor-specific extension to ANSI SQL here. All
DBMS vendors have their own, non-standard SQL extensions. THis
particular ASE extension is fully documented.
To indicate such non-standard statements, ASE has the command "set
fipsflagger on". This will print a warning message when a non-ANSI
construct appears -- but it won't abort it. To accomplish that, an
extension to the fipsflagger command might be an idea, e.g.:

set fipsflagger abort -- abort the statement if not ANSI-compliant


Rob V.
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------


Olivier Posted on 2003-03-31 13:03:29.0Z
From: "Olivier" <ociteau-NOCANSPAM@yahoo.fr>
Organization: 212.234.59.104
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub>
X-Newsreader: AspNNTP 1.50 (C I Host)
Subject: Re: Check validity of group by clause
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <edZ7tX49CHA.266@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Mon, 31 Mar 2003 05:03:29 -0800
NNTP-Posting-Host: 66.221.40.1
Lines: 50
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1092
Article PK: 95333

You are right Rob, thank you for quick answer.
Anyway i cannot use it because i use 2 other extensions
Select ... into #temp ...

With "fipsFlagger on" it says that #temp does'nt exist.
Strange, but not very important

On Mon, 31 Mar 2003 11:41:05 +0200,
in sybase.public.ase.product_futures_discussion

Rob Verschoor <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
>"Olivier" <ociteau-NOCANSPAM@yahoo.fr> wrote in message
>news:ump13A29CHA.266@forums-1-dub...
>> I post it as an enhancement request, but i consider this as a bug in
>ASE.
>> When i write :
>> select col1, sum(col2), col3
>> from table
>> group by col1
>>
>> This is an invalid request because 'col3' doesn't belong to the
>'group by'
>> clause.
>> With Oracle i get an error : great.
>> With ASE 12.0 : no errors, it returns me a result set.
>> I wish that ASE Stops me with an error.
>>
>>
>> Please, do not drop SQL Advantage again.
>
>
>You're looking at a vendor-specific extension to ANSI SQL here. All
>DBMS vendors have their own, non-standard SQL extensions. THis
>particular ASE extension is fully documented.
>To indicate such non-standard statements, ASE has the command "set
>fipsflagger on". This will print a warning message when a non-ANSI
>construct appears -- but it won't abort it. To accomplish that, an
>extension to the fipsflagger command might be an idea, e.g.:
>
>set fipsflagger abort -- abort the statement if not ANSI-compliant
>
>
>Rob V.
>-------------------------------------------------------------
>Rob Verschoor
>
>Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
>
>Author of "The Complete Sybase ASE Quick Reference Guide"
>Online orders accepted at http://www.sypron.nl/qr
>
>mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
>http://www.sypron.nl
>Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
>-------------------------------------------------------------
>

http://www.bretzelforbush.com/


Ilya Zvyagin Posted on 2003-04-14 10:51:44.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <edZ7tX49CHA.266@forums-1-dub>
Subject: Re: Check validity of group by clause
Date: Mon, 14 Apr 2003 14:51:44 +0400
Lines: 20
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Comment-To: Olivier
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Message-ID: <1050317504.339285@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1116
Article PK: 95355

Hello, Olivier!
You wrote on Mon, 31 Mar 2003 05:03:29 -0800:

O> You are right Rob, thank you for quick answer.
O> Anyway i cannot use it because i use 2 other extensions
O> Select ... into #temp ...

O> With "fipsFlagger on" it says that #temp does'nt exist.
O> Strange, but not very important

With "fipsFlagger on" ASE will complain about almost anything.
Imagine translating a stored procedure in this mode, SP is a
big ANSI violation itself.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Bret Halford Posted on 2003-03-31 14:30:11.0Z
Message-ID: <3E8850F3.2E95C5E3@sybase.com>
Date: Mon, 31 Mar 2003 07:30:11 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Check validity of group by clause
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: bret-pc2.sybase.com 157.133.80.186
Lines: 23
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1094
Article PK: 95334


> You're looking at a vendor-specific extension to ANSI SQL here. All
> DBMS vendors have their own, non-standard SQL extensions. THis
> particular ASE extension is fully documented.
> To indicate such non-standard statements, ASE has the command "set
> fipsflagger on". This will print a warning message when a non-ANSI
> construct appears -- but it won't abort it. To accomplish that, an
> extension to the fipsflagger command might be an idea, e.g.:
>
> set fipsflagger abort -- abort the statement if not ANSI-compliant
>
> Rob V.
> -------------------------------------------------------------
> Rob Verschoor
>

There is an existing feature request similar to this, CR 53719.
"Would like trace flag or ONE, SINGLE SET option to set the server up to
be fully ANSI compatible." I'm not aware of any current plans to
implement
it though.

-bret


Eugene Korolkov Posted on 2003-03-31 22:06:37.0Z
Message-ID: <3E88BBED.5106BA23@davidsohn.com>
Date: Mon, 31 Mar 2003 17:06:37 -0500
From: Eugene Korolkov <ekorolkov@davidsohn.com>
X-Mailer: Mozilla 4.77 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
To: Rob Verschoor <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Subject: Re: Check validity of group by clause
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 12.3.91.140
Lines: 48
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1096
Article PK: 95336

What is the the initial purpose of that so-called "extension" ?
It is only misleading and cause of many errors.
It seems to me that it has been named later on as an "extension"
(that can explain something, otherwise I am completely confused)

Regards,
Eugene

Rob Verschoor wrote:

> "Olivier" <ociteau-NOCANSPAM@yahoo.fr> wrote in message
> news:ump13A29CHA.266@forums-1-dub...
> > I post it as an enhancement request, but i consider this as a bug in
> ASE.
> > When i write :
> > select col1, sum(col2), col3
> > from table
> > group by col1
> >
> > This is an invalid request because 'col3' doesn't belong to the
> 'group by'
> > clause.
> > With Oracle i get an error : great.
> > With ASE 12.0 : no errors, it returns me a result set.
> > I wish that ASE Stops me with an error.
> >
> >
> > Please, do not drop SQL Advantage again.
>
> You're looking at a vendor-specific extension to ANSI SQL here. All
> DBMS vendors have their own, non-standard SQL extensions. THis
> particular ASE extension is fully documented.
> To indicate such non-standard statements, ASE has the command "set
> fipsflagger on". This will print a warning message when a non-ANSI
> construct appears -- but it won't abort it. To accomplish that, an
> extension to the fipsflagger command might be an idea, e.g.:
>
> set fipsflagger abort -- abort the statement if not ANSI-compliant
>
> Rob V.
> -------------------------------------------------------------
> Rob Verschoor
>
> Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
>
> Author of "The Complete Sybase ASE Quick Reference Guide"
> Online orders accepted at http://www.sypron.nl/qr
>
> mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
> http://www.sypron.nl
> Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> -------------------------------------------------------------


Rob Verschoor Posted on 2003-04-01 07:42:37.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <3E88BBED.5106BA23@davidsohn.com>
Subject: Re: Check validity of group by clause
Date: Tue, 1 Apr 2003 09:42:37 +0200
Lines: 72
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <uKpU6LC#CHA.185@forums-2-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: a66085.upc-a.chello.nl 62.163.66.85
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1098
Article PK: 95339

Well, how do these things work in practice? I suppose some important
customer really wanted to have that feature, for whatever reason. So
he gets it. That's not specific for Sybase -- it happens everywhere in
the software industry.
Whether or not it is misleading depends on your expectation. Note that
there isn't a single RDBMS vendor around who hasn't littered his
implementation of SQL with vendor-specific extensions.

HTH,

Rob V.

"Eugene Korolkov" <ekorolkov@davidsohn.com> wrote in message
news:3E88BBED.5106BA23@davidsohn.com...
> What is the the initial purpose of that so-called "extension" ?
> It is only misleading and cause of many errors.
> It seems to me that it has been named later on as an "extension"
> (that can explain something, otherwise I am completely confused)
>
> Regards,
> Eugene
>
> Rob Verschoor wrote:
>
> > "Olivier" <ociteau-NOCANSPAM@yahoo.fr> wrote in message
> > news:ump13A29CHA.266@forums-1-dub...
> > > I post it as an enhancement request, but i consider this as a
bug in
> > ASE.
> > > When i write :
> > > select col1, sum(col2), col3
> > > from table
> > > group by col1
> > >
> > > This is an invalid request because 'col3' doesn't belong to the
> > 'group by'
> > > clause.
> > > With Oracle i get an error : great.
> > > With ASE 12.0 : no errors, it returns me a result set.
> > > I wish that ASE Stops me with an error.
> > >
> > >
> > > Please, do not drop SQL Advantage again.
> >
> > You're looking at a vendor-specific extension to ANSI SQL here.
All
> > DBMS vendors have their own, non-standard SQL extensions. THis
> > particular ASE extension is fully documented.
> > To indicate such non-standard statements, ASE has the command "set
> > fipsflagger on". This will print a warning message when a non-ANSI
> > construct appears -- but it won't abort it. To accomplish that, an
> > extension to the fipsflagger command might be an idea, e.g.:
> >
> > set fipsflagger abort -- abort the statement if not
ANSI-compliant
> >
> > Rob V.
> > -------------------------------------------------------------
> > Rob Verschoor
> >
> > Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
> >
> > Author of "The Complete Sybase ASE Quick Reference Guide"
> > Online orders accepted at http://www.sypron.nl/qr
> >
> > mailto:rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
> > http://www.sypron.nl
> > Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
> > -------------------------------------------------------------
>


Ilya Zvyagin Posted on 2003-04-14 10:58:10.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <3E88BBED.5106BA23@davidsohn.com> <uKpU6LC#CHA.185@forums-2-dub>
Subject: Re: Check validity of group by clause
Date: Mon, 14 Apr 2003 14:58:10 +0400
Lines: 14
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Comment-To: Rob Verschoor
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Message-ID: <1050317890.365808@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1117
Article PK: 95357

Hello, Rob!
You wrote on Tue, 1 Apr 2003 09:42:37 +0200:

RV> Well, how do these things work in practice? I suppose some important customer really wanted
RV> to have that feature, for whatever reason.

I have been dreaming for years to get to know this reason. Does anyone know it ?

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Ilya Zvyagin Posted on 2003-04-14 10:47:07.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub>
Subject: Re: Check validity of group by clause
Date: Mon, 14 Apr 2003 14:47:07 +0400
Lines: 32
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Comment-To: Rob Verschoor
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Message-ID: <1050317227.149071@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1115
Article PK: 95356

Hello, Rob!
You wrote on Mon, 31 Mar 2003 11:41:05 +0200:

RV> You're looking at a vendor-specific extension to ANSI SQL here. All
RV> DBMS vendors have their own, non-standard SQL extensions. THis particular ASE extension is
RV> fully documented.
RV> To indicate such non-standard statements, ASE has the command "set fipsflagger on". This
RV> will print a warning message when a non-ANSI construct appears -- but it won't abort it. To
RV> accomplish that, an extension to the fipsflagger command might be an idea, e.g.:

In practice this is very important to catch the situation when a developer forgets to
add a column to GROUP BY list after adding it to SELECT output list.
But fipsflagger usually produces a lot of messages for *any* ANSI violation
and this makes using of fipsflagger for this impossible.

What I whould really like to know about this non-ANSY behavior is
1) why this feature was originally invented in ASE and what it was intended for.
I know well all what is written in documentation about this but I can't figure
out any really useful application for this.
Although I use sometimes this feature to avoid grouping on lots of additional
lookup columns joined to the main table just to visualize IDs.

2) does Sybase plan to add at least a warning when the result of grouping
is joined to one of original tables of the query ? This whould be very helpful.
Note that I don't want to know anything about if ANSI rules were broken here or not.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Glenn Paulley Posted on 2003-04-14 18:26:37.0Z
Date: Mon, 14 Apr 2003 14:26:37 -0400
From: Glenn Paulley <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: iAnywhere Solutions
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: Check validity of group by clause
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru>
In-Reply-To: <1050317227.149071@gatekeeper.fct.ru>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <e1ZLJPrADHA.334@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: vpn-dub-050.sybase.com 10.22.120.50
Lines: 133
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1120
Article PK: 95360

I believe the original intent of this feature in ASE was to permit the
retrieval of additional values without having to put them in the GROUP
BY clause (which was required in previous ISO/ANSI standards, up to and
including SQL/92). For example, one could specify

SELECT customer_id, customer_name, count(*)
FROM customer as c JOIN sales_orders as o on(c.customer_id = o.cust_id )
GROUP BY customer_id

and thus get the value of customer_name without having to add it to the
GROUP BY clause. The advantage leaving the GROUP BY clause untouched is
that the access plan for the query can remain unchanged. For example, if
there is an index on customer_id (and there should be, of course, if
customer_id is the primary key of the customer table) then the optimizer
can choose a complete index scan of customer to retrieve the rows of
customer in sorted order, rather than choosing a sequential scan
followed by a materializing sort (or hash) to compute the value of
count(*) for each group.

Doing this kind of thing only makes sense, however, if the additional
expressions in the SELECT list are *functionally determined* by the
expressions in the GROUP BY clause. In the example above, this is true;
each distinct customer_id will have a single, corresponding customer
name. The latest ANSI SQL standard, SQL99, now *permits* precisely this
type of query when such a functional dependency holds. The SQL99
standard does specify which dependencies should be recognized for
different query elements, but to my knowledge none of the commercial
RDBMS vendors fully support the automatic analysis of functional
dependencies in this way. In ASA, one can specify additional expressions
in the SELECT list that are not in the GROUP BY clause, but only if they
involve the combination of one or more GROUP BY expressions with
one or more:
- literal constants
- variables
- non-correlated subqueries (subselects)
- correlated subqueries whose correlations are to grouping columns.

ASE, however, does not restrict the usage of this construct to only
expressions that are functionally determined by those in the GROUP BY
clause. For example, in the query

SELECT customer_name, customer_id, count(*)
FROM customer as c JOIN sales_orders as o on(c.customer_id = o.cust_id )
GROUP BY customer_name

it is quite plausible (assuming the absence of a UNIQUE constraint on
customer name) that there can exist two customers with the same name. In
this case, I believe ASE will return *one* of the possible values of
customer_id for those customers with the same name. It would make sense
if ASE chose either the first or last customer_id encountered for each
"group", but one should not really rely on those semantics, since they
are implementation-dependent, and the value returned could change simply
through a re-ordering of the input rows. So applications should treat
the value returned as a random "sampling" of the possible values.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer

Ilya Zvyagin wrote:
> Hello, Rob!
> You wrote on Mon, 31 Mar 2003 11:41:05 +0200:
>
> RV> You're looking at a vendor-specific extension to ANSI SQL here. All
> RV> DBMS vendors have their own, non-standard SQL extensions. THis particular ASE extension is
> RV> fully documented.
> RV> To indicate such non-standard statements, ASE has the command "set fipsflagger on". This
> RV> will print a warning message when a non-ANSI construct appears -- but it won't abort it. To
> RV> accomplish that, an extension to the fipsflagger command might be an idea, e.g.:
>
> In practice this is very important to catch the situation when a developer forgets to
> add a column to GROUP BY list after adding it to SELECT output list.
> But fipsflagger usually produces a lot of messages for *any* ANSI violation
> and this makes using of fipsflagger for this impossible.
>
> What I whould really like to know about this non-ANSY behavior is
> 1) why this feature was originally invented in ASE and what it was intended for.
> I know well all what is written in documentation about this but I can't figure
> out any really useful application for this.
> Although I use sometimes this feature to avoid grouping on lots of additional
> lookup columns joined to the main table just to visualize IDs.
>
> 2) does Sybase plan to add at least a warning when the result of grouping
> is joined to one of original tables of the query ? This whould be very helpful.
> Note that I don't want to know anything about if ANSI rules were broken here or not.
>
> --------------------
> Ilya Zvyagin, First Container Terminal of SPb Sea Port
> E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
> ICQ UID: 29427861(MasterZIV)
>
>


Rob Verschoor Posted on 2003-04-14 22:41:02.0Z
Reply-To: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub>
Subject: Re: Check validity of group by clause
Date: Tue, 15 Apr 2003 00:41:02 +0200
Lines: 153
Organization: Sypron B.V.
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MIMEOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <#XWT8dtADHA.221@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: a66085.upc-a.chello.nl 62.163.66.85
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1122
Article PK: 95362

Thanks for this great background info!

Rob V.

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:e1ZLJPrADHA.334@forums-1-dub...
> I believe the original intent of this feature in ASE was to permit
the
> retrieval of additional values without having to put them in the
GROUP
> BY clause (which was required in previous ISO/ANSI standards, up to
and
> including SQL/92). For example, one could specify
>
> SELECT customer_id, customer_name, count(*)
> FROM customer as c JOIN sales_orders as o on(c.customer_id =
o.cust_id )
> GROUP BY customer_id
>
> and thus get the value of customer_name without having to add it to
the
> GROUP BY clause. The advantage leaving the GROUP BY clause untouched
is
> that the access plan for the query can remain unchanged. For
example, if
> there is an index on customer_id (and there should be, of course, if
> customer_id is the primary key of the customer table) then the
optimizer
> can choose a complete index scan of customer to retrieve the rows of
> customer in sorted order, rather than choosing a sequential scan
> followed by a materializing sort (or hash) to compute the value of
> count(*) for each group.
>
> Doing this kind of thing only makes sense, however, if the
additional
> expressions in the SELECT list are *functionally determined* by the
> expressions in the GROUP BY clause. In the example above, this is
true;
> each distinct customer_id will have a single, corresponding customer
> name. The latest ANSI SQL standard, SQL99, now *permits* precisely
this
> type of query when such a functional dependency holds. The SQL99
> standard does specify which dependencies should be recognized for
> different query elements, but to my knowledge none of the commercial
> RDBMS vendors fully support the automatic analysis of functional
> dependencies in this way. In ASA, one can specify additional
expressions
> in the SELECT list that are not in the GROUP BY clause, but only if
they
> involve the combination of one or more GROUP BY expressions with
> one or more:
> - literal constants
> - variables
> - non-correlated subqueries (subselects)
> - correlated subqueries whose correlations are to grouping columns.
>
> ASE, however, does not restrict the usage of this construct to only
> expressions that are functionally determined by those in the GROUP
BY
> clause. For example, in the query
>
> SELECT customer_name, customer_id, count(*)
> FROM customer as c JOIN sales_orders as o on(c.customer_id =
o.cust_id )
> GROUP BY customer_name
>
> it is quite plausible (assuming the absence of a UNIQUE constraint
on
> customer name) that there can exist two customers with the same
name. In
> this case, I believe ASE will return *one* of the possible values of
> customer_id for those customers with the same name. It would make
sense
> if ASE chose either the first or last customer_id encountered for
each
> "group", but one should not really rely on those semantics, since
they
> are implementation-dependent, and the value returned could change
simply
> through a re-ordering of the input rows. So applications should
treat
> the value returned as a random "sampling" of the possible values.
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available through the
iAnywhere
> Developer Community at www.ianywhere.com/developer
>
> Ilya Zvyagin wrote:
> > Hello, Rob!
> > You wrote on Mon, 31 Mar 2003 11:41:05 +0200:
> >
> > RV> You're looking at a vendor-specific extension to ANSI SQL
here. All
> > RV> DBMS vendors have their own, non-standard SQL extensions.
THis particular ASE extension is
> > RV> fully documented.
> > RV> To indicate such non-standard statements, ASE has the command
"set fipsflagger on". This
> > RV> will print a warning message when a non-ANSI construct
appears -- but it won't abort it. To
> > RV> accomplish that, an extension to the fipsflagger command
might be an idea, e.g.:
> >
> > In practice this is very important to catch the situation when a
developer forgets to
> > add a column to GROUP BY list after adding it to SELECT output
list.
> > But fipsflagger usually produces a lot of messages for *any* ANSI
violation
> > and this makes using of fipsflagger for this impossible.
> >
> > What I whould really like to know about this non-ANSY behavior is
> > 1) why this feature was originally invented in ASE and what it was
intended for.
> > I know well all what is written in documentation about this but I
can't figure
> > out any really useful application for this.
> > Although I use sometimes this feature to avoid grouping on lots of
additional
> > lookup columns joined to the main table just to visualize IDs.
> >
> > 2) does Sybase plan to add at least a warning when the result of
grouping
> > is joined to one of original tables of the query ? This whould be
very helpful.
> > Note that I don't want to know anything about if ANSI rules were
broken here or not.
> >
> > --------------------
> > Ilya Zvyagin, First Container Terminal of SPb Sea Port
> > E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
> > ICQ UID: 29427861(MasterZIV)
> >
> >
>


Ilya Zvyagin Posted on 2003-04-15 06:37:52.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <#XWT8dtADHA.221@forums-1-dub>
Subject: Re: Check validity of group by clause
Date: Tue, 15 Apr 2003 10:37:52 +0400
Lines: 14
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
X-Comment-To: Rob Verschoor
Message-ID: <1050388673.379971@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1129
Article PK: 95369

Hello, Rob!
You wrote on Tue, 15 Apr 2003 00:41:02 +0200:

RV> Thanks for this great background info!

I suspect this is not quite wright. I'll try to analyze Glenn's message later.
But what about Sybase plans about this feature ?

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Olivier Posted on 2003-04-16 13:48:13.0Z
From: "Olivier" <ociteau-NOCANSPAM@yahoo.fr>
Organization: 212.234.59.105
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <#XWT8dtADHA.221@forums-1-dub> <1050388673.379971@gatekeeper.fct.ru>
X-Newsreader: AspNNTP 1.50 (C I Host)
Subject: Re: Check validity of group by clause
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <uApZU7BBDHA.334@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Wed, 16 Apr 2003 06:48:13 -0700
NNTP-Posting-Host: 66.221.40.1
Lines: 22
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1136
Article PK: 95375

Ilya, i think the same as you : this feature is a pain in the hass.

But what Glenn wrote is true.
As an application developper i have been reading powerbuilder newsgroups for
years.
And i have never seen a sybase employee doing mistakes.

On Tue, 15 Apr 2003 10:37:52 +0400,
in sybase.public.ase.product_futures_discussion

Ilya Zvyagin <masterziv@mail.ru> wrote:
>Hello, Rob!
>You wrote on Tue, 15 Apr 2003 00:41:02 +0200:
>
> RV> Thanks for this great background info!
>
>I suspect this is not quite wright. I'll try to analyze Glenn's message later.
>But what about Sybase plans about this feature ?
>
>--------------------
>Ilya Zvyagin, First Container Terminal of SPb Sea Port
>E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
>ICQ UID: 29427861(MasterZIV)
>
>

--
Olivier Citeau from Paris, France
posted via pfcguide.com


Ilya Zvyagin Posted on 2003-04-17 12:46:00.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <#XWT8dtADHA.221@forums-1-dub> <1050388673.379971@gatekeeper.fct.ru> <uApZU7BBDHA.334@forums-1-dub>
Subject: Re: Check validity of group by clause
Date: Thu, 17 Apr 2003 16:46:00 +0400
Lines: 15
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Comment-To: Olivier
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Message-ID: <1050583560.517894@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1144
Article PK: 95385

Hello, Olivier!
You wrote on Wed, 16 Apr 2003 06:48:13 -0700:

O> But what Glenn wrote is true.
O> As an application developper i have been reading powerbuilder newsgroups for years.
O> And i have never seen a sybase employee doing mistakes.

What do you mean ? What is true ?

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Olivier Posted on 2003-04-17 13:12:18.0Z
From: "Olivier" <ociteau-NOCANSPAM@yahoo.fr>
Organization: 212.234.59.105
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <#XWT8dtADHA.221@forums-1-dub> <1050388673.379971@gatekeeper.fct.ru> <uApZU7BBDHA.334@forums-1-dub> <1050583560.517894@gatekeeper.fct.ru>
X-Newsreader: AspNNTP 1.50 (C I Host)
Subject: Re: Check validity of group by clause
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <ehdD6LOBDHA.221@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Thu, 17 Apr 2003 06:12:18 -0700
NNTP-Posting-Host: 66.221.40.1
Lines: 24
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1145
Article PK: 95384

Sorry that my english is bad.
I feel that what Glenn wrote is the truth.
Glenn wrote about historicals reasons why ASE do no check validity of group by
clause, and why it is documented as a feature.

But i agree with you Ilya, to say that Sybase made a big mistake.

On Thu, 17 Apr 2003 16:46:00 +0400,
in sybase.public.ase.product_futures_discussion

Ilya Zvyagin <masterziv@mail.ru> wrote:
>Hello, Olivier!
>You wrote on Wed, 16 Apr 2003 06:48:13 -0700:
>
> O> But what Glenn wrote is true.
> O> As an application developper i have been reading powerbuilder newsgroups
for years.
> O> And i have never seen a sybase employee doing mistakes.
>
>What do you mean ? What is true ?
>
>--------------------
>Ilya Zvyagin, First Container Terminal of SPb Sea Port
>E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
>ICQ UID: 29427861(MasterZIV)

--
Olivier Citeau from Paris, France
posted via pfcguide.com


Ilya Zvyagin Posted on 2003-04-17 15:32:33.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <#XWT8dtADHA.221@forums-1-dub> <1050388673.379971@gatekeeper.fct.ru> <uApZU7BBDHA.334@forums-1-dub> <1050583560.517894@gatekeeper.fct.ru> <ehdD6LOBDHA.221@forums-1-dub>
Subject: Re: Check validity of group by clause
Date: Thu, 17 Apr 2003 19:32:33 +0400
Lines: 25
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Comment-To: Olivier
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Message-ID: <1050593553.654516@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1149
Article PK: 95389

Hello, Olivier!
You wrote on Thu, 17 Apr 2003 06:12:18 -0700:

O> I feel that what Glenn wrote is the truth.

I do believe so.

O> Glenn wrote about historicals reasons why ASE do no check validity of group by clause, and
O> why it is documented as a feature.
This reasons, I did not see them.

O> But i agree with you Ilya, to say that Sybase made a big mistake.
No, I think this is not a mistake. This was a decision. And any
decision has its pros and cons. I know cons of this decision.
What are pros ?
Please, can someone say simply "Sybase did this to ..."
or name any usefull appliance of this feature ?
This is not sarcasm, this is curiosity. Why did they do this ?

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Jim Egan Posted on 2003-04-17 03:43:43.0Z
From: Jim Egan <eganjp@compuserve.com>
Subject: Re: Check validity of group by clause
Date: Wed, 16 Apr 2003 21:43:43 -0600
Message-ID: <9g8s9v8anr8rgks3fi1shtfnica087j2gh@4ax.com>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <#XWT8dtADHA.221@forums-1-dub> <1050388673.379971@gatekeeper.fct.ru>
X-Newsreader: Forte Free Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 12-252-236-190.client.attbi.com 12.252.236.190
Lines: 14
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1142
Article PK: 95383


On Tue, 15 Apr 2003 10:37:52 +0400, "Ilya Zvyagin" <masterziv@mail.ru> wrote:

>I suspect this is not quite wright. I'll try to analyze Glenn's message later.
>But what about Sybase plans about this feature ?

Are you questioning Glenn's information? I wouldn't. When it comes to knowing
how the internals of query processing and optimiztion work in Sybase databases
work (especially ASA) Glenn is the man. Glenn doesn't throw around his Phd
title like some do, but he is exceptionally knowledgable.
--
Jim Egan [TeamSybase]


Ilya Zvyagin Posted on 2003-04-17 15:13:23.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <#XWT8dtADHA.221@forums-1-dub> <1050388673.379971@gatekeeper.fct.ru> <9g8s9v8anr8rgks3fi1shtfnica087j2gh@4ax.com>
Subject: Re: Check validity of group by clause
Date: Thu, 17 Apr 2003 19:13:23 +0400
Lines: 32
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Comment-To: Jim Egan
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Message-ID: <1050592403.925498@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1148
Article PK: 95387

Hello, Jim!

You wrote on Wed, 16 Apr 2003 21:43:43 -0600:

>>I suspect this is not quite wright. I'll try to analyze Glenn's message later.
>>But what about Sybase plans about this feature ?

JE> Are you questioning Glenn's information? I wouldn't. When it comes to knowing how the
JE> internals of query processing and optimiztion work in Sybase databases work (especially ASA)
JE> Glenn is the man. Glenn doesn't throw around his Phd title like some do, but he is
JE> exceptionally knowledgable.

I whould not say that Glenn does not know something. But what he wrote is not how
ASE (not ASA!!) works.

Yes, the problem is that we have sometimes to group not only by short identifiers but also by long
names and titles (which sometimes do not fit in GROUP BY key size) only to make the data readable.
Or else we have to populate a temporary table with "group by" results and then join.
Glenn exellenty described this.
By the way, what whould solve this problem without any functional dependencies
and what is implemented in many other DBMS (f.e. MS SQLServer) is ability to write queries in
FROM clause. (GROUP BY first, then JOIN it).

But I think ASE's extention we discuss was not invented to solve this problem.

I tried to explain this in my previous letter.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Glenn Paulley Posted on 2003-04-17 15:12:36.0Z
Date: Thu, 17 Apr 2003 11:12:36 -0400
From: Glenn Paulley <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: iAnywhere Solutions
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
Subject: Re: Check validity of group by clause
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <#XWT8dtADHA.221@forums-1-dub> <1050388673.379971@gatekeeper.fct.ru> <9g8s9v8anr8rgks3fi1shtfnica087j2gh@4ax.com>
In-Reply-To: <9g8s9v8anr8rgks3fi1shtfnica087j2gh@4ax.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <uO$hrQPBDHA.303@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: paulley-xp.sybase.com 172.31.141.96
Lines: 83
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1147
Article PK: 95388


Jim Egan wrote:
> On Tue, 15 Apr 2003 10:37:52 +0400, "Ilya Zvyagin" <masterziv@mail.ru> wrote:
>
>
>>I suspect this is not quite wright. I'll try to analyze Glenn's message later.
>>But what about Sybase plans about this feature ?
>
>
> Are you questioning Glenn's information? I wouldn't. When it comes to knowing
> how the internals of query processing and optimiztion work in Sybase databases
> work (especially ASA) Glenn is the man. Glenn doesn't throw around his Phd
> title like some do, but he is exceptionally knowledgable.
> --
> Jim Egan [TeamSybase]

However, Glenn makes mistakes, just like anybody else, and I made one here.

I was correct in saying that the *intent* of the feature was to permit
the display of additional values (from the SELECT), but without
modifying the GROUP BY clause the result of the aggregate functions
would not be altered. In the case where the additional expression(s) are
functionally determined by the grouping expressions, this seems to work
OK and meets the SQL 99 standard. However, what you see from a test
depends on what data you use, alas.

ASE's non-standard behaviour is, I think, reasonably documented in
Chapter 3 of the Transact-SQL user's guide, although I had to read it
twice. Where I was incorrect is that instead of outputting a "sample"
value for each group when the functional dependency doesn't hold, ASE
*duplicates* each grouped result row for every expression in the SELECT
that is neither a grouping expression nor an aggregate function.

So the query

SELECT customer_id, customer_name, count(*)
FROM customer as c JOIN sales_orders as o on(c.customer_id = o.cust_id )
GROUP BY customer_name

can return something like

Paulley, 00001, 15
Paulley, 23432, 15
Paulley, 34543, 15

if there were 3 "Paulley"s in the customer table. This is equivalent to
the ANSI query of

SELECT c_name, c.id, c_orders
FROM ( SELECT customer_name, count(*)
FROM customer as c JOIN sales_orders as o on(c.customer_id =
o.cust_id )
GROUP BY customer_name ) as orders( c_name, c_orders)
JOIN customer as c on(c.customer_name = orders.c_name or
(c.customer_name IS NULL and order.c_name IS NULL) )

The additional IS NULL predicates in the join condition are necessary to
handle the case of a customer name being NULL.

Now that I understand what's going on, I agree with Ilya that this can
be a problem, not just because it's non-ANSI but because any
non-grouping expression in the SELECT list means that the GROUP BY is no
longer a reducing operator: with any non-grouping expression in the
SELECT list, the size of the output will be equivalent to the size of
the input.

My apologies for leading everyone astray.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer


Jim Egan Posted on 2003-04-19 03:45:24.0Z
From: Jim Egan <eganjp@compuserve.com>
Subject: Re: Check validity of group by clause
Date: Fri, 18 Apr 2003 21:45:24 -0600
Message-ID: <vgh1avkkljmps0hiobc2b6q5rte5cqq187@4ax.com>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <#XWT8dtADHA.221@forums-1-dub> <1050388673.379971@gatekeeper.fct.ru> <9g8s9v8anr8rgks3fi1shtfnica087j2gh@4ax.com> <uO$hrQPBDHA.303@forums-1-dub>
X-Newsreader: Forte Free Agent 1.93/32.576 English (American)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 12-252-236-190.client.attbi.com 12.252.236.190
Lines: 6
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1158
Article PK: 95400


On Thu, 17 Apr 2003 11:12:36 -0400, Glenn Paulley <paulley@ianywhere.com> wrote:

>However, Glenn makes mistakes, just like anybody else, and I made one here.

I'm speechless!
--
Jim Egan [TeamSybase]


Olivier Posted on 2003-04-16 14:19:58.0Z
From: "Olivier" <ociteau-NOCANSPAM@yahoo.fr>
Organization: 212.234.59.105
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub>
X-Newsreader: AspNNTP 1.50 (C I Host)
Subject: Re: Check validity of group by clause
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <eHOZDNCBDHA.296@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Wed, 16 Apr 2003 07:19:58 -0700
NNTP-Posting-Host: 66.221.40.1
Lines: 162
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1137
Article PK: 95377

Hi Glenn,
thank you for this long explanation.

There is so many disadvantages compared to advantages that i would rather call
it a bug than a feature. Even if it is documented.

I am an application developper, and it is very easy to forget to add a column to
"group by" when adding a column to "Select".

You gave us an example with inner join.
I made the mistake on an outer join query.
For example
SELECT a.x, sum(a.y), b.z
FROM a left outer join b on (a.z=b.z)
GROUP by a.x

If table a has 500 rows and b 20 rows, this query returns 10000 rows !
With GROUP by a.x, b.z we get 500 rows as expected.

And we cannot use "set fipsflagger on" because we need to use "select into" and
temporary tables (tables whose name start with a sharp).

So we really need to disable this, as Ilya wrote.

ASE should take example of ASA on this.

On Mon, 14 Apr 2003 14:26:37 -0400,
in sybase.public.ase.product_futures_discussion

Glenn Paulley <paulley@ianywhere.com> wrote:
>I believe the original intent of this feature in ASE was to permit the
>retrieval of additional values without having to put them in the GROUP
>BY clause (which was required in previous ISO/ANSI standards, up to and
>including SQL/92). For example, one could specify
>
>SELECT customer_id, customer_name, count(*)
>FROM customer as c JOIN sales_orders as o on(c.customer_id = o.cust_id )
>GROUP BY customer_id
>
>and thus get the value of customer_name without having to add it to the
>GROUP BY clause. The advantage leaving the GROUP BY clause untouched is
>that the access plan for the query can remain unchanged. For example, if
>there is an index on customer_id (and there should be, of course, if
>customer_id is the primary key of the customer table) then the optimizer
>can choose a complete index scan of customer to retrieve the rows of
>customer in sorted order, rather than choosing a sequential scan
>followed by a materializing sort (or hash) to compute the value of
>count(*) for each group.
>
>Doing this kind of thing only makes sense, however, if the additional
>expressions in the SELECT list are *functionally determined* by the
>expressions in the GROUP BY clause. In the example above, this is true;
>each distinct customer_id will have a single, corresponding customer
>name. The latest ANSI SQL standard, SQL99, now *permits* precisely this
>type of query when such a functional dependency holds. The SQL99
>standard does specify which dependencies should be recognized for
>different query elements, but to my knowledge none of the commercial
>RDBMS vendors fully support the automatic analysis of functional
>dependencies in this way. In ASA, one can specify additional expressions
>in the SELECT list that are not in the GROUP BY clause, but only if they
>involve the combination of one or more GROUP BY expressions with
>one or more:
>- literal constants
>- variables
>- non-correlated subqueries (subselects)
>- correlated subqueries whose correlations are to grouping columns.
>
>ASE, however, does not restrict the usage of this construct to only
>expressions that are functionally determined by those in the GROUP BY
>clause. For example, in the query
>
>SELECT customer_name, customer_id, count(*)
>FROM customer as c JOIN sales_orders as o on(c.customer_id = o.cust_id )
>GROUP BY customer_name
>
>it is quite plausible (assuming the absence of a UNIQUE constraint on
>customer name) that there can exist two customers with the same name. In
>this case, I believe ASE will return *one* of the possible values of
>customer_id for those customers with the same name. It would make sense
>if ASE chose either the first or last customer_id encountered for each
>"group", but one should not really rely on those semantics, since they
>are implementation-dependent, and the value returned could change simply
>through a re-ordering of the input rows. So applications should treat
>the value returned as a random "sampling" of the possible values.
>
>--
>Glenn Paulley
>Research and Development Manager, Query Processing
>iAnywhere Solutions Engineering
>
>EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
>To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
>SQL Anywhere Studio Supported Platforms and Support Status
>http://my.sybase.com/detail?id=1002288
>
>Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
>Developer Community at www.ianywhere.com/developer
>
>Ilya Zvyagin wrote:
>> Hello, Rob!
>> You wrote on Mon, 31 Mar 2003 11:41:05 +0200:
>>
>> RV> You're looking at a vendor-specific extension to ANSI SQL here. All
>> RV> DBMS vendors have their own, non-standard SQL extensions. THis
particular ASE extension is
>> RV> fully documented.
>> RV> To indicate such non-standard statements, ASE has the command "set
fipsflagger on". This
>> RV> will print a warning message when a non-ANSI construct appears -- but it
won't abort it. To
>> RV> accomplish that, an extension to the fipsflagger command might be an
idea, e.g.:
>>
>> In practice this is very important to catch the situation when a developer
forgets to
>> add a column to GROUP BY list after adding it to SELECT output list.
>> But fipsflagger usually produces a lot of messages for *any* ANSI violation
>> and this makes using of fipsflagger for this impossible.
>>
>> What I whould really like to know about this non-ANSY behavior is
>> 1) why this feature was originally invented in ASE and what it was intended
for.
>> I know well all what is written in documentation about this but I can't
figure
>> out any really useful application for this.
>> Although I use sometimes this feature to avoid grouping on lots of additional
>> lookup columns joined to the main table just to visualize IDs.
>>
>> 2) does Sybase plan to add at least a warning when the result of grouping
>> is joined to one of original tables of the query ? This whould be very
helpful.
>> Note that I don't want to know anything about if ANSI rules were broken here
or not.
>>
>> --------------------
>> Ilya Zvyagin, First Container Terminal of SPb Sea Port
>> E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
>> ICQ UID: 29427861(MasterZIV)

--
Olivier Citeau from Paris, France
posted via pfcguide.com


Ilya Zvyagin Posted on 2003-04-17 14:28:22.0Z
Reply-To: "Ilya Zvyagin" <masterziv@mail.ru>
From: "Ilya Zvyagin" <masterziv@mail.ru>
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <eHOZDNCBDHA.296@forums-1-dub>
Subject: Re: Check validity of group by clause
Date: Thu, 17 Apr 2003 18:28:22 +0400
Lines: 41
Organization: FCT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4807.1700
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
X-Comment-To: Olivier
FL-Build: Fidolook Express 2001 UIExt. BuildID: 3BC00FAD (7/10/2001 12:17:49).
Message-ID: <1050589702.981379@gatekeeper.fct.ru>
Cache-Post-Path: gatekeeper.fct.ru!unknown@dream.int.fct.ru
X-Cache: nntpcache 2.4.0b2 (see http://www.nntpcache.org/)
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: gatekeeper.fct.ru 212.113.103.2
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1146
Article PK: 95386

Hello, Olivier!
You wrote on Wed, 16 Apr 2003 07:19:58 -0700:

O> There is so many disadvantages compared to advantages that i would rather call it a bug than
O> a feature. Even if it is documented.

It is badly documented. Poorly documented. There is a lot of lines in docs about this
but I, having read this many times, still can't explain to ASE beginners two simple things
1) how this works

2) what it for
I'm just always saying "Oh, please, be carefull and DONT FORGET TO UPDATE "GROUP BY"
or our server will die".

O> So we really need to disable this, as Ilya wrote.

I vote for this with all my hands and all my legs !
But ... we will anyway need this to support existing applications and queries.
I think, if Sybase comes to conclusion to drop this feature, it's better
to
1) add a SET command to *explicitly* disable this feature. By default it should be enabled.
2) add a warning message in the mode when this is enabled and ONLY when an additional
table is attached to the query. This can waaarn the developer that he or she
forget to add a column to GROUP BY.
It's important that this message would appear also in SET NOEXEC ON mode.

O> I am an application developper, and it is very easy to forget to add a column to "group by"
O> when adding a column to "Select".

Yes, f.e. we have about 15 developers and many big queries of 2 - 4 screans of text.
People sometimes even don't figure out that GROUP BY is in the query.
Tables are not very small and when this happens, the server stops because of
tempdb overflow. This happens at least once a year, sometimes even more often
despite all our attempts to educate developers and remind them of this danger.

--------------------
Ilya Zvyagin, First Container Terminal of SPb Sea Port
E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
ICQ UID: 29427861(MasterZIV)


Olivier Posted on 2003-04-17 15:55:13.0Z
From: "Olivier" <ociteau-NOCANSPAM@yahoo.fr>
Organization: 212.234.59.105
References: <ump13A29CHA.266@forums-1-dub> <uwPqVp29CHA.185@forums-2-dub> <1050317227.149071@gatekeeper.fct.ru> <e1ZLJPrADHA.334@forums-1-dub> <eHOZDNCBDHA.296@forums-1-dub> <1050589702.981379@gatekeeper.fct.ru>
X-Newsreader: AspNNTP 1.50 (C I Host)
Subject: Re: Check validity of group by clause
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <#HiW8mPBDHA.221@forums-1-dub>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Thu, 17 Apr 2003 08:55:13 -0700
NNTP-Posting-Host: 66.221.40.1
Lines: 56
Path: forums-1-dub!forums-master.sybase.com!forums-1-dub.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:1150
Article PK: 95392

How to make actions now ?
Through ISUG ?
Or through caseXpress ?

On Thu, 17 Apr 2003 18:28:22 +0400,
in sybase.public.ase.product_futures_discussion

Ilya Zvyagin <masterziv@mail.ru> wrote:
>Hello, Olivier!
>You wrote on Wed, 16 Apr 2003 07:19:58 -0700:
>
> O> There is so many disadvantages compared to advantages that i would rather
call it a bug than
> O> a feature. Even if it is documented.
>
>It is badly documented. Poorly documented. There is a lot of lines in docs
about this
>but I, having read this many times, still can't explain to ASE beginners two
simple things
>1) how this works
>
>2) what it for
>I'm just always saying "Oh, please, be carefull and DONT FORGET TO UPDATE
"GROUP BY"
>or our server will die".
>
> O> So we really need to disable this, as Ilya wrote.
>
>I vote for this with all my hands and all my legs !
>But ... we will anyway need this to support existing applications and queries.
>I think, if Sybase comes to conclusion to drop this feature, it's better
>to
>1) add a SET command to *explicitly* disable this feature. By default it should
be enabled.
>2) add a warning message in the mode when this is enabled and ONLY when an
additional
>table is attached to the query. This can waaarn the developer that he or she
>forget to add a column to GROUP BY.
>It's important that this message would appear also in SET NOEXEC ON mode.
>
> O> I am an application developper, and it is very easy to forget to add a
column to "group by"
> O> when adding a column to "Select".
>
>Yes, f.e. we have about 15 developers and many big queries of 2 - 4 screans of
text.
>People sometimes even don't figure out that GROUP BY is in the query.
>Tables are not very small and when this happens, the server stops because of
>tempdb overflow. This happens at least once a year, sometimes even more often
>despite all our attempts to educate developers and remind them of this danger.
>
>--------------------
>Ilya Zvyagin, First Container Terminal of SPb Sea Port
>E-mail: masterziv@*KILLSPAM*mail.ru - include HP in subject
>ICQ UID: 29427861(MasterZIV)
>
>

--
Olivier Citeau from Paris, France
posted via pfcguide.com