The generated SQL query does not return the same as the corresponding static query in sqlite3 HDBC

Question

I generate SQL queries in Haskell and send them to the SQLite (3) database using HDBC. Now this function returns a request:

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 qa 

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, 2nd, SqlByteString, 2q, 1q, 2q "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 ", Sql3838, 38t38, 38t38, 38138, 38138, 38138, 38138, 38138, 38138, 3813838, 381 SqlByteString "0.312777777777778", SqlByteString "second"], [SqlByteString "3", SqlByteString "2", SqlByteString "6", SqlByteString "0.50748888888888989", SqlBy50ring50 “50th50] 50q50B50te50ring50y5050te5050t50qt50] 50q50b50te50ring50y5050te5050te5050te5050t50] 50t5050t50]

Now that I need to insert a couple of small dynamic parts into the query, like this (sorry, you need to scroll to the end of the line 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 qa 

and do the same, I get:

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

[]

Why does the second query return nothing (or, in fact, something in fact)?

Any ideas?

Edit:

Ive explored this further, thinking that this may be due to the lazy somehow. So now it has been changed:

 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 qa 

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

Answer 1

So ... just to state the facts:

• your code runs it, does not generate any syntax errors or warnings (and this is for both haskell and sql, haskell)
• the original request is executed, but not with the addition of op and pos (there were already dynamic parts)
• you get an empty set back (which means the query does not return rows) ...

If all this is true, it makes me think that the request should be valid, but incorrect. Check the data? Dump the request, run it manually. Let me know.

things to try:

• Try undoing the changes to see if everything works (so you don’t know that nothing was accidentally changed and to check that the data is the same).
• Can you try testing with a simpler query?
• Can you try to reset the query variable and run it manually in the database (with and without changes)?
• Do you want to publish several rows of your data (some rows that will be returned, some of them will not) so that I can load it into the temp test table?
• Try adding only pos to the work request (with op hard-coded) and see if this works
• Try adding only op to the work request (with pos hard-coded) and see if this works
• Make sure you list the variables in the correct order everywhere

For some reason, I keep thinking that it might be a data type problem with casting or something else, but I never worked with Haskell, so I can’t guess what else might happen.

Other offers:

• format your request correctly so that it is easy to read (at least a little, so this is not one huge line)
• update your question to include specifications on how your environment is configured (with software versions / stuff and more).
• If you think the problem is laziness, try to make it evaluate ...? But the request already had dynamic / variable parts. I had to assume that they would have the same problem if that were the case and the request would not work for a start.
• That would be stupid, but you were not able to change which database you are pulling from, right?

---

 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 qa 

 > 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 still haven't touched Haskell or SQLite. 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 works too ... (the request is also different from lil)

 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 qa > 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 it 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 qa > 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 also an unexpected difference ... this is not related to variables. This is one quote. Not sure if only typo in copy and paste or what. Obviously, I can’t fulfill your request, because this is precisely because a significant amount of mockup tables and data were invented ...

edit: hah ... I'm back to that again. I noticed that you had an extra line above your previous selectPos example that I did not use. I had to do this in order to make it work ... [[SqlValue]] or IO [[SqlValue]] , since the latter value did not work for me; errors (I'm just trying, I don't know if any of these values ​​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 qa > 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"]] 

anyway ... I'm glad I need to write my first Haskell program today ...!