Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2002
    Location
    Brooklyn, NY
    Posts
    5

    Unanswered: Too tough for 200 Sybase pros?

    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

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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?
    Thanks,

    Matt

  3. #3
    Join Date
    Mar 2002
    Location
    Brooklyn, NY
    Posts
    5

    Just SQL-readers

    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.


  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Ah I see. Well, that is due to Sybase's implementation of T-SQL.

    For instance, run:
    Code:
    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 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.:
    Code:
    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.
    Thanks,

    Matt

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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,

    Matt

  6. #6
    Join Date
    Mar 2002
    Location
    Brooklyn, NY
    Posts
    5
    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! 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!

  7. #7
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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 ).
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •