Generated SQL query not returning the same thing as the corresponding static query in sqlite3 HDBC
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?
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
posto the working query (withophard-coded) and see if that works - Try adding just
opto the working query (withposhard-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...!
- Cannot drop PostgreSQL role. Error: `cannot be dropped because some objects depend on it`
- SQL full outer join, default value to column in other table
- Error message: (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
- SQL filter rows by range overlap
- EXPDP doesn't recognise user names
- SSRS 2014 date range parameters not working with an OR clause
- Is there a way of referencing the prior observation's values in SAS/SQL when defining a new variable?
- Calculate duration using T-SQL
- How can I set a numeric wildcard in MySQL?
- SQL replace colum value with a specific string
- Conversion failed when converting the nvarchar value to data type int while inserting records into table
- Possible to Stringize a Polars Expression?
- Weighted hierarchical cumulative sum
- SQL statement evaluation order
- SQL rollup to add a total row for multiple columns
- SQL: query to select all records from join table
- Select SQL Server database size
- Order by the oldest relation Postgres
- INSERT INTO a temp table, and have an IDENTITY field created, without first declaring the temp table?
- TSQL: how to write this query?
- How to create a role in postgress db which supports the CRUD operations but doesn't allow to drop the database and tables with that role
- How Best to Extract Specific JSON Key-Value Pairs in Snowflake
- Are MySql Views Dynamic and Efficient?
- Create a new column based on duplicates of another column
- How to match req.body with update query?
- Dapper - Map join query to DTO using 'splitOn'
- SQL command works fine when run manually (SQL Developer) but gives ORA-00922 in Python's oracledb module
- Derive values from previous Quarters
- Fill null values in BigQuery table with next non-null value from specific column
- Error while trying to migrate data from one table to another in BigQuery