Hey Mike, On May 11, 2012, at 6:07 AM, Cayanan, Michael D (388J) wrote:
>>> >>> 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? Gotcha, that might help, 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 option in the filemgr.properties for the DataSourceCatalog. This is because, in these scenarios, the identifier for elementIds is a string, compared to a number (which would be the case if you used the DataSourceRepositoryManager -- the short answer there is don't, it's not as well maintained as the XML one). > >> >>> >>> 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. I think there is a fix for something similar to this in the trunk (as bfoster mentioned), but thinking about this more, I bet you're having the quoteFields problem, per above. Scope it out and let me know. > >> >>> >>> 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. Great Mike, thanks. 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 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
