Your nested select statement wants to return many rows. Technically I think this is illegal SQL (?), but it looks like SQLite is being lax about this as usual and doing what it normally does in this sort of situation: picks a row at random from the inner select.
Your statement is more or less equivalent to: SELECT DISTINCT refVolume FROM cosSquered WHERE (refVolumn CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM cosSquared LIMIT 1) The statement that will output the same same list of refVolumes is: SELECT DISTINCT refVolume FROM cosSquared WHERE CAST(10 * cosSquared AS INT) = 9 On Tue, 18 Jun 2019 at 16:44, Roman Fleysher <roman.fleys...@einstein.yu.edu> wrote: > Dear SQLiters, > > > I can not figure out what I am doing wrong. In testing, I simplified to > the following: > > CREATE TABLE cosSquared(refVolume INT, vecVolume INT, cosSquared REAL); > > SELECT refVolume, CAST(10*max(cosSquared) AS INT) FROM cosSquared GROUP BY > refVolume; > > refVolume CAST(10*max(cosSquared) AS INT) > ---------- ------------------------------- > 2 9 > 3 9 > 4 9 > 5 9 > ............. > 31 9 > 32 9 > 33 9 > > That is, we see that for refVolumes between 2 and 33, the value of the > CAST() is always 9. Thus, I expect the following statement to output the > same list of refVolumes. But it does not: > > SELECT DISTINCT refVolume FROM cosSquared > WHERE (refVolume, CAST(10*cosSquared AS INT)) = (SELECT refVolume, 9 FROM > cosSquared); > > refVolume > ---------- > 2 > > What am I doing wrong? I am using version 3.16. > > Thank you for your help, > > Roman > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
