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 <zabe...@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 >> <scar...@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 <mbu...@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 <scar...@cloudera.com.INVALID> >>> *Sent:* Monday, December 9, 2024 11:54 AM >>> *To:* dev@calcite.apache.org <dev@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 <mbu...@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 <scar...@cloudera.com.INVALID> >>>> Sent: Monday, December 9, 2024 11:36 AM >>>> To: dev@calcite.apache.org <dev@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 <mbu...@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 <scar...@cloudera.com.INVALID> >>>>> Sent: Monday, December 9, 2024 11:20 AM >>>>> To: dev@calcite.apache.org <dev@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 <scar...@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 <mbu...@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 <scar...@cloudera.com.INVALID> >>>>>>> Sent: Monday, December 9, 2024 10:40 AM >>>>>>> To: dev@calcite.apache.org <dev@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 >>>>>>> >>>>>> >>>>> >>>> >>>
