Request for new database: Doris

[zh0122]

Use Mysql Client to connet Doris,

Error:
errCode = 2, detailMessage = Unknown column 'UTC_TIMESTAMP' in 'table list'

---

VSCode Version: 1.96.2
Extension Version: 1.5.11
OS: win32
Arch: x64

[mikeburgh]

Thanks! Looks like I will need to add this as a new DB, got to get an instance up and running first to figure out what's happening.

[zh0122]

In terms of interfaces, Apache Doris adopts MySQL protocol, supports standard SQL, and is highly compatible with MySQL dialect. Users can access Doris through various client tools and it supports seamless connection with BI tools.

It may just be a compatibility issue with a few parameters. I believe you can fix it soon. Thank you.

Refer https://github.com/apache/doris

[mikeburgh]

Yeah the error you are getting is the initial connect details I fetch so I can figure out versions, timezone offsets..

select version(),@@GLOBAL.version_comment, DATABASE(), TIMEDIFF(NOW(), UTC_TIMESTAMP);

It will probably take me longer to get Doris running than to fix it 🙂 Wish they provided a complete docker solution for it for dev purposes

[zh0122]

Pls refer to https://doris.apache.org/docs/gettingStarted/quick-start#use-docker-for-quick-deployment to start a Doris demo

[zh0122]

Yeah the error you are getting is the initial connect details I fetch so I can figure out versions, timezone offsets..

select version(),@@GLOBAL.version_comment, DATABASE(), TIMEDIFF(NOW(), UTC_TIMESTAMP);
It will probably take me longer to get Doris running than to fix it 🙂 Wish they provided a complete docker solution for it for dev purposes

I test this sql on Doris, Add a pair of parentheses on UTC_TIMESTAMP.
It works fine.

select version(),@@GLOBAL.version_comment, DATABASE(), TIMEDIFF(NOW(), UTC_TIMESTAMP());

[mikeburgh]

Yeah I spent some time with that docker initially getting it work, but no luck, the example has the wrong tags to start with (easy fix) but after that, I still could not get the backend to run, it registers and then crashes.. I will spend some more time on it to try and sort it out.

Interesting, I assumed it was that query as it's the utc_timestamp.. once I get Doris working should be quick enough to hunt down.

[mikeburgh]

Okay I got Doris running using their manual config approach.

Found a few other issues in the introspection and sorted them along with the timestamp (information_schema.parameters is missing the field character_maximum_length)

However there is a bigger issue..

They have changed something in the protocol, or at least with what all the node drivers expect from the protocol for multiple statements:

select 1; select 2;

That works fine in the mysql cli, but in all the node drivers it generates a protocol error and only the last select's values are present.

Here is an example:

import mysql from "mysql2/promise";

async function main() {
	// Create the connection to database
	const connection = await mysql.createConnection({
		host: "localhost",
		port: 9030,
		user: "root",
		password: "",
		database: "mysql",
	});

	// A simple SELECT query
	try {
		const [results, fields] = await connection.query(`select 1; select 2;`);
		console.log(results); // results contains rows returned by server
		console.log(fields); // fields contains extra meta data about results, if available
	} catch (err) {
		console.log(err);
	}
}

main();

I logged the issue here:

apache/doris#47060

I will see if gets any traction, otherwise I can work around it, but it wont be straight forward, and is a little unreliable (I have to basically try and split strings with mutliple statements)

[mikeburgh]

Version 1.6 was just released, you can connect to Doris now, it will still error on the introspection step, so you cannot browse tables/views etc.

[zh0122]

Thank you, Mike.
Now I can connect to Doris. Although there are still some error messages, I will spend more time on testing.
BTW, can DBcode perform cross - database joins?

[zh0122]

[mikeburgh]

Yes, it's going to have some errors on the introspection, and even if I work around those, it has the problem of multiple statements. Thinking on how best to solve that as that issue I raised has had no updates.

Cross database joins, with a normal query ? if the engine supports it, then it should work, eg:

select t1.*, t2.* 
from table1 t1
join other_database.table2 t2 on (t1.id = t2.id) 

or are you referring to something else ?

[zhanghao1822]

Yes, it's going to have some errors on the introspection, and even if I work around those, it has the problem of multiple statements. Thinking on how best to solve that as that issue I raised has had no updates.

Cross database joins, with a normal query ? if the engine supports it, then it should work, eg:

select t1.*, t2.* 
from table1 t1
join other_database.table2 t2 on (t1.id = t2.id) 

or are you referring to something else ?

Sorry, what I meant was, when using @dbcode to call AI to write SQL, can it perform cross - database joins? I noticed that every time I make a call, I need to manually select a connection and a database. I tested it with MySQL when there were only a few tables in the test environment, and found that the SQL generated by the AI only used tables from the same database. However, due to network issues in the production environment, I can't test the Doris SQL in the production environment at the moment. My scenario is like this: there are dozens of databases and thousands of tables. In such a scenario, can the AI be called to write correct SQL involving cross - database and multiple tables?

[mikeburgh]

Ahh got it, so at the moment, the schema only for the current database is provided to the AI when asking questions/inline completions, so it wont know about the other databases to join with. I will create a new issue to track that, there are some other issues around cross databases I need to work on, so can pick it up as part of that.

As we cannot get the introspection for Doris at the moment, AI wont be much help on the Doris connection since there is no schema we are providing to it.

[zh0122]

Thank you. I'm looking forward to using dbcode to perform cross - database joins on Doris.