You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@calcite.apache.org by Stephen Carlin <sc...@cloudera.com.INVALID> on 2024/12/09 18:40:44 UTC

literal "type" issues in my database

Hi,

I'm having some literal type issues when trying to use Calcite with my
database.  I have quite a few of them, and I thought perhaps it was my
database not dealing with the SQL standard correctly, but the latest one I
hit seems, well, inconsistent within Calcite.

When I run the literal 123.45 through Calcite, it is producing a decimal
type for me, which is fine.  However, if I pass in the literal 1e32 through
Calcite, it gives me a double.

I noticed a comment somewhere which states Calcite cannot handle integers
over the bigint max.  The comment was from 2006, so it doesn't look like it
will be addressed anytime soon?

Has anyone dealt with this? If so, how did you handle it?

I'm not sure this can be handled well.  I did try to change the parser to
create my own NumericLiteral.  However, when I used a negative integer out
of range, it used a static SqlLiteral create function embedded in the
Calcite jar file and had similar issues.

I do have a hack around this in my code that gets around this
post-validation time, so I won't be addressing this in Calcite in the near
future, but has anyone thought about this in general?

I should also mention (since I alluded to it at the beginning of my
message) that my database treats char literals as a "varchar(maxint)" type
(Calcite treats it as char) and tinyints like "2" as a tinyint (calcite
treats this as int).

Thanks,

Steve

Re: literal "type" issues in my database

Posted by stanilovsky evgeny <es...@gridgain.com>.

Just use a cast here.

