You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "Tracy0725 (via GitHub)" <gi...@apache.org> on 2026/05/22 07:22:55 UTC
[I] oracle 19c parse sql error: Unknown column 'CURRENT_DATE' in 'where clause'. [shardingsphere]
Tracy0725 opened a new issue, #38712:
URL: https://github.com/apache/shardingsphere/issues/38712
## Bug Report
### Which version of ShardingSphere did you use?
shardingSphere JDBC 5.5.3 + springboot + oracle 19c
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
shardingSphere JDBC
### SQL
SELECT
area.org_id,
bu.id AS buId,
area.id AS workAreaId
FROM
hos_org_emp_post emp
LEFT JOIN hos_org_business_unit bu ON
bu.id = emp.bu_id
LEFT JOIN hos_org_post post ON
post.id = emp.post_id
LEFT JOIN hos_org_work_area area ON
area.id = bu.wa_id
LEFT JOIN hos_org_person person ON
person.person_id = emp.pa_person_id
WHERE
bu.is_deleted = 0
AND emp.is_deleted = 0
AND emp.status = 1
AND area.is_deleted = 0
AND post.is_deleted = 0
AND person.is_deleted = 0
AND (bu.end_date IS NULL
OR CURRENT_DATE <= bu.end_date)
AND (bu.start_date IS NULL
OR CURRENT_DATE >= bu.start_date)
AND (emp.end_date IS NULL
OR CURRENT_DATE <= emp.end_date)
AND (emp.start_date IS NULL
OR CURRENT_DATE >= emp.start_date)
AND (area.end_date IS NULL
OR CURRENT_DATE <= area.end_date)
AND (area.start_date IS NULL
OR CURRENT_DATE >= area.start_date)
AND (person.end_date IS NULL
OR CURRENT_DATE <= person.end_date)
AND (person.start_date IS NULL
OR CURRENT_DATE >= person.start_date)
AND (post.end_date IS NULL
OR CURRENT_DATE <= post.end_date)
AND (post.start_date IS NULL
OR CURRENT_DATE >= post.start_date)
AND bu.is_activity = 1
AND emp.is_activity = 1
AND area.is_activity = 1
AND post.is_activity = 1
AND person.is_activity = 1
AND emp.pa_person_id ='1'
AND emp.bu_id IN ('1')
ORDER BY
emp.seq_no,
emp.create_date ASC,
emp.create_time ASC,
emp.id DESC
### error
Caused by: org.apache.shardingsphere.infra.exception.kernel.metadata.ColumnNotFoundException: Unknown column 'CURRENT_DATE' in 'where clause'.
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.ColumnSegmentBinder.lambda$getColumnSegmentInfo$2(ColumnSegmentBinder.java:169)
at org.apache.shardingsphere.infra.exception.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:44)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.ColumnSegmentBinder.getColumnSegmentInfo(ColumnSegmentBinder.java:168)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.ColumnSegmentBinder.bind(ColumnSegmentBinder.java:88)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:103)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:51)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:51)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.type.BinaryOperationExpressionBinder.bind(BinaryOperationExpressionBinder.java:50)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.expression.ExpressionSegmentBinder.bind(ExpressionSegmentBinder.java:82)
at org.apache.shardingsphere.infra.binder.engine.segment.dml.predicate.WhereSegmentBinder.bind(WhereSegmentBinder.java:49)
at org.apache.shardingsphere.infra.binder.engine.statement.dml.SelectStatementBinder.lambda$bind$2(SelectStatementBinder.java:74)
at java.util.Optional.map(Optional.java:215)
at org.apache.shardingsphere.infra.binder.engine.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:74)
at org.apache.shardingsphere.infra.binder.engine.type.DMLStatementBindEngine.bind(DMLStatementBindEngine.java:45)
at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindSQLStatement(SQLBindEngine.java:73)
at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:59)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:144)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:115)
at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:108)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:377)
at sun.reflect.GeneratedMethodAccessor61.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
at com.sun.proxy.$Proxy145.prepareStatement(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
... 363 common frames omitted
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] oracle 19c parse sql error: Unknown column 'CURRENT_DATE' in 'where clause'. [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #38712: oracle 19c parse sql error: Unknown column 'CURRENT_DATE' in 'where clause'.
URL: https://github.com/apache/shardingsphere/issues/38712
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] oracle 19c parse sql error: Unknown column 'CURRENT_DATE' in 'where clause'. [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #38712:
URL: https://github.com/apache/shardingsphere/issues/38712#issuecomment-4555086065
Hi @Tracy0725, thanks for reporting this.
This is a duplicate issue. It is covered by the same `ColumnSegmentBinder` unparenthesized-function binding problem fixed by #38350. #38350 added `DialectFunctionOption`, and Oracle now includes `CURRENT_DATE` in its dialect-level unparenthesized function list, so the root cause is already covered on current `dev`.
In ShardingSphere-JDBC 5.5.3, Oracle `CURRENT_DATE` was not recognized as an unparenthesized function that should skip column binding. It was therefore bound as a normal column and finally failed with `Unknown column 'CURRENT_DATE' in 'where clause'`. Please verify with a version that includes #38350. I suggest labeling this as `type: duplicate`, `in: SQL bind`, `db: Oracle`, and closing it as duplicate.
The reply above is based on the analysis below; the detailed reasoning is kept here for reference and follow-up contributors.
### Problem Understanding
- **Issue:** Oracle 19c + ShardingSphere-JDBC 5.5.3 fails in SQL bind when bare `CURRENT_DATE` is used in `WHERE` predicates against date columns.
- **Topology:** JDBC + Spring Boot. Governance mode and registry/config center are not provided, but the stack trace is in `SQLBindEngine` / `ColumnSegmentBinder`, so topology does not affect the duplicate classification.
- **Observed Evidence:** `OBS-1`, `OBS-2`, `OBS-3`, `OBS-4`, `OBS-5`, `OBS-6`
- **OBS-1:** #38712 reports `shardingSphere JDBC 5.5.3 + springboot + oracle 19c`, and the SQL uses `CURRENT_DATE <= ...` / `CURRENT_DATE >= ...` multiple times. Source: https://github.com/apache/shardingsphere/issues/38712
- **OBS-2:** #38712 fails in `ColumnSegmentBinder.getColumnSegmentInfo` with `ColumnNotFoundException: Unknown column 'CURRENT_DATE' in 'where clause'`. Source: https://github.com/apache/shardingsphere/issues/38712
- **OBS-3:** In tag `5.5.3`, `ColumnSegmentBinder` has a static skip list containing `SYSDATE`, `SYSTIMESTAMP`, `CURRENT_TIMESTAMP`, and `LOCALTIMESTAMP`, but not `CURRENT_DATE`. Source: `infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java:61`
- **OBS-4:** In tag `5.5.3`, `ColumnSegmentBinder` throws `ColumnNotFoundException` when no input column is found and binding cannot be skipped. Source: `infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java:168`
- **OBS-5:** #38350 was merged with the title `Add DialectFunctionOption to handle wrong skip column bind in ColumnSegmentBinder`; its merge commit is `840f0de1ac71ec42b1d1575684bea23d794e9543`, and its milestone is `5.5.4`. Source: https://github.com/apache/shardingsphere/pull/38350
- **OBS-6:** Current `dev` checks unparenthesized functions through `DatabaseTypeRegistry(...).getDialectDatabaseMetaData().getFunctionOption().getUnparenthesizedFunctionNames()`, and `OracleFunctionOption` includes `CURRENT_DATE`. Source: `infra/binder/core/src/main/java/org/apache/shardingsphere/infra/binder/engine/segment/dml/expression/type/ColumnSegmentBinder.java:87`, `database/connector/dialect/oracle/src/main/java/org/apache/shardingsphere/database/connector/oracle/metadata/database/option/OracleFunctionOption.java:33`
### Root Cause
- **Observation:** #38712 fails during SQL bind, not during Oracle database execution (`OBS-2`).
- **Observation:** The 5.5.3 `ColumnSegmentBinder` skip list does not include `CURRENT_DATE`, so bare `CURRENT_DATE` continues into the normal column-binding path (`OBS-3`, `OBS-4`).
- **Observation:** #38350 already fixed the same class of unparenthesized-function skip-binding problem through dialect-level `DialectFunctionOption`, and the Oracle dialect now includes `CURRENT_DATE` (`OBS-5`, `OBS-6`).
- **Inference:** The root cause hit by #38712 is already covered by #38350, so this should be handled as a duplicate instead of a new bug (`OBS-2`, `OBS-3`, `OBS-5`, `OBS-6`).
- **Confidence:** High.
### Problem Analysis
- **Issue Type:** Duplicate
- **Evidence:** #38712 is an incorrect `ColumnSegmentBinder` column-binding failure for Oracle bare `CURRENT_DATE`; #38350 fixed the same `ColumnSegmentBinder` unparenthesized-function skip-binding logic and covers `CURRENT_DATE` in the Oracle dialect.
- **Label Recommendation:** `type: duplicate`, `in: SQL bind`, `db: Oracle`
### Problem Conclusion
- **Evidence Confidence:** High
- **Impact Scope:** Oracle + ShardingSphere-JDBC 5.5.3 + bare `CURRENT_DATE` expressions
- **Topology:** JDBC + governance mode not provided / not material
- **Issue Type:** Duplicate
- **Duplicate Of:** #38350
- **Fix PR:** #38350
- **Merged In:** `840f0de1ac71ec42b1d1575684bea23d794e9543`, milestone `5.5.4`
- **Recommended Labels:** `type: duplicate`, `in: SQL bind`, `db: Oracle`
- **Next Action:** Ask the reporter to verify with a version that includes #38350. If it is no longer reproducible, close this as a duplicate covered by #38350.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org