[ASTERIXDB-2830] Fix INLJ Bug for LOJ Queries w/ Array Indexes - ASF JIRA

XML

Word

Printable

JSON

Details

Type: Bug
Status: Resolved
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: None
Component/s: IDX - Indexes
Labels:
None

Description

Currently the INLJ query below w/ the following data does not correctly perform a LOJ, rather it performs an inner join.

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;

This query gives a count of 101, when it should be 107 (can be found by removing the indexnl join hint).

Attachments

Issue Links

Blocked

ASTERIXDB-2834 Recognize Applicable Nested Universal Quantification Queries for Array Indexes

Closed

Activity

People

Assignee:: Glenn Justo Galvizo

Reporter:: Glenn Justo Galvizo

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 11/Feb/21 03:26

Updated:: 20/Jul/21 19:57

Resolved:: 30/Jun/21 21:52