db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-39) Strange error in JOIN ON clause
Date Thu, 29 Apr 2010 15:02:57 GMT

    [ https://issues.apache.org/jira/browse/DERBY-39?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12862220#action_12862220
] 

Knut Anders Hatlen commented on DERBY-39:
-----------------------------------------

For the record, these queries fail on PostgreSQL and MySQL too. And, according to earlier
comments, they also fail on DB2.

Here's what PostgreSQL 8.4 says:

> ERROR:  invalid reference to FROM-clause entry for table "a0"
> LINE 4: ...MODEOFPAYMENTFLAVOUR_MODEOFPAYMENTFLAVOURID_OID = A0.MODEOFP...
>                                                              ^
> HINT:  There is an entry for table "a0", but it cannot be referenced from this part of
the query.

And here's the error reported by MySQL 5.1:

> ERROR 1054 (42S22): Unknown column 'A0.MODEOFPAYMENTFLAVOURID' in 'on clause'

> Strange error in JOIN ON clause
> -------------------------------
>
>                 Key: DERBY-39
>                 URL: https://issues.apache.org/jira/browse/DERBY-39
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.0.2.0
>            Reporter: Erik Bengtson
>         Attachments: d39.sql, derby-joinon.tar.gz
>
>
> The exception:
> ---------------------------------------
> Error: An ON clause associated with a JOIN operator is not valid.
> ---------------------------------------
> happens when I run the below SQL script:
> ---------------------------------------
> SELECT
> THIS.DOSSIERTEMPLATE_ID
> FROM DOSSIERTEMPLATE THIS,
> ENTITLEMENT UNBOUND_ENTITLE 
> INNER JOIN 
> ENTITLEMENT II 
> ON UNBOUND_ENTITLE.ENTITLEMENT_ID = II.ENTITLEMENT_ID 
> INNER JOIN 
> DOSSIERTEMPLATERESOURCE BB 
> ON II.ENTITLED_TO_RESOURCE_ID_OID = BB.DOSSIERTEMPLATERESOURCE_ID 
> INNER JOIN 
> I18N THIS_LABEL
> ON THIS.LABEL_I18N_ID_OID = THIS_LABEL.I18N_ID
> ---------------------------------------
> It works fine if I run without the LABEL join
> ---------------------------------------
> SELECT
> THIS.DOSSIERTEMPLATE_ID
> FROM DOSSIERTEMPLATE THIS,
> ENTITLEMENT UNBOUND_ENTITLE 
> INNER JOIN 
> ENTITLEMENT II 
> ON UNBOUND_ENTITLE.ENTITLEMENT_ID = II.ENTITLEMENT_ID 
> INNER JOIN 
> DOSSIERTEMPLATERESOURCE BB 
> ON II.ENTITLED_TO_RESOURCE_ID_OID = BB.DOSSIERTEMPLATERESOURCE_ID 
> ---------------------------------------
> The column LABEL_I18N_ID_OID is BIGINT and has a FK to I18N_ID, which is BIGINT as well

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message