Re: Beam SQL Alias issue while using With Clause

Hi Andrew, Thank you so much for your help. Sorry to hear you changed team :( I can handle calcite upgrades if there is a fix. I was working on calcite upgrade but then we started having so many issues. That's why I stopped doing it. Talat On Thu, Mar 2, 2023 at 11:56 AM Andrew Pilloud wrote:

Re: Beam SQL Alias issue while using With Clause

Hi Talat, I managed to turn your test case into something against Calcite. It looks like there is a bug affecting tables that contain one or more single element structs and no multi element structs. I've sent the details to the Calcite mailing list here.

Re: Beam SQL Alias issue while using With Clause

Hi @Andrew Pilloud Sorry for the late response. Yes your test is working fine. I changed the test input structure like our input structure. Now this test also has the same exception. Feb 21, 2023 2:02:28 PM org.apache.beam.sdk.extensions.sql.impl.CalciteQueryPlanner convertToBeamRel INFO: SQL:

Re: Beam SQL Alias issue while using With Clause

I have a test case that I believe should reproduce this on both head and 2.43 but it ends up with a different logical plan. Can you provide your input types? We have a class of issues around compex types https://github.com/apache/beam/issues/19009 I don't believe the

Re: Beam SQL Alias issue while using With Clause

Hi Andrew, Thank you for your MR. I am parricated to help us to solve the issue. I rerun our tests and they are partially passing now with your fix. However, there is one more issue with the WITH clause. When i run following query somehow beam lost type of column WITH tempTable AS (SELECT *

Re: Beam SQL Alias issue while using With Clause

It looks like Calcite stopped considering field names in RelNode equality as of Calcite 2.22 (which we use in Beam v2.34.0+). This can result in a planner state where two nodes that only differ by field name are considered equivalent. I have a fix for Beam in

Re: Beam SQL Alias issue while using With Clause

Also this is at very least a Beam bug. You can file a Beam issue if you want, otherwise I will when I get back. Andrew On Fri, Jan 27, 2023 at 11:27 AM Andrew Pilloud wrote: > Hi Talat, > > I did get your test case running and added some logging to > RexProgramBuilder.mergePrograms. There is

Re: Beam SQL Alias issue while using With Clause

Hi Talat, I did get your test case running and added some logging to RexProgramBuilder.mergePrograms. There is only one merge that occurs during the test and it has an output type of RecordType(JavaType(int) ID, JavaType(class java.lang.String) V). This does seem like the correct output name but

Re: Beam SQL Alias issue while using With Clause

Hi Andrew, Yes This aligned also with my debugging. In My Kenn's reply you can see a sql test which I wrote in Calcite. Somehow Calcite does not have this issue with the 1.28 version. !use post !set outputformat mysql #Test aliases with with clause WITH tempTable(id, v) AS (select

Re: Beam SQL Alias issue while using With Clause

Yes, that worked. The issue does not occur if I disable all of the following planner rules: CoreRules.FILTER_CALC_MERGE, CoreRules.PROJECT_CALC_MERGE, LogicalCalcMergeRule.INSTANCE (which wraps CoreRules.CALC_MERGE), and BeamCalcMergeRule.INSTANCE (which wraps CoreRules.CALC_MERGE). All the

Re: Beam SQL Alias issue while using With Clause

Hi Andrew, Thanks for writing a test for this use case. Without Where clause it works as expected on our test cases also too. Please add where clause on second select. With the below query it does not return column names. I tested on my local also. WITH tempTable (id, v) AS (SELECT f_int as id,

Re: Beam SQL Alias issue while using With Clause

+dev@beam.apache.org I tried reproducing this but was not successful, the output schema was as expected. I added the following to BeamSqlMultipleSchemasTest.java at head. (I did discover that PAssert.that(result).containsInAnyOrder(output) doesn't validate column names however.) @Test