I have the following code:
set getFirstAndLast {
SELECT MIN(WeekNo) AS FirstWeek
, MAX(WeekNo) AS LastWeek
FROM (
SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
, COUNT(dayViews) AS Days
FROM dayViews
GROUP BY WeekNo
)
WHERE Days = 7
}
set selectWeek {
SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
, COUNT(*) AS Days
, SUM(dayViews) AS WeekTotals
FROM dayViews
WHERE WeekNo >= :startWeek
AND WeekNo <= :endWeek
GROUP BY WeekNo
ORDER BY WeekNo
}

sqlite3 db ~/Databases/youtube.sqlite
db eval ${getFirstAndLast} {
set startWeek ${FirstWeek}
set endWeek ${LastWeek}
# set nrOfWeeks [expr { 1 + $endWeek - $startWeek }]
# puts ${nrOfWeeks}i
puts "Start with week ${FirstWeek} and en with week ${LastWeek}"
}
db eval ${selectWeek} {
if { ${Days} != 7 } {
puts "ERROR: week ${WeekNo} has ${Days} days"
}
puts [format "Week %2s has %4s views" ${WeekNo} ${WeekTotals}]
lappend viewsArr ${WeekTotals}
lappend weekArr ${WeekNo}
}
db close
puts "Got here"

When I run this I get:
Start with week 38 and en with week 45
Week 38 has 160 views
Week 39 has 92 views
Week 40 has 134 views
Week 41 has 200 views
Week 42 has 241 views
Week 43 has 241 views
Week 44 has 664 views
Week 45 has 416 views
Got here

When I uncomment the two statements in the first 'db eval' I get:
8i
Start with week 38 and en with week 45
Got here

So the set works and does not corrupt startWeek, or endWeek, bor does
it terminate the program.
But it ruins the second 'db eval'.
What could be happening here?
Even when I set nrOfWeeks2 instead of nrOfWeeks, I have the same
problem.

--
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

Cecil Westerhof schrieb am Sonntag, 12. November 2023 um 13:28:09 UTC+1:
> I have the following code:
> set getFirstAndLast {
> SELECT MIN(WeekNo) AS FirstWeek
> , MAX(WeekNo) AS LastWeek
> FROM (
> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
> , COUNT(dayViews) AS Days
> FROM dayViews
> GROUP BY WeekNo
> )
> WHERE Days = 7
> }
> set selectWeek {
> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
> , COUNT(*) AS Days
> , SUM(dayViews) AS WeekTotals
> FROM dayViews
> WHERE WeekNo >= :startWeek
> AND WeekNo <= :endWeek
> GROUP BY WeekNo
> ORDER BY WeekNo
> }
>
>
> sqlite3 db ~/Databases/youtube.sqlite
> db eval ${getFirstAndLast} {
> set startWeek ${FirstWeek}
> set endWeek ${LastWeek}
> # set nrOfWeeks [expr { 1 + $endWeek - $startWeek }]
> # puts ${nrOfWeeks}i
> puts "Start with week ${FirstWeek} and en with week ${LastWeek}"
> }
> db eval ${selectWeek} {
> if { ${Days} != 7 } {
> puts "ERROR: week ${WeekNo} has ${Days} days"
> }
> puts [format "Week %2s has %4s views" ${WeekNo} ${WeekTotals}]
> lappend viewsArr ${WeekTotals}
> lappend weekArr ${WeekNo}
> }
> db close
> puts "Got here"
>
> When I run this I get:
> Start with week 38 and en with week 45
> Week 38 has 160 views
> Week 39 has 92 views
> Week 40 has 134 views
> Week 41 has 200 views
> Week 42 has 241 views
> Week 43 has 241 views
> Week 44 has 664 views
> Week 45 has 416 views
> Got here
>
> When I uncomment the two statements in the first 'db eval' I get:
> 8i
> Start with week 38 and en with week 45
> Got here
>
> So the set works and does not corrupt startWeek, or endWeek, bor does
> it terminate the program.
> But it ruins the second 'db eval'.
> What could be happening here?
> Even when I set nrOfWeeks2 instead of nrOfWeeks, I have the same
> problem.
>
> --
> Cecil Westerhof
> Senior Software Engineer
> LinkedIn: http://www.linkedin.com/in/cecilwesterhof

