28992

Illegal mix of collations for operation for date/time comparison

I get this error

Illegal mix of collations for operation <=

for the following WHERE clause:

time(table1.dt1)<=date_add(table1.tim,interval 60 second)

dt1 = datetime, tim = time

Anybody knows why?

Answer1:

<ol> <li>

As explained in the manual entry for DATE_ADD():

The return value depends on the arguments:

    <li>

    DATETIME if the first argument is a DATETIME (or TIMESTAMP) value, or if the first argument is a DATE and the unit value uses HOURS, MINUTES, or SECONDS.

    </li> <li>

    String otherwise.

    </li> </ul>

    In your case, because table1.tim is a TIME value, the function is returning a string.

    The string is returned in the character set and collation given by character_set_connection and collation_connection.

    </li> <li>

    As explained in the manual entry for TIME():

    Extracts the time part of the time or datetime expression <strong>expr</strong> and returns it as a string.

    The string is returned as a binary string (why not using the connection character set & collation is beyond me—perhaps a bug?).

    </li> <li>

    As explained in the manual entry for Type Conversion in Expression Evaluation:

    If both arguments in a comparison operation are strings, they are compared as strings.

    Therefore the comparison undertaken is a string comparison, but the binary string cannot be coerced to a suitable collation for comparison (as its encoding is not known). Hence the error you are witnessing.

    </li> </ol>

    One could force the comparison to be conducted correctly by explicitly casting one or both of the operands, but I'd be tempted just to rework the clause so that conversion takes place implicitly from comparison against a TIME type:

    TIME(table1.dt1 - INTERVAL 1 MINUTE) <= table1.tim

Recommend

  • Additional scripts in PATH
  • Defining a method whose return type is the singleton type of an argument of that method
  • Error in GGally: Error in unit(tic_pos.c, “mm”) : 'x' and 'units' must have leng
  • Yii2: Using Kartik Depdrop Widget?
  • Does the MySQL IN clause execute the subquery multiple times?
  • How do you keep a running instance for Google App Engine
  • RavenDB indexing errors
  • Real Time CountDown Timer In Python
  • How dotnet build chooses the output name
  • Using Nested Table variables / Collections in SQL inside PL/SQL blocks
  • PHP multiple file uploads
  • Android cannot disable cut copy paste
  • hide missing dates from x-axis ggplot2
  • Add dynamic data to line chart from mysql database with highcharts
  • Calculate time difference in hh:mm:ss with simple javascript/jquery
  • Git describe fails to return most recent annotated tag
  • TFS 2015 - Waiting for an agent to be requested
  • Spring boot 2.0.0.M4 required a bean named 'entityManagerFactory' that could not be found
  • Extract zip entries to another Zip file
  • Is it possible to open regedit and navigate to straight to a specific key using process.start?
  • OOP Javascript - Is “get property” method necessary?
  • Eloquent update method change created_at timestamp
  • Marklogic : Query response time is very high
  • d3 v4 drag and drop with TypeScript
  • print() is showing quotation marks in results
  • ActiveRecord query for a count of new users by day
  • Row Count Is Returning the incorrect number using RaptureXML
  • Why does access(2) check for real and not effective UID?
  • Asynchronous UI Testing in Xcode With Swift
  • Q promise. Difference between .when and .then
  • MySQL WHERE-condition in procedure ignored
  • Release, debug version and Authorization Google?
  • R: gsub and capture
  • retrieve vertices with no linked edge in arangodb
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • A cron job substitute?
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • How can I use threading to 'tick' a timer to be accessed by other threads?