asterixdb-notifications mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Glenn Justo Galvizo (Jira)" <j...@apache.org>
Subject [jira] [Assigned] (ASTERIXDB-2830) Fix INLJ Bug for LOJ Queries w/ Array Indexes
Date Fri, 25 Jun 2021 22:19:00 GMT

     [ https://issues.apache.org/jira/browse/ASTERIXDB-2830?page=com.atlassian.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 perform a LOJ,
rather it performs an inner join. 
>  
>  
> {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 AUTOGENERATED;
> 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 */ = M.datetime; 
> {code}
> This query gives a count of 101, when it should be 107 (can be found by removing the
indexnl join hint).
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

Mime
View raw message