[MDEV-24139] CHECK_CLAUSE field in INFORMATION_SCHEMA.CHECK_CONSTRAINTS truncate check constraints expressions Created: 2020-11-05  Updated: 2020-12-07  Resolved: 2020-12-07

Status: Closed
Project: MariaDB Server
Component/s: Information Schema
Affects Version/s: None
Fix Version/s: 10.2.37, 10.3.28, 10.4.18, 10.5.9

Type: Bug Priority: Minor
Reporter: Anastasiya Volkova Assignee: Anel Husakovic
Resolution: Fixed Votes: 0
Labels: None
Environment:

MariaDB 10.3.1, DBeaver 7.2.4

Attachments: PNG File 2020-11-05 13_05_06-DBeaver Enterprise 7.3.0 - _none_ Script-4.png    
Issue Links:
Relates
relates to MDEV-14474 INFORMATION_SCHEMA.CHECK_CONSTRAINTS Closed

 Description   

Hello.
To get information from the database about the check constraints available in the tables, in DBeaver} we use this expression:
SELECT cc.CONSTRAINT_NAME, cc.CHECK_CLAUSE FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc

And it seems the number of characters in the CHECK_CLAUSE field is limited (64?).
Therefore, long expressions (like start_date IS NULL OR end_date IS NULL OR start_date<end_date)) are truncated.

This creates some inconvenience when working with data for our users.

We use SHOW CREATE TABLE to get tables DDL, but pulling the constraint expression out of there is difficult.

Are there any other alternative solutions for this problem?

 Comments   
Comment by Anastasiya Volkova [ 2020-11-05 ]

oh, i see the problem is known

Comment by Anel Husakovic [ 2020-11-05 ]

Hi,
based on MDEV-14474 comment and looking into PR's #1127 and #1150, patch for this problem is created c8b8b01b6 but patch pushed to server a134f1ebb129 is saying that we don't use patch for extension of the length.
I don't see why we didn't pushed (probably waiting on new MDEV? ) cvicentiu ?
Assigning to cvicentiu to determine further flow since if ^ is true, now we have a reason to apply the patch c8b8b01b6.

Comment by Daniel Black [ 2020-11-05 ] 

include/mysql_com.h:#define NAME_CHAR_LEN       64U    /* Field/table name length */
 
include/mysql_com.h:#define NAME_LEN                (NAME_CHAR_LEN*SYSTEM_CHARSET_MBMAXLEN)

Table definitions are defined in terms of character length, so using NAME_CHAR_LEN makes sense, if it was a field/table name, which is isn't.

So work out how big CHECK can be, does it actually have a character set? Maybe MYSQL_TYPE_BLOB is more approprate in the information_schema?

Comment by Anel Husakovic [ 2020-12-01 ]

Example of MYSQL_TYPE_BLOB filed type has length PROCESS_LIST_INFO_WIDTH=65535, which is only used for IS.PROCESSLIST.
But in this case it is the expression that has a variable length and more likely to string instead of blob.
So length could be here a scalar with the type of MYSQL_TYPE_STRING.
Comparing to the check_clause of sql server where CHECK_CLAUSE nvarchar(4000) and also comparing to other fields like PLUGIN_DESCRIPTION which has value of 65535 I would suggest to go with scalar length like 65535 of type MYSQL_TYPE_STRING as suggested in the patch

Comment by Daniel Black [ 2020-12-02 ]

The maximum is probably MAX_FIELD_VARCHARLENGTH (65535-2-1) which seems to match GENERATION_EXPRESSION and COLUMN_DEFAULT in the information schema.

Comment by Anel Husakovic [ 2020-12-07 ]

Pushed to 10.2 with f924a3bd6cd70c140f

Generated at Thu Feb 08 09:27:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.