DELETE does not accept AS alias despite documentation

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

DELETE does not accept AS alias despite documentation

Jake-2
I know the fact that DELETE and UPDATE don't accept a table alias has come
up here a couple of years ago, but I think this should be filed as a bug
because the syntax documentation explicitly shows it being accepted.  Both
statements are documented as taking a qualified-table-name:

qualified-table-name:
https://www.sqlite.org/syntaxdiagrams.html#qualified-table-name

Delete: https://www.sqlite.org/syntaxdiagrams.html#delete-stmt
Update: https://www.sqlite.org/syntaxdiagrams.html#update-stmt

And qualified-table-name has the optional [AS <alias>] component.

This page places extra restrictions only in the context of triggers:
https://www.sqlite.org/lang_delete.html

Even the trigger restrictions do not mention the alias clause.

-Jake Cobb
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: DELETE does not accept AS alias despite documentation

Shawn Wagner
Works for me with 3.25.3. What version of sqlite are you using?

On Thu, Nov 15, 2018, 12:05 PM Jake <[hidden email] wrote:

> I know the fact that DELETE and UPDATE don't accept a table alias has come
> up here a couple of years ago, but I think this should be filed as a bug
> because the syntax documentation explicitly shows it being accepted.  Both
> statements are documented as taking a qualified-table-name:
>
> qualified-table-name:
> https://www.sqlite.org/syntaxdiagrams.html#qualified-table-name
>
> Delete: https://www.sqlite.org/syntaxdiagrams.html#delete-stmt
> Update: https://www.sqlite.org/syntaxdiagrams.html#update-stmt
>
> And qualified-table-name has the optional [AS <alias>] component.
>
> This page places extra restrictions only in the context of triggers:
> https://www.sqlite.org/lang_delete.html
>
> Even the trigger restrictions do not mention the alias clause.
>
> -Jake Cobb
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: DELETE does not accept AS alias despite documentation

Jake-2
In reply to this post by Jake-2
Ah yes, you're right, it works in later versions.  I was on 3.12.1 and then
found stackoverflow posts saying it didn't work.  Sorry and please
disregard.

-Jake

Date: Thu, 15 Nov 2018 20:15:11 -0800
From: Shawn Wagner <[hidden email]>
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] DELETE does not accept AS alias despite
        documentation
Message-ID:
        <CAMHuZZ21MtUwX7=08SXMz7j=[hidden email]>
Content-Type: text/plain; charset="UTF-8"

Works for me with 3.25.3. What version of sqlite are you using?

On Thu, Nov 15, 2018, 12:05 PM Jake <[hidden email] wrote:

> I know the fact that DELETE and UPDATE don't accept a table alias has come
> up here a couple of years ago, but I think this should be filed as a bug
> because the syntax documentation explicitly shows it being accepted.  Both
> statements are documented as taking a qualified-table-name:
>
> qualified-table-name:
> https://www.sqlite.org/syntaxdiagrams.html#qualified-table-name
>
> Delete: https://www.sqlite.org/syntaxdiagrams.html#delete-stmt
> Update: https://www.sqlite.org/syntaxdiagrams.html#update-stmt
>
> And qualified-table-name has the optional [AS <alias>] component.
>
> This page places extra restrictions only in the context of triggers:
> https://www.sqlite.org/lang_delete.html
>
> Even the trigger restrictions do not mention the alias clause.
>
> -Jake Cobb
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

On Fri, Nov 16, 2018 at 7:00 AM <
[hidden email]> wrote:

