oodt-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Cayanan, Michael D (388J)" <michael.d.caya...@jpl.nasa.gov>
Subject Re: Query Tool Bugs?
Date Mon, 21 May 2012 17:02:30 GMT
Hey Brian,

Yep, I tried the query with spaces before and after > and also surrounded each value with
a single quote and it didn't work for me. I also put the query into the shell script as you've
suggested and still no luck.

The exception I'm getting is this:

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(XmlRpcClientResponseProcessor.java:104)
at org.apache.xmlrpc.XmlRpcClientResponseProcessor.decodeResponse(XmlRpcClientResponseProcessor.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(XmlRpcFileManagerClient.java:945)
at org.apache.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.java:250)
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(XmlRpcFileManagerClient.java:951)
at org.apache.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.java:250)
at org.apache.oodt.cas.filemgr.tools.QueryTool.main(QueryTool.java:241)
Caused by: 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(XmlRpcClientResponseProcessor.java:104)
at org.apache.xmlrpc.XmlRpcClientResponseProcessor.decodeResponse(XmlRpcClientResponseProcessor.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(XmlRpcFileManagerClient.java:945)
... 2 more

Attached is the query tool script, which contains the query I'm trying. It appears that this
is a bug. Since query tool has been deprecated in the trunk in favor of the super new file
manager client tool, then I'll go ahead and file a JIRA issue for this against the file manager.

In the meantime, I'll definitely look more into this and see if I can find out where the bug
is at.

Thanks for your help!

-Mike

From: Brian Foster <holenoter@mac.com<mailto:holenoter@mac.com>>
Reply-To: <user@oodt.apache.org<mailto:user@oodt.apache.org>>
Date: Fri, 18 May 2012 00:51:07 +0000
To: <user@oodt.apache.org<mailto:user@oodt.apache.org>>
Subject: Re: Query Tool Bugs?


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.

-brian

On May 17, 2012, at 04:18 PM, "Cayanan, Michael D (388J)" <michael.d.cayanan@jpl.nasa.gov<mailto:michael.d.cayanan@jpl.nasa.gov>>
wrote:

Hey Brian,

I checked out the wengine-branch filemgr and unfortunately, 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 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:

Surrounding single quotes around each of the conditions got me further into the code. So,
when I entered the query as follows,

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:

Post Fix Statement: ['RangeBeginningDate>2007-01-01', 'RangeBeginningTime>12:00:00Z',
AND]

I think from here, it creates some Boolean Query Criteria with the following content:

Boolean Query Criteria: AND('RangeBeginningTime:[12:00:00Z' TO null], 'RangeBeginningDate:[2007-01-01'
TO null])

I noticed that 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 tried surrounding the date and time values with single quotes as well, but the SQLParser
class didn't like that either:

SELECT * FROM L0a_Radar WHERE 'RangeBeginningDate>'2007-01-01'' AND 'RangeBeginningTime>'12:00:00Z''


-Mike

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


hey mike,

would you mind trying to run this query using the wengine-branch filemgr (https://svn.apache.org/repos/asf/oodt/branches/wengine-branch/filemgr/)...
there are a lot of bug fixes 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.

-brian

On May 16, 2012, at 07:26 AM, "Cayanan, Michael D (388J)" <michael.d.cayanan@jpl.nasa.gov<mailto:michael.d.cayanan@jpl.nasa.gov>>
wrote:

Hey Brian,

Ya, I tried the query with and without the double quotes surrounding 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!

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

./filemgr-client -u http://localhost:9000 -q "SELECT * FROM L0a_Radar WHERE RangeBeginningDate>'2007-01-01'
AND RangeBeginningTime>'12:00:00Z'" -op -sqlQuery
May 16, 2012 7:20:53 AM org.apache.oodt.cas.filemgr.system.XmlRpcFileManagerClient <init>
INFO: Loading File Manager Configuration Properties from: [../etc/filemgr.properties]
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(XmlRpcClientResponseProcessor.java:104)
at org.apache.xmlrpc.XmlRpcClientResponseProcessor.decodeResponse(XmlRpcClientResponseProcessor.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(XmlRpcFileManagerClient.java:990)
at org.apache.oodt.cas.filemgr.cli.action.AbstractQueryCliAction.execute(AbstractQueryCliAction.java:75)
at org.apache.oodt.cas.cli.CmdLineUtility.execute(CmdLineUtility.java:331)
at org.apache.oodt.cas.cli.CmdLineUtility.run(CmdLineUtility.java:187)
at org.apache.oodt.cas.filemgr.system.XmlRpcFileManagerClient.main(XmlRpcFileManagerClient.java:1323)
ERROR: Failed to perform sql query : sortBy 'null', outputFormat 'null', and delimiter '
', filterAlgor 'null', startDateTimeMetKey 'null', endDateTimeMetKey 'null', priorityMetKey
'null', null' : 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

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

Thanks,
Mike

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

hey mike,

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

./query_tool --url http://localhost:9000<http://localhost:9000/> --sql -query "SELECT
* FROM L0a_Radar WHERE "RangeBeginningDate>'2007-01-01' AND RangeBeginningTime>'12:00:00.000Z'""

you have an extra set of double quotes around the where clause... try removing those

-brian

On May 14, 2012, at 09:32 AM, "Cayanan, Michael D (388J)" <michael.d.cayanan@jpl.nasa.gov<mailto: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<mailto:chris.a.mattmann@jpl.nasa.gov>> wrote:

>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.j
>>>>av
>>>> 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).

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

filemgr.repository.factory=org.apache.oodt.cas.filemgr.repository.XMLReposi
toryManagerFactory

filemgr.catalog.factory=org.apache.oodt.cas.filemgr.catalog.DataSourceCatal
ogFactory

org.apache.oodt.cas.filemgr.catalog.datasource.quoteFields=true



>
>>
>>>
>>>>
>>>> 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.

Based on the properties specified above, the quoteFields option is turned
on. So I think 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 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:88
>>>>1)
>>>> at java.lang.StringBuffer.substring(StringBuffer.java:416)
>>>> at
>>>>
>>>>org.apache.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.j
>>>>av
>>>> 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,V20
>>>>51
>>>>
>>>>7SGS0706023302501.VCD,V20517SGS0706023302501.VCD,2012-05-08T14:27:59.38
>>>>5-
>>>> 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

Thanks,
Mike

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


Mime
View raw message