Hi! Thanks for raising this issue. This is unfortunately a bug. I've created a JIRA ticket [1] and you can track the progress of this issue there.
[1] https://issues.apache.org/jira/browse/FLINK-25357 Schwalbe Matthias <matthias.schwa...@viseca.ch> 于2021年12月16日周四 14:51写道： > Probably an oversight … did you actually mean to publish your password? > Better change it the sooner possible … > > > > Thias > > > > > > *From:* cy <caiyi_2...@126.com> > *Sent:* Donnerstag, 16. Dezember 2021 06:55 > *To:* user@flink.apache.org > *Subject:* unexpected result of interval join when using sql > > > > Hi > > Flink 1.14.0 Scala 2.12 > > > > I'm using flink sql interval join ability, here is my table schema and sql > > > > create table `queue_3_ads_ccops_perf_o_ebs_volume_capacity` ( > `dtEventTime` timestamp(3), `dtEventTimeStamp` bigint, `sourceid` string, > `cluster_name` string, `poolname` string, `storage_poolname` string, > `usage` decimal(10, 4), `provisioned_size` decimal(10, 4), `startat` > timestamp(3), `endat` timestamp(3), `vrespool_id` int, `uuid` string, > `version` string, `localTime` timestamp(3), `cluster_id` int, `extend1` > string, `extend2` string, `extend3` string, `mon_ip` string, `bussiness_ip` > string, `datasource` string, `thedate` int, `name` string, `used_size` int, > watermark for `startat` as `startat` - interval '60' minutes ) with ( > 'connector' = 'kafka', 'topic' = > 'queue_3_ads_ccops_perf_o_ebs_volume_capacity', 'format' = 'json', > 'scan.startup.mode' = 'earliest-offset', 'properties.bootstrap.servers' = ' > 10.172.234.67:9092,10.172.234.68:9092,10.172.234.69:9092', ' > properties.group.id' = 'layer-vdisk', 'properties.security.protocol' = > 'SASL_PLAINTEXT', 'properties.sasl.mechanism' = 'SCRAM-SHA-512', > 'properties.sasl.jaas.config' = > 'org.apache.flink.kafka.shaded.org.apache.kafka.common.security.scram.ScramLoginModule > required username="bkdata_admin" password="D41J48Cz3iwW7k6fFogX1A";' ); > > > > SELECT > > source.sourceid AS sourceid, > > cast(source.startat AS timestamp) AS source_startat, > > cast(target.startat AS timestamp) AS target_startat, > > source.used_size AS source_used_size, > > target.used_size AS target_used_size, > > source.usage AS source_usage, > > target.usage AS target_usage > > FROM queue_3_ads_ccops_perf_o_ebs_volume_capacity source, > queue_3_ads_ccops_perf_o_ebs_volume_capacity target > > WHERE source.sourceid = target.sourceid > > AND source.sourceid in ( > > 'volume-9dfed0d9-28b2-418a-9215-ce762ef80920', > > 'volume-9ece34f1-f4bb-475a-8e64-a2e37711b4fc', > > 'volume-9f0ec4cc-5cc4-49a8-b715-a91a25df3793', > > 'volume-9f38e0b3-2324-4505-a8ad-9b1ccb72181f', > > 'volume-9f3ec256-10fb-4d8b-a8cb-8498324cf309' > > ) > > AND source.startat >= FLOOR(target.startat TO HOUR) + INTERVAL '1' HOUR > AND source.startat < FLOOR(target.startat TO HOUR) + INTERVAL '2' HOUR; > > > > and result > > > > I'm confused about first row that source_startat and target_startat was > not matched the time condition. > > Also I try to execute the sql below > > > > SELECT TO_TIMESTAMP('2021-12-13 14:05:06') >= > FLOOR(TO_TIMESTAMP('2021-12-13 12:05:08') TO HOUR) + INTERVAL '1' HOUR AND > TO_TIMESTAMP('2021-12-13 14:05:06') < FLOOR(TO_TIMESTAMP('2021-12-13 > 12:05:08') TO HOUR) + INTERVAL '2' HOUR; > > > > the result false is correct. > > > > So is anything wrong with flink sql interval join? > > > > Need your help, thank you. > > > > > > > > > Diese Nachricht ist ausschliesslich für den Adressaten bestimmt und > beinhaltet unter Umständen vertrauliche Mitteilungen. Da die > Vertraulichkeit von e-Mail-Nachrichten nicht gewährleistet werden kann, > übernehmen wir keine Haftung für die Gewährung der Vertraulichkeit und > Unversehrtheit dieser Mitteilung. Bei irrtümlicher Zustellung bitten wir > Sie um Benachrichtigung per e-Mail und um Löschung dieser Nachricht sowie > eventueller Anhänge. Jegliche unberechtigte Verwendung oder Verbreitung > dieser Informationen ist streng verboten. > > This message is intended only for the named recipient and may contain > confidential or privileged information. As the confidentiality of email > communication cannot be guaranteed, we do not accept any responsibility for > the confidentiality and the intactness of this message. If you have > received it in error, please advise the sender by return e-mail and delete > this message and any attachments. Any unauthorised use or dissemination of > this information is strictly prohibited. >
