[CONPY-264] Incorrect types for defaults returned for `show columns in table` Created: 2023-06-03  Updated: 2023-06-25  Resolved: 2023-06-04

Status: Closed
Project: MariaDB Connector/Python
Component/s: Parser
Affects Version/s: 1.1.6
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Andrej Assignee: Georg Richter
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu linux 22.04

Python Version: 3.10.6

 Description   

This is table definition:

MariaDB [test]> show columns in mytable;
 
+---------------------+---------------+------+-----+---------+-------+
 
| Field               | Type          | Null | Key | Default | Extra |
 
+---------------------+---------------+------+-----+---------+-------+
 
| id                  | char(18)      | NO   | PRI | NULL    |       |
 
| active              | tinyint(1)    | YES  |     | 1       |       |
 
| slots               | int(11)       | YES  |     | 100     |       |
 
| differential        | decimal(5,4)  | YES  |     | 0.0100  |       |
 
+---------------------+---------------+------+-----+---------+-------+

Running from the connector:

>>> dbc.execute(f'show columns in mytable')
 
>>> results = dbc.fetchall()
 
>>> print(results)
 
[{'Field': 'id', 'Type': 'char(18)', 'Null': 'NO', 'Key': 'PRI', 'Default': None, 'Extra': ''}, {'Field': 'active', 'Type': 'tinyint(1)', 'Null': 'YES', 'Key': '', 'Default': '1', 'Extra': ''}, {'Field': 'slots', 'Type': 'int(11)', 'Null': 'YES', 'Key': '', 'Default': '100', 'Extra': ''}, {'Field': 'differential', 'Type': 'decimal(5,4)', 'Null': 'YES', 'Key': '', 'Default': '0.0100', 'Extra': ''}]

Evidently all default values are returned as strings rather than the columns' respective types. The expected output should have been:

[{'Field': 'id', 'Type': 'char(18)', 'Null': 'NO', 'Key': 'PRI', 'Default': None, 'Extra': ''}, {'Field': 'active', 'Type': 'tinyint(1)', 'Null': 'YES', 'Key': '', 'Default': 1, 'Extra': ''}, {'Field': 'slots', 'Type': 'int(11)', 'Null': 'YES', 'Key': '', 'Default': 100, 'Extra': ''}, {'Field': 'differential', 'Type': 'decimal(5,4)', 'Null': 'YES', 'Key': '', 'Default': Decimal('0.0100'), 'Extra': ''}]



 Comments   
Comment by Georg Richter [ 2023-06-04 ]

MariaDB Connector/Python converts the value in default to the type which was sent by server in the metadata packet.

$ mariadb test --column-type-info
 
MariaDB [test]> show columns from t1;
 
Field   1:  `Field`
 
Org_field:  `COLUMN_NAME`
 
Catalog:    `def`
 
Database:   `information_schema`
 
Table:      `COLUMNS`
 
Org_table:  `COLUMNS`
 
Type:       VAR_STRING
 
Collation:  utf8mb3_general_ci (33)
 
Length:     192
 
Max_length: 1
 
Decimals:   0
 
Flags:      NOT_NULL NO_DEFAULT_VALUE 
 
 
Field   2:  `Type`
 
Org_field:  `COLUMN_TYPE`
 
Catalog:    `def`
 
Database:   `information_schema`
 
Table:      `COLUMNS`
 
Org_table:  `COLUMNS`
 
Type:       BLOB
 
Collation:  utf8mb3_general_ci (33)
 
Length:     589815
 
Max_length: 11
 
Decimals:   0
 
Flags:      NOT_NULL BLOB NO_DEFAULT_VALUE 
 
 
Field   3:  `Null`
 
Org_field:  `IS_NULLABLE`
 
Catalog:    `def`
 
Database:   `information_schema`
 
Table:      `COLUMNS`
 
Org_table:  `COLUMNS`
 
Type:       VAR_STRING
 
Collation:  utf8mb3_general_ci (33)
 
Length:     9
 
Max_length: 3
 
Decimals:   0
 
Flags:      NOT_NULL NO_DEFAULT_VALUE 
 
 
Field   4:  `Key`
 
Org_field:  `COLUMN_KEY`
 
Catalog:    `def`
 
Database:   `information_schema`
 
Table:      `COLUMNS`
 
Org_table:  `COLUMNS`
 
Type:       VAR_STRING
 
Collation:  utf8mb3_general_ci (33)
 
Length:     9
 
Max_length: 0
 
Decimals:   0
 
Flags:      NOT_NULL NO_DEFAULT_VALUE 
 
 
Field   5:  `Default`
 
Org_field:  `COLUMN_DEFAULT`
 
Catalog:    `def`
 
Database:   `information_schema`
 
Table:      `COLUMNS`
 
Org_table:  `COLUMNS`
 
Type:       BLOB
 
Collation:  utf8mb3_general_ci (33)
 
Length:     589788
 
Max_length: 6
 
Decimals:   0
 
Flags:      BLOB NO_DEFAULT_VALUE 
 
 
Field   6:  `Extra`
 
Org_field:  `EXTRA`
 
Catalog:    `def`
 
Database:   `information_schema`
 
Table:      `COLUMNS`
 
Org_table:  `COLUMNS`
 
Type:       VAR_STRING
 
Collation:  utf8mb3_general_ci (33)
 
Length:     240
 
Max_length: 0
 
Decimals:   0
 
Flags:      NOT_NULL NO_DEFAULT_VALUE 
 
 
 
 
+-------+-------------+------+-----+---------+-------+
 
| Field | Type        | Null | Key | Default | Extra |
 
+-------+-------------+------+-----+---------+-------+
 
| a     | int(11)     | YES  |     | 5       |       |
 
| b     | varchar(20) | YES  |     | no bug  |       |
 
+-------+-------------+------+-----+---------+-------+

As you can see, the default value is defined as VAR_STRING. So it's up to your application to convert the value to the appropriate type.

Comment by Andrej [ 2023-06-06 ]

Thank you for your quick response, and my apologies for reporting a non-issue. But please help me understand this better. If I query an actual row through the connector, all types get converted to the proper python types; can the same not be done for the defaults? Variable types are clearly stored correctly in the database itself, why can't the connector cast them to proper types? Are there any public methods in the connector code that can be used to consistently (w.r.t. the connector itself) convert default values to the appropriate type? I would have expected that type conversion should be done by the connector and not the application.

Comment by Sergei Golubchik [ 2023-06-25 ]

select * from mytable or show columns in mytable — these statements return results as a table. In a table all values in a column have the same metadata. You cannot have the value in the second row, fourth column to be an integer and the value in the third row, fourth column, to be a string.

In this case you ask Connector/Python to send an SQL query to the server, and return you the tabular data as they come from the server. Connector/Python doesn't even know that the first column corresponds to the column name in some table of yours and the fifth column contains its default value.

Generated at Thu Feb 08 03:31:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.