[MDEV-14053] Unquoted NULL default values in information schema with views Created: 2017-10-12  Updated: 2018-10-08  Resolved: 2018-10-08

Status: Closed
Project: MariaDB Server
Component/s: Admin statements
Affects Version/s: 10.2.7, 10.2.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sébastien Vanvelthem Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 16.04x64

Attachments: PNG File Selection_086.png     PNG File Selection_087.png    
Issue Links:
Relates
relates to MDEV-13132 Information Schema does not show whet... Closed
relates to MDEV-13341 information_schema.columns Column_def... Closed

 Description   

I'm currently working on doctrine support for mariadb 10.2 and information_schema changes introduced in 10.2.7 (MDEV-13132).

I faced an unexpected case in information schema concerning default values. I understand the new behavior of 'NULL' being quoted. And it works well, except that after an upgrade from 10.2.6 to 10.2.7, not all the fields have been converted to the new spec.

As an example:

SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_DEFAULT IS NULL AND IS_NULLABLE = "YES"

Should not return any row, but returns 4 rows on my database (amongst 4206 fields matching default is null). The column is nullable and the column_default is NULL (should be 'NULL'). See attached screenshot 086.

For info, the normal behaviour was working:

SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
 
FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_DEFAULT IS NULL AND IS_NULLABLE <> "YES"

For this one, 4202 records worked. See screenshot 087.

It's only happening after upgrade to 10.2.7. So I guess the upgrade of information_schema failed somewhere. If I recreate the database the information_schema is correct.



 Comments   
Comment by Sébastien Vanvelthem [ 2017-10-12 ]

@serg FYI, doctrine (and related owncloud, nextcloud, most symfony apps...) does not yet support mariadb 10.2 due to information schema changes. But it's on the way. This issue does not block us (I've made a test for this situation)

Comment by Elena Stepanova [ 2017-10-13 ]

belgattitude, can you paste SHOW CREATE TABLE for the tables which contain those columns?
Technically, a table can have a NULLable column without a default value, in which case COLUMN_DEFAULT IS NULL would be true. It usually does not happen, because ways to achieve it are somewhat cumbersome, but nothing specifically prohibits it, e.g.

MariaDB [test]> select @@version;
 
+----------------+
 
| @@version      |
 
+----------------+
 
| 10.2.7-MariaDB |
 
+----------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> create table t1 (c varchar(50));
 
Query OK, 0 rows affected (0.25 sec)
 
 
 
MariaDB [test]> alter table t1 alter column c drop default;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0


MariaDB [test]> show create table t1 \G
 
*************************** 1. row ***************************
 
       Table: t1
 
