Bug #78779
closed
A Query on columns of type DATE results in an empty resultset
0%
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
Updated by Frans Saris over 7 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 -.
Updated by Stefan Froemken over 7 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
Updated by Frans Saris over 7 years ago
But did you try it without ->format('Y-m-d') as that is imo no valid value to use.
Updated by Stefan Froemken over 7 years ago
Yes. I have added the related ticket: https://forge.typo3.org/issues/63482
Updated by Frans Saris over 7 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....
Updated by Stefan Froemken over 7 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
Updated by Alexander Opitz almost 7 years ago
- Status changed from Needs Feedback to New
Updated by Georg Ringer about 6 years ago
- Status changed from New to Needs Feedback
IMO this has been solved, can you confirm that?
Updated by Stefan Froemken about 6 years 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.
Updated by Riccardo De Contardi almost 6 years ago
- Status changed from Needs Feedback to New
Updated by Benni Mack about 5 years ago
- Target version changed from next-patchlevel to Candidate for patchlevel
Updated by Benni Mack about 4 years ago
- Status changed from New to Needs Feedback
Dear Stefan,
can you share your TCA & ext_tables.sql for that?
Thanks.
Updated by Stefan Froemken about 4 years ago
- Status changed from Needs Feedback to Closed
Hello Benni,
solved with this Patch: https://forge.typo3.org/issues/79473
So this ticket can be closed now.
Stefan