Hi Daniel, Thanks for your help, i answer between the lines. On 10/01/2013 10:51 PM, Daniel L. Wang wrote: > Hi Fabrice, > > Thanks for posting the query! > > > mysql> SELECT s.ra, s.decl, o.raRange, o.declRange FROM Object o > JOIN Source s USING (objectId) WHERE o.objectId = 390034570102582 > AND o.latestObsTime = s.taiMidPoint; > > I note that there is no code for supporting the FROM ... JOIN ... > USING ( ...) syntax. I started playing with it last month, but don't > have anything I can merge--it's just not on the road map, even though > I think that you all want it (there seem to be other things more > pressing). > I had a look a this code today. Even if i'm not very acquainted with parsing techniques, it looks clear. Do you want me to merge it to my current code in a personal branch and then test it. > I think > > SELECT s.ra, s.decl, o.raRange, o.declRange > FROM Object o, Source s, where o.objectId = > WHERE o.objectId = 390034570102582 > AND o.objectId = s.objectId > AND o.latestObsTime = s.taiMidPoint; > > should work, though I haven't tested it. It returns the good result but duplicated 288 times (mysql query only returns one row) In qserv : mysql> SELECT s.ra, s.decl, o.raRange, o.declRange FROM Object o, Source s WHERE o.objectId = 390034570102582 AND o.objectId = s.objectId AND o.latestObsTime = s.taiMidPoint; ... | 1.8100328888038 | -5.50529748780274 | NULL | NULL | | 1.8100328888038 | -5.50529748780274 | NULL | NULL | | 1.8100328888038 | -5.50529748780274 | NULL | NULL | | 1.8100328888038 | -5.50529748780274 | NULL | NULL | | 1.8100328888038 | -5.50529748780274 | NULL | NULL | +-----------------+-------------------+---------+-----------+ 288 rows in set (7.27 sec) I've done a few debugging and it seems that the master sends the query 228 times to the worker, via xrootd : Here's and extract of the qserv-master log file (qserv was patch to write SQL queries sent to worker) Msg cid=6630 with size=54091 TEST-DEBUG SQL=^H^B^R^DLSST^Xæ3"¾: ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint .... ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint ¶^ASELECT s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_6630 AS o,LSST.Source_6630 AS s WHERE o.objectId=390034570102582 AND o.objectId=s.objectId AND o.latestObsTime=s.taiMidPoint^Z*r_2536c46ad2383d164ac4bf716edf836a9_6630_0"(^X 1e122f26cf89e2b55bfd9c05bf5f9cd6 QuerySize 54095 1e122f26cf89e2b55bfd9c05bf5f9cd6 WriteClose Thu Oct 3 17:31:55 2013 0.001213 I try to do some more investigation, but your help on this issue is very welcome. Thanks, Fabrice > > If you'd like to have a look at the code I was playing with, it's on > branch > u/danielw/joinSyntax1 > but it's now behind the shared scan code (which I hope to clean up to > Serge's quality and merge soon) by a fair bit. > > -Daniel > > > On 09/30/2013 08:38 AM, Fabrice Jammes wrote: >> Hello Daniel, >> >> As told on Thursday, i've met several error on SQL queries wich works on >> previous version (without qms and new parser). >> I'm not sure if error come from the test procedure or from qserv new >> version. >> I propose to send it to you one by one with detailed log, instead of >> sending the whole list (bugs may be fixed once and solve many queries). >> >> Here's one example for test case 01 : >> >> While connected to qserv : >> >> mysql> SELECT s.ra, s.decl, o.raRange, o.declRange FROM Object o >> JOIN Source s USING (objectId) WHERE o.objectId = 390034570102582 >> AND o.latestObsTime = s.taiMidPoint; >> ERROR 4120 (Proxy): Error during execution: 'open failed for chunk(s): >> 6801 6630 6800 7138 7308 6970 6971 6968 6631 7140read failed for >> chunk(s): 7478 7648 7310' >> >> xrootd and qserv-master logs are attached and it seems the errors are >> reported in xrootd.log with SQL sentence like this one : >> >> >>Unknown column 's.ra' in 'field list' Unable to execute query: >> CREATE >> TABLE r_1e01fa2d9ea14d542987c2af43bb3645f_7478_0 SELECT >> s.ra,s.decl,o.raRange,o.declRange FROM LSST.Object_7478 AS o WHERE >> o.objectId=390034570102582 AND o.latestObsTime=s.taiMidPoint; >> <<---Error with piece 0 complete (size=1). >> >> Indeed it seems table Source should be in FROM clause, aliased with >> 's' ? >> >> I can of course give you more information if needed. >> >> Thanks, >> >> Fabrice > ######################################################################## Use REPLY-ALL to reply to list To unsubscribe from the QSERV-L list, click the following link: https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1