[jira] [Created] (HIVE-26186) Resultset returned by getTables does not order data per JDBC specification
N Campbell created HIVE-26186:
-
Summary: Resultset returned by getTables does not order data per
JDBC specification
Key: HIVE-26186
URL: https://issues.apache.org/jira/browse/HIVE-26186
Project: Hive
Issue Type: Bug
Components: JDBC
Affects Versions: 3.1.3
Environment: !HiveMeta.png!
Reporter: N Campbell
Attachments: HiveMeta.png
JDBC specification states that data in a Resultset must be ordered.
A simple Java program issues a request to getTables
ResultSet rs = dbMeta.getTables( {*}null{*}, "cert", "%", {*}null{*});
The Resultset is not order per JDBC spec
[https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getTables-java.lang.String-java.lang.String-java.lang.String-java.lang.String:A-]
Happens with various releases including
hive-jdbc-3.1.3000.7.1.7.0-551
hive-jdbc-3.1.3000.7.1.6.0-297
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Created] (HIVE-25280) Hive standalone JAR packaging javamail.providers
N Campbell created HIVE-25280: - Summary: Hive standalone JAR packaging javamail.providers Key: HIVE-25280 URL: https://issues.apache.org/jira/browse/HIVE-25280 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 3.1.1 Reporter: N Campbell Various Apache Hive standalone JAR files have been found to include javamail.providers and others do not. Was the inclusion of javamail a packaging defect ? Given it appears to be removed in other releases, can it be confirmed which versions the fix would be effective from. Fexample, hive-jdbc-3.1.0.3.1.5.0-152-standalone.jar and others (i.e. hive-jdbc-3.1.0.3.1.4.41-3 etc) have included or excluded javamail.providers. The presence of this file impacts products which need where their classpath includes the Apache Hive driver. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Created] (HIVE-24323) JDBC driver fails when using Kerberos due to missing dependencies
N Campbell created HIVE-24323:
-
Summary: JDBC driver fails when using Kerberos due to missing
dependencies
Key: HIVE-24323
URL: https://issues.apache.org/jira/browse/HIVE-24323
Project: Hive
Issue Type: Bug
Components: JDBC
Affects Versions: 3.1.0
Reporter: N Campbell
The Apache Hive web pages historically implied that only 3-JAR files are
required
hadoop-auth
hadoop-common
hive-jdbc
If a connection is attempted using Kerberos authentication, it will fail due to
several missing dependencies
hadoop-auth-3.1.1.3.1.5.0-152.jar
hadoop-common-3.1.1.3.1.5.0-152.jar
hive-jdbc-3.1.0.3.1.5.0-152-standalone.jar
It is unclear if the intent of the standalone JAR is to include these
dependencies or not. But does not seem to be any documentation either way.
It also appears that dependencies are not being shaded, which can result in
conflicts with guava or wstx jar files in the class path. Such as noted by
ORACLE {color:#00}Doc ID 2650046.1{color}
commons-collections-3.2.2.jar
commons-configuration2.jar
commons-lang-2.6.jar
guava-29.0-jre.jar
log4j-1.2.17.jar
slf4j-api-1.7.25.jar
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Created] (HIVE-22134) Hive 3.1 driver includes org.glassfish.jersey.* which can interfer with an application
N Campbell created HIVE-22134:
-
Summary: Hive 3.1 driver includes org.glassfish.jersey.* which can
interfer with an application
Key: HIVE-22134
URL: https://issues.apache.org/jira/browse/HIVE-22134
Project: Hive
Issue Type: Bug
Components: JDBC
Affects Versions: 3.1.0
Reporter: N Campbell
An application that uses JAX-RS 1.1 can be broken by the Hive 3.1 standalone
JAR.
For example, an application is running in IBM Websphere Liberty Profile (WLP)
which detects the classes packaged in the Apache Hive standalone JAR. This
results in WLP assuming that the application is providing it's implementation
and should not use the default in WLP.
Can the Apache Hive JDBC team confirm why these classes are in the JAR.
Can the Apache Hive JDBC team schedule to remove them if they are not mandatory.
Can the Apache Hive JDBC team confirm which individual JAR files can be copied
instead of the uber-standalone JAR which would not include these conflicting
classes.
This is the class which triggers the problem if all of the jersey stuff is
deleted the issue will go away
org.glassfish.jersey.server.internal.RuntimeDelegateImpl
{{org.glassfish.jersey.*}}
--
This message was sent by Atlassian Jira
(v8.3.2#803003)
[jira] [Created] (HIVE-22129) Hive 3.1 standalone JAR includes Microsoft SQL Server JDBC driver
N Campbell created HIVE-22129: - Summary: Hive 3.1 standalone JAR includes Microsoft SQL Server JDBC driver Key: HIVE-22129 URL: https://issues.apache.org/jira/browse/HIVE-22129 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 3.1.0 Reporter: N Campbell Attachments: ApacheHive.png The Apache Hive 3.1 JDBC driver, specifically the standalone JAR has included the Microsoft SQL Server JDBC driver. A mutual customer may be trying to use their own copy of a Microsoft JDBC driver and the Apache Hive driver. Potentially the JRE will pick up the code in the Apache Hive driver. What is it included at all and can it be removed going forward? !ApacheHive.png! -- This message was sent by Atlassian Jira (v8.3.2#803003)
[jira] [Created] (HIVE-15561) JDBC driver does not implement getArray method for array types
N Campbell created HIVE-15561:
-
Summary: JDBC driver does not implement getArray method for array
types
Key: HIVE-15561
URL: https://issues.apache.org/jira/browse/HIVE-15561
Project: Hive
Issue Type: Bug
Affects Versions: 1.2.1
Reporter: N Campbell
getDriverVersion: 1.2.1000.2.5.0.0-1245
The JDBC driver returns a JDBC type enum 2003 which is an array type. If an
application then attempts to call the getArray() method the driver will throw a
not implemented exception.
Array z = rs.getArray("carrint");
create table if not exists TARRSINT (RNUM int , CARRSINT ARRAY< SMALLINT >)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' COLLECTION ITEMS TERMINATED BY
'^' LINES TERMINATED BY '\n'
STORED AS textfile ;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (HIVE-15553) expression to compute running sum fails with Failed to breakup Windowing invocations into Groups
N Campbell created HIVE-15553: - Summary: expression to compute running sum fails with Failed to breakup Windowing invocations into Groups Key: HIVE-15553 URL: https://issues.apache.org/jira/browse/HIVE-15553 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 2.1.0 Reporter: N Campbell The following statement will fail in Hive.Expecting to compute the following as is possible in Oracle, Db2, Teradata, ... select c1, sum ( sum ( c3 ) ) over ( order by c1 ) from certtext.tolap group by c1 Error: Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: org.apache.hadoop.hive.ql.parse.SemanticException: Line 1:18 Expression not in GROUP BY key 'c3' SQLState: 42000 ErrorCode: 4 Note: if you use the following statement it can encounter a Tez error when c1 is a char vs string type. select c1, sum( c3 ), sum ( sum ( c3 ) ) over ( order by c1 ) from certtext.tolap group by c1 C1 SUM(C3) SUM(SUM(C3))OVER(ORDERBYC1) AAA 50 50 BBB 20 70 CCC 30 100 DDD 40 140 50 190 Table definition create table if not exists TOLAP (RNUM int , C1 char(3), C2 char(2), C3 int, C4 int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-15552) unable to coalesce DATE and TIMESTAMP types
N Campbell created HIVE-15552: - Summary: unable to coalesce DATE and TIMESTAMP types Key: HIVE-15552 URL: https://issues.apache.org/jira/browse/HIVE-15552 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 2.1.0 Reporter: N Campbell Priority: Minor COALESCE expression does not expect DATE and TIMESTAMP types select tdt.rnum, coalesce(tdt.cdt, cast(tdt.cdt as timestamp)) from certtext.tdt Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Argument type mismatch 'cdt': The expressions after COALESCE should all have the same type: "date" is expected but "timestamp" is found SQLState: 42000 ErrorCode: 4 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-15548) TEZ exception error when NULL ordering specification used on cursor or window agg
N Campbell created HIVE-15548: - Summary: TEZ exception error when NULL ordering specification used on cursor or window agg Key: HIVE-15548 URL: https://issues.apache.org/jira/browse/HIVE-15548 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 2.1.0 Reporter: N Campbell select c1, c2 from tset1 order by c1 asc nulls last, c2 asc nulls first select rnum , c1 , c2 , sum( c3 ) over (partition by sum( c3 ) over (partition by c1 order by c1 )) from tolap i.e. Error: Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.tez.TezTask. Vertex failed, vertexName=Reducer 2, vertexId=vertex_1483461312952_0011_11_01, diagnostics=[Task failed, taskId=task_1483461312952_0011_11_01_00, diagnostics=[TaskAttempt 0 failed, info=[Error: Error while running task ( failure ) : attempt_1483461312952_0011_11_01_00_0:java.lang.RuntimeException: java.lang.RuntimeException: java.io.EOFException: Detail: "java.io.EOFException" occured for field 0 of 2 fields (INT, CHAR) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:211) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.run(TezProcessor.java:168) at org.apache.tez.runtime.LogicalIOProcessorRuntimeTask.run(LogicalIOProcessorRuntimeTask.java:370) at org.apache.tez.runtime.task.TaskRunner2Callable$1.run(TaskRunner2Callable.java:73) at org.apache.tez.runtime.task.TaskRunner2Callable$1.run(TaskRunner2Callable.java:61) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1724) at org.apache.tez.runtime.task.TaskRunner2Callable.callInternal(TaskRunner2Callable.java:61) at org.apache.tez.runtime.task.TaskRunner2Callable.callInternal(TaskRunner2Callable.java:37) at org.apache.tez.common.CallableWithNdc.call(CallableWithNdc.java:36) at org.apache.hadoop.hive.llap.daemon.impl.StatsRecordingThreadPool$WrappedCallable.call(StatsRecordingThreadPool.java:110) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) table definitions: create table if not exists TSET1 (RNUM int , C1 int, C2 char(3)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; create table if not exists TOLAP (RNUM int , C1 char(3), C2 char(2), C3 int, C4 int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-15547) nulls not sorted last on cursor specification
N Campbell created HIVE-15547: - Summary: nulls not sorted last on cursor specification Key: HIVE-15547 URL: https://issues.apache.org/jira/browse/HIVE-15547 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 2.1.0 Reporter: N Campbell Query that attempts to sort nulls last produces an incorrect result set. (1) first column contains all null values which is wrong (2) second column has not sorted the only null value as the last row Hive server version: 2.1.0.2.5.3.0-37 Query: SELECT `tint`.`rnum`, `tint`.`cint` FROM `tint` ORDER BY `tint`.`rnum` ASC NULLS LAST Results: tint.rnum tint.cint -1 0 1 10 Source data rnumcint 0 1 -1 2 0 3 1 4 10 Table create table if not exists TINT ( RNUM int , CINT int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-15545) JDBC driver aborts when DatabaseMetadata.getFunctions is called
N Campbell created HIVE-15545: - Summary: JDBC driver aborts when DatabaseMetadata.getFunctions is called Key: HIVE-15545 URL: https://issues.apache.org/jira/browse/HIVE-15545 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 2.1.1 Reporter: N Campbell getDatabaseProductVersion:1.2.1000.2.5.3.0-37 getDriverVersion:1.2.1000.2.5.0.0-1245 ResultSet rs = dbMeta.getFunctions(null, null, null); Exception in thread "main" java.sql.SQLException: Required field 'functionName' is unset! Struct:TGetFunctionsReq(sessionHandle:TSessionHandle(sessionId:THandleIdentifier(guid:99 48 E7 57 6A 77 40 00 8D 49 99 34 81 51 C7 04, secret:F8 64 B2 9C D8 A2 41 7A 99 E6 F1 34 E9 38 13 1D)), functionName:null) at org.apache.hive.jdbc.HiveDatabaseMetaData.getFunctions(HiveDatabaseMetaData.java:330) at zBug.getFunctions(zBug.java:679) at test.main(test.java:87) Caused by: org.apache.thrift.protocol.TProtocolException: Required field 'functionName' is unset! Struct:TGetFunctionsReq(sessionHandle:TSessionHandle(sessionId:THandleIdentifier(guid:99 48 E7 57 6A 77 40 00 8D 49 99 34 81 51 C7 04, secret:F8 64 B2 9C D8 A2 41 7A 99 E6 F1 34 E9 38 13 1D)), functionName:null) at org.apache.hive.service.cli.thrift.TGetFunctionsReq.validate(TGetFunctionsReq.java:542) at org.apache.hive.service.cli.thrift.TCLIService$GetFunctions_args.validate(TCLIService.java:10145) at org.apache.hive.service.cli.thrift.TCLIService$GetFunctions_args$GetFunctions_argsStandardScheme.write(TCLIService.java:10202) at org.apache.hive.service.cli.thrift.TCLIService$GetFunctions_args$GetFunctions_argsStandardScheme.write(TCLIService.java:10171) at org.apache.hive.service.cli.thrift.TCLIService$GetFunctions_args.write(TCLIService.java:10122) at org.apache.thrift.TServiceClient.sendBase(TServiceClient.java:71) at org.apache.thrift.TServiceClient.sendBase(TServiceClient.java:62) at org.apache.hive.service.cli.thrift.TCLIService$Client.send_GetFunctions(TCLIService.java:384) at org.apache.hive.service.cli.thrift.TCLIService$Client.GetFunctions(TCLIService.java:376) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55) at java.lang.reflect.Method.invoke(Method.java:508) at org.apache.hive.jdbc.HiveConnection$SynchronizedHandler.invoke(HiveConnection.java:1363) at com.sun.proxy.$Proxy0.GetFunctions(Unknown Source) at org.apache.hive.jdbc.HiveDatabaseMetaData.getFunctions(HiveDatabaseMetaData.java:328) ... 2 more -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-11377) currrent_timestamp in ISO-SQL is a timezone bearing type but Hive uses timezoneless types
N Campbell created HIVE-11377: - Summary: currrent_timestamp in ISO-SQL is a timezone bearing type but Hive uses timezoneless types Key: HIVE-11377 URL: https://issues.apache.org/jira/browse/HIVE-11377 Project: Hive Issue Type: Bug Components: Documentation, SQL Affects Versions: 1.2.0 Reporter: N Campbell Priority: Minor Hive 1.2.x has added the niladic function current_timestamp. when ISO SQL introduced time zone bearing types, it defined two forms of niladic functions. Current_timetstamp/time return time zone bearing type and Local_timestamp/time non-time zone bearing types. This implementation is not described in the current documentation. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Timestamps -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-11371) Null pointer exception for nested table query when using ORC versus text
N Campbell created HIVE-11371: - Summary: Null pointer exception for nested table query when using ORC versus text Key: HIVE-11371 URL: https://issues.apache.org/jira/browse/HIVE-11371 Project: Hive Issue Type: Bug Affects Versions: 1.2.0 Reporter: N Campbell Following query will fail if the file format is ORC select tj1rnum, tj2rnum, tjoin3.rnum as rnumt3 from (select tjoin1.rnum tj1rnum, tjoin2.rnum tj2rnum, tjoin2.c1 tj2c1 from tjoin1 left outer join tjoin2 on tjoin1.c1 = tjoin2.c1 ) tj left outer join tjoin3 on tj2c1 = tjoin3.c1 aused by: java.lang.NullPointerException at org.apache.hadoop.hive.ql.exec.vector.VectorCopyRow$LongCopyRow.copy(VectorCopyRow.java:60) at org.apache.hadoop.hive.ql.exec.vector.VectorCopyRow.copyByReference(VectorCopyRow.java:260) at org.apache.hadoop.hive.ql.exec.vector.mapjoin.VectorMapJoinGenerateResultOperator.generateHashMapResultMultiValue(VectorMapJoinGenerateResultOperator.java:238) at org.apache.hadoop.hive.ql.exec.vector.mapjoin.VectorMapJoinOuterGenerateResultOperator.finishOuter(VectorMapJoinOuterGenerateResultOperator.java:495) at org.apache.hadoop.hive.ql.exec.vector.mapjoin.VectorMapJoinOuterLongOperator.process(VectorMapJoinOuterLongOperator.java:430) ... 22 more ]], Vertex did not succeed due to OWN_TASK_FAILURE, failedTasks:1 killedTasks:0, Vertex vertex_1437788144883_0004_2_02 [Map 1] killed/failed due to:null]DAG did not succeed due to VERTEX_FAILURE. failedVertices:1 killedVertices:0 SQLState: 08S01 ErrorCode: 2 getDatabaseProductName Apache Hive getDatabaseProductVersion 1.2.1.2.3.0.0-2557 getDriverName Hive JDBC getDriverVersion1.2.1.2.3.0.0-2557 getDriverMajorVersion 1 getDriverMinorVersion 2 create table if not exists TJOIN1 (RNUM int , C1 int, C2 int) -- ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS orc ; create table if not exists TJOIN2 (RNUM int , C1 int, C2 char(2)) -- ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS orc ; create table if not exists TJOIN3 (RNUM int , C1 int, C2 char(2)) -- ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS orc ; create table if not exists TJOIN4 (RNUM int , C1 int, C2 char(2)) -- ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS orc ; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-11372) join with betwee predicate comparing integer types returns no rows when ORC format used
N Campbell created HIVE-11372: - Summary: join with betwee predicate comparing integer types returns no rows when ORC format used Key: HIVE-11372 URL: https://issues.apache.org/jira/browse/HIVE-11372 Project: Hive Issue Type: Bug Affects Versions: 1.2.0 Reporter: N Campbell getDatabaseProductName Apache Hive getDatabaseProductVersion 1.2.1.2.3.0.0-2557 getDriverName Hive JDBC getDriverVersion1.2.1.2.3.0.0-2557 getDriverMajorVersion 1 getDriverMinorVersion 2 select tint.rnum, tsint.rnum from tint , tsint where tint.cint between tsint.csint and tsint.csint when ORC used no rows returned versus TEXT create table if not exists TSINT ( RNUM int , CSINT smallint ) -- ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS orc ; create table if not exists TINT ( RNUM int , CINT int ) -- ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS orc ; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-10936) incorrect result set when hive.vectorized.execution.enabled = true with predicate casting to CHAR or VARCHAR
N Campbell created HIVE-10936: - Summary: incorrect result set when hive.vectorized.execution.enabled = true with predicate casting to CHAR or VARCHAR Key: HIVE-10936 URL: https://issues.apache.org/jira/browse/HIVE-10936 Project: Hive Issue Type: Bug Components: Hive Affects Versions: 0.14.0 Environment: In this case using HDP install of Hive - 0.14.0.2.2.4.2-2 Reporter: N Campbell Query returns data when set hive.vectorized.execution.enabled = false -or- if target of CAST is STRING and not CHAR/VARCHAR set hive.vectorized.execution.enabled = true; select `GO_TIME_DIM`.`day_key` from `gosalesdw1021`.`go_time_dim` `GO_TIME_DIM` where CAST(`GO_TIME_DIM`.`current_year` AS CHAR(4)) = '2010' group by `GO_TIME_DIM`.`day_key`; create table GO_TIME_DIM ( DAY_KEY int , DAY_DATE timestamp , MONTH_KEY int , CURRENT_MONTH smallint , MONTH_NUMBER int , QUARTER_KEY int , CURRENT_QUARTER smallint , CURRENT_YEAR smallint , DAY_OF_WEEK smallint , DAY_OF_MONTH smallint , DAYS_IN_MONTH smallint , DAY_OF_YEAR smallint , WEEK_OF_MONTH smallint , WEEK_OF_QUARTER smallint , WEEK_OF_YEAR smallint , MONTH_EN string , WEEKDAY_EN string , MONTH_DE string , WEEKDAY_DE string , MONTH_FR string , WEEKDAY_FR string , MONTH_JA string , WEEKDAY_JA string , MONTH_AR string , WEEKDAY_AR string , MONTH_CS string , WEEKDAY_CS string , MONTH_DA string , WEEKDAY_DA string , MONTH_EL string , WEEKDAY_EL string , MONTH_ES string , WEEKDAY_ES string , MONTH_FI string , WEEKDAY_FI string , MONTH_HR string , WEEKDAY_HR string , MONTH_HU string , WEEKDAY_HU string , MONTH_ID string , WEEKDAY_ID string , MONTH_IT string , WEEKDAY_IT string , MONTH_KK string , WEEKDAY_KK string , MONTH_KO string , WEEKDAY_KO string , MONTH_MS string , WEEKDAY_MS string , MONTH_NL string , WEEKDAY_NL string , MONTH_NO string , WEEKDAY_NO string , MONTH_PL string , WEEKDAY_PL string , MONTH_PT string , WEEKDAY_PT string , MONTH_RO string , WEEKDAY_RO string , MONTH_RU string , WEEKDAY_RU string , MONTH_SC string , WEEKDAY_SC string , MONTH_SL string , WEEKDAY_SL string , MONTH_SV string , WEEKDAY_SV string , MONTH_TC string , WEEKDAY_TC string , MONTH_TH string , WEEKDAY_TH string , MONTH_TR string , WEEKDAY_TR string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS TEXTFILE LOCATION '../GO_TIME_DIM'; Then create an ORC equivalent table and load it insert overwrite table GO_TIME_DIM select * from TEXT.GO_TIME_DIM ; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-10488) cast DATE as TIMESTAMP returns incorrect values
N Campbell created HIVE-10488: - Summary: 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 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)
[jira] [Created] (HIVE-10487) remove non-ISO restriction that projections in a union have identical column names
N Campbell created HIVE-10487: - Summary: remove non-ISO restriction that projections in a union have identical column names Key: HIVE-10487 URL: https://issues.apache.org/jira/browse/HIVE-10487 Project: Hive Issue Type: Improvement Components: SQL Affects Versions: 0.13.1 Reporter: N Campbell Priority: Critical While documented https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union an application should be able to perform a union query where the projections are union compatible which does not include the projected column names being identical which Hive imposes vs ISO-SQL 20xx. i.e rejected select c1 from t1 union all select c2 from t2 Schema of both sides of union should match. _u1-subquery2 accepted select c1 from t1 union all select c2 c1 from t2 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-10282) Implement ISO-SQL substring semantics
N Campbell created HIVE-10282: - Summary: Implement ISO-SQL substring semantics Key: HIVE-10282 URL: https://issues.apache.org/jira/browse/HIVE-10282 Project: Hive Issue Type: Improvement Components: SQL Affects Versions: 0.13.0 Reporter: N Campbell Priority: Minor Hive provides SUBSTR which is copying MySQL which does not follow ISO-SQL semantic for a -ve start position. A -ve start is logically to the left of the first character in the string not starting from the end of the string. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (HIVE-9745) predicate evaluation of character fields with spaces and literals with spaces returns unexpected result
[
https://issues.apache.org/jira/browse/HIVE-9745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14330279#comment-14330279
]
N Campbell commented on HIVE-9745:
--
Turns out I added similar comments in 2013 to HIVE-3745 where references were
drawn to MySQL/Postgres in that thread etc.
predicate evaluation of character fields with spaces and literals with spaces
returns unexpected result
---
Key: HIVE-9745
URL: https://issues.apache.org/jira/browse/HIVE-9745
Project: Hive
Issue Type: Bug
Components: SQL
Affects Versions: 0.14.0
Reporter: N Campbell
The following query should return 5 rows but Hive returns 3
{code}
select rnum, tchar.cchar from tchar where not ( tchar.cchar = ' ' or (
tchar.cchar is null and ' ' is null ))
{code}
Consider the following project of the base table
{code}
select rnum, tchar.cchar,
case tchar.cchar when ' ' then 'space' else 'not space' end,
case when tchar.cchar is null then 'is null' else 'not null' end, case when '
' is null then 'is null' else 'not null' end
from tchar
order by rnum
{code}
Row 0 is a NULL
Row 1 was loaded with a zero length string ''
Row 2 was loaded with a single space ' '
{code}
rnum tchar.cchar _c2 _c3 _c4
0 null not space is null not null
1 not space not null
not null
2 not space not null
not null
3 BB not space not null
not null
4 EE not space not null
not null
5 FF not space not null
not null
{code}
Explicitly type cast the literal which many SQL developers would not expect
need to do.
{code}
select rnum, tchar.cchar,
case tchar.cchar when cast(' ' as char(1)) then 'space' else 'not space' end,
case when tchar.cchar is null then 'is null' else 'not null' end, case when
cast( ' ' as char(1)) is null then 'is null' else 'not null' end
from tchar
order by rnum
rnum tchar.cchar _c2 _c3 _c4
0 null not space is null not null
1 space not nullnot null
2 space not nullnot null
3 BB not space not null
not null
4 EE not space not null
not null
5 FF not space not null
not null
create table if not exists T_TCHAR ( RNUM int , CCHAR char(32 ))
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'
STORED AS TEXTFILE ;
0|\N
1|
2|
3|BB
4|EE
5|FF
create table if not exists TCHAR ( RNUM int , CCHAR char(32 ))
STORED AS orc ;
insert overwrite table TCHAR select * from T_TCHAR;
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (HIVE-9739) Quantified query fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask
N Campbell created HIVE-9739: Summary: Quantified query fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask Key: HIVE-9739 URL: https://issues.apache.org/jira/browse/HIVE-9739 Project: Hive Issue Type: Bug Components: SQL Reporter: N Campbell This fails when using Tez and ORC. It will run when text files are used or text/ORC and MapReduce and not Tez used. Is this another example of a type issue per https://issues.apache.org/jira/browse/HIVE-9735 select rnum, c1, c2 from tset1 as t1 where exists ( select c1 from tset2 where c1 = t1.c1 ) This will run in both Tez and MapReduce using a text file select rnum, c1, c2 from t_tset1 as t1 where exists ( select c1 from t_tset2 where c1 = t1.c1 ) Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.processRow(MapRecordSource.java:91) at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.pushRecord(MapRecordSource.java:68) at org.apache.hadoop.hive.ql.exec.tez.MapRecordProcessor.run(MapRecordProcessor.java:294) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:163) ... 13 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:52) at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.processRow(MapRecordSource.java:83) ... 16 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Unexpected exception: org.apache.hadoop.hive.serde2.io.HiveCharWritable cannot be cast to org.apache.hadoop.hive.common.type.HiveChar at org.apache.hadoop.hive.ql.exec.MapJoinOperator.processOp(MapJoinOperator.java:311) at org.apache.hadoop.hive.ql.exec.vector.VectorMapJoinOperator.processOp(VectorMapJoinOperator.java:249) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815) at org.apache.hadoop.hive.ql.exec.vector.VectorFilterOperator.processOp(VectorFilterOperator.java:111) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815) at org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:95) at org.apache.hadoop.hive.ql.exec.MapOperator$MapOpCtx.forward(MapOperator.java:157) at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:45) ... 17 more Caused by: java.lang.ClassCastException: org.apache.hadoop.hive.serde2.io.HiveCharWritable cannot be cast to org.apache.hadoop.hive.common.type.HiveChar at org.apache.hadoop.hive.ql.exec.vector.VectorColumnAssignFactory$18.assignObjectValue(VectorColumnAssignFactory.java:432) at org.apache.hadoop.hive.ql.exec.vector.VectorMapJoinOperator.internalForward(VectorMapJoinOperator.java:196) at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.genAllOneUniqueJoinObject(CommonJoinOperator.java:670) at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.checkAndGenObject(CommonJoinOperator.java:748) at org.apache.hadoop.hive.ql.exec.MapJoinOperator.processOp(MapJoinOperator.java:299) ... 24 more create table if not exists T_TSET1 (RNUM int , C1 int, C2 char(3)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; create table if not exists T_TSET1 (RNUM int , C1 int, C2 char(3)) STORED AS ORC ; create table if not exists T_TSET2 (RNUM int , C1 int, C2 char(3)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; TSET1 data 0|10|AAA 1|10|AAA 2|10|AAA 3|20|BBB 4|30|CCC 5|40|DDD 6|50|\N 7|60|\N 8|\N|AAA 9|\N|AAA 10|\N|\N 11|\N|\N TSET2 DATA 0|10|AAA 1|10|AAA 2|40|DDD 3|50|EEE 4|60|FFF -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (HIVE-9537) string expressions on a fixed length character do not preserve trailing spaces
[
https://issues.apache.org/jira/browse/HIVE-9537?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14328992#comment-14328992
]
N Campbell commented on HIVE-9537:
--
I saying that if the current implementation is by design and will not change
that it be more completely documented. In particular, to show examples of how
the design may deviate from other systems which implement fixed length
character types as many vendors do (Teradata, DB2, Informix, Netezza, ).
Unfortunately, the current implementation feels like cases where vendors have
added syntax that other products have but the implementation is not the
same/has various gaps. The SQL documentation on the Apache Hive WIKI has
limited discussion on the implementation etc.
As for the last comment: ISO-SQL concatenation CHAR(x) || CHAR(y) should return
CHAR(x+y) with implementation details re what happens when x+y maximum
precision of CHAR that a vendor provides.
string expressions on a fixed length character do not preserve trailing spaces
--
Key: HIVE-9537
URL: https://issues.apache.org/jira/browse/HIVE-9537
Project: Hive
Issue Type: Bug
Components: SQL
Reporter: N Campbell
Assignee: Aihua Xu
When a string expression such as upper or lower is applied to a fixed length
column the trailing spaces of the fixed length character are not preserved.
{code:sql}
CREATE TABLE if not exists TCHAR (
RNUM int,
CCHAR char(32)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
{code}
{{cchar}} as a {{char(32)}}.
{code:sql}
select cchar, concat(cchar, cchar), concat(lower(cchar), cchar),
concat(upper(cchar), cchar)
from tchar;
{code}
0|\N
1|
2|
3|BB
4|EE
5|FF
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9735) aggregate ( smalllint ) fails when ORC file used ava.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Short
[ https://issues.apache.org/jira/browse/HIVE-9735?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14329533#comment-14329533 ] N Campbell commented on HIVE-9735: -- Using JDBC with SQLSquirrel aggregate ( smalllint ) fails when ORC file used ava.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Short -- Key: HIVE-9735 URL: https://issues.apache.org/jira/browse/HIVE-9735 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 0.14.0 Reporter: N Campbell select min( tsint.csint ) from tsint select max( tsint.csint ) from tsint ava.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Short select min( t_tsint.csint ) from t_tsint create table if not exists T_TSINT ( RNUM int , CSINT smallint ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; create table if not exists TSINT ( RNUM int , CSINT smallint ) TERMINATED BY '\n' STORED AS orc ; input data loaded into text file and then inserted into ORC table from text based table 0|\N 1|-1 2|0 3|1 4|10 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (HIVE-9739) Various queries fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask
[ https://issues.apache.org/jira/browse/HIVE-9739?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-9739: - Summary: Various queries fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask (was: Quantified query fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask) Various queries fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask -- Key: HIVE-9739 URL: https://issues.apache.org/jira/browse/HIVE-9739 Project: Hive Issue Type: Bug Components: SQL Reporter: N Campbell This fails when using Tez and ORC. It will run when text files are used or text/ORC and MapReduce and not Tez used. Is this another example of a type issue per https://issues.apache.org/jira/browse/HIVE-9735 select rnum, c1, c2 from tset1 as t1 where exists ( select c1 from tset2 where c1 = t1.c1 ) This will run in both Tez and MapReduce using a text file select rnum, c1, c2 from t_tset1 as t1 where exists ( select c1 from t_tset2 where c1 = t1.c1 ) Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.processRow(MapRecordSource.java:91) at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.pushRecord(MapRecordSource.java:68) at org.apache.hadoop.hive.ql.exec.tez.MapRecordProcessor.run(MapRecordProcessor.java:294) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:163) ... 13 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:52) at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.processRow(MapRecordSource.java:83) ... 16 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Unexpected exception: org.apache.hadoop.hive.serde2.io.HiveCharWritable cannot be cast to org.apache.hadoop.hive.common.type.HiveChar at org.apache.hadoop.hive.ql.exec.MapJoinOperator.processOp(MapJoinOperator.java:311) at org.apache.hadoop.hive.ql.exec.vector.VectorMapJoinOperator.processOp(VectorMapJoinOperator.java:249) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815) at org.apache.hadoop.hive.ql.exec.vector.VectorFilterOperator.processOp(VectorFilterOperator.java:111) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815) at org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:95) at org.apache.hadoop.hive.ql.exec.MapOperator$MapOpCtx.forward(MapOperator.java:157) at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:45) ... 17 more Caused by: java.lang.ClassCastException: org.apache.hadoop.hive.serde2.io.HiveCharWritable cannot be cast to org.apache.hadoop.hive.common.type.HiveChar at org.apache.hadoop.hive.ql.exec.vector.VectorColumnAssignFactory$18.assignObjectValue(VectorColumnAssignFactory.java:432) at org.apache.hadoop.hive.ql.exec.vector.VectorMapJoinOperator.internalForward(VectorMapJoinOperator.java:196) at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.genAllOneUniqueJoinObject(CommonJoinOperator.java:670) at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.checkAndGenObject(CommonJoinOperator.java:748) at org.apache.hadoop.hive.ql.exec.MapJoinOperator.processOp(MapJoinOperator.java:299) ... 24 more create table if not exists T_TSET1 (RNUM int , C1 int, C2 char(3)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; create table if not exists T_TSET1 (RNUM int , C1 int, C2 char(3)) STORED AS ORC ; create table if not exists T_TSET2 (RNUM int , C1 int, C2 char(3)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; TSET1 data 0|10|AAA 1|10|AAA 2|10|AAA 3|20|BBB 4|30|CCC 5|40|DDD 6|50|\N 7|60|\N 8|\N|AAA 9|\N|AAA 10|\N|\N 11|\N|\N TSET2 DATA 0|10|AAA 1|10|AAA 2|40|DDD 3|50|EEE 4|60|FFF -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (HIVE-9739) Various queries fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask due to Caused by: java.lang.ClassCastException
[ https://issues.apache.org/jira/browse/HIVE-9739?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-9739: - Summary: Various queries fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask due to Caused by: java.lang.ClassCastException (was: Various queries fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask) Various queries fails with Tez/ORC file org.apache.hadoop.hive.ql.exec.tez.TezTask due to Caused by: java.lang.ClassCastException - Key: HIVE-9739 URL: https://issues.apache.org/jira/browse/HIVE-9739 Project: Hive Issue Type: Bug Components: SQL Reporter: N Campbell This fails when using Tez and ORC. It will run when text files are used or text/ORC and MapReduce and not Tez used. Is this another example of a type issue per https://issues.apache.org/jira/browse/HIVE-9735 select rnum, c1, c2 from tset1 as t1 where exists ( select c1 from tset2 where c1 = t1.c1 ) This will run in both Tez and MapReduce using a text file select rnum, c1, c2 from t_tset1 as t1 where exists ( select c1 from t_tset2 where c1 = t1.c1 ) Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.processRow(MapRecordSource.java:91) at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.pushRecord(MapRecordSource.java:68) at org.apache.hadoop.hive.ql.exec.tez.MapRecordProcessor.run(MapRecordProcessor.java:294) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:163) ... 13 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:52) at org.apache.hadoop.hive.ql.exec.tez.MapRecordSource.processRow(MapRecordSource.java:83) ... 16 more Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Unexpected exception: org.apache.hadoop.hive.serde2.io.HiveCharWritable cannot be cast to org.apache.hadoop.hive.common.type.HiveChar at org.apache.hadoop.hive.ql.exec.MapJoinOperator.processOp(MapJoinOperator.java:311) at org.apache.hadoop.hive.ql.exec.vector.VectorMapJoinOperator.processOp(VectorMapJoinOperator.java:249) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815) at org.apache.hadoop.hive.ql.exec.vector.VectorFilterOperator.processOp(VectorFilterOperator.java:111) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:815) at org.apache.hadoop.hive.ql.exec.TableScanOperator.processOp(TableScanOperator.java:95) at org.apache.hadoop.hive.ql.exec.MapOperator$MapOpCtx.forward(MapOperator.java:157) at org.apache.hadoop.hive.ql.exec.vector.VectorMapOperator.process(VectorMapOperator.java:45) ... 17 more Caused by: java.lang.ClassCastException: org.apache.hadoop.hive.serde2.io.HiveCharWritable cannot be cast to org.apache.hadoop.hive.common.type.HiveChar at org.apache.hadoop.hive.ql.exec.vector.VectorColumnAssignFactory$18.assignObjectValue(VectorColumnAssignFactory.java:432) at org.apache.hadoop.hive.ql.exec.vector.VectorMapJoinOperator.internalForward(VectorMapJoinOperator.java:196) at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.genAllOneUniqueJoinObject(CommonJoinOperator.java:670) at org.apache.hadoop.hive.ql.exec.CommonJoinOperator.checkAndGenObject(CommonJoinOperator.java:748) at org.apache.hadoop.hive.ql.exec.MapJoinOperator.processOp(MapJoinOperator.java:299) ... 24 more create table if not exists T_TSET1 (RNUM int , C1 int, C2 char(3)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; create table if not exists T_TSET1 (RNUM int , C1 int, C2 char(3)) STORED AS ORC ; create table if not exists T_TSET2 (RNUM int , C1 int, C2 char(3)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; TSET1 data 0|10|AAA 1|10|AAA 2|10|AAA 3|20|BBB 4|30|CCC 5|40|DDD 6|50|\N 7|60|\N 8|\N|AAA 9|\N|AAA 10|\N|\N 11|\N|\N TSET2 DATA 0|10|AAA 1|10|AAA 2|40|DDD 3|50|EEE 4|60|FFF -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-9743) incorrect result set for left outer join when executed with tez versus mapreduce
N Campbell created HIVE-9743: Summary: incorrect result set for left outer join when executed with tez versus mapreduce Key: HIVE-9743 URL: https://issues.apache.org/jira/browse/HIVE-9743 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 0.14.0 Reporter: N Campbell This query is supposed to return 3 rows and will when run without Tez but returns 2 rows when run with Tez. select tjoin1.rnum, tjoin1.c1, tjoin1.c2, tjoin2.c2 as c2j2 from tjoin1 left outer join tjoin2 on ( tjoin1.c1 = tjoin2.c1 and tjoin1.c2 15 ) tjoin1.rnum tjoin1.c1 tjoin1.c2 c2j2 1 20 25 null 2 null 50 null instead of tjoin1.rnum tjoin1.c1 tjoin1.c2 c2j2 0 10 15 null 1 20 25 null 2 null 50 null create table if not exists TJOIN1 (RNUM int , C1 int, C2 int) STORED AS orc ; 0|10|15 1|20|25 2|\N|50 create table if not exists TJOIN2 (RNUM int , C1 int, C2 char(2)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS TEXTFILE ; 0|10|BB 1|15|DD 2|\N|EE 3|10|FF -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-9745) predicate evaluation of character fields with spaces and literals with spaces returns unexpected result
N Campbell created HIVE-9745:
Summary: predicate evaluation of character fields with spaces and
literals with spaces returns unexpected result
Key: HIVE-9745
URL: https://issues.apache.org/jira/browse/HIVE-9745
Project: Hive
Issue Type: Bug
Components: SQL
Affects Versions: 0.14.0
Reporter: N Campbell
The following query should return 5 rows but Hive returns 3
select rnum, tchar.cchar from tchar where not ( tchar.cchar = ' ' or (
tchar.cchar is null and ' ' is null ))
Consider the following project of the base table
select rnum, tchar.cchar,
case tchar.cchar when ' ' then 'space' else 'not space' end,
case when tchar.cchar is null then 'is null' else 'not null' end, case when ' '
is null then 'is null' else 'not null' end
from tchar
order by rnum
Row 0 is a NULL
Row 1 was loaded with a zero length string ''
Row 2 was loaded with a single space ' '
rnumtchar.cchar _c2 _c3 _c4
0 null not space is null not null
1 not space not null
not null
2 not space not null
not null
3 BB not space not null
not null
4 EE not space not null
not null
5 FF not space not null
not null
Explicitly type cast the literal which many SQL developers would not expect
need to do.
select rnum, tchar.cchar,
case tchar.cchar when cast(' ' as char(1)) then 'space' else 'not space' end,
case when tchar.cchar is null then 'is null' else 'not null' end, case when
cast( ' ' as char(1)) is null then 'is null' else 'not null' end
from tchar
order by rnum
rnumtchar.cchar _c2 _c3 _c4
0 null not space is null not null
1 space not nullnot null
2 space not nullnot null
3 BB not space not null
not null
4 EE not space not null
not null
5 FF not space not null
not null
create table if not exists T_TCHAR ( RNUM int , CCHAR char(32 ))
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'
STORED AS TEXTFILE ;
0|\N
1|
2|
3|BB
4|EE
5|FF
create table if not exists TCHAR ( RNUM int , CCHAR char(32 ))
STORED AS orc ;
insert overwrite table TCHAR select * from T_TCHAR;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (HIVE-9537) string expressions on a fixed length character do not preserve trailing spaces
[
https://issues.apache.org/jira/browse/HIVE-9537?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14327408#comment-14327408
]
N Campbell commented on HIVE-9537:
--
I wouldn't suggest that use MySQL and Postgres alone is ideal to cross check
SQL semantics.
Should the Apache implementation for CHAR(n) stay this way going forward I
recommend you get the documentation improved.
Having seen companies migrate applications across vendors where this sort of
thing burns them. Many may not know what ISO-SQL 20xx states (let alone care)
and will presume that their source vendor(s) have followed the specification
etc.
Given the server side string operations etc, they may as well stay with VARCHAR
and know that trailing spaces are not preserved vs the 'appears' to have spaces
when projected but not in other cases.
See discussion:
http://www.postgresql.org/docs/9.1/static/datatype-character.html
See discussion of MySQL JDBC:
https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
re padCharsWithSpace
If you apply a fold (upper/lower) the type of the result is supposed to be
from the character value expression
Length should be the number of characters in the string value expression -
where number of characters is based upon the semantics of the character set of
the string value expression
If both operands are fixed length character strings, concatenation result is a
fixed length character string with a length equal to the sum of the lengths of
the operands where the length cannot exceed the maximum allowed for a fixed
length character string.
Cheers.
string expressions on a fixed length character do not preserve trailing spaces
--
Key: HIVE-9537
URL: https://issues.apache.org/jira/browse/HIVE-9537
Project: Hive
Issue Type: Bug
Components: SQL
Reporter: N Campbell
Assignee: Aihua Xu
When a string expression such as upper or lower is applied to a fixed length
column the trailing spaces of the fixed length character are not preserved.
{code:sql}
CREATE TABLE if not exists TCHAR (
RNUM int,
CCHAR char(32)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
{code}
{{cchar}} as a {{char(32)}}.
{code:sql}
select cchar, concat(cchar, cchar), concat(lower(cchar), cchar),
concat(upper(cchar), cchar)
from tchar;
{code}
0|\N
1|
2|
3|BB
4|EE
5|FF
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (HIVE-9734) Correlating expression cannot contain unqualified column references
N Campbell created HIVE-9734: Summary: Correlating expression cannot contain unqualified column references Key: HIVE-9734 URL: https://issues.apache.org/jira/browse/HIVE-9734 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 0.14.0 Reporter: N Campbell Priority: Minor The following valid ISO-SQL constructs will not parse Unsupported SubQuery Expression 'c1': Correlating expression cannot contain unqualified column references The statement will parse if the predicate is changed to where tjoin1.c1 in ( ) select tjoin1.rnum, tjoin1.c1, tjoin1.c2 from tjoin1 where c1 in ( select c1 from tjoin1) select tjoin1.rnum, tjoin1.c1, tjoin1.c2 from tjoin1 where c1 in ( select tjoin1.c1 from tjoin1) select tjoin1.rnum, tjoin1.c1, tjoin1.c2 from tjoin1 where c1 in ( select x.c1 from tjoin1 x) -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-9735) aggregate ( smalllint ) fails when ORC file used ava.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Short
N Campbell created HIVE-9735: Summary: aggregate ( smalllint ) fails when ORC file used ava.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Short Key: HIVE-9735 URL: https://issues.apache.org/jira/browse/HIVE-9735 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 0.14.0 Reporter: N Campbell select min( tsint.csint ) from tsint select max( tsint.csint ) from tsint ava.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Short select min( t_tsint.csint ) from t_tsint create table if not exists T_TSINT ( RNUM int , CSINT smallint ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS textfile ; create table if not exists TSINT ( RNUM int , CSINT smallint ) TERMINATED BY '\n' STORED AS orc ; input data loaded into text file and then inserted into ORC table from text based table 0|\N 1|-1 2|0 3|1 4|10 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Reopened] (HIVE-9537) string expressions on a fixed length character do not preserve trailing spaces
[
https://issues.apache.org/jira/browse/HIVE-9537?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
N Campbell reopened HIVE-9537:
--
The Hive documentation is vague at best with respect to when padding is
preserved/ignored:
Char types are similar to Varchar but they are fixed-length meaning that
values shorter than the specified length value are padded with spaces but
trailing spaces are not important during comparisons. The maximum length is
fixed at 255.
There is no discussion on non-comparison operations such as upper, lower,
concat etc.
Consider the following, the driver may return CCHAR will trailing blanks but a
string operation such as concat fails to preserve them. Should an application
locally perform a scalar operation on the returned value such as LEN, LOWER etc
then it may retain the spaces. Meanwhile server side an 'equivalent' expression
is not blank preserving.
select rnum, cchar, concat( concat( concat( cchar,'...'), cchar),'...') from
tchar.
So the driver will return BBspaces and then BB...BB... for the 2nd and 3rd
projected item. Similarly length(cchar) returns 2 and not 5 etc.
Customers using technologies such as Hana, DB2, Netezza, ... will expect the
blank padded behaviour. To all intents and purposes most SQL persons would not
consider the implementation to be fixed length character.
i.e length(cchar) returns 32
i.e cchar || '...' . returns 'BB ...BB
...'
Should this be the design intent of Hive I would ask for the documentation to
be far more comprehensive is stating the semantics.
string expressions on a fixed length character do not preserve trailing spaces
--
Key: HIVE-9537
URL: https://issues.apache.org/jira/browse/HIVE-9537
Project: Hive
Issue Type: Bug
Components: SQL
Reporter: N Campbell
Assignee: Aihua Xu
When a string expression such as upper or lower is applied to a fixed length
column the trailing spaces of the fixed length character are not preserved.
{code:sql}
CREATE TABLE if not exists TCHAR (
RNUM int,
CCHAR char(32)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
{code}
{{cchar}} as a {{char(32)}}.
{code:sql}
select cchar, concat(cchar, cchar), concat(lower(cchar), cchar),
concat(upper(cchar), cchar)
from tchar;
{code}
0|\N
1|
2|
3|BB
4|EE
5|FF
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (HIVE-9598) java.lang.IllegalMonitorStateException/java.util.concurrent.locks.ReentrantLock$Sync.tryRelease if ResultSet.closed called after Statement.close called
N Campbell created HIVE-9598:
Summary:
java.lang.IllegalMonitorStateException/java.util.concurrent.locks.ReentrantLock$Sync.tryRelease
if ResultSet.closed called after Statement.close called
Key: HIVE-9598
URL: https://issues.apache.org/jira/browse/HIVE-9598
Project: Hive
Issue Type: Bug
Components: JDBC
Affects Versions: 0.13.0
Reporter: N Campbell
http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#close()
http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#close()
Statement stmt;
try {
stmt = dbConnection.createStatement();
stmt.executeQuery(select* from t);
ResultSet rs = stmt.getResultSet();
stmt.close();
if (rs != null) {
System.out.println(IS NOT NULL);
// Hive does not implement isClosed()
// if (!rs.isClosed()) {
// System.out.println(IS NOT CLOSED);
// }
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Exception in thread main java.lang.IllegalMonitorStateException
at
java.util.concurrent.locks.ReentrantLock$Sync.tryRelease(ReentrantLock.java:166)
at
java.util.concurrent.locks.AbstractQueuedSynchronizer.release(AbstractQueuedSynchronizer.java:1271)
at
java.util.concurrent.locks.ReentrantLock.unlock(ReentrantLock.java:471)
at
org.apache.hive.jdbc.HiveStatement.closeClientOperation(HiveStatement.java:175)
at
org.apache.hive.jdbc.HiveQueryResultSet.close(HiveQueryResultSet.java:293)
/D:/JDBC/Hortonworks_Hive13/commons-configuration-1.6.jar
/D:/JDBC/Hortonworks_Hive13/commons-logging-1.1.3.jar
/D:/JDBC/Hortonworks_Hive13/hadoop-common-2.4.0.2.1.1.0-385.jar
/D:/JDBC/Hortonworks_Hive13/hive-exec-0.13.0.2.1.1.0-385.jar
/D:/JDBC/Hortonworks_Hive13/hive-jdbc-0.13.0.2.1.1.0-385.jar
/D:/JDBC/Hortonworks_Hive13/hive-service-0.13.0.2.1.1.0-385.jar
/D:/JDBC/Hortonworks_Hive13/httpclient-4.2.5.jar
/D:/JDBC/Hortonworks_Hive13/httpcore-4.2.5.jar
/D:/JDBC/Hortonworks_Hive13/libfb303-0.9.0.jar
/D:/JDBC/Hortonworks_Hive13/libthrift-0.9.0.jar
/D:/JDBC/Hortonworks_Hive13/log4j-1.2.16.jar
/D:/JDBC/Hortonworks_Hive13/slf4j-api-1.7.5.jar
/D:/JDBC/Hortonworks_Hive13/slf4j-log4j12-1.7.5.jar
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (HIVE-9535) add support for NULL ORDER specification in windowed aggregates
N Campbell created HIVE-9535: Summary: add support for NULL ORDER specification in windowed aggregates Key: HIVE-9535 URL: https://issues.apache.org/jira/browse/HIVE-9535 Project: Hive Issue Type: Improvement Components: SQL Reporter: N Campbell Cannot explicitly state how nulls order in windowed aggregates. select rnum, c1, c2, c3, rank() over(partition by c1,c2 order by c3 desc nulls last), rank() over(partition by c1,c2 order by c3 desc nulls first) from tolap while faking with an expression may simulate the intent supporting the ISO standard would be preferred. select rnum, c1, c2, c3, rank() over(partition by c1,c2 order by c3 desc nulls last), rank() over(partition by c1,c2 order by c3 desc nulls first) from tolap -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-9530) constant * column is null interpreted as constant * boolean
N Campbell created HIVE-9530: Summary: constant * column is null interpreted as constant * boolean Key: HIVE-9530 URL: https://issues.apache.org/jira/browse/HIVE-9530 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 0.14.0 Reporter: N Campbell Priority: Minor select c1 from tversion where 1 * cnnull is null FAILED: SemanticException [Error 10014]: Line 1:30 Wrong arguments 'cnnull': No matching method for class org.apache.hadoop.hive.ql.udf.generic.GenericUDFOPMultiply with (int, boolean) create table if not exists TVERSION (RNUM int , C1 int, CVER char(6), CNNULL int, CCNULL char(1)) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS TEXTFILE ; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-9532) add support for quantified predicates
N Campbell created HIVE-9532: Summary: add support for quantified predicates Key: HIVE-9532 URL: https://issues.apache.org/jira/browse/HIVE-9532 Project: Hive Issue Type: Improvement Components: SQL Reporter: N Campbell allow a quantified predicate using ALL/ANY/SOME select rnum, c1, c2 from tjoin2 where 20 some ( select c1 from tjoin1) Error while compiling statement: FAILED: ParseException line 1:50 cannot recognize input near 'select' 'c1' 'from' in function specification -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-9531) remove requirement that EXISTS subquery must be correlated
N Campbell created HIVE-9531: Summary: remove requirement that EXISTS subquery must be correlated Key: HIVE-9531 URL: https://issues.apache.org/jira/browse/HIVE-9531 Project: Hive Issue Type: Improvement Components: SQL Reporter: N Campbell lift the documented restriction that a exists/not exists must involve a correlated sub-query SQL: select rnum, c1, c2 from tjoin2 where exists ( select c1 from tjoin1) FAILED: SemanticException Line 1:62 Invalid SubQuery expression 'c1' in definition of SubQuery sq_1 [ exists ( select c1 from tjoin1) ] used as sq_1 at Line 1:38: For Exists/Not Exists operator SubQuery must be Correlated. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-9534) incorrect result set for query that projects a windowed aggregate
N Campbell created HIVE-9534: Summary: incorrect result set for query that projects a windowed aggregate Key: HIVE-9534 URL: https://issues.apache.org/jira/browse/HIVE-9534 Project: Hive Issue Type: Bug Components: SQL Reporter: N Campbell Result set returned by Hive has one row instead of 5 select avg( distinct tsint.csint ) over () from tsint create table if not exists TSINT ( RNUM int , CSINT smallint ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS TEXTFILE ; 0|\N 1|-1 2|0 3|1 4|10 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-9536) precision of a string concatenation becomes huge precision
N Campbell created HIVE-9536:
Summary: precision of a string concatenation becomes huge precision
Key: HIVE-9536
URL: https://issues.apache.org/jira/browse/HIVE-9536
Project: Hive
Issue Type: Bug
Components: SQL
Reporter: N Campbell
where CCHAR is CHAR(32) and CVCHAR is VARCHAR(32)
select concat ( cchar, cchar ) from t returns a result set where the precision
of the projected column is 64
meanwhile the following becomes 2147483647.
select concat('1234567890' , cchar) from t
meanwhile the following returns 64
select concat(lower(cchar), cchar) from t
create table if not exists TCHAR ( RNUM int , CCHAR char(32 ))
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'
STORED AS TEXTFILE ;
create table if not exists TVCHAR ( RNUM int , CVCHAR varchar(32 ))
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'
STORED AS TEXTFILE ;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (HIVE-9537) string expressions on a fixed length character do not preserve trailing spaces
N Campbell created HIVE-9537: Summary: string expressions on a fixed length character do not preserve trailing spaces Key: HIVE-9537 URL: https://issues.apache.org/jira/browse/HIVE-9537 Project: Hive Issue Type: Bug Components: SQL Reporter: N Campbell cchar is char(32). when a string expression such as upper or lower is applied to a fixed length column the trailing spaces of the fixed length character are not preserved. select cchar, concat(cchar, cchar), concat(lower(cchar), cchar), concat(upper(cchar), cchar) from tchar create table if not exists TCHAR ( RNUM int , CCHAR char(32 )) ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' STORED AS TEXTFILE ; 0|\N 1| 2| 3|BB 4|EE 5|FF -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (HIVE-9533) remove restriction that windowed aggregate ORDER BY can only have one key
N Campbell created HIVE-9533: Summary: remove restriction that windowed aggregate ORDER BY can only have one key Key: HIVE-9533 URL: https://issues.apache.org/jira/browse/HIVE-9533 Project: Hive Issue Type: Improvement Reporter: N Campbell current restriction makes the existing support very limited. select rnum, c1, c2, c3, sum( c3 ) over(partition by c1 order by c2 , c3) from tolap Error: Error while compiling statement: FAILED: SemanticException Range based Window Frame can have only 1 Sort Key SQLState: 42000 ErrorCode: 4 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (HIVE-9533) remove restriction that windowed aggregate ORDER BY can only have one key
[ https://issues.apache.org/jira/browse/HIVE-9533?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-9533: - Component/s: SQL remove restriction that windowed aggregate ORDER BY can only have one key - Key: HIVE-9533 URL: https://issues.apache.org/jira/browse/HIVE-9533 Project: Hive Issue Type: Improvement Components: SQL Reporter: N Campbell current restriction makes the existing support very limited. select rnum, c1, c2, c3, sum( c3 ) over(partition by c1 order by c2 , c3) from tolap Error: Error while compiling statement: FAILED: SemanticException Range based Window Frame can have only 1 Sort Key SQLState: 42000 ErrorCode: 4 -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (HIVE-3245) UTF encoded data not displayed correctly by Hive driver
[ https://issues.apache.org/jira/browse/HIVE-3245?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13842258#comment-13842258 ] N Campbell commented on HIVE-3245: -- Re the prior comment. If encoding information has to be specified, it should be expressed via the connection URL as a name-value property and not assuming that a mid-tier application will be started with a collection of -D options. UTF encoded data not displayed correctly by Hive driver --- Key: HIVE-3245 URL: https://issues.apache.org/jira/browse/HIVE-3245 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 0.8.0 Reporter: N Campbell Assignee: Szehon Ho Attachments: ASF.LICENSE.NOT.GRANTED--screenshot-1.jpg, CERT.TLJA.txt various foreign language data (i.e. japanese, thai etc) is loaded into string columns via tab delimited text files. A simple projection of the columns in the table is not displaying the correct data. Exporting the data from Hive and looking at the files implies the data is loaded properly. it appears to be an encoding issue at the driver but unaware of any required URL connection properties re encoding that Hive JDBC requires. create table if not exists CERT.TLJA_JP_E ( RNUM int , C1 string, ORD int) row format delimited fields terminated by '\t' stored as textfile; create table if not exists CERT.TLJA_JP ( RNUM int , C1 string, ORD int) stored as sequencefile; load data local inpath '/home/hadoopadmin/jdbc-cert/CERT/CERT.TLJA_JP.txt' overwrite into table CERT.TLJA_JP_E; insert overwrite table CERT.TLJA_JP select * from CERT.TLJA_JP_E; -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Commented] (HIVE-4662) first_value can't have more than one order by column
[
https://issues.apache.org/jira/browse/HIVE-4662?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13793651#comment-13793651
]
N Campbell commented on HIVE-4662:
--
That is a fairly significant limitation.
first_value can't have more than one order by column
Key: HIVE-4662
URL: https://issues.apache.org/jira/browse/HIVE-4662
Project: Hive
Issue Type: Bug
Components: SQL
Affects Versions: 0.11.0
Reporter: Frans Drijver
In the current implementation of the first_value function, it's not allowed
to have more than one (1) order by column, as so:
{quote}
select distinct
first_value(kastr.DEWNKNR) over ( partition by kastr.DEKTRNR order by
kastr.DETRADT, kastr.DEVPDNR )
from RTAVP_DRKASTR kastr
;
{quote}
Error given:
{quote}
FAILED: SemanticException Range based Window Frame can have only 1 Sort Key
{quote}
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Commented] (HIVE-4662) first_value can't have more than one order by column
[
https://issues.apache.org/jira/browse/HIVE-4662?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13793652#comment-13793652
]
N Campbell commented on HIVE-4662:
--
not just first value
select c1, c2, sum ( c3 ) over ( partition by c1 order by c2, c3 ) from t
first_value can't have more than one order by column
Key: HIVE-4662
URL: https://issues.apache.org/jira/browse/HIVE-4662
Project: Hive
Issue Type: Bug
Components: SQL
Affects Versions: 0.11.0
Reporter: Frans Drijver
In the current implementation of the first_value function, it's not allowed
to have more than one (1) order by column, as so:
{quote}
select distinct
first_value(kastr.DEWNKNR) over ( partition by kastr.DEKTRNR order by
kastr.DETRADT, kastr.DEVPDNR )
from RTAVP_DRKASTR kastr
;
{quote}
Error given:
{quote}
FAILED: SemanticException Range based Window Frame can have only 1 Sort Key
{quote}
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Commented] (HIVE-3745) Hive does improper = based string comparisons for strings with trailing whitespaces
[
https://issues.apache.org/jira/browse/HIVE-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13793658#comment-13793658
]
N Campbell commented on HIVE-3745:
--
The ISO-SQL standard is very clear as to what a vendor may choose to do re
blank padding semantics. Similarly, how operations such as min, max, distinct
etc operate on a variable length character type. Persons simply comparing to
another RDBMS need to compare what ISO states, where it allows 'vendor
implementation' and to see what a given vendor claims.
For example, if you were to use Postgres and other vendors derived from it. You
will find various differences with respect to
length( char (n) ) vs varchar(n)
group by
min
distinct/union
To some persons trailing spaces are of no interest and they may assume that one
general string type will 'ignore' spaces. Others may state to their business
application that trailing spaces are significant. That is distinct from what a
given standard states or perhaps what a vendor chooses to implement
irrespective of any given standard.
It would help either way if the Hive QL documentation could be improved to
state intent of a given construct/feature.
Hive does improper = based string comparisons for strings with trailing
whitespaces
-
Key: HIVE-3745
URL: https://issues.apache.org/jira/browse/HIVE-3745
Project: Hive
Issue Type: Bug
Components: SQL
Affects Versions: 0.9.0
Reporter: Harsh J
Assignee: Kevin Wilfong
Compared to other systems such as DB2, MySQL, etc., which disregard trailing
whitespaces in a string used when comparing two strings with the {{=}}
relational operator, Hive does not do this.
For example, note the following line from the MySQL manual:
http://dev.mysql.com/doc/refman/5.1/en/char.html
{quote}
All MySQL collations are of type PADSPACE. This means that all CHAR and
VARCHAR values in MySQL are compared without regard to any trailing spaces.
{quote}
Hive still is whitespace sensitive and regards trailing spaces of a string as
worthy elements when comparing. Ideally {{LIKE}} should consider this
strongly, but {{=}} should not.
Is there a specific reason behind this difference of implementation in Hive's
SQL?
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Created] (HIVE-5529) precision and scale for DECIMAL type p=2147483647 s=2147483647
N Campbell created HIVE-5529: Summary: precision and scale for DECIMAL type p=2147483647 s=2147483647 Key: HIVE-5529 URL: https://issues.apache.org/jira/browse/HIVE-5529 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 0.11.0 Reporter: N Campbell Priority: Critical If you declare a column of type DECIMAL you cannot define a precision and scale (per ISO-SQL). Should you attempt to describe a column in a resulset etc which is a decimal type it reports p=2147483647 s=2147483647. -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Commented] (HIVE-3173) implement getTypeInfo database metadata method
[ https://issues.apache.org/jira/browse/HIVE-3173?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13793491#comment-13793491 ] N Campbell commented on HIVE-3173: -- Hive 0.11 fails to define prefix and suffix information etc. TYPE_NAME DATA_TYPE PRECISION LITERAL_PREFIX LITERAL_SUFFIX CREATE_PARAMS NULLABLECASE_SENSITIVE SEARCHABLE UNSIGNED_ATTRIBUTE FIXED_PREC_SCALEAUTO_INCREMENT LOCAL_TYPE_NAME MINIMUM_SCALE MAXIMUM_SCALE SQL_DATA_TYPE SQL_DATETIME_SUB NUM_PREC_RADIX ARRAY 12 [VARCHAR]null null null null truefalse no support truefalse false null 0 0 null null null BIGINT -5 [BIGINT] 19 null null null truefalse supports all WHERE false false false null 0 0 null null 10 BINARY -2 [BINARY] null null null null truefalse supports all WHERE truefalse false null 0 0 null null null BOOLEAN 16 [BOOLEAN]null null null null truefalse supports all WHERE truefalse false null 0 0 null null null DECIMAL 3 [DECIMAL] null null null null truefalse supports all WHERE false false false null 0 0 null null null DOUBLE 8 [DOUBLE] 15 null null null truefalse supports all WHERE false false false null 0 0 null null 2 FLOAT 6 [FLOAT] 7 null null null truefalse supports all WHERE false false false null 0 0 null null 2 INT 4 [INTEGER] 10 null null null truefalse supports all WHERE false false false null 0 0 null null 10 MAP 12 [VARCHAR]null null null null truefalse no support truefalse false null 0 0 null null null SMALLINT5 [SMALLINT]5 null null null truefalse supports all WHERE false false false null 0 0 null null 10 STRING 12 [VARCHAR]null null null null truetrue supports all WHERE truefalse false null 0 0 null null null STRUCT 12 [VARCHAR]null null null null truefalse no support truefalse false null 0 0 null null null TIMESTAMP 93 [TIMESTAMP] null null null null truefalse supports all WHERE truefalse false null 0 0 null null null TINYINT -6 [TINYINT]3 null null null truefalse supports all WHERE false false false null 0 0 null null 10 UNIONTYPE 12 [VARCHAR]null null null null truefalse no support truefalse false null 0 0 null null null null 12 [VARCHAR]null null null null truefalse no support truefalse false null 0 0 null null null implement getTypeInfo database metadata method --- Key: HIVE-3173 URL: https://issues.apache.org/jira/browse/HIVE-3173 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 0.8.1 Reporter: N Campbell Attachments: Hive-3173.patch.txt The JDBC driver does not implement the database metadata method getTypeInfo. Hence, an application cannot dynamically determine the available type information and associated properties. -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5530) null pointer exception when case returns null
N Campbell created HIVE-5530: Summary: null pointer exception when case returns null Key: HIVE-5530 URL: https://issues.apache.org/jira/browse/HIVE-5530 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor The following expression will cause an NPE select case when 1 = 1 then null end from t -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Commented] (HIVE-5473) sqrt of -ve value returns null instead of throwing an error
[ https://issues.apache.org/jira/browse/HIVE-5473?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13788638#comment-13788638 ] N Campbell commented on HIVE-5473: -- other cases are select 0 / 0 from tversion select sqrt ( -4 ) from tversion select power( 0, -1) from tversion note, in ISO these should be exceptions in the first case you get a garbage value lack and infinity in the others so even if design intent in hive the docs make no statement etc. sqrt of -ve value returns null instead of throwing an error --- Key: HIVE-5473 URL: https://issues.apache.org/jira/browse/HIVE-5473 Project: Hive Issue Type: Bug Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor select sqrt( -4 ) from t will return a null instead of throwing an exception. no discussion on web page that this would be by design to not throw an error. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5460) invalid offsets in lag lead should return an exception (per ISO-SQL)
N Campbell created HIVE-5460: Summary: invalid offsets in lag lead should return an exception (per ISO-SQL) Key: HIVE-5460 URL: https://issues.apache.org/jira/browse/HIVE-5460 Project: Hive Issue Type: Bug Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor ISO-SQL 2012 defines how lag and lead should behave when invalid offsets are provided to the functions. i.e. select tint.rnum,tint.cint, lag( tint.cint, -100 ) over ( order by tint.rnum) from tint tint Instead of a meaningful error (as other vendors will emit) you get Error: Query returned non-zero code: 2, cause: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask SQLState: 08S01 ErrorCode: 2 -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5461) lag lead do not offer ISO-SQL 2012 null treatment
N Campbell created HIVE-5461: Summary: lag lead do not offer ISO-SQL 2012 null treatment Key: HIVE-5461 URL: https://issues.apache.org/jira/browse/HIVE-5461 Project: Hive Issue Type: Improvement Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor ISO-SQL lag and lead provide clauses that allow the author to denote how nulls are treated (respect or ignore nulls). Hive does not. -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5462) improve error when an approximate numeric literal provided to lag lead
N Campbell created HIVE-5462: Summary: improve error when an approximate numeric literal provided to lag lead Key: HIVE-5462 URL: https://issues.apache.org/jira/browse/HIVE-5462 Project: Hive Issue Type: Bug Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor ISO SQL would define this as an error. select vint.rnum,vint.cint, lead( vint.cint, 1.70 ) over ( order by vint.rnum) from vint vint The issue is the error message returned by Hive Query returned non-zero code: 4, cause: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. At least 1 group must only depend on input columns. Also check for circular dependencies. Underlying error: org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException: Lead amount must be a integer value double was passed as parameter 1. -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5463) support hypothetical ranking aggregates
N Campbell created HIVE-5463: Summary: support hypothetical ranking aggregates Key: HIVE-5463 URL: https://issues.apache.org/jira/browse/HIVE-5463 Project: Hive Issue Type: Improvement Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor existing function support does not include forms as select rank ( 5 ) within group ( order by vsint.csint ) from vsint vsint -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5464) allow OR conditions in table join
N Campbell created HIVE-5464: Summary: allow OR conditions in table join Key: HIVE-5464 URL: https://issues.apache.org/jira/browse/HIVE-5464 Project: Hive Issue Type: Improvement Affects Versions: 0.11.0 Reporter: N Campbell select tjoin1.c1, tjoin1.c2, tjoin2.c2 as c2j2 from tjoin1 tjoin1 inner join tjoin2 tjoin2 on ( tjoin1.c1 = 10 or tjoin1.c1=20 ) Query returned non-zero code: 10019, cause: FAILED: SemanticException [Error 10019]: Line 1:96 OR not supported in JOIN currently '20' -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5465) unable to express nested join
N Campbell created HIVE-5465: Summary: unable to express nested join Key: HIVE-5465 URL: https://issues.apache.org/jira/browse/HIVE-5465 Project: Hive Issue Type: Bug Affects Versions: 0.11.0 Reporter: N Campbell select tjoin2.rnum, tjoin1.c1, tjoin1.c2, tjoin2.c2 as c2j2, tjoin3.c2 as c2j3,tjoin4.c2 as c2j4 from (tjoin1 tjoin1 right outer join (tjoin2 tjoin2 left outer join tjoin3 tjoin3 on tjoin2.c1=tjoin3.c1) on (tjoin1.c1=tjoin2.c1)) left outer join tjoin4 tjoin4 on (tjoin1.c1=tjoin4.c1) Query returned non-zero code: 4, cause: FAILED: ParseException line 1:104 cannot recognize input near 'tjoin1' 'tjoin1' 'right' in subquery source -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5466) add support for table and row constructors in a statement
N Campbell created HIVE-5466: Summary: add support for table and row constructors in a statement Key: HIVE-5466 URL: https://issues.apache.org/jira/browse/HIVE-5466 Project: Hive Issue Type: Improvement Reporter: N Campbell Priority: Minor Various filtering requirements can be expressed and performed more efficiently when row and table constructors can be pushed into a statement where (c1, c2 ) .. ( z1, z2) select t1.c1, t1.c2 from (values (10,'BB')) t1(c1,c2) etc -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Commented] (HIVE-5460) invalid offsets in lag lead should return an exception (per ISO-SQL)
[ https://issues.apache.org/jira/browse/HIVE-5460?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13787673#comment-13787673 ] N Campbell commented on HIVE-5460: -- You would need to go to the ISO web site http://www.iso.org/iso/home/store/catalogue_ics/catalogue_detail_ics.htm?csnumber=53681 invalid offsets in lag lead should return an exception (per ISO-SQL) - Key: HIVE-5460 URL: https://issues.apache.org/jira/browse/HIVE-5460 Project: Hive Issue Type: Bug Affects Versions: 0.11.0 Reporter: N Campbell Assignee: Edward Capriolo Priority: Minor ISO-SQL 2012 defines how lag and lead should behave when invalid offsets are provided to the functions. i.e. select tint.rnum,tint.cint, lag( tint.cint, -100 ) over ( order by tint.rnum) from tint tint Instead of a meaningful error (as other vendors will emit) you get Error: Query returned non-zero code: 2, cause: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask SQLState: 08S01 ErrorCode: 2 -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Updated] (HIVE-5460) invalid offsets in lag lead should return an exception (per ISO-SQL)
[ https://issues.apache.org/jira/browse/HIVE-5460?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-5460: - Description: ISO-SQL 2011 defines how lag and lead should behave when invalid offsets are provided to the functions. i.e. select tint.rnum,tint.cint, lag( tint.cint, -100 ) over ( order by tint.rnum) from tint tint Instead of a meaningful error (as other vendors will emit) you get Error: Query returned non-zero code: 2, cause: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask SQLState: 08S01 ErrorCode: 2 was: ISO-SQL 2012 defines how lag and lead should behave when invalid offsets are provided to the functions. i.e. select tint.rnum,tint.cint, lag( tint.cint, -100 ) over ( order by tint.rnum) from tint tint Instead of a meaningful error (as other vendors will emit) you get Error: Query returned non-zero code: 2, cause: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask SQLState: 08S01 ErrorCode: 2 invalid offsets in lag lead should return an exception (per ISO-SQL) - Key: HIVE-5460 URL: https://issues.apache.org/jira/browse/HIVE-5460 Project: Hive Issue Type: Bug Affects Versions: 0.11.0 Reporter: N Campbell Assignee: Edward Capriolo Priority: Minor ISO-SQL 2011 defines how lag and lead should behave when invalid offsets are provided to the functions. i.e. select tint.rnum,tint.cint, lag( tint.cint, -100 ) over ( order by tint.rnum) from tint tint Instead of a meaningful error (as other vendors will emit) you get Error: Query returned non-zero code: 2, cause: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask SQLState: 08S01 ErrorCode: 2 -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5467) allow null ordering specification in a windowed aggregate
N Campbell created HIVE-5467: Summary: allow null ordering specification in a windowed aggregate Key: HIVE-5467 URL: https://issues.apache.org/jira/browse/HIVE-5467 Project: Hive Issue Type: Improvement Affects Versions: 0.11.0 Reporter: N Campbell A business user may require that nulls are sorted first with respect to an analytical expression as opposed to last. Hive does not support the null ordering specification. select rnum, c1, c2, c3, rank() over(order c1 desc nulls first ) from tolap order by rnum Error: Query returned non-zero code: 4, cause: FAILED: ParseException line 1:37 cannot recognize input near 'order' 'c1' 'desc' in order by clause You can fake is by ordering on a case expression select rnum, c1, c2, c3, rank() over(order by case when c1 is null then 0 else 1 end, c1 desc ) from tolap order by rnum -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5468) allow null ordering to be defined on cursor specification
N Campbell created HIVE-5468: Summary: allow null ordering to be defined on cursor specification Key: HIVE-5468 URL: https://issues.apache.org/jira/browse/HIVE-5468 Project: Hive Issue Type: Improvement Affects Versions: 0.11.0 Reporter: N Campbell Similar comment to HIVE-5467 select rnum, c1, c2 from tset1 tset1 order by c1 nulls first, c2 nulls last -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5469) support nullif
N Campbell created HIVE-5469: Summary: support nullif Key: HIVE-5469 URL: https://issues.apache.org/jira/browse/HIVE-5469 Project: Hive Issue Type: Improvement Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor Have to express case expression to work around lack of NULLIF select nullif(cint, 1) from tint select cint, case when cint = 1 then null else cint end from tint -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Updated] (HIVE-3194) implement the JDBC canonical/ISO-SQL 2011 scalar functions
[ https://issues.apache.org/jira/browse/HIVE-3194?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3194: - Summary: implement the JDBC canonical/ISO-SQL 2011 scalar functions (was: implement the JDBC canonical/ISO-SQL 2012 scalar functions) implement the JDBC canonical/ISO-SQL 2011 scalar functions -- Key: HIVE-3194 URL: https://issues.apache.org/jira/browse/HIVE-3194 Project: Hive Issue Type: Improvement Components: JDBC, SQL, UDF Affects Versions: 0.8.0, 0.11.0 Reporter: N Campbell Attachments: Hive-3194.patch The Hive driver does not return any canonical JDBC scalar functions (per JDBC 3.0/4.0). Similarly, while Hive has various 'similar' scalar functions their signature (name etc) is not the same as ISO-SQL equivalent. It would be better for portable dynamic SQL applications to see more alignment by Hive etc to both standards. In some cases it is small syntactic changes/tweaks (i.e. see mod, substring etc). -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Updated] (HIVE-5461) lag lead do not offer ISO-SQL 2011 null treatment
[ https://issues.apache.org/jira/browse/HIVE-5461?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-5461: - Summary: lag lead do not offer ISO-SQL 2011 null treatment (was: lag lead do not offer ISO-SQL 2012 null treatment ) lag lead do not offer ISO-SQL 2011 null treatment -- Key: HIVE-5461 URL: https://issues.apache.org/jira/browse/HIVE-5461 Project: Hive Issue Type: Improvement Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor ISO-SQL lag and lead provide clauses that allow the author to denote how nulls are treated (respect or ignore nulls). Hive does not. -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Updated] (HIVE-3188) allow cast to use double precision per ISO-SQL 2011
[
https://issues.apache.org/jira/browse/HIVE-3188?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
N Campbell updated HIVE-3188:
-
Summary: allow cast to use double precision per ISO-SQL 2011 (was: allow
cast to use double precision per ISO-SQL 2012)
allow cast to use double precision per ISO-SQL 2011
---
Key: HIVE-3188
URL: https://issues.apache.org/jira/browse/HIVE-3188
Project: Hive
Issue Type: Improvement
Affects Versions: 0.8.0
Reporter: N Campbell
While ISO-SQL allows cast('10.3' as double precision) Hive forces you to say
cast('10.3' as double ). Accept the additional keyword.
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Updated] (HIVE-3190) allow INTEGER as a type name in a column/cast expression (per ISO-SQL 2011)
[
https://issues.apache.org/jira/browse/HIVE-3190?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
N Campbell updated HIVE-3190:
-
Summary: allow INTEGER as a type name in a column/cast expression (per
ISO-SQL 2011) (was: allow INTEGER as a type name in a column/cast expression
(per ISO-SQL 2012))
allow INTEGER as a type name in a column/cast expression (per ISO-SQL 2011)
---
Key: HIVE-3190
URL: https://issues.apache.org/jira/browse/HIVE-3190
Project: Hive
Issue Type: Improvement
Components: SQL
Affects Versions: 0.8.0
Reporter: N Campbell
Just extend the parser to allow INTEGER instead of making folks use INT
select cast('10' as integer) from cert.tversion tversion
FAILED: Parse Error: line 1:20 cannot recognize input near 'integer' ')'
'from' in primitive type specification
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Updated] (HIVE-3184) remove existing restriction that union must be defined within a derived table (follow ISO-SQL 2011)
[ https://issues.apache.org/jira/browse/HIVE-3184?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3184: - Summary: remove existing restriction that union must be defined within a derived table (follow ISO-SQL 2011) (was: remove existing restriction that union must be defined within a derived table (follow ISO-SQL 2012)) remove existing restriction that union must be defined within a derived table (follow ISO-SQL 2011) --- Key: HIVE-3184 URL: https://issues.apache.org/jira/browse/HIVE-3184 Project: Hive Issue Type: Improvement Components: SQL Affects Versions: 0.8.0 Reporter: N Campbell Don't make SQL applications have to wrap the union operation within a derived table per the SQL standard. Failing to do so results in Top level UNION is not supported currently; use a subquery i.e. select c1, c2 from cert.tset1 tset1 union all select c1, c2 from cert.tset2 tset2 vs select * from ( select c1, c2 from cert.tset1 tset1 union all select c1, c2 from cert.tset2 tset2 ) T -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Updated] (HIVE-3185) allow union set operation without ALL keyword (align to ISO-SQL 2011 specification)
[ https://issues.apache.org/jira/browse/HIVE-3185?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3185: - Summary: allow union set operation without ALL keyword (align to ISO-SQL 2011 specification) (was: allow union set operation without ALL keyword (align to ISO-SQL 2012 specification)) allow union set operation without ALL keyword (align to ISO-SQL 2011 specification) --- Key: HIVE-3185 URL: https://issues.apache.org/jira/browse/HIVE-3185 Project: Hive Issue Type: Improvement Components: SQL Affects Versions: 0.8.0 Reporter: N Campbell Allow a union operation to be specified as qb1 union qb2 in addition to th qb1 union all qb2 Common requirement of queries to project two sets where it is not always the case you want distinct rows in the set. Failing to do so obviously will result in the current exception: mismatched input 'select' expecting ALL near 'union' in query operator -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Updated] (HIVE-3186) support having without a grouping operation (per ISO-SQL 2011)
[ https://issues.apache.org/jira/browse/HIVE-3186?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3186: - Summary: support having without a grouping operation (per ISO-SQL 2011) (was: support having without a grouping operation (per ISO-SQL 2012)) support having without a grouping operation (per ISO-SQL 2011) -- Key: HIVE-3186 URL: https://issues.apache.org/jira/browse/HIVE-3186 Project: Hive Issue Type: Improvement Components: SQL Affects Versions: 0.8.0 Reporter: N Campbell select count(*) from cert.tset1 tset1 having count(*) 2 granted you can manually write a grouped derived table and filter etc but it valid to define a having statement (implicit grouped table). HAVING specified without GROUP BY -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5470) support extract function
N Campbell created HIVE-5470: Summary: support extract function Key: HIVE-5470 URL: https://issues.apache.org/jira/browse/HIVE-5470 Project: Hive Issue Type: Improvement Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor You have the equivalent scalar functions but do not offer ISO-SQL extract which should be a small enhancement. Note, the second() scalar does not return the fractional seconds in a timestamp with ISO-SQL would expect extract (second) to implement. select extract (year from cts), vs select rnum, year (cts), month( cts), day( cts), hour(cts), minute (cts), second( cts), cts from tts -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5471) allow timestamp literal per ISO-SQL
N Campbell created HIVE-5471:
Summary: allow timestamp literal per ISO-SQL
Key: HIVE-5471
URL: https://issues.apache.org/jira/browse/HIVE-5471
Project: Hive
Issue Type: Improvement
Affects Versions: 0.11.0
Reporter: N Campbell
Support the ISO-SQL timestamp literal specification
select timestamp '1999-01-01 12:23.30.01' from tversion
instead of having to use
select cast('1999-01-01 12:23:30.01' as timestamp) from tversion
--
This message was sent by Atlassian JIRA
(v6.1#6144)
[jira] [Created] (HIVE-5472) support a simple scalar which returns the current timestamp
N Campbell created HIVE-5472: Summary: support a simple scalar which returns the current timestamp Key: HIVE-5472 URL: https://issues.apache.org/jira/browse/HIVE-5472 Project: Hive Issue Type: Improvement Affects Versions: 0.11.0 Reporter: N Campbell ISO-SQL has two forms of functions local and current timestamp where the former is a TIMESTAMP WITHOUT TIMEZONE and the latter with TIME ZONE select cast ( unix_timestamp() as timestamp ) from T implement a function which computes LOCAL TIMESTAMP which would be the current timestamp for the users session time zone. -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Commented] (HIVE-3183) case expression should allow different types per ISO-SQL 2011
[ https://issues.apache.org/jira/browse/HIVE-3183?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13787715#comment-13787715 ] N Campbell commented on HIVE-3183: -- I am not planning to commit code changes to Apache. I'll leave that to Cloudera, Hortonworks and others who are incrementally improving Hive-QL :-) The intent is to enable others in the public domain to understand the bugs and opportunities that exist re Hive-QL types and SQL support distinct from working within the the supplied grammar or hand writing work arounds etc case expression should allow different types per ISO-SQL 2011 - Key: HIVE-3183 URL: https://issues.apache.org/jira/browse/HIVE-3183 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 0.8.0 Reporter: N Campbell Attachments: Hive-3183.patch.txt, udf_when_type_wrong2.q.out, udf_when_type_wrong3.q.out The ISO-SQL standard specification for CASE allows the specification to include different types in the WHEN and ELSE blocks including this example which mixes smallint and integer types select case when vsint.csint is not null then vsint.csint else 1 end from cert.vsint vsint The Apache Hive docs do not state how it deviates from the standard or any given restrictions so unsure if this is a bug vs an enhancement. Many SQL applications mix so this seems to be a restrictive implementation if this is by design. Argument type mismatch '1': The expression after ELSE should have the same type as those after THEN: smallint is expected but int is found -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Created] (HIVE-5473) sqrt of -ve value returns null instead of throwing an error
N Campbell created HIVE-5473: Summary: sqrt of -ve value returns null instead of throwing an error Key: HIVE-5473 URL: https://issues.apache.org/jira/browse/HIVE-5473 Project: Hive Issue Type: Bug Affects Versions: 0.11.0 Reporter: N Campbell Priority: Minor select sqrt( -4 ) from t will return a null instead of throwing an exception. no discussion on web page that this would be by design to not throw an error. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions -- This message was sent by Atlassian JIRA (v6.1#6144)
[jira] [Commented] (HIVE-3121) JDBC driver's getCatalogs() method returns schema/db information
[ https://issues.apache.org/jira/browse/HIVE-3121?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13699651#comment-13699651 ] N Campbell commented on HIVE-3121: -- does this mean that getTables will work correctly now when NULL is provided for the catalog and schema parameters? we found in 0.8, 0.9 and 0.10 etc that it is not default context for catalog/schema and returns nothing. an application is forced to work around this by parsing the connection URL and stripping any db info provided or a default name. JDBC driver's getCatalogs() method returns schema/db information Key: HIVE-3121 URL: https://issues.apache.org/jira/browse/HIVE-3121 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 0.9.0 Reporter: Carl Steinbach Assignee: Richard Ding Attachments: hive-3121_1.patch, hive-3121.patch -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-4573) JDBC Compliance getTables
[
https://issues.apache.org/jira/browse/HIVE-4573?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13699670#comment-13699670
]
N Campbell commented on HIVE-4573:
--
while it is true that getTableTypes lists the types which exist, the Hive JDBC
documentation continues to reference you to the standard JDBC pages where those
types have existed since early days of JDBC.
where was the change in types relative to prior releases etc documented re the
change and purpose?
as in ODBC, calling getTableTypes might help an application determine what
types it 'expects' are shown to be supported or 'not'. However, for other types
it becomes a crap shoot as to what a dynamic SQL application would presume to
do re types listed that it is not familiar with.
https://hive.apache.org/docs/r0.10.0/api/org/apache/hadoop/hive/jdbc/HiveDatabaseMetaData.html
and follow the links from getTables().
http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html?is-external=true#getTables(java.lang.String,
java.lang.String, java.lang.String, java.lang.String[])
JDBC Compliance getTables
-
Key: HIVE-4573
URL: https://issues.apache.org/jira/browse/HIVE-4573
Project: Hive
Issue Type: Bug
Components: JDBC
Affects Versions: 0.10.0
Reporter: Johndee Burks
Priority: Minor
The getTables jdbc function no longer returns information when using normal
JDBC table types like TABLE or VIEW. You must now use a more specific type
such as MANAGED_TABLE or VIRTUAL_VIEW. An example application that will fail
to return results against 0.10 is below, works without issue in 0.9. In my
0.10 test I used HS2.
{code}
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
import org.apache.hive.jdbc.HiveDriver;
import java.sql.DatabaseMetaData;
public class TestGet {
private static String driverName = org.apache.hive.jdbc.HiveDriver;
/**
* @param args
* @throws SQLException
*/
public static void main(String[] args) throws SQLException {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.exit(1);
}
Connection con =
DriverManager.getConnection(jdbc:hive2://hostname:1/default);
DatabaseMetaData dbmd = con.getMetaData();
String[] types = {TABLE};
ResultSet rs = dbmd.getTables(null, null, %, types);
while (rs.next()) {
System.out.println(rs.getString(TABLE_NAME));
}
}
}
}
{code}
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-4256) JDBC2 HiveConnection does not use the specified database
[ https://issues.apache.org/jira/browse/HIVE-4256?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13699675#comment-13699675 ] N Campbell commented on HIVE-4256: -- we have seen 'similar' issues with Hive 0.8, 0.9 and 0.10 re metadata methods such as getTables etc failing to work when null is provided as parameter values for schema etc. JDBC2 HiveConnection does not use the specified database Key: HIVE-4256 URL: https://issues.apache.org/jira/browse/HIVE-4256 Project: Hive Issue Type: Bug Components: HiveServer2, JDBC Affects Versions: 0.11.0 Reporter: Chris Drome Assignee: Chris Drome HiveConnection ignores the database specified in the connection string when configuring the connection. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3176) implement returning values for SQLException getSQLState()
[ https://issues.apache.org/jira/browse/HIVE-3176?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13650764#comment-13650764 ] N Campbell commented on HIVE-3176: -- I'd expect the Hive driver to align to the JDBC 3.x or 4.x API specification with respect to exception handling re transient and non-transient exceptions and the associated SQL state codes http://docs.oracle.com/javase/7/docs/api/java/sql/package-summary.html implement returning values for SQLException getSQLState() - Key: HIVE-3176 URL: https://issues.apache.org/jira/browse/HIVE-3176 Project: Hive Issue Type: Improvement Components: JDBC Affects Versions: 0.8.1 Reporter: N Campbell Labels: newbie, patch Attachments: HIVE-3176.patch.txt a dynamic SQL application should be able to check the values returned by getSQLState on a SQLException object. Currently the Hive driver is not doing this (throws exceptions etc). -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3245) UTF encoded data not displayed correctly by Hive driver
N Campbell created HIVE-3245: Summary: UTF encoded data not displayed correctly by Hive driver Key: HIVE-3245 URL: https://issues.apache.org/jira/browse/HIVE-3245 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 0.8.0 Reporter: N Campbell various foreign language data (i.e. japanese, thai etc) is loaded into string columns via tab delimited text files. A simple projection of the columns in the table is not displaying the correct data. Exporting the data from Hive and looking at the files implies the data is loaded properly. it appears to be an encoding issue at the driver but unaware of any required URL connection properties re encoding that Hive JDBC requires -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3245) UTF encoded data not displayed correctly by Hive driver
[ https://issues.apache.org/jira/browse/HIVE-3245?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3245: - Attachment: CERT.TLJA.txt example JP data UTF encoded data not displayed correctly by Hive driver --- Key: HIVE-3245 URL: https://issues.apache.org/jira/browse/HIVE-3245 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 0.8.0 Reporter: N Campbell Attachments: CERT.TLJA.txt various foreign language data (i.e. japanese, thai etc) is loaded into string columns via tab delimited text files. A simple projection of the columns in the table is not displaying the correct data. Exporting the data from Hive and looking at the files implies the data is loaded properly. it appears to be an encoding issue at the driver but unaware of any required URL connection properties re encoding that Hive JDBC requires -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3245) UTF encoded data not displayed correctly by Hive driver
[ https://issues.apache.org/jira/browse/HIVE-3245?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3245: - Description: various foreign language data (i.e. japanese, thai etc) is loaded into string columns via tab delimited text files. A simple projection of the columns in the table is not displaying the correct data. Exporting the data from Hive and looking at the files implies the data is loaded properly. it appears to be an encoding issue at the driver but unaware of any required URL connection properties re encoding that Hive JDBC requires. create table if not exists CERT.TLJA_JP_E ( RNUM int , C1 string, ORD int) row format delimited fields terminated by '\t' stored as textfile; create table if not exists CERT.TLJA_JP ( RNUM int , C1 string, ORD int) stored as sequencefile; load data local inpath '/home/hadoopadmin/jdbc-cert/CERT/CERT.TLJA_JP.txt' overwrite into table CERT.TLJA_JP_E; insert overwrite table CERT.TLJA_JP select * from CERT.TLJA_JP_E; was:various foreign language data (i.e. japanese, thai etc) is loaded into string columns via tab delimited text files. A simple projection of the columns in the table is not displaying the correct data. Exporting the data from Hive and looking at the files implies the data is loaded properly. it appears to be an encoding issue at the driver but unaware of any required URL connection properties re encoding that Hive JDBC requires UTF encoded data not displayed correctly by Hive driver --- Key: HIVE-3245 URL: https://issues.apache.org/jira/browse/HIVE-3245 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 0.8.0 Reporter: N Campbell Attachments: CERT.TLJA.txt various foreign language data (i.e. japanese, thai etc) is loaded into string columns via tab delimited text files. A simple projection of the columns in the table is not displaying the correct data. Exporting the data from Hive and looking at the files implies the data is loaded properly. it appears to be an encoding issue at the driver but unaware of any required URL connection properties re encoding that Hive JDBC requires. create table if not exists CERT.TLJA_JP_E ( RNUM int , C1 string, ORD int) row format delimited fields terminated by '\t' stored as textfile; create table if not exists CERT.TLJA_JP ( RNUM int , C1 string, ORD int) stored as sequencefile; load data local inpath '/home/hadoopadmin/jdbc-cert/CERT/CERT.TLJA_JP.txt' overwrite into table CERT.TLJA_JP_E; insert overwrite table CERT.TLJA_JP select * from CERT.TLJA_JP_E; -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3245) UTF encoded data not displayed correctly by Hive driver
[ https://issues.apache.org/jira/browse/HIVE-3245?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3245: - Attachment: screenshot-1.jpg example of test shown in SQLSquirrel UTF encoded data not displayed correctly by Hive driver --- Key: HIVE-3245 URL: https://issues.apache.org/jira/browse/HIVE-3245 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 0.8.0 Reporter: N Campbell Attachments: CERT.TLJA.txt, screenshot-1.jpg various foreign language data (i.e. japanese, thai etc) is loaded into string columns via tab delimited text files. A simple projection of the columns in the table is not displaying the correct data. Exporting the data from Hive and looking at the files implies the data is loaded properly. it appears to be an encoding issue at the driver but unaware of any required URL connection properties re encoding that Hive JDBC requires. create table if not exists CERT.TLJA_JP_E ( RNUM int , C1 string, ORD int) row format delimited fields terminated by '\t' stored as textfile; create table if not exists CERT.TLJA_JP ( RNUM int , C1 string, ORD int) stored as sequencefile; load data local inpath '/home/hadoopadmin/jdbc-cert/CERT/CERT.TLJA_JP.txt' overwrite into table CERT.TLJA_JP_E; insert overwrite table CERT.TLJA_JP select * from CERT.TLJA_JP_E; -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3239) exception will be thrown for timestamp column in resultset from getColumns metadata method
N Campbell created HIVE-3239:
Summary: exception will be thrown for timestamp column in
resultset from getColumns metadata method
Key: HIVE-3239
URL: https://issues.apache.org/jira/browse/HIVE-3239
Project: Hive
Issue Type: Bug
Reporter: N Campbell
create table cert.tts ( x int, y timestamp )
describe the table column metadata using
rs = meta.getColumns(null, cert, tts, %);
..
while (rs.next()) {
for (int i = 1; i = rsmd.getColumnCount(); i++) {
if (i 1) {
System.out.print(,);
}
System.out.print(String.format((%s), rs.getObject(i)));
.
java.sql.SQLException: Unrecognized column type: timestamp
at org.apache.hadoop.hive.jdbc.Utils.hiveTypeToSqlType(Utils.java:56)
at org.apache.hadoop.hive.jdbc.JdbcColumn.getSqlType(JdbcColumn.java:62)
at
org.apache.hadoop.hive.jdbc.HiveDatabaseMetaData$2.next(HiveDatabaseMetaData.java:244)
at bug.main(bug.java:232)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3239) exception will be thrown for timestamp column in resultset from getColumns metadata method
[
https://issues.apache.org/jira/browse/HIVE-3239?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
N Campbell updated HIVE-3239:
-
Component/s: JDBC
Affects Version/s: 0.8.1
exception will be thrown for timestamp column in resultset from getColumns
metadata method
--
Key: HIVE-3239
URL: https://issues.apache.org/jira/browse/HIVE-3239
Project: Hive
Issue Type: Bug
Components: JDBC
Affects Versions: 0.8.1
Reporter: N Campbell
create table cert.tts ( x int, y timestamp )
describe the table column metadata using
rs = meta.getColumns(null, cert, tts, %);
..
while (rs.next()) {
for (int i = 1; i = rsmd.getColumnCount(); i++) {
if (i 1) {
System.out.print(,);
}
System.out.print(String.format((%s), rs.getObject(i)));
.
java.sql.SQLException: Unrecognized column type: timestamp
at org.apache.hadoop.hive.jdbc.Utils.hiveTypeToSqlType(Utils.java:56)
at org.apache.hadoop.hive.jdbc.JdbcColumn.getSqlType(JdbcColumn.java:62)
at
org.apache.hadoop.hive.jdbc.HiveDatabaseMetaData$2.next(HiveDatabaseMetaData.java:244)
at bug.main(bug.java:232)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-3216) DateDiff UDF overload with a unit parameter
[ https://issues.apache.org/jira/browse/HIVE-3216?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13407002#comment-13407002 ] N Campbell commented on HIVE-3216: -- Note, ISO-SQL does not have DateDiff rather timestamp - timestamp returns an interval type where you can say ( ... ) interval month etc to control what interval type you want. The DateDiff like function if semantically equivalent would be a syntactic short form of the equivalent intent however the return type would be a numeric type and not an interval. DateDiff UDF overload with a unit parameter --- Key: HIVE-3216 URL: https://issues.apache.org/jira/browse/HIVE-3216 Project: Hive Issue Type: Improvement Components: SQL, UDF Affects Versions: 0.10.0, 0.9.1 Reporter: Shefali Vohra Priority: Minor Labels: date, patch, sql, timestamp, udf Fix For: 0.9.1 *Parameters* This function overloads the current DateDiff(expr1, expr2) by adding another parameter to specify the units. It takes 3 parameters. The first two are timestamps, and the formats accepted are: -MM-dd -MM-dd HH:mm:ss -MM-dd HH:mm:ss.milli These are the formats accepted by the current DateDiff(expr1, expr2) function and allow for that consistency. The accepted data types for the timestamp will be Text, TimestampWritable, Date, and String, just as with the already existing function. The third parameter is the units the user wants the response to be in. Acceptable units are: Microsecond Millisecond Second Minute Hour Day Week Month Quarter Year When calculating the difference, the full timestamp is used when the specified unit is hour or smaller (microsecond, millisecond, second, minute, hour), and only the date part is used if the unit is day or larger (day, week, month, quarter, year). If for the smaller units the time is not specified and the format -MM-dd is used, the time 00:00:00.0 is used. Leap years are accounted for by the Calendar class in Java, which inherently addresses the issue. The assumption is made that all these time parameters are in the same time zone. *Return Value* The function returns expr1 - expr2 expressed as an int in the units specified. *Hive vs. SQL* SQL also has a DateDiff() function with some more acceptable units. The order of parameters is different between SQL and Hive. The reason for this is that Hive already has a DateDiff() function with the same first two parameters, and having this order here allows for that consistency within Hive. *Example Query* hive DATEDIFF(DATE_FIELD, '2012-06-01', ‘day’); *Diagnostic Error Messages* Invalid table alias or column name reference Table not found -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3228) unable to load null values that represent a timestamp value
N Campbell created HIVE-3228: Summary: unable to load null values that represent a timestamp value Key: HIVE-3228 URL: https://issues.apache.org/jira/browse/HIVE-3228 Project: Hive Issue Type: Bug Affects Versions: 0.8.0 Reporter: N Campbell Attempting to load delimited data into a table with one or more timestamp columns will fail when null values are represented in the input set. load data local inpath 'CERT.TTS.txt' overwrite into table CERT.TTS_E; insert overwrite table CERT.TTS select * from CERT.TTS_E; Error: Query returned non-zero code: 9, cause: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask SQLState: 08S01 ErrorCode: 9 create table if not exists CERT.TTS_E ( RNUM int , CTS timestamp) row format delimited fields terminated by '\t' stored as textfile; create table if not exists CERT.TTS ( RNUM int , CTS timestamp) stored as sequencefile; 0 1 1996-01-01 00:00:00.0 2 1996-01-01 12:00:00.0 3 1996-01-01 23:59:30.12300 4 2000-01-01 00:00:00.0 5 2000-01-01 12:00:00.0 6 2000-01-01 23:59:30.12300 7 2000-12-31 00:00:00.0 8 2000-12-31 12:00:00.0 9 2000-12-31 12:15:30.12300 -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3228) unable to load null values that represent a timestamp value
[ https://issues.apache.org/jira/browse/HIVE-3228?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3228: - Attachment: CERT.TTS.txt input data file using tab delimited columns unable to load null values that represent a timestamp value --- Key: HIVE-3228 URL: https://issues.apache.org/jira/browse/HIVE-3228 Project: Hive Issue Type: Bug Affects Versions: 0.8.0 Reporter: N Campbell Attachments: CERT.TTS.txt Attempting to load delimited data into a table with one or more timestamp columns will fail when null values are represented in the input set. load data local inpath 'CERT.TTS.txt' overwrite into table CERT.TTS_E; insert overwrite table CERT.TTS select * from CERT.TTS_E; Error: Query returned non-zero code: 9, cause: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask SQLState: 08S01 ErrorCode: 9 create table if not exists CERT.TTS_E ( RNUM int , CTS timestamp) row format delimited fields terminated by '\t' stored as textfile; create table if not exists CERT.TTS ( RNUM int , CTS timestamp) stored as sequencefile; 0 1 1996-01-01 00:00:00.0 2 1996-01-01 12:00:00.0 3 1996-01-01 23:59:30.12300 4 2000-01-01 00:00:00.0 5 2000-01-01 12:00:00.0 6 2000-01-01 23:59:30.12300 7 2000-12-31 00:00:00.0 8 2000-12-31 12:00:00.0 9 2000-12-31 12:15:30.12300 -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3229) null values being loaded as non-null values into Hive
N Campbell created HIVE-3229: Summary: null values being loaded as non-null values into Hive Key: HIVE-3229 URL: https://issues.apache.org/jira/browse/HIVE-3229 Project: Hive Issue Type: Bug Reporter: N Campbell Attachments: CERT.TSET1.txt various tab delimited input files contain one or more columns that represent null values in rows. the data appears to load (without an error such as in JIRA 3228) however the resulting values are now non-null values which is incorrect. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3229) null values being loaded as non-null values into Hive
[ https://issues.apache.org/jira/browse/HIVE-3229?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3229: - Attachment: CERT.TSET1.txt One example null values being loaded as non-null values into Hive - Key: HIVE-3229 URL: https://issues.apache.org/jira/browse/HIVE-3229 Project: Hive Issue Type: Bug Reporter: N Campbell Attachments: CERT.TSET1.txt various tab delimited input files contain one or more columns that represent null values in rows. the data appears to load (without an error such as in JIRA 3228) however the resulting values are now non-null values which is incorrect. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (HIVE-3229) null values being loaded as non-null values into Hive
[ https://issues.apache.org/jira/browse/HIVE-3229?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] N Campbell updated HIVE-3229: - Description: various tab delimited input files contain one or more columns that represent null values in rows. the data appears to load (without an error such as in JIRA 3228) however the resulting values are now non-null values which is incorrect. create table if not exists CERT.TSET1_E ( RNUM int , C1 int, C2 string) row format delimited fields terminated by '\t' stored as textfile; create table if not exists CERT.TSET1 ( RNUM int , C1 int, C2 string) stored as sequencefile; load data local inpath 'CERT.TSET1.txt' overwrite into table CERT.TSET1_E; insert overwrite table CERT.TSET1 select * from CERT.TSET1_E; was: various tab delimited input files contain one or more columns that represent null values in rows. the data appears to load (without an error such as in JIRA 3228) however the resulting values are now non-null values which is incorrect. null values being loaded as non-null values into Hive - Key: HIVE-3229 URL: https://issues.apache.org/jira/browse/HIVE-3229 Project: Hive Issue Type: Bug Reporter: N Campbell Attachments: CERT.TSET1.txt various tab delimited input files contain one or more columns that represent null values in rows. the data appears to load (without an error such as in JIRA 3228) however the resulting values are now non-null values which is incorrect. create table if not exists CERT.TSET1_E ( RNUM int , C1 int, C2 string) row format delimited fields terminated by '\t' stored as textfile; create table if not exists CERT.TSET1 ( RNUM int , C1 int, C2 string) stored as sequencefile; load data local inpath 'CERT.TSET1.txt' overwrite into table CERT.TSET1_E; insert overwrite table CERT.TSET1 select * from CERT.TSET1_E; -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (HIVE-2703) ResultSetMetaData.getColumnType() always returns VARCHAR(string) for partition columns irrespective of partition column type
[ https://issues.apache.org/jira/browse/HIVE-2703?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13403048#comment-13403048 ] N Campbell commented on HIVE-2703: -- Is this fix (or any other) addressing similar issues with respect to map, array, struct... where the type is always returned and described a string type? ResultSetMetaData.getColumnType() always returns VARCHAR(string) for partition columns irrespective of partition column type Key: HIVE-2703 URL: https://issues.apache.org/jira/browse/HIVE-2703 Project: Hive Issue Type: Bug Components: JDBC Affects Versions: 0.8.0 Reporter: Mythili Gopalakrishnan Assignee: tamtam180 Priority: Critical Attachments: HIVE-2703.D2829.1.patch ResultSetMetaData.getColumnType() always returns VARCHAR(string) as column type, no matter what the column type is for the partition column. However DatabaseMetadata.getColumnType() returns correct type. Create a table with a partition column having a type other than string, you will see that ResultSet.getColumnType() always returns string as the type for int or boolean or float columns... -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3196) date_add of a timestamp returning a string representation of the date portion
N Campbell created HIVE-3196: Summary: date_add of a timestamp returning a string representation of the date portion Key: HIVE-3196 URL: https://issues.apache.org/jira/browse/HIVE-3196 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 0.8.0 Reporter: N Campbell the following expressions are expected to derive a new timestamp where the input has been incremented by n days. The resulting value is a string representation of the date portion (time component truncated) select date_add(cast(from_unixtime(unix_timestamp()) as timestamp), 5) from cert.tversion select date_add(from_unixtime(unix_timestamp()), 5) from cert.tversion the apache docs do not clarify if the is an implicit type conversion to date or the docs need updating to day they will support a timestamp type as an input (which I would expect given the claim to offer timestamp types). https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3183) case expression should allow different types per ISO-SQL 2012
N Campbell created HIVE-3183: Summary: case expression should allow different types per ISO-SQL 2012 Key: HIVE-3183 URL: https://issues.apache.org/jira/browse/HIVE-3183 Project: Hive Issue Type: Bug Affects Versions: 0.8.0 Reporter: N Campbell The ISO-SQL standard specification for CASE allows the specification to include different types in the WHEN and ELSE blocks including this example which mixes smallint and integer types select case when vsint.csint is not null then vsint.csint else 1 end from cert.vsint vsint The Apache Hive docs do not state how it deviates from the standard or any given restrictions so unsure if this is a bug vs an enhancement. Many SQL applications mix so this seems to be a restrictive implementation if this is by design. Argument type mismatch '1': The expression after ELSE should have the same type as those after THEN: smallint is expected but int is found -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3184) remove existing restriction that union must be defined within a derived table (follow ISO-SQL 2012)
N Campbell created HIVE-3184: Summary: remove existing restriction that union must be defined within a derived table (follow ISO-SQL 2012) Key: HIVE-3184 URL: https://issues.apache.org/jira/browse/HIVE-3184 Project: Hive Issue Type: Improvement Affects Versions: 0.8.0 Reporter: N Campbell Don't make SQL applications have to wrap the union operation within a derived table per the SQL standard. Failing to do so results in Top level UNION is not supported currently; use a subquery i.e. select c1, c2 from cert.tset1 tset1 union all select c1, c2 from cert.tset2 tset2 vs select * from ( select c1, c2 from cert.tset1 tset1 union all select c1, c2 from cert.tset2 tset2 ) T -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3185) allow union set operation without ALL keyword (align to ISO-SQL 2012 specification)
N Campbell created HIVE-3185: Summary: allow union set operation without ALL keyword (align to ISO-SQL 2012 specification) Key: HIVE-3185 URL: https://issues.apache.org/jira/browse/HIVE-3185 Project: Hive Issue Type: Improvement Affects Versions: 0.8.0 Reporter: N Campbell Allow a union operation to be specified as qb1 union qb2 in addition to th qb1 union all qb2 Common requirement of queries to project two sets where it is not always the case you want distinct rows in the set. Failing to do so obviously will result in the current exception: mismatched input 'select' expecting ALL near 'union' in query operator -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3186) support having without a grouping operation (per ISO-SQL 2012)
N Campbell created HIVE-3186: Summary: support having without a grouping operation (per ISO-SQL 2012) Key: HIVE-3186 URL: https://issues.apache.org/jira/browse/HIVE-3186 Project: Hive Issue Type: Improvement Affects Versions: 0.8.0 Reporter: N Campbell select count(*) from cert.tset1 tset1 having count(*) 2 granted you can manually write a grouped derived table and filter etc but it valid to define a having statement (implicit grouped table). HAVING specified without GROUP BY -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3187) support ISO-2012 timestamp literals
N Campbell created HIVE-3187:
Summary: support ISO-2012 timestamp literals
Key: HIVE-3187
URL: https://issues.apache.org/jira/browse/HIVE-3187
Project: Hive
Issue Type: Improvement
Affects Versions: 0.8.0
Reporter: N Campbell
Enable the JDBC driver/Hive SQL engine to accept JDBC canonical or ISO-SQL 20xx
Timestamp literals
ie.
select 1 from cert.tversion tversion where timestamp '1989-01-01
10:20:30.0' timestamp '2000-12-31 12:15:30.12300'
instead of
unix_timestamp('.)
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3188) allow cast to use double precision per ISO-SQL 2012
N Campbell created HIVE-3188:
Summary: allow cast to use double precision per ISO-SQL 2012
Key: HIVE-3188
URL: https://issues.apache.org/jira/browse/HIVE-3188
Project: Hive
Issue Type: Improvement
Affects Versions: 0.8.0
Reporter: N Campbell
While ISO-SQL allows cast('10.3' as double precision) Hive forces you to say
cast('10.3' as double ). Accept the additional keyword.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3189) cast ( string type as bigint) returning null values
N Campbell created HIVE-3189: Summary: cast ( string type as bigint) returning null values Key: HIVE-3189 URL: https://issues.apache.org/jira/browse/HIVE-3189 Project: Hive Issue Type: Bug Affects Versions: 0.8.0 Reporter: N Campbell select rnum, c1, cast(c1 as bigint) from cert.tsdchar tsdchar where rnum in (0,1,2) create table if not exists CERT.TSDCHAR ( RNUM int , C1 string) row format sequencefile rnumc1 _c2 0 -1 null 1 0 null 2 10 null -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3190) allow INTEGER as a type name in a column/cast expression (per ISO-SQL 2012)
N Campbell created HIVE-3190:
Summary: allow INTEGER as a type name in a column/cast expression
(per ISO-SQL 2012)
Key: HIVE-3190
URL: https://issues.apache.org/jira/browse/HIVE-3190
Project: Hive
Issue Type: Improvement
Affects Versions: 0.8.0
Reporter: N Campbell
Just extend the parser to allow INTEGER instead of making folks use INT
select cast('10' as integer) from cert.tversion tversion
FAILED: Parse Error: line 1:20 cannot recognize input near 'integer' ')' 'from'
in primitive type specification
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3192) between predicate parsing issues
N Campbell created HIVE-3192: Summary: between predicate parsing issues Key: HIVE-3192 URL: https://issues.apache.org/jira/browse/HIVE-3192 Project: Hive Issue Type: Bug Affects Versions: 0.8.0 Reporter: N Campbell These SQL statements will not parse. select tbint.rnum, case when tbint.cbint between 6 and 8 then 'between' else 'other' end from cert.tbint tbint Error: Query returned non-zero code: 11, cause: FAILED: Parse Error: line 3:17 mismatched input 'between' expecting KW_THEN near 'cbint' in case expression SQLState: 42000 ErrorCode: 11 select tbint.rnum from cert.tbint tbint where tbint.cbint between 6 and 8 Error: Query returned non-zero code: 11, cause: FAILED: Parse Error: line 3:18 mismatched input 'between' expecting EOF near 'cbint' SQLState: 42000 ErrorCode: 11 create table if not exists CERT.TBINT ( RNUM int , CBINT bigint) stored as sequencefile; -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (HIVE-3193) cannot query views using qualified table references
N Campbell created HIVE-3193: Summary: cannot query views using qualified table references Key: HIVE-3193 URL: https://issues.apache.org/jira/browse/HIVE-3193 Project: Hive Issue Type: Bug Affects Versions: 0.8.0 Reporter: N Campbell hive server has databases. irrespective of specifying /db-name on the connection or an explicit set the following scenario will fail set database cert drop view cert.vsint create view CERT.VSINT as select * from CERT.TSINT select * from CERT.VSINT Error: Query returned non-zero code: 10, cause: FAILED: Error in semantic analysis: Invalid table alias Referencing view from foreign databases is not supported. SQLState: 42000 ErrorCode: 10 and ditto set database cert drop view cert.vsint create view CERT.VSINT as select * from TSINT select * from CERT.VSINT Error: Query returned non-zero code: 10, cause: FAILED: Error in semantic analysis: Line 1:14 Table not found 'VSINT' SQLState: 42000 ErrorCode: 10 -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