Create Table: CREATE TABLE `t1` (
 
  `c` varchar(50)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_DEFAULT IS NULL AND IS_NULLABLE = "YES";
 
+-------------+-------------+-------------+----------------+
 
| COLUMN_NAME | COLUMN_TYPE | IS_NULLABLE | COLUMN_DEFAULT |
 
+-------------+-------------+-------------+----------------+
 
| c           | varchar(50) | YES         | NULL           |
 
+-------------+-------------+-------------+----------------+
 
1 row in set (0.03 sec)

Comment by Sébastien Vanvelthem [ 2017-10-13 ]

Yep, nothing special:

CREATE TABLE IF NOT EXISTS `ship_view_alltrack` (
 
  // columns removed for clarity
 
  `pack_status_title` varchar(50)
 
);

Comment by Sébastien Vanvelthem [ 2017-10-13 ]

Dear Elena,

Sorry my bad. It's a VIEW not a TABLE. Problem happens only with views.

Let me know if I can help on this and thanks for your time.

Comment by Elena Stepanova [ 2017-10-13 ]

belgattitude, sorry, I don't understand. What is a view and not table?
`ship_view_alltrack` as you quoted above is a base table, not a view. And indeed, pack_status_title does not have a DEFAULT value, so it would expectedly appear in your first search, both for the base table and any views based on it.
The query result in first screenshot does not have table names, but there are two pairs of columns with the same names, I assume one pair belongs to the base table, and the other pair belongs to a view.

Comment by Sébastien Vanvelthem [ 2017-10-15 ]

Elena Stepanova,

> The query result in first screenshot does not have table names, but there are two pairs of columns with the same names, I assume one pair belongs to the base table, and the other pair belongs to a view.

I checked and both are views (one from staging db, the other from dev db). At first I recreated the schemas, my migration tool created invalid migrations (changing view in tables), that's how it explain why I felt the problem disappeared and suspect an incorrect schema upgrade on server part.

So I tested from your example:

CREATE TABLE t2 (c varchar(50));
 
CREATE VIEW v2 AS SELECT * FROM t2;

Both table and view have a default of 'NULL'.

After removing the default:

ALTER TABLE t2 ALTER COLUMN c DROP DEFAULT;

Both table and view have a default of NULL.

All good then.

But in my previous example, the view I had was :

CREATE ALGORITHM = MERGE VIEW `ship_view_alltrack` AS 
SELECT o.id as order_id, c.id as customer_id, c.name as customer_name, 
o.reference as order_reference, o.customer_reference as order_customer_reference, 
o.recorded_at as order_recorded_at, t.id as type_id,  t18.title as type_title,
 
l.id as line_id, l.line_number, l.quantity, l.volume, l.weight, l.cod, l.customer_comment,
 
cfrom.city as from_city, cfrom.zipcode as from_zipcode, cfrom.fingerprint as from_fingerprint,
 
cto.city as to_city, cto.zipcode as to_zipcode, cto.fingerprint as to_fingerprint,
 
p.id as pack_id, p.customer_id as pack_customer_id, p.reference as pack_reference, 
p.customer_reference as pack_customer_reference, ps18.title as pack_status_title
 
FROM base_customer c inner join ship_order o on c.id = o.customer_id
 
INNER JOIN ship_order_type t on o.type_id = t.id
 
LEFT OUTER JOIN ship_order_type_translation t18 on t.id = t18.id and t18.lang = 'fr'
 
INNER JOIN  ship_order_line l on l.order_id = o.id
 
INNER JOIN crm_contact cfrom on cfrom.id = l.address_from_id
 
INNER JOIN crm_contact cto on cto.id = l.address_to_id
 
LEFT OUTER JOIN ship_pack p on p.order_line_id = l.id
 
LEFT OUTER JOIN ship_pack_status ps on p.status_id = ps.id
 
LEFT OUTER JOIN ship_pack_status_translation ps18 on ps.id = ps18.id and ps18.lang = 'fr'
 
ORDER BY o.id, l.id

A schema inspection gives

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_DEFAULT IS NULL AND IS_NULLABLE = "YES";
 
+--------------------+--------------------+-------------------+----------------+-------------+-----------+
 
| TABLE_SCHEMA       | TABLE_NAME         | COLUMN_NAME       | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |
 
+--------------------+--------------------+-------------------+----------------+-------------+-----------+
 
| xyz_dev        | ship_view_alltrack | type_title        | NULL           | YES         | varchar   |
 
| xyz_dev        | ship_view_alltrack | pack_status_title | NULL           | YES         | varchar   |
 
| xyz_staging | ship_view_alltrack | type_title        | NULL           | YES         | varchar   |
 
| xyz_staging | ship_view_alltrack | pack_status_title | NULL           | YES         | varchar   |
 
| jira_tests         | t1                 | c                 | NULL           | YES         | varchar   |
 
+--------------------+--------------------+-------------------+----------------+-------------+-----------+

The weird thing happens when I look at the corresponding table.column: 

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'akilia2_dev' and TABLE_NAME = 'ship_pack_status_translation' and COLUMN_NAME = 'title';
 
+--------------+------------------------------+-------------+----------------+-------------+-----------+
 
| TABLE_SCHEMA | TABLE_NAME                   | COLUMN_NAME | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |
 
+--------------+------------------------------+-------------+----------------+-------------+-----------+
 
| xyz_dev  | ship_pack_status_translation | title       | NULL           | NO          | varchar   |
 
+--------------+------------------------------+-------------+----------------+-------------+-----------+

The originating column is actually non-nullable default.

The view gives a nullable default.

So the problem is very different from what I originally thought. But probably still a bug.

FYI, I'm currently working on doctrine support for MariaDB 10.2.7, and tried to give some indications in the code (see: https://github.com/belgattitude/dbal/blob/ad52f401c8d543060eda564f85b772c10dba9cad/lib/Doctrine/DBAL/Schema/MySqlSchemaManager.php#L223). I'll update the comment, but thanks to you I know that we the 'NULL' is not enforced and therefore should be tested on our side too.

Comment by Sébastien Vanvelthem [ 2017-10-23 ]

Elena Stepanova,

This ticket can be closed.

Thanks for your explanation, I've narrowed the problem not being linked to information_schema upgrade. See my previous comment, the 'NULL' vs NULL happens only for views (in information schema: The view and the base table defaults values gives different "notations")

Up to you to open a new issue for that specific case.

Comment by Elena Stepanova [ 2018-10-08 ]

Sorry there was no reply before.
Naturally if you left-join the table, the resulting column will be NULL-able, regardless NULL-ability of the column on the base table, because LEFT JOIN can always result in NULL. Consider the most basic example:

MariaDB [test]> create table t1 (a int not null);
 
Query OK, 0 rows affected (0.22 sec)
 
 
 
MariaDB [test]> create table t2 (b int not null);
 
Query OK, 0 rows affected (0.23 sec)
 
 
 
MariaDB [test]> select * from t1 left join t2 on (a=b);
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS where table_schema = 'test';
 
+--------------+------------+-------------+----------------+-------------+-----------+
 
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |
 
+--------------+------------+-------------+----------------+-------------+-----------+
 
| test         | v          | a           | NULL           | NO          | int       |
 
| test         | v          | b           | NULL           | YES         | int       |
 
| test         | t2         | b           | NULL           | NO          | int       |
 
| test         | t1         | a           | NULL           | NO          | int       |
 
+--------------+------------+-------------+----------------+-------------+-----------+
 
4 rows in set (0.00 sec)
 
 
 
MariaDB [test]> insert into t1 values (1);
 
Query OK, 1 row affected (0.06 sec)
 
 
 
MariaDB [test]> select * from v;
 
+---+------+
 
| a | b    |
 
+---+------+
 
| 1 | NULL |
 
+---+------+
 
1 row in set (0.00 sec)

Generated at Thu Feb 08 08:10:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.