Re: [GENERAL] Oracle buys Innobase - Mailing list pgsql-advocacy
From | Jonathan Gennick |
---|---|
Subject | Re: [GENERAL] Oracle buys Innobase |
Date | |
Msg-id | 115271350861.20051021114025@oreilly.com Whole thread Raw |
In response to | Re: [GENERAL] Oracle buys Innobase (Jan Wieck <JanWieck@Yahoo.com>) |
List | pgsql-advocacy |
I hope it's ok if I jump in here. I am having a difficult time following Jan's logic: JW> The Restrictions for Entry level SQL say that JW> a) A <general literal> shall not be a <national character string JW> literal>. JW> b) A <general literal> shall not be a <datetime literal> or JW> <interval literal>. JW> There are no restrictions that say a <general literal> cannot be a JW> <numeric literal>. I just took a look at my copy of SQL:2003, and I do agree that there is no rule per se stating that "a <general literal>" cannot be a <numeric literal>". However, I also find nothing to indicate that a <general literal> *can* be a <numeric literal>. Looking at the definition for <general literal>, I see: <general literal> ::= <character string literal> | <national character string literal> | <Unicode character string literal> | <binary string literal> | <datetime literal> | <interval literal> | <boolean literal> I see seven possible manifestations of a general literal (<boolean literal>, <interval literal>, and so forth). None of these manifestations has to do with numeric values. I see numeric literals defined as follows: <signed numeric literal> ::= [ <sign> ] <unsigned numeric literal> <unsigned numeric literal> ::= <exact numeric literal> | <approximate numeric literal> And, digging down further, I see no quotes anywhere in the definitions. From all that I have seen so far in the standard, if you write a literal such as, say, '23.5', what you have is a character string. I see no ambiguity on that point. Now, Oracle (and possibly other databases) will allow you to use a character string when a number is called for. For example: SELECT some_number + '23.5' from some_table; In such cases, Oracle implicitly converts the character string, but implicit conversion is potentially a can of worms and I don't think it's really the issue you are talking about, is it? Interestingly, DB2 is rather hard-nosed about implicit conversions. I would have to test again to be certain, but I don't believe DB2 would implicitly convert '23.5' to a number in the above context. Pardon me if I've missed the boat somewhere, or misunderstood the issue, but I don't at the moment see where you can put quotes around digits and still have a numeric literal. I believe the quotes give you a character string. Best regards, Jonathan Gennick Editor, O'Reilly Media 906.387.1698 mailto:jgennick@oreilly.com Friday, October 21, 2005, 10:29:43 AM, Jan Wieck (JanWieck@Yahoo.com) wrote: JW> On 10/21/2005 1:58 AM, Jeff Davis wrote: >> SCassidy@overlandstorage.com wrote: >>> A "PostgreSQL to Oracle converter" might be a really big project. >>> >>> Having ported an application from PostgreSQL (7.3) to Oracle 9i, as I >>> recall, my biggest problems were: >>> >>> - Quoting issues: the original PostgreSQL application quoted >>> integer/numeric type, and Oracle will not allow that, so I had to make sure >>> only types that had to be quoted, were. (I normally do not quote numbers >>> in PostgreSQL, but I inherited the application). >> >> What does the standard say about that? Which types of values in Oracle >> need to not be quoted? JW> Section 5.3 of SQL92 defines: JW> <literal> ::= JW> <signed numeric literal> JW> | <general literal> JW> <unsigned literal> ::= JW> <unsigned numeric literal> JW> | <general literal> JW> <general literal> ::= JW> <character string literal> JW> | <national character string literal> JW> | <bit string literal> JW> | <hex string literal> JW> | <datetime literal> JW> | <interval literal> JW> The Restrictions for Entry level SQL say that JW> a) A <general literal> shall not be a <national character string JW> literal>. JW> b) A <general literal> shall not be a <datetime literal> or JW> <interval literal>. JW> There are no restrictions that say a <general literal> cannot be a JW> <numeric literal>. <numeric literal> is defined without quotes, but from JW> the above I conclude that a <general literal> is allowed instead of a JW> <numeric literal> to represent a numeric value, which means Oracle is JW> wrong IMNSVHO. JW> No reason to swell your breast though, we aren't perfect in the literal JW> area either. Because JW> <datetime literal> ::= JW> <date literal> JW> | <time literal> JW> | <timestamp literal> JW> <date literal> ::= JW> DATE <date string> JW> <time literal> ::= JW> TIME <time string> JW> <timestamp literal> ::= JW> TIMESTAMP <timestamp string> JW> <date string> ::= JW> <quote> <date value> <quote> JW> ... JW> We allow a <general literal> to be used as a <datetime literal> or JW> <interval literal> without the keyword DATE, TIME, TIMESTAMP or JW> INTERVAL. Isn't that violating the restrictions for Entry level SQL? JW> Further JW> 1) In a <character string literal> or <national character string JW> literal>, the sequence: JW> <quote> <character representation>... <quote> JW> <separator>... <quote> <character representation>... <quote> JW> is equivalent to the sequence JW> <quote> <character representation>... <character JW> representation>... <quote> JW> Note: The <character representation>s in the equivalent se- JW> quence are in the same sequence and relative sequence as in the JW> original <character string literal>. JW> The rule is more or less repeated for other literal types. But 8.0.4 claims JW> wieck=# select 'foo ' 'bar'; JW> ERROR: syntax error at or near "'bar'" at character 15 JW> LINE 1: select 'foo ' 'bar'; JW> ^ JW> I guess neither Oracle nor we get the cookie here. JW> Jan >> >> Regards, >> Jeff Davis >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match
pgsql-advocacy by date: