[
https://issues.apache.org/jira/browse/HIVE-10488?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14513641#comment-14513641
]
Alexander Pivovarov commented on HIVE-10488:
--------------------------------------------
I can not reproduce this issue in hive 1.2.0
I created 2 tables
t3 - textfile
t3o - Orc
{code}
hive> desc formatted t3;
OK
# col_name data_type comment
rnum int
cdt date
# Detailed Table Information
Database: default
Owner: apivovarov
CreateTime: Sun Apr 26 23:58:29 PDT 2015
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://localhost/apps/apivovarov/warehouse/t3
Table Type: MANAGED_TABLE
Table Parameters:
transient_lastDdlTime 1430117909
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat:
org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.098 seconds, Fetched: 27 row(s)
{code}
{code}
hive> desc formatted t3o;
OK
# col_name data_type comment
rnum int
cdt date
# Detailed Table Information
Database: default
Owner: apivovarov
CreateTime: Mon Apr 27 00:00:11 PDT 2015
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://localhost/apps/apivovarov/warehouse/t3o
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE true
numFiles 1
numRows 4
rawDataSize 184
totalSize 302
transient_lastDdlTime 1430118011
# Storage Information
SerDe Library: org.apache.hadoop.hive.ql.io.orc.OrcSerde
InputFormat: org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.096 seconds, Fetched: 32 row(s)
{code}
{code}
hive> select * from t3;
OK
0 NULL
1 1996-01-01
2 2000-01-01
3 2000-12-31
Time taken: 0.086 seconds, Fetched: 4 row(s)
{code}
{code}
hive> select * from t3o;
OK
0 NULL
1 1996-01-01
2 2000-01-01
3 2000-12-31
Time taken: 0.086 seconds, Fetched: 4 row(s)
{code}
{code}
hive> select rnum, cdt, cast (cdt as timestamp) from t3;
OK
0 NULL NULL
1 1996-01-01 1996-01-01 00:00:00
2 2000-01-01 2000-01-01 00:00:00
3 2000-12-31 2000-12-31 00:00:00
Time taken: 0.091 seconds, Fetched: 4 row(s)
{code}
{code}
hive> select rnum, cdt, cast (cdt as timestamp) from t3o;
OK
0 NULL NULL
1 1996-01-01 1996-01-01 00:00:00
2 2000-01-01 2000-01-01 00:00:00
3 2000-12-31 2000-12-31 00:00:00
Time taken: 0.108 seconds, Fetched: 4 row(s)
{code}
MR
{code}
hive> select t3.rnum, t3.cdt, cast (t3.cdt as timestamp) cts, t3o.cdt cdt2,
cast(t3o.cdt as timestamp) cts2 from t3 join t3o on (t3.rnum = t3o.rnum);
Query ID = apivovarov_20150427000533_2734a9a1-63eb-45d4-83a4-4129ae3e7afc
Total jobs = 1
15/04/27 00:05:36 WARN util.NativeCodeLoader: Unable to load native-hadoop
library for your platform... using builtin-java classes where applicable
Execution log at:
/tmp/apivovarov/apivovarov_20150427000533_2734a9a1-63eb-45d4-83a4-4129ae3e7afc.log
2015-04-27 00:05:37 Starting to launch local task to process map join;
maximum memory = 477102080
2015-04-27 00:05:39 Dump the side-table for tag: 0 with group count: 4 into
file:
file:/tmp/apivovarov/fe4b8d14-3414-4790-a737-7a5d00bd04d0/hive_2015-04-27_00-05-33_412_2029315734201436275-1/-local-10003/HashTable-Stage-3/MapJoin-mapfile00--.hashtable
2015-04-27 00:05:39 Uploaded 1 File to:
file:/tmp/apivovarov/fe4b8d14-3414-4790-a737-7a5d00bd04d0/hive_2015-04-27_00-05-33_412_2029315734201436275-1/-local-10003/HashTable-Stage-3/MapJoin-mapfile00--.hashtable
(345 bytes)
2015-04-27 00:05:39 End of local task; Time Taken: 1.612 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1429923083119_0002, Tracking URL =
http://c11.example.com:8088/proxy/application_1429923083119_0002/
Kill Command = /usr/lib/hadoop-2.6.0/bin/hadoop job -kill
job_1429923083119_0002
Hadoop job information for Stage-3: number of mappers: 1; number of reducers: 0
2015-04-27 00:05:47,494 Stage-3 map = 0%, reduce = 0%
2015-04-27 00:05:54,942 Stage-3 map = 100%, reduce = 0%, Cumulative CPU 2.03
sec
MapReduce Total cumulative CPU time: 2 seconds 30 msec
Ended Job = job_1429923083119_0002
MapReduce Jobs Launched:
Stage-Stage-3: Map: 1 Cumulative CPU: 2.03 sec HDFS Read: 6756 HDFS Write:
206 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 30 msec
OK
0 NULL NULL NULL NULL
1 1996-01-01 1996-01-01 00:00:00 1996-01-01 1996-01-01
00:00:00
2 2000-01-01 2000-01-01 00:00:00 2000-01-01 2000-01-01
00:00:00
3 2000-12-31 2000-12-31 00:00:00 2000-12-31 2000-12-31
00:00:00
Time taken: 22.631 seconds, Fetched: 4 row(s)
{code}
> cast DATE as TIMESTAMP returns incorrect values
> -----------------------------------------------
>
> Key: HIVE-10488
> URL: https://issues.apache.org/jira/browse/HIVE-10488
> Project: Hive
> Issue Type: Bug
> Components: SQL
> Affects Versions: 0.13.1
> Reporter: N Campbell
> Assignee: Chaoyu Tang
>
> same data in textfile works
> same data loaded into an ORC table does not
> connection property of tez/mr makes no difference.
> select rnum, cdt, cast (cdt as timestamp) from tdt
> 0 <null> <null>
> 1 1996-01-01 1969-12-31 19:00:09.496
> 2 2000-01-01 1969-12-31 19:00:10.957
> 3 2000-12-31 1969-12-31 19:00:11.322
> vs
> 0 <null> <null>
> 1 1996-01-01 1996-01-01 00:00:00.0
> 2 2000-01-01 2000-01-01 00:00:00.0
> 3 2000-12-31 2000-12-31 00:00:00.0
> create table if not exists TDT ( RNUM int , CDT date )
> STORED AS orc ;
> insert overwrite table TDT select * from text.TDT;
> 0|\N
> 1|1996-01-01
> 2|2000-01-01
> 3|2000-12-31
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
