maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

Hi Monty,

Please have a look into the latest comments in MDEV-9541 on how
PostgreSQL, SQL Server, Oracle 11i and MySQL-5.7 work when
comparing temporal data types.

There is no a general rule. Every vendor implements it in a different way.


1. PostgreSQL almost follows the standard, which disallows direct
comparison of TIME and TIMESTAMP/DATETIME.

The only exception, it allows to compare a TIME field to a string
literal which looks like TIMESTAMP:

SELECT * FROM t1 WHERE a='2001-01-01 10:20:30';
    a     |          b
----------+---------------------
 10:20:30 | 2001-01-01 10:20:30


But strangely not the other way around!
Comparing of a TIMESTAMP field to a string which looks like TIME does 
not work. I did not find an explanation for this.


2. SQL Server

- Comparison of TIME + VARCHAR is done as TIME
- Comparison of DATETIME + VARCHAR is done as DATETIME
- Comparison of TIME and DATETIME is not allowed
- Comparison of TIME and INT is not allowed
- Comparison of DATETIME and INT is allowed (not sure how exactly it
  works though)
- Temporal literals {t'10:20:30'} have data type VARCHAR, this is just
  a syntactic variation of a string literal '10:20:30', with no any
  additional difference.

3. Oracle 11i

Oracle does not have the TIME data type.
So mixture of TIME and TIMESTAMP is not possible.

But comparison of TIMESTAMP and VARCHAR is always done as TIMESTAMP,
no matter what kind expressions are involved (fields, functions, literals).

4. MySQL-5.7
It has a special code: for a TIME field compared to a non-DATE constant,
the constant argument is converted to TIME. But as soon as you
wrap the field say into COALESCE, or put a non-constant instead of a
constant, this works in a different way.


So, comparing of a TIME field to a string literal that looks like a 
TIMESTAMP/DATETIME works similar in PostgreSQL, SQL Server, MySQL-5.7.
However, this looks only as a coincidence for this particular corner
case.

Perhaps we should not fix that in 10.0. It seems that MariaDB since 5.3
handles comparison of TIME in a more consistent way (less exceptions).


Btw, why not to add a warning every time when an implicit
non-standard type conversion happens and suggest an explicit CAST?
This will encourage users to avoid doubtful constructions.

Also, I noticed that the SQL Server documentation says that comparison
of different data types is a subject to change in the future versions,
and using this is not recommended. Perhaps we should also say something
like this.


I propose instead of adding a quick fix for this exception,
to introduce a conception of "type strength" or "type derivation"
in 10.2.


The idea is simple:
1. Comparison is done according to the type of the strongest side.
2. Strengths(derivations) are as follows:
- A field or an SP variable has the strongest type derivation, say 4.
- Functions have derivation between field and typified constants, say 3.
- Some functions that return input arguments (e.g. COALESCE, LEFT) can
  repeat derivations of their arguments.
- A typified constant like TIME'10:20:30' has derivation 2.
- A typeless constant like '10:20:30' has a very weak derivation, say 1.
- An explicit NULL has the weakest derivation, say 0.
3. Only in case of equal strengths the result depends on the data
   types. Perhaps data types can have some sort of "minor strength",
   for conflict resolution. Say DATETIME/TIMESTAMP can have more minor
   strength over TIME or DATE. So a DATETIME field is compared to a TIME
   field as DATETIME rather than TIME.


This will give some advantages:

- Universal comparison rules for all data types

- This idea will simplify adding new data types a lot.
  They will just follow the universal rule.
  Otherwise, new data types (say INET6), we'll have to have
  different execution paths with some exceptions and
  exceptions from exceptions :)

- The problem mentioned in MDEV-9541 will be gone: it will compare as
  TIME just because it's a field compared to a typeless constant.
- We'll remove a special code covering comparison of YEAR and LONGLONG
  fields. They will just work as before, but now because a field always
  wins a constant, according to the universal rules.
- We'll probably remove some examples of a special code covering
  explicit NULLs  (i.e. Item::NULL_ITEM).
- A field compared to a constant will always be able to use indexes.
  Less chances to put the server into a heavy load because of a
  non-smart query executed.
- The same results for a field compared to some expression,
  and for the same field wrapped into a hybrid function like COALESCE.
- Hybrid functions with field arguments will also be able to use
  indexes in some cases.
- Equal field propagation will be possible in more cases.
- It should be easier to add a new SQL mode for the strict SQL standard
  compliant comparison style. I think it's good to have this option.

Greetings.