[
https://issues.apache.org/jira/browse/HIVE-25718?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ayush Saxena updated HIVE-25718:
--------------------------------
Labels: hive-4.1.0-must (was: )
> 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
> Priority: Major
> Labels: hive-4.1.0-must
> Attachments: jdbc_table_orderby_mssql.q
>
>
> +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.10#820010)
