db select problem

[chengyi]

Hi, @klihub
I make a resource set be acquired. it contains resource audio_playback. then I using the debug console to watch audio_playback_users table.

first I execute select * from audio_playback_users, it output:

rsetid     autorel       state       grant role                    

     1           0           2           1 usb 

second, I execute select * from audio_playback_users where grant=1, it output:

rsetid     autorel       state       grant role                    

no rows

this seems strange. is this a db engine bug?

[klihub]

Unfortunately that happens because the DB/parser is a bit stupid while the low level condition evluator is conservative and inflexible:

• the sql parser will parse your '1' as an unsigned integer, but the table has been defined with 'grant' being a signed integer
• the conditional evaluator refuses to compare two values of different types (even different integer types) and always considers them unequal.

Luckily, in your case a workaround is to formulate the query so that you force the compared vlaue to be a signed integer too, at the parser level:

# grant is a signed integer:
murphy db> describe audio_playback_users
index  name    type     length
------------------------------
    0 *rsetid  unsigned      4
    1  autorel integer       4
    2  state   integer       4
    3  grant   integer       4
    4  policy  varchar      24
    5  role    varchar      24
    6  pid     varchar      24
    7  name    varchar      24

# this will fail, because '1' gets parsed as an unsigned integer:
murphy db> select * from audio_playback_users where grant = 1
    rsetid     autorel       state       grant policy                   role                     pid                      name                    
--------------------------------------------------------------------------------------------------------------------------------------------------
no rows
# but if we force '1' to be signed using '+1' it should succeed:
murphy db> select * from audio_playback_users where grant = +1
    rsetid     autorel       state       grant policy                   role                     pid                      name                    
--------------------------------------------------------------------------------------------------------------------------------------------------
         1           0           2           1 relaxed                  music                    <unknown>                <unknown>   

[klihub]

If you want, you can cherry-pick #27 , which relaxes the conditions for integer inequality by actually comparing signed and unsigned integers if they both fit in the positive signed integer range. Note, however, that I have not properly tested boundary conditions of that patch, so it might work incorrectly there...

[chengyi]

@klihub thanks. got it.