Yes, I agree with Suresh. The reason is likely that you defined the output column name as a lower-case string "id", and that is treated as a case-sensitive, delimited identifier. If you define the column as "ID" in the UDF, that is treated as a regular identifier, which is not case-sensitive. The calls in the UDF interface that deal with column names, such as TupleInfo.addCharColumn, use an "internal format" with no quotes. ALL CAPS mean a regular identifier, otherwise it's interpreted as a delimited identifier. This is mentioned in the addCharColumn Javadoc <https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/index.html?org/trafodion/sql/udr/TupleInfo.html>, but the explanation is missing from the ColumnInfo constructor <https://57e4a67d4ac0d80d65fbe17f54e5cf57ab4e0478.googledrive.com/host/0BztdUbYgvYe0N0hSRG9HME85MlE/1.3.0/apidocs/index.html?org/trafodion/sql/udr/ColumnInfo.html> (same is true for C++ interface). I'll update the Javadoc/Doxygen.
Hans On Wed, Jun 1, 2016 at 8:24 AM, Suresh Subbiah <[email protected]> wrote: > Hi Ming, > > That is an interesting UDF. > > The syntax you have shown should work. Please see the modified example > below from regression test udr/TEST001. > I think either we have a bug or it is an issue with delmited names. You > could try these two steps to understand the issue better. > > a) select * from udf(solrUDF('db','iphone')) u where u."id > <http://u.id/>" = 1; OR > b) cqd UDR_DEBUG_FLAGS '64'; select * from udf(solrUDF('db','iphone')) u > ; > Approach b) will print out the output column names which could be helpful. > Debug flags are documented by Hans at <http://goog_1873561815> > https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface > > Thanks > Suresh > > Example from udr/test001 showing that syntax given above should work. The > bug I mentioned could be due to the fact that solrUDF does not have a table > input. We have had issues with 0 table valued input UDFs in the past. > > SELECT * > FROM UDF(sessionize_dynamic(TABLE(SELECT userid, > JULIANTIMESTAMP(ts) as TS, > ipAddr > FROM clicks > PARTITION BY ipaddr ORDER BY ts), > 'IPADDR', > cast('TS' as char(2)), > 60000000)) u > where u.session_id < 10; > > SESSION_ID SEQUENCE_NO USERID > TS IPADDR > -------------------- -------------------- > -------------------------------- -------------------- --------------- > > 1 1 super-user > 212331578399500000 12.345.567.345 > 2 1 super-user > 212331585599500000 12.345.567.345 > 2 2 super-services > 212331585599500000 12.345.567.345 > 2 3 super-services > 212331585599550000 12.345.567.345 > > --- 4 row(s) selected. > >> > > Notice how column names are in upper case in the output. > > > > > On Wed, Jun 1, 2016 at 4:18 AM, Liu, Ming (Ming) <[email protected]> > wrote: > >> Hi, all, >> >> >> >> I wrote a simple TMUDF which will perform a solr query and get result as >> a table value. >> >> >> >> It can work like this: >> >> >> >> >>select * from udf(solrUDF('db','iphone')); >> >> id description >> >> ------ ---------------------------------------- >> >> 1 iphone 5 >> >> 2 iphone 5s >> >> --- 2 row(s) selected. >> >> >> >> >> >> As you can see, it will return two columns: ‘id’ and ‘description’. Now I >> want to do a filter on id, so I try this: >> >> >> >> >>select * from udf(solrUDF('db','iphone')) u where u.id = 1; >> >> >> >> It failed and report this error: >> >> >> >> *** ERROR[4003] Column U.ID is not a column in table U, or, after a >> NATURAL JOIN or JOIN USING, is no longer allowed to be specified with a >> table correlation name. >> >> >> >> *** ERROR[8822] The statement was not prepared. >> >> >> >> Because I want to join the udf result from the source Trafodion table, so >> I have to reference the columns in the UDF. >> >> >> >> Please help, how can I reference to the column returned from a UDF? >> >> >> >> Thanks, >> >> Ming >> >> >> > >
