Return-Path: X-Original-To: apmail-oodt-dev-archive@www.apache.org Delivered-To: apmail-oodt-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id C1C5E931E for ; Fri, 18 May 2012 00:51:35 +0000 (UTC) Received: (qmail 32915 invoked by uid 500); 18 May 2012 00:51:35 -0000 Delivered-To: apmail-oodt-dev-archive@oodt.apache.org Received: (qmail 32851 invoked by uid 500); 18 May 2012 00:51:35 -0000 Mailing-List: contact user-help@oodt.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@oodt.apache.org Delivered-To: mailing list user@oodt.apache.org Received: (qmail 32841 invoked by uid 99); 18 May 2012 00:51:35 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 May 2012 00:51:35 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,MIME_QP_LONG_LINE,RCVD_IN_DNSWL_LOW,SPF_PASS,WEIRD_PORT X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of holenoter@mac.com designates 17.172.220.237 as permitted sender) Received: from [17.172.220.237] (HELO st11p02mm-asmtpout002.mac.com) (17.172.220.237) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 May 2012 00:51:27 +0000 MIME-version: 1.0 Content-type: multipart/alternative; boundary="Boundary_(ID_5OfKtEW09ZRKCS/FwJN1Wg)" Received: from st11p02mm-spool001.mac.com ([17.172.220.246]) by st11p02mm-asmtp002.mac.com (Oracle Communications Messaging Server 7u4-23.01(7.0.4.23.0) 64bit (built Aug 10 2011)) with ESMTP id <0M4700BTU1158O10@st11p02mm-asmtp002.mac.com> for user@oodt.apache.org; Fri, 18 May 2012 00:51:06 +0000 (GMT) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:5.6.7580,1.0.260,0.0.0000 definitions=2012-05-17_06:2012-05-17,2012-05-17,1970-01-01 signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 ipscore=0 suspectscore=1 phishscore=0 bulkscore=0 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=6.0.2-1012030000 definitions=main-1205170303 Received: from localhost ([17.172.220.222]) by st11p02mm-spool001.mac.com (Oracle Communications Messaging Server 7u4-23.01(7.0.4.23.0) 64bit (built Aug 10 2011)) with ESMTP id <0M4700AYJ115FS80@st11p02mm-spool001.mac.com> for user@oodt.apache.org; Fri, 18 May 2012 00:51:05 +0000 (GMT) To: user@oodt.apache.org From: Brian Foster Subject: Re: Query Tool Bugs? Date: Fri, 18 May 2012 00:51:07 +0000 (GMT) X-Mailer: MobileMe Mail (1J25+8525) X-Originating-IP: [216.239.55.102] Message-id: <18207d99-cad6-4554-c250-a7ec7c3e3051@me.com> In-reply-to: X-Virus-Checked: Checked by ClamAV on apache.org --Boundary_(ID_5OfKtEW09ZRKCS/FwJN1Wg) Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: quoted-printable =0Ahey mike,=0A=0AYou putting a space between the operators? its hard to t= ell in emails, stuff gets normalized sometimes.=0A=0ARangeBeginningDate > = '2007-01-01'=0A=0AShould be a space before and after >=0A=0Aeach value sho= uld start and end with single quote.=0A=0A-brian=0A=0AOn May 17, 2012, at = 04:18 PM, "Cayanan, Michael D (388J)" wro= te:=0A=0AHey Brian,=0A=0AI checked out the wengine-branch filemgr and unfo= rtunately, it still didn't work. I tried a query using the wengine branch'= s query_tool since this version of the filemgr-client didn't have the abil= ity to query.=0A=0AIn any case, I took the liberty of diving into the Quer= y Tool code to see if I can find out more as to what is going on. So this = is what I found:=0A=0ASurrounding single quotes around each of the conditi= ons got me further into the code. So, when I entered the query as follows,= =0A=0ASELECT * FROM L0a_Radar WHERE 'RangeBeginningDate>2007-01-01' AND 'R= angeBeginningTime>12:00:00Z'=0A=0AThe SQLParser class liked the syntax and= it produced the following post fix statement:=0A=0APost Fix Statement:=A0= ['RangeBeginningDate>2007-01-01', 'RangeBeginningTime>12:00:00Z', AND]=0A=0A= I think from here, it creates some Boolean Query Criteria with the followi= ng content:=0A=0ABoolean Query Criteria:=A0AND('RangeBeginningTime:[12:00:= 00Z' TO null], 'RangeBeginningDate:[2007-01-01' TO null])=0A=0AI noticed t= hat there are unmatched single quotes around the date and time values. Not= sure how that came about. The tool still threw an exception. I'm guessing= it's because of the unmatched single quotes.=0A=0AI tried surrounding the= date and time values with single quotes as well, but the SQLParser class = didn't like that either:=0A=0ASELECT * FROM L0a_Radar WHERE 'RangeBeginnin= gDate>'2007-01-01'' AND 'RangeBeginningTime>'12:00:00Z''=0A=0A=0A-Mike=0A=0A= From: Brian Foster =0AReply-To: =0A= Date: Thu, 17 May 2012 18:40:20 +0000=0ATo: =0ASubje= ct: Re: Query Tool Bugs?=0A=0A=0Ahey mike,=0A=0Awould you mind trying to r= un this query using the wengine-branch filemgr (https://svn.apache.org/rep= os/asf/oodt/branches/wengine-branch/filemgr/)... there are a lot of bug fi= xes floating around in both Lucene and DataSource Catalogs in that branch.= .. if it works with that then we know it is a bug with trunk.=0A=0A-brian=0A= =0AOn May 16, 2012, at 07:26 AM, "Cayanan, Michael D (388J)" wrote:=0A=0AHey Brian,=0A=0AYa, I tried the query with= and without the double quotes surrounding the where clause and they both = didn't work for me.=0A=0AIn any case, I've built the latest file manager i= n the trunk and it fixed that StringIndexOutofBoundsException issue I was = seeing. Thanks!=0A=0AOut of curiosity, I tried using the latest File Manag= er Client tool with the query with multiple conditions to see if I could g= et a result, but it threw an exception as well:=0A=0A./filemgr-client -u h= ttp://localhost:9000 -q "SELECT * FROM L0a_Radar WHERE RangeBeginningDate>= '2007-01-01' AND RangeBeginningTime>'12:00:00Z'" -op -sqlQuery=0AMay 16, 2= 012 7:20:53 AM org.apache.oodt.cas.filemgr.system.XmlRpcFileManagerClient = =0AINFO: Loading File Manager Configuration Properties from: [../etc= /filemgr.properties]=0Aorg.apache.xmlrpc.XmlRpcException: java.lang.Except= ion: org.apache.oodt.cas.filemgr.structs.exceptions.CatalogException: Fail= ed to perform complex query : You have an error in your SQL syntax; check = the manual that corresponds to your MySQL server version for the right syn= tax to use near 'INTERSECT (SELECT DISTINCT product_id FROM L0a_Radar_meta= data WHERE element_id =3D' at line 1=0Aat org.apache.xmlrpc.XmlRpcClientRe= sponseProcessor.decodeException(XmlRpcClientResponseProcessor.java:104)=0A= at org.apache.xmlrpc.XmlRpcClientResponseProcessor.decodeResponse(XmlRpcCl= ientResponseProcessor.java:71)=0Aat org.apache.xmlrpc.XmlRpcClientWorker.e= xecute(XmlRpcClientWorker.java:73)=0Aat org.apache.xmlrpc.XmlRpcClient.exe= cute(XmlRpcClient.java:194)=0Aat org.apache.xmlrpc.XmlRpcClient.execute(Xm= lRpcClient.java:185)=0Aat org.apache.xmlrpc.XmlRpcClient.execute(XmlRpcCli= ent.java:178)=0Aat org.apache.oodt.cas.filemgr.system.XmlRpcFileManagerCli= ent.complexQuery(XmlRpcFileManagerClient.java:990)=0Aat org.apache.oodt.ca= s.filemgr.cli.action.AbstractQueryCliAction.execute(AbstractQueryCliAction= .java:75)=0Aat org.apache.oodt.cas.cli.CmdLineUtility.execute(CmdLineUtili= ty.java:331)=0Aat org.apache.oodt.cas.cli.CmdLineUtility.run(CmdLineUtilit= y.java:187)=0Aat org.apache.oodt.cas.filemgr.system.XmlRpcFileManagerClien= t.main(XmlRpcFileManagerClient.java:1323)=0AERROR: Failed to perform sql q= uery : sortBy 'null', outputFormat 'null', and delimiter '=0A', filterAlgo= r 'null', startDateTimeMetKey 'null', endDateTimeMetKey 'null', priorityMe= tKey 'null', null' : java.lang.Exception: org.apache.oodt.cas.filemgr.stru= cts.exceptions.CatalogException: Failed to perform complex query : You hav= e an error in your SQL syntax; check the manual that corresponds to your M= ySQL server version for the right syntax to use near 'INTERSECT (SELECT DI= STINCT product_id FROM L0a_Radar_metadata WHERE element_id =3D' at line 1=0A= =0ALet me know if you'd like for me to file a bug for this.=0A=0AThanks,=0A= Mike=0A=0AFrom: Brian Foster =0AReply-To: =0ADate: Mon, 14 May 2012 22:28:58 +0000=0ATo: =0ASubject: Re: Query Tool Bugs?=0A=0Ahey mike,=0A=0Afirst pass over t= he policy files look good... however, is the way you posted the query the = way you are running it?=0A=0A./query_tool --url=A0http://localhost:9000=A0= --sql -query "SELECT * FROM L0a_Radar WHERE "RangeBeginningDate>'2007-01-0= 1' AND RangeBeginningTime>'12:00:00.000Z'""=0A=0Ayou have an extra set of = double quotes around the where clause... try removing those=0A=0A-brian=0A= =0AOn May 14, 2012, at 09:32 AM, "Cayanan, Michael D (388J)" wrote:=0A=0AHey Chris,=0A=0AOn 5/12/12 11:25 AM, "Matt= mann, Chris A (388J)"=0A wrote:=0A=0A>Hey M= ike,=0A>=0A>On May 11, 2012, at 6:07 AM, Cayanan, Michael D (388J) wrote:=0A= >=0A>>>> =0A>>>> org.apache.oodt.cas.filemgr.structs.exceptions.CatalogExc= eption:=0A>>>>Failed=0A>>>> to perform complex query : You have an error i= n your SQL syntax; check=0A>>>> the manual that corresponds to your MySQL = server version for the right=0A>>>> syntax to use near 'INTERSECT (SELECT = DISTINCT product_id FROM=0A>>>> L0a_Radar_metadata WHERE element_id =3D' a= t line 1=0A>>>> at =0A>>>> =0A>>>>org.apache.oodt.cas.filemgr.system.XmlRp= cFileManagerClient.complexQuery=0A>>>>(X=0A>>>> mlRpcFileManagerClient.jav= a:958)=0A>>>> at =0A>>>> =0A>>>>org.apache.oodt.cas.filemgr.tools.QueryToo= l.performSqlQuery(QueryTool.j=0A>>>>av=0A>>>> a:251)=0A>>>> at =0A>>>>org.= apache.oodt.cas.filemgr.tools.QueryTool.main(QueryTool.java:241)=0A>>> =0A= >>> Just out of curiosity, is that correct ISO 8601 date/time format? Look= s=0A>>> like a partial one, missing the timezone do you think that might=0A= >>> affect ir?=0A>> =0A>> I talked with Rishi regarding this and he recomm= ended that the date and=0A>> time be split when performing a query. Reason= being is that the query=0A>>tool=0A>> blows up when trying to compare dat= etime values. He mentioned that he=0A>> tried querying against ISO 8601 da= te/time values before and it didn't=0A>>work=0A>> for him and the way arou= nd it was to split it up. I think behind the=0A>> scenes, the query tool i= s actually doing an ascii comparison, which=0A>>might=0A>> be why the tool= might be having performance issues?=0A>=0A>Gotcha, that might help, yes. = I was thinking: what is your repository=0A>manager,=0A>and catalog combina= tion? If you are using e.g., a DataSourceCatalog,=0A>with the XMLRepositor= yManager, you'll need to turn on the quoteFields=0A>option in the filemgr.= properties for the DataSourceCatalog. This is=0A>because,=0A>in these scen= arios, the identifier for elementIds is a string, compared=0A>to a =0A>num= ber (which would be the case if you used the=0A>DataSourceRepositoryManage= r --=0A>the short answer there is don't, it's not as well maintained as th= e XML=0A>one).=0A=0AMy repository manager and catalog combination is defin= ed as follows in the=0Afilemgr.properties:=0A=0Afilemgr.repository.factory= =3Dorg.apache.oodt.cas.filemgr.repository.XMLReposi=0AtoryManagerFactory=0A= =0Afilemgr.catalog.factory=3Dorg.apache.oodt.cas.filemgr.catalog.DataSourc= eCatal=0AogFactory=0A=0Aorg.apache.oodt.cas.filemgr.catalog.datasource.quo= teFields=3Dtrue=0A=0A=0A=0A>=0A>> =0A>>> =0A>>>> =0A>>>> I tried surroundi= ng the entire condition with quotes, but still no=0A>>>>luck:=0A>>>> =0A>>= >> ./query_tool --url http://localhost:9000 --sql -query "SELECT * FROM=0A= >>>> L0a_Radar WHERE "RangeBeginningDate>'2007-01-01' AND=0A>>>> RangeBegi= nningTime>'12:00:00.000Z'""=0A>>>> Ambiguous output redirect.=0A>>>> =0A>>= >> I'm assuming this is a syntax thing, although I don't know what the=0A>= >>> tool is expecting.=0A>>> =0A>>> Did you check the code in SVN?=0A>> =0A= >> I'm running 0.3 of the code. Does the trunk fix this? I have the code=0A= >> checked out onto my local machine. I can certainly build the trunk and=0A= >>see=0A>> if I get the same results.=0A>=0A>I think there is a fix for so= mething similar to this in the trunk (as=0A>bfoster mentioned),=0A>but thi= nking about this more, I bet you're having the quoteFields=0A>problem, per= =0A>above. Scope it out and let me know.=0A=0ABased on the properties spec= ified above, the quoteFields option is turned=0Aon. So I think the propert= ies are set correctly, no?=0AIt'll be interesting to see if Brian was able= to find an error in the=0Apolicy files that I sent in the previous e-mail= .=0A=0A>=0A>> =0A>>> =0A>>>> =0A>>>> My second issue that I'm running into= is in regards to querying of=0A>>>> dates. I tried the following query be= low and got the following output:=0A>>>> =0A>>>> ./query_tool --url http:/= /localhost:9000 --sql -query "SELECT * FROM=0A>>>> L0a_Radar WHERE RangeBe= ginningDate>'2007-03-02'"=0A>>>> log4j:WARN No appenders could be found fo= r logger=0A>>>> (org.apache.commons.httpclient.HttpClient).=0A>>>> log4j:W= ARN Please initialize the log4j system properly.=0A>>>> log4j:WARN See htt= p://logging.apache.org/log4j/1.2/faq.html#noconfig=0A>>>> for more info.=0A= >>>> Exception in thread "main" java.lang.StringIndexOutOfBoundsException:= =0A>>>> String index out of range: -1=0A>>>> at =0A>>>> =0A>>>>java.lang.A= bstractStringBuilder.substring(AbstractStringBuilder.java:88=0A>>>>1)=0A>>= >> at java.lang.StringBuffer.substring(StringBuffer.java:416)=0A>>>> at =0A= >>>> =0A>>>>org.apache.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(Qu= eryTool.j=0A>>>>av=0A>>>> a:255)=0A>>>> at =0A>>>>org.apache.oodt.cas.file= mgr.tools.QueryTool.main(QueryTool.java:241)=0A>>>> =0A>>>> For this parti= cular product, I have 1 product in my catalog where the=0A>>>> RangeBeginn= ingDate is equal to '2007-03-01'. Not sure if that factors=0A>>>> into why= an exception is being thrown here. When I use an earlier date=0A>>>> on m= y query, the tool returns a result as expected:=0A>>>> =0A>>>> ./query_too= l --url http://localhost:9000 --sql -query "SELECT * FROM=0A>>>> L0a_Radar= WHERE RangeBeginningDate>'2007-01-01'"=0A>>>> log4j:WARN No appenders cou= ld be found for logger=0A>>>> (org.apache.commons.httpclient.HttpClient).=0A= >>>> log4j:WARN Please initialize the log4j system properly.=0A>>>> log4j:= WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig=0A>>>> for = more info.=0A>>>> =0A>>>> =0A>>>>/Users/mcayanan/smap/staging,2007-03-01,2= 3:30:25.000Z,314,L0a_Radar,V20=0A>>>>51=0A>>>> =0A>>>>7SGS0706023302501.VC= D,V20517SGS0706023302501.VCD,2012-05-08T14:27:59.38=0A>>>>5-=0A>>>> 07:00,= L0a_Radar,23:30:25.000Z,2007-03-01=0A>>> =0A>>> Interesting! Did you scope= the code to see if there's a RangeQuery=0A>>>issue?=0A>>> =0A>>> Feel fre= e to file a bug and would love you to investigate!=0A>> =0A>> I haven't di= ved into the code, but will certainly do this as SMAP will=0A>> need these= capabilities. I will file a bug if it turns out that this is=0A>> indeed = a bug.=0A>=0A>Great Mike, thanks.=0A>=0A>Cheers,=0A>Chris=0A=0AThanks,=0AM= ike=0A=0A>=0A>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++= ++++++=0A>Chris Mattmann, Ph.D.=0A>Senior Computer Scientist=0A>NASA Jet P= ropulsion Laboratory Pasadena, CA 91109 USA=0A>Office: 171-266B, Mailstop:= 171-246=0A>Email: chris.a.mattmann@nasa.gov=0A>WWW: http://sunset.usc.edu= /~mattmann/=0A>+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++= +++++++=0A>Adjunct Assistant Professor, Computer Science Department=0A>Uni= versity of Southern California, Los Angeles, CA 90089 USA=0A>+++++++++++++= +++++++++++++++++++++++++++++++++++++++++++++++++++++=0A>=0A=0A= --Boundary_(ID_5OfKtEW09ZRKCS/FwJN1Wg) Content-type: multipart/related; boundary="Boundary_(ID_2jBWR5ksYNfpV9tJR0dsRQ)"; type="text/html" --Boundary_(ID_2jBWR5ksYNfpV9tJR0dsRQ) Content-type: text/html; CHARSET=US-ASCII Content-transfer-encoding: quoted-printable

hey mike,

You putting a space= between the operators? its hard to tell in emails, stuff gets normalized = sometimes.

RangeBeginningDate > '2007-01-01'=

Should be a space before and after >

each value should start and end with single quote.
<= div>
-brian

On May 17, 2012, at 04:18 PM, "Cay= anan, Michael D (388J)" <michael.d.cayanan@jpl.nasa.gov> wrote:
<= br>
Hey Brian,

I checked out the wengine-b= ranch filemgr and unfortunately, it still didn't work. I tried a query usi= ng the wengine branch's query_tool since this version of the filemgr-clien= t didn't have the ability to query.

In any case, = I took the liberty of diving into the Query Tool code to see if I can find= out more as to what is going on. So this is what I found:

<= /div>
Surrounding single quotes around each of the conditions got me f= urther into the code. So, when I entered the query as follows,
<= br>
SELECT * FROM L0a_Radar WHERE 'RangeBeginningDate>2007-01= -01' AND 'RangeBeginningTime>12:00:00Z'

The SQLParser = class liked the syntax and it produced the following post fix statement:

Po= st Fix Statement: ['RangeBeginningDate>2007-01-01', 'RangeBeginnin= gTime>12:00:00Z', AND]

I think from here, it creates some Boolean Query Criteria with the follo= wing content:

Boolean Qu= ery Criteria: AND('RangeBeginningTime:[12:00:00Z' TO null], 'RangeBeg= inningDate:[2007-01-01' TO null])

I noticed t= hat there are unmatched single quotes around the date and time values. Not= sure how that came about. The tool still threw an exception. I'm guessing= it's because of the unmatched single quotes.

I t= ried surrounding the date and time values with single quotes as well, but = the SQLParser class didn't like that either:

SELE= CT * FROM L0a_Radar WHERE 'RangeBeginningDate>'2007-01-01'' AND 'RangeB= eginningTime>'12:00:00Z''


-Mike

From: Brian Foster <holenoter@mac.com&= gt;
Reply-To: <user@oodt.apache.org>
Date: = Thu, 17 May 2012 18:40:20 +0000
To: <user@oo= dt.apache.org>
Subject: Re: Query Tool Bugs?

hey mike,

w= ould you mind trying to run this query using the wengine-branch filemgr (<= a href=3D"https://svn.apache.org/repos/asf/oodt/branches/wengine-branch/fi= lemgr/" data-mce-href=3D"https://svn.apache.org/repos/asf/oodt/branches/we= ngine-branch/filemgr/">https://svn.apache.org/repos/asf/oodt/branches/weng= ine-branch/filemgr/)... there are a lot of bug fixes floating around i= n both Lucene and DataSource Catalogs in that branch... if it works with t= hat then we know it is a bug with trunk.

-brian
On May 16, 2012, at 07:26 AM, "Cayanan, Michael D (388J)" <michael.d.cayanan@jpl.nasa.gov> wrote:<= br>
Hey Brian,
Ya, I tried the query with and without the double quotes surro= unding the where clause and they both didn't work for me.

In any case, I've built the latest file manager in the trunk and = it fixed that StringIndexOutofBoundsException issue I was seeing. Thanks!<= /div>

Out of curiosity, I tried using the latest File M= anager Client tool with the query with multiple conditions to see if I cou= ld get a result, but it threw an exception as well:

./filemgr-client -u http://localhost:9000 -q "SELECT * FROM L0= a_Radar WHERE RangeBeginningDate>'2007-01-01' AND RangeBeginningTime>= ;'12:00:00Z'" -op -sqlQuery
May 16, 2012 7:20:53 AM org.apache.o= odt.cas.filemgr.system.XmlRpcFileManagerClient <init>
INFO= : Loading File Manager Configuration Properties from: [../etc/filemgr.prop= erties]
org.apache.xmlrpc.XmlRpcException: java.lang.Exception: = org.apache.oodt.cas.filemgr.structs.exceptions.CatalogException: Failed to= perform complex query : You have an error in your SQL syntax; check the m= anual that corresponds to your MySQL server version for the right syntax t= o use near 'INTERSECT (SELECT DISTINCT product_id FROM L0a_Radar_metadata = WHERE element_id =3D' at line 1
at org.apache.xmlrpc.XmlRpcClien= tResponseProcessor.decodeException(XmlRpcClientResponseProcessor.java:104)=
at org.apache.xmlrpc.XmlRpcClientResponseProcessor.decodeRespon= se(XmlRpcClientResponseProcessor.java:71)
at org.apache.xmlrpc.X= mlRpcClientWorker.execute(XmlRpcClientWorker.java:73)
at org.apa= che.xmlrpc.XmlRpcClient.execute(XmlRpcClient.java:194)
at org.ap= ache.xmlrpc.XmlRpcClient.execute(XmlRpcClient.java:185)
at org.a= pache.xmlrpc.XmlRpcClient.execute(XmlRpcClient.java:178)
at org.= apache.oodt.cas.filemgr.system.XmlRpcFileManagerClient.complexQuery(XmlRpc= FileManagerClient.java:990)
at org.apache.oodt.cas.filemgr.cli.a= ction.AbstractQueryCliAction.execute(AbstractQueryCliAction.java:75)
=
at org.apache.oodt.cas.cli.CmdLineUtility.execute(CmdLineUtility.java= :331)
at org.apache.oodt.cas.cli.CmdLineUtility.run(CmdLineUtili= ty.java:187)
at org.apache.oodt.cas.filemgr.system.XmlRpcFileMan= agerClient.main(XmlRpcFileManagerClient.java:1323)
ERROR: Failed= to perform sql query : sortBy 'null', outputFormat 'null', and delimiter = '
', filterAlgor 'null', startDateTimeMetKey 'null', endDateTime= MetKey 'null', priorityMetKey 'null', null' : java.lang.Exception: org.apa= che.oodt.cas.filemgr.structs.exceptions.CatalogException: Failed to perfor= m complex query : You have an error in your SQL syntax; check the manual t= hat corresponds to your MySQL server version for the right syntax to use n= ear 'INTERSECT (SELECT DISTINCT product_id FROM L0a_Radar_metadata WHERE e= lement_id =3D' at line 1

Let me know if you= 'd like for me to file a bug for this.

Thanks,
Mike

From: Brian Foster <holenoter@mac.com>
Reply-To: <= user@oodt.apache.org>
Date: Mon, 14 May 2012 22= :28:58 +0000
To: <user@oodt.apache.org>
= Sub= ject: Re: Query Tool Bugs?

= hey mike,

first pass over the policy files look g= ood... however, is the way you posted the query the way you are running it= ?

./query_tool --url http://localhost:9000 --sql -quer= y "SELECT * FROM L0a_Radar WHERE "RangeBeginningDate>'2007-01-01' AND R= angeBeginningTime>'12:00:00.000Z'""

you have an extra set of double quotes around the where cla= use... try removing those
=
-brian

On M= ay 14, 2012, at 09:32 AM, "Cayanan, Michael D (388J)" <michael.d.cayanan@jpl.nasa.gov> wrote:

Hey Chris,

On 5/12/12 11:25 AM, "Mattmann, Chris A (388J)= "
<chris.a.mattmann@jpl.nasa.gov&g= t; wrote:

>Hey Mike,
>
>On May 11, 2012, at 6:07= AM, Cayanan, Michael D (388J) wrote:
>
>>>>
&= gt;>>> org.apache.oodt.cas.filemgr.structs.exceptions.CatalogExce= ption:
>>>>Failed
>>>> to perform complex = query : You have an error in your SQL syntax; check
>>>> t= he manual that corresponds to your MySQL server version for the right
= >>>> syntax to use near 'INTERSECT (SELECT DISTINCT product_id= FROM
>>>> L0a_Radar_metadata WHERE element_id =3D' at lin= e 1
>>>> at
>>>>
>>>>org= .apache.oodt.cas.filemgr.system.XmlRpcFileManagerClient.complexQuery
&= gt;>>>(X
>>>> mlRpcFileManagerClient.java:958) >>>> at
>>>>
>>>>org.apach= e.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.j
>>= ;>>av
>>>> a:251)
>>>> at
>&= gt;>>org.apache.oodt.cas.filemgr.tools.QueryTool.main(QueryTool.java= :241)
>>>
>>> Just out of curiosity, is that co= rrect ISO 8601 date/time format? Looks
>>> like a partial one= , missing the timezone do you think that might
>>> affect ir?=
>>
>> I talked with Rishi regarding this and he reco= mmended that the date and
>> time be split when performing a que= ry. Reason being is that the query
>>tool
>> blows up = when trying to compare datetime values. He mentioned that he
>> = tried querying against ISO 8601 date/time values before and it didn't
= >>work
>> for him and the way around it was to split it up= . I think behind the
>> scenes, the query tool is actually doing= an ascii comparison, which
>>might
>> be why the tool= might be having performance issues?
>
>Gotcha, that might h= elp, yes. I was thinking: what is your repository
>manager,
>= ;and catalog combination? If you are using e.g., a DataSourceCatalog,
= >with the XMLRepositoryManager, you'll need to turn on the quoteFields<= br> >option in the filemgr.properties for the DataSourceCatalog. This i= s
>because,
>in these scenarios, the identifier for elementI= ds is a string, compared
>to a
>number (which would be the = case if you used the
>DataSourceRepositoryManager --
>the sh= ort answer there is don't, it's not as well maintained as the XML
>= one).

My repository manager and catalog combination is defined as= follows in the
filemgr.properties:

filemgr.repository.factor= y=3Dorg.apache.oodt.cas.filemgr.repository.XMLReposi
toryManagerFactor= y

filemgr.catalog.factory=3Dorg.apache.oodt.cas.filemgr.catalog.D= ataSourceCatal
ogFactory

org.apache.oodt.cas.filemgr.catalog.= datasource.quoteFields=3Dtrue



>
>>
&g= t;>>
>>>>
>>>> I tried surrounding= the entire condition with quotes, but still no
>>>>luck:<= br> >>>>
>>>> ./query_tool --url http://loca= lhost:9000 --sql -query "SELECT * FROM
>>>> L0a_Radar = WHERE "RangeBeginningDate>'2007-01-01' AND
>>>> RangeBe= ginningTime>'12:00:00.000Z'""
>>>> Ambiguous output red= irect.
>>>>
>>>> I'm assuming this is a s= yntax thing, although I don't know what the
>>>> tool is e= xpecting.
>>>
>>> Did you check the code in SVN= ?
>>
>> I'm running 0.3 of the code. Does the trunk f= ix this? I have the code
>> checked out onto my local machine. I= can certainly build the trunk and
>>see
>> if I get t= he same results.
>
>I think there is a fix for something sim= ilar to this in the trunk (as
>bfoster mentioned),
>but thin= king about this more, I bet you're having the quoteFields
>problem,= per
>above. Scope it out and let me know.

Based on the pr= operties specified above, the quoteFields option is turned
on. So I th= ink the properties are set correctly, no?
It'll be interesting to see = if Brian was able to find an error in the
policy files that I sent in = the previous e-mail.

>
>>
>>>
>= ;>>>
>>>> My second issue that I'm running into = is in regards to querying of
>>>> dates. I tried the follo= wing query below and got the following output:
>>>>
&= gt;>>> ./query_tool --url http://localhost:9000 --sql -query= "SELECT * FROM
>>>> L0a_Radar WHERE RangeBeginningDate>= ;'2007-03-02'"
>>>> log4j:WARN No appenders could be found= for logger
>>>> (org.apache.commons.httpclient.HttpClient= ).
>>>> log4j:WARN Please initialize the log4j system prop= erly.
>>>> log4j:WARN See http://logging.apache.org/log4j/1.2/faq.h= tml#noconfig
>>>> for more info.
>>>> = Exception in thread "main" java.lang.StringIndexOutOfBoundsException:
= >>>> String index out of range: -1
>>>> at >>>>
>>>>java.lang.AbstractStringBuilder.su= bstring(AbstractStringBuilder.java:88
>>>>1)
>>&= gt;> at java.lang.StringBuffer.substring(StringBuffer.java:416)
>= ;>>> at
>>>>
>>>>org.apache.ood= t.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.j
>>>= >av
>>>> a:255)
>>>> at
>>&g= t;>org.apache.oodt.cas.filemgr.tools.QueryTool.main(QueryTool.java:241)=
>>>>
>>>> For this particular product, I= have 1 product in my catalog where the
>>>> RangeBeginnin= gDate is equal to '2007-03-01'. Not sure if that factors
>>>&= gt; into why an exception is being thrown here. When I use an earlier date=
>>>> on my query, the tool returns a result as expected:<= br> >>>>
>>>> ./query_tool --url http://loca= lhost:9000 --sql -query "SELECT * FROM
>>>> L0a_Radar = WHERE RangeBeginningDate>'2007-01-01'"
>>>> log4j:WARN = No appenders could be found for logger
>>>> (org.apache.co= mmons.httpclient.HttpClient).
>>>> log4j:WARN Please initi= alize the log4j system properly.
>>>> log4j:WARN See http://logging= .apache.org/log4j/1.2/faq.html#noconfig
>>>> for more = info.
>>>>
>>>>
>>>>/Use= rs/mcayanan/smap/staging,2007-03-01,23:30:25.000Z,314,L0a_Radar,V20
&g= t;>>>51
>>>>
>>>>7SGS07060233025= 01.VCD,V20517SGS0706023302501.VCD,2012-05-08T14:27:59.38
>>>&= gt;5-
>>>> 07:00,L0a_Radar,23:30:25.000Z,2007-03-01
&g= t;>>
>>> Interesting! Did you scope the code to see if= there's a RangeQuery
>>>issue?
>>>
>>= ;> Feel free to file a bug and would love you to investigate!
>&= gt;
>> I haven't dived into the code, but will certainly do thi= s as SMAP will
>> need these capabilities. I will file a bug if = it turns out that this is
>> indeed a bug.
>
>Grea= t Mike, thanks.
>
>Cheers,
>Chris

Thanks, Mike

>
>+++++++++++++++++++++++++++++++++++++++++++++= +++++++++++++++++++++
>Chris Mattmann, Ph.D.
>Senior Compute= r Scientist
>NASA Jet Propulsion Laboratory Pasadena, CA 91109 USA<= br> >Office: 171-266B, Mailstop: 171-246
>Email: chris.a.mattmann@nasa.gov
>WWW: = http://sunset.usc.edu/~mattmann/
>+++++++++++++++++++++++++++++= +++++++++++++++++++++++++++++++++++++
>Adjunct Assistant Professor,= Computer Science Department
>University of Southern California, Lo= s Angeles, CA 90089 USA
>++++++++++++++++++++++++++++++++++++++++++= ++++++++++++++++++++++++
>

<= /div>
= --Boundary_(ID_2jBWR5ksYNfpV9tJR0dsRQ)-- --Boundary_(ID_5OfKtEW09ZRKCS/FwJN1Wg)--