[ https://issues.apache.org/jira/browse/ASTERIXDB-3037?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17543416#comment-17543416 ]
Michael J. Carey edited comment on ASTERIXDB-3037 at 5/28/22 5:28 AM: ---------------------------------------------------------------------- Aha!! ( Makes sense) not found. was (Author: dtabass): Aha!!! Makes sense! > Array indexing not being used when it seems applicable > ------------------------------------------------------ > > Key: ASTERIXDB-3037 > URL: https://issues.apache.org/jira/browse/ASTERIXDB-3037 > Project: Apache AsterixDB > Issue Type: Bug > Components: COMP - Compiler, IDX - Indexes, SQL - Translator SQL++ > Affects Versions: 0.9.8 > Reporter: Michael J. Carey > Assignee: Glenn Justo Galvizo > Priority: Major > > Why aren't array indexes useful for the queries below that have existential > predicates on the bosses array? Seems like they should apply! > > {noformat} > DROP DATAVERSE HierarchyDemo IF EXISTS; > CREATE DATAVERSE HierarchyDemo; > USE HierarchyDemo; > CREATE TYPE genericType AS { _id: uuid }; > CREATE COLLECTION employees(genericType) > PRIMARY KEY _id AUTOGENERATED; > CREATE INDEX bossIdx ON employees (UNNEST bosses: INT) EXCLUDE UNKNOWN KEY; > INSERT INTO employees ( > [ > { > "eno":100, "name": "Matt", "sal": 950000, > "level": 1, > "boss": null, > "bosses": [] > }, > { > "eno":200, "name": "Gopi", "sal": 750000, > "level": 2, > "boss": 100, > "bosses": [100] > }, > { > "eno":75, "name": "Mike", "sal": 50000, > "level": 3, > "boss": 200, > "bosses": [100,200] > }, > { > "eno":50, "name": "Keshav", "sal": 500000, > "level": 3, > "boss": 200, > "bosses": [100,200] > }, > { > "eno":80, "name": "Till", "sal": 400000, > "level": 4, > "boss": 50, > "bosses": [100,200,50] > }, > { > "eno":130, "name": "Kamini", "sal": 450000, > "level": 4, > "boss": 50, > "bosses": [100,200,50] > }, > { > "eno":1000, "name": "Dmitry", "sal": 300000, > "level": 5, > "boss": 80, > "bosses": [100,200,50,80] > }, > { > "eno":300, "name": "Murali", "sal": 275000, > "level": 5, > "boss": 80, > "bosses": [100,200,50,80] > }, > { > "eno":70, "name": "Sitaram", "sal": 300000, > "level": 5, > "boss": 130, > "bosses": [100,200,50,130] > } > ]); > -- Show all employees > SELECT VALUE e FROM employees e; > -- Show Keshav's boss > SELECT VALUE b > FROM employees e, employees b > WHERE e.boss = b.eno > AND e.name = "Keshav"; > -- Show all of Keshav's bosses (ordered by level) > LET kbossas = (SELECT VALUE k FROM employees k WHERE k.name = > "Keshav")[0].bosses > SELECT VALUE e FROM employees e > WHERE e.eno IN kbossas > ORDER BY e.level DESC; > -- Show Keshav's direct reports > SELECT VALUE r > FROM employees e, employees r > WHERE r.boss = e.eno > AND e.name = "Keshav"; > -- Show all of Keshav's reports > LET keshav = (SELECT VALUE t.eno FROM employees t WHERE t.name = "Keshav")[0] > SELECT VALUE e > FROM employees e > WHERE (SOME b IN e.bosses SATISFIES b = keshav); > {noformat} -- This message was sent by Atlassian Jira (v8.20.7#820007)