flo076 commented on PR #35472: URL: https://github.com/apache/beam/pull/35472#issuecomment-3019387378
Hello @liferoad , After further analysis, your modification appears to be correct. However, the current version of Calcite (1.28.0) lacks support for many BigQuery (or other dialect) functions. In fact, if you examine the org.apache.calcite.sql.fun.SqlLibraryOperators for BigQuery, there are only 18 functions available compared to 100 in the latest versions of Calcite (see reference: [Calcite SqlLibraryOperators Documentation](https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/fun/SqlLibraryOperators.html)). It seems necessary to create a separate issue to address updating Calcite, as version 1.28.0 was released 4 years ago. As a suggestion for this Pull Request, adding the ability to configure the lexical policy ( lex property) could be a useful enhancement. My test updated with bigquery function supported by calcite 1.28 ```java @Test public void testInternalConnect_unbounded_limit_bq() throws Exception { ReadOnlyTableProvider tableProvider = new ReadOnlyTableProvider( "test", ImmutableMap.of( "test", TestUnboundedTable.of( Schema.FieldType.INT32, "order_id", Schema.FieldType.INT32, "site_id", Schema.FieldType.INT32, "price", Schema.FieldType.STRING, "label", Schema.FieldType.STRING, "date", Schema.FieldType.DATETIME, "order_time") .timestampColumnIndex(5) .addRows( Duration.ZERO, 1, 1, 1, "test-1", "2025-06-30", FIRST_DATE, 1, 2, 6, "test-2", "2025-06-30", FIRST_DATE))); BeamSqlPipelineOptions options = PipelineOptionsFactory.create().as(BeamSqlPipelineOptions.class); options.setCalciteConnectionDialect("bigquery"); CalciteConnection connection = JdbcDriver.connect(tableProvider, options); assertEquals("bigquery", connection.getProperties().getProperty("fun", "CALCITE")); Statement statement = connection.createStatement(); ResultSet resultSet1 = statement.executeQuery( "SELECT SUBSTRING(`label`,2), SUBSTR(`label`, 2,4) FROM test LIMIT 1"); assertTrue(resultSet1.next()); System.out.println("DEBUG " + resultSet1.getString(1)); System.out.println("DEBUG " + resultSet1.getString(2)); // System.out.println(resultSet1.getInt(2)); assertFalse(resultSet1.next()); ResultSet resultSet2 = statement.executeQuery("SELECT * FROM test LIMIT 2"); assertTrue(resultSet2.next()); assertTrue(resultSet2.next()); assertFalse(resultSet2.next()); } ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@beam.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
