Problem with join returning zero rows

[Former Member]

Hello, I am trying to do a select with joins on a IQ16 server, the query is returning 0 rows when it should be returning 23 rows. If I use a subquery instead of a join, it correctly returns the 23 expected rows.

e.g.

select count(*) from t1, t2 where t1.ID = t2.ID and t2.misc = 'A'

returns 0 rows

select count(*) from t1 where t1.ID in (select t2.ID from t2 where t2.misc = 'A')

returns 23 rows

I can't explain this behaviour. I've checked the datatypes and and data match (thankfully these are very small tables), there are no hidden characters, i.e. I compared select len(ID) from t1 and select len(ID) from t2 and they match. ID is a varchar(30) in both tables. I don't understand.

Any help with this mystery would be appreciated!

[tayeb_hadjou]

Hi David,

- What is the exact IQ version (select @@version) ?

- Check tables consistency :

sp_iqcheckdb 'verify table t1' ;

sp_iqcheckdb 'verify table t2'

- Check current options : sp_iqcheckoptions ;

- If no HG indexes on t1.ID and t2.ID, add them and try again.

Regards,

Tayeb.

[Former Member]

Hi, version is SAP IQ/16.0.0.800/141110 on Windows 2008R2 server.

I ran the sp_iqcheckdb verify table on both tables. t1 has 3 columns, and 3 HG indexes and 3 FP indexes, t2 has 4 columns and 3 HG indexes and 4 HP indexes.

What would I be looking for under options? Nothing jumps out.

Thanks!

[markmumy]

Please provide the full output of select @@version.

Mark

[Former Member]

SAP IQ/16.0.0.808/141110/P/sp08.20/MS/Windows 2003/64bit/2014-11-10 13:39:42

[tayeb_hadjou]

Look if index incosistency is reported.

Look certain options impatcing results are set to non-default (eg. AnsiNull).

Recent pacth level PL31 fixed issues :CR781504 & CR771660.

[tayeb_hadjou]

To check if one of the CRs above is involved, test the query with different join methods :

1-

set temporary option join_preference='-1';

select ..... ;

2- set temporary option join_preference='-2';

select ..... ;

....

2- set temporary option join_preference='-7';

select ..... ;

[Former Member]

In all 3 cases, query still returns zero rows.

[Former Member]

No index consistency or other problems reported.

These are all the non-default options set (sp_iqcheckoptions output):

options.txt.zip

[c_baker]

I have seen this before.  It is not clear from your other answers if you have an HG index on each individual column of the join.

e.g. create an HG index on each of T1.ID and T2.ID.  Only if they are the only columns already identified as Pkey, Fkey or Akey do you not have to index them, as those are automatically indexed.  If the column is part of a composite key, still HG index each separate column.

(You can also add an HG to your search argument if you like).

Can you post a query plan (HTML) with query_plan_after_run = 'off' to see the estimates?

Chris

[Former Member]

In both tables, the column used in the join is part of a composite primary key, but it does have its own HG index as well.

Attached is the query plan, it wont allow me to attach an html file, so I renamed it .txt

DBA_L_0__20150908-110412_7229.txt.zip

[Former Member]

On a related note, I am trying to drop the HG indexes to recreate them. I issue the command:

drop index table.column.index

and I am receiving an error:

SQL Anywhere Error -1008004: Cannot DROP index 'index_name'

I am googling this error and nothing is coming up...

[saroj_bagai]

correct syntax:

drop index <indexname>

[Former Member]

Ok, finally getting somewhere, but still bewildered. This is what I did:

1. Dropped the primary key of table2 (primary key is a composite of two columns "A" and "B", "A" is used in the original join).

2. Dropped the foreign key constraint on "A"

3. Dropped HG index on "A"

4. Recreated HG index on "A"

5. Added the foreign key constraint on "A"

*Here's where it gets interesting! I RUN the original query, and it returns the expected records!

6. I add the primary key

*I run the original query, and it returns zero records!

I drop the primary key, and the original query returns expected records again.

So it is the existence of this composite primary key which is effecting whether the query works or not. I don't understand why, but at least I've located the issue.

[c_baker]

What is the behavior if instead of the PK, you add the composite key as an AK instead (just add as an HG UNIQUE)? 

This will cause the index to be built explicitly, without an actual PK causing the automatic index creation, and can still be used as a constraint.

Chris

[Former Member]

Ok wait, it is working now with the primary key added. I may have confused myself (I was dropping and recreating primary keys, foreign keys, and indexes on two tables). So is the cause of all this because the original HG index on this column was implicitly created when the primary key was created. And when I dropped the primary key, the index, and then created the index, then primary key, the resulting HG index was explicitly created?

So is this a bug in IQ16 then? i.e. the original query should still have worked with implicitly created indexes right?

[markmumy]

Yes, this appears to be a bug.  We've had similar issues in the past versions (IQ 15) where results would vary based on whether an HG was on the columns or not.  I thought we had cleared all this up some time ago.

The PK will likely not present an issue unless it is just a single column PK.

Mark

[Former Member]

Ok thanks everyone for your help! I am still fairly new to IQ (been an ASE DBA for 12 years) and I am still not sure when I encounter issues like this whether it's my lack of IQ experience or an actual problem.

Thanks again!

[Pragnesh_7se7]

Have you tried any one field instead of * ?

[Former Member]

Yes, my actual query is selecting for specific columns and not an aggregate, the behaviour is the same whether I do select count(*), count(t1.A), A, B, C etc.

[markmumy]

What version of IQ?  Run select @@version and send that.  In some versions we had "issues" with certain joins and index types.

You should also open a case so that support can get involved.

Mark