Re: [sqlite] Group by in sqlite 3.8 works a little differently depending on the spaces at the end

[Max Vlasov]

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

[Igor Tandetnik]

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

[John McKown]

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

[Max Vlasov]

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