You can go two ways with this. If the SQL string gets parsed (and validated and translated to RelNode/RexNode) then I can see that’s convenient for you. It becomes a code maintenance problem for RelBuilder because its dependencies just got much larger and possibly cyclic.
If the SQL string remains unparsed then you will need to wrap it in a special function. One company I am aware of that uses Calcite has a function called ‘asIs’ for this purpose. The problem with that function is that it needs to deduce the true type of the expression (Calcite is strongly typed). Another is that if you rename tables, columns, or table or column aliases, or if you just push the function call to a different part of the RelNode tree, then you will need to revise the SQL string in order to keep it valid. Julian > On Jan 9, 2023, at 4:51 PM, Heiko Heijenga <[email protected]> wrote: > > I want to use the algebra builder to construct SQL queries, but use an > unparsed/unparseable subquery as the root (as just a string). Does that > make sense? > > (obviously this subquery would be in same sql dialect as output of RelToSql) > > On Mon, Jan 9, 2023 at 4:41 PM Julian Hyde <[email protected]> wrote: > >> What are you trying to achieve? >> >> If you want Calcite to understand an unparsed SQL expression then you >> need a SQL parser. RelBuilder is not a SQL parser. SqlParser is a SQL >> parser. >> >> On Mon, Jan 9, 2023 at 4:37 PM Heiko <[email protected]> wrote: >>> >>> Say from some legacy system I get some valid select stmt, e.g. "select >> a, b >>> from foobar" (and known field names/types, how do I push this as an >>> unparsed subquery into a relbuilder so I can do something like >>> >>> RelNode node = builder >>> .projectPlus(builder.call(MINUS, builder.field("b"), >>> builder.field("a"))) >>> .build(); >>> >>> Result result = new RelToSqlConverter(dialect).visitRoot(node); >>> >>> String sqlString = result >>> .asSelect() >>> .toSqlString(dialect) >>> .getSql(); >>> >>> where "sqlString" would then become something like >>> >>> SELECT `a` - `b` FROM (select a, b from foobar) AS `$f1` >>> >>> (note; I could of course parse the SELECT statement but I'm not 100% >>> convinced all the possible SQL select statements would be parseable by >>> Calcite, so when they're not I want to use above approach to treat them >>> basically as black-box subqueries) >>> >>> Thanks, >>> -Heiko >>