expr change in {} the variable type, not in ()

in the second select:
from
https://www.sqlite.org/lang_datefunc.html
Their strftime() equivalents return a string that is the text representation of the corresponding number.
....
WHERE WeekNo >= ':startWeek'
AND WeekNo <= ':endWeek'

or

in the first 'db eval'.

puts "[tcl::unsupported::representation $endWeek]"
#int
set nrOfWeeks [expr { 1 + $endWeek - $startWeek }]

#string
#set nrOfWeeks [expr ( 1 + $endWeek - $startWeek )]
puts "[tcl::unsupported::representation $endWeek]"
# puts ${nrOfWeeks}i

greg <gregor.ebbing@googlemail.com> writes:

> Cecil Westerhof schrieb am Sonntag, 12. November 2023 um 13:28:09 UTC+1:
>> I have the following code:
>> set getFirstAndLast {
>> SELECT MIN(WeekNo) AS FirstWeek
>> , MAX(WeekNo) AS LastWeek
>> FROM (
>> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
>> , COUNT(dayViews) AS Days
>> FROM dayViews
>> GROUP BY WeekNo
>> )
>> WHERE Days = 7
>> }
>> set selectWeek {
>> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
>> , COUNT(*) AS Days
>> , SUM(dayViews) AS WeekTotals
>> FROM dayViews
>> WHERE WeekNo >= :startWeek
>> AND WeekNo <= :endWeek
>> GROUP BY WeekNo
>> ORDER BY WeekNo
>> }
>>
>>
>> sqlite3 db ~/Databases/youtube.sqlite
>> db eval ${getFirstAndLast} {
>> set startWeek ${FirstWeek}
>> set endWeek ${LastWeek}
>> # set nrOfWeeks [expr { 1 + $endWeek - $startWeek }]
>> # puts ${nrOfWeeks}i
>> puts "Start with week ${FirstWeek} and en with week ${LastWeek}"
>> }
>> db eval ${selectWeek} {
>> if { ${Days} != 7 } {
>> puts "ERROR: week ${WeekNo} has ${Days} days"
>> }
>> puts [format "Week %2s has %4s views" ${WeekNo} ${WeekTotals}]
>> lappend viewsArr ${WeekTotals}
>> lappend weekArr ${WeekNo}
>> }
>> db close
>> puts "Got here"
>>
>> When I run this I get:
>> Start with week 38 and en with week 45
>> Week 38 has 160 views
>> Week 39 has 92 views
>> Week 40 has 134 views
>> Week 41 has 200 views
>> Week 42 has 241 views
>> Week 43 has 241 views
>> Week 44 has 664 views
>> Week 45 has 416 views
>> Got here
>>
>> When I uncomment the two statements in the first 'db eval' I get:
>> 8i
>> Start with week 38 and en with week 45
>> Got here
>>
>> So the set works and does not corrupt startWeek, or endWeek, bor does
>> it terminate the program.
>> But it ruins the second 'db eval'.
>> What could be happening here?
>> Even when I set nrOfWeeks2 instead of nrOfWeeks, I have the same
>> problem.
>>
>> --
>> Cecil Westerhof
>> Senior Software Engineer
>> LinkedIn: http://www.linkedin.com/in/cecilwesterhof
>
>
> expr change in {} the variable type, not in ()
>
>
>
> in the second select:
> from
> https://www.sqlite.org/lang_datefunc.html
> Their strftime() equivalents return a string that is the text
> representation of the corresponding number.
> ...
> WHERE WeekNo >= ':startWeek'
> AND WeekNo <= ':endWeek'
>
>
> or
>
> in the first 'db eval'.
>
>
> puts "[tcl::unsupported::representation $endWeek]"
> #int
> set nrOfWeeks [expr { 1 + $endWeek - $startWeek }]
>
> #string
> #set nrOfWeeks [expr ( 1 + $endWeek - $startWeek )]
> puts "[tcl::unsupported::representation $endWeek]"
> # puts ${nrOfWeeks}i

