Hi, I found an issue with SQLite 3.27.2 and is also existent in 3.20.1.
Of the four queries below, the second query should have returned the same result set as the first query. As a side note, I also tried creating the tables with no primary keys and got the same results. I also tried using the "ON" syntax and got the same results. sqlite3.exe bugtest SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> create table competitors (compid INTEGER PRIMARY KEY, last TEXT, first TEXT); sqlite> create table matches (category INTEGER, number INTEGER, blue INTEGER, white INTEGER, PRIMARY KEY(category,number)); sqlite> insert into matches values(10032,2,1109,1197); sqlite> insert into competitors values (1109,"SERHAN", "James"); sqlite> insert into competitors values (1197,"WHITE","Justin"); sqlite> .headers on sqlite> select * from matches; category|number|blue|white 10032|2|1109|1197 sqlite> select * from competitors; compid|last|first 1109|SERHAN|James 1197|WHITE|Justin sqlite> select competitors.* from matches INNER JOIN competitors WHERE matches.white = competitors.compid and matches.category=10032 and matches.number=2 and competitors.first="Justin"; compid|last|first 1197|WHITE|Justin sqlite> select competitors.* from matches INNER JOIN competitors WHERE matches.white = competitors.compid and matches.category=10032 and matches.number=2 and competitors.last="WHITE"; sqlite> select competitors.* from matches INNER JOIN competitors WHERE matches.blue = competitors.compid and matches.category=10032 and matches.number=2 and competitors.first="James"; compid|last|first 1109|SERHAN|James sqlite> select competitors.* from matches INNER JOIN competitors WHERE matches.blue = competitors.compid and matches.category=10032 and matches.number=2 and competitors.last="SERHAN"; compid|last|first 1109|SERHAN|James sqlite>.quit Out of curiosity, I changed the last name of "WHITE" to "WHTE" and queries now work!?!? sqlite3.exe bugtest SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints. sqlite> update competitors set last="WHTE" where compid=1197; sqlite> select competitors.* from matches INNER JOIN competitors WHERE matches.white = competitors.compid and matches.category=10032 and matches.number=2 and competitors.last="WHTE"; 1197|WHTE|Justin sqlite> select competitors.* from matches INNER JOIN competitors WHERE matches.white = competitors.compid and matches.category=10032 and matches.number=2 and competitors.first="Justin"; 1197|WHTE|Justin sqlite> select competitors.* from matches INNER JOIN competitors ON matches.blue = competitors.compid where matches.category=10032 and matches.number=2 and competitors.first="James"; 1109|SERHAN|James sqlite> select competitors.* from matches INNER JOIN competitors ON matches.blue = competitors.compid where matches.category=10032 and matches.number=2 and competitors.last="SERHAN"; 1109|SERHAN|James sqlite> .quit I hope you have enough details to troubleshoot! Thanks in advance, STeve Horvath _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
