We recently upgraded from 11 to 17 (I know) and I've hit a really weird problem with a very simple query.

I'm running a query joining two tables and fetching DISTINCT values from the second table.

The tables are:

CREATE TABLE "DBA"."GenericInfoLinks" (
    "LINKID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    "PARENTID" INTEGER NOT NULL,
    "PARENTTYPEID" INTEGER NOT NULL,
    "PROPERTYID" INTEGER NOT NULL,
    "VALUEID" INTEGER NOT NULL,
    "BlobId" INTEGER NOT NULL,
    PRIMARY KEY ( "LINKID" ASC )
) IN "system";

approx rows: 8.6m

and

CREATE TABLE "DBA"."GenericInfoValues" (
    "VALUEID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    "VALUESTRING" CHAR(1000) NOT NULL UNIQUE,
    PRIMARY KEY ( "VALUEID" ASC )
) IN "system";

approx rows: 1.2m

The query I'm running was:

SELECT 
    DISTINCT 
        GIV.ValueString
FROM 
    GenericInfoLinks GIL
JOIN
    GenericInfoValues GIV ON GIL.ValueID = GIV.ValueId
WHERE 
    GIL.PropertyId = 129

on 11 this would return almost instantly with a few results (there are only 10-30 rows that match the criteria at any time). On 17 it never (read: I gave up after 2 hours) returned a result.

If I change the query to:

SELECT 
    DISTINCT 
        GIV.ValueString,
        GIL.ValueID,
        GIV.ValueId
FROM 
    GenericInfoLinks GIL
JOIN
    GenericInfoValues GIV ON GIL.ValueID = GIV.ValueId
WHERE 
    GIL.PropertyId = 129

it returns instantly with results.

If I change the query to (swapping the order of the ID columns):

SELECT 
    DISTINCT 
        GIV.ValueString,
        GIV.ValueId,
        GIL.ValueID

FROM 
    GenericInfoLinks GIL
JOIN
    GenericInfoValues GIV ON GIL.ValueID = GIV.ValueId
WHERE 
    GIL.PropertyId = 129

It never returns.

I've changed the query to work now but I just don't understand what the problem is. I feel like I'm missing some fundamental or subtle behaviour of DISTINCT that I haven't hit before.

Any help would be appreciated.

asked 25 Feb, 10:53

samrae's gravatar image

samrae
2265619
accept rate: 50%


Hm, isn't there a FOREIGN KEY declared on GenericInfoLinks.ValueID (as this seems to be a referencing GenericInfoValues)? - I'm asking that because the difference might result from a table scan in the last query.

The query plans should show the difference between the two latter queries.

permanent link

answered 25 Feb, 11:17

Volker%20Barth's gravatar image

Volker Barth
35.4k339497740
accept rate: 33%

converted 26 Feb, 07:21

I added a foreign key in my test DB and all permutations of the query returned results as expected. So, this is about the difference in behaviour between the server "knowing" where to find the related records and having to search for them?

(26 Feb, 05:48) samrae
Replies hidden

I don't know the details of the query optimizer but I guess your understanding seems reasonable. FWIW, if you declare a foreign key, it will automatically use the index built on the PK column so it really "knows" it can read both columns over the same physical access.

(26 Feb, 07:25) Volker Barth

> I added a foreign key

Are you sure that foreign key did not exist in the V11 databases?

Was the V17 database created by upgrading the V17 database?

Have you looked at the graphical plan with statistics for the V11 query?

(26 Feb, 07:57) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×161
×4

question asked: 25 Feb, 10:53

question was seen: 130 times

last updated: 26 Feb, 07:57