PDA

View Full Version : Too tough for 200 Sybase pros?

jsadove
03-11-02, 12:51
I posted an earlier thread on the Sybase Spurious Group By.
It looks like either a) no one really knows what to do with this; b) everyone's keeping quiet.
From setting up tests, my conclusion is that the query selects every row in the table and returns the max(it_prod_db.dbo.APP_RISK_YW.RISKLVL_ID) for the unique rows as per the group by clause.

Maybe there is someone out there who really knows or can confirm my guess?

(This is an embarassment for Sybase; and maybe they have corrected this in 12.0?)


SELECT
it_prod_db.dbo.APP_RISK_YW.S_YEAR,
it_prod_db.dbo.APP_RISK_YW.S_MONTH,
0,
it_prod_db.dbo.APP_RISK_YW.APPSYS_NAME,
it_prod_db.dbo.APP_RISK_YW.APSS_NAME,
it_prod_db.dbo.APP_RISK_YW.CLS_NAME,
it_prod_db.dbo.APP_RISK_YW.OBJ_NAME,
it_prod_db.dbo.APP_RISK_YW.MO_NAME,
it_prod_db.dbo.APP_RISK_YW.RISKLVL_ID,
it_prod_db.dbo.APP_RISK_YW.RISK_WEIGHT,
it_prod_db.dbo.APP_RISK_YW.CAT_NAME,
it_prod_db.dbo.APP_RISK_YW.THRESHOP_NAME,
it_prod_db.dbo.APP_RISK_YW.THRESHOLD_VAL,
it_prod_db.dbo.APP_RISK_YW.UNIT_NAME,
it_prod_db.dbo.APP_RISK_YW.USED_PCT,
max(it_prod_db.dbo.APP_RISK_YW.RISKLVL_ID),
it_prod_db.dbo.APP_RISK_YW.S_WEEK
FROM it_prod_db.dbo.APP_RISK_YW
GROUP BY
it_prod_db.dbo.APP_RISK_YW.S_YEAR,
it_prod_db.dbo.APP_RISK_YW.S_MONTH,
it_prod_db.dbo.APP_RISK_YW.S_WEEK,
it_prod_db.dbo.APP_RISK_YW.APPSYS_NAME,
it_prod_db.dbo.APP_RISK_YW.APSS_NAME,
it_prod_db.dbo.APP_RISK_YW.CLS_NAME,
it_prod_db.dbo.APP_RISK_YW.OBJ_NAME
ORDER BY
it_prod_db.dbo.APP_RISK_YW.S_YEAR ASC,
it_prod_db.dbo.APP_RISK_YW.S_MONTH ASC,
it_prod_db.dbo.APP_RISK_YW.S_WEEK ASC,
it_prod_db.dbo.APP_RISK_YW.APPSYS_NAME ASC,
it_prod_db.dbo.APP_RISK_YW.APSS_NAME ASC,
it_prod_db.dbo.APP_RISK_YW.CLS_NAME ASC,
it_prod_db.dbo.APP_RISK_YW.OBJ_NAME ASC,
it_prod_db.dbo.APP_RISK_YW.MO_NAME ASC
MattR
03-18-02, 20:14
What are you trying to do? What is the specific error message or weird behavior that you are seeing? We're not mind readers ;) If you give me the SQL to create the table and populate it with data I'll give you the results on ASE 12.5; what version are you using?
jsadove
03-19-02, 23:07
Originally posted by MattR
What are you trying to do? What is the specific error message or weird behavior that you are seeing? We're not mind readers ;) If you give me the SQL to create the table and populate it with data I'll give you the results on ASE 12.5; what version are you using?

Matt,
I'm looking for message and sql-readers, not mind readers :)
Below is the message I referred to. I hope it is pretty self-explanatory, but I'll rephrase it just in case.

In all other dialects of SQL i'm familiar with, you cannot have an unequal number of columns in the select clause and in the group by clause; with the sole exception that you are using a function in the select clause such as sum, min, max, count, etc. And this is, vis a vis first order predicate calculus, completely logical, and hence why in other SQL dialects such a statement is forbidden and returns an error.

So this brings us to Sybase. When you have more columns in the select than in the group by, what values do the "extra" columns have and what is the effect on a function such as sum? Does the query aggregate for the function over all the select columns? Well, no I don't think so. Are the values appearing in the extra select columns random? Quite possibly, and perhaps this is what is meant when i read that the query produces "spurious" results.

