Re: [sqlite] Group by in sqlite 3.8 works a little differently depending on the spaces at the end
On Wed, Aug 28, 2013 at 5:11 PM, Igor Tandetnik wrote: > On 8/28/2013 8:57 AM, Max Vlasov wrote: > See the recent discussion at > > http://comments.gmane.org/gmane.comp.db.sqlite.general/83005 > > It's not about trailing spaces, but about whether Title in GROUP BY resolves > to mean the table column or the alias. Thanks, Igor, sorry, didn't notice the original discussion Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Group by in sqlite 3.8 works a little differently depending on the spaces at the end
On 8/28/2013 8:57 AM, Max Vlasov wrote: the following query (notice the space at the end of the 3rd string) Create table [TestTable] ([Title] TEXT); INsert into TestTable (Title) VALUES ('simple text'); INsert into TestTable (Title) VALUES ('simple text'); INsert into TestTable (Title) VALUES ('simple text '); select Trim(Title) as Title, Count(*) as Cnt FROM TestTable Group By Title; produces two results "simple text""2" "simple text""1" while all previous versions I tried a single one "simple text""3" See the recent discussion at http://comments.gmane.org/gmane.comp.db.sqlite.general/83005 It's not about trailing spaces, but about whether Title in GROUP BY resolves to mean the table column or the alias. The correct behavior is that exhibited by 3.8, and apparently by 3.7.15 and earlier: GROUP BY should prefer the table column over the alias. There were a couple of releases in between that behaved differently. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Group by in sqlite 3.8 works a little differently depending on the spaces at the end
FWIW, PostgreSQL 9.2.4 shows two rows, like sqlite 3.8.0. On Wed, Aug 28, 2013 at 7:57 AM, Max Vlasov wrote: > Hi, > > the following query (notice the space at the end of the 3rd string) > > Create table [TestTable] ([Title] TEXT); > INsert into TestTable (Title) VALUES ('simple text'); > INsert into TestTable (Title) VALUES ('simple text'); > INsert into TestTable (Title) VALUES ('simple text '); > select Trim(Title) as Title, Count(*) as Cnt FROM TestTable Group By Title; > > produces two results > > "simple text""2" > "simple text""1" > > while all previous versions I tried a single one > > "simple text""3" > > Speaking about the correct way, seems like 3.8 is right (technically > the strings are different), but I just wonder why all this time this > seems like existed and never noticed. On the other side, mysql of a > some old version also showed a single result > > Max > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- As of next week, passwords will be entered in Morse code. Maranatha! <>< John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Group by in sqlite 3.8 works a little differently depending on the spaces at the end
Hi, the following query (notice the space at the end of the 3rd string) Create table [TestTable] ([Title] TEXT); INsert into TestTable (Title) VALUES ('simple text'); INsert into TestTable (Title) VALUES ('simple text'); INsert into TestTable (Title) VALUES ('simple text '); select Trim(Title) as Title, Count(*) as Cnt FROM TestTable Group By Title; produces two results "simple text""2" "simple text""1" while all previous versions I tried a single one "simple text""3" Speaking about the correct way, seems like 3.8 is right (technically the strings are different), but I just wonder why all this time this seems like existed and never noticed. On the other side, mysql of a some old version also showed a single result Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users