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@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