It would be great if someone knew Sybase well enough to describe its behavior with confidence. Thanks if you can help.

BEGIN COPIED MESSAGE---
I am converting a Sybase 11 application to an Oracle8 application. There are a number of queries similar to the one below. The columns of in the Group By clause do not match the columns in the Select clause. In Oracle, this is illegal. In fact, in Sybase I have read that it is allowed but produces "spurious" results. Can someone please tell me:
a) whether those "spurious results are at least consistent for a given query
b) what the "logical" result should be whenever a query similar to the one below is run; i.e., what values are simply repeated(?) and which are actually rolled up on. Please also explain (or point to someplace that explains) what the effect of nulls would be.

Thanks much in advance.

;)
MattR
03-19-02, 23:22
Ah I see. Well, that is due to Sybase's implementation of T-SQL.

For instance, run:

1> SET FIPSFLAGGER ON
2> GO
1> SELECT ...
xx> GO
FIPS WARNING: GROUP BY clause specified when all the items in select
list are aggregate functions or none of the items in select list are
aggregate functions.


The 'fipsflagger' will output a message when you deviate from SQL standard code.

Like all things, it is best if you go straight to the horse's mouth, so to speak. The Documentation (http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/8511;pt=8511#X) has all you'd ever need to know regarding the extra columns (a T-SQL extention).

What it appears (and it would be wise to check your data) that it will group by the columns you specify, and if you specify extra columns it will contain 'extra' data but only unique rows (again check with your data).

e.x.:

select type, title_id, avg(price), avg(advance)
from titles
group by type
type title_id
------------ -------- ----- -------
business BU1032 13.73 6,281.25
business BU1111 13.73 6,281.25
business BU2075 13.73 6,281.25
business BU7832 13.73 6,281.25
mod_cook MC2222 11.49 7,500.00
mod_cook MC3021 11.49 7,500.00
UNDECIDED MC3026 NULL NULL
popular_comp PC1035 21.48 7,500.00
popular_comp PC8888 21.48 7,500.00
popular_comp PC9999 21.48 7,500.00
psychology PS1372 13.50 4,255.00
psychology PS2091 13.50 4,255.00
psychology PS2106 13.50 4,255.00
psychology PS3333 13.50 4,255.00
psychology PS7777 13.50 4,255.00
trad_cook TC3218 15.96 6,333.33
trad_cook TC4203 15.96 6,333.33
trad_cook TC7777 15.96 6,333.33

So it basically groups by type, but keeps distinct 'extra' columns (rows). As we can see, the AVG is calculated over the group but since we have our 'extra' columns it simply repeats the avg values. I suspect it would be the same with SUM.
This is a cooked example from the documentation, so I am not sure if duplicates are actually removed. Testing would be trivially easy though. :)
MattR
03-19-02, 23:33
And no, I don't think it's an embarassment. It's a T-SQL extention (have you tried it in MS SQL Server?) and not expected to be cross-platform. Oracle has it's own oddities which obviously wouldn't work in any other RDBMS (RID, tree processing with CONNECT BY, etc.).
The developers should be embarrased ;)
jsadove
03-20-02, 09:36
Originally posted by MattR
And no, I don't think it's an embarassment. It's a T-SQL extention (have you tried it in MS SQL Server?) and not expected to be cross-platform. Oracle has it's own oddities which obviously wouldn't work in any other RDBMS (RID, tree processing with CONNECT BY, etc.).
The developers should be embarrased ;)

Thanks very much Matt. What a blinding insight! Look in the documentation! :o I googled "sybase group by -user" and paged and searched and gave up.
Well, it's still a semi-embarassment. And, of course, all SQL dialects have their oddities, particularly when it comes to handling NULLS. However, Oracle doesn't deviate, by default, from standard SQL constructs such as group by. DB2 doesn't either. And, oddly, neither does MS-Sqlserver. Interestingly, MySQL accepts the non-standard "select..group by". I'll have to check later PostgreSQL.
Thanks again!
MattR
03-21-02, 23:57
I'm sure there's a trace flag somewhere to turn off this behavior, but I'd say always develop with FIPSFLAGGER on so that you can double-check your SQL is portable to stricter SQL engines. :)

MySQL is notorious at allowing 'bad' SQL, but I'd think Sybase had a very good reason why they needed this deviation (think: big $$ client ;)).