> If I do a "create table as select 2" in my database, it creates a TINYINT
> column.  I hope that answers your question.   So I need the RelDataType  
> to
> be of type TINYINT
>
> I do have a mechanism to deal with this in my current code a little bit
> similar to what you're suggesting (I won't go into it, but it works, it's
> 'shuttle-ish' and at RelNode time).  But a shuttle seems like a hack, no?
>
> Anyway, I might develop this in the future to get rid of my hack, but  
> just
> wanted to bring it up.
>
> The DECIMAL issue was really my main problem, but I see you specifically
> dealt with this in 1.38 with this jira:
> https://issues.apache.org/jira/browse/CALCITE-3522.   Heh, I didn't think
> to look in the latest version since the original comment was dated from
> 2006 and I was one version off from the most recent version.  So thanks  
> for
> that fix!
>
> Thanks for your help!
>
> On Mon, Dec 9, 2024 at 11:45 AM Mihai Budiu <mb...@gmail.com> wrote:
>
>> Why is 2 a TINYINT and not an INTEGER?
>> What is the type of 128?
>>
>> If the only problem is the literals, you can probably fix it by writing  
>> a
>> SqlShuttle which inserts appropriate casts for literals, converting the
>> SqlLiteral with value 2 to a CAST(2 AS TINYINT).
>>
>> Mihai
>>
>> ________________________________
>> From: Stephen Carlin <sc...@cloudera.com.INVALID>
>> Sent: Monday, December 9, 2024 11:36 AM
>> To: dev@calcite.apache.org <de...@calcite.apache.org>
>> Subject: Re: literal "type" issues in my database
>>
>> Sorry, lemme try to explain further...
>>
>> I am a Java developer working on a database that is trying to use the
>> Calcite library. There is no other system I'm interacting with.  I am  
>> using
>> Calcite to parse, validate, and optimize my sql query.
>>
>> We have branched off our own version of Parser.jj and running with the
>> Calcitte library to generate the whole SqlNode tree.  After the SqlNode
>> tree gets generated, we use the RelNodeConverter and are generating
>> RelNodes, and doing all sorts of optimizations on top of that.
>>
>> I've debugged this several months ago, but the RelDataType that is  
>> created
>> when the number 2 is seen in the parser (and goes through the parser and
>> validator and RelNodeConverter) is coming out with a type of
>> SqlTypeName.INTEGER.  This doesn't match what my system expects, since  
>> we
>> prefer a SqlTypeName.TINYINT
>>
>> I can change Parser.jj and potentially create my one SqlNumericLiteral
>> derivation (another thing I can change), but that doesn't help because
>> certain static methods within the Calcite library will bring it back to
>> whatever Calcite's default SqlNumericLiteral is.
>>
>> I am an ac
>>
>> On Mon, Dec 9, 2024 at 11:26 AM Mihai Budiu <mb...@gmail.com> wrote:
>>
>> > You didn't answer my question about what you are trying to achieve.
>> >
>> > You say "I need...". Why do you need that? And I suspect you don't  
>> need
>> it
>> > yourself, but some other system you interact with expects something
>> > particular. What are the fixed points in your system, and what can be
>> > changed?
>> >
>> > Mihai
>> >
>> > ________________________________
>> > From: Stephen Carlin <sc...@cloudera.com.INVALID>
>> > Sent: Monday, December 9, 2024 11:20 AM
>> > To: dev@calcite.apache.org <de...@calcite.apache.org>
>> > Subject: Re: literal "type" issues in my database
>> >
>> > My general issue though still stands though?  I'll test out my code  
>> with
>> > 1.38 and see how that goes.
>> >
>> > But ultimately, the validator is forcing a literal type on me that
>> doesn't
>> > match what my database has (as of 1.37).
>> >
>> > Explicit casting is irrelevant for me.  I'm trying to parse an end
>> > user's SQL statement which doesn't have a cast.
>> >
>> > For instance, if I parse the number "2", Calcite is creating this as a
>> > RelDataType.INTEGER.  This doesn't match my needs, since I need this  
>> as a
>> > RelDataType.TINYINT.  This is forcing me to change the type at some
>> point.
>> >
>> > While that one might be easily fixable, my second one is not.   
>> Calcite is
>> > creating "hello" as a char(5).  I need it as a VARCHAR(<max bigint>)
>> (which
>> > I use for String type).
>> >
>> > On Mon, Dec 9, 2024 at 11:12 AM Stephen Carlin <sc...@cloudera.com>
>> > wrote:
>> >
>> > > Oh my.
>> > >
>> > > I am running on 1.37.
>> > >
>> > > I see that you have made a change 5 months ago in github.  That  
>> might
>> > > help!  Thanks!
>> > >
>> > > On Mon, Dec 9, 2024 at 10:52 AM Mihai Budiu <mb...@gmail.com>  
>> wrote:
>> > >
>> > >> What are you trying to achieve?
>> > >>
>> > >> Indeed, today the Calcite parser assumes that numeric literals  
>> with an
>> > >> exponent are floating point.
>> > >>
>> > >> I don't think the standard mandates a specific type based on the
>> syntax
>> > >> of the literal.
>> > >>
>> > >> I am pretty sure Calcite can parse literals above Long.MAX_VALUE,
>> since
>> > >> some Decimal literals may be larger.
>> > >> After parsing all literals are stored as Java BigDecimal value,  
>> which
>> > has
>> > >> unbounded precision.
>> > >>
>> > >> In general, if you want a specific type for a SQL expression, you
>> should
>> > >> write it down explicitly in the code, using a CAST. The rules for
>> > implicit
>> > >> casts are also unspecified and vary from database to database.
>> > >>
>> > >> Mihai
>> > >>
>> > >> ________________________________
>> > >> From: Stephen Carlin <sc...@cloudera.com.INVALID>
>> > >> Sent: Monday, December 9, 2024 10:40 AM
>> > >> To: dev@calcite.apache.org <de...@calcite.apache.org>
>> > >> Subject: literal "type" issues in my database
>> > >>
>> > >> Hi,
>> > >>
>> > >> I'm having some literal type issues when trying to use Calcite  
>> with my
>> > >> database.  I have quite a few of them, and I thought perhaps it  
>> was my
>> > >> database not dealing with the SQL standard correctly, but the  
>> latest
>> > one I
>> > >> hit seems, well, inconsistent within Calcite.
>> > >>
>> > >> When I run the literal 123.45 through Calcite, it is producing a
>> decimal
>> > >> type for me, which is fine.  However, if I pass in the literal 1e32
>> > >> through
>> > >> Calcite, it gives me a double.
>> > >>
>> > >> I noticed a comment somewhere which states Calcite cannot handle
>> > integers
>> > >> over the bigint max.  The comment was from 2006, so it doesn't look
>> like
>> > >> it
>> > >> will be addressed anytime soon?
>> > >>
>> > >> Has anyone dealt with this? If so, how did you handle it?
>> > >>
>> > >> I'm not sure this can be handled well.  I did try to change the  
>> parser
>> > to
>> > >> create my own NumericLiteral.  However, when I used a negative  
>> integer
>> > out
>> > >> of range, it used a static SqlLiteral create function embedded in  
>> the
>> > >> Calcite jar file and had similar issues.
>> > >>
>> > >> I do have a hack around this in my code that gets around this
>> > >> post-validation time, so I won't be addressing this in Calcite in  
>> the
>> > near
>> > >> future, but has anyone thought about this in general?
>> > >>
>> > >> I should also mention (since I alluded to it at the beginning of my
>> > >> message) that my database treats char literals as a  
>> "varchar(maxint)"
>> > type
>> > >> (Calcite treats it as char) and tinyints like "2" as a tinyint
>> (calcite
>> > >> treats this as int).
>> > >>
>> > >> Thanks,
>> > >>
>> > >> Steve
>> > >>
>> > >
>> >

