Thread: BUG #7850: left outer join is not working or I didn't contruct the query properly

BUG #7850: left outer join is not working or I didn't contruct the query properly

From

nvardar@carrieriq.com

Date:

05 February 2013, 17:30:41

The following bug has been logged on the website:

Bug reference:      7850
Logged by:          Nadir Vardar
Email address:      nvardar@carrieriq.com
PostgreSQL version: 9.0.10
Operating system:   linux-gridsql
Description:        =


I am trying to run below query;

SELECT
    tbl.id,
    tbl.day,
    tbl.week,
    tbl.appfailure,
    tbl.appname,
    tbl.deviceid,
    ext6.DIM_NAME,
    ext7.DIM_NAME,
    ext8.DIM_NAME,
    ext9.DIM_NAME,
    tbl.starttime
FROM
    APPSTARTEVT tbl
    left outer join DIM_deviceMakeModel ext6 on
(ext6.DIM_ID=3Dtbl.devicemakemodel)
    left outer join DIM_devicePlatformType ext7 on
(ext7.DIM_ID=3Dtbl.deviceplatformtype)
    left outer join DIM_deviceVersion ext8 on
(ext8.DIM_ID=3Dtbl.deviceversion)
    left outer join DIM_operator ext9 on (ext9.DIM_ID=3Dtbl.operator)    =

ORDER BY
    tbl.id ASC

throws;

>[Error] Script lines: 1-20 -------------------------
 ERROR: Node 2 has aborted execution, cause is:
com.edb.gridsql.exception.XDBServerException :
java.lang.NullPointerException
QUERY: SELECT "TMPTT6624_1"."deviceplatformtype" AS
"deviceplatformtype","TMPTT6624_1"."XSERIALID0" AS
"XSERIALID0","TMPTT6624_1"."XONODEID0" AS "XONODEID0" FROM "TMPTT6624_1" =

LEFT OUTER JOIN "dim_deviceplatformtype" "ext7" ON ("ext7"."dim_id" =3D
"TMPTT6624_1"."deviceplatformtype")  WHERE "ext7"."dim_id" IS NULL
 Line: 1 =03

 [Executed: 2/4/13 11:07:08 PM PST ] [Execution: 0/ms] =



exception.

Is there anything i am missing.

Query;

SELECT
    tbl.id,
    tbl.day,
    tbl.week,
    tbl.appfailure,
    tbl.appname,
    tbl.deviceid,
    ext6.DIM_NAME,
    ext7.DIM_NAME,
    ext8.DIM_NAME,
    ext9.DIM_NAME,
    tbl.starttime
FROM
    APPSTARTEVT tbl,    =

    DIM_deviceMakeModel ext6,
    DIM_devicePlatformType ext7,
    DIM_deviceVersion ext8,
    DIM_operator ext9
WHERE
    tbl.devicemakemodel=3Dext6.DIM_ID AND tbl.deviceplatformtype=3Dext7.DIM=
_ID
AND tbl.deviceversion=3D
    ext8.DIM_ID AND tbl.operator=3Dext9.DIM_ID
ORDER BY
    tbl.id ASC

works btw...
Thanks.

Re: BUG #7850: left outer join is not working or I didn't contruct the query properly

From

Pavel Stehule

Date:

05 February 2013, 17:46:29

Hello

this is not a bug report.

please, send your query to pg_general mailing list

probably better - ask on gridsql mailing list

Regards

Pavel Stehule

2013/2/5  <nvardar@carrieriq.com>:
> The following bug has been logged on the website:
>
> Bug reference:      7850
> Logged by:          Nadir Vardar
> Email address:      nvardar@carrieriq.com
> PostgreSQL version: 9.0.10
> Operating system:   linux-gridsql
> Description:
>
> I am trying to run below query;
>
> SELECT
>     tbl.id,
>     tbl.day,
>     tbl.week,
>     tbl.appfailure,
>     tbl.appname,
>     tbl.deviceid,
>     ext6.DIM_NAME,
>     ext7.DIM_NAME,
>     ext8.DIM_NAME,
>     ext9.DIM_NAME,
>     tbl.starttime
> FROM
>     APPSTARTEVT tbl
>     left outer join DIM_deviceMakeModel ext6 on
> (ext6.DIM_ID=tbl.devicemakemodel)
>     left outer join DIM_devicePlatformType ext7 on
> (ext7.DIM_ID=tbl.deviceplatformtype)
>     left outer join DIM_deviceVersion ext8 on
> (ext8.DIM_ID=tbl.deviceversion)
>     left outer join DIM_operator ext9 on (ext9.DIM_ID=tbl.operator)
> ORDER BY
>     tbl.id ASC
>
> throws;
>
>>[Error] Script lines: 1-20 -------------------------
>  ERROR: Node 2 has aborted execution, cause is:
> com.edb.gridsql.exception.XDBServerException :
> java.lang.NullPointerException
> QUERY: SELECT "TMPTT6624_1"."deviceplatformtype" AS
> "deviceplatformtype","TMPTT6624_1"."XSERIALID0" AS
> "XSERIALID0","TMPTT6624_1"."XONODEID0" AS "XONODEID0" FROM "TMPTT6624_1"
> LEFT OUTER JOIN "dim_deviceplatformtype" "ext7" ON ("ext7"."dim_id" =
> "TMPTT6624_1"."deviceplatformtype")  WHERE "ext7"."dim_id" IS NULL
>  Line: 1
>
>  [Executed: 2/4/13 11:07:08 PM PST ] [Execution: 0/ms]
>
>
> exception.
>
> Is there anything i am missing.
>
> Query;
>
> SELECT
>     tbl.id,
>     tbl.day,
>     tbl.week,
>     tbl.appfailure,
>     tbl.appname,
>     tbl.deviceid,
>     ext6.DIM_NAME,
>     ext7.DIM_NAME,
>     ext8.DIM_NAME,
>     ext9.DIM_NAME,
>     tbl.starttime
> FROM
>     APPSTARTEVT tbl,
>     DIM_deviceMakeModel ext6,
>     DIM_devicePlatformType ext7,
>     DIM_deviceVersion ext8,
>     DIM_operator ext9
> WHERE
>     tbl.devicemakemodel=ext6.DIM_ID AND tbl.deviceplatformtype=ext7.DIM_ID
> AND tbl.deviceversion=
>     ext8.DIM_ID AND tbl.operator=ext9.DIM_ID
> ORDER BY
>     tbl.id ASC
>
> works btw...
> Thanks.
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

