From notifications-return-158436-archive-asf-public=cust-asf.ponee.io@asterixdb.apache.org Fri Jun 25 22:19:20 2021 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mxout1-ec2-va.apache.org (mxout1-ec2-va.apache.org [3.227.148.255]) by mx-eu-01.ponee.io (Postfix) with ESMTPS id B5562180674 for ; Sat, 26 Jun 2021 00:19:20 +0200 (CEST) Received: from mail.apache.org (mailroute1-lw-us.apache.org [207.244.88.153]) by mxout1-ec2-va.apache.org (ASF Mail Server at mxout1-ec2-va.apache.org) with SMTP id 86FF93F1B6 for ; Fri, 25 Jun 2021 22:19:05 +0000 (UTC) Received: (qmail 49817 invoked by uid 500); 25 Jun 2021 22:19:05 -0000 Mailing-List: contact notifications-help@asterixdb.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@asterixdb.apache.org Delivered-To: mailing list notifications@asterixdb.apache.org Received: (qmail 49806 invoked by uid 99); 25 Jun 2021 22:19:05 -0000 Received: from spamproc1-he-de.apache.org (HELO spamproc1-he-de.apache.org) (116.203.196.100) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 25 Jun 2021 22:19:05 +0000 Received: from localhost (localhost [127.0.0.1]) by spamproc1-he-de.apache.org (ASF Mail Server at spamproc1-he-de.apache.org) with ESMTP id 991F81FF3A1 for ; Fri, 25 Jun 2021 22:19:04 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamproc1-he-de.apache.org X-Spam-Flag: NO X-Spam-Score: -110.509 X-Spam-Level: X-Spam-Status: No, score=-110.509 tagged_above=-999 required=6.31 tests=[ENV_AND_HDR_SPF_MATCH=-0.5, RCVD_IN_HOSTKARMA_W=-2.5, RCVD_IN_MSPIKE_H3=0.001, RCVD_IN_MSPIKE_WL=0.001, SPF_PASS=-0.001, USER_IN_DEF_SPF_WL=-7.5, USER_IN_WELCOMELIST=-0.01, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-ec2-va.apache.org ([116.203.227.195]) by localhost (spamproc1-he-de.apache.org [116.203.196.100]) (amavisd-new, port 10024) with ESMTP id E4EmObXaC2TP for ; Fri, 25 Jun 2021 22:19:03 +0000 (UTC) Received-SPF: Pass (mailfrom) identity=mailfrom; client-ip=95.216.194.37; helo=mxout1-he-de.apache.org; envelope-from=jira@apache.org; receiver= Received: from mxout1-he-de.apache.org (mxout1-he-de.apache.org [95.216.194.37]) by mx1-ec2-va.apache.org (ASF Mail Server at mx1-ec2-va.apache.org) with ESMTPS id 053B5BC489 for ; Fri, 25 Jun 2021 22:19:03 +0000 (UTC) Received: from mail.apache.org (mailroute1-lw-us.apache.org [207.244.88.153]) by mxout1-he-de.apache.org (ASF Mail Server at mxout1-he-de.apache.org) with SMTP id 7FBCF5FEF6 for ; Fri, 25 Jun 2021 22:19:01 +0000 (UTC) Received: (qmail 49734 invoked by uid 99); 25 Jun 2021 22:19:01 -0000 Received: from mailrelay1-he-de.apache.org (HELO mailrelay1-he-de.apache.org) (116.203.21.61) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 25 Jun 2021 22:19:01 +0000 Received: from jira2-he-de.apache.org (jira2-he-de.apache.org [168.119.33.54]) by mailrelay1-he-de.apache.org (ASF Mail Server at mailrelay1-he-de.apache.org) with ESMTPS id 2E5613E8A5 for ; Fri, 25 Jun 2021 22:19:00 +0000 (UTC) Received: from jira2-he-de.apache.org (localhost.localdomain [127.0.0.1]) by jira2-he-de.apache.org (ASF Mail Server at jira2-he-de.apache.org) with ESMTP id 0F33FC801FE for ; Fri, 25 Jun 2021 22:19:00 +0000 (UTC) Date: Fri, 25 Jun 2021 22:19:00 +0000 (UTC) From: "Glenn Justo Galvizo (Jira)" To: notifications@asterixdb.incubator.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Assigned] (ASTERIXDB-2830) Fix INLJ Bug for LOJ Queries w/ Array Indexes MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/ASTERIXDB-2830?page=3Dcom.atla= ssian.jira.plugin.system.issuetabpanels:all-tabpanel ] Glenn Justo Galvizo reassigned ASTERIXDB-2830: ---------------------------------------------- Assignee: Glenn Justo Galvizo > Fix INLJ Bug for LOJ Queries w/ Array Indexes > --------------------------------------------- > > Key: ASTERIXDB-2830 > URL: https://issues.apache.org/jira/browse/ASTERIXDB-2830 > Project: Apache AsterixDB > Issue Type: Bug > Components: IDX - Indexes > Reporter: Glenn Justo Galvizo > Assignee: Glenn Justo Galvizo > Priority: Major > > Currently the INLJ query below w/ the following data does not correctly p= erform a LOJ, rather it performs an inner join.=C2=A0 > =C2=A0 > =C2=A0 > {code:java} > SET `compiler.arrayindex` "true"; > DROP DATAVERSE TestYelp IF EXISTS; > CREATE DATAVERSE TestYelp; > USE TestYelp; > CREATE TYPE CheckinType AS { > checkin_id: uuid, > business_id: string, > dates: [string] > }; > CREATE TYPE CheckinDatesToMarkersType AS { > marker: string, > datetime: string, > date: string, > time: string > }; > CREATE DATASET YelpCheckin(CheckinType) PRIMARY KEY checkin_id AUTOGENERA= TED; > CREATE DATASET YelpCheckinDateMarkers (CheckinDatesToMarkersType) PRIMARY= KEY marker; > CREATE INDEX IdxYelpCheckinDates ON YelpCheckin (UNNEST dates); > INSERT INTO YelpCheckin ( > [ > { > "business_id": "--1UhMGODdWsrMastO9DZw", > "dates": [ > "2016-04-26 19:49:16", > "2016-08-30 18:36:57", > "2016-10-15 02:45:18", > "2016-11-18 01:54:50", > "2017-04-20 18:39:06", > "2017-05-03 17:58:02", > "2019-03-19 22:04:48" > ] > }, > { > "business_id": "--EF5N7P70J_UYBTPypYlA", > "dates": [ > "2018-05-25 19:52:07", > "2018-09-18 16:09:44", > "2019-10-18 21:29:09" > ] > }, > { > "business_id": "--Ni3oJ4VOqfOEu7Sj2Vzg", > "dates": [ > "2019-06-07 17:54:58" > ] > }, > { > "business_id": "--Y1Adl1YUWfYIRSd8vkmA", > "dates": [ > "2011-05-03 20:54:05", > "2011-08-23 20:49:45", > "2014-12-04 06:13:01", > "2016-11-16 19:25:55" > ] > }, > { > "business_id": "--YPwqIlRJrhHkJcjY3eiA", > "dates": [ > "2016-06-18 21:35:45", > "2016-10-15 18:17:51" > ] > }, > { > "business_id": "--e8PjCNhEz32pprnPhCwQ", > "dates": [ > "2015-04-02 21:45:17" > ] > }, > { > "business_id": "--kinfHwmtdjz03g8B8z8Q", > "dates": [ > "2014-08-27 17:49:18", > "2015-12-19 21:30:31", > "2018-11-27 15:53:50" > ] > }, > { > "business_id": "--q6datkI-f0EoVheXNEeQ", > "dates": [ > "2014-01-28 20:56:04", > "2014-11-16 16:11:58", > "2015-11-15 19:21:53", > "2015-11-15 19:33:39" > ] > }, > { > "business_id": "--qvQS4MigHPykD2GV0-zw", > "dates": [ > "2019-04-11 18:30:12" > ] > }, > { > "business_id": "--wIGbLEhlpl_UeAIyDmZQ", > "dates": [ > "2015-06-06 20:01:06", > "2019-03-14 22:01:52" > ] > }, > { > "business_id": "-0FA-Qdi3SPYIoJz9UQw-A", > "dates": [ > "2018-09-29 18:55:17", > "2018-10-20 16:48:05", > "2018-10-20 22:20:24" > ] > }, > { > "business_id": "-0Hj1hb_XW6ybWq2M7QhGA", > "dates": [ > "2011-04-23 21:11:22", > "2014-05-04 19:42:48", > "2014-05-11 19:16:08", > "2014-06-04 19:14:18", > "2015-12-05 19:22:42", > "2017-05-15 23:19:00" > ] > }, > { > "business_id": "-0KMvRFwDWdVBeTpT11iHw", > "dates": [ > "2012-07-13 21:43:57", > "2016-12-24 02:27:31", > "2017-08-31 00:35:26" > ] > }, > { > "business_id": "-0LPtgJC31FWMrMv317p0Q", > "dates": [ > "2013-04-13 12:35:33", > "2013-08-19 23:35:49", > "2013-10-04 19:14:56" > ] > }, > { > "business_id": "-0M3o2uWBnQZwd3hmfEwuw", > "dates": [ > "2016-09-10 19:26:19", > "2018-09-08 14:15:37", > "2019-09-13 22:47:25" > ] > }, > { > "business_id": "-0RRiWDtfnS16AKCtfvBZg", > "dates": [ > "2017-05-19 14:30:16", > "2017-05-19 14:30:25", > "2017-08-28 15:49:37", > "2017-09-20 20:19:51", > "2017-10-01 16:31:05", > "2017-10-01 16:56:27", > "2017-12-27 23:33:20" > ] > }, > { > "business_id": "-0Soj75v-XoRcf2ERr8Bmg", > "dates": [ > "2019-06-05 18:22:49" > ] > }, > { > "business_id": "-0ZumLlFjMh4ZW1z2nXGug", > "dates": [ > "2011-09-24 21:37:32", > "2014-03-10 20:20:07", > "2015-05-27 00:40:24", > "2015-08-29 17:58:15", > "2018-03-16 15:03:26" > ] > }, > { > "business_id": "-0aOudcaAyac0VJbMX-L1g", > "dates": [ > "2015-03-16 23:51:16", > "2015-12-21 04:48:01", > "2016-10-28 20:22:42", > "2016-10-28 20:23:00" > ] > }, > { > "business_id": "-0b86isaXMY0v4g-V8GZ9Q", > "dates": [ > "2013-10-22 16:49:21", > "2014-11-21 17:39:24" > ] > }, > { > "business_id": "-0d-BfFSU0bwLcnMaGRxYw", > "dates": [ > "2014-08-07 18:30:48", > "2014-09-16 20:41:45", > "2014-10-12 23:22:27", > "2015-07-21 20:43:56", > "2015-07-21 20:45:07" > ] > }, > { > "business_id": "-0jz6c3C6i7RG7Ag22K-Pg", > "dates": [ > "2015-05-02 19:49:05", > "2015-05-06 03:52:18", > "2015-09-26 01:13:19" > ] > }, > { > "business_id": "-0y3MZU2oYP8r1ruDP1bfQ", > "dates": [ > "2015-04-11 13:14:14", > "2015-11-21 16:05:56", > "2016-05-06 14:10:04", > "2017-08-09 15:15:10", > "2017-10-21 15:12:56" > ] > }, > { > "business_id": "-1BPe8UjF2_l3nVk-DFUjA", > "dates": [ > "2015-12-03 18:44:00", > "2016-03-17 18:19:21", > "2016-11-02 15:58:38" > ] > }, > { > "business_id": "-1E2CQu_38mkghvmZgCCRw", > "dates": [ > "2019-04-04 22:02:37" > ] > }, > { > "business_id": "-1wzk43IZ5D9Ysu6kzb5xA", > "dates": [ > "2019-02-27 14:03:08" > ] > }, > { > "business_id": "-23R9P2eG7VTc6DVLjFKzA", > "dates": [ > "2011-12-21 19:02:51", > "2012-04-15 04:21:39", > "2012-04-15 14:23:56", > "2013-06-30 22:39:51", > "2013-10-04 20:34:13", > "2014-07-16 02:28:40" > ] > }, > { > "business_id": "-26MGfikhJiTfCI-GqmzhQ", > "dates": [ > "2018-06-13 20:16:07" > ] > }, > { > "business_id": "-2bLuJsMZ0WhI9daurVQNQ", > "dates": [ > "2015-05-29 16:46:17", > "2015-06-01 15:03:53" > ] > }, > { > "business_id": "-2hDBMaza_ldqnZdiU06LQ", > "dates": [ > "2011-10-08 12:02:23", > "2014-08-18 02:11:11", > "2016-01-07 05:27:51", > "2016-10-21 20:15:55", > "2016-12-01 03:57:10", > "2016-12-29 01:54:42", > "2018-07-22 19:55:31", > "2018-09-07 01:42:54", > "2019-03-08 03:41:06" > ] > } > ] > ); > INSERT INTO YelpCheckinDateMarkers ( > [ > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f5", "datetime": "= 2016-04-26 19:49:16", "date": "2016-04-26", "time": "19:49:16" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f6", "datetime": "= 2016-08-30 18:36:57", "date": "2016-08-30", "time": "18:36:57" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f7", "datetime": "= 2016-10-15 02:45:18", "date": "2016-10-15", "time": "02:45:18" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f8", "datetime": "= 2016-11-18 01:54:50", "date": "2016-11-18", "time": "01:54:50" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882f9", "datetime": "= 2017-04-20 18:39:06", "date": "2017-04-20", "time": "18:39:06" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882fa", "datetime": "= 2017-05-03 17:58:02", "date": "2017-05-03", "time": "17:58:02" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882fb", "datetime": "= 2019-03-19 22:04:48", "date": "2019-03-19", "time": "22:04:48" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882fc", "datetime": "= 2018-05-25 19:52:07", "date": "2018-05-25", "time": "19:52:07" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882fd", "datetime": "= 2018-09-18 16:09:44", "date": "2018-09-18", "time": "16:09:44" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882fe", "datetime": "= 2019-10-18 21:29:09", "date": "2019-10-18", "time": "21:29:09" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c36882ff", "datetime": "= 2011-05-03 20:54:05", "date": "2011-05-03", "time": "20:54:05" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688300", "datetime": "= 2011-08-23 20:49:45", "date": "2011-08-23", "time": "20:49:45" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688301", "datetime": "= 2014-12-04 06:13:01", "date": "2014-12-04", "time": "06:13:01" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688302", "datetime": "= 2016-11-16 19:25:55", "date": "2016-11-16", "time": "19:25:55" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688303", "datetime": "= 2016-06-18 21:35:45", "date": "2016-06-18", "time": "21:35:45" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688304", "datetime": "= 2016-10-15 18:17:51", "date": "2016-10-15", "time": "18:17:51" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688305", "datetime": "= 2014-08-27 17:49:18", "date": "2014-08-27", "time": "17:49:18" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688306", "datetime": "= 2015-12-19 21:30:31", "date": "2015-12-19", "time": "21:30:31" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688307", "datetime": "= 2018-11-27 15:53:50", "date": "2018-11-27", "time": "15:53:50" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688308", "datetime": "= 2019-04-11 18:30:12", "date": "2019-04-11", "time": "18:30:12" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688309", "datetime": "= 2015-06-06 20:01:06", "date": "2015-06-06", "time": "20:01:06" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368830a", "datetime": "= 2019-03-14 22:01:52", "date": "2019-03-14", "time": "22:01:52" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368830b", "datetime": "= 2012-07-13 21:43:57", "date": "2012-07-13", "time": "21:43:57" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368830c", "datetime": "= 2016-12-24 02:27:31", "date": "2016-12-24", "time": "02:27:31" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368830d", "datetime": "= 2017-08-31 00:35:26", "date": "2017-08-31", "time": "00:35:26" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368830e", "datetime": "= 2013-04-13 12:35:33", "date": "2013-04-13", "time": "12:35:33" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368830f", "datetime": "= 2013-08-19 23:35:49", "date": "2013-08-19", "time": "23:35:49" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688310", "datetime": "= 2013-10-04 19:14:56", "date": "2013-10-04", "time": "19:14:56" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688311", "datetime": "= 2016-09-10 19:26:19", "date": "2016-09-10", "time": "19:26:19" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688312", "datetime": "= 2018-09-08 14:15:37", "date": "2018-09-08", "time": "14:15:37" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688313", "datetime": "= 2019-09-13 22:47:25", "date": "2019-09-13", "time": "22:47:25" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688314", "datetime": "= 2019-06-05 18:22:49", "date": "2019-06-05", "time": "18:22:49" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688315", "datetime": "= 2015-03-16 23:51:16", "date": "2015-03-16", "time": "23:51:16" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688316", "datetime": "= 2015-12-21 04:48:01", "date": "2015-12-21", "time": "04:48:01" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688317", "datetime": "= 2016-10-28 20:22:42", "date": "2016-10-28", "time": "20:22:42" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688318", "datetime": "= 2016-10-28 20:23:00", "date": "2016-10-28", "time": "20:23:00" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688319", "datetime": "= 2014-08-07 18:30:48", "date": "2014-08-07", "time": "18:30:48" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368831a", "datetime": "= 2014-09-16 20:41:45", "date": "2014-09-16", "time": "20:41:45" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368831b", "datetime": "= 2014-10-12 23:22:27", "date": "2014-10-12", "time": "23:22:27" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368831c", "datetime": "= 2015-07-21 20:43:56", "date": "2015-07-21", "time": "20:43:56" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368831d", "datetime": "= 2015-07-21 20:45:07", "date": "2015-07-21", "time": "20:45:07" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368831e", "datetime": "= 2015-05-02 19:49:05", "date": "2015-05-02", "time": "19:49:05" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368831f", "datetime": "= 2015-05-06 03:52:18", "date": "2015-05-06", "time": "03:52:18" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688320", "datetime": "= 2015-09-26 01:13:19", "date": "2015-09-26", "time": "01:13:19" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688321", "datetime": "= 2019-04-04 22:02:37", "date": "2019-04-04", "time": "22:02:37" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688322", "datetime": "= 2011-12-21 19:02:51", "date": "2011-12-21", "time": "19:02:51" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688323", "datetime": "= 2012-04-15 04:21:39", "date": "2012-04-15", "time": "04:21:39" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688324", "datetime": "= 2012-04-15 14:23:56", "date": "2012-04-15", "time": "14:23:56" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688325", "datetime": "= 2013-06-30 22:39:51", "date": "2013-06-30", "time": "22:39:51" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688326", "datetime": "= 2013-10-04 20:34:13", "date": "2013-10-04", "time": "20:34:13" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688327", "datetime": "= 2014-07-16 02:28:40", "date": "2014-07-16", "time": "02:28:40" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688328", "datetime": "= 2018-06-13 20:16:07", "date": "2018-06-13", "time": "20:16:07" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688329", "datetime": "= 2011-10-08 12:02:23", "date": "2011-10-08", "time": "12:02:23" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832a", "datetime": "= 2014-08-18 02:11:11", "date": "2014-08-18", "time": "02:11:11" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832b", "datetime": "= 2016-01-07 05:27:51", "date": "2016-01-07", "time": "05:27:51" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832c", "datetime": "= 2016-10-21 20:15:55", "date": "2016-10-21", "time": "20:15:55" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832d", "datetime": "= 2016-12-01 03:57:10", "date": "2016-12-01", "time": "03:57:10" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832e", "datetime": "= 2016-12-29 01:54:42", "date": "2016-12-29", "time": "01:54:42" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c368832f", "datetime": "= 2018-07-22 19:55:31", "date": "2018-07-22", "time": "19:55:31" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688330", "datetime": "= 2018-09-07 01:42:54", "date": "2018-09-07", "time": "01:42:54" }, > { "marker": "4fa1e250-7a68-3adb-04ec-6569c3688331", "datetime": "= 2019-03-08 03:41:06", "date": "2019-03-08", "time": "03:41:06" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848381", "datetime": "= 2019-06-07 17:54:58", "date": "2019-06-07", "time": "17:54:58" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848382", "datetime": "= 2015-04-02 21:45:17", "date": "2015-04-02", "time": "21:45:17" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848383", "datetime": "= 2014-01-28 20:56:04", "date": "2014-01-28", "time": "20:56:04" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848384", "datetime": "= 2014-11-16 16:11:58", "date": "2014-11-16", "time": "16:11:58" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848385", "datetime": "= 2015-11-15 19:21:53", "date": "2015-11-15", "time": "19:21:53" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848386", "datetime": "= 2015-11-15 19:33:39", "date": "2015-11-15", "time": "19:33:39" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848387", "datetime": "= 2018-09-29 18:55:17", "date": "2018-09-29", "time": "18:55:17" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848388", "datetime": "= 2018-10-20 16:48:05", "date": "2018-10-20", "time": "16:48:05" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848389", "datetime": "= 2018-10-20 22:20:24", "date": "2018-10-20", "time": "22:20:24" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784838a", "datetime": "= 2011-04-23 21:11:22", "date": "2011-04-23", "time": "21:11:22" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784838b", "datetime": "= 2014-05-04 19:42:48", "date": "2014-05-04", "time": "19:42:48" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784838c", "datetime": "= 2014-05-11 19:16:08", "date": "2014-05-11", "time": "19:16:08" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784838d", "datetime": "= 2014-06-04 19:14:18", "date": "2014-06-04", "time": "19:14:18" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784838e", "datetime": "= 2015-12-05 19:22:42", "date": "2015-12-05", "time": "19:22:42" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784838f", "datetime": "= 2017-05-15 23:19:00", "date": "2017-05-15", "time": "23:19:00" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848390", "datetime": "= 2017-05-19 14:30:16", "date": "2017-05-19", "time": "14:30:16" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848391", "datetime": "= 2017-05-19 14:30:25", "date": "2017-05-19", "time": "14:30:25" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848392", "datetime": "= 2017-08-28 15:49:37", "date": "2017-08-28", "time": "15:49:37" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848393", "datetime": "= 2017-09-20 20:19:51", "date": "2017-09-20", "time": "20:19:51" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848394", "datetime": "= 2017-10-01 16:31:05", "date": "2017-10-01", "time": "16:31:05" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848395", "datetime": "= 2017-10-01 16:56:27", "date": "2017-10-01", "time": "16:56:27" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848396", "datetime": "= 2017-12-27 23:33:20", "date": "2017-12-27", "time": "23:33:20" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848397", "datetime": "= 2011-09-24 21:37:32", "date": "2011-09-24", "time": "21:37:32" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848398", "datetime": "= 2014-03-10 20:20:07", "date": "2014-03-10", "time": "20:20:07" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f067848399", "datetime": "= 2015-05-27 00:40:24", "date": "2015-05-27", "time": "00:40:24" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784839a", "datetime": "= 2015-08-29 17:58:15", "date": "2015-08-29", "time": "17:58:15" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784839b", "datetime": "= 2018-03-16 15:03:26", "date": "2018-03-16", "time": "15:03:26" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784839c", "datetime": "= 2013-10-22 16:49:21", "date": "2013-10-22", "time": "16:49:21" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784839d", "datetime": "= 2014-11-21 17:39:24", "date": "2014-11-21", "time": "17:39:24" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784839e", "datetime": "= 2015-04-11 13:14:14", "date": "2015-04-11", "time": "13:14:14" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f06784839f", "datetime": "= 2015-11-21 16:05:56", "date": "2015-11-21", "time": "16:05:56" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a0", "datetime": "= 2016-05-06 14:10:04", "date": "2016-05-06", "time": "14:10:04" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a1", "datetime": "= 2017-08-09 15:15:10", "date": "2017-08-09", "time": "15:15:10" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a2", "datetime": "= 2017-10-21 15:12:56", "date": "2017-10-21", "time": "15:12:56" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a3", "datetime": "= 2015-12-03 18:44:00", "date": "2015-12-03", "time": "18:44:00" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a4", "datetime": "= 2016-03-17 18:19:21", "date": "2016-03-17", "time": "18:19:21" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a5", "datetime": "= 2016-11-02 15:58:38", "date": "2016-11-02", "time": "15:58:38" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a6", "datetime": "= 2019-02-27 14:03:08", "date": "2019-02-27", "time": "14:03:08" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a7", "datetime": "= 2015-05-29 16:46:17", "date": "2015-05-29", "time": "16:46:17" }, > { "marker": "991ed2f7-3374-33a5-ee65-77f0678483a8", "datetime": "= 2015-06-01 15:03:53", "date": "2015-06-01", "time": "15:03:53" }, > { "marker": "BAD_ENTRY_1", "datetime": "NA", "date": "NA", "time"= : "NA" }, > { "marker": "BAD_ENTRY_2", "datetime": "NA", "date": "NA", "time"= : "NA" }, > { "marker": "BAD_ENTRY_3", "datetime": "NA", "date": "NA", "time"= : "NA" }, > { "marker": "BAD_ENTRY_4", "datetime": "NA", "date": "NA", "time"= : "NA" }, > { "marker": "BAD_ENTRY_5", "datetime": "NA", "date": "NA", "time"= : "NA" }, > { "marker": "BAD_ENTRY_6", "datetime": "NA", "date": "NA", "time"= : "NA" } > ] > ); > SELECT COUNT(*) > FROM YelpCheckinDateMarkers M > LEFT OUTER JOIN ( > SELECT VALUE CD > FROM YelpCheckin C, C.dates CD > ) AS D ON D /* + indexnl */ =3D M.datetime;=20 > {code} > This query gives a count of 101, when it should be 107 (can be found by r= emoving the indexnl join hint). > =C2=A0 -- This message was sent by Atlassian Jira (v8.3.4#803005)