You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Oleg Alekseev (Jira)" <ji...@apache.org> on 2024/05/10 12:46:00 UTC
[jira] [Updated] (CALCITE-6405) TO_TIMESTAMP doesn't work
[ https://issues.apache.org/jira/browse/CALCITE-6405?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Oleg Alekseev updated CALCITE-6405:
-----------------------------------
Description:
TO_TIMESTAMP doesn't work... it leads to java.lang.RuntimeException: cannot translate call TO_TIMESTAMP($t1, $t2)
PARSE_TIMESTAMP works good
{code:java}
import org.apache.calcite.adapter.jdbc.JdbcSchema;
import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.jdbc.CalciteConnection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.calcite.jdbc.Driver;
import org.apache.calcite.schema.SchemaPlus;
import javax.sql.DataSource;public class Test {
public static void main(String[] args) throws SQLException {
CalciteConnection connection = initCalciteConnection(); DataSource oracleDataSource1 = JdbcSchema.dataSource(
"jdbc:oracle:thin:@//<ip>:<port>/<service>",
"oracle.jdbc.OracleDriver",
"<user>",
"<password>"
); String schemaName = "oracle_1";
SchemaPlus rootSchema = connection.getRootSchema();
JdbcSchema jdbcSchema = JdbcSchema.create(rootSchema, schemaName, oracleDataSource1, null, "<schema>");
rootSchema.add(schemaName, jdbcSchema);// String sql = """
// SELECT PARSE_TIMESTAMP('%d.%m.%Y %H:%M:%S', '01.01.2024 00:00:00')
// """; String sql = """
SELECT TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
"""; ResultSet resultSet = connection.createStatement().executeQuery(sql); while (resultSet.next()) { for (int i=1; i <= resultSet.getMetaData().getColumnCount(); i++) {
System.out.println(resultSet.getString(i));
System.out.println(" ");
}
System.out.println(" ");
}
} private static CalciteConnection initCalciteConnection() throws SQLException {
DriverManager.registerDriver(new Driver());
Properties properties = new Properties();
// properties.putIfAbsent(CalciteConnectionProperty.FUN.camelName(), "oracle,postgresql,bigquery");
properties.putIfAbsent(CalciteConnectionProperty.FUN.camelName(), "all");
properties.putIfAbsent(CalciteConnectionProperty.CASE_SENSITIVE.camelName(), "false");
properties.putIfAbsent(CalciteConnectionProperty.QUOTED_CASING.camelName(), Casing.UNCHANGED.name());
properties.putIfAbsent(CalciteConnectionProperty.UNQUOTED_CASING.camelName(), Casing.UNCHANGED.name()); return DriverManager.getConnection("jdbc:calcite:", properties).unwrap(CalciteConnection.class);
}
}
{code}
was:
TO_TIMESTAMP doesn't work... it leads to java.lang.RuntimeException: cannot translate call TO_TIMESTAMP($t1, $t2)
PARSE_TIMESTAMP works good
```
import org.apache.calcite.adapter.jdbc.JdbcSchema;
import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.jdbc.CalciteConnection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.calcite.jdbc.Driver;
import org.apache.calcite.schema.SchemaPlus;
import javax.sql.DataSource;
public class Test {
public static void main(String[] args) throws SQLException {
CalciteConnection connection = initCalciteConnection();
DataSource oracleDataSource1 = JdbcSchema.dataSource(
"jdbc:oracle:thin:@//<ip>:<port>/<service>",
"oracle.jdbc.OracleDriver",
"<user>",
"<password>"
);
String schemaName = "oracle_1";
SchemaPlus rootSchema = connection.getRootSchema();
JdbcSchema jdbcSchema = JdbcSchema.create(rootSchema, schemaName, oracleDataSource1, null, "<schema>");
rootSchema.add(schemaName, jdbcSchema);
// String sql = """
// SELECT PARSE_TIMESTAMP('%d.%m.%Y %H:%M:%S', '01.01.2024 00:00:00')
// """;
String sql = """
SELECT TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
""";
ResultSet resultSet = connection.createStatement().executeQuery(sql);
while (resultSet.next()) {
for (int i=1; i <= resultSet.getMetaData().getColumnCount(); i++) {
System.out.println(resultSet.getString(i));
System.out.println(" ");
}
System.out.println(" ");
}
}
private static CalciteConnection initCalciteConnection() throws SQLException {
DriverManager.registerDriver(new Driver());
Properties properties = new Properties();
// properties.putIfAbsent(CalciteConnectionProperty.FUN.camelName(), "oracle,postgresql,bigquery");
properties.putIfAbsent(CalciteConnectionProperty.FUN.camelName(), "all");
properties.putIfAbsent(CalciteConnectionProperty.CASE_SENSITIVE.camelName(), "false");
properties.putIfAbsent(CalciteConnectionProperty.QUOTED_CASING.camelName(), Casing.UNCHANGED.name());
properties.putIfAbsent(CalciteConnectionProperty.UNQUOTED_CASING.camelName(), Casing.UNCHANGED.name());
return DriverManager.getConnection("jdbc:calcite:", properties).unwrap(CalciteConnection.class);
}
}
```
> TO_TIMESTAMP doesn't work
> -------------------------
>
> Key: CALCITE-6405
> URL: https://issues.apache.org/jira/browse/CALCITE-6405
> Project: Calcite
> Issue Type: Bug
> Reporter: Oleg Alekseev
> Priority: Major
>
> TO_TIMESTAMP doesn't work... it leads to java.lang.RuntimeException: cannot translate call TO_TIMESTAMP($t1, $t2)
> PARSE_TIMESTAMP works good
> {code:java}
> import org.apache.calcite.adapter.jdbc.JdbcSchema;
> import org.apache.calcite.avatica.util.Casing;
> import org.apache.calcite.config.CalciteConnectionProperty;
> import org.apache.calcite.jdbc.CalciteConnection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.util.Properties;
> import org.apache.calcite.jdbc.Driver;
> import org.apache.calcite.schema.SchemaPlus;
> import javax.sql.DataSource;public class Test {
> public static void main(String[] args) throws SQLException {
> CalciteConnection connection = initCalciteConnection(); DataSource oracleDataSource1 = JdbcSchema.dataSource(
> "jdbc:oracle:thin:@//<ip>:<port>/<service>",
> "oracle.jdbc.OracleDriver",
> "<user>",
> "<password>"
> ); String schemaName = "oracle_1";
> SchemaPlus rootSchema = connection.getRootSchema();
> JdbcSchema jdbcSchema = JdbcSchema.create(rootSchema, schemaName, oracleDataSource1, null, "<schema>");
> rootSchema.add(schemaName, jdbcSchema);// String sql = """
> // SELECT PARSE_TIMESTAMP('%d.%m.%Y %H:%M:%S', '01.01.2024 00:00:00')
> // """; String sql = """
> SELECT TO_TIMESTAMP('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
> """; ResultSet resultSet = connection.createStatement().executeQuery(sql); while (resultSet.next()) { for (int i=1; i <= resultSet.getMetaData().getColumnCount(); i++) {
> System.out.println(resultSet.getString(i));
> System.out.println(" ");
> }
> System.out.println(" ");
> }
> } private static CalciteConnection initCalciteConnection() throws SQLException {
> DriverManager.registerDriver(new Driver());
> Properties properties = new Properties();
> // properties.putIfAbsent(CalciteConnectionProperty.FUN.camelName(), "oracle,postgresql,bigquery");
> properties.putIfAbsent(CalciteConnectionProperty.FUN.camelName(), "all");
> properties.putIfAbsent(CalciteConnectionProperty.CASE_SENSITIVE.camelName(), "false");
> properties.putIfAbsent(CalciteConnectionProperty.QUOTED_CASING.camelName(), Casing.UNCHANGED.name());
> properties.putIfAbsent(CalciteConnectionProperty.UNQUOTED_CASING.camelName(), Casing.UNCHANGED.name()); return DriverManager.getConnection("jdbc:calcite:", properties).unwrap(CalciteConnection.class);
> }
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)