Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: OR-statement returns true on "wrong" condition, returning wrong row

    Hello

    I have this OR in a simple query:
    Code:
    DECLARE @searchString nvarchar(100)
    SET @searchString = 'sample'
    SELECT TOP(1) * FROM user
    WHERE (user.identity LIKE @searchString OR 
    CHARINDEX(@searchString, user.firstname + ' ' + user.lastname) > 0)
    PS: Handwritten...

    If I pass in a searchString that matches the Identity and a different users lastname, this query will return the user with the lastname-match (which is wrong in my eyes, it should have matched the Identity first, then returned that row [Identity is a Primary key, indexed non-clustered]).


    I've tried various things:
    Removing LastName: (CHARINDEX(@searchString, user.firstname + ' ') > 0), then the returned row is from a matching identity, due to the lastname of a user was a match, but lastname was removed...so :P
    Flipping the conditions around
    Adding/removing paranthesis... without any luck.


    Is there some option somewhere, to force the OR statement to return on the first true condition. I've always thought OR-statements (in a computer that is) breaked and returned true on the first true condition it found (from left to right, not bother to check the rest of the conditions...)?

    Or do I have to rewrite the query, with an IF, checking if the @searchString is a valid identity-format, if it is, query on the identity, if not query on the username...?
    Suggestions are more than welcome.

    Edit: Solved, TOP(1) will return the first row, from all the rows that matches either [where the rows are by default, ordered by the way they were inserted into the table]...

    Edit: SQL 2008 , R2.
    Last edited by ManyTimes; 06-11-13 at 19:37.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Doesn't LIKE require wildcard characters for pattern-matching?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    It is not required, but without it, LIKE equals operator sign =, as far as my understanding goes.

    But if I write LIKE or = does not change the row returned, the condition with CHARINDEX() is still checked first...

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try adding a Order By clause:

    Code:
    ORDER BY case when user.identity LIKE @searchString then 1 else 2 end
    Hope this helps.

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    equal is not LIKE

    Quote Originally Posted by ManyTimes View Post
    It is not required, but without it, LIKE equals operator sign =, as far as my understanding goes.
    NO! The equality for strings pads the shorter string with trailing blanks and then compares them position by position. The LIKE <pattern> does not do this padding. It uses a simple regular expression algorithm.

    Identity is a terrible name for a column, since it is a reserved word in T-SQL, violates ISO-11179 rules and is too vague to be meaningful. We have no idea even what the data type is! This is why Netiquette requires DDL.

  6. #6
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    Celko:
    There exists no column with the name identity, nor does the table "user" exist, query is handwritten...

    Behind the scenes for a LIKE vs = is not the same (of course they are not, else you could have wildcars with the = !!!). Point was (and I can see that I worded myself, poorly..."far as my understanding goes", like it goes not further...Yeah, right...)...oh why even bother...

    Imex: Hmm, do not want to order any thing.

    My problem still exists though: Why does the OR statement not trigger when the first condition is met, as in: the condition does not trigger from "left to right".
    Last edited by ManyTimes; 06-11-13 at 10:57.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ManyTimes View Post
    I've always thought OR-statements (in a computer that is) breaked and returned true on the first true condition it found (from left to right, not bother to check the rest of the conditions...)?
    No, an OR statement in a WHERE condition will return all the records that match either clause, and each record is evaluated independently. Its certainly not going to stop at the first record that matches.
    You may be thinking of a CASE statement, which stops at the first successful match.
    Maybe you should be using a CASE statement in your WHERE clause instead, but this might yield terrible query performance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't reproduce this problem. Please try running this code:
    Code:
    DECLARE @searchString nvarchar(100)
    DECLARE  @u TABLE (
       [identity]       INT
    ,  [firstname]      NVARCHAR(9)
    ,  [lastname]       NVARCHAR(9)
       )
    
    INSERT @u ([identity], [firstname], [lastname])
       VALUES (1, N'John', N'Doe'), (2, N'Jane', N'Smith')
    ,    (3, N'Just a', N'Sample')
    
    SET @searchString = 'sample'
    
    SELECT TOP(1) *
       FROM @u AS u
       WHERE (u.[identity] LIKE @searchString
          OR CHARINDEX(@searchString, u.firstname + ' ' + u.lastname) > 0)
    
    SET @searchString = '2'
    
    SELECT TOP(1) *
       FROM @u AS u
       WHERE (u.[identity] LIKE @searchString
          OR CHARINDEX(@searchString, u.firstname + ' ' + u.lastname) > 0)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Let me ramble a bit.

    You may be thinking of a CASE statement, which stops at the first successful match.
    Let me ramble a bit.

    There is no short-circuit or McCarthy evaluation in functional and declarative languages. The SQL model is that a little demon is assigned to every row in the FROM clause of the query by the WHERE clause. This demon applies the search condition in parallel to each rows. Each demon is free to do anything he wants as long as it “effectively” (that is a special word in ISO-speak) does the search condition and returns TRUE, FALSE or UNKNOWN. There is no concept of sequential execution. Thre is no concept of even having exactly the same code applied to each row!

    The problem with procedural code and left-to-right evaluation is that order matters. Imagine two functions f(x) and g(x) where f(x) returns a result, but also changes x (a side effect). This means the sequence BEGIN f(x); g(x); END; is not the same as the sequence BEGIN g(x); f(x); END;

    The CASE expression was taken from ADA; as I remember, it was either Phil Shaw or Andrew Eisenberg who proposed it. We then added COALESCE, IFNULL, et al to the family. Here, the order of the WHEN clauses matters, as per ADA and primitive recursive function theory. First you go to the THEN clauses and find the highest data type. This becomes the data type of the expression. It does not matter if a THEN clause cannot be executed.

    We debated changing the rules to say that one and only one WHEN could be TRUE, but that was a nightmare.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    We debated changing the rules to say that one and only one WHEN could be TRUE, but that was a nightmare.
    Who in the world would argue for that? Makes no sense at all.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Who in the world would argue for that? Makes no sense at all.
    The arguments were that (1) SQL uses the "all at once, no ordering" model of processing. (2) The CASE expression could be re-arranged.

    The counter-arguments were that (1) primitive recursive function theory and all that wonderful math have a sector operator that is sequential (2) The optimizer can know that if you are testing predicate w[n+1], you know that predicates w[1] thru w[n] were FALSE or UNKNOWN in the DML; this helps the optimizer a lot! (3) it is a lot easier to implement than doing w[1] to w[n] on every row when (n) is large.

  12. #12
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    blindman:
    Haha, of course it will return all the results that matches either, it is a god damn loop that checks each row... And after all rows are returned (that did match either), I return only top(1)... LMAO! What was I thinking! After 10 years of programming...

    Why the rest of the folks didnt just say that ASAP, is above me (not really, did not see it myself at the time posting )...

    Thanks all, especially blindman, who was not blind at all! And thanks to Imex to, doing some ordering now, everything is good!

    Edit: Pat, if Identity was not an INT, but a NVARCHAR, and the Identity also contained the word "sample"... While a lastname of a user also was the word "sample"... Thought I explained it "clearly"
    >> If I pass in a searchString that matches the Identity and a different users lastname
    :P
    Anyways, my bad, saying identity without mentioning that it is not decimal/int, but nvarchar... Forgive n forget!
    Last edited by ManyTimes; 06-12-13 at 08:42.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by ManyTimes View Post
    Why the rest of the folks didnt just say that ASAP, is above me (not really, did not see it myself at the time posting )...
    Either:
    A) Business requirements of the original post were unclear.
    B) The obvious answers are occasionally overlooked, under the assumption that a problem is difficult.
    C) I am epic beyond belief.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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