Feature: Make Databend's create view syntax closer to mysql

[hanyisong]

Summary
It can be parsed normally in mysql, but the following error is reported in databend

mysql> create view v1(a) AS select number from numbers(10);
ERROR 1105 (HY000): Code: 1005, displayText = sql parser error: need `AS` after VIEW NAME.

[dzdx]

/help

[BohuTANG]

/help

Can I help you?

[johnhaxx7]

Hi @BohuTANG, @ariesdevil, I see there's no update from dzdx, is it ok for me to take over the ticket?

I have following questions:

1. I was building databend from source and trying to run sqllogictests to verify some change. I ran cargo run -p sqllogictests and project root, got

    Finished dev [unoptimized + debuginfo] target(s) in 0.51s
     Running `target/debug/sqllogictests`
MySQL client starts to run...
thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: MysqlClient(Io(Io(Os { code: 61, kind: ConnectionRefused, message: "Connection refused" })))', tests/sqllogictests/src/main.rs:235:69
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

I also tried make sqllogic-test and got

bash ./scripts/build/build-debug.sh
Build(DEBUG) start...
    Finished dev [unoptimized + debuginfo] target(s) in 0.77s
All done...
rm -rf ./_meta*/
ulimit -n 10000;ulimit -s 16384; bash ./scripts/ci/ci-run-sqllogic-tests.sh
Starting standalone DatabendQuery and DatabendMeta
No matching processes belonging to you were found
No matching processes belonging to you were found
Start databend-meta...
Waiting on databend-meta 10 seconds...
appending output to nohup.out
... connecting to :9191
OK :9191 is listening
Start databend-query...
Waiting on databend-query 10 seconds...
appending output to nohup.out
... connecting to :3307
... connecting to :3307
... connecting to :3307
... connecting to :3307
... connecting to :3307
Traceback (most recent call last):
  File "/Users/JohnHe/Documents/GitHub/databend/scripts/ci/wait_tcp.py", line 38, in <module>
    tcp_ping(args.port, args.timeout)
  File "/Users/JohnHe/Documents/GitHub/databend/scripts/ci/wait_tcp.py", line 26, in tcp_ping
    raise Exception("fail to connect to :{}".format(port))
Exception: fail to connect to :3307
make: *** [sqllogic-test] Error 1

Aslo I was trying to deploy a local build with scripts/ci/deploy/databend-query-standalone.sh, got a connection issue as well. Seems like it's similar issue as above. Can you please advice what's the proper way to run the test and deploy a local build?
2. For this task, except sqllogictests, can you share some pointers on where else I need to add test?

[BohuTANG]

Hi @johnhaxx7

I see there's no update from dzdx, is it ok for me to take over the ticket?

Sure, have re-signed for you. (Also, thanks to @dzdx ).

For the other 2 problems, it is likely the databend-query can't be started, you can try and check the following:

make build
./target/debug/databend-query -c scripts/ci/deploy/config/databend-query-embedded-meta.toml

[johnhaxx7]

it works, thank you!

[sundy-li]

@johnhaxx7

You can store the schema names inside the view, so query will be rewritten like this:

MySQL [(none)]> select t.a + t.b  from (select number, number + 1 from numbers(10)) t(a,b);
+-------------+
| (t.a + t.b) |
+-------------+
|           1 |
|           3 |
|           5 |
|           7 |
|           9 |
|          11 |
|          13 |
|          15 |
|          17 |
|          19 |
+-------------+
10 rows in set (0.024 sec)

[johnhaxx7]

Hi @sundy-li , thank you for the pointer! I'm relative new to the code base, so I'm kind of exploring while implementing. Till now I identified that i need to update create, describe, alter, select for view to show the correct schema. I found that create save the subquery an option, which will be put into CreateTableReq

https://github.com/datafuselabs/databend/blob/8be55847084afb38f9c92d28ee10eb2f52bed90c/src/query/service/src/interpreters/interpreter_view_create.rs#L69-L73

When we describe the view, it parses the subquery and get the schema from there.
https://github.com/datafuselabs/databend/blob/8be55847084afb38f9c92d28ee10eb2f52bed90c/src/query/service/src/interpreters/interpreter_table_describe.rs#L61-L65

What I'm doing is, in create_view function, I parse the subquery, get its schema and re-assemble a new schema with new column names, and then put it to CreateTableReq.TableMeta.schema. When I describe, I read from there. I'm still exploring how select works and what I can do.

You can store the schema names inside the view

I literally pass a list of column names from ast, plan to interpreter. Can you please advice does my implementation align with your idea?

[sundy-li]

When we describe the view, it parses the subquery and get the schema from there

I think you can contact the SQL as:

select * from ({QUERY}) {hash_name_of_view}( names )

Thus you will get the correct schema.

And when you query the view, you can still use:

({QUERY}) {hash_name_of_view}( names )