Hi Chris, Comments are below....
On 5/10/12 6:22 PM, "Mattmann, Chris A (388J)" <[email protected]> wrote: >Hi Mike, > >On May 10, 2012, at 1:28 PM, Cayanan, Michael D (388J) wrote: > >> Hi All, >> >> I'm having several issues with the Query Tool and wondering if anyone >>has run into these issues before: >> >> First, I'm having an issue when giving the Query Tool a query >>containing multiple conditions: >> >> Below is a command-line run of my query: >> >> ./query_tool --url http://localhost:9000 --sql -query "SELECT * FROM >>L0a_Radar WHERE RangeBeginningDate>'2007-01-01' AND >>RangeBeginningTime>'12:00:00.000Z'" >> log4j:WARN No appenders could be found for logger >>(org.apache.commons.httpclient.HttpClient). >> log4j:WARN Please initialize the log4j system properly. >> log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig >>for more info. >> 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 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 =' at line 1 >> at >>org.apache.xmlrpc.XmlRpcClientResponseProcessor.decodeException(XmlRpcCli >>entResponseProcessor.java:104) >> at >>org.apache.xmlrpc.XmlRpcClientResponseProcessor.decodeResponse(XmlRpcClie >>ntResponseProcessor.java:71) >> at >>org.apache.xmlrpc.XmlRpcClientWorker.execute(XmlRpcClientWorker.java:73) >> at org.apache.xmlrpc.XmlRpcClient.execute(XmlRpcClient.java:194) >> at org.apache.xmlrpc.XmlRpcClient.execute(XmlRpcClient.java:185) >> at org.apache.xmlrpc.XmlRpcClient.execute(XmlRpcClient.java:178) >> at >>org.apache.oodt.cas.filemgr.system.XmlRpcFileManagerClient.complexQuery(X >>mlRpcFileManagerClient.java:952) >> at >>org.apache.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.jav >>a:251) >> at org.apache.oodt.cas.filemgr.tools.QueryTool.main(QueryTool.java:241) >> Exception in thread "main" >>org.apache.oodt.cas.filemgr.structs.exceptions.CatalogException: >>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 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 =' at line 1 >> at >>org.apache.oodt.cas.filemgr.system.XmlRpcFileManagerClient.complexQuery(X >>mlRpcFileManagerClient.java:958) >> at >>org.apache.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.jav >>a:251) >> at org.apache.oodt.cas.filemgr.tools.QueryTool.main(QueryTool.java:241) > >Just out of curiosity, is that correct 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 recommended that the date and time be split when performing a query. 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? > >> >> I tried surrounding the entire condition with quotes, but still no luck: >> >> ./query_tool --url http://localhost:9000 --sql -query "SELECT * FROM >>L0a_Radar WHERE "RangeBeginningDate>'2007-01-01' AND >>RangeBeginningTime>'12:00:00.000Z'"" >> Ambiguous output redirect. >> >> I'm assuming this is a syntax thing, although I don't know what the >>tool is expecting. > >Did you check the code in SVN? I'm running 0.3 of the code. Does the trunk fix this? I have the code checked out onto my local machine. I can certainly build the trunk and see if I get the same results. > >> >> My second issue that I'm running into is in regards to querying of >>dates. I tried the following query below and got the following output: >> >> ./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 properly. >> log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig >>for more info. >> Exception in thread "main" java.lang.StringIndexOutOfBoundsException: >>String index out of range: -1 >> at >>java.lang.AbstractStringBuilder.substring(AbstractStringBuilder.java:881) >> at java.lang.StringBuffer.substring(StringBuffer.java:416) >> at >>org.apache.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.jav >>a:255) >> at org.apache.oodt.cas.filemgr.tools.QueryTool.main(QueryTool.java:241) >> >> For this particular product, I have 1 product in my catalog where the >>RangeBeginningDate is equal to '2007-03-01'. Not sure if that factors >>into why an exception is being thrown here. When I use an earlier date >>on my query, the tool returns a result as expected: >> >> ./query_tool --url http://localhost:9000 --sql -query "SELECT * FROM >>L0a_Radar WHERE RangeBeginningDate>'2007-01-01'" >> log4j:WARN No appenders could be found for logger >>(org.apache.commons.httpclient.HttpClient). >> log4j:WARN Please initialize the log4j system properly. >> log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig >>for more info. >> >>/Users/mcayanan/smap/staging,2007-03-01,23:30:25.000Z,314,L0a_Radar,V2051 >>7SGS0706023302501.VCD,V20517SGS0706023302501.VCD,2012-05-08T14:27:59.385- >>07:00,L0a_Radar,23:30:25.000Z,2007-03-01 > >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! I haven't dived into the code, but will certainly do this as SMAP will need these capabilities. I will file a bug if it turns out that this is indeed a bug. Thanks, Mike > >Cheers, >Chris > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >Chris Mattmann, Ph.D. >Senior Computer Scientist >NASA Jet Propulsion Laboratory Pasadena, CA 91109 USA >Office: 171-266B, Mailstop: 171-246 >Email: [email protected] >WWW: http://sunset.usc.edu/~mattmann/ >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >Adjunct Assistant Professor, Computer Science Department >University of Southern California, Los Angeles, CA 90089 USA >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >
