Sybase-IQ : Query with join returns always 0 rows (but count(*) give rows)

[Former Member]

Hello,

I have a very strange comportment on my sybase-iq database.

I have 2 tables :

Table A : 47 000 000 rows

Table B : 497 rows

If I make the following query :

select count(*) from A inner join B on A.ID = B.ID;

The result of the count is 5 661 188.

If I make the following query :

select * from A inner join B on A.ID = B.ID;

The result is 0 row !

If I make the following query :

select A.field1 from A inner join B on A.ID = B.ID;

The result is 5 661 188 rows.

select A.field2 from A inner join B on A.ID = B.ID

The result is 0 row !

I execute these queries with Interactive SQL (Sybase Central).

I've tried also to execute these same queries with the ETL Talend, and i got same results as above.

Any ideas why we have these very strange results ?

Thanks

[tayeb_hadjou · Accepted solution]

Hi Tangi,

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

Please post output of sp_iqcheckoptions executed by same user name who executes the query.

Regards,

Tayeb.

[Former Member · Accepted solution]

Hi Tayeb,

The IQ version is :

Sybase IQ/15.2.0.5604/100518/P/GA/MS/Windows 2003/64bit/2010-05-18 09:24:29

sp_iqcheckoptions :

User_name,Option_name,Current_value,Default_value,Option_type

'DWH_ADMIN','Query_Plan_Text_Access','ON','OFF','Permanent'

'DWH_ADMIN','Query_Plan_Text_Access','On','OFF','Temporary'

'DBA','allow_read_client_file','on','Off','Permanent'

'DWH_ADMIN','allow_read_client_file','On','Off','Permanent'

'PUBLIC','allow_read_client_file','On','Off','Permanent'

'DWH_ADMIN','checkpoint_time','20','60','Temporary'

'DWH_ADMIN','compression','Off','6','Temporary'

'DWH_ADMIN','connection_authentication','Company=Sybase;Application=DBTools;Signature=000fa55157edb8e14d818eb4fe3db41447146f1571g2a1b5949cab32c7760419117ca3ce88770fecfd7','','Temporary'

'DWH_ADMIN','date_format','DD-MM-YYYY','YYYY-MM-DD','Permanent'

'PUBLIC','date_format','DD-MM-YYYY','YYYY-MM-DD','Permanent'

'DWH_ADMIN','return_date_time_as_string','On','Off','Temporary'

'DWH_ADMIN','sql_flagger_error_level','Off','W','Temporary'

'DWH_ADMIN','sql_flagger_warning_level','Off','W','Temporary'

'DWH_ADMIN','suppress_tds_debugging','On','Off','Temporary'

'DWH_ADMIN','time_zone_adjustment','60','0','Temporary'

Thanks & regards,

Tangi

[tayeb_hadjou · Accepted solution]

Options list looks not related

It could be some thing wrong in IQ15.2 GA. Whichi is very Basic and Old version.

To double check, try execute the query in question using different optimizer join_preference and see if it changes the result :

set temporary option join_preference='-1';

select * from ....

Try with values ('-2', '-3', '-4', '-5').

If you get expected results, then you need absolutelly to upgrade.

[Former Member · Accepted solution]

Thanks a lot for your answer Tayeb.

I've tried all the values for the option join_preference and it works with the value -5, 3 and 4.

We will use this option for the moment and we will see if we can update our version.

Regards,

Tangi

[c_baker · Accepted solution]

Have you confirmed that you have the correct indexes on both join columns?

Chris

[Former Member · Accepted solution]

Hi Chris,

I have 2 indexes on the join column of table A :

- 1 HG index on only this column

- 1 unique HG index on this column and others columns

No indexes on table B.

The problem can be here ?

Should i add an index on join column of table B ?

Indexes do not affect only performance ?

Thanks

Regards,

Tangi

[c_baker · Accepted solution]

I assume from your answer that you have a composite HG-U index on Table A and HG indexes on the individual columns of that index as well, and that the join column of A definitely has an HG index.

Yes, you should add an HG index on the join column of table B, even though it is s small number of rows.  If the join column is a primary key, then define the column as a PK constraint or add an HG-U on the column instead of using an HG.

By setting the join preference, you are avoiding certain join strategies the optimizer could use.  In this case, the small table could be pushed down into the larger table, avoiding a more expensive join.  Without the proper indexes and statistics however, a more expensive join (e.g. sort-merge) must be used to ensure the query works.

Once you properly index the join columns, remove any join preferences and examine query plans to see how it is working.

Chris

[Former Member · Accepted solution]

Chris,

Ths for your answer.

I've added an unique HG index on the join column index of table B.

But the result is still 0 row.

I've looked at the execution plan and seen that the join done is a hash push-down join.

I think there's a problem with the hash push-down join for my query.

When i do the count(*) the join used is nested-loop push-down, and it's works.

So, the only solution I see for the moment is to use "set temporary option join_preference='-5';"

Tangi

[c_baker · Accepted solution]

The other solution is to upgrade.

You are running a 4 year old GA version of IQ 15.2 that may have already had this issue identified and patched long ago.

Chris