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]> 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]>> 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]> > > > 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 >
_______________________________________________ FastBit-users mailing list [email protected] https://hpcrdm.lbl.gov/cgi-bin/mailman/listinfo/fastbit-users
