Search Builder - results display NOT NULL fields when searching for NULL

[anton]

OK so I want to search for contacts with no address details, so I go to Search Builder and search for:

Individual -> Street Address -> Primary -> IS NULL

and search, but the results I get are for contacts who do have Street Address data! Tried this on the demo site and the results are the same.

Also...

Searching for:

Individual -> Postal Code -> Primary -> IS NULL

results in a database error! This is also reproducable on the demo server.

This is why CiviCRM drives me nuts, as soon as one bug gets fixed, another crops up in its place...

If someone could file two issue reports for me I'd be grateful.

[Dave Greenberg]

I just verified that these are both regression bugs - those two search patterns appear to work properly (at least w/ my sample data) in 3.1.x. 

That said, improving the quality of CiviCRM releases has to be a community supported effort. Did you download a beta release of 3.2 and test your important workflows and use cases with your data? Did you try things out on sandbox or demo during the release cycle? Have you committed any resources to developing unit tests. If this answer to all 3 is no, then .... 

Please go ahead and file an issue for these (you can put them in the same issue). If you have resources to investigate and submit a patch - that will help ensure it gets addressed. If there's some reason why you aren't able to file an issue, please post back here and let me know what the barrier is.

You can include this "bad" query from the Postal Code search in the issue - since that one is pretty clear in terms of what's going wrong (the extra '' after IS NULL in the WHERE clause):

Code: [Select]

SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) WHERE  (  ( civicrm_address.postal_code IS NULL '' AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0)

[anton]

I didn't download a v3.2 beta version - I don't have the time (nor disk space on my host) to test them, I waited for the final release in the hope that any bugs in the beta version would be eradicated. I did briefly try out the alphas/betas on the demo server and everything I tried seemed OK.

I could probably find some more similar bugs but to be honest I have other things to do with my time, though if I find more I'll post them on the issue tracker rather than here.

Unfortunately I don't have the PHP skills to create a patch, sorry.

I will register with the issue tracker and post the issues on there.

Thanks a lot.

[anton]

Issue filed here:

http://issues.civicrm.org/jira/browse/CRM-6588

[Kurund Jalmi]

You can get fix from http://fisheye2.atlassian.com/changelog/CiviCRM?cs=29031

Kurund

[anton]

Wow, such efficiency! Thanks a lot, it's working great.