Mailing-List: contact user-help@oodt.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@oodt.apache.org
Received-SPF: pass (athena.apache.org: local policy)
From: "Mattmann, Chris A (388J)" <chris.a.mattmann@jpl.nasa.gov>
To: "<user@oodt.apache.org>" <user@oodt.apache.org>
Subject: Re: Query Tool Bugs?
Thread-Topic: Query Tool Bugs?
Thread-Index: AQHNLutx/sA73Fkv3E2/HXYlcu2ygZbEQLCAgACB/ICAAi3vAA==
Date: Sat, 12 May 2012 18:25:21 +0000
Message-ID: <58E08318-7E70-47E5-9ABA-917148220643@jpl.nasa.gov>
References: <CBD2850C.C43B%michael.cayanan@jpl.nasa.gov>
In-Reply-To: <CBD2850C.C43B%michael.cayanan@jpl.nasa.gov>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-ID: <5991C8DA86DE5E499C4A8089B2119A48@ad.jpl>
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

Hey Mike,

On May 11, 2012, at 6:07 AM, Cayanan, Michael D (388J) wrote:

>>>=20
>>> 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 =3D' at line 1
>>> at=20
>>> org.apache.oodt.cas.filemgr.system.XmlRpcFileManagerClient.complexQuery=
(X
>>> mlRpcFileManagerClient.java:958)
>>> at=20
>>> 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)
>>=20
>> 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?
>=20
> 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 too=
l
> 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 wor=
k
> 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 manag=
er,
and catalog combination? If you are using e.g., a DataSourceCatalog,
with the XMLRepositoryManager, you'll need to turn on the quoteFields=20
option in the filemgr.properties for the DataSourceCatalog. This is because=
,
in these scenarios, the identifier for elementIds is a string, compared to =
a=20
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=
).

>=20
>>=20
>>>=20
>>> I tried surrounding the entire condition with quotes, but still no luck=
:
>>>=20
>>> ./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.
>>>=20
>>> I'm assuming this is a syntax thing, although I don't know what the
>>> tool is expecting.
>>=20
>> Did you check the code in SVN?
>=20
> 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 se=
e
> if I get the same results.

I think there is a fix for something similar to this in the trunk (as bfost=
er mentioned),
but thinking about this more, I bet you're having the quoteFields problem, =
per
above. Scope it out and let me know.

>=20
>>=20
>>>=20
>>> 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:
>>>=20
>>> ./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=20
>>> java.lang.AbstractStringBuilder.substring(AbstractStringBuilder.java:88=
1)
>>> at java.lang.StringBuffer.substring(StringBuffer.java:416)
>>> at=20
>>> 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)
>>>=20
>>> 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:
>>>=20
>>> ./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.
>>>=20
>>> /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
>>=20
>> Interesting! Did you scope the code to see if there's a RangeQuery issue=
?
>>=20
>> Feel free to file a bug and would love you to investigate!
>=20
> 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: 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
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++