[feature]: support comparison binary operations about lindb HOT 6 CLOSED

Hi there!

I'd like to work on this issue. 🚀Let's make this happen! 💪

Please forgive me for not fully understanding the meaning of this issue. What I can think of is that you hope that lindb can support the following SQL statement query:

select x, y, z from a where x > 10.1 and y < 5.2; // x, y and z are all fields

Can you tell me whether my understanding is correct? If not, please describe this issue in more detail. Thank you.

from lindb.

✌️ , Welcome to contribute LinDB together.

Yes, you are right.

Some cases like this:

select x, y, z from a>10.1; // fields

select x+y as a, 2*z, z from a>10 and 2*z>10; // expr

select sum(x) as a from a>10; // function

from lindb.

✌️ , Welcome to contribute LinDB together.

Yes, you are right.

Some cases like this:

select x, y, z from a>10.1; // fields

select x+y as a, 2*z, z from a>10 and 2*z>10; // expr

select sum(x) as a from a>10; // function

1. In your example, there is no specification of the metric, and there is no 'WHERE' keyword. Is this just a shorthand form, or is it a formal syntax?

2. When you need to simultaneously filter based on both tags and fields, what should the SQL statement look like?

3. In this example, would it be better to use the HAVING clause?

select sum(x) as a from a>10; // function
=>
select sum(x) as a from metric_x having a > 10;

from lindb.

If I make any mistakes, please feel free to interrupt me.

Filtering for fields can only be done at the root node of the broker because it requires aggregating results from all intermediate nodes or storage nodes in the broker. Only when all families from all shards are aggregated by interval can the data that doesn't meet the WHERE conditions be filtered out. Implementing this feature in root_metric_context.go might be the most suitable approach.

For queries involving GROUP BY time in standard SQL, field filtering should occur before grouping. In other words, fields that do not meet the conditions should not be included in the group calculation. However, for Lindb, filtering of fields should occur after grouping. In this case, filtering fields behaves similarly to the HAVING clause. Therefore, if Lindb supports HAVING queries, the following two SQL statements would be equivalent.

select * from a where field_a = 1 group by time(1m); // Assuming this is valid.
select * from a group by time(1m) having field_a = 1;

The field condition may not necessarily appear in the selectExpr, which also determines some modifications in certain operators.

from lindb.

I made a mistake.
There are 2 scenarios here.

1. Before grouping aggregate, filtering raw value of field.
   select * from a where field_a =1
2. After grouping aggregate, filtering the result of grouping. Like you said group by having statement is better.
   select * from a group by time(1m) having field_a = 1; // using default aggregate function

Let's implement scenario 2 first, and at the same time, like standard SQL, the field condition must appera in the selectExpr?

from lindb.

If I make any mistakes, please feel free to interrupt me.

Filtering for fields can only be done at the root node of the broker because it requires aggregating results from all intermediate nodes or storage nodes in the broker. Only when all families from all shards are aggregated by interval can the data that doesn't meet the WHERE conditions be filtered out. Implementing this feature in root_metric_context.go might be the most suitable approach.

For queries involving GROUP BY time in standard SQL, field filtering should occur before grouping. In other words, fields that do not meet the conditions should not be included in the group calculation. However, for Lindb, filtering of fields should occur after grouping. In this case, filtering fields behaves similarly to the HAVING clause. Therefore, if Lindb supports HAVING queries, the following two SQL statements would be equivalent.

select * from a where field_a = 1 group by time(1m); // Assuming this is valid.
select * from a group by time(1m) having field_a = 1;

The field condition may not necessarily appear in the selectExpr, which also determines some modifications in certain operators.

Yes, grouping field filtering only be done in the root node before building result set.

from lindb.