Re: [GENERAL] Oracle buys Innobase

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(at)oreilly(dot)com

Friday, October 21, 2005, 10:29:43 AM, Jan Wieck (JanWieck(at)Yahoo(dot)com) wrote:
JW> On 10/21/2005 1:58 AM, Jeff Davis wrote:
>> SCassidy(at)overlandstorage(dot)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