Generated SQL query not returning the same thing as the corresponding static query in sqlite3 HDBC

Question

I am generating SQL queries in Haskell and submitting them to a SQLite(3) database using HDBC. Now, this function returns a query:

import Database.HDBC.Sqlite3 
import Database.HDBC
data UmeQuery = UmeQuery String [SqlValue] deriving Show

tRunUmeQuery :: UmeQuery -> FilePath -> IO [[SqlValue]]
tRunUmeQuery (UmeQuery q args) dbFile = do
    conn <- connectSqlite3 dbFile
    stat <- prepare conn q
    s <- execute stat args
    res <- fetchAllRows' stat 
    disconnect conn
    return $ res

selectPos targetlt parentlt op pos = let 
    q= "select TARGET.* from levels tl, labeltypes tlt, segments TARGET, 
    (select TARGET.session_id session_id,SECONDARY.labeltype_id labeltype_id, 
    SECONDARY.label_id label_id,min(TARGET.label_id) min_childlabel_id from 
    levels tl, labeltypes tlt, segments TARGET, segments SECONDARY, labeltypes slt, 
    levels sl where TARGET.session_id = SECONDARY.session_id and ((SECONDARY.start 
    <= TARGET.start and TARGET.end <= SECONDARY.end) or (TARGET.start <= SECONDARY.start 
    and SECONDARY.end <= TARGET.end)) and tl.name = ? and sl.name = ? and SECONDARY.label '
    != '' and tl.id = tlt.level_id and sl.id = slt.level_id and tlt.id = TARGET.labeltype_id 
    and slt.id = SECONDARY.labeltype_id group by TARGET.session_id, TARGET.labeltype_id, 
    SECONDARY.label_id) SUMMARY, segments SECONDARY, labeltypes slt, levels sl where 
    TARGET.session_id = SECONDARY.session_id and TARGET.session_id = SUMMARY.session_id 
    and ((SECONDARY.start <= TARGET.start and TARGET.end <= SECONDARY.end) or (TARGET.start 
    <= SECONDARY.start and SECONDARY.end <= TARGET.end)) and tl.name = ? and sl.name = ? 
    and tl.id = tlt.level_id and tlt.id = TARGET.labeltype_id and SUMMARY.labeltype_id = 
    SECONDARY.labeltype_id and SUMMARY.label_id = SECONDARY.label_id and sl.id = slt.level_id 
    and slt.id = SECONDARY.labeltype_id and (TARGET.label_id - SUMMARY.min_childlabel_id +1) = 2 "
    a = [toSql targetlt, toSql parentlt, toSql targetlt, toSql parentlt ]
    in UmeQuery q a

which, when applied to the database returns the right thing:

> let a =selectPos "Word" "Utterance" "=" 2
> let b = tRunUmeQuery a testdb 
> b

outputs :

[[SqlByteString "1",SqlByteString "2",SqlByteString "3",SqlByteString "0.149383838383838",SqlByteString "0.312777777777778",SqlByteString "second"],[SqlByteString "1",SqlByteString "2",SqlByteString "6",SqlByteString "0.507488888888889",SqlByteString "0.655905050505051",SqlByteString "fourth"],[SqlByteString "2",SqlByteString "2",SqlByteString "3",SqlByteString "0.149383838383838",SqlByteString "0.312777777777778",SqlByteString "second"],[SqlByteString "2",SqlByteString "2",SqlByteString "6",SqlByteString "0.507488888888889",SqlByteString "0.655905050505051",SqlByteString "fourth"],[SqlByteString "3",SqlByteString "2",SqlByteString "3",SqlByteString "0.149383838383838",SqlByteString "0.312777777777778",SqlByteString "second"],[SqlByteString "3",SqlByteString "2",SqlByteString "6",SqlByteString "0.507488888888889",SqlByteString "0.655905050505051",SqlByteString "fourth"]]

Now, when I need to insert a couple of small dynamic parts into the query, like this (sorry, you have to scroll to the end of the String to see this):

