Bug #78779

A Query on columns of type DATE results in an empty resultset

Added by Stefan Froemken almost 3 years ago. Updated 5 months ago.

Status:
New
Priority:
Must have
Assignee:
-
Category:
Extbase
Start date:
2016-11-23
Due date:
% Done:

0%

TYPO3 Version:
8
PHP Version:
7.0
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

Hello Core-Team,

I'm working with extbase and created a matching like that:

$constraint = array();
$constraint[] = $query->greaterThanOrEqual('day', $monthBegin->format('Y-m-d'));
$constraint[] = $query->lessThan('day', $monthEnd->format('Y-m-d'));        
return $query->matching($query->logicalAnd($constraint))->execute();

But after initializing the QueryResult-Object the resultset is always empty.

The problem seems to be in the new Typo3DbQueryParser:

case QueryInterface::OPERATOR_GREATER_THAN_OR_EQUAL_TO:
  $value = $this->queryBuilder->createNamedParameter($this->dataMapper->getPlainValue($value), \PDO::PARAM_INT);
  $expr = $exprBuilder->comparison($fieldName, $exprBuilder::GTE, $value);

As I work with greaterThanOrEqual the core expects that my date value is of type INT.

The resulting query looks like that:

WHERE ((`tx_events2_domain_model_day`.`day` >= 2016-11-01)
AND (`tx_events2_domain_model_day`.`day` < 2016-12-01))

When I add the quotes my query works like expected.

Stefan


Related issues

Related to TYPO3 Core - Bug #63482: Query constraint on a DateTime field with a DateTime argument should trigger date format New 2014-12-01

History

#1 Updated by Frans Saris almost 3 years ago

  • Status changed from New to Needs Feedback

When you work with dates make sure you provide a date object or timestamp. Or if it day is a int value in the format yyyymmdd format your value also like a that ->format('ymd') so loose the -.

#2 Updated by Stefan Froemken almost 3 years ago

Hello Frans,

my fault. I have forgotten to say, that I use dbType => 'datetime' in TCA:
https://docs.typo3.org/typo3cms/TCAReference/Reference/Columns/Input/Index.html#dbtype

#3 Updated by Frans Saris almost 3 years ago

But did you try it without ->format('Y-m-d') as that is imo no valid value to use.

#4 Updated by Stefan Froemken almost 3 years ago

Yes. I have added the related ticket: https://forge.typo3.org/issues/63482

#5 Updated by Frans Saris over 2 years ago

Stefan, what mysql version are you using?

I just encountered something similar. On server 1 a query like ... WHERE model.reminder_send < 1451815237 works but on server 2 it doesn't. reminder_send is a datetime field here.

Difference between the 2 servers is:
- Server 1: MySQL 5.7.16-0ubuntu0.16.04.1
- Server 2: MariaDB 5.5.41-MariaDB

So it looks like MySQL server allows this to work where MariaDB doesn't handle this....

#6 Updated by Stefan Froemken over 2 years ago

On my MAC

Stefans-MacBook-Pro:bin stefan$ ./mysql --version
./mysql Ver 14.14 Distrib 5.6.28, for osx10.9 (x86_64) using EditLine wrapper

On our servers:
(13:37:08) [~] mysql --version
mysql Ver 14.14 Distrib 5.6.21, for Linux (i686) using EditLine wrapper

#7 Updated by Alexander Opitz over 2 years ago

  • Status changed from Needs Feedback to New

#8 Updated by Georg Ringer over 1 year ago

  • Status changed from New to Needs Feedback

IMO this has been solved, can you confirm that?

#9 Updated by Stefan Froemken over 1 year ago

Sorry. It didn't work.
In createTypedNamedParameter you call getPlainValue without second parameter which is important to check against dbType. As this is not given, the DateTime can't be converted to a string representation and falls back to int Timestamp.

#10 Updated by Riccardo De Contardi about 1 year ago

  • Status changed from Needs Feedback to New

#11 Updated by Benni Mack 5 months ago

  • Target version changed from next-patchlevel to Candidate for patchlevel

Also available in: Atom PDF