Re: literal "type" issues in my database

Posted by Julian Hyde <jh...@gmail.com>.

Also, there are more types than literal formats. For some types, e.g. TINYINT, the only way to create a literal is to use a cast: “CAST(3 AS TINYINT)”. 

This problem is not unique to SQL. In Java, if you want a literal of type short you write “(short) 3”. 

Julian

> On Dec 10, 2024, at 6:11 AM, Stamatis Zampetakis <za...@apache.org> wrote:
> 
> For what is worth, the SQL standard defines that numeric literals with
> the exponent notation (e.g., 1e32) are approximate numerics (e.g.,
> float, double, real, etc.) while literals with periods (e.g., 123.45)
> are exact numerics (int, smallint, decimal, etc.). The fact that
> calcite parser treats literals with the exponent as floating point is
> aligned with the SQL standard.
> 
> Obviously many DBMS don't follow the standard closely so we should
> provide the right extension points to allow them to customize the
> types of the literals. My feeling is that this is more like a
> responsibility for the validator so I am wondering if the latter can
> be extended to accommodate for such use-cases.
> 
> Best,
> Stamatis
> 
>> On Tue, Dec 10, 2024 at 2:42 AM Stephen Carlin
>> <sc...@cloudera.com.invalid> wrote:
>> 
>> Hi,
>> 
>> So I'm aware of the limitations of ReINodes and that they already contain
>> type information.  I won't explain here how I did it, but trust me, I did
>> it.  And the code is working fine.  If you are curious, I can DM you some
>> more information.  And I will fully admit my solution is hacky.
>> 
>> But I do have to respectfully disagree with you about the SQL shuttle not
>> being a hack.
>> 
>> Anytime you have a state that exists and has to then be corrected to be
>> what you want it to be?  Via a shuttle? That's a hack, in my opinion.  We
>> should be able to infer the right types at SqlLiteral type creation, not
>> correct it after initial creation.
>> 
>> Perhaps some kind of "literal factory" is needed?  I haven't thought out a
>> design, but that's the first thing that pops into my head.  And this
>> factory can then be replaced by a custom factory.
>> 
>> 
>>> On Mon, Dec 9, 2024 at 4:36 PM Mihai Budiu <mb...@gmail.com> wrote:
>>> 
>>> You cannot make changes like that on the RelNodes, since they already
>>> contain type information, which will not match if you change the types. You
>>> have to do it on the SqlNodes. I don't think the shuttle is a hack at all.
>>> 
>>> Mihai
>>> 
>>> ------------------------------
>>> *From:* Stephen Carlin <sc...@cloudera.com.INVALID>
>>> *Sent:* Monday, December 9, 2024 11:54 AM
>>> *To:* dev@calcite.apache.org <de...@calcite.apache.org>
>>> *Subject:* Re: literal "type" issues in my database
>>> 
>>> If I do a "create table as select 2" in my database, it creates a TINYINT
>>> column.  I hope that answers your question.   So I need the RelDataType to
>>> be of type TINYINT
>>> 
>>> I do have a mechanism to deal with this in my current code a little bit
>>> similar to what you're suggesting (I won't go into it, but it works, it's
>>> 'shuttle-ish' and at RelNode time).  But a shuttle seems like a hack, no?
>>> 
>>> Anyway, I might develop this in the future to get rid of my hack, but just
>>> wanted to bring it up.
>>> 
>>> The DECIMAL issue was really my main problem, but I see you specifically
>>> dealt with this in 1.38 with this jira:
>>> https://issues.apache.org/jira/browse/CALCITE-3522.   Heh, I didn't think
>>> to look in the latest version since the original comment was dated from
>>> 2006 and I was one version off from the most recent version.  So thanks for
>>> that fix!
>>> 
>>> Thanks for your help!
>>> 
>>>> On Mon, Dec 9, 2024 at 11:45 AM Mihai Budiu <mb...@gmail.com> wrote:
>>> 
>>>> Why is 2 a TINYINT and not an INTEGER?
>>>> What is the type of 128?
>>>> 
>>>> If the only problem is the literals, you can probably fix it by writing a
>>>> SqlShuttle which inserts appropriate casts for literals, converting the
>>>> SqlLiteral with value 2 to a CAST(2 AS TINYINT).
>>>> 
>>>> Mihai
>>>> 
>>>> ________________________________
>>>> From: Stephen Carlin <sc...@cloudera.com.INVALID>
>>>> Sent: Monday, December 9, 2024 11:36 AM
>>>> To: dev@calcite.apache.org <de...@calcite.apache.org>
>>>> Subject: Re: literal "type" issues in my database
>>>> 
>>>> Sorry, lemme try to explain further...
>>>> 
>>>> I am a Java developer working on a database that is trying to use the
>>>> Calcite library. There is no other system I'm interacting with.  I am
>>> using
>>>> Calcite to parse, validate, and optimize my sql query.
>>>> 
>>>> We have branched off our own version of Parser.jj and running with the
>>>> Calcitte library to generate the whole SqlNode tree.  After the SqlNode
>>>> tree gets generated, we use the RelNodeConverter and are generating
>>>> RelNodes, and doing all sorts of optimizations on top of that.
>>>> 
>>>> I've debugged this several months ago, but the RelDataType that is
>>> created
>>>> when the number 2 is seen in the parser (and goes through the parser and
>>>> validator and RelNodeConverter) is coming out with a type of
>>>> SqlTypeName.INTEGER.  This doesn't match what my system expects, since we
>>>> prefer a SqlTypeName.TINYINT
>>>> 
>>>> I can change Parser.jj and potentially create my one SqlNumericLiteral
>>>> derivation (another thing I can change), but that doesn't help because
>>>> certain static methods within the Calcite library will bring it back to
>>>> whatever Calcite's default SqlNumericLiteral is.
>>>> 
>>>> I am an ac
>>>> 
>>>> On Mon, Dec 9, 2024 at 11:26 AM Mihai Budiu <mb...@gmail.com> wrote:
>>>> 
>>>>> You didn't answer my question about what you are trying to achieve.
>>>>> 
>>>>> You say "I need...". Why do you need that? And I suspect you don't need
>>>> it
>>>>> yourself, but some other system you interact with expects something
>>>>> particular. What are the fixed points in your system, and what can be
>>>>> changed?
>>>>> 
>>>>> Mihai
>>>>> 
>>>>> ________________________________
>>>>> From: Stephen Carlin <sc...@cloudera.com.INVALID>
>>>>> Sent: Monday, December 9, 2024 11:20 AM
>>>>> To: dev@calcite.apache.org <de...@calcite.apache.org>
>>>>> Subject: Re: literal "type" issues in my database
>>>>> 
>>>>> My general issue though still stands though?  I'll test out my code
>>> with
>>>>> 1.38 and see how that goes.
>>>>> 
>>>>> But ultimately, the validator is forcing a literal type on me that
>>>> doesn't
>>>>> match what my database has (as of 1.37).
>>>>> 
>>>>> Explicit casting is irrelevant for me.  I'm trying to parse an end
>>>>> user's SQL statement which doesn't have a cast.
>>>>> 
>>>>> For instance, if I parse the number "2", Calcite is creating this as a
>>>>> RelDataType.INTEGER.  This doesn't match my needs, since I need this as
>>> a
>>>>> RelDataType.TINYINT.  This is forcing me to change the type at some
>>>> point.
>>>>> 
>>>>> While that one might be easily fixable, my second one is not.  Calcite
>>> is
>>>>> creating "hello" as a char(5).  I need it as a VARCHAR(<max bigint>)
>>>> (which
>>>>> I use for String type).
>>>>> 
>>>>> On Mon, Dec 9, 2024 at 11:12 AM Stephen Carlin <sc...@cloudera.com>
>>>>> wrote:
>>>>> 
>>>>>> Oh my.
>>>>>> 
>>>>>> I am running on 1.37.
>>>>>> 
>>>>>> I see that you have made a change 5 months ago in github.  That might
>>>>>> help!  Thanks!
>>>>>> 
>>>>>> On Mon, Dec 9, 2024 at 10:52 AM Mihai Budiu <mb...@gmail.com>
>>> wrote:
>>>>>> 
>>>>>>> What are you trying to achieve?
>>>>>>> 
>>>>>>> Indeed, today the Calcite parser assumes that numeric literals with
>>> an
>>>>>>> exponent are floating point.
>>>>>>> 
>>>>>>> I don't think the standard mandates a specific type based on the
>>>> syntax
>>>>>>> of the literal.
>>>>>>> 
>>>>>>> I am pretty sure Calcite can parse literals above Long.MAX_VALUE,
>>>> since
>>>>>>> some Decimal literals may be larger.
>>>>>>> After parsing all literals are stored as Java BigDecimal value,
>>> which
>>>>> has
>>>>>>> unbounded precision.
>>>>>>> 
>>>>>>> In general, if you want a specific type for a SQL expression, you
>>>> should
>>>>>>> write it down explicitly in the code, using a CAST. The rules for
>>>>> implicit
>>>>>>> casts are also unspecified and vary from database to database.
>>>>>>> 
>>>>>>> Mihai
>>>>>>> 
>>>>>>> ________________________________
>>>>>>> From: Stephen Carlin <sc...@cloudera.com.INVALID>
>>>>>>> Sent: Monday, December 9, 2024 10:40 AM
>>>>>>> To: dev@calcite.apache.org <de...@calcite.apache.org>
>>>>>>> Subject: literal "type" issues in my database
>>>>>>> 
>>>>>>> Hi,
>>>>>>> 
>>>>>>> I'm having some literal type issues when trying to use Calcite with
>>> my
>>>>>>> database.  I have quite a few of them, and I thought perhaps it was
>>> my
>>>>>>> database not dealing with the SQL standard correctly, but the latest
>>>>> one I
>>>>>>> hit seems, well, inconsistent within Calcite.
>>>>>>> 
>>>>>>> When I run the literal 123.45 through Calcite, it is producing a
>>>> decimal
>>>>>>> type for me, which is fine.  However, if I pass in the literal 1e32
>>>>>>> through
>>>>>>> Calcite, it gives me a double.
>>>>>>> 
>>>>>>> I noticed a comment somewhere which states Calcite cannot handle
>>>>> integers
>>>>>>> over the bigint max.  The comment was from 2006, so it doesn't look
>>>> like
>>>>>>> it
>>>>>>> will be addressed anytime soon?
>>>>>>> 
>>>>>>> Has anyone dealt with this? If so, how did you handle it?
>>>>>>> 
>>>>>>> I'm not sure this can be handled well.  I did try to change the
>>> parser
>>>>> to
>>>>>>> create my own NumericLiteral.  However, when I used a negative
>>> integer
>>>>> out
>>>>>>> of range, it used a static SqlLiteral create function embedded in
>>> the
>>>>>>> Calcite jar file and had similar issues.
>>>>>>> 
>>>>>>> I do have a hack around this in my code that gets around this
>>>>>>> post-validation time, so I won't be addressing this in Calcite in
>>> the
>>>>> near
>>>>>>> future, but has anyone thought about this in general?
>>>>>>> 
>>>>>>> I should also mention (since I alluded to it at the beginning of my
>>>>>>> message) that my database treats char literals as a
>>> "varchar(maxint)"
>>>>> type
>>>>>>> (Calcite treats it as char) and tinyints like "2" as a tinyint
>>>> (calcite
>>>>>>> treats this as int).
>>>>>>> 
>>>>>>> Thanks,
>>>>>>> 
>>>>>>> Steve
>>>>>>> 
>>>>>> 
>>>>> 
>>>> 
>>>