http://starrocks.io logo
Join Slack
Powered by
Title: How to handle case-sensitive column names i...
# questions-and-troubleshooting
q
Title: How to handle case-sensitive column names in StarRocks Oracle Catalog? Body: Hi everyone, I'm encountering an issue with case sensitivity when querying Oracle tables through StarRocks External Catalog. When I run 
DESC
, I can see columns like 
name1
(lowercase). However, running 
SELECT name1 ...
throws an 
ORA-00904
error because Oracle treats unquoted identifiers as uppercase (
NAME1
). It only works if I manually add double quotes: 
SELECT "name1" ...
. Looking at the error stack (
JDBCScanner.open
), it seems StarRocks pushes down the query without quoting the lowercase identifier. 1. Is this a known limitation of the Oracle Catalog? 2. Do I need to quote every lowercase column manually in my queries? 3. Or is there a configuration to make StarRocks automatically handle identifier quoting for Oracle? Thanks for any insights! LOGs: command1:
Copy code
DESC oracle_catalog.ORACLE.test_user;
print:
ID	DECIMAL(38,0)	Yes	false			
NAME	VARCHAR(100)	Yes	false			
name1	VARCHAR(100)	Yes	false			
username	VARCHAR(20)	Yes	false
select name1 from oracle_catalog.ORACLE.test_user;
print:
SQL 错误 [1064] [42000]: open JDBCScanner failed, error: java.sql.SQLSyntaxErrorException: ORA-00904: "NAME1": invalid identifier

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
	at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
	at com.starrocks.jdbcbridge.JDBCScanner.open(JDBCScanner.java:96)
Caused by: Error : 904, Position : 22, Sql = SELECT * FROM (SELECT name1 FROM "ORACLE"."test_user") WHERE ROWNUM <= 200, OriginalSql = SELECT * FROM (SELECT name1 FROM "ORACLE"."test_user") WHERE ROWNUM <= 200, Error Msg = ORA-00904: "NAME1": invalid identifier

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
	... 17 more
: BE:10002
select "name1" from oracle_catalog.ORACLE.test_user;
print(error):
name1
name1
select NAME from oracle_catalog.ORACLE.test_user;
print(right):
xxxx
xxxx
b
@Rocky I'm encountering an issue with case sensitivity when querying Oracle tables through StarRocks External Catalog. When I run 
DESC
, I can see columns like 
name1
(lowercase). However, running 
SELECT name1 ...
throws an 
ORA-00904
error because Oracle treats unquoted identifiers as uppercase (
NAME1
). It only works if I manually add double quotes: 
SELECT "name1" ...
. Looking at the error stack (
JDBCScanner.open
), it seems StarRocks pushes down the query without quoting the lowercase identifier. 1. Is this a known limitation of the Oracle Catalog? 2. Do I need to quote every lowercase column manually in my queries? 3. Or is there a configuration to make StarRocks automatically handle identifier quoting for Oracle? Thanks for any insights!
r
Hello! I'm Rocky, your StarRocks chatbot. I've looked into your issue regarding case sensitivity with the Oracle JDBC Catalog. This behavior is a known challenge when integrating StarRocks with Oracle, primarily because of how the two systems handle identifiers: 1. StarRocks Behavior: In StarRocks, column names are generally treated as case-insensitive. When StarRocks retrieves metadata from Oracle, it often maps column names to lowercase by default. 2. Oracle Behavior: Oracle is case-sensitive for identifiers that are lowercase or mixed-case. Unquoted identifiers in Oracle are automatically converted to uppercase (e.g., 
name1
becomes 
NAME1
). To query a column that was created in lowercase, Oracle requires it to be enclosed in double quotes (
"name1"
). 3. The Issue: When StarRocks pushes down a query via the JDBC Catalog, if it sends the identifier 
name1
without quotes, Oracle interprets it as 
NAME1
, resulting in the 
ORA-00904: "NAME1": invalid identifier
error. Answers to your questions: 1. Is this a known limitation? Yes, this is a known behavior. While StarRocks has introduced features like 
enable_table_name_case_insensitive
in v4.0, the documentation explicitly states that this feature does not support JDBC catalogs. The way StarRocks handles identifier quoting during JDBC pushdown can sometimes lead to this discrepancy with Oracle's strict quoting rules for lowercase columns. 2. Do I need to quote every lowercase column manually? Currently, in most versions, manually quoting the identifier in your query (e.g., 
SELECT "name1" FROM ...
) is the standard way to ensure StarRocks passes the quotes to Oracle, preventing the automatic capitalization. 3. Is there a configuration to handle this automatically? There isn't a single global toggle specifically for "automatic Oracle quoting" for columns in the JDBC Catalog yet. However, you can try the following: * Check the Metadata: When you created the Oracle table, if the columns were created without quotes (e.g., 
CREATE TABLE t1 (NAME1 VARCHAR2(10))
), Oracle stores them as uppercase. In that case, querying them as 
name1
(unquoted) in StarRocks usually works because Oracle treats it as 
NAME1
. The issue specifically arises if the columns were created as 
"name1"
(lowercase) in Oracle. * Use Backticks: In your StarRocks SQL, try using backticks: `` SELECT 
name1
FROM ... ``. StarRocks often uses the underlying dialect's quote character (double quotes for Oracle) when identifiers are quoted in the source query. * Check StarRocks Version: Ensure you are on at least v3.2.9, which is when official support for the Oracle JDBC Catalog was introduced. Newer versions have improved SQL dialect translation for pushdowns. Recommendation: If your Oracle schema uses lowercase or mixed-case column names, the most reliable approach at this time is to enclose the column names in double quotes in your 
SELECT
statements. This ensures the pushdown logic preserves the casing exactly as Oracle expects. If you have further questions or if this doesn't resolve the issue, feel free to share your StarRocks version! References • release_notes/release-3.2.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/jdbc_catalog.md • sql-reference/System_limit.md
2 Views
Open in Slack
PreviousNext
Powered by