[Bug] visitEndServerDate greater than a number segment breaks archiving

[tsteur]

What happened?

Customer created a segment visitEndServerDate>3. The segment visitEndServerDate is the dimension "Time in UTC — date (time of last action)" in the segment selector.

The error we get is

piwik.ERROR: Uncaught exception in API: PDOException: SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: '9' in /libs/Zend/Db/Statement/Pdo.php:233 Stack trace: #0 /libs/Zend/Db/Statement/Pdo.php(233): PDOStatement->execute() #1 /libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array) #2
/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #3 /libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('INSERT IGNORE I...', Array) #4 /core/Db/Adapter/Pdo/Mysql.php(332): Zend_Db_Adapter_Pdo_Abstract->query('INSERT IGNORE I...', Array) #5 /core/DataAccess/LogAggregator.php(325): Piwik\Db\Adapter\Pdo\Mysql->query('INSERT IGNORE I...', Array) #6 /core/DataAccess/LogAggregator.php(413): Piwik\DataAccess\LogAggregator->createTemporaryTable('logtmpsegment3b...', 'SELECT /* trigg...', Array) #7 /core/DataAccess/LogAggregator.php(355): Piwik\DataAccess\LogAggregator->createSegmentTable() #8 /core/DataAccess/LogAggregator.php(573): Piwik\DataAccess\LogAggregator->generateQuery('count(distinct ...', Array, 'log_visit.visit...', '', '') #9 /core/ArchiveProcessor/PluginsArchiver.php(306): Piwik\DataAccess\LogAggregator->queryVisitsByDimension() #10 /va

What should happen?

No error when this report is being archived or looked at in the UI.

How can this be reproduced?

Possibly the segment needs to be created through the API as I believe through the UI it cannot be configured like this.

The error may show up when selecting the segment in the UI. In any case the error happens when triggering the archiving.

Matomo major version

Matomo 4

Matomo minor or patch Version

15

PHP version

.

Server operating system

.

What browsers are you seeing the problem on?

No response

Computer operating system

.

Relevant log output

No response

Validations

• Read our Contributing Guidelines.
• Follow our Security Policy.
• Check that there isn't already an issue that reports the same bug to avoid creating duplicates.
• The provided steps to reproduce is a minimal reproducible of the Bug.

[tsteur]

Getting the same error with visitEndServerDate!=0;visitEndServerDate!=1;visitEndServerDate!=2;visitEndServerDate!=3;visitEndServerDate!=4;visitEndServerDate!=5;visitEndServerDate!=6;visitEndServerDate!=7;visitEndServerDate!=8;visitEndServerDate!=9

piwik.ERROR: Uncaught exception in API: PDOException: SQLSTATE[HY000]: General error: 1525 Incorrect DATE value: '0' in libs/Zend/Db/Statement/Pdo.php:233 Stack trace: #0 /var/www/html/libs/Zend/Db/Statement/Pdo.php(233):

[tsteur]

Maybe visitServerHour>9 was meant and checking for invalid values on segment creation would help to avoid these errors.

[sgiehl]

Yes. Guess it would make sense to check the provided values for validity at least before using them in a query