1

Real Final edit (since it seems like people think is a null :P):

Let me rephrase it, since it is hard to explain and it seems no one can help me. I made 2 stored procedures in MySQL with phpMyAdmin. Both with an IN parameter VARCHAR(500) in utf8, var1.

With a value of 'novalue' for the In parameter these are the behaviours:

tableA: 

------------------ 
a  |  b  | example 
------------------ 
1  |  A  |  1 
2  |  A  |  1 
3  |  T  |  1 

SELECT * FROM tableA
WHERE (var1 = 'novalue')

SELECT * FROM tableA
WHERE (var1 = 'novalue' OR var1 = tableA.col1)

Expected output (Only first procedure will give me this result): 

------------------ 
a  |  b  | example 
------------------ 
1  |  A  |  1 
2  |  A  |  1 
3  |  T  |  1

So my problem is, how to get the same expected output on the second procedure?

Thanks in advance

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Jorge Rodríguez González
  • 75
  • 1
  • 9
  • Please add example data and expected output – juergen d Feb 02 '17 at 10:14
  • Well, if I enter an empty var1 = '' and the condition is WHERE var1 = '' OR var1 = col1 it should retrieve all rows, since the first part of the OR clause is always true – Jorge Rodríguez González Feb 02 '17 at 10:26
  • See this question as an example how to show example data and expected ouput: http://stackoverflow.com/questions/41757206/sum-count-of-case-within-case-in-mysql – juergen d Feb 02 '17 at 10:28
  • 1
    are you sure var1 is '' (an empty string) and not actually NULL? – AntDC Feb 02 '17 at 10:33
  • yes, when changing the condition to var1='' gives me back all rows. Also, when adding the parameter to the select fields, it shows an empty varchar not null – Jorge Rodríguez González Feb 02 '17 at 10:37
  • Also, this very same SQL works correctly in SQL server. Just as a piece of info – Jorge Rodríguez González Feb 02 '17 at 10:39
  • And when using this kind of condition with INT it does work. So In parameter var1 = 0 then condition (var1 = 0 OR var1 = col1) would give me all rows – Jorge Rodríguez González Feb 02 '17 at 11:35
  • Are you 100% sure, that it is '' and not a NULL? Can you try it in another client or in command line? Can you please show us the query which invokes this procedure (and maybe the full code with procedure head)? – Pred Feb 02 '17 at 16:01
  • If null was the value passed, the first select would also give 0 rows as result. I am calling these procedures from phpMyAdmin on the run option and not typing anything on the IN parameter. I am trying to fix it before calling it from my app. Pretty sure that value passed is not null, since the first procedure returns all rows. – Jorge Rodríguez González Feb 02 '17 at 16:17
  • You are right in the NULL, my fault. Try it by typing in the actual command. CALL myProc ''; – Pred Feb 02 '17 at 16:29
  • Changed the value of the IN parameter since the one that commented if it was null got a positive vote, and does not help me at all... – Jorge Rodríguez González Feb 02 '17 at 16:31
  • You! Man! deserve some good things in your life xD, tried it in the SQL command line and gave me this error: #1267 - Ilegal mezcla de collations (utf8mb4_unicode_ci,IMPLICIT) y (utf8mb4_general_ci,IMPLICIT) para operación '=', where my problem is collation. Running the procedure in the run option did not give me any feedback on this! THANK YOU. My problem now is how to set the unicode one for IN parameters – Jorge Rodríguez González Feb 02 '17 at 16:35
  • Welcome and have fun :) – Pred Feb 02 '17 at 16:43
  • Please Pred, can you write an answer to this post so I can mark it as the solution?? So when I executed the procedure from the run option in phpMyAdmin it didn't give the error and when doing so in the SQL window it threw me back error #1267. To solve it I just did what this post says: http://stackoverflow.com/questions/1008287/illegal-mix-of-collations-mysql-error Thanks! – Jorge Rodríguez González Feb 02 '17 at 16:47

0 Answers0