Re: [sqlite] Query Problem when Executed from PHP v5.2.9-2
try : $strQuery ="SELECT CASE WHEN substr(substr(eTimeStart,1,2),-1) =\':\' THEN substr(eTimeStart,1,1)||substr(etimeStart,3,2) ELSE substr(eTimeStart,1,2)||substr(eTimeStart,4,2) END as aTIME FROM EVENTS WHERE Cast(eMonth as int)= 2 AND CAST(eYear as INT)=2010 and CAST(eDay as INT)=13 ORDER BY substr(eTimeStart,length(etimeStart),-2), CAST(aTIME as INTEGER)"; --- El mar 9-feb-10, Rush <rushaus...@gmail.com> escribió: > De: Rush <rushaus...@gmail.com> > Asunto: [sqlite] Query Problem when Executed from PHP v5.2.9-2 > Para: sqlite-users@sqlite.org > Fecha: martes, 9 de febrero de 2010, 6:24 pm > I have a query that I can > successfully execute from a Windows command > prompt, and from the FireFox SQLite Manager (v0.5.9) > > SELECT > CASE WHEN substr(substr(eTimeStart,1,2),-1) =':' > THEN substr(eTimeStart,1,1)||substr(etimeStart,3,2) > ELSE substr(eTimeStart,1,2)||substr(eTimeStart,4,2) > END as aTIME > FROM EVENTS > WHERE Cast(eMonth as int)= 2 AND CAST(eYear as > INT)=2010 and CAST(eDay as > INT)=13 > ORDER BY substr(eTimeStart,length(etimeStart),-2), > CAST(aTIME as INTEGER) > > > (eTimeStart is a TEXT field with values like 7:30 am and > 10:15 pm) > > This runs flawlessly from the sqlite3 command prompt and > from the Firefox > SQLite Manager. > > All of my other Selects, Updates, Creates, Deletes run fine > from PHP, but I > cannot get this query to execute. > > Here's the exact line from the PHP code that sets the query > string: > > $strQuery ="SELECT CASE WHEN > substr(substr(eTimeStart,1,2),-1) =':' THEN > substr(eTimeStart,1,1)||substr(etimeStart,3,2) ELSE > substr(eTimeStart,1,2)||substr(eTimeStart,4,2) END as aTIME > FROM EVENTS > WHERE Cast(eMonth as int)= 2 AND CAST(eYear as > INT)=2010 and CAST(eDay as > INT)=13 ORDER BY substr(eTimeStart,length(etimeStart),-2), > CAST(aTIME as > INTEGER)"; > > Thanks in advance for any assistance. > > Rush A. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Yahoo! Cocina Encontra las mejores recetas con Yahoo! Cocina. http://ar.mujer.yahoo.com/cocina/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query Problem when Executed from PHP v5.2.9-2
I have a query that I can successfully execute from a Windows command prompt, and from the FireFox SQLite Manager (v0.5.9) SELECT CASE WHEN substr(substr(eTimeStart,1,2),-1) =':' THEN substr(eTimeStart,1,1)||substr(etimeStart,3,2) ELSE substr(eTimeStart,1,2)||substr(eTimeStart,4,2) END as aTIME FROM EVENTS WHERE Cast(eMonth as int)= 2 AND CAST(eYear as INT)=2010 and CAST(eDay as INT)=13 ORDER BY substr(eTimeStart,length(etimeStart),-2), CAST(aTIME as INTEGER) (eTimeStart is a TEXT field with values like 7:30 am and 10:15 pm) This runs flawlessly from the sqlite3 command prompt and from the Firefox SQLite Manager. All of my other Selects, Updates, Creates, Deletes run fine from PHP, but I cannot get this query to execute. Here's the exact line from the PHP code that sets the query string: $strQuery ="SELECT CASE WHEN substr(substr(eTimeStart,1,2),-1) =':' THEN substr(eTimeStart,1,1)||substr(etimeStart,3,2) ELSE substr(eTimeStart,1,2)||substr(eTimeStart,4,2) END as aTIME FROM EVENTS WHERE Cast(eMonth as int)= 2 AND CAST(eYear as INT)=2010 and CAST(eDay as INT)=13 ORDER BY substr(eTimeStart,length(etimeStart),-2), CAST(aTIME as INTEGER)"; Thanks in advance for any assistance. Rush A. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query problem
I am really thankful to all of you who replied my query and helped solve my problem. Again thank you very much. Best Regards, Yasir Nisar - Looking for last minute shopping deals? Find them fast with Yahoo! Search.
Re: [sqlite] Query problem
On Wed, Jan 23, 2008 at 10:24:53PM -0800, Scott Hess wrote: > Seems to me that GLOB is a poor substitute for REGEXP. At the shell If, as I suspect, many more users can enter simple globs than can enter simple regexps, then providing a GLOB operator and function in SQLite is very useful indeed. Of course, regexps are very useful as well, particularly for "power" users, so it's good that SQLite offers both: it makes development of applications that offer either, or both glob and regexp search options, much easier. IMO the regexp facility should get more attention than the glob facility, but both should be present. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
On Wed, Jan 23, 2008 at 3:23 PM, James Dennett <[EMAIL PROTECTED]> wrote: > The POSIX/Single Unix Spec documentation for fnmatch might be a good > source, but I agree with the idea that SQLite should just document what > it does rather than assuming that there's a universal standard for > globbing. Seems to me that GLOB is a poor substitute for REGEXP. At the shell level, I think an argument can be made that it is more concise, but I don't think that really holds for this use. Rather than extending GLOB towards a full REGEXP implementation, maybe it would be more reasonable to find (or write) a REGEXP implementation which was slight enough to be bundled into the SQLite core? No, I'm not volunteering :-). I think it would be pretty reasonable for SQLite to strictly match the TCL glob command's operation, for obvious reasons. I'd probably miss not being able to say [^x], but, *shrug*, what are you going to do? I could also see matching the glob provided by some other popular scripting language which might have SQLite embedded, say Python or Perl. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
James Dennett wrote: The POSIX/Single Unix Spec documentation for fnmatch might be a good source, but I agree with the idea that SQLite should just document what it does rather than assuming that there's a universal standard for globbing. I think the most direct documentation I have found so far is from the Linux glob man page at http://www.kernel.org/doc/man-pages/online/pages/man7/glob.7.html Linux uses ! for inversion only (so SQLite would still have to add that and continue to support the ^ inversion character for backwards compatability) and a backslash to escape the special characters where needed. It also specifically states that the string between the square brackets can't be empty, so an error should be warranted in this case. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Query problem
> -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 23, 2008 3:08 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Query problem > > James Dennett wrote: > > > > Square brackets don't "escape" thing that way: [[] is a character class > > containing only the character '['. [][], however, is a character class > > containing two characters. The special rule is that the first character > > after the opening '[' is part of the class even if it's a ']' or a '-'. > > > > > James, > > I don't think it is that simple. > > What happens if, as in the OP, the character set is simply []? Is this > an empty character set, or is a set containing a ] but missing the > terminal ]? That's not a valid specification of a character class. > According to your special rule above, it would be the latter, in which > case it should generate some kind of error message reporting the > unterminated character set. That would be appropriate; the alternative (which bash appears to use) is to take it as two literal characters. > If you say it is supposed to be greedy and include all characters it can > until the terminal ] before examining the set of characters it contains, > then this would be an empty character set. No, because there *is* no terminal ]. (The first character after the opening '[' is *never* the end of the character class: that's exactly the special rule.) > What does an empty set match? Nothing, but you'd have to specify exclusion of every character. > Normally, a set matches any of the contained characters, but an empty > set can't match any character, so any pattern containing the empty set > would always fail. So, is an empty set a special case, that matches the > literal characters [] instead? That's not an empty character class; it's not a character class at all. The POSIX/Single Unix Spec documentation for fnmatch might be a good source, but I agree with the idea that SQLite should just document what it does rather than assuming that there's a universal standard for globbing. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
James Dennett wrote: Square brackets don't "escape" thing that way: [[] is a character class containing only the character '['. [][], however, is a character class containing two characters. The special rule is that the first character after the opening '[' is part of the class even if it's a ']' or a '-'. James, I don't think it is that simple. What happens if, as in the OP, the character set is simply []? Is this an empty character set, or is a set containing a ] but missing the terminal ]? According to your special rule above, it would be the latter, in which case it should generate some kind of error message reporting the unterminated character set. If you say it is supposed to be greedy and include all characters it can until the terminal ] before examining the set of characters it contains, then this would be an empty character set. What does an empty set match? Normally, a set matches any of the contained characters, but an empty set can't match any character, so any pattern containing the empty set would always fail. So, is an empty set a special case, that matches the literal characters [] instead? None of this is really clear and unambiguous from any of the documentation I have seen so far. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
[EMAIL PROTECTED] wrote:
Experiments using bash indicate that either ^ or ! is accepted
as the negation of a character set. Hence,
ls -d [^tu]*
ls -d [!tu]*
both return the same thing - a list of all files and directories
in the current directory whose names do not begin with "t" or "u".
SQLite only supports ^, not !. I wonder if this is something I
should change? It would not be much trouble to get GLOB to support
both, must like the globber in bash.
Anybody have an old Bourne shell around? An authentic C-shell?
What do they do?
Richard,
I found the following info in a Jedit appendix.
| |
*
|?| matches any one character
*
|*| matches any number of characters
*
|{!/|glob|/}| Matches anything that does /not/ match /|glob|/
*
|{/|a|/,/|b|/,/|c|/}| matches any one of /|a|/, /|b|/ or /|c|/
*
|[/|abc|/]| matches any character in the set /|a|/, /|b|/ or /|c|/
*
|[^/|abc|/]| matches any character not in the set /|a|/, /|b|/
or /|c|/
*
|[/|a-z|/]| matches any character in the range /|a|/ to /|z|/,
inclusive. A leading or trailing dash will be interpreted literally
I noticed that SQLite doesn't implement any of the curly brace grouping
of globs. It also shows the use of ^ for inversion with a character set,
and ! for inversion of a complete glob.
The following is from the TCL documentation:
The /pattern/ arguments may contain any of the following special
characters:
*?*
Matches any single character.
***
Matches any sequence of zero or more characters.
*[*/chars/*]*
Matches any single character in /chars/. If /chars/ contains a
sequence of the form /a/*-*/b/ then any character between /a/ and
/b/ (inclusive) will match.
*\*/x/
Matches the character /x/.
*{*/a/*,*/b/*,*/.../}
Matches any of the strings /a/, /b/, etc.
This doesn't mention inversion at all, but it does say a backslash can
be used to escape a character.
And the following is from a the documentation of a glob compiler class.
* *** - Matches zero or more instances of any character. If the
STAR_CANNOT_MATCH_NULL_MASK option is used, *** matches one or
more instances of any character.
* *?* - Matches one instance of any character. If the
QUESTION_MATCHES_ZERO_OR_ONE_MASK option is used, *?* matches
zero or one instances of any character.
* *[...]* - Matches any of characters enclosed by the brackets. *
* * and *?* lose their special meanings within a character
class. Additionaly if the first character following the opening
bracket is a *!* or a *^*, then any character not in the
character class is matched. A *-* between two characters can be
used to denote a range. A *-* at the beginning or end of the
character class matches itself rather than referring to a range.
A *]* immediately following the opening *[* matches itself
rather than indicating the end of the character class, otherwise
it must be escaped with a backslash to refer to itself.
* *\* - A backslash matches itself in most situations. But when a
special character such as a *** follows it, a backslash /
escapes / the character, indicating that the special chracter
should be interpreted as a normal character instead of its
special meaning.
* All other characters match themselves.
This class explicitly mentions using either ^ or ! to invert a character
set. It also allows backslash escapes for special characters. It says *
and ? loose their special status in a character set, so it isn't really
an escape.
The following is from the Apple's documentation
*?* Matches any single character.
*** Matches any sequence of zero or more characters.
*[*_chars_*]* Matches any single character in _chars_. If _chars_
contains a
sequence of the form _a_*-*_b_ then any character between
_a_ and _b_
(inclusive) will match.
*\*_x_Matches the character _x_.
*{*_a_*,*_b_*,*_..._} Matches any of the strings _a_, _b_, etc.
And finally, from the GNU bash documentation:
3.5.8.1 Pattern Matching
Any character that appears in a pattern, other than the special
pattern characters described below, matches itself. The nul character
may not occur in a pattern. A backslash escapes the following
character; the escaping backslash is discarded when matching. The
special pattern characters must be quoted if they are to be matched
literally.
The special pattern characters have the following meanings:
|*|
Matches any string, including the null string.
|?|
Matches any single character.
|[...]|
Matches any one of the enclosed characters. A pair of characters
separated by a hyphen denotes a range expression; any character
that sorts between those two characters, inclusive, using the
current
RE: [sqlite] Query problem
> -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 23, 2008 2:22 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Query problem > > [EMAIL PROTECTED] wrote: > > > > You > > can escape characters using [..]. To match a * anywhere in a string, > > for example: > > > > x GLOB '*[*]*' > > > > The [..] pattern must contain at least one internal character. So > > to match a "]" you can use the pattern > > > > x GLOB '*[]]*' > > > So to match the OP's original string he would need to use > '*1[[][]]1.txt' as his pattern? > > With each of the square brackets to be matched escaped by a pair of > enclosing square brackets. Square brackets don't "escape" thing that way: [[] is a character class containing only the character '['. [][], however, is a character class containing two characters. The special rule is that the first character after the opening '[' is part of the class even if it's a ']' or a '-'. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: This appears to be slightly different than normal *nix globbing since SQLite uses '^' rather than '!' for the set inversion (if my reading of the source is correct). GLOB is suppose to exactly mimic Unix, except that SQLite does not break pattern matching at / boundaries the way the shell does. So if the previous statement is true, it is a bug. Experiments using bash indicate that either ^ or ! is accepted as the negation of a character set. Hence, ls -d [^tu]* ls -d [!tu]* both return the same thing - a list of all files and directories in the current directory whose names do not begin with "t" or "u". SQLite only supports ^, not !. I wonder if this is something I should change? It would not be much trouble to get GLOB to support both, must like the globber in bash. Anybody have an old Bourne shell around? An authentic C-shell? What do they do? -- D. Richard Hipp <[EMAIL PROTECTED]> Both Korn and Bourne shells behave differently for the examples but in the same way. ls -d [^tu]* behaves like your example ls -d [!tu]* lists all directories. C Shell does not recognize the 2nd example. First one behaves as above. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
[EMAIL PROTECTED] wrote: You can escape characters using [..]. To match a * anywhere in a string, for example: x GLOB '*[*]*' The [..] pattern must contain at least one internal character. So to match a "]" you can use the pattern x GLOB '*[]]*' So to match the OP's original string he would need to use '*1[[][]]1.txt' as his pattern? With each of the square brackets to be matched escaped by a pair of enclosing square brackets. I haven't had much luck finding a detailed spec for the GLOB pattern language. Most descriptions only show the basics. Does anyone know of a good reference? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Query problem
DRH wrote: > > Experiments using bash indicate that either ^ or ! is accepted > as the negation of a character set. Hence, > > ls -d [^tu]* > ls -d [!tu]* > > both return the same thing - a list of all files and directories > in the current directory whose names do not begin with "t" or "u". > > SQLite only supports ^, not !. I wonder if this is something I > should change? It would not be much trouble to get GLOB to support > both, must like the globber in bash. > > Anybody have an old Bourne shell around? An authentic C-shell? > What do they do? C shell on Solaris 9 gives an error on echo [!c]* as it considers the !c to be an event specification. Tcsh the same. Ksh treats echo [^c]* the same as echo c* but does "the right thing" with echo [!c]* bash treats the two the same (as all names starting with a character other than lower-case 'c'). -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
[EMAIL PROTECTED] wrote: > > > This appears to be slightly different than normal *nix globbing since > > SQLite uses '^' rather than '!' for the set inversion (if my reading of > > the source is correct). > > GLOB is suppose to exactly mimic Unix, except that SQLite does not > break pattern matching at / boundaries the way the shell does. > So if the previous statement is true, it is a bug. > Experiments using bash indicate that either ^ or ! is accepted as the negation of a character set. Hence, ls -d [^tu]* ls -d [!tu]* both return the same thing - a list of all files and directories in the current directory whose names do not begin with "t" or "u". SQLite only supports ^, not !. I wonder if this is something I should change? It would not be much trouble to get GLOB to support both, must like the globber in bash. Anybody have an old Bourne shell around? An authentic C-shell? What do they do? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
Dennis Cote <[EMAIL PROTECTED]> wrote: >> > SQLite seems to do the following: > > The glob syntax supports the following patterns: > ? - matches any single character > * - matches zero or more characters > [seq] - matches any single character in seq > [!seq] - matches any single character not in seq > > seq is one or more characters, such as abc. You may specify character > ranges using a dash. For example, a-z0-9 specifies all of the characters > in the English alphabet and the decimal digits 0 through 9. > > This appears to be slightly different than normal *nix globbing since > SQLite uses '^' rather than '!' for the set inversion (if my reading of > the source is correct). GLOB is suppose to exactly mimic Unix, except that SQLite does not break pattern matching at / boundaries the way the shell does. So if the previous statement is true, it is a bug. > > It is not clear how you should escape these characters if you need to > match them literally. It may not be possible, since these characters are > not allowed in filenames and hence wouldn't need to be matched by *nix > commands. Unix allows *any* characters in filenames except \000 and /. You can escape characters using [..]. To match a * anywhere in a string, for example: x GLOB '*[*]*' The [..] pattern must contain at least one internal character. So to match a "]" you can use the pattern x GLOB '*[]]*' -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query problem
Yasir Nisar wrote:
Hi,
Hope you will find this mail in the best of your health.
SELECT * FROM BackupTable,BackupItemTable,BackItUpPathTable WHERE lower(BackItUpPathTable.WinName) GLOB lower("*1[]1.txt*") AND BackupItemTable.BKItemSize > -1 AND BackupTable.BackupNo = BackupItemTable.BackupNo AND BackupItemTable.PathID = BackItUpPathTable.PathID ;
Above mentioned is the query which returns nothing. Problem is with "[" (1[]1.txt). Would you kindly tell me whether "[" is reserved for something or not?
Best Regards,
Yasir Nisar
Yes, the '[' character is used to mark the beginning of a set of
characters to match at that position in the string.
SQLite seems to do the following:
The glob syntax supports the following patterns:
? - matches any single character
* - matches zero or more characters
[seq] - matches any single character in seq
[!seq] - matches any single character not in seq
seq is one or more characters, such as abc. You may specify character
ranges using a dash. For example, a-z0-9 specifies all of the characters
in the English alphabet and the decimal digits 0 through 9.
This appears to be slightly different than normal *nix globbing since
SQLite uses '^' rather than '!' for the set inversion (if my reading of
the source is correct).
It is not clear how you should escape these characters if you need to
match them literally. It may not be possible, since these characters are
not allowed in filenames and hence wouldn't need to be matched by *nix
commands.
You can always trace through the source of the patternCompare function
in SQLite's source file func.c for more details.
HTH
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Query problem
You need single quotes for text literals.
On Jan 23, 2008 6:15 AM, Yasir Nisar <[EMAIL PROTECTED]> wrote:
> SELECT * FROM BackupTable,BackupItemTable,BackItUpPathTable WHERE
> lower(BackItUpPathTable.WinName) GLOB lower("*1[]1.txt*") AND
> BackupItemTable.BKItemSize > -1 AND BackupTable.BackupNo =
> BackupItemTable.BackupNo AND BackupItemTable.PathID =
> BackItUpPathTable.PathID ;
>
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite
Cthulhu Bucks!
http://www.cthulhubucks.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Query problem
Hi,
Hope you will find this mail in the best of your health.
SELECT * FROM BackupTable,BackupItemTable,BackItUpPathTable WHERE
lower(BackItUpPathTable.WinName) GLOB lower("*1[]1.txt*") AND
BackupItemTable.BKItemSize > -1 AND BackupTable.BackupNo =
BackupItemTable.BackupNo AND BackupItemTable.PathID = BackItUpPathTable.PathID ;
Above mentioned is the query which returns nothing. Problem is with "["
(1[]1.txt). Would you kindly tell me whether "[" is reserved for something or
not?
Best Regards,
Yasir Nisar
-
Looking for last minute shopping deals? Find them fast with Yahoo! Search.
RE: [sqlite] query problem
You are correct. I just run a test on 3.2.6 release, and it does handle joins incorrectly. I cant get the files from cvs, so I am not sure whether the fix also Handles the reverse situation: Select * >From T1 left join t2 on (t1.ref=t2.id) and (t2.kind=1) Ie if there is a join term that restricts the RIGHT hand table only. Just a thought. > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Tuesday, September 20, 2005 12:07 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] query problem > > I've changed my mind. I think instead that there is a bug in > SQLite that caused LEFT JOINs to be computed incorrectly if > one of the terms in the ON clause restricts only the left > table in the join. > > Check-in [2725] at http://www.sqlite.org/cvstrac/chngview?cn=2725 > contains > a fix for this problem for version 3.x. The problem has > existed in SQLite forever (because it originates from a > conceptual misunderstanding by the code author :-)) so > version 2.8.16 is still broken. Because the problem is > obscure, I am not inclined to fix it in the 2.8.x series... > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > >
[sqlite] Re: - Re: [sqlite] query problem
No. COUNT(*) of an empty result returns 0. COUNT(*) should always return an integer value, never NULL. It is a row-based, rather than a column-based, aggregate function. rayB |-+> | | "D. Richard Hipp"| | | <[EMAIL PROTECTED]> | | || | | 20/09/2005 05:19 | | | Please respond to| | | sqlite-users | | || |-+> >--| | | | To: sqlite-users@sqlite.org | | cc: | | Subject: - Re: [sqlite] query problem | >--| Hence, the result set contains no rows. A COUNT() of a empty result set gives NULL. -- D. Richard Hipp <[EMAIL PROTECTED]> ** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING * *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com
Re: [sqlite] query problem
On Mon, 2005-09-19 at 15:19 -0400, D. Richard Hipp wrote: > On Mon, 2005-09-19 at 19:36 +0200, Alain Bertrand wrote: > > hi all, > > > > I am porting a program from mysql to sqlite. > > The following statement doesn't work correctly with sqlite though it does > > with mysql. > > SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON > > ttd_photos.kind=1 AND ttd_photos.refId=ttd_trees.treeId LEFT JOIN ttd_pots > > ON ttd_photos.kind=2 AND ttd_photos.refId=ttd_pots.potId > > > > I believe the query above should always return 0... I've changed my mind. I think instead that there is a bug in SQLite that caused LEFT JOINs to be computed incorrectly if one of the terms in the ON clause restricts only the left table in the join. Check-in [2725] at http://www.sqlite.org/cvstrac/chngview?cn=2725 contains a fix for this problem for version 3.x. The problem has existed in SQLite forever (because it originates from a conceptual misunderstanding by the code author :-)) so version 2.8.16 is still broken. Because the problem is obscure, I am not inclined to fix it in the 2.8.x series... -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] query problem
On Mon, 2005-09-19 at 16:06 -0400, Kervin L. Pierre wrote: > D. Richard Hipp wrote: > > Hence, the result set contains no rows. A COUNT() of a empty result > > set gives NULL. > > I thought per the last discussion on "Sum and NULL" > that the count of an empty set would return zero. > You're right. I was thinking of sum. The count should be zero. BTW, I'm beginning to doubt my previous analysis of the problem and am thinking perhaps that SQLite does LEFT JOINs wrong if on the ON constraints refers only to the left table of the LEFT JOIN. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] query problem
D. Richard Hipp wrote: Hence, the result set contains no rows. A COUNT() of a empty result set gives NULL. I thought per the last discussion on "Sum and NULL" that the count of an empty set would return zero. Regards, Kervin
Re: [sqlite] query problem
On Mon, 2005-09-19 at 19:36 +0200, Alain Bertrand wrote: > hi all, > > I am porting a program from mysql to sqlite. > The following statement doesn't work correctly with sqlite though it does > with mysql. > SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON > ttd_photos.kind=1 AND ttd_photos.refId=ttd_trees.treeId LEFT JOIN ttd_pots > ON ttd_photos.kind=2 AND ttd_photos.refId=ttd_pots.potId > I believe the query above should always return NULL (or 0 prior to check-in [2677]). Here's why: Joins group from left to right. The first join to be evaluated is: photos LEFT JOIN trees ON photos.kind=1 AND photos.refid=trees.id This join results (logically) in a table where every row has a column named "photos.kind" with a value of 1. This logical table is then joined as follows: LEFT JOIN pots ON photos.kind=2 AND photos.refid=pots.id In this second join, the photos.kind=2 condition can never be met because every row in the result of the previous join has photos.kind==1. Hence, the result set contains no rows. A COUNT() of a empty result set gives NULL. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] query problem
On Sep 19, 2005, at 12:36 PM, Alain Bertrand wrote: hi all, I am porting a program from mysql to sqlite. The following statement doesn't work correctly with sqlite though it does with mysql. SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON ttd_photos.kind=1 AND ttd_photos.refId=ttd_trees.treeId LEFT JOIN ttd_pots ON ttd_photos.kind=2 AND ttd_photos.refId=ttd_pots.potId In my test case, it reports 0 where it should report 2. This is the LEFT JOIN part which has a problem ;) without seeing the data, a good guess would be, "Yes." LEFT JOIN selects all the rows with values from the left table and either matching values or NULL from the right table. -- Puneet Kishor
[sqlite] query problem
hi all, I am porting a program from mysql to sqlite. The following statement doesn't work correctly with sqlite though it does with mysql. SELECT COUNT(*) AS nb FROM ttd_photos LEFT JOIN ttd_trees ON ttd_photos.kind=1 AND ttd_photos.refId=ttd_trees.treeId LEFT JOIN ttd_pots ON ttd_photos.kind=2 AND ttd_photos.refId=ttd_pots.potId In my test case, it reports 0 where it should report 2. This is the LEFT JOIN part which has a problem ;) Any idea ? Thanks, Alain
Re: [sqlite] Query problem
D. Richard Hipp wrote:
Simon Berthiaume wrote:
For those of you that tends to write complex queries, I noted that
SQLite doesn't like when a table name follows a opening parenthesis in
the FROM clause.
The simplest fix for this would be to insert "SELECT * FROM" right after
the "(" in the FROM list. So, if the original query was like this:
SELECT * FROM tab1 LEFT JOIN (tab2 LEFT JOIN tab3);
The query could be rewritten as follows:
SELECT * FROM tab1 LEFT JOIN (SELECT * FROM tab2 LEFT JOIN tab3);
The 2nd form would be correctly understood by SQLite. It wouldn't be
very difficult to get the SQLite parser to do this automatically, I
expect. Then the first form would work just like the second without
any need for human intervention.
Note a recent similar change to SQLite in check-in [1180]
http://www.sqlite.org/cvstrac/chngview?cn=1180
The IN operator for SQLite used to require a fully-formed SELECT statement
on the right. Like this:
column IN (SELECT * FROM table)
But after check-in [1180], you can now use the following shorthand:
column IN table
The change was to automatically insert the "SELECT * FROM" in the
parser. I'm guessing that a changes to allow parentheses in the
FROM clause would be along the same lines. So if anybody is interested
in working on a patch, I suggest you have a look at [1180] first
to see if you can get any ideas from it.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query problem
Simon Berthiaume wrote:
For those of you that tends to write complex queries, I noted that
SQLite doesn't like when a table name follows a opening parenthesis in
the FROM clause.
The simplest fix for this would be to insert "SELECT * FROM" right after
the "(" in the FROM list. So, if the original query was like this:
SELECT * FROM tab1 LEFT JOIN (tab2 LEFT JOIN tab3);
The query could be rewritten as follows:
SELECT * FROM tab1 LEFT JOIN (SELECT * FROM tab2 LEFT JOIN tab3);
The 2nd form would be correctly understood by SQLite. It wouldn't be
very difficult to get the SQLite parser to do this automatically, I
expect. Then the first form would work just like the second without
any need for human intervention.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query problem
For those of you that tends to write complex queries, I noted that SQLite doesn't like when a table name follows a opening parenthesis in the FROM clause. For example, the following works under Access ans Oracle, but not in SQLite: SELECT T0.OBJECTS_ID , T0.OBJECTS_REFNO, T8.OBJECTS_LOCATION_LOCATION FROM ( OBJECTS AS T0 LEFT JOIN ( SELECT OBJECTS_LOCATION_FOREIGN, MAX(OBJECTS_LOCATION_DATE) A LATEST_DATE FROM OBJECTS_LOCATION GROUP BY OBJECTS_LOCATION_FOREIGN ) AS T9 ON T9.OBJECTS_LOCATION_FOREIGN = T0.OBJECTS_ID ) LEFT JOIN OBJECTS_LOCATION AS T8 ON (T8.OBJECTS_LOCATION_FOREIGN = T9.OBJECTS_LOCATION_FOREIGN AN T8.OBJECTS_LOCATION_DATE = T9.LATEST_DATE) WHERE T0.OBJECTS_FLAG = 3 AND T0.OBJECTS_DELETED = 0 ORDER BY 2 ASC; To make it work, I have to write the following instead: SELECT T0.OBJECTS_ID , T0.OBJECTS_REFNO, T8.OBJECTS_LOCATION_LOCATION FROM OBJECTS AS T0 LEFT JOIN ( SELECT OBJECTS_LOCATION_FOREIGN, MAX(OBJECTS_LOCATION_DATE) AS LATEST_DATE FROM OBJECTS_LOCATION GROUP BY OBJECTS_LOCATION_FOREIGN ) AS T9 ON T9.OBJECTS_LOCATION_FOREIGN = T0.OBJECTS_ID LEFT JOIN OBJECTS_LOCATION AS T8 ON (T8.OBJECTS_LOCATION_FOREIGN = T9.OBJECTS_LOCATION_FOREIGN AN T8.OBJECTS_LOCATION_DATE = T9.LATEST_DATE) WHERE T0.OBJECTS_FLAG = 0 AND T0.OBJECTS_DELETED = 0 ORDER BY 2 ASC; I hope I will make someone save the trouble I went through finding that out. Simon Berthiaume On Mon, 2004-01-19 at 16:37, Simon Berthiaume wrote: > Maybe I used a too simplistic example. I have code that generates SQL > queries so users can request reports with various fields, various > criterias and various sorting orders. So technicaly there can be 1,2,3,N > inner queries with various statements in them. Here is an example of a > more complex query: > SELECT t0.OBJECTS_BRIEF, t2.OBJECTS_ALPHANUMERIC_VALUE, > t3.OBJECTS_DATE_VALUE > FROM > ( > ( > ( > SELECT * > FROM OBJECTS > WHERE OBJECTS_COLTYPE='Jewelry' > ) AS t0 > LEFT JOIN > ( > SELECT * > FROM OBJECTS_ALPHANUMERIC > WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='GRADE' > AND (OBJECTS_ALPHANUMERIC_VALUE='Excellent' > OR OBJECTS_ALPHANUMERIC_VALUE IS NULL) > ) AS t1 > ON t0.OBJECTS_ID = t1.OBJECTS_ID > ) > LEFT JOIN > ( > SELECT * > FROM OBJECTS_ALPHANUMERIC > WHERE OBJECTS_ALPHANUMERIC_FIELD_ID='OWNER' > AND NOT OBJECTS_ALPHANUMERIC_VALUE='John Smith' > ) AS t2 > ON t0.OBJECTS_ID = t2.OBJECTS_ID > ) > LEFT JOIN > ( > SELECT * > FROM OBJECTS_DATE > WHERE OBJECTS_DATE_FIELD_ID='EXHIBIT_END_DATE' > ) AS t3 > ON t0.OBJECTS_ID = t3.OBJECTS_ID > ORDER BY 3,2; > > In the previous example, the user would have asked for > 1- the item's description, it's owner it's exhibit end date > 2- Objects that are Jewelry > 3- Objects in excellent condition (grade) or unspecified > 4- Objects not owned by John Smith > 5- Sorted by exhibit end date and owner name > > The "relationship" between OBJECTS and OBJECTS_ALPHANUMERIC is 0,N and > the "relationship" between OBJECTS and OBJECTS_DATE is also 0,N. > > The previous query worked just fine in Oracle and Access, but not in > SQLite. SQLite might simply not be support it, that is what I want to > know. If it's a bug, a feature SQLite will support in the future (how > close in the future) or a feature that will never be supported. > > Thank you all for your time > > > Simon Berthiaume > > > On Mon, 2004-01-19 at 14:24, Kurt Welgehausen wrote: > > > If you look at the SQLite grammar in lang.html, you'll see > > that parentheses are not allowed around a table-list. That's > > why you're getting an error. > > > > If you remove either of the first 2 left parens (and its > > corresponding right paren), the query will work, but the > > outer select and the first subselect serve no purpose. It's > > hard to predict performance just by reading a query, but this > > looks pretty inefficient. How about > > > > select * from > > INSCLAIMS t0 > > left join > > (select * from INSCLAIMS_CONCAT > > where INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO') t1 > > on t1.INSCLAIMS_ID = t0.INSCLAIMS_ID; > > > > - > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > >
Re: [sqlite] Query problem
If you look at the SQLite grammar in lang.html, you'll see that parentheses are not allowed around a table-list. That's why you're getting an error. If you remove either of the first 2 left parens (and its corresponding right paren), the query will work, but the outer select and the first subselect serve no purpose. It's hard to predict performance just by reading a query, but this looks pretty inefficient. How about select * from INSCLAIMS t0 left join (select * from INSCLAIMS_CONCAT where INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO') t1 on t1.INSCLAIMS_ID = t0.INSCLAIMS_ID; - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Query problem
Actualy none, there was an error in the statement I copied. But the following works on Access (ADO) and Oracle as well, but not on SQLite. Since I don't have access to any other RDBMS, I couldn't test others. SELECT * FROM ( ( SELECT * from INSCLAIMS ) AS T0 LEFT JOIN ( SELECT * FROM INSCLAIMS_CONCAT WHERE ( INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO' ) ) AS T1 ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID ) ; Simon Berthiaume On Mon, 2004-01-19 at 12:52, D. Richard Hipp wrote: > Simon Berthiaume wrote: > > SELECT * > > FROM > >( > > ( > > SELECT * from INSCLAIMS > > ) AS T0 > > LEFT JOIN > > ( > > SELECT * FROM INSCLAIMS_CONCAT WHERE > > ( > > INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO' > > ) > > ) AS T1 > > ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID > >) AS foo > > ; > > > > On what RDBMSes does the above query actually work? >
Re: [sqlite] Query problem
Simon Berthiaume wrote: SELECT * FROM ( ( SELECT * from INSCLAIMS ) AS T0 LEFT JOIN ( SELECT * FROM INSCLAIMS_CONCAT WHERE ( INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO' ) ) AS T1 ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID ) AS foo ; On what RDBMSes does the above query actually work? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Query problem
Yep, it's me again (no I haven't solved my other problem yet). This time
I was wondering why the SQL engine of SQLite works fine with the
following query:
SELECT *
FROM
(
SELECT * from INSCLAIMS
) AS T0
LEFT JOIN
(
SELECT * FROM INSCLAIMS_CONCAT WHERE
(
INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO'
)
) AS T1
ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID
;
But returns an error (SQL error: near "(": syntax error) with the
following query:
SELECT *
FROM
(
(
SELECT * from INSCLAIMS
) AS T0
LEFT JOIN
(
SELECT * FROM INSCLAIMS_CONCAT WHERE
(
INSCLAIMS_CONCAT_FIELD_ID = 'INSCLAIMS_POLICYNO'
)
) AS T1
ON T1.INSCLAIMS_ID = T0.INSCLAIMS_ID
) AS foo
;
So why do I have this behavior, does SQLite only support a maximum
degree of "inner-ness" in queries?
Simon Berthiaume
