Recommend Select * OR Select ** In IOTDB-SQL

[MarcosZyk]

We are developing a new feature on master branch, support wildcard ** in IOTDB-SQL. Here's the link. #3918

Since we support * and **, and apply path pattern in SQL statement. Here are two type of DDL SQL to get all data under one prefixPath, and we want to choose one of them as default recommended statement presenting in UserGuide docs.

1. select * from <prefixPath>.**, e.g. select * from root.**
2. select ** from <pathPattern>, e.g. select ** from root

Obviously, the second one is more simple than the first one.
However, since we IoTDB have some hidden bugs in data query and presentation, define entities clearly in sql from clause and IoTDB will run more stable. That's why the second one prevails.

Look forward to your suggestions.

[cornmonster]

Hi, it seems that this change breaks the backward compatibility?

Let's say we have 3 time series: root.sg.d.s1, root.sg.d.s2, and root.sg.d.t.s1.

Before this change, the following query produces a result set that contains all of the time series.

select * from root.sg.d

After this change, it produces only root.sg.d.s1 and root.sg.d.s2, right?

[cornmonster]

Yep, I know how the concatenation and star removal works. My concern is that this change may break forces our users to examine all the code used in a serious production environment, which could be really time-consuming. Also, when the users rewriting their queries, bugs can be introduced if * and ** are not properly used.

[MarcosZyk]

That's right. Users need to make an upgrade plan to accomplish adaption if they want to upgrade their IoTDB from 0.12x to 0.13. They have to check the * usage and prefix path usage in all their sql statements.

[cornmonster]

Understood, but I am afraid this could prevent some of our users in a serious productive environment from using 0.13.x. I believe it is better to provide a document or guidance, helping our users to rewrite their queries.

Currently, It looks like that if a path after concatenation ends with a start, the star means to match everything. If the star is in the middle of a path, it means to match only a single node, is it correct?

[MarcosZyk]

Right, we will provide some upgrade docs.

Currently, the star * works as what you say. More correctly, it match nodes on one level, in other words all children of the parent, when it is in the middle of a path.

[cornmonster]

Cool, thanks for your time.

[jixuan1989]

If there is a good idea to let reduce the side effect of upgrading stale version, then this feature will be much better. But I have no idea.... Apache Cassandra had a similar issue, and Cassandra uses CQL2 and CQL3, and let the session claim which version it will use when the session connects the server. Best, Zhong Wang ***@***.***> 于2021年9月17日周五 下午2:39写道：

…

Cool, thanks for your time. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#3990 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAHZOVQ4QN5RL3RZH2LRJUDUCLPCVANCNFSM5EE7X4BA> .

[cornmonster]

I actually have considered adding an option and let users decide if they want a more correct semantic or backward compatibility. But then I realized it is not a good idea.

The previous semantic of '' is indeed quite confusing, and adding a '**' to represent 'matching all' and change the semantic of '' to 'matching one level' does make sense. Although this change is painful for our users to change their code, it prevents them from making mistakes.

Thus I think we could provide a tool that converts the previous queries into correct ones. I guess it can be achieved with ANTLR and some semantic analysis, but I am not sure.