bug: need to remove single quotes for comment

[cdmikechen]

Search before asking

• I had searched in the issues and found no similar issues.

Version

v1.2.642-nightly

What's Wrong?

Table and column comments using jdbc are wrapped in single quotes, which are not actually comments and need to be remove.
We found that the jdbc for mysql or databend is fetched from information_schema.columns and information_schema.tables.

databend/src/query/storages/system/src/columns_table.rs

Lines 207 to 226 in 3036fa8

	_ => {
	let schema = table.schema();
	let field_comments = table.field_comments();
	let n_fields = schema.fields().len();
	for (idx, field) in schema.fields().iter().enumerate() {
	// compatibility: creating table in the old planner will not have `fields_comments`
	let comment = if field_comments.len() == n_fields
	&& !field_comments[idx].is_empty()
	{
	// can not use debug print, will add double quote
	format!("'{}'", &field_comments[idx].as_str().replace('\'', "\\'"))
	} else {
	"".to_string()
	};
	rows.push((
	database.clone(),
	table.name().into(),
	comment,
	field.clone(),
	))

How to Reproduce?

create table test_comment (a int comment 'test comment') COMMENT ='table comment'

trying to fix databend-jdbc issue databendlabs/databend-jdbc#285

We try to do it in a regular way can we change it in the relevant code? REGEXP_REPLACE(comment, '^\\'(.+)\\'$', '$1')

databend/src/query/storages/information_schema/src/columns_table.rs

Lines 29 to 62 in 3036fa8

	let query = "SELECT
	database AS table_catalog,
	database AS table_schema,
	table AS table_name,
	name AS column_name,
	1 AS ordinal_position,
	NULL AS column_default,
	comment AS column_comment,
	NULL AS column_key,
	case when is_nullable='NO' then 0
	when is_nullable='YES' then 1
	end as nullable,
	is_nullable AS is_nullable,
	type AS data_type,
	data_type AS column_type,
	NULL AS character_maximum_length,
	NULL AS character_octet_length,
	NULL AS numeric_precision,
	NULL AS numeric_precision_radix,
	NULL AS numeric_scale,
	NULL AS datetime_precision,
	NULL AS character_set_catalog,
	NULL AS character_set_schema,
	NULL AS character_set_name,
	NULL AS collation_catalog,
	NULL AS collation_schema,
	NULL AS collation_name,
	NULL AS domain_catalog,
	NULL AS domain_schema,
	NULL AS domain_name,
	NULL AS privileges,
	default_expression as default,
	NULL AS extra
	FROM system.columns;";

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[sundy-li]

system.columns need to fix this bug

🐳 :) select comment  from system.tables where name  = 'test_comment';
┌───────────────┐
│    comment    │
│     String    │
├───────────────┤
│ table comment │
└───────────────┘

🐳 :) 🐳 :) select comment from system.columns where table = 'test_comment';
┌────────────────┐
│     comment    │
│     String     │
├────────────────┤
│ 'test comment' │
└────────────────┘