It works, but I find it strange for two reasons:
- I was always told that what is given to expr should be between '{'
and '}'.
- I would not expect that expr would change the input parameters.

--
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

On Monday, November 13, 2023 at 2:28:08 PM UTC+1, Cecil Westerhof wrote:
> greg writes:
>
> > Cecil Westerhof schrieb am Sonntag, 12. November 2023 um 13:28:09 UTC+1:
> >> I have the following code:
> >> set getFirstAndLast {
> >> SELECT MIN(WeekNo) AS FirstWeek
> >> , MAX(WeekNo) AS LastWeek
> >> FROM (
> >> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
> >> , COUNT(dayViews) AS Days
> >> FROM dayViews
> >> GROUP BY WeekNo
> >> )
> >> WHERE Days = 7
> >> }
> >> set selectWeek {
> >> SELECT strftime('%W', dayDate, '+1 day') AS WeekNo
> >> , COUNT(*) AS Days
> >> , SUM(dayViews) AS WeekTotals
> >> FROM dayViews
> >> WHERE WeekNo >= :startWeek
> >> AND WeekNo <= :endWeek
> >> GROUP BY WeekNo
> >> ORDER BY WeekNo
> >> }
> >>
> >>
> >> sqlite3 db ~/Databases/youtube.sqlite
> >> db eval ${getFirstAndLast} {
> >> set startWeek ${FirstWeek}
> >> set endWeek ${LastWeek}
> >> # set nrOfWeeks [expr { 1 + $endWeek - $startWeek }]
> >> # puts ${nrOfWeeks}i
> >> puts "Start with week ${FirstWeek} and en with week ${LastWeek}"
> >> }
> >> db eval ${selectWeek} {
> >> if { ${Days} != 7 } {
> >> puts "ERROR: week ${WeekNo} has ${Days} days"
> >> }
> >> puts [format "Week %2s has %4s views" ${WeekNo} ${WeekTotals}]
> >> lappend viewsArr ${WeekTotals}
> >> lappend weekArr ${WeekNo}
> >> }
> >> db close
> >> puts "Got here"
> >>
> >> When I run this I get:
> >> Start with week 38 and en with week 45
> >> Week 38 has 160 views
> >> Week 39 has 92 views
> >> Week 40 has 134 views
> >> Week 41 has 200 views
> >> Week 42 has 241 views
> >> Week 43 has 241 views
> >> Week 44 has 664 views
> >> Week 45 has 416 views
> >> Got here
> >>
> >> When I uncomment the two statements in the first 'db eval' I get:
> >> 8i
> >> Start with week 38 and en with week 45
> >> Got here
> >>
> >> So the set works and does not corrupt startWeek, or endWeek, bor does
> >> it terminate the program.
> >> But it ruins the second 'db eval'.
> >> What could be happening here?
> >> Even when I set nrOfWeeks2 instead of nrOfWeeks, I have the same
> >> problem.
> >>
> >> --
> >> Cecil Westerhof
> >> Senior Software Engineer
> >> LinkedIn: http://www.linkedin.com/in/cecilwesterhof
> >
> >
> > expr change in {} the variable type, not in ()
> >
> >
> >
> > in the second select:
> > from
> > https://www.sqlite.org/lang_datefunc.html
> > Their strftime() equivalents return a string that is the text
> > representation of the corresponding number.
> > ...
> > WHERE WeekNo >= ':startWeek'
> > AND WeekNo <= ':endWeek'

I did not test it, but are you telling SQL to compare strings here? That might introduce a data-dependent bug like '2' >= '10'.

> > or
> >
> > in the first 'db eval'.
> >
> >
> > puts "[tcl::unsupported::representation $endWeek]"
> > #int
> > set nrOfWeeks [expr { 1 + $endWeek - $startWeek }]
> >
> > #string
> > #set nrOfWeeks [expr ( 1 + $endWeek - $startWeek )]
> > puts "[tcl::unsupported::representation $endWeek]"
> > # puts ${nrOfWeeks}i
> It works, but I find it strange for two reasons:
> - I was always told that what is given to expr should be between '{'
> and '}'.

