Servoy Forum

Version: 7.0.1 - build 2008

Since Servoy version 7, we noticed that SQL statements containing the DISTINCT modifier no longer work. For example, the two methods below produce identical results (one has the DISTINCT modifier, the other does not have the modifier). The code with the DISTINCT modifier should only return one dataset item containing 'LONDON'; instead, it returns 4 items all of which are 'LONDON'.

Code: Select all

function onShow(firstShow, event)
{
   var SQL = "SELECT city FROM employees WHERE city = 'LONDON'"
      
    var ds = databaseManager.getDataSetByQuery( 'example_data', SQL, null, 10 )
   
   var length = ds.getMaxRowIndex()
   
   if ( length < 1 ) application.closeSolution()
}

Code: Select all

function onShow(firstShow, event)
{
   var SQL = "SELECT DISTINCT city FROM employees WHERE city = 'LONDON'"
      
    var ds = databaseManager.getDataSetByQuery( 'example_data', SQL, null, 10 )
   
   var length = ds.getMaxRowIndex()
   
   if ( length < 1 ) application.closeSolution()
}

Would someone care to verify this? The above code uses the 'example_data' database that comes with Servoy, so you should be able to cut and paste it into a sample solution.

Thanks!

No one else is experiencing this? We load up our form (custom) valuelists using this methodology, and all of them have duplicate entries as of 7.0. I believe it may be a bug but wanted someone else to verify the behavior before reporting it. All that is needed is to copy and paste the code into a test solution and run it.

Kim,

I noticed this behavior in 6.1 and I was able to get around it by adding a "group by" clause to the query.

So in your case "SELECT DISTINCT city FROM employees WHERE city = 'LONDON' group by city" may "cure" it for now. Using "DISTINCT" should make this unnecessary, but it was the workaround that worked for me.

Out of curiosity, what backend database are you seeing this on?

Ken

Hi, Ken.

For this project, we are using Sybase SQL Anywhere version 11.

We migrated this project from 6.0.9 which did not exhibit the DISTINCT problem using the Sybase database, so it was probably introduced in 6.1 as you mentioned. Since the DISTINCT and GROUP BY SQL modifiers should not affect one another, I will file a bug report.

Thank you for reposnding and for the workaround!

Bug Report: SVY-4274

Sybase is my backend for the solution I saw this in as well so it could be a Sybase thing. I don't think I've used this technique in my other solutions using mysql.

We have another project that uses MS SQL 2008R2 that I just tested with the test code, and the DISTINCT modifier behavior is properly observed. So, you are correct that the DISTINCT modifier problem is related (in some manner) to Sybase (and, perhaps, other databases as well). You may wish to test the code in mysql and report here in order to give Servoy more information.

But, the DISTINCT modifier does work in pre-6.1 versions of Servoy using the identical Sybase database. Since, the Sybase database has not changed, it is apparent to me that something changed as of version 6.1, hence, the bug report.

Thanks, Ken, for all your feedback!

Do you use the same jdbc driver?

Hi, Nicola.

I was wondering about that myself, so I copied the 'jconn3.jar' driver from version 6 to the driver folder for version 7 (I believe Sybase uses the jconn3.jar driver). Unfortunately, the result was the same...the DISTINCT SQL modifier is not being observed.

Kim.

Servoy will send the sql in databaseManager.getDataSetByQuery() as-is to the database, the driver returns the wrong result.
The bug in the driver seems to be triggered by a setFetchSize() call we do in jdbc.
This is just an optimization but seems to break the sybase driver.

Please try the same with the latest sybase anywhere, this is possibly already fixed by sybase.

We have a setting that can be set in servoy.properties: servoy.server.setFetchSize=false this will prevent the setFetchSize() call.
Since setFetchSize() is just an optimization hint to the driver this does not have any other effects.

Rob

We are also getting this error.

Setting servoy.server.setFetchSize=false does resolve this problem.

We have also found that specifying the max_returned_rows parameter in getDataSetByQuery to -1 will also suppress this error.

It appears that the error only manifests itself when you actually choose a max_returned_rows (even if it matches what your expected result set size is).

James,

Are you using sybase ianywhere?
We saw a bug related to distinct and fetchsize in that db some time ago.
I think it is fixed in the latest version/driver.

Rob