Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
firebird-automations opened this issue Jul 6, 2007 · 4 comments
Open

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

firebird-automations opened this issue Jul 6, 2007 · 4 comments
Labels
affect-version: 2.1 Beta 1 component: engine priority: trivial type: improvement

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: Nils Boedeker (nilsboedeker)

priority: Critical [ 2 ] => Trivial [ 5 ]

issuetype: Bug [ 1 ] => Improvement [ 4 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: 2.1 Beta 1 component: engine priority: trivial type: improvement
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations