Shyama created SPARK-27723: ------------------------------ Summary: Unable to pull the oracle table data using patitionColumn date/timeStamp Key: SPARK-27723 URL: https://issues.apache.org/jira/browse/SPARK-27723 Project: Spark Issue Type: Bug Components: SQL Affects Versions: 2.4.1 Environment: Me using spark-sql-2.4.1v , java8 . ojdbc6.jar Reporter: Shyama Fix For: 2.4.0
Reproduction steps : 1. create oracle table create table schema1.modal_vals( FAMILY_ID NOT NULL NUMBER, INSERTION_DATE NOT NULL DATE, ITEM_VALUE VARCHAR2(4000), YEAR NUMBER, QUARTER NUMBER, LAST_UPDATE_DATE DATE ) 3. insert data into oracle table with date column format like "30-JUN-02" insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-02","bbb-",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","b+",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-17","bbb-",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","bb",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-02","ccc-",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","aa-",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-OCT-13","a-",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-03","bbb-",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","b",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-FEB-03","aa+",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-13","aa+",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JAN-19","aaa+",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"30-JUN-18","ccc-",2013,2,null); insert into modal_vals(FAMILY_ID,INSERTION_DATE,ITEM_VALUE,YEAR,QUARTER,LAST_UPDATE_DATE) values(2,"01-MAY-19","bb-",2013,2,null); //please fill the respected oracle details DataFrameReader ora_df_reader = spark.read().format("jdbc") .option("url", o_url) .option("driver", Constants.ORACLE_DRIVER) .option("user", o_userName) .option("password", o_passwd) .option("fetchsize",1000); Dataset<Row> ss = ora_df_reader .option("inferSchema", true) .option("schema","schema1") .option("numPartitions", 20); .option("partitionColumn", "INSERTION_DATE"); .option("lowerBound", "2002-03-31" ) .option("upperBound", "2019-05-01") .option("dateFormat", "yyyy-MM-dd" )// Tried all "yyyy-mm-dd" ,"yyyy-MM-dd" "YYYY-MM-DD" "DD-MMM-YY" "dd-MMM-yy" .option("dbtable", "select * from schema1.modal_vals") .load(); Error : {{java.sql.SQLException: ORA-12801: error signaled in parallel query server P001(2) ORA-01861: literal does not match format string}} -- This message was sent by Atlassian JIRA (v7.6.3#76005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org