Let’s continue conversation in https://issues.apache.org/jira/browse/CALCITE-4999 <https://issues.apache.org/jira/browse/CALCITE-4999>. I already pushed back there on the idea that ARRAY_CONCAT’s type derivation was wrong, and Dmitry conceded.
> On Feb 10, 2022, at 5:08 AM, Michael Mior <mm...@apache.org> wrote: > > Exactly. I understand the problem is not CHAR(1) vs CHAR(7), but the record > type. That is the point I was trying to make. > -- > Michael Mior > mm...@apache.org > > > Le jeu. 10 févr. 2022 à 07:47, Dmitry Sysolyatin <dm.sysolya...@gmail.com> > a écrit : > >> 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? >>>>>>>> >>>>>>> >>>>>> >>>> >>> >>
