Hi, Petr, Please update to Revision 657 if you can. It has the new function EXISTS to test whether its argument is present as a column in the given data partition. This will allow you to do
(NOT EXISTS(e0id27p0) or (e0id8 <= 167772161) Let us know if you spot any problem with the new function. Thanks. John On 10/12/13 11:31 PM, Petr Velan wrote: > Hi John, > > I have an older version of the library. Is it possible that this > behavior changed since r612? > > I tried following: > FILTER: ((e0id27p0 > 0) and (e0id27p0 < 18446744073709551567) and > (e0id27p1 > 0) and (e0id27p1 < > 18446744073709551615)) > > > returns data > > FILTER: (( not (e0id8 not NULL )) or (e0id8 <= 167772161) or (e0id8 >= > 167772161)) and ((e0id27p0 > 0) and (e0id27p0 < 18446744073709551567) > and (e0id27p1 > 0) and (e0id27p1 < 18446744073709551615)) > does not return data. > > Regarding the SQL standard, I believe that what I want is not easily > possible. What I would do in SQL is query the schema to determine the > presence of the column and if it is not there, i would add "NULL as > col4" to the SELECT part of the query. Is this possible in FastBit? I > have not tried it yet. I'm just looking for a shortcut. > > Thanks, > Petr > > > On Sun, Oct 13, 2013 at 5:37 AM, K. John Wu <[email protected] > <mailto:[email protected]>> wrote: > > Hi, Petr, > > I just tried it, the expression "col4 != 0 || not (col4 not NULL)" > seems to work even if col4 is not in any of the data partitions. > However, this logical is probably not compliant with any SQL standard. > > I have not found a "standard" way of dealing with this case in SQL. > Do you happen to have more information on this? > > John > > > On 10/12/13 4:16 AM, Petr Velan wrote: > > Hi John, > > > > thanks for the advice. What about the case when the column is not > > present in any of the tables and therefore it is not in the logical > > table? Is there any way to tell the query that the column is not > > really required, but if it is there, then it should have a > certain value? > > > > This would help our usecase a lot. > > > > Thanks, > > Petr > > > > > > On Thu, Oct 10, 2013 at 5:41 PM, K. John Wu <[email protected] > <mailto:[email protected]> > > <mailto:[email protected] <mailto:[email protected]>>> wrote: > > > > Hi, Petr, > > > > If a column does not appear in a partition (but in other > partition of > > your logical table), then it is treated as containing on > NULL values. > > The condition "col4 != 0" still implies that "col4" is NOT > NULL. > > > > You might want to simply leave out "col4" if your query > logical is > > fine with this options. > > > > If your where clause need to be "col4 != 0 || col4 is NULL", > then you > > will need to do the following > > > > "col4 != 0 || not (col4 not NULL)" > > > > This double negative is necessary because FastBit does not > currently > > support the expression of the form "col4 is NULL". > > > > Hope this works for you. > > > > John > > > > > > > > > > On 10/9/13 10:17 PM, Petr Velan wrote: > > > Hi John, > > > > > > I've run into following issue: We have multiple tables, > each with > > > different set of columns. We want to run the same query on > all of > > > them. The problem is when the query contains a column that > is not > > > present in a table. Even if the query is something like > col1 != > > 0, the > > > column col1 is required and no results are returned. > > > > > > An example: > > > table1: col1, col2, col3 > > > table2: col2, col3, col4 > > > > > > query: col2 = 42 and col4 != 0 > > > > > > I would want the query to give me all rows from table1 > with col2 > > = 42, > > > thus ignoring the missing col4. Is that possible to achieve in > > FastBit > > > query? Since we have quite lot of different table schemes, > it is not > > > easy to adjust the given query for each of the tables. > > > > > > Thank you for your help, > > > Petr > > > > > > > > > _______________________________________________ > > > FastBit-users mailing list > > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > > > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > > > > > _______________________________________________ > > FastBit-users mailing list > > [email protected] > <mailto:[email protected]> > <mailto:[email protected] > <mailto:[email protected]>> > > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > > > > > > > > > > _______________________________________________ > > FastBit-users mailing list > > [email protected] <mailto:[email protected]> > > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > > > _______________________________________________ > FastBit-users mailing list > [email protected] <mailto:[email protected]> > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > > > > > _______________________________________________ > FastBit-users mailing list > [email protected] > https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users > _______________________________________________ FastBit-users mailing list [email protected] https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
