Query with >= doesn't work as I expect

Hello everybody, I’ve a question.
A client application that I can’t change, sends queries like the following:

SELECT * FROM TABLENAME WHERE TABLENAME.FIELD1>='DUMMY ’ AND
TABLENAME.FIELD1<='DUMMY ’

filling the string dummy with spaces as the length of the field.
The interested field is a CHAR(9) type
In this case, the DB Manager returns zero records found, but if I try to execute the query manually trimming the spaces like the query below,

SELECT * FROM TABLENAME WHERE TABLENAME.FIELD1>=‘DUMMY’ AND
TABLENAME.FIELD1<=‘DUMMY’

then the DB Manager returns records working very well…
Is there a way to solve this trouble???

Unfortunately I can’t change the source query because I don’t have the source code of the application :wink:


Buongiorno a tutti, avrei un quesito.
Un applicativo che non posso modificare effettua alcune query su un db MySql con condizioni del tipo:

SELECT * FROM TABELLA WHERE TABELLA.CAMPO1>='STRINGA ’ AND
TABELLA.CAMPO1<='STRINGA ’
completando la query con tanti spazi quanti ne servono per raggiungere la dimensione del campo di tipo CHAR

Il database manager non restituisce alcun risultato, ma se provo ad eseguire esternamente la query, togliendo gli spazi

SELECT * FROM TABELLA WHERE TABELLA.CAMPO1>=‘STRINGA’ AND
TABELLA.CAMPO1<=‘STRINGA’

il Db manager mi torna il risultato atteso.
Posso agire su qualche parametro di configurazione per risolvere il mio problema’

if you cannot change it, you will have to live with it

Please don’t reply with the same nonsense answer to all my posts… :eye:

it’s not a nonsense answer

and if you post the same question in multiple forums, you must be prepared to get the same answer in all of them

have a nice day, and remember, don’t shoot the messenger if you don’t like the answer

What I mean is that the previous Db Manager (IMB DB2) worked perfectly so probably it’s possible to manage the MySql configuration to solve the issue.

What do you think Mr. r937?? :wink:

here, test for yourself…

CREATE TABLE test_trailing_spaces
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, fixed CHAR(9)
, variable VARCHAR(9)
);
INSERT INTO test_trailing_spaces ( fixed, variable ) VALUES
 ( 'dummy'     , 'dummy'     )
,( 'dummy    ' , 'dummy    ' )
;

now run each of these 4 queries separately and let me know what you get –

SELECT * 
  FROM test_trailing_spaces
 WHERE fixed >= 'DUMMY' 
   AND fixed <= 'DUMMY' 
;
SELECT * 
  FROM test_trailing_spaces
 WHERE fixed >= 'DUMMY    ' 
   AND fixed <= 'DUMMY    ' 
;
SELECT * 
  FROM test_trailing_spaces
 WHERE variable >= 'DUMMY' 
   AND variable <= 'DUMMY' 
;
SELECT * 
  FROM test_trailing_spaces
 WHERE variable >= 'DUMMY    ' 
   AND variable <= 'DUMMY    ' 
;

oh, and by the way, please tell me what you get from this query –

SELECT VERSION()

First of all, thanks for your help & time.
Your test works fine but doesn’t replicate the problem that I had so I think that the problem was born during the data migration… anyway changing the column in VARCHAR the problem seems to be solved.
The MySql version is 5.1.52

The reason why I’ve ritten that your reply was a nonsense answer is because there’s a way to solve my problem, adding a directive in the configuration file of MySql db Engine.

Look at this please:

Set the SQL mode to strict

sql-mode=“STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH

Enjoy :wink:

that’s nonsense, you did not know that when you first replied :smiley:

anyhow, good job on finding the solution

:slight_smile:

Right! but in my deep I knew about a solution around the configuration…

Anyway every day there’s a new thing to learn (at least)…
:wink: