Sébastien Vanvelthem added a comment - 2017-10-12 09:44 @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)

Elena Stepanova added a comment - 2017-10-13 10:59 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)

Sébastien Vanvelthem added a comment - 2017-10-13 11:04 - edited Yep, nothing special: CREATE TABLE IF NOT EXISTS `ship_view_alltrack` ( // columns removed for clarity `pack_status_title` varchar(50) );

Sébastien Vanvelthem added a comment - 2017-10-13 11:14 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.

Elena Stepanova added a comment - 2017-10-13 11:36 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.

Sébastien Vanvelthem added a comment - 2017-10-15 10:39 - edited 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.

Sébastien Vanvelthem added a comment - 2017-10-23 12:15 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.

Elena Stepanova added a comment - 2018-10-08 17:26 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)