Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-02-06 Thread famat 
Federal University Oye Ekiti conduct Third Matriculation this February visit
http://www.fuoye.edu.ng for more details 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/ORDER-BY-issue-v3-8-2-amalgamtion-tp73605p73704.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-02-03 Thread Labar, Ken 
Hello Simon, Clemens, and Richard,

Thank you for your help.


> Simon: "What are you seeing from your SELECT that you weren't expecting ?"
0 | Batt
0 | ClockBatt
0 | Batt
0 | BP
0 | ORP
0 | Ref
0 | pH
0 | pH
0 | DO
...


> Simon: "Can you reduce your INSERTs to just two rows, and still get results 
> you consider to be incorrect from the SELECT ?"
Yes, I reduced the table to 2 rows by:
DELETE FROM userparameter WHERE hepi_parameter_id !=32961 AND hepi_parameter_id 
!= 32881;

select (rtTableSort < 1000) as selected, abbrString from userParameter order by 
abbrString;

results:

0 | ORP

0 | DO


> DRH: "Can you try recompiling with all compiler optimizations turned off and 
> see if you still get the error?"

I have turned off all optimizations (FYI: only C++ optimizations were on before)


> DRH: ", can you compile with -DSQLITE_DEBUG then run the query *after* first 
> running "PRAGMA vdbe_debug=ON" and then send us the output?"
I have been struggling to enable SQLITE_DEBUG with our uCOS-II VFS port.
(namely our code does not have stdout or printf as compiled now. I love 
embedded;-) I'm working on a solution for debugging, more soon).


> Clemens: "What is the EXPLAIN QUERY PLAN output for this query on the 
> handheld?"
0|0|0|SCAN TABLE userParameter
0|0|0|USE TEMP B-TREE FOR ORDER BY

Thank you,
Ken

ken labar | Embedded Firmware Engineer
Hach Hydromet | www.hachhydromet.com | 
kla...@hach.com

Innovating technology behind better data




Please be advised that this email may contain confidential 
information.  If you are not the intended recipient, please notify us 
by email by replying to the sender and delete this message.  The 
sender disclaims that the content of this email constitutes an offer 
to enter into, or the acceptance of, any agreement; provided that the 
foregoing does not invalidate the binding effect of any digital or 
other electronic reproduction of a manual signature that is included 
in any attachment.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-02-03 Thread Richard Hipp 
On Sun, Feb 2, 2014 at 11:58 PM, Labar, Ken  wrote:
>
>
>
> This used to work until we upgraded sqlite to v3.8.2. It still does work
at the PC level.

And earlier:


> -  IAR C compiler

Can you try recompiling with all compiler optimizations turned off and see
if you still get the error?

If that fails to resolve the issue, can you compile with -DSQLITE_DEBUG
then run the query *after* first running "PRAGMA vdbe_debug=ON" and then
send us the output?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-02-03 Thread Clemens Ladisch 
Labar, Ken wrote:
> [...]
> select (rtTableSort < 1000) as selected, abbrString from userParameter order 
> by abbrString;
>
> This used to work until we upgraded sqlite to v3.8.2. It still does work at 
> the PC level.

What is the EXPLAIN QUERY PLAN output for this query on the handheld?

When it works, it uses the index on the sort column to ensure that records
are returned in the correct order:
0|0|0|SCAN TABLE userParameter USING INDEX idxAbbrString


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-02-03 Thread Simon Slavin 

On 3 Feb 2014, at 4:58am, Labar, Ken  wrote:

> select (rtTableSort < 1000) as selected, abbrString from userParameter order 
> by abbrString;
>  
>  
> This used to work until we upgraded sqlite to v3.8.2. It still does work at 
> the PC level.

Thanks for test data and SELECT command, which allow us to test our own 
installations.

What are you seeing from your SELECT that you weren't expecting ?

Can you reduce your INSERTs to just two rows, and still get results you 
consider to be incorrect from the SELECT ?  If so, which two INSERTs are shown 
in the wrong order ?  If not, at what point does the problem go away ?

