Re: [Qgis-user] Query expression bugs with NULL Values
Thanks for the answer Jürgen, didnt know that QGIS used normal database null logic which is fantastic. Cheers, Jose On 26/03/2015 10:53, Jürgen E. Fischer wrote: Hi José, On Thu, 26. Mar 2015 at 10:19:10 +0100, José Carlos Martínez Llario wrote: "amenity" <> 'restaurant' returns 37 rows "amenity" = 'restaurant' returns 11 rows The layers has 48 NULL values, so it means that amenity" <> 'restaurant' does not take into account the null values. Hope this is a bug and not the correct behaviour. It's the correct behaviour. NULL <> 'restaurant' is NULL, ie. not true. Use eg. coalesce('amenity','') <> 'restaurant' Jürgen ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user -- -- José Carlos Martínez Llario http://cartosig.upv.es Producción Cartográfica y SIG. Dpto. Ingeniería Cartográfica. Univ. Politécnica de Valencia. -- ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Query expression bugs with NULL Values
Hi José, On Thu, 26. Mar 2015 at 10:19:10 +0100, José Carlos Martínez Llario wrote: > "amenity" <> 'restaurant' returns 37 rows > "amenity" = 'restaurant' returns 11 rows > > The layers has 48 NULL values, so it means that amenity" <> > 'restaurant' does not take into account the null values. > Hope this is a bug and not the correct behaviour. It's the correct behaviour. NULL <> 'restaurant' is NULL, ie. not true. Use eg. coalesce('amenity','') <> 'restaurant' Jürgen -- Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31 Dipl.-Inf. (FH) Rheinstraße 13 Fax. +49-4931-918175-50 Software Engineer D-26506 Norden http://www.norbit.de QGIS release manager (PSC) GermanyIRC: jef on FreeNode signature.asc Description: Digital signature ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Query expression bugs with NULL Values
Sorry I didnt explain well. Forget about the second part of the email, I was talking about syntax, of course is well-known that NULL and 'NULL' are two different things but that was not my point): So this this the problem that I would like an answer: The table has 4635 rows, which 4587 are null as the following queries prove: "amenity" is not NULL returns 48 rows "amenity" is NULL returns 4587 rows The following queries doest not make sense: "amenity" <> 'restaurant' returns 37 rows "amenity" = 'restaurant' returns 11 rows The layers has 48 NULL values, so it means that amenity" <> 'restaurant' does not take into account the null values. Hope this is a bug and not the correct behaviour. Im using QGIS 2.8.1 The table has 48 null "amenity" rows and 4587 not null "amenity" rows as you can see from the first queries On 25/03/2015 20:38, Andre Joost wrote: Am 25.03.2015 um 20:07 schrieb José Carlos Martínez Llario: The layers has 48 NULL values, ... THIS IS OK "amenity" is not NULL returns 48 "amenity" is NULL returns 4587 This does not make nuch sense to me. Do you have 48 empty (NULL) entries, or 48 non-empty values? THIS IS WRONG (if null values can not be treated as a text in the syntax it shouldnt work in both cases or throw some error) "amenity" <> 'NULL' returns 48 "amenity" = 'NULL' returns 0 No, NULL and 'NULL' are two different things. It is possible to write 'NULL' as a text value for amenity (although it does not make sense, but the OSM database does not reject it). So it should be ok that "amenity" = 'NULL' returns nothing in your dataset. HTH, André Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user -- -- José Carlos Martínez Llario http://cartosig.upv.es Producción Cartográfica y SIG. Dpto. Ingeniería Cartográfica. Univ. Politécnica de Valencia. -- ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Query expression bugs with NULL Values
Am 25.03.2015 um 20:07 schrieb José Carlos Martínez Llario: The layers has 48 NULL values, ... THIS IS OK "amenity" is not NULL returns 48 "amenity" is NULL returns 4587 This does not make nuch sense to me. Do you have 48 empty (NULL) entries, or 48 non-empty values? THIS IS WRONG (if null values can not be treated as a text in the syntax it shouldnt work in both cases or throw some error) "amenity" <> 'NULL' returns 48 "amenity" = 'NULL' returns 0 No, NULL and 'NULL' are two different things. It is possible to write 'NULL' as a text value for amenity (although it does not make sense, but the OSM database does not reject it). So it should be ok that "amenity" = 'NULL' returns nothing in your dataset. HTH, André Joost ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Query expression bugs with NULL Values
Hi List, Im using some data from OSM, I used this data directly from osm file and converted to shape file too. In both cases same results: The layers has 4635 rows: "amenity" <> 'restaurant' returns 37 "amenity" = 'restaurant' returns 11 The layers has 48 NULL values, so it means that amenity" <> 'restaurant' does not take into account the null values. Hope this is a bug and not the correct behaviour. Im using QGIS 2.8.1 PD: There are more weird results with the NULL syntax. for example: THIS IS OK "amenity" is not NULL returns 48 "amenity" is NULL returns 4587 THIS IS WRONG (if null values can not be treated as a text in the syntax it shouldnt work in both cases or throw some error) "amenity" <> 'NULL' returns 48 "amenity" = 'NULL' returns 0 cheers, -- -- José Carlos Martínez Llario http://cartosig.upv.es Producción Cartográfica y SIG. Dpto. Ingeniería Cartográfica. Univ. Politécnica de Valencia. -- ___ Qgis-user mailing list Qgis-user@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/qgis-user