Re: [PyWrapper-devel] SQL syntax

[<wi...@go...>]

Milko, isNotNull would change TAPIR and destroys the "symmetry" of =20
the protocol.
that is no solution anymore.
but ive added the paranthesis to NOT, that fixes it.
SOLVED!
markus


On 16.11.2006, at 18:25, Skofic A. Milko (IPGRI) wrote:

I updated and now you can save the configurations fine, fixed!

Now, regarding NULLs, this is the reference SQL query that I send (to =20=

determine the correct count), this would be the query if we had the =20
isNotNull operator:

A.) SELECT COUNT(DISTINCT `acc`.`AccessionID`) FROM `acc` WHERE=20
( (`LATITUDE` IS NOT NULL) AND (`LONGITUDE` IS NOT NULL) )

Here is the query I send to TAPIR:

<?xml version=3D'1.0' encoding=3D'utf-8'?>
<request>
   <header>
     <source accesspoint=3D"http://tapir.grinfo.net:8080/pywrapper/=20
pywrapper?dsa=3DEURISCO" sendtime=3D"2006-11-13T12:54:29.43" />
   </header>
   <search count=3D'true' start=3D'0' limit=3D'0'>
     <externalOutputModel location=3D"http://eurisco.ecpgr.org/models/=20=

output/MCPD_EURISCO_1.00.xml" />
     <filter>
       <and>
         <not>
           <isNull>
             <concept id=3D'LATITUDED@mcpd_eurisco_100' />
           </isNull>
         </not>
         <not>
           <isNull>
             <concept id=3D'LONGITUDED@mcpd_eurisco_100' />
           </isNull>
         </not>
       </and>
     </filter>
   </search>
</request>

This is the SQL that TAPIR does:

B.) SELECT COUNT(DISTINCT ACC.AccessionID) FROM acc AS ACC WHERE (NOT =20=

ACC.LATITUDE is NULL AND NOT ACC.LONGITUDE is NULL)

On EURISCO data source:
A.) returns 63618 records
B.) returns 930966 records

On EURISCO_TEST data source:

A.) SELECT COUNT(DISTINCT `acc`.`AccessionID`) FROM `acc` LEFT JOIN =20
`coordinates` ON `acc`.`CoordinatesID` =3D `coordinates`.`ID` WHERE=20
( (`coordinates`.`LATITUDE` IS NOT NULL) AND =20
(`coordinates`.`LONGITUDE` IS NOT NULL) )

B.) SELECT COUNT(DISTINCT ACC.AccessionID) FROM acc AS ACC JOIN =20
coordinates AS COORDINATES ON ACC.CoordinatesID =3D COORDINATES.ID =20
WHERE (NOT COORDINATES.LATITUDE is NULL AND NOT COORDINATES.LONGITUDE =20=

is NULL)

In this case both queries return the same result (the correct one): =20
63618 records.

So, the problem we had when TAPIR was not including a table in the =20
FROM clause is resolved, but we still gave a problem with the SQL it =20
generates.

Going back to the EURISCO data source here are some tests I did:

SELECT COUNT(DISTINCT ACC.AccessionID) FROM acc AS ACC WHERE( (NOT =20
ACC.LATITUDE is NULL) AND (NOT ACC.LONGITUDE is NULL) )
returns 930966 records: wrong

SELECT COUNT(DISTINCT ACC.AccessionID) FROM acc AS ACC WHERE( (NOT =20
(ACC.LATITUDE is NULL)) AND (NOT (ACC.LONGITUDE is NULL)) )
returns 63618 records: correct!

The second version works, although you get an indigestion of =20
parenthesis. That's why I was wondering if it would not be easier to =20
implement the isNotNull operator, since I think it will be used much =20
more than the isNull one.

Hope it helps.

Bye!

Milko

On Nov 16, 2006, at 16:59 , Markus D=F6ring wrote:

> ahh. I found that it only occurs with NOTs in the filter. otherwise =20=

> it was working.
> and I actually found something in the filters. can you try the new =20
> trunk? im curious if this fixes it!
>
> markus
>
> On 11/16/06, "D=F6ring, Markus" <m.d...@bg...> wrote:
>
> -----Original Message-----
> From: pyw...@li... [mailto: =20
> pyw...@li...] On Behalf Of Skofic =20
> A. Milko (IPGRI)
> Sent: Donnerstag, 16. November 2006 09:18
> To: Markus D=F6ring
> Cc: pyw...@li... ; Javier privat
> Subject: Re: [PyWrapper-devel] SQL syntax
>
>
> I tried to install it on the server but TAPIR gave me errors when
> loading the structure, fortunately it left a dot file before
> crashing. Here is the structure of the database, I send you both the
> dot file and a .png
>
>
>
>