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 <[email protected]> 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 <[email protected]> > *Sent:* Monday, December 9, 2024 11:54 AM > *To:* [email protected] <[email protected]> > *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 <[email protected]> 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 <[email protected]> > > Sent: Monday, December 9, 2024 11:36 AM > > To: [email protected] <[email protected]> > > 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 <[email protected]> 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 <[email protected]> > > > Sent: Monday, December 9, 2024 11:20 AM > > > To: [email protected] <[email protected]> > > > 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 <[email protected]> > > > 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 <[email protected]> > 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 <[email protected]> > > > >> Sent: Monday, December 9, 2024 10:40 AM > > > >> To: [email protected] <[email protected]> > > > >> 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 > > > >> > > > > > > > > > >
