getDataSetByQuery(): SQL DISTINCT Not Working

Questions, tips and tricks and techniques for scripting in Servoy

getDataSetByQuery(): SQL DISTINCT Not Working

Postby kwpsd » Tue Mar 26, 2013 11:24 pm

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!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby kwpsd » Fri Mar 29, 2013 5:50 pm

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 W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby keenkenny » Fri Mar 29, 2013 7:08 pm

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
Ken Keen
KeenEyes Interactive, Inc.
SAN Developer
keenkenny
 
Posts: 70
Joined: Sun Jun 01, 2003 10:31 pm
Location: Cedar City, UT

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby kwpsd » Fri Mar 29, 2013 8:50 pm

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!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby kwpsd » Fri Mar 29, 2013 9:00 pm

Bug Report: SVY-4274
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby keenkenny » Fri Mar 29, 2013 9:14 pm

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.
Ken Keen
KeenEyes Interactive, Inc.
SAN Developer
keenkenny
 
Posts: 70
Joined: Sun Jun 01, 2003 10:31 pm
Location: Cedar City, UT

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby kwpsd » Fri Mar 29, 2013 10:18 pm

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!
Kim W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby ngervasi » Sat Mar 30, 2013 1:09 am

Do you use the same jdbc driver?
Nicola Gervasi
sintpro.com
SAN Partner
ngervasi
 
Posts: 1485
Joined: Tue Dec 21, 2004 12:47 pm
Location: Arezzo, Italy

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby kwpsd » Tue Apr 02, 2013 9:08 pm

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 W. Premuda
San Diego, CA USA
User avatar
kwpsd
 
Posts: 687
Joined: Sat Jul 28, 2007 6:59 pm
Location: San Diego, CA USA

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby rgansevles » Mon Apr 15, 2013 10:07 am

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby jgarfield » Wed Apr 23, 2014 6:15 pm

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).
Programmer.
adBlocks
http://www.adblocks.com
jgarfield
 
Posts: 223
Joined: Wed Sep 28, 2005 9:02 pm
Location: Boston, US

Re: getDataSetByQuery(): SQL DISTINCT Not Working

Postby rgansevles » Fri Apr 25, 2014 11:20 am

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
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to Methods

Who is online

Users browsing this forum: No registered users and 11 guests

cron