Allow unioned selections to be combined when possible

[markdanese]

When two different code sets are combined via a union, this causes two scans to be done. When the scans are done on the same table, this is inefficient. We need to allow the code sets to be unioned prior to doing the selection, but only the case where all codes from both code sets are in the same table (e.g., all diagnosis codes, or all procedure codes).

There may already be an issue for this but I couldn't find it.

We could assign Jeremy to this, depending on timing and his availability. Right now assigning to Chisel.

Note that this will work really well for GDM since all codes are in one table. It may be easier to implement for GDM only (if that is even possible).

[aguynamedryan]

This should be a lot more straightforward now that #241 was implemented.

[aguynamedryan]

chisel.jsaw.io is now running my initial implementation. I think we could do more, but it will require additional work to other parts of ConceptQL. I'm going to spin that off into a new ticket.

[jenniferduryea]

I'm not sure what this ticket has changed. I created an algo looking for an ICD9 and CPT code. The resulting SQL still puts separate Select statements inside the algorithm, which seems like something we did prior. @aguynamedryan would you please tell me what was changed so I can check if it's working? Thanks.

[markdanese]

being handled in the "columns" update. revisit once that is done.

[jenniferduryea]

So this is pretty cool. I tested this by unioning icd9cm, icd10cm, cpt4 codes and verified that the SQL on the SOURCE screen (in the JAM) show up under one SELECT statement. However, this does not seem to work when using the CPT_OR_HCPCS operator. In this instance, the codes listed under CPT_OR_HCPCS are in one SELECT statement while the other operators (i.e. ICD9cm, ICD10cm, NDC, etc.) are under another SELECT statement. I'm not sure why the CPT_OR_HCPCS operator is treated differently (except that it's looking under two vocabularies for one code). Tagging @aguynamedryan for explanation.

I confirmed the SQL shown in the JAM and what is used in Exports are the same. Looks good. Pending if we need to do something for CPT_OR_HCPCS operator, since it is a heavily used operator.

[aguynamedryan]

Thanks for pointing this out, @jenniferduryea. I've updated ConceptQL so MultipleVocabulary operators play nice with Vocabulary operators and avoid UNIONs.

[jenniferduryea]

Looks awesome now. I made sure the updated code works for cpt_or_hcpcs operator by looking at the SOURCE view (in the JAM) and running an export and confirming the SQL used. Great job!

[jenniferduryea]

this is specific to new columns. so this ticket will be assigned to sandbox.