You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "yuanyuanli (Jira)" <ji...@apache.org> on 2022/06/14 14:57:00 UTC
[jira] [Updated] (HIVE-26328) ParseException for right nested join query
[ https://issues.apache.org/jira/browse/HIVE-26328?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
yuanyuanli updated HIVE-26328:
------------------------------
Description:
I have the following sql where right nested join is used:
SELECT
`sp`.`sno` AS `SNO`,
`p`.`pno` AS `PNO`
FROM
`tparts` `p`
JOIN (
SELECT
`sno`,
`pno`
FROM
`tsupply`
) `sp`
JOIN `tsuppliers` `s` ON `sp`.`sno` = `s`.`sno` ON `p`.`pno` = `sp`.`pno`,
`tpartorder` `po`
When running above sql I got the following error:
Error: Error while compiling statement: FAILED: ParseException line 16:4 cannot recognize input near 'ON' 'p' '.' in expression specification
SQLState: 42000
ErrorCode: 40000
Tried to add parentheses but no luck.
After added
SELECT
`sp`.`sno` AS `SNO`,
`p`.`pno` AS `PNO`
FROM
(`tparts` `p`
JOIN (
(SELECT
`sno`,
`pno`
FROM
`tsupply`
) `sp`
JOIN `tsuppliers` `s` ON `sp`.`sno` = `s`.`sno`)
ON `p`.`pno` = `sp`.`pno`),
`tpartorder` `po`
Got the following error:
Error: Error while compiling statement: FAILED: ParseException line 12:4 missing ) at 'sp' near ')'
line 15:2 missing EOF at 'ON' near ')'
SQLState: 42000
ErrorCode: 40000
I am using driver hive-jdbc-3.1.3000.7.1.6.0-297-standalone with Apache Hive 3.1.3000 on CDP 7.1.7.0-551
was:
I have the following sql where right nested join is used:
SELECT
`sp`.`sno` AS `SNO`,
`p`.`pno` AS `PNO`
FROM
`tparts` `p`
JOIN (
SELECT
`sno`,
`pno`
FROM
`tsupply`
) `sp`
JOIN `tsuppliers` `s` ON `sp`.`sno` = `s`.`sno` ON `p`.`pno` = `sp`.`pno`,
`tpartorder` `po`
When running above sql I got the following error:
Error: Error while compiling statement: FAILED: ParseException line 16:4 cannot recognize input near 'ON' 'p' '.' in expression specification
SQLState: 42000
ErrorCode: 40000
Tried to add parentheses but no luck.
After added
SELECT
`sp`.`sno` AS `SNO`,
`p`.`pno` AS `PNO`
FROM
(`tparts` `p`
JOIN (
(SELECT
`sno`,
`pno`
FROM
`tsupply`
) `sp`
JOIN `tsuppliers` `s` ON `sp`.`sno` = `s`.`sno`)
ON `p`.`pno` = `sp`.`pno`),
`tpartorder` `po`
Got the following error:
Error: Error while compiling statement: FAILED: ParseException line 12:4 missing ) at 'sp' near ')'
line 15:2 missing EOF at 'ON' near ')'
SQLState: 42000
ErrorCode: 40000
I am using driver hive-jdbc-3.1.3000.7.1.6.0-297-standalone.
> ParseException for right nested join query
> ------------------------------------------
>
> Key: HIVE-26328
> URL: https://issues.apache.org/jira/browse/HIVE-26328
> Project: Hive
> Issue Type: Bug
> Reporter: yuanyuanli
> Priority: Major
>
> I have the following sql where right nested join is used:
> SELECT
> `sp`.`sno` AS `SNO`,
> `p`.`pno` AS `PNO`
> FROM
> `tparts` `p`
> JOIN (
> SELECT
> `sno`,
> `pno`
> FROM
> `tsupply`
> ) `sp`
> JOIN `tsuppliers` `s` ON `sp`.`sno` = `s`.`sno` ON `p`.`pno` = `sp`.`pno`,
> `tpartorder` `po`
>
> When running above sql I got the following error:
> Error: Error while compiling statement: FAILED: ParseException line 16:4 cannot recognize input near 'ON' 'p' '.' in expression specification
> SQLState: 42000
> ErrorCode: 40000
>
>
> Tried to add parentheses but no luck.
> After added
>
> SELECT
> `sp`.`sno` AS `SNO`,
> `p`.`pno` AS `PNO`
> FROM
> (`tparts` `p`
> JOIN (
> (SELECT
> `sno`,
> `pno`
> FROM
> `tsupply`
> ) `sp`
> JOIN `tsuppliers` `s` ON `sp`.`sno` = `s`.`sno`)
>
> ON `p`.`pno` = `sp`.`pno`),
> `tpartorder` `po`
>
> Got the following error:
> Error: Error while compiling statement: FAILED: ParseException line 12:4 missing ) at 'sp' near ')'
> line 15:2 missing EOF at 'ON' near ')'
> SQLState: 42000
> ErrorCode: 40000
>
> I am using driver hive-jdbc-3.1.3000.7.1.6.0-297-standalone with Apache Hive 3.1.3000 on CDP 7.1.7.0-551
--
This message was sent by Atlassian Jira
(v8.20.7#820007)