[
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)
