Re: [PyWrapper-devel] SQL syntax

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

ah, ok. so its not about the is null syntax but that its not being =20
considered for the FROM clause.
thats a completely different thing. shouldnt be hard...
m

On 15.11.2006, at 19:23, Skofic A. Milko (IPGRI) wrote:

OK:

MySQL version 5.0.15

SELECT COUNT(*) FROM ACC WHERE NOT LATITUDE IS NULL AND NOT LONGITUDE =20=

IS NULL
returns 63603 records (correct)

SELECT COUNT(*) FROM ACC WHERE NOT( LATITUDE IS NULL ) AND NOT=20
( LONGITUDE IS NULL )
returns 63603 records (correct)

SELECT COUNT(*) FROM ACC WHERE NOT( LATITUDE IS NULL AND LONGITUDE IS =20=

NULL )
returns 66650 records

SELECT COUNT(*) FROM ACC WHERE LATITUDE IS NOT NULL AND LONGITUDE IS =20
NOT NULL
returns 63603 records (correct)

SELECT COUNT(*) FROM ACC WHERE( (LATITUDE IS NOT NULL) AND (LONGITUDE =20=

IS NOT NULL) )
returns 63603 records (correct)

Parenthesis are not needed because I guess "LATITUDE IS NOT NULL" is =20
a unary expression,

I am sending the following query:

<?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'50'>
		<externalOutputModel =
location=3D"http://eurisco.ecpgr.org/models/=20
output/MCPD_EURISCO_1.00.xml"/>
		<filter>
			<and>
				<like>
					<concept =
id=3D'GENUS@mcpd_eurisco_100' />
					<literal value=3D'B*' />
				</like>
				<not>
					<isNull>
						<concept =
id=3D'LATITUDED@mcpd_eurisco_100' />
					</isNull>
				</not>
				<not>
					<isNull>
						<concept =
id=3D'LONGITUDED@mcpd_eurisco_100' />
					</isNull>
				</not>
			</and>
		</filter>
		<orderBy>
			<concept id=3D'INSTCODE@mcpd_eurisco_100' />
			<concept id=3D'GENUS@mcpd_eurisco_100' />
			<concept id=3D'ACCENUMB@mcpd_eurisco_100' />
		</orderBy>
	</search>
</request>

The returned result has an SQL error:

<diagnostic time=3D"2006-11-15T19:06:11.47" level=3D"info">
Executing SQL: 'SELECT COUNT(DISTINCT ACC.AccessionID) FROM acc AS =20
ACC JOIN acc_tax AS TAXA ON ACC.TaxonomyID =3D TAXA.TaxonomyID WHERE =20
(TAXA.GENUS LIKE %s AND NOT COORDINATES.LATITUDE is NULL AND NOT =20
COORDINATES.LONGITUDE is NULL)   ' with parameters: 'B%'
</diagnostic>

The error is that the parser does not consider the concepts =20
referenced in the isNull clause, not placing them in the FROM SQL =20
clause.

Bye!

milko

On Nov 15, 2006, at 18:02 , Markus D=F6ring wrote:

> Milko,
> for programming it would be easier to have isNotNull. But we dont =20
> wanna change TAPIR and its really more consistent to NOT have it.
>
> So we gotta solve that. its a dbmod problem.
> ive just tried with mysql5.0 and something like this works fine:
>
> SELECT * FROM taxon WHERE NOT synonyms IS NULL
>
> what version are you using?
> can you try to do a simple statement like the above?
> if it doesnt work, how about enclosing is null in brackets like this:
>
> SELECT * FROM taxon WHERE NOT (synonyms IS NULL)
>
> please let me know!
> markus
>
> On 11/14/06, Skofic A. Milko (IPGRI) <m.s...@cg...> wrote:
> I was looking at how TAPIR resolves the SQL syntax for selecting =20
> not null elements it does:
> WHERE( NOT table.field IS NULL )
> the problem is that it is not the correct syntax, the correct one is:
> WHERE( table.field IS NOT NULL )
>
> Wouldn't it be easier to add "isNotNull" to the unary operators?
>