Michael, the problem is not because CHAR(1) and CHAR(7). Calcite can derive common type in this case = CHAR(7) and all will work ok.
The problem is that one type is [<scalar> ARRAY] and another [<RecordType> ARRAY]. I see two options for resolving this problem: 1. Allow casting scalar type to RecordType with one field. I described it inside https://issues.apache.org/jira/browse/CALCITE-4999 2. Modify `ARRAY` function in the way that it will return ARRAY of scalar if subquery is used as argument. Like Postgres does - https://www.postgresql.org/docs/14/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS On Thu, Feb 10, 2022 at 2:02 PM Michael Mior <mm...@apache.org> wrote: > The two types in your example are incompatible. One is an array of CHAR(1). > The other is an array of records, each with a single CHAR(7) field. > -- > Michael Mior > mm...@apache.org > > > Le ven. 4 févr. 2022 à 11:27, Dmitry Sysolyatin <dm.sysolya...@gmail.com> > a > écrit : > > > So, the previous case started to work. But I faced with another issue. > This > > query does not work: > > > > ``` > > "SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from > > unnest(ARRAY['1','2']) x))" > > ``` > > > > ``` > > java.lang.IllegalArgumentException: Cannot infer return type for > > ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0) > > ARRAY] > > ``` > > > > I created an issue - https://issues.apache.org/jira/browse/CALCITE-4999 > > and > > PR - https://github.com/apache/calcite/pull/2712 > > > > > > On Fri, Feb 4, 2022 at 2:51 PM Dmitry Sysolyatin < > dm.sysolya...@gmail.com> > > wrote: > > > > > Actually, > > > > `SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY)` > > > helped, thanks ! > > > I will try to understand why it doesn't work with BABEL conformance > > > > > > On Fri, Feb 4, 2022 at 2:35 PM Dmitry Sysolyatin < > > dm.sysolya...@gmail.com> > > > wrote: > > > > > >> How SqlParser config can help? If I understood correctly, parser > config > > >> affects only parser.parse stage. But code fails at the validation > phase > > >> > > >> Also, I would like to use ARRAY_CONCAT function with postgres dialect. > > My > > >> parser config at the moment: > > >> > > >> ``` > > >> val parserConfig: SqlParser.Config = > > >> PostgresqlSqlDialect.DEFAULT > > >> .configureParser(SqlParser > > >> .config() > > >> // It is needed in order to parse PG "!~" operator > > >> .withParserFactory(SqlBabelParserImpl.FACTORY) > > >> ) > > >> .withConformance(SqlConformanceEnum.BABEL) > > >> ``` > > >> > > >> On Fri, Feb 4, 2022 at 2:26 PM Thomas Rebele > <treb...@tibco.com.invalid > > > > > >> wrote: > > >> > > >>> Hello, > > >>> > > >>> SqlParserTest might help you. It has some checks related to > BIG_QUERY. > > >>> Maybe a > > >>> > > >>> > > > .parserConfig(SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY)) > > >>> could fix the problem. > > >>> > > >>> Cordialement / Best Regards, > > >>> *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com > > >>> > > >>> > > >>> On Fri, Feb 4, 2022 at 12:57 PM Dmitry Sysolyatin < > > >>> dm.sysolya...@gmail.com> > > >>> wrote: > > >>> > > >>> > Hi! > > >>> > I have a problem with ARRAY_CONCAT operator. > > >>> > > > >>> > The following code failed with the exception ''No match found for > > >>> function > > >>> > signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)". > > >>> > ``` > > >>> > val config = Frameworks.newConfigBuilder() > > >>> > .parserConfig(parserConfig) > > >>> > .defaultSchema(CalciteSchema.createRootSchema(false, > > >>> false).plus()) > > >>> > .operatorTable(SqlOperatorTables.chain( > > >>> > SqlStdOperatorTable.instance(), > > >>> > > > SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable( > > >>> > SqlLibrary.BIG_QUERY > > >>> > ))) > > >>> > .programs(Programs.standard()) > > >>> > .build() > > >>> > val planner = Frameworks.getPlanner(config) > > >>> > > > >>> > val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])" > > >>> > val parsedSqlNode = planner.parse(query) > > >>> > planner.validate(parsedSqlNode) > > >>> > ``` > > >>> > > > >>> > Am I doing something wrong or it is a bug? > > >>> > > > >>> > > >> > > >
