Re: Informix and ANSI "SQL2" compliance

Jeff Webster (71421.2631@compuserve.com) wrote: : As a recent user of Informix Online 7.1, I'm finding several instances where : Informix's implementation of SQL is at variance with my understanding of the : ANSI SQL2 standard. For reference, I'm using the 3rd edition of "A Guide to THE : SQL STANDARD" by Date & Darwen. An example of a variance that's causing me : grief is the count aggregate. According to the Informix SQL version 7.1 manual, : the UNIQUE or DISTINCT keywords are required by the count aggregate, unless one : is using the count(*) version. But according to Date's book, the DISTINCT : keyword (UNIQUE is Informix specific I guess) is optional, along with ALL. [...] : Anyway, I'm just wondering if any Informix gurus know if or when Informix will : be in full compliance with the SQL2 standard. [...] I have no knowledge of Informix or its plans, but FWIW I use lots of different database products and happen to have a copy of ANSI X3.135-1992 (a.k.a ISO/IEC 9075-1992, "SQL92" and "SQL2"). As you probably know, the SQL92 standard is so huge that it is defined as having three levels of conformance: Entry level, Intermediate level, and Full level. "Entry level" is the lowest level, and was designed to be pretty close to SQL89 (the previous standard). Most database products that claim conformance to SQL92 are referring to Entry level -- that's still the level that's required for conformance to U.S. FIPS 127-2, I think (I'm not American, so don't know if they've upped the bar recently) and is the level that is tested in the NIST SQL conformance tests. Anyway, the SQL standard (delightful reading :-) in section 6.5 <set function specification> says: "<set value function> ::= COUNT <left paren> <asterisk> <right paren> | <general set function> <general set function> ::= <set function type> <left paren> [ <set quantifier> ] blah-blah-blah... <set function type> ::= AVG|...|COUNT <set quantifier> ::= DISTINCT | ALL" In other words :-), it says that the DISTINCT and ALL quantifiers are optional. *BUT*, the Leveling Rules for that function include: "2) The following restrictions apply for Entry SQL [...]: a) If a <general set function> specifies or implies ALL, then COUNT shall not be specified. ..." So, while I agree with you that very few vendors actually enforce this Leveling rule (standards?), Informix does appear to be complying with the rules of SQL92 Entry level. Good luck, db -- Diane Brown brownd@cognos.COM Cognos Incorporated Ottawa, CANADA Phone: (613) 738-1338 x3481 FAX: (613) 738-0002