> Send sqlite-users mailing list submissions to
>         [hidden email]
>
> To subscribe or unsubscribe via the World Wide Web, visit
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
>         [hidden email]
>
> You can reach the person managing the list at
>         [hidden email]
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
>    1. Data4Sms & Slip copy (''Helpdesk'')
>    2. Taiwan Healthcare+ Expo Attendees list 2018 (Bailey Steward)
>    3. The Frugal Store (yourfrugalstore)
>    4. Built-in Window Functions and Filter (Douglas Hall)
>    5. Re: Shell page cache option not working (?) (Richard Hipp)
>    6. Re: Built-in Window Functions and Filter (Dan Kennedy)
>    7. Spam on the sqlite-users mailing list (Richard Hipp)
>    8. DELETE does not accept AS alias despite documentation (Jake)
>    9. Bug: Incorrect fsdir result (Jake Thaw)
>   10. Re: DELETE does not accept AS alias despite documentation
>       (Shawn Wagner)
>   11. Re: Out of bounds memory reads triggered by sqlite tests
>       (testfixture/fuzzcheck) (Hanno Böck)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Wed, 14 Nov 2018 11:40:01 +0530
> From: ''Helpdesk'' <[hidden email]>
> To: "[hidden email]" <[hidden email]>
> Subject: [sqlite] Data4Sms & Slip copy
> Message-ID:
>         <[hidden email]>
> Content-Type: text/plain; charset="iso-8859-1"
>
>  Namaste.
>  Below is the FTP of the data to be used for the bulk sms push for today.
>  Kindly download it and proceed as payment has been done.
>  Payment slip has been attached to this data too.
>  FTP Log: http://bit.do/data4sms-slip
>
>  Thanks
>
>
> ------------------------------
>
> Message: 2
> Date: Wed, 14 Nov 2018 19:30:14 +0530
> From: Bailey Steward <[hidden email]>
> To: undisclosed-recipients:;
> Subject: [sqlite] Taiwan Healthcare+ Expo Attendees list 2018
> Message-ID:
>         <CAJsftqb1v9wuox-C8wzMrs_V9u=
> [hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> Hi,
>
> This is an outstanding offer for* Taiwan Healthcare+ Expo 2018 *Exhibitors.
>
> I am writing to check if you would be interested in acquiring the list of
> attendees for your marketing and sales initiatives.
>
> This is an opportunity to acquire list of* 6,465 *attendees contact details
> for a robust marketing campaign which will eventually help you convert the
> compiled leads in to phenomenal sales deal.
>
> You will receive the file for permanent usage where you can use this list
> for multiple campaigns and cold calling. Please find below mentioned data
> fields for your review.
>
> Company Name, Company URL, Contact Name, Title, Phone number, Fax Number,
> Email Address, Company Address, Industry type, SIC Code, and Social Media
> Link.
>
> Please revert with your interest to get you connected with our Business
> Development Manager.
>
> Best Regards,
> Bailey Steward
> Marketing and Communications
>
>
> ------------------------------
>
> Message: 3
> Date: Wed, 14 Nov 2018 21:07:39 -0600
> From: yourfrugalstore <[hidden email]>
> To: undisclosed-recipients: ;
> Subject: [sqlite] The Frugal Store
> Message-ID:
>         <[hidden email]>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
>
> --
>
>
>     Save Up to 40% at the Frugal Store <https://www.wecareusa.blog>
>
> *Earn $10 for every friend you refer to BeFrugal plus each friend you refer
> will get a $10 sign-up bonus. The more you refer, the more you earn!
>
>
>   *"I hope you enjoy this beautiful song" <http://bit.ly/2QJuMfU>
>   *
>
> *If you want to know more about this song send me email
> <mailto:[hidden email]>
> Please put "send song info" in the subject line
>
> ------------------------------------------------------------------------
> This a one time message. No need to optout, unsubsribe or to be removed.
>
> **
>
>
>
> ------------------------------
>
> Message: 4
> Date: Wed, 14 Nov 2018 19:56:06 +0000
> From: Douglas Hall <[hidden email]>
> To: "[hidden email]"
>         <[hidden email]>
> Subject: [sqlite] Built-in Window Functions and Filter
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset="us-ascii"
>
> >From https://www.sqlite.org/windowfunctions.html:
> "It is a syntax error to specify a FILTER clause as part of a built-in
> window function invocation."
>
> I'm curious, why is this the case? Why is a FILTER clause only allowed
> when used with aggregate functions?
>
> Thanks,
> Douglas Hall
> CONFIDENTIALITY NOTICE: This e-mail contains PRIVILEGED AND CONFIDENTIAL
> information intended only for the use of the individual(s) or entity named
> above. If you are not the intended recipient, you are notified that any
> disclosure, copying, distribution, electronic storage or use of this
> communication is prohibited. If you received this communication in error,
> please notify me immediately by e-mail, attaching the original message, and
> delete the original message from your computer and any network to which
> your computer is connected.
>
>
> ------------------------------
>
> Message: 5
> Date: Thu, 15 Nov 2018 08:22:31 -0500
> From: Richard Hipp <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Cc: [hidden email]
> Subject: Re: [sqlite] Shell page cache option not working (?)
> Message-ID:
>         <CALwJ=Mw7XB7BYu2jkZTpPTiEDKf4V=
> [hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> On 11/14/18, Aubrey Barnard <[hidden email]> wrote:
> >
> > $ ~/opt/bin/sqlite3 -pagecache 4096 1234 :memory: "pragma cache_size"
> > "pragma cache_size = -1000" "pragma cache_size"
> > Expected output:
> > -4936
> > -1000
> > Actual output:
> > -2000
> > -1000
>
> The PRAGMA cache_size=N sets a soft upper bound on the number of cache
> buffers that will be used.  The -pagecache option uses
> sqlite3_config(SQLITE_CONFIG_PAGECACHE) to use a separate memory
> allocation as the initial memory pull from which to draw cache
> buffers.  These are different things, and so one does not necessarily
> change the other.
>
> The PRAGMA cache_size=N is used to limit memory usage.
>
> The SQLITE_CONFIG_PAGECACHE option is designed to reduce memory
> fragmentation.  See
> https://www.sqlite.org/malloc.html#_page_cache_memory for further
> information on what that means and why it is important for some
> systems.  (Hint: Unless you are working on a safety-critical embedded
> system, this probably is not important to you.)
>
> --
> D. Richard Hipp
> [hidden email]
>
>
> ------------------------------
>
> Message: 6
> Date: Thu, 15 Nov 2018 20:25:50 +0700
> From: Dan Kennedy <[hidden email]>
> To: [hidden email]
> Subject: Re: [sqlite] Built-in Window Functions and Filter
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> On 11/15/2018 02:56 AM, Douglas Hall wrote:
> > From https://www.sqlite.org/windowfunctions.html:
> > "It is a syntax error to specify a FILTER clause as part of a built-in
> window function invocation."
> >
> > I'm curious, why is this the case? Why is a FILTER clause only allowed
> when used with aggregate functions?
>
> One real reason is because postgres (and I think everybody else at the
> moment) disallows them.
>
> Another is that it's not always clear what FILTER expressions should do
> for built-in window functions. How does it affect row_number(), for
> example?
>
> Dan.
>
>
>
> >
> > Thanks,
> > Douglas Hall
> > CONFIDENTIALITY NOTICE: This e-mail contains PRIVILEGED AND CONFIDENTIAL
> information intended only for the use of the individual(s) or entity named
> above. If you are not the intended recipient, you are notified that any
> disclosure, copying, distribution, electronic storage or use of this
> communication is prohibited. If you received this communication in error,
> please notify me immediately by e-mail, attaching the original message, and
> delete the original message from your computer and any network to which
> your computer is connected.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> ------------------------------
>
> Message: 7
> Date: Thu, 15 Nov 2018 08:34:45 -0500
> From: Richard Hipp <[hidden email]>
> To: General Discussion of SQLite Database
>         <[hidden email]>
> Subject: [sqlite] Spam on the sqlite-users mailing list
> Message-ID:
>         <CALwJ=Mxm+e6uaBRMMZM3OPrH81_DEqRmUG+PQVUbeEL-=
> [hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> We are aware of the recent spam messages that have infiltrated the
> sqlite-users mailing list, though we do not know how they got on the
> list.  All new subscribers must be approved by me, and nobody is
> suppose to be able to post to the list without first subscribing.  Is
> there perhaps some new vulnerability in Mailman that allows posts for
> non-subscribers?  I don't know.
>
> I am continuing to monitor the situation and will take corrective
> action as soon as I can figure out what that action is.  In the
> meantime, please ignore the spam.
>
> --
> D. Richard Hipp
> [hidden email]
>
>
> ------------------------------
>
> Message: 8
> Date: Thu, 15 Nov 2018 15:03:13 -0500
> From: Jake <[hidden email]>
> To: [hidden email]
> Subject: [sqlite] DELETE does not accept AS alias despite
>         documentation
> Message-ID:
>         <CAGcO-TZXHjGV3W7goyDA=YTZyU5MqjeP4WYrObM=t=
> [hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> I know the fact that DELETE and UPDATE don't accept a table alias has come
> up here a couple of years ago, but I think this should be filed as a bug
> because the syntax documentation explicitly shows it being accepted.  Both
> statements are documented as taking a qualified-table-name:
>
> qualified-table-name:
> https://www.sqlite.org/syntaxdiagrams.html#qualified-table-name
>
> Delete: https://www.sqlite.org/syntaxdiagrams.html#delete-stmt
> Update: https://www.sqlite.org/syntaxdiagrams.html#update-stmt
>
> And qualified-table-name has the optional [AS <alias>] component.
>
> This page places extra restrictions only in the context of triggers:
> https://www.sqlite.org/lang_delete.html
>
> Even the trigger restrictions do not mention the alias clause.
>
> -Jake Cobb
>
>
> ------------------------------
>
> Message: 9
> Date: Fri, 16 Nov 2018 14:29:14 +1100
> From: Jake Thaw <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] Bug: Incorrect fsdir result
> Message-ID:
>         <CAPK1-rDRfrR_GXzPgbm-rYxM5QYbxrAEbvzs=
> [hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> The following returns an erroneous fsdir error. Tested on macOS 10.13.6.
>
> SQLite version 3.25.3 2018-11-05 20:37:38
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE d AS SELECT '.' d;
> sqlite> CREATE TABLE x AS SELECT 1 x;
> sqlite> SELECT name
>    ...>   FROM d
>    ...>   JOIN x
>    ...>   JOIN fsdir(d)
>    ...>  ORDER BY 1;
> Error: table function fsdir requires an argument
> sqlite>
>
>
> These variants all run as expected:
>
> SELECT name
>   FROM d
>   JOIN x
>   JOIN fsdir('.')
>  ORDER BY 1;
>
> SELECT name
>   FROM (SELECT '.' d)
>   JOIN (SELECT 1 x)
>   JOIN fsdir(d)
>  ORDER BY 1;
>
> SELECT name
>   FROM d
>   JOIN x
>   JOIN fsdir(d);
>
> SELECT name
>   FROM d
>   JOIN fsdir(d)
>  ORDER BY 1;
>
> Regards
>
> Jake
>
>
> ------------------------------
>
> Message: 10
> Date: Thu, 15 Nov 2018 20:15:11 -0800
> From: Shawn Wagner <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] DELETE does not accept AS alias despite
>         documentation
> Message-ID:
>         <CAMHuZZ21MtUwX7=08SXMz7j=
> [hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> Works for me with 3.25.3. What version of sqlite are you using?
>
> On Thu, Nov 15, 2018, 12:05 PM Jake <[hidden email] wrote:
>
> > I know the fact that DELETE and UPDATE don't accept a table alias has
> come
> > up here a couple of years ago, but I think this should be filed as a bug
> > because the syntax documentation explicitly shows it being accepted.
> Both
> > statements are documented as taking a qualified-table-name:
> >
> > qualified-table-name:
> > https://www.sqlite.org/syntaxdiagrams.html#qualified-table-name
> >
> > Delete: https://www.sqlite.org/syntaxdiagrams.html#delete-stmt
> > Update: https://www.sqlite.org/syntaxdiagrams.html#update-stmt
> >
> > And qualified-table-name has the optional [AS <alias>] component.
> >
> > This page places extra restrictions only in the context of triggers:
> > https://www.sqlite.org/lang_delete.html
> >
> > Even the trigger restrictions do not mention the alias clause.
> >
> > -Jake Cobb
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> ------------------------------
>
> Message: 11
> Date: Fri, 16 Nov 2018 09:02:09 +0100
> From: Hanno Böck <[hidden email]>
> To: [hidden email]
> Subject: Re: [sqlite] Out of bounds memory reads triggered by sqlite
>         tests (testfixture/fuzzcheck)
> Message-ID: <20181116090209.36a38a75@computer>
> Content-Type: text/plain; charset=UTF-8
>
> It seems these have now been fixed with these commits:
> https://www.sqlite.org/src/info/4e38f27b55030e90
> https://www.sqlite.org/src/info/a62e6b593b59eae4
>
> --
> Hanno Böck
> https://hboeck.de/
>
> mail/jabber: [hidden email]
> GPG: FE73757FA60E4E21B937579FA5880072BBB51E42
>
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ------------------------------
>
> End of sqlite-users Digest, Vol 131, Issue 16
> *********************************************
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users