Re: BUG #7850: left outer join is not working or I didn't contruct the query properly

From

Amit Kapila

Date:

05 February 2013, 19:06:18

Tuesday, February 05, 2013 12:40 PM nvardar wrote:
> I am trying to run below query;
>=20
> SELECT
>     tbl.id,
>     tbl.day,
>     tbl.week,
>     tbl.appfailure,
>     tbl.appname,
>     tbl.deviceid,
>     ext6.DIM_NAME,
>     ext7.DIM_NAME,
>     ext8.DIM_NAME,
>     ext9.DIM_NAME,
>     tbl.starttime
> FROM
>     APPSTARTEVT tbl
>     left outer join DIM_deviceMakeModel ext6 on
> (ext6.DIM_ID=3Dtbl.devicemakemodel)
>     left outer join DIM_devicePlatformType ext7 on
> (ext7.DIM_ID=3Dtbl.deviceplatformtype)
>     left outer join DIM_deviceVersion ext8 on
> (ext8.DIM_ID=3Dtbl.deviceversion)
>     left outer join DIM_operator ext9 on (ext9.DIM_ID=3Dtbl.operator)
> ORDER BY
>     tbl.id ASC
>=20
> throws;
>=20
> >[Error] Script lines: 1-20 -------------------------
>  ERROR: Node 2 has aborted execution, cause is:
> com.edb.gridsql.exception.XDBServerException :
> java.lang.NullPointerException
> QUERY: SELECT "TMPTT6624_1"."deviceplatformtype" AS
> "deviceplatformtype","TMPTT6624_1"."XSERIALID0" AS
> "XSERIALID0","TMPTT6624_1"."XONODEID0" AS "XONODEID0" FROM
> "TMPTT6624_1"
> LEFT OUTER JOIN "dim_deviceplatformtype" "ext7" ON ("ext7"."dim_id" =
=3D
> "TMPTT6624_1"."deviceplatformtype")  WHERE "ext7"."dim_id" IS NULL
>  Line: 1 =03
>=20
>  [Executed: 2/4/13 11:07:08 PM PST ] [Execution: 0/ms]
>=20
>=20
> exception.
>=20
> Is there anything i am missing.

Why the query is different in Exception?
Can you try with=20
SELECT
     tbl.id,
     tbl.day,
     tbl.week,
     tbl.appfailure,
     tbl.appname,
     tbl.deviceid,
     ext6.DIM_NAME,
     tbl.starttime
 FROM
     APPSTARTEVT tbl
     left outer join DIM_deviceMakeModel ext6 on
    (ext6.DIM_ID=3Dtbl.devicemakemodel)
    ORDER BY   tbl.id ASC

Also as Pavel has pointed, send this to General mailing list.=20

With Regards,
Amit Kapila.

Re: [GENERAL] BUG #7850: left outer join is not working or I didn't contruct the query properly

From

Alban Hertroys

Date:

05 February 2013, 19:45:43

On 5 February 2013 17:06, Amit Kapila <amit.kapila@huawei.com> wrote:

> Tuesday, February 05, 2013 12:40 PM nvardar wrote:
> > throws;
> >
> > >[Error] Script lines: 1-20 -------------------------
> >  ERROR: Node 2 has aborted execution, cause is:
> > com.edb.gridsql.exception.XDBServerException :
> > java.lang.NullPointerException
> > QUERY: SELECT "TMPTT6624_1"."deviceplatformtype" AS
> > "deviceplatformtype","TMPTT6624_1"."XSERIALID0" AS
> > "XSERIALID0","TMPTT6624_1"."XONODEID0" AS "XONODEID0" FROM
> > "TMPTT6624_1"
> > LEFT OUTER JOIN "dim_deviceplatformtype" "ext7" ON ("ext7"."dim_id" =
> > "TMPTT6624_1"."deviceplatformtype")  WHERE "ext7"."dim_id" IS NULL
> >  Line: 1
> >
> >  [Executed: 2/4/13 11:07:08 PM PST ] [Execution: 0/ms]
>

Does this query work if you copy it from the exception message and execute
it in psql?

If so, then the problem is somewhere in the Java domain, between gridsql
and jdbc.

If not, you'll probably get a more informative error out of it ;)

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.