[jira] [Created] (TAJO-1795) row_number() works with a table but not with inline view

["Youngkyong Ko (JIRA)" <ji...@apache.org>]

Youngkyong Ko created TAJO-1795:
-----------------------------------

             Summary: row_number() works with a table but not with inline view 
                 Key: TAJO-1795
                 URL: https://issues.apache.org/jira/browse/TAJO-1795
             Project: Tajo
          Issue Type: Bug
    Affects Versions: 0.11.0
         Environment: single node Tajo on Macbook
            Reporter: Youngkyong Ko


I used row_number() to get top 2 results per each window and Q1 works as I intended.
But Q2 didn't. Q2 has same structure but replaced a table with an inline view. Is there any difference to use window function with a table and an inline view?

Q1. 
SELECT i1, rownum, i2, support, confidence, lift  FROM (
  SELECT row_number() over ( partition by i1 order by lift desc ) as rownum, r.*
  FROM t_rule r 
)x
WHERE  rownum <= 2;

-- t_rule 
create table t_rule as
    SELECT item1 as i1, item2 as i2, support, confidence1 as confidence, lift
    FROM rules
    WHERE support > 0.005;

-- Result -- 
baking powder,  1,  other vegetables,  0.007320793,  0.41379312,  2.1385472
baking powder,  2,  whole milk,  0.009252669,  0.5229885,  2.0467935
beef,  1,  root vegetables,  0.017386883,  0.33139536,  3.040367
beef,  2,  pork,  0.007625826,  0.14534883,  2.5211742
berries,  1,  whipped/sour cream,  0.009049314,  0.27217126,  3.7968855
berries,  2,  yogurt,  0.010574479,  0.3180428,  2.2798476

Q2.
SELECT i1, rownum, i2, support, confidence, lift  FROM (
  SELECT row_number() over ( partition by i1 order by lift desc ) as rownum, r.*
  FROM (
    SELECT item1 as i1, item2 as i2, support, confidence1 as confidence, lift
    FROM rules
    WHERE support > 0.005
  ) r 
)x
WHERE  rownum <= 2;

-- RESULT --
baking powder,  1,  other vegetables,  0.007320793,  0.41379312,  2.1385472
baking powder,  2,  whole milk,  0.009252669,  0.5229885,  2.0467935
beef,  1,  root vegetables,  0.017386883,  0.33139536,  3.040367
beef,  2,  pork,  0.007625826,  0.14534883,  2.5211742
beef,  3,  margarine,  0.0062023387,  0.11821705,  2.018515
beef,  4,  butter,  0.005795628,  0.11046512,  1.9934393





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)