[jira] [Commented] (FLINK-11242) The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
[
https://issues.apache.org/jira/browse/FLINK-11242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17334044#comment-17334044
]
Flink Jira Bot commented on FLINK-11242:
This issue was marked "stale-assigned" and has not received an update in 7
days. It is now automatically unassigned. If you are still working on it, you
can assign it to yourself again. Please also give an update about the status of
the work.
> The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
> ---
>
> Key: FLINK-11242
> URL: https://issues.apache.org/jira/browse/FLINK-11242
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / API
>Affects Versions: 1.8.0
>Reporter: sunjincheng
>Assignee: Dian Fu
>Priority: Major
> Labels: stale-assigned
>
> Schema:
> {code:java}
> Table(c_id:String, o_c_id:String){code}
> Data:
> {code:java}
> c_001,c_001
> c_002,c_002
> c_003,null{code}
> TableAPI：
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id === Null(Types.STRING)){code}
> Result:
> We expect `c_003, null`, but got empty.
> BTW, we can got the correct result by follows TableAPI:
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id isNull){code}
>
> I think is better for we let `.where('o_c_id === Null(Types.STRING))` work
> well. What do you think?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (FLINK-11242) The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
[
https://issues.apache.org/jira/browse/FLINK-11242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17323403#comment-17323403
]
Flink Jira Bot commented on FLINK-11242:
This issue is assigned but has not received an update in 7 days so it has been
labeled "stale-assigned". If you are still working on the issue, please give an
update and remove the label. If you are no longer working on the issue, please
unassign so someone else may work on it. In 7 days the issue will be
automatically unassigned.
> The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
> ---
>
> Key: FLINK-11242
> URL: https://issues.apache.org/jira/browse/FLINK-11242
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / API
>Affects Versions: 1.8.0
>Reporter: sunjincheng
>Assignee: Dian Fu
>Priority: Major
> Labels: stale-assigned
>
> Schema:
> {code:java}
> Table(c_id:String, o_c_id:String){code}
> Data:
> {code:java}
> c_001,c_001
> c_002,c_002
> c_003,null{code}
> TableAPI：
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id === Null(Types.STRING)){code}
> Result:
> We expect `c_003, null`, but got empty.
> BTW, we can got the correct result by follows TableAPI:
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id isNull){code}
>
> I think is better for we let `.where('o_c_id === Null(Types.STRING))` work
> well. What do you think?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (FLINK-11242) The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
[
https://issues.apache.org/jira/browse/FLINK-11242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16908025#comment-16908025
]
Jark Wu commented on FLINK-11242:
-
I agree with [~fhueske], the three-value logic is a widely known fundamental
concept and supported in the known databases/systems. If we accept {{o_c_id ===
Null(Types.STRING)}}, all the condition syntax will be changed which will
affect the join/exist/in operations, and it will make users confused why the
result of join/exist/in is different when using Table API and SQL.
So, I'd be in favor of following SQL. Shall we close this issue?
> The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
> ---
>
> Key: FLINK-11242
> URL: https://issues.apache.org/jira/browse/FLINK-11242
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / API
>Affects Versions: 1.8.0
>Reporter: sunjincheng
>Assignee: Dian Fu
>Priority: Major
>
> Schema:
> {code:java}
> Table(c_id:String, o_c_id:String){code}
> Data:
> {code:java}
> c_001,c_001
> c_002,c_002
> c_003,null{code}
> TableAPI：
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id === Null(Types.STRING)){code}
> Result:
> We expect `c_003, null`, but got empty.
> BTW, we can got the correct result by follows TableAPI:
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id isNull){code}
>
> I think is better for we let `.where('o_c_id === Null(Types.STRING))` work
> well. What do you think?
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (FLINK-11242) The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
[
https://issues.apache.org/jira/browse/FLINK-11242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16739269#comment-16739269
]
Fabian Hueske commented on FLINK-11242:
---
Hi,
SQL's three-value logic (true, false, null) is not always easy to reason about.
However, I not very comfortable to override it with our own semantics. It's
true that the Table API is not SQL, but both are facing the same issues with
respect to null. So, I'd be in favor of following SQL rather than defining our
own semantics (which might result in even stranger results or inconsistencies).
Best, Fabian
> The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
> ---
>
> Key: FLINK-11242
> URL: https://issues.apache.org/jira/browse/FLINK-11242
> Project: Flink
> Issue Type: Bug
> Components: Table API SQL
>Affects Versions: 1.8.0
>Reporter: sunjincheng
>Assignee: Dian Fu
>Priority: Major
>
> Schema:
> {code:java}
> Table(c_id:String, o_c_id:String){code}
> Data:
> {code:java}
> c_001,c_001
> c_002,c_002
> c_003,null{code}
> TableAPI：
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id === Null(Types.STRING)){code}
> Result:
> We expect `c_003, null`, but got empty.
> BTW, we can got the correct result by follows TableAPI:
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id isNull){code}
>
> I think is better for we let `.where('o_c_id === Null(Types.STRING))` work
> well. What do you think?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (FLINK-11242) The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
[
https://issues.apache.org/jira/browse/FLINK-11242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16739268#comment-16739268
]
Fabian Hueske commented on FLINK-11242:
---
Hi,
SQL's three-value logic (true, false, null) is not always easy to reason about.
However, I not very comfortable to override it with our own semantics. It's
true that the Table API is not SQL, but both are facing the same issues with
respect to null. So, I'd be in favor of following SQL rather than defining our
own semantics (which might result in even stranger results or inconsistencies).
Best, Fabian
> The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
> ---
>
> Key: FLINK-11242
> URL: https://issues.apache.org/jira/browse/FLINK-11242
> Project: Flink
> Issue Type: Bug
> Components: Table API SQL
>Affects Versions: 1.8.0
>Reporter: sunjincheng
>Assignee: Dian Fu
>Priority: Major
>
> Schema:
> {code:java}
> Table(c_id:String, o_c_id:String){code}
> Data:
> {code:java}
> c_001,c_001
> c_002,c_002
> c_003,null{code}
> TableAPI：
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id === Null(Types.STRING)){code}
> Result:
> We expect `c_003, null`, but got empty.
> BTW, we can got the correct result by follows TableAPI:
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id isNull){code}
>
> I think is better for we let `.where('o_c_id === Null(Types.STRING))` work
> well. What do you think?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (FLINK-11242) The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
[
https://issues.apache.org/jira/browse/FLINK-11242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16731759#comment-16731759
]
sunjincheng commented on FLINK-11242:
-
Hi [~dian.fu] Thanks for the comment, we using calcite parsing the flink SQL,
and calcite have the NULL type(SqlTypeName.NULL), So in SQL `col isNull` not
equal to `col = null` 。But In flink we have no NULL type, we only have NULL
value with specific data type, e.g: Null(STRING). IMO. The problem is how do we
convert `col isNull` and ` col === Null(type)` to the correct RexNode. Form
the view of user, they never know NULL type, in the filter expression, they may
want check col is null by using ` col === Null(type)` .
When the user uses `col === Null(Type)`, flink does not report an error. In
fact, the current implementation where ( `col === Null(Type)`) will always get
the empty data, which is not what the user expects.
I am not sure if my point certain, but from the point of user view, `col ===
Null(Type)` equal to `col.isNull` will meet the user's expectations.
What do you think? [~dian.fu] [~tiwalter] [~fhue...@gmail.com] Welcome any
feedback ...
> The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
> ---
>
> Key: FLINK-11242
> URL: https://issues.apache.org/jira/browse/FLINK-11242
> Project: Flink
> Issue Type: Bug
> Components: Table API SQL
>Affects Versions: 1.8.0
>Reporter: sunjincheng
>Assignee: Dian Fu
>Priority: Major
>
> Schema:
> {code:java}
> Table(c_id:String, o_c_id:String){code}
> Data:
> {code:java}
> c_001,c_001
> c_002,c_002
> c_003,null{code}
> TableAPI：
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id === Null(Types.STRING)){code}
> Result:
> We expect `c_003, null`, but got empty.
> BTW, we can got the correct result by follows TableAPI:
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id isNull){code}
>
> I think is better for we let `.where('o_c_id === Null(Types.STRING))` work
> well. What do you think?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (FLINK-11242) The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
[
https://issues.apache.org/jira/browse/FLINK-11242?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16731567#comment-16731567
]
Dian Fu commented on FLINK-11242:
-
Hi [~sunjincheng121] Thanks a lot for reporting this issue. I think this is
not a bug. The expression "o_c_id === Null(Types.STRING))" is equal to "o_c_id
= null" in SQL and the expression "o_c_id isNull" is equal to "o_c_id is null"
in SQL. The behaviors of these two SQL expressions are different (have checked
these two expressions in MySQL). Thoughts?
> The predicate `'o_c_id === Null(Types.STRING)` not work in TableAPI
> ---
>
> Key: FLINK-11242
> URL: https://issues.apache.org/jira/browse/FLINK-11242
> Project: Flink
> Issue Type: Bug
> Components: Table API SQL
>Affects Versions: 1.8.0
>Reporter: sunjincheng
>Assignee: Dian Fu
>Priority: Major
>
> Schema:
> {code:java}
> Table(c_id:String, o_c_id:String){code}
> Data:
> {code:java}
> c_001,c_001
> c_002,c_002
> c_003,null{code}
> TableAPI：
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id === Null(Types.STRING)){code}
> Result:
> We expect `c_003, null`, but got empty.
> BTW, we can got the correct result by follows TableAPI:
> {code:java}
> val result = tab
> .select('c_id, 'o_c_id)
> .where('o_c_id isNull){code}
>
> I think is better for we let `.where('o_c_id === Null(Types.STRING))` work
> well. What do you think?
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
