Hi,
we just discovered this nasty problem. If you have a query like
var query = 'select distinct(table.pk) from table ORDER BY table.pk'
and do a controller.loadRecords(query) and then do a databaseManager.getFoundSetCount(foundset), Servoy actually issues a
SELECT COUNT(table.pk) FROM table ...
instead of
SELECT COUNT(DISTINCT(table.pk)) FROM table ...
which of course is a big difference.
This is very confusing to users and has cost us a lot of time, because we were investigating the wrong stuff (“why does my text file contain only 1704 records if there are 2043 in the database?”).
Can this be fixed?
Thanks
Patrick
How can the count of PK’s be different than a distinct count of PK’s?
Pk’s are supposed to be unique record identifiers, thus 1 row for 1 PK, thus distinct, thus the distinct part in the statement around a PK is not needed.
Or am I missing something?
Paul
Yes, you are. If you have a situation where for example a person can have n addresses and you search for persons with certain values in addresses, you will get “multiple” records (the same person pk is returned as often as the person has addresses). To avoid this, you query using distinct.
That really is a big difference!
But then you are not querying by the table’s PK…
In your example, are you querying the addresses table, or the persons table?
Based on your example, I would guess you query either
select distinct(person.personid) from person order by person.personid
or
select distinct(address.addressis) from addressorder by address.addressid
In either case, the distinct is not needed, because the person.personid and address.addressid are unique anyway.
If I’m misinterpreting this, please extend your example.
Paul
For example:
SELECT distinct(person.person_id) FROM person, addresses WHERE person.person_id = addresses.person_id AND addresses.city = ‘New York’ ORDER BY person.person_id
And then load this query into a form based on person. If you do not use a distinct here, you get duplicate persons…
Patrick,
Before release 3.5 (beta is just publically released), using distinct with a form by query is not allowed (although it may work sometimes), see the code sample of controller.loadRecords:
//4) to load records in to the form based on a query (also known as ‘Form by query’)
//controller.loadRecords(sqlstring,parameters);
//limitations/requirements for sqlstring are:
//-must start with ‘select’
//-must contain ‘from’ and ‘order by’ keywords
//-the ‘from’ must be a comma separated list of table names
//-must at least select from the table used in Servoy Form
//-cannot contain ‘group by’ or ‘having’
//-all columns must be fully qualified like ‘orders.order_id’
//-the selected columns must be the (Servoy Form) table primary key columns (alphabetically ordered like ‘select a_id, b_id,c_id …’)
//-can contain ‘?’ which are replaced with values from the array supplied to parameters argument
In release 3.5 the sql engine has been rewritten and allows a lot more freedom to the custom query (see the release notes post on this forum)
Additionally, the sql engine will automatically remove duplicate PKs so the distinct should not be necessary anymore.
Please try it on 3.5 and tell us if it works.
Rob
Hello Rob,
although I know what you mean, a distinct(pk) also complies somehow to
-the selected columns must be the (Servoy Form) table primary key columns (alphabetically ordered like ‘select a_id, b_id,c_id …’)
So I never thought of “breaking the rules”, especially since this is the only way to query using a join without getting strange results. And while this always works as expected, the only problem I have is the count.
If I understand you right, you say that the count is expected to go wrong (if I may put it that way)? Of course I will try that with 3.5, but I still have a problem with my 2.2.x installations. Fixing this myself (find a workaround) is pretty complex, especially because I have no means to get the SQL from a foundset in 2.2.
Any ideas how this can be prevented?
Same issue here…
databaseManager.getFoundSetCount(forms[_form].foundset) returns more records then the actual amount of records in the database.