> I’m wondering if we are short on RAM, and the sort engine is failing quietly, 
> returning the un-sorted query with SQLITE_OK.

If SQLite is doing that, then there is a serious bug in it which will be 
corrected very quickly.  SQLite is not designed to fail 'quietly'.  In the 
unlikely event that it runs out of a resource it should be returning an error.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-02-02 Thread Labar, Ken 
Hello Simon,

Below is the query, and the schema I’m trying to debug.
FYI: I’m signed up for the daily digest, so please CC 
kla...@hach.com on replies for a faster response.

CREATE TABLE userParameter (
hepi_parameter_id INT NOT NULL
,hepi_category INT NOT NULL
,hepi_number INT NOT NULL
,preferredUnit INT
,rtTableSort INT
,rtGraphSort INT
,rtGridSort INT
,dpTableSort INT
,dpGraphSort INT
,dpGridSort INT
,name_deviceString_id INT
,abbr_deviceString_id INT
,units_deviceString_id INT
,nameString TEXT COLLATE BINARY
,abbrString TEXT COLLATE BINARY
,unitsString TEXT
);

CREATE INDEX idxHepi_parameter_id ON userParameter (hepi_parameter_id);
CREATE INDEX idxRtTableSort ON userParameter (rtTableSort);
CREATE INDEX idxDpTableSort ON userParameter (dpTableSort);
CREATE INDEX idxNameString ON userParameter (nameString COLLATE BINARY);
CREATE INDEX idxAbbrString ON userParameter (abbrString COLLATE BINARY);