That still holds. expr should not shimmer, but it does in this case (actually evaluation of the arg _before_ the call to expr does). Compare these (first one with braces):

expect:~$ set i [expr 1]; puts [::tcl::unsupported::representation $i]; set ii [expr {$i+$i}]; puts [::tcl::unsupported::representation $i]
value is a int with a refcount of 2, object pointer at 0xbdbfa0, internal representation 0x1:0x1, no string representation
value is a int with a refcount of 2, object pointer at 0xbdbfa0, internal representation 0x1:0x1, no string representation
expect:~$ set i [expr 1]; puts [::tcl::unsupported::representation $i]; set ii [expr $i+$i]; puts [::tcl::unsupported::representation $i]
value is a int with a refcount of 2, object pointer at 0xb6d560, internal representation 0x1:0x1, no string representation
value is a int with a refcount of 2, object pointer at 0xb6d560, internal representation 0x1:0x1, string representation "1"

i has no string rep before expr.
When passing {$i+$i} to expr, expr will parse that constant string and read the int rep of i.
When passing $i+$i to expr, expr will actually see and parse 1+1, not i. The string rep of i is needed to subst $i in $i+$i.

> - I would not expect that expr would change the input parameters.

It does not, because EIAS in Tcl. I think the unexpected behavior happens in the SQL library, not Tcl. It seems that it relies on the (unsupported) representation, i.e. it behaves in a different way depending on the existence of a string rep. However, the existence of either representation is just for performance.

More remarks with examples below:
* Emphasizing that expr is _not_ the offending part, note that merely logging the number value would also produce a string rep. Expr can actually fix the string rep "problem", just like incr.
* The refcount of 2 in my examples most likely comes from the interactive session (e.g. command history). In such a session, it is also important to never return the object under investigation to the command line (or it will have a string rep!).

expect:~$ set i [expr 1]; puts [::tcl::unsupported::representation $i]; puts $i; puts [::tcl::unsupported::representation $i]; puts [::tcl::unsupported::representation [expr {$i}]]
value is a int with a refcount of 2, object pointer at 0xbdbf70, internal representation 0x1:0x1, no string representation
1 value is a int with a refcount of 2, object pointer at 0xbdbf70, internal representation 0x1:0x1, string representation "1"
value is a int with a refcount of 1, object pointer at 0xbdb1c0, internal representation 0x1:0x1, no string representation
expect:~$ set i [expr 1]; puts [::tcl::unsupported::representation $i]; puts $i; puts [::tcl::unsupported::representation $i]; set i [expr {$i}]; puts [::tcl::unsupported::representation $i]
value is a int with a refcount of 2, object pointer at 0xbdc5a0, internal representation 0x1:0x1, no string representation
1 value is a int with a refcount of 2, object pointer at 0xbdc5a0, internal representation 0x1:0x1, string representation "1"
value is a int with a refcount of 2, object pointer at 0xbdbf70, internal representation 0x1:0x1, no string representation
expect:~$ set i [expr 1]; puts [::tcl::unsupported::representation $i]; puts $i
value is a int with a refcount of 2, object pointer at 0xb95a90, internal representation 0x1:0x1, no string representation
1 expect:~$ puts [::tcl::unsupported::representation $i]
value is a int with a refcount of 2, object pointer at 0xb95a90, internal representation 0x1:0x1, string representation "1"
expect:~$ incr i 0; puts [::tcl::unsupported::representation $i]
value is a int with a refcount of 2, object pointer at 0xb95a90, internal representation 0x1:0x1, no string representation

heinrichmartin schrieb am Montag, 13. November 2023 um 23:08:28 UTC+1:

> > > ...
> > > WHERE WeekNo >= ':startWeek'
> > > AND WeekNo <= ':endWeek'
> I did not test it, but are you telling SQL to compare strings here? That might introduce a data-dependent bug like '2' >= '10'.

True, that was a bad solution on my part.
This is how it could work:

cast(strftime('%W', dayDate, '+1 day') as integer)