New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Multi Column Filter With NonLatin Character Cause Mysql Error #392

Closed
sysulincn opened this Issue Nov 12, 2018 · 1 comment

Comments

Projects
None yet
2 participants
@sysulincn

sysulincn commented Nov 12, 2018

Steps to reproduce this issue

  1. create a table with columns in type Date/DateTime/Time/Timestamp like below:
    CREATE TABLE ActDataDefine_DB(ActNameVARCHAR(100) NOT NULL,DataNameVARCHAR(100) NOT NULL,TableIdTINYINT(3) UNSIGNED NOT NULL,DataIdSMALLINT(5) UNSIGNED NOT NULL,WeeklyClearTINYINT(1) UNSIGNED NOT NULL DEFAULT '0',LastReadDateDATE NULL DEFAULT NULL,LastWriteDateDATE NULL DEFAULT NULL,CreateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (TableId, DataId), UNIQUE INDEX ActName (ActName, DataName) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ;

  2. open mysql table in heidisql, open the tab 'Data' of the table, fill in some non-latin character, and Click 'Apply Filter'

Current behavior

It create 'ColName like '%filter%' for every column, and mysql server will throw an error with the 'like' operation with the Date/DateTime/Time/Timestamp column like the pic below :
SQL Error(1271): Illegal mix of collations for operation 'like',
then I have to delete the "ColName like '%filter%'" for every Date/DateTime/Time/Timestamp columns
image

Expected behavior

for Date/DateTime/Time/Timestamp Columns, change the filter string
"ColName LIKE '%filter%'"
TO
"ColName LIKE BINARY '%filter%'"
when generate the filter string with the apply filter button.

Possible solution

Environment

  • HeidiSQL version:
    HeidiSQL 9.5.0.5332
  • Database system and version:
    mysql server 5.5 or later
  • Operating system:
    windows

@ansgarbecker ansgarbecker added this to the v10 milestone Nov 28, 2018

@ansgarbecker

This comment has been minimized.

Collaborator

ansgarbecker commented Nov 28, 2018

Should be fixed now in the latest build. But I am generating a CAST(mycol AS CHAR) instead of your proposed mycol LIKE BINARY .... Please test if that works for you.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment