On Sat, Oct 3, 2015 at 4:51 PM, andrew <[email protected]> wrote: > I’m not sure I understand your point regarding how an alias would render > it meaningless. Could you explain that? >
The alias I was mentioning is v2 below. v1: select t.id, t.b.* from t; (makes sense) v2: select t.id, t.b.* as newcol from t; (doesn't make any sense as what does it mean to alias newcol) On Sat, Oct 3, 2015 at 5:56 PM, Ted Dunning <[email protected]> wrote: > > Another option would be to allow a udf to return many top level fields. > With regards to multiple output from a UDF: if you want to drive this forward on Drill, I think everyone would be fully supportive of it. It would be great to start with a design doc. You'll also need to talk through what the syntax would be. This is really orthogonal to the use case I'm trying to enable. The functionality I want to achieve could just as easily work against a raw record as a UDF output. > > The use of star is intriguing, but there is an inherent problem, logically > speaking since in normal queries * can be replaced for a list of fields. In > this usage, it hard to understand how that might be. It would be unpleasant > if I could extract all fields or one field but not two. > > Would you consider a syntax like this? > > select t.b.(x,y) from t > > Ted, your proposal is already achievable easily with t.b.x, t.b.y. Not sure what the upside of this additional syntax would be. I'm looking specifically for a wildcard where we there is no guaranteed way to enumerate. (the set of fields may be unknown by the end user). The * syntax already works with multiple table prefixes (also supporting additional field references from those same tables): select t.*, x.*, t.c1, t.c2 from t,x I'm not sure how this is any harder to understand than the t.* and x.* syntax above since it seems to be the exact same behavior. > > Sent from my iPhone > > > On Oct 3, 2015, at 16:51, andrew <[email protected]> wrote: > > > > Hey Jacques, > > > > The first option feels more natural to me, but I’m not sure I understand > your point regarding how an alias would render it meaningless. Could you > explain that? > > > > Thanks, > > A > > > >> On Oct 3, 2015, at 8:54 AM, Jacques Nadeau <[email protected]> wrote: > >> > >> Hey All, > >> > >> One of the use cases that we're trying to solve in Drill is unrolling an > >> inner map. This is used frequently since various types of outputs from > UDFs > >> are maps. Imagine a situation where records are effectively: > >> > >> { > >> id: 123, > >> b: { > >> x:10, > >> y:20 > >> } > >> } > >> > >> We want to the output of the operation to be > >> > >> { > >> id: 123, > >> x: 10, > >> y:20 > >> } > >> > >> This is frequently useful if you want to present data to a bi tool. The > >> question is, what is the right way to express this concept in SQL? The > two > >> ideas I have are: > >> > >> select t.id, t.b.* from t; > >> > >> OR > >> > >> select t.id, t.b as * from t > >> > >> The first seems to fit well with the existing pattern around * from SQL. > >> However, we would need to validate that there was no use of an alias > since > >> that would be meaningless in this context. Thinking about that led me to > >> the second idea. > >> > >> Any other thoughts/proposals? What do other people think? > > >
