Re: Bacula 15.0.2 update

On 5/10/24 07:04, Martin Simmons wrote:
>>>>>> On Thu, 9 May 2024 14:45:31 -0400, Phil Stracchino said:
>>
>> I'm seeing a LOT of columns either defined with a DEFAULT but allowed to
>> be NULL (in which case the DEFAULT will not be used because NULL is
>> allowed),
> 
> Are you sure the DEFAULT will not be used?  I think it will be used if the
> column is omitted in an INSERT.  If you want to insert NULL and there is a non
> NULL DEFAULT then you need to provide NULL for that column explicity in the
> INSERT.


OK, so, I can't speak to the internals of PostgreSQL and don't wish to. 
But I did just recheck in MariaDB (10.6.17).  I've never tried 
specifying a DEFAULT value *WITHOUT* NOT NULL, because why would you, 
but I just re-checked, and somewhat to my surprise, that is in fact 
valid.  (I expected weird behavior.)


This is the documented/tested behavior for missing values in MariaDB 
10.6 at least, which matches the *documented* behavior of MySQL 8.x:


Column declared with DEFAULT but neither NULL nor NOT NULL:
— Implicit default for the data type is used
— NULLs can be manually inserted

Column declared NULL with no DEFAULT
— NULLs can be manually inserted
— If STRICT SQL is in effect, missing value inserts NULL
— If STRICT is not in effect, the implicit default for data type is used

Column declared with neither NULL/NOT NULL nor DEFAULT
— As for previous case, as far as I can tell

Column declared NOT NULL without DEFAULT (SQL99 violation)
— Missing value creates an error
— NULL creates an error


It should go without saying that STRICT_ALL_TABLES *should* ideally 
always be in use.  But a lot of times, you *can't* because some 
application or another uses non-compliant SQL and breaks with STRICT in 
use.  And it also should go without saying that best practice is to 
explicitly declare whether NULLs are allowed in the column instead of 
relying on what the database internals do when it is not specified.

(MySQL 8 and I think MariaDB 10 clamped down a lot on what SQL99 
violations were ...  let's say tolerated.)



-- 
   Phil Stracchino
   Fenian House Publishing
   [email protected]
   [email protected]
   Landline: +1.603.293.8485
   Mobile:   +1.603.998.6958



_______________________________________________
Bacula-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-devel