Oh, Thank you Barry. I am glad it is not a bug. Bug is in my head.
Thank you, Roman ________________________________________ From: sqlite-users [[email protected]] on behalf of Barry [[email protected]] Sent: Tuesday, June 18, 2019 7:59 PM To: SQLite mailing list Subject: Re: [sqlite] Is this a bug? 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 <[email protected]> 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 > [email protected] > https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&reserved=0 > _______________________________________________ sqlite-users mailing list [email protected] https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C0f35bb6726c3413690d208d6f4490695%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636964991807478228&sdata=AJjpaa48jiKBcZI3eSsNRapqcBp%2FywsQ%2BWMoYCo1jOw%3D&reserved=0 _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
