cancel
Showing results for 
Search instead for 
Did you mean: 

Unexpected SELECT ... WITH ... result

0 Kudos

Hi everyone!

I got some .hdbview with a several columns, and one of them is "VALUE" of Integer. Strange things happen when I try to filter that view. Let's say, I request somting like:

SELECT * FROM "SOME_VIEW" WHERE "VALUE" = '20'

As a result I got nothing, even though there a lot of records with "VALUE" = '20'. The problem takes plase with any value from "VALUE", but only with that specific column. It keeps even when I add another conditions not relaved to column "VALUE". And the form "VALUE" IN (20) also makes no sense. It also makes no sense if I use quotes ('20') or not (20). So if I filter column "VALUE" of my view with any single value it contains, HANA acts like there is no such records.

Howewer, when I add another condition to "VALUE" column, everything becames Ok, for example:

... WHERE "VALUE" IN ('20', '21')

returns records with 20 or 21 in "VALUE", and

... WHERE "VALUE" IN ('20', '-1')

returns records with 20 as there is no records with -1 in "VALUE".

The problem isn't appear if I create table as

CREATE COLUMN TABLE "SOME_TABLE" AS (SELECT * FROM "SOME_VIEW")

and then apply condition to "SOME_TABLE" instead of "SOME_VIEW". But I don't need table.

So is it me or HANA or some feature I'm not familiar with?

pfefferf
Active Contributor
0 Kudos

How does the definition of the view look like? Where do you execute the queries, directly on db level within e.g. a SQL console?

0 Kudos

I make a call from SQL console, but error appears even when call executed by xsjs.

In general the definition of "SOME_VIEW" looks like:

SELECT
    T1."VALUE", ... -- other columns
FROM (
    SELECT
        "VALUE", ...
        MAX(...) AS ...
    FROM "ANOTHER_VIEW_1"
    GROUP BY 
        "VALUE", ...
    UNION ALL
    SELECT 
        "BATCH_ID", ...
    FROM "ANOTHER_VIEW_2"
) AS T1
    LEFT JOIN "VALUES_TABLE" AS T2
        ON T1."VALUE" = T2."VALUE"
    LEFT JOIN ... -- other left joins

Problem are not observed if I apply WHERE "VALUE" = '20' to T1 or "VALUES_TABLE" itself.

I noticed that problem keeps for LEFT, INNER or FULL JOIN between T1 and T2, but not for RIGHT JOIN - in this case problem goes away.

I have to mention that T1 and T2 each have 3450 distinct "VALUE", and LEFT or RIGHT joined T1 and T2 also have 3450 of distinct "VALUE" (in T2 that column is PK), so "VALUE" column in T1 and T2 is fully matches each other (consistent) and there should not be difference between LEFT, RIGHT and FULL joins.

I also noticed, that if query returns nothing, it executes very quickly, like interpreter at the beginning knows that there will be no records; otherwise it takes several seconds to execute.

Accepted Solutions (0)

Answers (0)