Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

WINDOWSTART doesn't work in push query predicate #4482

Closed
rmoff opened this issue Feb 7, 2020 · 1 comment · Fixed by #4500
Closed

WINDOWSTART doesn't work in push query predicate #4482

rmoff opened this issue Feb 7, 2020 · 1 comment · Fixed by #4500
Assignees
Labels
Milestone

Comments

@rmoff
Copy link
Member

rmoff commented Feb 7, 2020

Tested using ksqlDB built on 20200207 from commit hash f8bb986

Works with pull query

ksql> SELECT TIMESTAMPTOSTRING(WINDOWSTART, 'yyyy-MM-dd HH:mm:ss') AS WINDOW_START_TS,
>       FULL_NAME,
>       RATINGS_COUNT
>  FROM RATINGS_PER_CUSTOMER_PER_MINUTE
>  WHERE ROWKEY='Rica Blaisdell'
>  AND  WINDOWSTART > '2020-02-07T17:08:00.000'
>  ;
+----------------------------+----------------------------+----------------------------+
|WINDOW_START_TS             |FULL_NAME                   |RATINGS_COUNT               |
+----------------------------+----------------------------+----------------------------+
|2020-02-07 17:09:00         |Rica Blaisdell              |8                           |
|2020-02-07 17:10:00         |Rica Blaisdell              |14                          |
|2020-02-07 17:11:00         |Rica Blaisdell              |6                           |
|2020-02-07 17:12:00         |Rica Blaisdell              |8                           |
|2020-02-07 17:13:00         |Rica Blaisdell              |1                           |
Query terminated

Doesn't work in push query

ksql> SELECT TIMESTAMPTOSTRING(WINDOWSTART, 'yyyy-MM-dd HH:mm:ss') AS WINDOW_START_TS,
>       FULL_NAME,
>       RATINGS_COUNT
>  FROM RATINGS_PER_CUSTOMER_PER_MINUTE
>  WHERE ROWKEY='Rica Blaisdell'
>  AND  WINDOWSTART > '2020-02-07T17:08:00.000'
>  EMIT CHANGES;
Code generation failed for Predicate: Operator GREATER_THAN cannot be used to compare BIGINT and STRING. expression:((ROWKEY = 'Rica Blaisdell') AND (WINDOWSTART > '2020-02-07T17:08:00.000')), schema:`ROWKEY` STRING KEY, `FULL_NAME` STRING, `RATINGS_COUNT` BIGINT, `ROWTIME` BIGINT, `ROWKEY` STRING, `WINDOWSTART` BIGINT, `WINDOWEND` BIGINT
ksql>

Note : works fine in push query if using rowtime

ksql> SELECT TIMESTAMPTOSTRING(WINDOWSTART, 'yyyy-MM-dd HH:mm:ss') AS WINDOW_START_TS,
>       FULL_NAME,
>       RATINGS_COUNT
>  FROM RATINGS_PER_CUSTOMER_PER_MINUTE
>  WHERE ROWKEY='Rica Blaisdell'
>  AND  rowtime > '2020-02-07T17:08:00.000'
>  EMIT CHANGES;
+----------------------------+----------------------------+----------------------------+
|WINDOW_START_TS             |FULL_NAME                   |RATINGS_COUNT               |
+----------------------------+----------------------------+----------------------------+
|2020-02-07 17:08:00         |Rica Blaisdell              |6                           |
|2020-02-07 17:09:00         |Rica Blaisdell              |8                           |
|2020-02-07 17:10:00         |Rica Blaisdell              |14                          |
|2020-02-07 17:11:00         |Rica Blaisdell              |6                           |
|2020-02-07 17:12:00         |Rica Blaisdell              |8                           |
|2020-02-07 17:13:00         |Rica Blaisdell              |5                           |
|2020-02-07 17:14:00         |Rica Blaisdell              |5                           |
|2020-02-07 17:15:00         |Rica Blaisdell              |2                           |
^CQuery terminated
@rmoff rmoff added the bug label Feb 7, 2020
@big-andy-coates big-andy-coates added this to the 0.7.0 milestone Feb 7, 2020
@rmoff
Copy link
Member Author

rmoff commented Feb 7, 2020

CREATE TABLE RATINGS_PER_CUSTOMER_PER_MINUTE AS 
SELECT FULL_NAME,COUNT(*) AS RATINGS_COUNT
  FROM RATINGS_WITH_CUSTOMER_DATA 
        WINDOW TUMBLING (SIZE 1 MINUTE) 
  GROUP BY FULL_NAME
  EMIT CHANGES;

ksql> describe RATINGS_PER_CUSTOMER_PER_MINUTE;

Name                 : RATINGS_PER_CUSTOMER_PER_MINUTE
 Field         | Type
-------------------------------------------------------------------
 ROWTIME       | BIGINT           (system)
 ROWKEY        | VARCHAR(STRING)  (system) (Window type: TUMBLING)
 FULL_NAME     | VARCHAR(STRING)
 RATINGS_COUNT | BIGINT
-------------------------------------------------------------------
For runtime statistics and query details run: DESCRIBE EXTENDED <Stream,Table>;
ksql>

big-andy-coates added a commit to big-andy-coates/ksql that referenced this issue Feb 10, 2020
Fixes: confluentinc#4482

Comparison and Between expressions in the WHERE clause already support magic conversion from a STRING containing a ISO formatted datetime into a BIGINT for the `ROWTIME` column.

This changes extends the support to cover the `WINDOWSTART` and `WINDOWEND` columns.

The change also fixes a bug where by a numeric Between expression on `ROWTIME` resulted in a class-cast exception, e.g. `WHERE ROWTIME < 123546794894`.
big-andy-coates added a commit that referenced this issue Feb 10, 2020
Fixes: #4482

Comparison and Between expressions in the WHERE clause already support magic conversion from a STRING containing a ISO formatted datetime into a BIGINT for the `ROWTIME` column.

This changes extends the support to cover the `WINDOWSTART` and `WINDOWEND` columns.

The change also fixes a bug where by a numeric Between expression on `ROWTIME` resulted in a class-cast exception, e.g. `WHERE ROWTIME < 123546794894`.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants