This message was deleted.

Also, in general, whats the best way to do some post processing of columns after a join? We do a join of table_1 and table_2, and then want to combine col_A from table_1 and col_B from table_2 into a new column ( = col_A - col_B). Currently using scan with virtual column to achieve this.

Hi Apoorv, regarding your last question -- I just ran a test SQL join, e.g.:

Select a.key, a.col_A - a.col_B diff
  from tableA a 
  join tableB b on a.key = b.key

and did an explain plan to generate the native query JSON, and it looks like it turned it into a scan with virtual column, which is what you are using. I don't know of any other performant way of doing this. Is this a 1-1 join between the two tables or are them multiple matches for each key?

This is a 1-1 join, single match for each key. However, the virtual columns approach is not working because of https://github.com/apache/druid/issues/13851

Did you see the last comment, that he got the query to work using some escaped quotation marks? What's your expression?

Oh, if that's the case and this is just a syntax typo issue, then here's what came out of my explain plan for the SQL join query: "virtualColumns": [ { "type": "expression", "name": "v0", "expression": "(\"added\" - \"j0.added\")", "outputType": "LONG" } ], Note the escaped quotes around the column names used in the expression, including the table.column dot notation. If you know SQL at all I would suggest you start by running this as a SQL query, make sure it works, then check the explain plan to see what it is coming up with for Native.

Thanks for the suggestions john and ben. The write sql and explain sounds like a good approach going forward. Saket and I are working together, so yeah I know about the solution now. FYI, is there a reference for the syntax expected in the expression field?

It looks to me like this is regular SQL syntax quoting rules, e.g. chars like underscore _ don't require quoting, but hypens, spaces, etc, do, as do reserved keywords like "from" or "sum" Quoting token names to preserve "illegal" characters and reserved keywords is double-quoted ... single-quote is used for literal strings in SQL. Looking at the above example, I would guess that the quotes are those individual fields are not needed, e.g. this should work:

"virtualColumns": [
    {
      "type": "expression",
      "name": "v0",
      "expression": "(added - j0.added)",
      "outputType": "LONG"
    }
  ],

It's worth trying out a few just to see what works and what doesn't. You should find the rules are pretty consistent here.