Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Inconsistent query results with sub-selects #16

Open
yanmendes opened this issue Apr 3, 2020 · 0 comments
Open

Inconsistent query results with sub-selects #16

yanmendes opened this issue Apr 3, 2020 · 0 comments

Comments

@yanmendes
Copy link

yanmendes commented Apr 3, 2020

I'm issuing the following query to BigDAWG and it's not returning the appropriate response.

SELECT
      program_id as program_id,
      label as label,
      ipw as is_provone_Workflow,
      phssubp as provone_hasSubProgram
    FROM
      (
        (
          SELECT
            a.id as program_id
          FROM
            actor as a
        )
      ) as t1
      INNER JOIN (
        (
          SELECT
            e.id as joinId,
            COALESCE(w.name, e.name) as label,CASE
              WHEN w.id IS NOT NULL THEN TRUE
              ELSE FALSE
            END as ipw,CASE
              WHEN w.id IS NOT NULL THEN NULL
              ELSE e.wf_id
            END as phssubp
          FROM
            entity as e
            LEFT JOIN workflow as w ON w.id = e.id
        )
      ) as t2 ON program_id = joinId

Given the left join issue, it was expected from this query to return the following result:

psql -d postgresql://postgres@localhost:5401/kepler -c \
"SELECT program_id as program_id, label as label, ipw as is_provone_Workflow, phssubp as provone_hasSubProgram FROM (( SELECT a.id as program_id FROM actor as a) ) as t1 INNER JOIN (( SELECT e.id as joinId, COALESCE(w.name, e.name) as label,CASE WHEN w.id IS NOT NULL THEN TRUE ELSE FALSE END as ipw,CASE WHEN w.id IS NOT NULL THEN NULL ELSE e.wf_id END as phssubp FROM entity as e INNER JOIN workflow as w ON w.id = e.id) ) as t2 ON program_id = joinId"

 program_id |     label     | is_provone_workflow | provone_hassubprogram 
------------+---------------+---------------------+-----------------------
          1 | 04-HelloWorld | t                   |                      

However, BigDAWG returns an empty set:

curl -X POST -d \
"bdrel(SELECT program_id as program_id,label as label,ipw as is_provone_Workflow,phssubp as provone_hasSubProgram FROM (( SELECT a.id as program_id FROM actor as a )) as t1 INNER JOIN (( SELECT e.id as joinId,COALESCE(w.name, e.name) as label,CASE WHEN w.id IS NOT NULL THEN TRUE ELSE FALSE END as ipw,CASE WHEN w.id IS NOT NULL THEN NULL ELSE e.wf_id END as phssubp FROM entity as e LEFT JOIN workflow as w ON w.id = e.id )) as t2 ON program_id = joinId)" http://localhost:8080/bigdawg/query

EDIT:

If you need a reproducible environment, just follow the installation steps here

The following query just spit out a plain error:

curl -X POST http://localhost:8080/bigdawg/query -d "bdrel(sele
ct * from ( ( SELECT af.id as execution_id,actor_id as prov_hadPlan FROM actor_fire as af ) UNION ALL ( SELECT NULL as execution_id,wfe.wf_id as prov_hadPlan FROM workflow_exec as wfe ) ) as table_3)"

net.sf.jsqlparser.statement.select.SetOperationList cannot be cast to net.sf.jsqlparser.statement.select.PlainSelect


EDIT 2:

Also, BigDAWG doesn't allow filtering and projecting properties over subselects, e.g.:

Also, BigDAWG doesn't output results with the defined aliases, instead, it outputs the original dimension name.

curl -X POST http://localhost:8080/bigdawg/query -d "bdrel(select execution_id from ( SELECT pe.fire_id as execution_id,pe.port_id as provone_hadInPort,data as data FROM port_event as pe WHERE pe.write_event_id = -1 ) as table_6 where e
xecution_id='1')"
fire_id
1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant