| [1]StevenBao | Hi, can someone tell me why the following sql query isn't working? Is it the syntax? SELECT * FROM pages WHERE `fold1`='products' AND fold2=NULL ORDER BY pid DESC LIMIT 1 Yes, all these records exist |
| ToeBee | when comparing to null you need the "IS" compare operator not = fold2 IS null |
| [1]StevenBao | THANK YOU ToeBee! |
| ToeBee | always beware of nulls. They can cause very unexpected results |
| dools | if i run this query: select MIN(score) from vpc_key_behaviour_cohort_score where core_behaviour_id = 4 and key_behaviour_id = 3;, i get the result: MIN(score) = 1.3999999761581 but when i run the query select * from vpc_key_behaviour_cohort_score where score = 1.3999999761581; i get an empty set... how can that be possible? |
| [RainMkr] | LoL you used floats as a data type. |
| ToeBee | heh |
| dools | [RainMkr]: yeah, is that a bad idea? |
| [RainMkr] | well... what do you think? ;) |
| dools | [RainMkr]: ... i think if i'm storing a float value that i should use a float datatype? |
| [RainMkr] | dools: I think if you are going to use a float data type you should learn about floats |
| dools | [RainMkr]: thanks for your input |
| ToeBee | floats suck if you need precision there are always rounding issues |
| dools | ToeBee: i don't need precision, just need something that won't kill a floating point value. i looked at the types and float seemd appropriate. decimal also looked appropriate. perhaps i should have used decimal |
| Xgc | dools: Don't expect to check for equality using floats unless you're checking identity. |
| dools | Xgc: what do you mean by 'checking identity'? |
| Xgc | dools: WHERE t1.colx=t2.colx ... when colx contains a particular stored value. dools: by identify I mean tests for self. dools: or anything similar. |
| dools | Xgc: ah okay. so not with something like 'where score < 2'; which is also not returning that particular row (although it is returning things like 1.7 and 1.4) |
| Xgc | dools: Once you have a stored value, you can check col1=col1 successfully. But you can't assume some constant 5.678 can equal any stored float value. |
| dools | Xgc: right. yes it does seem like using float datatypes is a bad idea |
| Xgc | dools: Not so much a bad idea, but you need to know how to use them properly. |
| dools | Xgc: would you be able to give me an example of where you would use them to benefit if no comparison with constants work properly? |
| ToeBee | dools: what happens when you do a select * from table where value = (select min(score) from table) ? |
| dools | ToeBee: it returns two rows which have a score of 1.4 in them ToeBee: if i change that to MIN then it prints out 1.3999999761581 so it's apparently using different precisions depending on whether i've called MIN or not |
| ToeBee | well it might just be a display issue |
| dools | ToeBee: yes, i suppose it must be because even if i use MIN, but include other columns, it shows up as 1.4 |
| Xgc | dools: When you only care about testing or ordering by stored values or where inequalities are all you care about (f1 < f2) or (f1 < constant). |
| dools | Xgc: right thanks ToeBee: which is problematic because i really want to find out the primary keys of the row with that minimum score :P |
| Xgc | dools: That's not a problem. dools: The MIN(f1) will always match some specific f1. dools: Apart from null. |