INSERT INTO "userParameter" 
VALUES(32785,1,1,1,0,1000,1000,1000,1000,1000,2,24,127,'Temperature','Temp','°C');
INSERT INTO "userParameter" 
VALUES(32786,1,2,1,1000,1000,1000,1000,1000,1000,2,24,128,'Temperature','Temp','°F');
INSERT INTO "userParameter" 
VALUES(32787,1,3,1,1000,1000,1000,1000,1000,1000,2,24,129,'Temperature','Temp','°K');
INSERT INTO "userParameter" 
VALUES(32817,3,1,1,1000,1000,1000,1000,1000,1000,138,125,139,'Resistivity','Res','kΩ-cm');
INSERT INTO "userParameter" 
VALUES(32833,4,1,1,1000,1000,1000,1000,1000,1000,140,141,137,'Specific 
Conductivity','SpCond','mS/cm');
INSERT INTO "userParameter" 
VALUES(32834,4,2,1,1000,1000,1000,1000,1000,1000,140,141,142,'Specific 
Conductivity','SpCond','µS/cm');
INSERT INTO "userParameter" 
VALUES(32849,5,1,1,1000,1000,1000,1000,1000,1000,143,144,145,'Salinity','Sal','psu');
INSERT INTO "userParameter" 
VALUES(32850,5,2,1,1000,1000,1000,1000,1000,1000,143,144,146,'Salinity','Sal','ppt');
INSERT INTO "userParameter" 
VALUES(32865,6,1,1,1000,1000,1000,1000,1000,1000,147,148,149,'Total Dissolved 
Solids','TDS','g/L');
INSERT INTO "userParameter" 
VALUES(32881,7,1,1,1000,1000,1000,1000,1000,1000,203,203,204,'DO','DO','%SAT');
INSERT INTO "userParameter" 
VALUES(32897,8,1,1,1000,1000,1000,1000,1000,1000,203,203,196,'DO','DO','mg/L');
INSERT INTO "userParameter" 
VALUES(32929,10,1,1,1000,1000,1000,1000,1000,1000,6,6,213,'pH','pH','units');
INSERT INTO "userParameter" 
VALUES(32945,11,1,1,1000,1000,1000,1000,1000,1000,6,6,214,'pH','pH','mV');
INSERT INTO "userParameter" 
VALUES(33249,30,1,1,1000,1000,1000,1000,1000,1000,223,224,214,'Reference','Ref','mV');
INSERT INTO "userParameter" 
VALUES(32961,12,1,1,1000,1000,1000,1000,1000,1000,7,7,214,'ORP','ORP','mV');
INSERT INTO "userParameter" 
VALUES(32977,13,1,1,1000,1000,1000,1000,1000,1000,8,8,240,'Depth','Depth','meters');
INSERT INTO "userParameter" 
VALUES(32978,13,2,1,1000,1000,1000,1000,1000,1000,8,8,241,'Depth','Depth','feet');
INSERT INTO "userParameter" 
VALUES(33377,38,1,1,1000,1000,1000,1000,1000,1000,794,794,242,'Pressure','Pressure','psig');
INSERT INTO "userParameter" 
VALUES(33378,38,2,1,1000,1000,1000,1000,1000,1000,794,794,250,'Pressure','Pressure','psia');
INSERT INTO "userParameter" 
VALUES(32993,14,1,1,1000,1000,1000,1000,1000,1000,257,257,259,'TDG','TDG','mmHg');
INSERT INTO "userParameter" 
VALUES(32994,14,2,1,1000,1000,1000,1000,1000,1000,257,257,250,'TDG','TDG','psia');
INSERT INTO "userParameter" 
VALUES(33009,15,1,1,1000,1000,1000,1000,1000,1000,10,268,272,'Turbidity','Turb','NTU');
INSERT INTO "userParameter" 
VALUES(33010,15,2,1,1000,1000,1000,1000,1000,1000,10,268,273,'Turbidity','Turb','FNU');
INSERT INTO "userParameter" 
VALUES(33025,16,1,1,1000,1000,1000,1000,1000,1000,287,288,294,'Chlorophyll 
a','CHL','µg/L');
INSERT INTO "userParameter" 
VALUES(33041,17,1,1,1000,1000,1000,1000,1000,1000,287,288,245,'Chlorophyll 
a','CHL','V');
INSERT INTO "userParameter" 
VALUES(32913,9,1,1,1000,1000,1000,1000,1000,1000,14,299,259,'Barometric 
Pressure','BP','mmHg');
INSERT INTO "userParameter" 
VALUES(33057,18,1,1,1000,1000,1000,1000,1000,1000,331,332,334,'Rhodamine 
WT','RWT','ppb');
INSERT INTO "userParameter" 
VALUES(33073,19,1,1,1000,1000,1000,1000,1000,1000,331,332,245,'Rhodamine 
WT','RWT','V');
INSERT INTO "userParameter" 
VALUES(33089,20,1,1,1000,1000,1000,1000,1000,1000,343,344,345,'Phycocyanin','PCY','cells/mL');
INSERT INTO "userParameter" 
VALUES(33105,21,1,1,1000,1000,1000,1000,1000,1000,343,344,245,'Phycocyanin','PCY','V');
INSERT INTO "userParameter" 
VALUES(33121,22,1,1,1000,1000,1000,1000,1000,1000,352,353,345,'Phycoerythrin','PRY','cells/mL');
INSERT INTO "userParameter" 
VALUES(33137,23,1,1,1000,1000,1000,1000,1000,1000,352,353,245,'Phycoerythrin','PRY','V');
INSERT INTO

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

2014-02-01 Thread Simon Slavin 

On 1 Feb 2014, at 3:53am, Labar, Ken  wrote:

> Upgrading  from  3.7.14.1 to 3.8.2 our previously sorted queries are no 
> longer sorted.
> Testing this same database with the win7 binary 3.8.2 the query is correctly 
> sorted.

Does your query include an ORDER BY clause ?  If not, then you cannot rely on 
sorted results.  Even if the query returns sorted results during testing, 
changes in the data that may happen at your client's site (running ANALYZE, 
more rows, rows inserted in a different order, different version of your 
operating system or disk operating system) may mean that one of your customer 
units starts returning differently sorted results for no apparent reason.

Your fix is to have an appropriate ORDER BY clauses in each SELECT.  This will 
guarantee correct results in all future versions of SQLite no matter what 
hardware and OS you're using.

On the other hand, if you have a query that contains an ORDER BY clause and 
your results are not in that order, please post your schema and the query, 
since that suggests a serious bug in SQLite and we'd love to know about it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users