selectPos targetlt parentlt op pos = let
    q= "select TARGET.* from levels tl, labeltypes tlt, segments TARGET, 
    (select TARGET.session_id session_id,SECONDARY.labeltype_id labeltype_id,
    SECONDARY.label_id label_id,min(TARGET.label_id) min_childlabel_id from 
    levels tl, labeltypes tlt, segments TARGET, segments SECONDARY, labeltypes slt,
     levels sl where TARGET.session_id = SECONDARY.session_id and ((SECONDARY.start 
     <= TARGET.start and TARGET.end <= SECONDARY.end) or (TARGET.start <= SECONDARY.start 
     and SECONDARY.end <= TARGET.end)) and tl.name = ? and sl.name = ? and SECONDARY.label 
     != '' and tl.id = tlt.level_id and sl.id = slt.level_id and tlt.id = TARGET.labeltype_id 
     and slt.id = SECONDARY.labeltype_id group by TARGET.session_id, TARGET.labeltype_id, 
     SECONDARY.label_id) SUMMARY, segments SECONDARY, labeltypes slt, levels sl where 
     TARGET.session_id = SECONDARY.session_id and TARGET.session_id = SUMMARY.session_id 
     and ((SECONDARY.start <= TARGET.start and TARGET.end <= SECONDARY.end) or (TARGET.start
      <= SECONDARY.start and SECONDARY.end <= TARGET.end)) and tl.name = ? and sl.name = ? 
      and tl.id = tlt.level_id and tlt.id = TARGET.labeltype_id and SUMMARY.labeltype_id = 
      SECONDARY.labeltype_id and SUMMARY.label_id = SECONDARY.label_id and sl.id = slt.level_id 
      and slt.id = SECONDARY.labeltype_id and (TARGET.label_id - SUMMARY.min_childlabel_id +1) " 
      ++ op ++ " ? "
    a = [toSql targetlt, toSql parentlt, toSql targetlt, toSql parentlt , toSql pos]
    in UmeQuery q a

and do the same thing, I get :

> let a =selectPos "Word" "Utterance" "=" 2
> let b = tRunUmeQuery a testdb  
> b 

[]

How come the second query does not return anything (or, the same thing actually)?

Any ideas?

Edit:

Ive investigated this further, thinking that this may have to do with lazy somehow. Ok, the has now been reshaped to this:

selectPos :: String -> String -> String -> Integer -> [[SqlValue]]
selectPos targetlt parentlt op pos = let
    q= foldl' (++)  [] ["select TARGET.* from levels tl, labeltypes tlt, segments TARGET, 
    (select TARGET.session_id session_id,SECONDARY.labeltype_id labeltype_id,SECONDARY.label_id 
    label_id,min(TARGET.label_id) min_childlabel_id from levels tl, labeltypes tlt, segments 
    TARGET, segments SECONDARY, labeltypes slt, levels sl where TARGET.session_id = SECONDARY.session_id "
    ,matchstring , " and tl.name = ? and sl.name = ? and SECONDARY.label != '' and tl.id = tlt.level_id 
    and sl.id = slt.level_id and tlt.id = TARGET.labeltype_id and slt.id = SECONDARY.labeltype_id 
    group by TARGET.session_id, TARGET.labeltype_id, SECONDARY.label_id) SUMMARY, segments SECONDARY, 
    labeltypes slt, levels sl where TARGET.session_id = SECONDARY.session_id and TARGET.session_id = 
    SUMMARY.session_id " , matchstring , " and tl.name = ? and sl.name = ? and tl.id = tlt.level_id 
    and tlt.id = TARGET.labeltype_id and SUMMARY.labeltype_id = SECONDARY.labeltype_id and SUMMARY.label_id
     = SECONDARY.label_id and sl.id = slt.level_id and slt.id = SECONDARY.labeltype_id and 
     (TARGET.label_id - SUMMARY.min_childlabel_id +1) " , op , " ? "]  
    a = [toSql targetlt, toSql parentlt, toSql targetlt, toSql parentlt , toSql (pos :: Integer)]
    in UmeQuery q a

Unfortunately, this does not help the issue (and when I :sprint the return value of the function in ghci, it is still unevaluated). So, laziness may be the issue somehow, but I don't know how to make this fully evaluated..? Please, any ideas?

Answer 1

So... just to state the facts:

• your code does run it does not produce any syntax errors or warnings (and this is for both the haskell and the sql that is ran by the haskell)
• the original query does run but not with op and pos added (there were already dynamic parts to it)
• you get an empty set back (meaning, the query returns no rows)...

If all these things are true, it leads me to believe that the query must be valid but wrong. Check the data? Dump the query, run it manually. Let me know.

things to try:

• Try rolling back the changes to see if it still works (so you know nothing was accidentally changed and to verify the data is the same).
• Can you try testing with a simpler query?
• Can you try dumping the query variable and running it manually in the DB (with and without changes)?
• Do you want to post a few rows of your data (some rows that will be returned, some that won't) so I can load it into a temp table test with it?
• Try adding just pos to the working query (with op hard-coded) and see if that works
• Try adding just op to the working query (with pos hard-coded) and see if that works
• Make sure you are listing your variables in the correct order everywhere

For some reason, I keep thinking it might be a datatype issue with casting or something but I have never worked with Haskell so I don't can't really guess at what else could be going on.

Other suggestions:

• format your query properly so it is easily readable (at least a little, so it isn't one huge string)
• update your question to include specifications on how your environment is set up (with versions of software/things and stuff)
• if you think the issue is tied to laziness, try forcing evaluation...? But the query did already have dynamic/variable parts to it. I would have to assume that they would have the same problem, if this was the case, and the query wouldn't have worked to begin with.
• this would be silly, but you didn't happen to change what DB you are pulling from, did you?

---

sqlite> select * from temp;
temp_id     temp_name
----------  ----------
1           one
2           two
3           three

import Database.HDBC.Sqlite3 
import Database.HDBC

testdb = "C:\\Users\\Kim!\\test.db"

data UmeQuery = UmeQuery String [SqlValue] deriving Show

tRunUmeQuery :: UmeQuery -> FilePath -> IO [[SqlValue]]

tRunUmeQuery (UmeQuery q args) dbFile = do
    conn <- connectSqlite3 dbFile
    stat <- prepare conn q
    s <- execute stat args
    res <- fetchAllRows' stat 
    disconnect conn
    return $ res
     
selectPos temp_id op = let 
   q = "select temp_id, temp_name from temp where temp_id = " ++ op ++ " ?";  
   a = [ toSql temp_id ] 
   in UmeQuery q a

> let a = selectPos (1::Int) "="
> let b = tRunUmeQuery a testdb 
> b
[[SqlByteString "1",SqlByteString "one"]]

> let a = selectPos (1::Int) ">"
> let b = tRunUmeQuery a testdb 
> b
[[SqlByteString "2",SqlByteString "two"],[SqlByteString "3",SqlByteString "three"]] 

Quick note: I've never touched Haskell or SQLite before today. I am running Haskell Platform 2014.2.0.0 with this SQLite3 - sqlite-dll-win64-x64-201409301904.zip on Windows 7 Professional 64bit.

edit: this also works... (query is a lil different, too)

import Data.List

selectPos temp_id op temp_name = let 
   q = foldl' (++)  [] [
       "select temp_id, temp_name        " ++ 
       "from   temp                      " ++
       "where  temp_id " , op , " ? or   " ++
       "       temp_name = ?             "]
   a = [ toSql (temp_id::Int), toSql temp_name ]  
   in UmeQuery q a

> let a = selectPos 1 ">" "one"
> let b = tRunUmeQuery a testdb 
> b
[[SqlByteString "1",SqlByteString "one"],[SqlByteString "2",SqlByteString "two"],[SqlByteString "3",SqlByteString "three"]] 

edit: and this works...

sqlite> insert into temp values (4, "Word"); 
sqlite> insert into temp values (5, "Utterance");

selectPos targetlt parentlt op pos = let 
   q = " select temp_id, temp_name        \
       \ from   temp                      \
       \ where  temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_id "++op++" ?        "
   a = [toSql targetlt, toSql parentlt, 
        toSql targetlt, toSql parentlt, 
        toSql (pos::Int) ]
   in UmeQuery q a

> let a = selectPos "Word" "Utterance" "=" 2
> let b = tRunUmeQuery a testdb 
> b
[[SqlByteString "2",SqlByteString "two"],[SqlByteString "4",SqlByteString "Word"],[SqlByteString "5",SqlByteString "Utterance"]]

so... in your queries that you posted in the question... there is an unexpected difference, too... that doesn't have to do with the variables. It's a single quote. Not sure if just a typo in copy and paste or what. I obviously cannot run your query as it is exactly because that's a significant amount of mock tables and data to come up with...

edit: hah... I came back to this again. I noticed you had an extra line above your last selectPos example that I wasn't using. I had to do it like this to get it to work... [[SqlValue]] or IO [[SqlValue]] as the last value did not work for me; errors (I'm just trying things, I don't know if either of those values truly make sense).

selectPos :: String -> String -> String -> Integer -> UmeQuery
selectPos targetlt parentlt op pos = let 
   q = " select temp_id, temp_name        \
       \ from   temp                      \
       \ where  temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_name = ?  or         \
       \        temp_name != ?  or        \
       \        temp_id "++op++" ?        "
   a = [toSql targetlt, toSql parentlt, 
        toSql targetlt, toSql parentlt, 
        toSql pos ]
   in UmeQuery q a

> let a = selectPos "Word" "Utterance" "=" 2
> let b = tRunUmeQuery a testdb 
> b
[[SqlByteString "1",SqlByteString "one"],[SqlByteString "2",SqlByteString "two"],[SqlByteString "3",SqlByteString "three"],[SqlByteString "4",SqlByteString "Word"],[SqlByteString "5",SqlByteString "Utterance"]] 

either way on this... I'm happy I got to write my first Haskell program today...!