Stamatis Zampetakis created HIVE-25718:
------------------------------------------
Summary: ORDER BY query on external MSSQL table fails
Key: HIVE-25718
URL: https://issues.apache.org/jira/browse/HIVE-25718
Project: Hive
Issue Type: Bug
Components: HiveServer2
Reporter: Stamatis Zampetakis
+Microsoft SQLServer+
{code:sql}
CREATE TABLE country (id int, name varchar(20));
insert into country values (1, 'India');
insert into country values (2, 'Russia');
insert into country values (3, 'USA');
{code}
+Hive+
{code:sql}
CREATE EXTERNAL TABLE country (id int, name varchar(20))
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
"hive.sql.database.type" = "MSSQL",
"hive.sql.jdbc.driver" = "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"hive.sql.jdbc.url" = "jdbc:sqlserver://localhost:1433;",
"hive.sql.dbcp.username" = "sa",
"hive.sql.dbcp.password" = "Its-a-s3cret",
"hive.sql.table" = "country");
SELECT * FROM country ORDER BY id;
{code}
The query fails with the following stacktrace:
{noformat}
com.microsoft.sqlserver.jdbc.SQLServerException: The ORDER BY clause is invalid
in views, inline functions, derived tables, subqueries, and common table
expressions, unless TOP, OFFSET or FOR XML is also specified.
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
~[mssql-jdbc-6.2.1.jre8.jar:?]
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
~[mssql-jdbc-6.2.1.jre8.jar:?]
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
~[mssql-jdbc-6.2.1.jre8.jar:?]
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
~[mssql-jdbc-6.2.1.jre8.jar:?]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
~[mssql-jdbc-6.2.1.jre8.jar:?]
at
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478)
~[mssql-jdbc-6.2.1.jre8.jar:?]
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
~[mssql-jdbc-6.2.1.jre8.jar:?]
at
com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
~[mssql-jdbc-6.2.1.jre8.jar:?]
at
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331)
~[mssql-jdbc-6.2.1.jre8.jar:?]
at
org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
~[commons-dbcp2-2.7.0.jar:2.7.0]
at
org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
~[commons-dbcp2-2.7.0.jar:2.7.0]
at
org.apache.hive.storage.jdbc.dao.GenericJdbcDatabaseAccessor.getRecordIterator(GenericJdbcDatabaseAccessor.java:180)
[hive-jdbc-handler-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hive.storage.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:58)
[hive-jdbc-handler-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hive.storage.jdbc.JdbcRecordReader.next(JdbcRecordReader.java:35)
[hive-jdbc-handler-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hadoop.hive.ql.exec.FetchOperator.getNextRow(FetchOperator.java:589)
[hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hadoop.hive.ql.exec.FetchOperator.pushRow(FetchOperator.java:529)
[hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at org.apache.hadoop.hive.ql.exec.FetchTask.fetch(FetchTask.java:150)
[hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hadoop.hive.ql.Driver.getFetchingTableResults(Driver.java:716)
[hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at org.apache.hadoop.hive.ql.Driver.getResults(Driver.java:668)
[hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hadoop.hive.ql.reexec.ReExecDriver.getResults(ReExecDriver.java:241)
[hive-exec-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:277)
[hive-cli-4.0.0-SNAPSHOT.jar:?]
at org.apache.hadoop.hive.cli.CliDriver.processCmd1(CliDriver.java:201)
[hive-cli-4.0.0-SNAPSHOT.jar:?]
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:127)
[hive-cli-4.0.0-SNAPSHOT.jar:?]
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:422)
[hive-cli-4.0.0-SNAPSHOT.jar:?]
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:353)
[hive-cli-4.0.0-SNAPSHOT.jar:?]
at
org.apache.hadoop.hive.ql.QTestUtil.executeClientInternal(QTestUtil.java:726)
[hive-it-util-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hadoop.hive.ql.QTestUtil.executeClient(QTestUtil.java:696)
[hive-it-util-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hadoop.hive.cli.control.CoreCliDriver.runTest(CoreCliDriver.java:114)
[hive-it-util-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hadoop.hive.cli.control.CliAdapter.runTest(CliAdapter.java:157)
[hive-it-util-4.0.0-SNAPSHOT.jar:4.0.0-SNAPSHOT]
at
org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver(TestMiniLlapLocalCliDriver.java:62)
[test-classes/:?]
{noformat}
The Hive plan showing also the SQL query that is send to Microsoft SQLServer is
provided below:
{noformat}
STAGE DEPENDENCIES:
Stage-0 is a root stage
STAGE PLANS:
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
TableScan
alias: country
properties:
hive.sql.query SELECT "id", "name"
FROM (SELECT "id", "name"
FROM "country"
ORDER BY CASE WHEN "id" IS NULL THEN 1 ELSE 0 END, "id") AS "t"
hive.sql.query.fieldNames id,name
hive.sql.query.fieldTypes int,varchar(20)
hive.sql.query.split false
Select Operator
expressions: id (type: int), name (type: varchar(20))
outputColumnNames: _col0, _col1
ListSink
{noformat}
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
