Pull Request for Issue #24535 (part).

Summary of Changes

This PR fixes all datetime columns of com_finder (Smart Search) tables so there will not be any Invalid value '0000-00-00 00:00:00' for datetime error anymore on MySQL 5.7 or later when strict mode is enabled, and the datetime (MySQL) or timestamp without time zone (PostgreSQL) columns will allow NULL values wherever useful/possible.

Because Smart Search has been completely restructured in the update sql scripts 4.0.0-2018-07-29.sql, the schema changes made by this PR are made in these update sql scripts, and because the tables modified by this PR are cleared in these scripts, there is no need to update any old data.

Note on updating old 4.0 sql update scripts

Since we are not in Beta phase yet and so don't have to support updates from 4.0-Alpha-x to 4.0-Alpha-y or between nightly builds, we can change the existing 4.0 update scripts (but of course not pre-4.0 scripts). Later when in beta this will not be allowed anymore because we have to support updating from Beta-x to Beta-y (with y > x of course), so now is a good time to fix them.

Furthermore it makes sense to keep the schema updates for the datetime columns of each table together in one script, because on MySQL it might be necessary to combine all changes for a particular table into 1 single ALTER TABLE statement in order to run properly in strict mode (i.e. strict tables enabled). Currently the db checker/fixer can't understand such statements, but it might be necessary to teach that tool soon to do that.

That's why this PR updates the existing sql update script and doesn't create a new one.

Testing Instructions

New Installation

1. Apply the patch on a clean 4.0-dev branch using git or merging manually, or apply it on an installation of current 4.0-dev using patchtester.
2. If used patchtester on an existing installation in step 1, delete file configuration.php and delete all Joomla database tables in PhpMyAdmin or PhpPgAdmin (depending on your database type).
3. Do a new installation, login to backend, confirm the statistics dialog, go to global config and set error reporting to maximum or development in server settings.
4. After update, play around with com_finder. Use it in all possible ways (of which you know).
5. After any action, check in your database the relevant datetime/timestamp without timezone columns.

Update from 3.9.12 or staging

1. On a clean staging or 3.9.12, set error reporting to maximum or development in global config server section.
2. Update to 4.0.0-alpha12-dev nightly build plus this PR applied, using the "Upload & Update" tab of the Joomla Update component and the update zip package which you have downloaded from following link before: https://test5.richard-fath.de/Joomla_4.0.0-alpha12-dev-Development-Update_Package_2019-10-07_pr-26463.zip.
3. Watch the PHP error log and the log file of your database server during the update and after.
4. After update, play around with com_finder. Use it in all possible ways (of which you know).
5. After each activity, check in your database the relevant datetime/timestamp without timezone columns.

Expected result

New Installation

Smart search works as well as without this PR. There are no datetime columns having value '0000-00-00 00:00:00' in any of the tables #__finder_filters and #__finder_links on MySQL', and there is no invalid default value anymore in MySQL >= 5.7 with strict mode on, and on PostgreSQL there are no values '1970-01-01 00:00:00'.

There is one exception: When checking in a finder filter using com_checkin, the checked_out_time is set to '0000-00-00 00:00:00' on MySQL and '1970-01-01 00:00:00' on PosgreSQL. This will be changed with a separate, future PR for com_checkin. Checking in an item with the lock icon in list display works, i.e. there checked_out_time is set to NULL.

On PostgreSQL there are issues which seem not to be related to this PR, e.g. "ERROR: invalid input syntax for type bytea at character 101". Such an error has been fixed once with PR #25884 by @alikon for com_finder, but now it seems to be back for some reason. @alikon Could you check?

Update from 3.9.12 or staging

Update works, no errors in SQL or PHP about smart search. Rest see above for new installation.

Actual result

New Installation

Same as expected result, but the default value of these database column is invalid in MySQL >= 5.7 with strict mode on, and there might be values of '0000-00-00 00:00:00' on MySQL and '1970-01-01 00:00:00' on PostgreSQL in the datetime/timestamp columns of tables #__finder_filters and #__finder_links.

Update from 3.9.12 or staging

See new installation.

Documentation Changes Required

Maybe core developer docs and extension developer docs should be updated to encourage them not to use '0000-00-00 00:00:00' on MySQL anymore but use real NULL and not abuse '1970-01-01 00:00:00' on PostgreSQL as a speudo null date anymore and use real NULL values also there.