Servoy Forum

Either we are doing something wrong, or we've found a bug in finding...

If we construct a form with fields from the table the form is based on
("MASTER") and also fields from a related table ("OTHER") then we can,
with apparently correct results, find records based on fields in the
MASTER or OTHER records. In this case, each MASTER record can have zero
or one related OTHER records.

We can also, again apparently correctly, logically OR two search requests
using criteria matching fields in the MASTER (Find; enter criteria; Add
Record; enter next criteria; ... Search). This also seems OK in a method.

So far, so good - but... if we then try to OR two sets of search criteria
where the first one specifies only fields in the MASTER (which by itself
would return some MASTER records which do not have related OTHER records)
and the second specifies field(s) in the OTHER record we then find that
the foundset excludes MASTER records which match the first set of criteria
but which do NOT hve a related OTHER record. I.e. the foundset of the
first request in the search appears to be influenced by referencing fields
in a related record in the second request of the find.

Are we doing something wrong, or have we tripped over a bug?

FWIW, this has been observed with Servoy 1.01 on two different platforms:

* Mac OS-X, Servoy 1.01, Java 1.4.1, MySQL
* Linux, Servoy 1.01, Java 1.3.1, PostgreSQL

Thanks,
Neale.

Hi Neale,

This is a known issue. The search engine automatically joins a related table when you have a related column in your form. So if you indeed do a search on a record that doesn't have related data, the outcome is empty. We deliberately don't use outer joins, because those are expensive queries. Currently we are looking for the best way to solve this, keeping performance in mind.

Thanks for your input.

Hmm... not sure if we are talking about the same thing here.

I don't think outer joins (or lack thereof) come into my issue - except when joining the multiple parts of a search request.

As I said, we start with a form with fields from it's base table plus fields from a related table. I can do a search on fields in the base table just fine, regardless of if the matching records have related records. I can search on fields in the base table plus fields from the related table (this obviously being an inner join of the two tables.

But if I attempt to combine the above two, then the first request , surprisingly, appears to become an inner join with all records in the related table (even though that part of the search specification doesn't reference any fields in the related table).

'tis quite odd indeed.

Thanks,
Neale.