Re: [sqlite] Is this a bug?
Oh, Thank you Barry. I am glad it is not a bug. Bug is in my head. Thank you, Roman From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Barry [smith.bar...@gmail.com] 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 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 > 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 sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 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
Re: [sqlite] Is this a bug with expression evaluation?
Tony Papadimitriou wrote: > I really don't know what the standard says, but here are two different > opinions in implementation. > > MySQL example: You know that the "SQL" in "MySQL" is actually the abbreviation of "something quite loose"? ;-) Anyway, it appears even MySQL conforms to SQL-92 subclause 6.12: | 1) If the data type of both operands of a dyadic arithmetic opera- |tor is exact numeric, then the data type of the result is exact |numeric, with precision and scale determined as follows: |[...] |d) The precision and scale of the result of division is | implementation-defined. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
On 12/14/17, 12:08 PM, "sqlite-users on behalf of Simon Slavin" wrote: > Just to remind you that if something is not documented it can change. The > next version of SQLite might decide that 1 / 2 is 0. So don’t write code > that depends on it. I think it already does: sqlite> select 1/2; 0 sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
On 12/14/17, Tony Papadimitriou wrote: > > MySQL example: > mysql> select 1/2; > ++ > | 1/2| > ++ > | 0.5000 | > ++ > 1 row in set (0.13 sec) MySQL is the only database engine that behaves this way. All others do integer arithmetic on integer values. This is probably the reason that MySQL has the separate "DIV" operator for integer division, whereas everybody else makes due with the standard "/" operator. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
On 14 Dec 2017, at 5:03pm, Tony Papadimitriou wrote: > SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has > no affinity. " > It seems that 'no affinity' gets translated to integer affinity, then. Just to remind you that if something is not documented it can change. The next version of SQLite might decide that 1 / 2 is 0. So don’t write code that depends on it. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
-Original Message- From: J. King Someone please correct me if I'm wrong, but I believe it's mandated by the SQL standard that integer division is used when both operands are integers. I really don't know what the standard says, but here are two different opinions in implementation. MySQL example: mysql> select 1/2; ++ | 1/2| ++ | 0.5000 | ++ 1 row in set (0.13 sec) PostgreSQL example: psql=# select 1/2; ?column? -- 0 (1 row) Your synthetic example doesn't use a fixed table, but if it did the easiest solution for you would probably be to define any columns where you need arbitrary precision as REAL rather than INTEGER, and SQLite column >affinity would do the rest. SQLite3 (https://sqlite.org/datatype3.html) -- "Otherwise, an expression has no affinity. " It seems that 'no affinity' gets translated to integer affinity, then. Is there a way to default to float? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
Someone please correct me if I'm wrong, but I believe it's mandated by the SQL standard that integer division is used when both operands are integers. Your synthetic example doesn't use a fixed table, but if it did the easiest solution for you would probably be to define any columns where you need arbitrary precision as REAL rather than INTEGER, and SQLite column affinity would do the rest. Otherwise, yes, I believe you would need to cast. On December 14, 2017 11:36:19 AM EST, Tony Papadimitriou wrote: >I’ve noticed this (very annoying) behavior: > >select column1*(24/100) wrong from (values(100)); > >Removing the parentheses yields the correct result: > >select column1*24/100 correct from (values(100)); > >This obviously behaves like integer math is used and (24/100) gets >truncated to zero. > >If I add a dot to either number (e.g., 24. or 100.) I get the correct >result. >But, with named fields, it’s not as easy as adding a dot: > >select column1*(column2/column3) wrong from (values(100,24,100)); >select column1*column2/column3 correct from (values(100,24,100)); > >So, to get correct answer I have to use a cast for either field? > >select column1*(cast(column2 as float)/column3) correct from >(values(100,24,100)); > >In this example removing the parentheses is a simple solution. >But if the expression was column1*(1+column2/column3) a cast is the >only way? (Hope not!) > >Anyway, if all this happens to be so by design, is there at least some >way to default to float math rather than integer? > >Thanks. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Sent from my Android device with K-9 Mail. Please excuse my brevity. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug with expression evaluation?
I just multiply by 1.0 Select column1*(column2 * 1.0 / column3)... Removing the parentheses only provide the correct results in your example. It's still using integer math, it's just performing the multiply first, as per order of operations. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Tony Papadimitriou Sent: Thursday, December 14, 2017 11:36 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Is this a bug with expression evaluation? I’ve noticed this (very annoying) behavior: select column1*(24/100) wrong from (values(100)); Removing the parentheses yields the correct result: select column1*24/100 correct from (values(100)); This obviously behaves like integer math is used and (24/100) gets truncated to zero. If I add a dot to either number (e.g., 24. or 100.) I get the correct result. But, with named fields, it’s not as easy as adding a dot: select column1*(column2/column3) wrong from (values(100,24,100)); select column1*column2/column3 correct from (values(100,24,100)); So, to get correct answer I have to use a cast for either field? select column1*(cast(column2 as float)/column3) correct from (values(100,24,100)); In this example removing the parentheses is a simple solution. But if the expression was column1*(1+column2/column3) a cast is the only way? (Hope not!) Anyway, if all this happens to be so by design, is there at least some way to default to float math rather than integer? Thanks. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Confidentiality notice: This e-mail is intended solely for use of the individual or entity to which it is addressed and may contain information that is proprietary, privileged, company confidential and/or exempt from disclosure under applicable law. If the reader is not the intended recipient or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply e-mail or collect telephone call and delete or destroy all copies of this e-mail message, any physical copies made of this e-mail message and/or any file attachment(s). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key
Thanks for the link Bernard Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 16 November 2014 16:07, Bernardo Sulzbach wrote: > You are supposing that INT PRIMARY KEY == INTEGER PRIMARY KEY. Which, > clearly, is not true. > http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite > see this link for more on the subject. > > 2014-11-16 13:56 GMT-02:00 Igor Tandetnik : > >> On 11/16/2014 10:51 AM, Paul Sanderson wrote: >> >>> AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY >>> >> >> No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY. >> Which part of the error message do you find unclear? >> >> For details, see http://www.sqlite.org/autoinc.html >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > Bernardo Sulzbach > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key
You are supposing that INT PRIMARY KEY == INTEGER PRIMARY KEY. Which, clearly, is not true. http://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite see this link for more on the subject. 2014-11-16 13:56 GMT-02:00 Igor Tandetnik : > On 11/16/2014 10:51 AM, Paul Sanderson wrote: > >> AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY >> > > No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY. > Which part of the error message do you find unclear? > > For details, see http://www.sqlite.org/autoinc.html > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Bernardo Sulzbach ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key
On 11/16/2014 10:51 AM, Paul Sanderson wrote: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY. Which part of the error message do you find unclear? For details, see http://www.sqlite.org/autoinc.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? Can't tell from docs....
On Fri, Nov 18, 2011 at 05:30:20PM -0800, Yang Zhang scratched on the wall: > I just got bit by some peculiar behavior in sqlite where > > id int primary key > is different from: > id integer primary key > In particular, sqlite will generate values for the latter but not the former: Well, yes... They're different types. "FLOAT" and "FLOATING POINT" have very different behaviors as well (although not for the reasons you might guess). > I couldn't find in http://www.sqlite.org/autoinc.html To quote: "If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. [...] When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine." The docs are quite specific that this is a "column of type..." and not just any column with an integer storage class. The phrase INTEGER PRIMARY KEY is also a link, which takes you to a section of the CREATE TABLE docs that includes this: "Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column..." > or http://www.sqlite.org/datatype3.html any mention of this > peculiardistinguishing behavior. Again, the phrase INTEGER PRIMARY KEY appears on this page as a link to the above information that clearly states the column must be defined as an "INTEGER". You seem to be assuming the defined type "INT" and the type "INTEGER" should be equivalent. > Anyway, if this is intentional (as > I'm guessing), I wouldn't have been able to tell from the docs - > perhaps this would warrant special mention? Almost every use of the phrase "INTEGER PRIMARY KEY" in the documentation is a link that brings you right to this information. It seems like it would be difficult to make this any more accessible without copying the information to several different places on the website, thus bulking up the docs so much people don't bother to read them and making it much more difficult to maintain. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug? Can't tell from docs....
Actually, it is documented, on the CREATE TABLE page, near the bottom, in the section titled "ROWIDs and the INTEGER PRIMARY KEY". Not that this is an exactly obvious place to look for it... Best regards, Peter > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Yang Zhang > Sent: Friday, November 18, 2011 6:30 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Is this a bug? Can't tell from docs > > I just got bit by some peculiar behavior in sqlite where > > id int primary key > is different from: > id integer primary key > In particular, sqlite will generate values for the latter but not the > former: > sqlite> create table a (a integer primary key, b integer);sqlite> > insert into a (b) values (0);sqlite> select * from a;1|0sqlite> create > table b (a int primary key, b integer);sqlite> insert into b (b) > values (0);sqlite> select * from b;|0 > I couldn't find in http://www.sqlite.org/autoinc.html > orhttp://www.sqlite.org/datatype3.html any mention of this > peculiardistinguishing behavior. Anyway, if this is intentional (as > I'm guessing), I wouldn't have been able to tell from the docs - > perhaps this would warrant special mention? Just thought I'd bring > this to your attention. > -- > Yang Zhang > http://yz.mit.edu/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
On Tue, Jun 28, 2011 at 8:50 PM, thilo wrote: > > use SINGLE quotes, not double quotes. > bummer, Thanks a lot > i PROMISE that you won't find such an obvious bug in sqlite3 ;). sqlite uses single quotes because that's what ANSI SQL specifies. MySQL uses (or can use) double quotes, but that is an unportable SQL extension. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
On 6/28/2011 8:45 PM, Stephan Beal wrote: > On Tue, Jun 28, 2011 at 8:42 PM, thilo wrote: > >> sqlite> select (select v from t1 where n="a") wrong,* from a1; >> > > use SINGLE quotes, not double quotes. bummer, Thanks a lot thilo -- Dipl. Ing. Thilo Jeremias Zur Rabenwiese 14 27239 Twistringen T: +49 15782492240 T: +49 4243941633 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
Use single quotes instead of double
sqlite> select (select v from t1 where n='a') wrong,* from a1;
wrong|a|b
2000|123|456
2000|999|999
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of thilo [th...@nispuk.com]
Sent: Tuesday, June 28, 2011 1:42 PM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] Is this a BUG or am I wrong ? sub-select returns nothing
when column id matches the inner query
Hello there!
I found a strange behavior while doing a
select with a sub select that has a where clause with a value (here
'a') which is the same as a column id:
What am I missing here ?
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode column
sqlite> .header on
sqlite> select (select v from t1 where n="a") wrong,* from a1;
wrong a b
-- -- --
123 456
999 999
sqlite>
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a1 (a int,b int);
INSERT INTO "a1" VALUES(123,456);
INSERT INTO "a1" VALUES(999,999);
CREATE TABLE "t1" (n text primary key on conflict replace,v integer);
INSERT INTO "t1" VALUES('good',1000);
INSERT INTO "t1" VALUES('a',2000);
COMMIT;
sqlite>
Any light to the problem (especially if it is already fixed) is very
much appreciated.
My OS is NetBSD,
cheers thilo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
string literals are enclose in single quotes not double quotes" select (select v from t1 where n='a') wrong,* from a1; On 6/28/2011 11:42 AM, thilo wrote: > select (select v from t1 where n="a") wrong,* from a1; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query
On Tue, Jun 28, 2011 at 8:42 PM, thilo wrote: > sqlite> select (select v from t1 where n="a") wrong,* from a1; > use SINGLE quotes, not double quotes. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this a bug?
On Fri, Dec 18, 2009 at 07:23:24PM -0700, Valerio Aimale scratched on the wall: > Hello all, > > I've run into an interesting situation; when duplicating parenthesis > around a 'in ()' subquery, only the first row is returned. > Why only one value returned when parenthesis are duplicated? Wrapping a sub-SELECT in parenthesis turns it into an expression, rather than a result-set. This is done by returning the first value. See the diagram here: http://www.sqlite.org/lang_expr.html In your specific case, the "IN" operator is defined as: IN ( | ) In other words, it allows either a SELECT -or- one or more expressions. If it is a sub-SELECT that has one column, the IN operator is smart enough to consider the returned column to be an expression set. However, when you wrap the sub-SELECT in parenthesis, it becomes a scalar expression of only one value (the first row), so you're IN test-set has only one value, and returns only one row in the super-SELECT. If you're using a sub-SELECT, the IN operator must see it directly. > Same with triple parenthesis enclosing: An expression in parenthesis is still just an expression. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this a bug?
It´s good to try to reproduce all conditions that this problem happens, to help with creating test-cases and with bug fix. []'s -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: sábado, 19 de dezembro de 2009 00:38 To: General Discussion of SQLite Database Subject: Re: [sqlite] is this a bug? On Fri, Dec 18, 2009 at 8:23 PM, Valerio Aimale wrote: > Hello all, > > I've run into an interesting situation; when duplicating parenthesis > around a 'in ()' subquery, only the first row is returned. > > This is not my real-life query, but a test that replicates the problem. > > Thanks, > > Valerio > > $ sqlite3 --version > 3.6.16 > > prepare some dummy data: > > create table test ( id INT ); > insert into test VALUES(1); > insert into test VALUES(2); > insert into test VALUES(3); > insert into test VALUES(4); > insert into test VALUES(5); > insert into test VALUES(6); > insert into test VALUES(7); > insert into test VALUES(8); > insert into test VALUES(9); > insert into test VALUES(10); > > sqlite> select id from test where (id > 5); > 6 > 7 > 8 > 9 > 10 > [Good] > > sqlite> select id from test where id in (select id from test where (id > > 5)); > 6 > 7 > 8 > 9 > 10 > [Still Good] > > Now let's duplicate parenthesis around the subquery: > > sqlite> select id from test where id in ((select id from test where (id > > 5))); > 6 > > Why only one value returned when parenthesis are duplicated? > > Same with triple parenthesis enclosing: > > sqlite> select id from test where id in (((select id from test where (id > > 5; > 6 > Given the above table sqlite> SELECT id FROM test WHERE id IN (6, 7, 8, 9, 10); id -- 6 7 8 9 10 sqlite> SELECT id FROM test WHERE id IN ((6, 7, 8, 9, 10)); SQL error: near ",": syntax error sqlite> Seems like IN expects a comma separated list, and nothing else within a single set of parens. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this a bug?
On Fri, Dec 18, 2009 at 8:23 PM, Valerio Aimale wrote: > Hello all, > > I've run into an interesting situation; when duplicating parenthesis > around a 'in ()' subquery, only the first row is returned. > > This is not my real-life query, but a test that replicates the problem. > > Thanks, > > Valerio > > $ sqlite3 --version > 3.6.16 > > prepare some dummy data: > > create table test ( id INT ); > insert into test VALUES(1); > insert into test VALUES(2); > insert into test VALUES(3); > insert into test VALUES(4); > insert into test VALUES(5); > insert into test VALUES(6); > insert into test VALUES(7); > insert into test VALUES(8); > insert into test VALUES(9); > insert into test VALUES(10); > > sqlite> select id from test where (id > 5); > 6 > 7 > 8 > 9 > 10 > [Good] > > sqlite> select id from test where id in (select id from test where (id > > 5)); > 6 > 7 > 8 > 9 > 10 > [Still Good] > > Now let's duplicate parenthesis around the subquery: > > sqlite> select id from test where id in ((select id from test where (id > > 5))); > 6 > > Why only one value returned when parenthesis are duplicated? > > Same with triple parenthesis enclosing: > > sqlite> select id from test where id in (((select id from test where (id > > 5; > 6 > Given the above table sqlite> SELECT id FROM test WHERE id IN (6, 7, 8, 9, 10); id -- 6 7 8 9 10 sqlite> SELECT id FROM test WHERE id IN ((6, 7, 8, 9, 10)); SQL error: near ",": syntax error sqlite> Seems like IN expects a comma separated list, and nothing else within a single set of parens. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is this a bug?
This is probably a bug in SQL parser, that doesn't reduced to one parenthesis, causing the side effect in VDBE engine, that doesn't returned all ID´s to IN evaluator in first select. This should be easily reproduced, but the fix you must wait for Richard or other that have knowledge on VDBE instruction debugging. I've tested the same thing on SQL Server 2008, correct results appeared: --- create database x go use x go create table test (id int not null, primary key(id)) go insert into test values (1) insert into test values (2) insert into test values (3) insert into test values (4) insert into test values (5) insert into test values (6) insert into test values (7) insert into test values (8) insert into test values (9) insert into test values (10) go select id from test where id in (select id from test where id > 5) go id --- 6 7 8 9 10 (5 row(s) affected) select id from test where id in (select id from test where (id > 5)) go id --- 6 7 8 9 10 (5 row(s) affected) select id from test where id in ((select id from test where (id > 5))) go id --- 6 7 8 9 10 (5 row(s) affected) []'s -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Valerio Aimale Sent: sábado, 19 de dezembro de 2009 00:23 To: sqlite-users@sqlite.org Subject: [sqlite] is this a bug? Hello all, I've run into an interesting situation; when duplicating parenthesis around a 'in ()' subquery, only the first row is returned. This is not my real-life query, but a test that replicates the problem. Thanks, Valerio $ sqlite3 --version 3.6.16 prepare some dummy data: create table test ( id INT ); insert into test VALUES(1); insert into test VALUES(2); insert into test VALUES(3); insert into test VALUES(4); insert into test VALUES(5); insert into test VALUES(6); insert into test VALUES(7); insert into test VALUES(8); insert into test VALUES(9); insert into test VALUES(10); sqlite> select id from test where (id > 5); 6 7 8 9 10 [Good] sqlite> select id from test where id in (select id from test where (id > 5)); 6 7 8 9 10 [Still Good] Now let's duplicate parenthesis around the subquery: sqlite> select id from test where id in ((select id from test where (id > 5))); 6 Why only one value returned when parenthesis are duplicated? Same with triple parenthesis enclosing: sqlite> select id from test where id in (((select id from test where (id > 5; 6 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J. R. Westmoreland Is there a way to get an autoincrement field without being a primary key? Yes, you can create a trigger to do this. (This also allows you great flexibility, since you can, for instance, use an increment other than 1, include a check digit, etc., but it's not "built-in" as the one for primary key can be.) Hope this helps, Donald ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
Is there a way to get an autoincrement field without being a primary key? J. R. Westmoreland E-mail: j...@jrw.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Friday, January 23, 2009 1:01 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is this a bug? J. R. Westmoreland wrote: > I have the following table creation statements and get an error. > > Functioning statement > > CREATE TABLE xxx (ID INTEGERPRIMARY KEY AUTOINCREMENT, .) > > If I remove the PRIMARY KEY" part of the statement, according to the > docs on SQL that should be still a valid statement, I get an error. > > Brken statement: > > CREATE TABLE zzz (ID INTEGER AUTOINCREMENT .); SQLite only supports AUTOINCREMENT together with PRIMARY KEY. See http://sqlite.org/lang_createtable.html > But, if I change "AUTOINCREMENT "AUTO_INCREMENT" it works > > Functioning statement: > > CREATE TABLE zzz (ID INTEGER AUTO_INCREMENT .); AUTO_INCREMENT has no special meaning in the syntax. It works for the same reason this works: CREATE TABLE zzz (ID WHATEVER I WANT); Neither makes ID an autoincrement field. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
J. R. Westmoreland wrote: > I have the following table creation statements and get an error. > > Functioning statement > > CREATE TABLE xxx (ID INTEGERPRIMARY KEY AUTOINCREMENT, .) > > If I remove the PRIMARY KEY" part of the statement, according to the > docs on SQL that should be still a valid statement, I get an error. > > Brken statement: > > CREATE TABLE zzz (ID INTEGER AUTOINCREMENT .); SQLite only supports AUTOINCREMENT together with PRIMARY KEY. See http://sqlite.org/lang_createtable.html > But, if I change "AUTOINCREMENT "AUTO_INCREMENT" it works > > Functioning statement: > > CREATE TABLE zzz (ID INTEGER AUTO_INCREMENT .); AUTO_INCREMENT has no special meaning in the syntax. It works for the same reason this works: CREATE TABLE zzz (ID WHATEVER I WANT); Neither makes ID an autoincrement field. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
On Fri, Jan 23, 2009 at 12:51:07PM -0700, J. R. Westmoreland scratched on the wall: > I have the following table creation statements and get an error. > > Functioning statement > CREATE TABLE xxx (ID INTEGERPRIMARY KEY AUTOINCREMENT, .) > If I remove the PRIMARY KEY" part of the statement, according to the docs on > SQL that should be still a valid statement, I get an error. AUTOINCREMENT is only a valid modifier to INTEGER PRIMARY KEY. > Brken statement: > CREATE TABLE zzz (ID INTEGER AUTOINCREMENT .); > > But, if I change "AUTOINCREMENT "AUTO_INCREMENT" it works Yes, but it doesn't mean anything. You've defined a user-type of "INTEGER AUTO_INCREMENT", which is about as meaningful to SQLite as "INTEGER PINEAPPLE" (also valid). http://sqlite.org/autoinc.html Also, see the syntax diagram here: http://sqlite.org/syntaxdiagrams.html#column-constraint -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
I am not sure about the email server. You're probably correct.
I spent couple of painful hours on this issue. I just moved the error printf
after finalize now.
Thanks Roger!
-Alex
On Fri, Jul 4, 2008 at 7:03 PM, Roger Binns <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Alex Katebi wrote:
> > I was able to get the attachment myself. It could be your email server.
>
> Are you sure? I was looking at the actual raw message. My mail server
> is postfix with messages funneled through clamassassin and spamassassin.
> It could be the SQLite mailing list manager that stripped out the
> attachment when adding the list trailer part.
>
> > sqlite3_step(pStmt);
> > printf("%s\n", sqlite3_errmsg(db));
>
> If you add sqlite3_reset(pStmt) or sqlite3_finalize(pStmt) after the
> step then you get the correct error message. Also note that you should
> use sqlite3_prepare_v2.
>
> In the "olden days" sqlite3_step() used to only return SQLITE_ERROR and
> then you had to call reset or finalize to get the actual error code and
> message.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIbqw6mOOfHg372QQRAmeFAKChAns1ELMHkCNdlAoBajWmQE4MnQCeJE9V
> qbl2fYgpqCwcbeCe1WAvEKU=
> =608O
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Alex Katebi wrote:
> I was able to get the attachment myself. It could be your email server.
Are you sure? I was looking at the actual raw message. My mail server
is postfix with messages funneled through clamassassin and spamassassin.
It could be the SQLite mailing list manager that stripped out the
attachment when adding the list trailer part.
> sqlite3_step(pStmt);
> printf("%s\n", sqlite3_errmsg(db));
If you add sqlite3_reset(pStmt) or sqlite3_finalize(pStmt) after the
step then you get the correct error message. Also note that you should
use sqlite3_prepare_v2.
In the "olden days" sqlite3_step() used to only return SQLITE_ERROR and
then you had to call reset or finalize to get the actual error code and
message.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFIbqw6mOOfHg372QQRAmeFAKChAns1ELMHkCNdlAoBajWmQE4MnQCeJE9V
qbl2fYgpqCwcbeCe1WAvEKU=
=608O
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
I was able to get the attachment myself. It could be your email server.
Below is the the code.
Thanks,
-Alex
#include
void bitwise_and(sqlite3_context* pContext, int N, sqlite3_value** ppValue)
{
int dest_bytes = sqlite3_value_bytes(ppValue[0]);
int mask_bytes = sqlite3_value_bytes(ppValue[1]);
if(dest_bytes != mask_bytes)
{
sqlite3_result_error(pContext, "unequal blob lengths", -1);
return;
}
char* dest = (char*) sqlite3_value_blob(ppValue[0]);
char* mask = (char*) sqlite3_value_blob(ppValue[1]);
char* result = sqlite3_malloc(dest_bytes);
int i; for(i = 0 ; i < dest_bytes ; i++) result[i] = dest[i] & mask[i];
sqlite3_result_blob(pContext, result, dest_bytes, SQLITE_TRANSIENT);
sqlite3_free(result);
}
int main()
{
sqlite3* db;
sqlite3_open(":memory:", &db);
sqlite3_create_function(db, "bitand", 2, SQLITE_UTF8, 0, bitwise_and, 0,
0);
char* errmsg;
char* zSql = "select bitand(x'01',x'0101');";
int rc = sqlite3_exec(db, zSql, 0, 0, &errmsg);
printf("%s\n", errmsg);
sqlite3_stmt* pStmt;
sqlite3_prepare(db,zSql, -1, &pStmt, 0);
printf("%s\n", sqlite3_errmsg(db));
sqlite3_step(pStmt);
printf("%s\n", sqlite3_errmsg(db));
sqlite3_finalize(pStmt);
sqlite3_close(db);
}
On Fri, Jul 4, 2008 at 4:49 PM, Roger Binns <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Alex Katebi wrote:
> > How about this one?
>
> Still no attachment. It is a mime message with two parts. The first is
> your message and the second is a mailing list information trailer.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIboznmOOfHg372QQRAr6ZAJ9cEfod2s7rOwzg9cKXXxyJFxfymACfW6OB
> NLZdqmlmDRWb6IMXvaoOgds=
> =PaSo
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alex Katebi wrote: > How about this one? Still no attachment. It is a mime message with two parts. The first is your message and the second is a mailing list information trailer. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIboznmOOfHg372QQRAr6ZAJ9cEfod2s7rOwzg9cKXXxyJFxfymACfW6OB NLZdqmlmDRWb6IMXvaoOgds= =PaSo -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
How about this one? On Thu, Jul 3, 2008 at 9:39 PM, Roger Binns <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Alex Katebi wrote: > > sqlite3_errmsg(db) does not provide the correct error message but > > sqlite_exec(...) does. > > The message gets cleared on various calls so you'll want to get it as > early as possible after knowing there is an error. For example before > 3.5.9 sqlite3_clear_bindings used to clear the message. > > > I have attached my test code. > > It isn't in the message sent to list members :-) > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFIbX9JmOOfHg372QQRAnT+AJ9+y5+H+4w8R3SG8nG4vOnZTPb/9gCfWfSA > kmDrsFz0u516u1b+QF7pDHA= > =EJN7 > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a bug?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alex Katebi wrote: > sqlite3_errmsg(db) does not provide the correct error message but > sqlite_exec(...) does. The message gets cleared on various calls so you'll want to get it as early as possible after knowing there is an error. For example before 3.5.9 sqlite3_clear_bindings used to clear the message. > I have attached my test code. It isn't in the message sent to list members :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIbX9JmOOfHg372QQRAnT+AJ9+y5+H+4w8R3SG8nG4vOnZTPb/9gCfWfSA kmDrsFz0u516u1b+QF7pDHA= =EJN7 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
