[HIVE-13708] Create table should verify datatypes supported by the serde

• Type: Bug
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Component/s: Query Planning
• Assignee: Hari Sankar Sivarama Subramaniyan
• Reporter: Thejas Nair
• Created: 06/May/16 16:57
• Updated: 09/Jun/18 16:16

Description

As Goldshuv mentioned in HIVE-7777.
Create table with serde such as OpenCSVSerde allows for creation of table with columns of arbitrary types. But 'describe table' would still return string datatypes, and so does selects on the table.
This is misleading and would result in users not getting intended results.
The create table ideally should disallow the creation of such tables with unsupported types.

Example posted by Goldshuv in HIVE-7777 -

CREATE EXTERNAL TABLE test (totalprice DECIMAL(38,10)) 
ROW FORMAT SERDE 'com.bizo.hive.serde.csv.CSVSerde' with 
serdeproperties ("separatorChar" = ",","quoteChar"= "'","escapeChar"= "\\") 
STORED AS TEXTFILE 
LOCATION '<some location>' 
tblproperties ("skip.header.line.count"="1");

Now consider this sql:

hive> select min(totalprice) from test;

in this case given my data, the result should have been 874.89, but the actual result became 100001.57 (as it is first according to byte ordering of a string type). this is a wrong result.

hive> desc extended test;
OK
o_totalprice string from deserializer
...

Issue Links

• is depended upon by: HIVE-19842 Improve Hive UDF/SerDe Usability
• relates to: HIVE-13709 OpenCSVSerde should support tables non string columns in tables
• relates to: HIVE-7777 Add CSV Serde based on OpenCSV