MySql Between clause not returning expected values when used in a JOIN with Order By-CodePudding

I have two tables that I want to join and query. I want the query to return all records where the field columns value is between 2 values x, y. Table 1:

id  int(11) YES     NULL    
game    int(11) YES     NULL    
numbers varchar(255)    YES     NULL    
bonus   varchar(255)    YES     NULL    
multiplier  varchar(255)    YES     NULL    
created int(11) YES     NULL    
data    text    YES     NULL

Table 2:

bundle  varchar(128)    NO  MUL     
deleted tinyint(4)  NO  PRI 0   
entity_id   int(10) unsigned    NO  PRI NULL    
revision_id int(10) unsigned    NO  MUL NULL    
langcode    varchar(32) NO  PRI     
delta   int(10) unsigned    NO  PRI NULL    
field_secondary_prize_value_value   varchar(255)    NO      NULL

And the query :

SELECT
    t1.id,
    t2.field_secondary_prize_value_value
FROM
    table1 t1
INNER JOIN table2 t2 ON
    t1.id = t2.entity_id AND t2.field_secondary_prize_value_value BETWEEN 0 AND 10
ORDER BY
    t2.field_secondary_prize_value_value
DESC;

The result sets should start with records where t2.field_secondary_prize_value_value is 10 but this what I am getting:

ID.     field_secondary_prize_value_value  
1490476 5
1490496 5
1490531 5
1490596 5
1490636 5
1490651 5
1490666 5
1490676 5
1490756 5
1490761 5

If replace the between clause to equality: i.e:

INNER JOIN table2 t2 ON
    t1.id = t2.entity_id AND t2.field_secondary_prize_value_value = 10

I tried casting varchar to int but I still got the same results. If I order by a column on the base table I get results where field_secondary_prize_value_value shows different values within the range. Perhaps I need to use a subquery.

CodePudding user response：

The ordering is actually right, because the column is a varchar and values will be ordered alphanumeric. You can change it to a numeric sortorder by:

changing the datatype of the column to a numeric one if that is an option/applies
use type conversion in your queries (see link in Akina's comment for explanation and pitfalls)

More explanations of sorting types can be found here.

Applying this to your query (quick 'n dirty):

SELECT
  t1.id,
  t2.field_secondary_prize_value_value
FROM
  table1 t1
INNER JOIN table2 t2 ON
  t1.id = t2.entity_id AND t2.field_secondary_prize_value_value BETWEEN 0 AND 10
ORDER BY
  (t2.field_secondary_prize_value_value * 1)
DESC;

Personally I think it is better in this case to do the type conversion as close to the source as possible so it's easier to stay consistent, but that of course also depends on what you're trying to accomplish.

My alternative would be:

SELECT
  t1.id,
  t2.prize_value_num
FROM
  table1 t1
INNER JOIN (SELECT 
    entity_id, 
    (field_secondary_prize_value_value * 1) AS prize_value_num 
     -- or any other type conversion you see fit
  FROM table2
  ) t2 ON
  t1.id = t2.entity_id AND t2.prize_value_num BETWEEN 0 AND 10
ORDER BY
  t2.prize_value_num
DESC;

Be aware: in both examples the value '5B' will also meet the condition BETWEEN 0 AND 10.