Restore the FB 1.5 and FB 2.0 style to use DISTINCT with Blob fields [CORE1348]

[firebird-automations]

Submitted by: Nils Boedeker (nilsboedeker)

Referring to a long discussion:

In Firebird 2.1 - Beta 1 a bugfix was done that prevents that Resultsets with blobs can use DISTINCT.

This was justified because Blobs can't sorted and the classic (IB, FB 1.5.X, FB 2.0) way use the BlobID to build the unique dataset.

This is NOT an improvement because it means you can't build queries that returns an unique datarecord of table N if the query works over an N:M connection. This means for me (Nils Boedeker) and I also assume a lot of other users a lot of problems.

For this purpose using BlobID is absolut correctly. Please restore the Classice style that the BlobID used for sorting if DISTINCT use with a resultset that contains Blobs.

This issue is discussed very well in firebird-devel newslist...

[Firebird-devel] FB 2.1 Beta 1: Datatype needs modification., integers in SQL dialect 3 [Firebird-devel] [FB-Tracker] Created: (CORE1345) No support for BLOB sorting

for further Infos please concact me: Nils Boedeker mailto:nilsboedeker@t-online.de skype: NilsBoedeker

also... if somebody need it... i can give live access to the database structur that are affected by this problem

with best regards

Nils

[firebird-automations]

Modified by: Nils Boedeker (nilsboedeker)

summary: Restore the FB 1\.5 and FB 2\.0 to use DISTINCT with Blob fields =\> Restore the FB 1\.5 and FB 2\.0 style to use DISTINCT with Blob fields

[firebird-automations]

Commented by: Nils Boedeker (nilsboedeker)

Hi,

I check the postings again found a solution that are faster than my distinct query. But it only runs on FB 2.x and not in 1.5.x. This solution are posted before by Adriano dos Santos Fernandes:

This query: -------------------------------------------------------------------------------

select article.* from article join (

  SELECT DISTINCT ARTICLE\_CONTENTTREE\_LINK\.ARTICLEID

FROM ARTICLE_CONTENTTREE_LINK INNER JOIN SP_CT_VIDLIST_WITHROOT(15000) ON (ARTICLE_CONTENTTREE_LINK.TREEID = SP_CT_VIDLIST_WITHROOT.RTREEID)

\) link
    on \(article\.relid = link\.articleid\);

-------------------------------------------------------------------------------

is finally faster (2-3 times) then my query. Also it seemed to be working with all my very special queries...

Nevertheless it means an lot of work to change my query engine an found all places but this style above are a solution...

with regards from germany

Nils B?deker

[firebird-automations]

Modified by: Nils Boedeker (nilsboedeker)

priority: Critical \[ 2 \] =\> Trivial \[ 5 \] issuetype: Bug \[ 1 \] =\> Improvement \[ 4 \]

[firebird-automations]

Modified by: @pcisar

Workflow: jira \[ 12520 \] =\> Firebird \[ 15221 \]