Bug #78779: A Query on columns of type DATE results in an empty resultset - TYPO3 Core - TYPO3 Forge

Actions

Copy link

Bug #78779

closed

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

Added by Stefan Froemken almost 8 years ago. Updated over 4 years ago.

Status:

Closed

Priority:

Must have

Assignee:

Category:

Extbase

Target version:

Candidate for patchlevel

Start date:

2016-11-23

Due date:

% Done:

Estimated time:

TYPO3 Version:

PHP Version:

7.0

Tags:

Complexity:

Is Regression:

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 1 (1 open — 0 closed)

Actions

Copy link

Updated by Frans Saris almost 8 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 -.

Actions

Copy link

Updated by Stefan Froemken almost 8 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

Actions

Copy link

Updated by Frans Saris almost 8 years ago

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

Actions

Copy link

Updated by Stefan Froemken almost 8 years ago

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

Actions

Copy link

Updated by Frans Saris almost 8 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....

Actions

Copy link

Updated by Stefan Froemken almost 8 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

Actions

Copy link

Updated by Alexander Opitz over 7 years ago

Status changed from Needs Feedback to New

Actions

Copy link

Updated by Georg Ringer over 6 years ago

Status changed from New to Needs Feedback

IMO this has been solved, can you confirm that?

Actions

Copy link

Updated by Stefan Froemken over 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.

Actions

Copy link

#10