Обсуждение: 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
От
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=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
От
Pavel Stehule
Дата:
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
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
От
Alban Hertroys
Дата:
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.