SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

    ------------------------------------------------------------------------

    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'

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by digitstudios View Post
    A client application that I can't change...
    if you cannot change it, you will have to live with it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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..

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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??

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    here, test for yourself...
    Code:
    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 --
    Code:
    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 --
    Code:
    SELECT VERSION()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    here, test for yourself...
    Code:
    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 --
    Code:
    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 --
    Code:
    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

  8. #8
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool

    Quote Originally Posted by r937 View Post
    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
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by digitstudios View Post
    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.
    that's nonsense, you did not know that when you first replied

    anyhow, good job on finding the solution

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Aug 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's nonsense, you did not know that when you first replied

    anyhow, good job on finding the solution

    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)...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •