hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alexander Pivovarov (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (HIVE-10488) cast DATE as TIMESTAMP returns incorrect values
Date Mon, 27 Apr 2015 07:11:39 GMT

    [ 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)

Mime
View raw message